CODEWITHSUNDEEP
plsqldb2dbeaverdbvisualizer
Aru
Aru

Reputation: 1

How can I write a DB2 pl/sql script in DBeaver/DBVisualizer

How can I write a simple DB2 pl/sql script in DBeaver/DBVisualizer? I am basically trying to create dynamic SQL (in a loop) and then run it. So for this I will need variables such as the SQL string, build, etc. and then run the script that was created dynamically.

Here is an example in SQL Server. I want to write something like this for DB2:

BEGIN  
  DECLARE @example VARCHAR(15) ;
  SET @example = 'welcome' ;
  SELECT @example;
END

Upvotes: 0

Views: 7129

Answers (1)

mao
mao

Reputation: 12267

dbvis lets you develop scripts for Db2.

You need to know some basics.

First you need to tell dbvis that there is an additional statement delimiter other than the default ; semi-colon. Db2 needs to know the difference between the end of an interim-statement and the end of a compound block and for this Db2 uses an additional delimiter/terminator for the end of a block.

You can either specify this block delimiter/terminator inside the script with the @delimiter command (specific to dbvis), or you can configure the delimiter via the dbvis settings GUI (this is the better approach). This depends on the version of your dbvis.

Many people use the @ character as the block delimiter when writing compound SQL for Db2 although other characters are possible (as long as it is different from the default semi-colon).

If you want your scripts to be runnable by Db2 command line processor directly without using dbvis (i.e. to run the scripts from the command line shell (cmd.exe or bash/ksh etc) then you would not use the @delimiter command because that is only known to dbvis. The Db2 command line processor understands the syntax --#SET TERMINATOR @ to change the delimiter on the fly inside scripts, and it also has a command line option (-td@) to let you specify the alternative delimiter via the command line.

Second you need to be aware of which platform (Db2-for-Z/OS, Db2-for-i, Db2-for-Linux/Unix/Windows/Cloud) that you are targetting, because the features and syntax can differ per platform. When asking for help with Db2 you should always specify the target platform, and stackoverflow has dedicated tags for db2-luw, db2-400, db2-zos.

Third you need to follow either ANSI SQL PL syntax rules (i.e. not transact SQL as used in Microsoft SQL Server), which includes rules on the valid characters for identifier names. On Db2 variables cannot begin with @. If your Db2-server runs on Linux/Unix/Windows, and it has been specially configured in advance, then you can also write your blocks in Oracle PL/SQL syntax and Db2 will emulate that. But at the present time there is no ability in Db2 to emulate Transact-SQL.

Upvotes: 1

Related Questions