0

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
nardnob
  • 840
  • 13
  • 23
Aru
  • 1
  • 2
  • Do you want something like this? https://stackoverflow.com/a/40360471/230471 I don't know whether DBeaver/DBVisualizer support this though. In PL/SQL there is one `declare` section at the top for all variables, and they don't start with `@`. Also Oracle SQL requires a `from` clause so you can't just `select 123;` (assuming the DB2 implementation is the same). The system dummy table `dual` is provided for this. – William Robertson Dec 24 '20 at 14:46
  • in DBeaver, I like to use blank lines as statement separators. The only (minor) downside is that if you like to have blank lines in your SPs (or queries etc), then you would need e.g. a dummy `--` comment if you like to space things out – Paul Vernon Jan 04 '21 at 17:49

1 Answers1

1

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.

mao
  • 8,724
  • 2
  • 10
  • 25
  • my dbvis settings delimiter is set to ";". its Db2 LUW type. having said this, can you please give an example? – Aru Jan 04 '21 at 22:17