Using Dynamic SQL in DB2 2

[Dunshee]

I need to use dynamic SQL in my COBOL program. Environment is DB2 / COBOL 370.

I need to build the actual select statement for the cursor, including a term "LOCATION IN ('xxx', 'xxx',...)
where I'll be getting a random (but small) number of
xxx's at run time. I now have a kludgey set up with
(LOCATION = :WS-XXX-1 OR
LOCATION = :WS-XXX-2 OR
LOCATION = :WS-XXX-3 OR
LOCATION = :WS-XXX-4 OR
LOCATION = :WS-XXX-5 )
which looks dopey and has to be increased when I
need to handle 6 or 7 locations...


I know there is a way to do this, but I can't find any
examples to steal (my favorite way of coding).

If I have to build a work area for the dynamic SQL, how
do I estimate its size?

Thanks much,
-Dunshee/

 

[RussBartlett]

Hi I'm not familiar with 370 Cobol but for the AS 400 you'd probably use the varying-list Dynamic SQL. This is where the number and type of parameters are unknown. Do you have this available in Cobol 370? On the AS 400 each parameter marker is a question mark (?).

 

[slade]

Hi,

You wrote:

.... but I can't find any
examples to steal (my favorite way of coding).


You broke into the wrong house. Have you tried the DB2 Forum?

 

[MarcLodge]

Hi Dun,
I've posted this in the DB2 forum too, but added here just in case anybody's interested..... What you need to do is to build the SQL dynamically in your program and then execute by issuing a PREPARE statement the format is something along the lines of.....
EXEC SQL
DECLARE CURS1 CURSOR FOR STMT1
EXEC SQL

In the logic of the program
PREPARE STMT1 INTO SQLDA FROM :WS-STATEMENT
END-EXEC
(having previously built your SELECT in WS-STATEMENT)

You'll then need to
EXEC SQL
OPEN CURS1 USING DESCRIPTOR :SQLDA
END-EXEC

and then fetch each row
EXEC SQL
FETCH CURS1 USING DESCRIPTOR :SQLDA
END-EXEC

You'll need to set up the SQLDA area which will contain the dynamic results of the read.

Hope this helps, come back to me if it doesn't

Marc Lodge

 

[MarcLodge]

Sorry forgot to say, if you want a link, try....

http://www-3.ibm.com/software/data/db2/udb/ad/v7/adg/db2a0/frame3.htm#dynamic

 

[Dunshee]

Thanks, MarcLodge, your example, and that on the link, will help. -dd/

 

[MarcLodge]

feel free to award a star if you wish!