Dynamic SQL in COBOL

[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?

(I posted this originally in the COBOL Forum, & was advised to try here also...)

Thanks much,
-Dun<TD /

 

[MarcLodge]

Hi Dun,
I saw this in the Cobol forum too, but needed to check some things before replying. 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
(I'll post this in Cobol forum too)
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

 

[gregsimpson]

You could set up a small table (either temporary or permanent) with only 1 column, LOCATION.

Have your program Delete all values from the table, then Insert into the table the values for LOCATION whenever it is run.

In your existing cursor use a sub select to access the new table by adding something along the lines of the following in the WHERE clause

WHERE.....

A.LOCATION IN(SELECT B.LOCATION FROM NEWTABLE).

This should have the effect that you will only pick up those locations in which you are interested. It never needs amending, no matter how many LOCATIONS are added. Keeps the SQL from being, 'kludgey' and you can also get away with having it static rather than dynamic.

Greg

 

[Dunshee]

OK, thanks.

But now I have a new question: What do you mean by
&quot;a temporary table&quot;? Assuming I have CREATE permission
on the database, I can imagine how to do that. Does
DB2 support a &quot;temporary table&quot; as something local to
my currently executing process?

The characteristics which would make this very nice
are:
- the temporary table is &quot;local&quot; to my job/program;
other users of the database would not see it
- the temporary table would be dropped automatically
when the program ends.

I have built a temp table in the past, but had to get the
permission/assistance of the DBA, and had to include an
&quot;instance&quot; number in the key -- a a number unique to my
job that would allow multiple programs to insert rows
without interfering with each other.

So, is there something in between an inline view (which
exists in and for this SQL statement only), and a regular
table (which exists until explicitly deleted and is
visible to all users of the database)?

Thanks,
-dd/

 

[gregsimpson]

I have included a high level description of the TEMPORARY TABLE from the DB2 Manual below. The whole text is to large to include here. However if you are not keen on the temporary table(I prefer not to use temporary tables myself and would favour the permanent table solution) the permanent table could be defined much as you said with LOCATION + UNIQUESESSIONIDENTIFIER, so you only delete and insert rows for your session. Still has all the advantages of not having to change the SQL and letting DB2 do the work etc.

I'm not sure why your DBA made you use the instance number in the key as the documentation suggests a temporary table cannot be shared with other sessions, therefore this seems superfluous.

Greg


DECLARE GLOBAL TEMPORARY TABLE

The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

SYSADM or DBADM authority
USE privilege on the USER TEMPORARY table space.
When defining a table using LIKE or a fullselect, the privileges held by the authorization ID of the statement must also include at least one of the following on each identified table or view:

SELECT privilege on the table or view
CONTROL privilege on the table or view
SYSADM or DBADM authority
Syntax

>>-DECLARE GLOBAL TEMPORARY TABLE--table-name------------------->

.-,------------------------.
V |
>-----+-(-----| column-definition |---+---)--------------------------+>
+-LIKE--+-table-name2-+---+-------------------+----------------+
| '-view-name---' '-| copy-options |--' |
'-AS--(--fullselect--)--DEFINITION ONLY--+-------------------+-'
'-| copy-options |--'

.-ON COMMIT DELETE ROWS---.
>----*--+--------------+--*--+-------------------------+---*--NOT LOGGED--*->
'-WITH REPLACE-' '-ON COMMIT PRESERVE ROWS-'

>----+----------------------+---*------------------------------->
'-IN--tablespace-name--'

>----+------------------------------------------------------------------+---*->
| .-,--------------. |
| V | .-USING HASHING-. |
'-PARTITIONING KEY--(-----column-name---+---)--+---------------+---'

>--------------------------------------------------------------><

column-definition

|---column-name---| data-type |----+---------------------+------|
'-| column-options |--'

column-options

|---*--+-----------+---*--+------------------------------------------------------+---*-->
'-NOT NULL--' +-| default-clause |-----------------------------------+
'-GENERATED--+-ALWAYS-----+---AS--| identity-clause |--'
'-BY DEFAULT-'

>---------------------------------------------------------------|

copy-options

.-COLUMN ATTRIBUTES-.
.-EXCLUDING IDENTITY--+-------------------+--.
|---*--+---------------------------------------+---*--+--------------------------------------------+---*-->
| .-COLUMN-. | | .-COLUMN ATTRIBUTES-. |
'--+-INCLUDING-+--+--------+--DEFAULTS--' '-INCLUDING IDENTITY-+-------------------+---'
'-EXCLUDING-'

>---------------------------------------------------------------|


Description

 

[MarcLodge]

I'm not a DBA so would question the efficiency of deleting all rows from a table and then inserting the ones that you require, just for doing a join. Wouldn't dynamic SQL be more efficient?
Marc

 

[Dunshee]

Thanks, Greg,

This is exactly what I was thinking of, but didn't know if it existed. Phase 2 will be finding out if our DBA allows temp tables (& getting &quot;USE privilege on the USER TEMPORARY table space.&quot

(You asked &quot;why your DBA made you use the instance number in the key ... [in ] a temporary table...&quot;

I wasn't clear. I was using a permanent table as a temp table, in the manner you described in the 1st paragraph. )

Thanks again,
-d/

 

[gregsimpson]

Answer for MarcLodge,

it would appear that Dunshee is only intending to write 5 or 6 rows to this table, hence he will only be deleting 5 or 6 rows. Even more beneficial, if Dunshee isn't doing a commit(enquiry only), the chances are these rows won't be physically written, they will simply go to the buffer. Therefore if Dunshee deletes them again at the end of his process(and good programming practice says he will), they will in all probability only have been written to the buffer so the overhead is absolutely minimal.

Dunshee,

I did some further reading last night and came across someone who claimed they had used temporary tables but were unable to perform a join on them. I haven't found this comment anywhere else and certainly not in the IBM manuals. If this proves to be the case you may be better going with the option of using a permanent table like a temporary table in the manner we discussed earlier. Let me know how it goes.

 

[gregsimpson]

Dunshee,

looked it up in my DB2 Developers Guide(DB2 Bible) and it distinctly says you can join to Global Temporary Tables. So you should be fine to proceed along this path.

 

[Dunshee]

Thanks again Greg.
-dd/