Abhinav Abhinav - 6 months ago 21
SQL Question

How to execute compound sql in DB2

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 :(

Answer

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.