Q and A

Asked and Answered

How can I refer a global temporary table defined in the calling stored procedure in the called stored procedure?

Robert Catterall responds:

The best way to deal with your first question is to use a declared temporary table, a capability added to DB2 for OS/390 version 6 via the fix for APAR PQ32670 --and part of the DB2 version 7 base code ý you can download at http://techsupport.services.ibm.com/ s390/390.uhuic_getrec? args=DVhuron.boulder.ibm.com+ DB390+DA39782+ STDB2,DECLARED, TEMPORARY+USbin .

A key advantage of a declared temporary table versus a global temporary table is that information about declared temporary tables is not entered into the DB2 catalog when the table is created. Why is this important? Because it means that two or more declared temporary tables with the same fully qualified name can exist at the same time. The name of the table only has to be unique for the thread through which the table was created. With a global temporary table, the fully qualified table name must be unique, so you either have to single-thread whatever process is going to create and reference the table, or you have to ensure that every thread will have a unique ID associated with it. It is not uncommon for people to want to have "generic" IDs for a given application (consider a CICS-DB2 set-up in which all transactions have a DB2 primary authorization ID equal to the authorization ID of the associated CICS region). With declared temporary tables, generic authorization IDs are no problem. Stored procedure A creates a declared temporary table, called (for example) TEMPTAB, and stored procedure B (called by stored procedure A) simply selects (or uses some other SQL DML statement) from TEMPTAB. How do you know that stored procedure B is referencing the right TEMPTAB? Simple: it's the only TEMPTAB associated with that particular thread. Even as you go from one stored procedure to the next, it's still the same DB2 thread.

See a complete archive of reader/author Q&As .

Back to The Limited Case For Dynamic SQL by Robert Catterall