How to execute compound sql in DB2

Question

I ran into a problem trying to understand DB2 sql (note, I come from MS SQL Server): P.

Here is the scenario, I have 2 tables that have identifiers and other data, the second has a lot of other information corresponding to each ID.

ID Info for ID _______ ____> _______ | | / | | | T1 |<---------> | T2 | |_____| \____> |_____| 

Coming from SQL Server, I use scripts like:

 Declare @ID int Declare @ID1 int select @ID=ID from T1 where col1 = @ID1 select * from T2 where ID = @ID 

All this works great and gives me ID ID1, which can be used to get all the identifier information from T2.

Unfortunately, in DB2 this explodes right on my face, I am afraid that if I run this request again, it will forever renounce me: (.

I did some research and wrote this (I was even stuck in a variable declaration).

 --#SET TERMINATOR @ BEGIN ATOMIC DECLARE UID char(30); END @ 

For others, it worked fine, but I get the following error:

 BEGIN ATOMIC DECLARE UID char(30); END ILLEGAL USE OF KEYWORD ATOMIC. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.63.108 

Additional Information:

IBM DB2 for z / OS V9.1 IBM Data Studio V3.1.1.0

[EDIT: using DECLARE] Another thing I tried that didn't work:

 CREATE VARIABLE UID CHAR(30) DEFAULT 'USERID'; select * from testdb2.T1 A WHERE A.UID=v_UID; --some other activity goes here --and here DROP VARIABLE UID; 

TIA, Abhinav

---

May 13, 2016 Patch (Friday the 13th)

Creating a stored procedure is the only way to eliminate it: (

Answer 1

Here is the basic syntax for your first case:

 create variable id_var integer; create variable id_var1 integer; set id_var = 100; set id_var1 = (select id from t1 where id = id_var); select * from t2 where id = id_var1; 

In this example, however, you are trying to use a variable as the column name:

 CREATE VARIABLE UID CHAR(30) DEFAULT 'USERID'; select * from testdb2.T1 A WHERE A.UID=v_UID; --some other activity goes here --and here DROP VARIABLE UID; 

Unfortunately, you cannot do this in DB2. The only way to do something like this is to create a dynamic SQL statement and execute it. This is kind of a mess: you create the sql command in a string, and then you prepare and execute it. There are also SELECT restrictions that are used directly in dynamic sql. It is probably best to think of another design that will solve your problem, and not go this route.

Answer 2

Your team works great in DB2 10 for LUW. The data studio version does not matter, since it works the same way with CLP. I tested this code in a sample database:

 BEGIN ATOMIC DECLARE UID char(30); SET UID = 200280; SELECT FIRSTNME, LASTNAME FROM ANDRES.EMPLOYEE WHERE EMPNO = UID; END @ 

Perhaps the z / OS version you are using does not support embedded SQL (Begin atomic). I am not a zOS database administrator, and I know that there are many SQL differences between LUW, iSeries and zOS.

Please check compatibility between platforms. This is a very good blog to understand the problem: https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/crossplatformsqlrefv4?lang=en