MVSFORUMS.com

[Hema_Manyam]

Hi,

I have learned that DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor.

Below is SENSITIVE STATIC SCROLL cursor, how should I use declared temporary table here ?? How to relate DGTT with the below cursor ?Please advise. I read IBM help, but could not get the complete picture.

[kolusu]

Hema_Manyam,

Check this

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/1.2.1.2.2?SHELF=&DT=20010710165542&CASE=

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Hema_Manyam]

Hi Kolusu,

Thanks for your quick reply.
I have already referred this link. But I could not understand the relation between scrollable cursor and GTT , on how they should be operated together ?? Please help me.

In my case should I declare GTT as below ??

EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEMPPROD
AS (SELECT SN, PN, JN, QTY FROM SPJ)
DEFINITION ONLY
INCLUDING COLUMN DEFAULTS
END-EXEC

EXEC SQL INSERT INTO SESSION.TEMPPROD ......???

How to proceed further ??

Thanks,
hema

[Manas Biswal]

Hema_Manyam,

Scrollable cursors use declared temporary tables for storing intermediate resultsets and for scrolling through them. You don't have to explicitly declare the temporary tables in your application if you want to use scrollable cursors. Just declare your scroll cursor and carry on with your open/fetch statements. The necessary temporary tables will be automatically declared and destroyed in the background. You don't have to worry about that from a programmer's perspective.

However, verify that a couple of things are in place with regards to temporary tables before you attempt to use scrollable cursors, particularly if your installation is using temporary tables for the first time.
Declared temporary tables are materialised in the TEMP database on your subsystem. Don't confuse these temporary tables with "created temporary tables" which are created on the workfile database - DSNDB07. There can be only one TEMP database in a subsystem. So, verify with your DBA, if there is a TEMP database on your subsystem. If not, then that can be created using the "..AS TEMP" clause. And there should be segmented tablespaces within the TEMP database that you can use.
Provided all this setup is there, you are ready to roll with scrollable cursors.

HTH...Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

[Hema_Manyam]

Manas,
That was an excellent explanation. Thanks for your detailed reply.
I got the complete picture now.

Regards,
Hema

[Hema_Manyam]

Manas,

I have read in manual that DECLARE GLOBAL TEMPORARY TABLE is issued in program to create an empty instance and then INSERT , DELETE, UPDATE can be used in GTT. In what cases these are useful ?? Pls advise.. Thanks

Regards
Hema

[Manas Biswal]

Hema_Manyam,

Temporary tables have their own uses. They are used in stored procedures to store intermediate data. They are also used extensively in programs which combine usage of relational and non-relational data. For example, suppose you use a VSAM file and a DB2 table in the same program. You can then read the entire VSAM file within the program to a temporary table and then use that as relational data within your program. So, in effect now, your program deals with relational data.
The usage of temporary tables is infinite. All depends on your imagination.

You can have a look at the following article by Craig Mullins. It explores both the types of temporary tables in detail alongwith their potential usage -

http://www.craigsmullins.com/dbu_0802.htm

HTH...Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)