How to execute compound sql in DB2

Question

I am facing a challenge trying to understand DB2 sql (note, I am coming from MS SQL Server) :P.

Here is a scenario, I have got 2 tables one has IDs and other details, second one has lot of other info corresponding to each ID.

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

Coming from SQL Server, I am used to running scripts like:

Declare @ID  int
Declare @ID1 int

select @ID=ID from T1 where col1 = @ID1

select * from T2 where ID = @ID

This all runs fine there and gives me an ID corresponding ID1 which can further be used to obtain all info about ID from T2.

Sadly, in DB2 this explodes right in my face, I am scared if I will execute this query one more time, it will disown me forever :(.

I did some research and wrote this (am even stuck at variable declaration).

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

For others it worked great, but I am getting 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

Other Info:

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

[EDIT: using DECLARE] Another thing I tried that did not work for:

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

Update on May 13th, 2016 (Friday the 13th)

Creating a stored proc is the only way of fixing this :(

What are you attempting to do that a `JOIN` isn't an option? Have a look at the [DB2 Reference](http://publib.boulder.ibm.com/epubs/pdf/dsnsqk1c.pdf) - I think `BEGIN ATOMIC` is only part of a trigger definition (is that what you're attempting?). If you're just trying to execute this in a command shell, it's probably expecting `DECLARE` next, although I don't think that's actually the way you're wanting to go. What is your real question? — Clockwork-Muse, Oct 19 '12 at 23:09
@Clockwork-Muse I simply need to store the ID in a variable from table T1 and then use that variable in the rest of the script. Later on that script will become a stored procedure, but right now I am just starting, so need to learn a lil more about variable declarations etc. — Abhinav, Oct 19 '12 at 23:35
So, after much discussion with DBA, I have realized that it is not possible in this version of DB2 for z/OS V9.1. He has suggested that I should rather wrap it in the stored proc and then test it. Ill update if that works. — Abhinav, Oct 23 '12 at 18:34

score 3 · Answer 1 · answered Oct 21 '12 at 02:23

Your command works fine in DB2 10 for LUW. The data studio version does not matter, as it works the same from the CLP. I tested this code in the sample database:

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

Probably, the z/OS version you are using does not support inline SQL (Begin atomic). I am not a zOS DBA, and I know there are many SQL differences between LUW, iSeries and zOS.

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

This could also help (a little old) http://www.ibm.com/developerworks/data/library/techarticle/db2common/ — AngocA, Oct 21 '12 at 04:21

score 2 · Accepted Answer · answered Oct 22 '12 at 08:43

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 a 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 can't do that in DB2. The only way to do something like that is to build a dynamic sql statement and execute it. This is kind of a mess: you create an sql command in a string, and then prepare and execute it. And there are also restrictions on SELECT being used directly in dynamic sql. It is probably better to think of another design that will solve your problem, rather than going down this route.

How to execute compound sql in DB2

2 Answers2