export from Temp tables

Ed Mullikin

export from Temp tables
Is there a way with DB2 to use SQL to export data from temp tables with in the same session? As soon as the session is done so is the data we want from temp tables. Was able to do this with Informix, has anybody done this with DB2?

Thanks,
Ed Mullikin
Santa Clara University



Phil Grainger

Re: export from Temp tables
(in response to Ed Mullikin)
Perhaps a silly question, but if you want the data after the UOW completes,
why is it in a temporary table??

Otherwise, you could SELECT the data in your program and just write the data
out to a QSAM/VSAM file

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: Ed Mullikin [mailto:[login to unmask email]
Sent: 03 January 2002 16:33
To: [login to unmask email]
Subject: [DB2-L] export from Temp tables


Is there a way with DB2 to use SQL to export data from temp tables with in
the same session? As soon as the session is done so is the data we want from
temp tables. Was able to do this with Informix, has anybody done this with
DB2?

Thanks,
Ed Mullikin
Santa Clara University



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]

[login to unmask email]

Re: export from Temp tables
(in response to Phil Grainger)
What do you mean exactly by export the data from temp tables (assume you
are talking about global and not declarative)?

One of the main uses and intents of 'temporary tables' is in the use of
stored procedures and returning result sets. We've used them (SP's and
Temp Tables) with web based applications and clients when business rules
determine that a definable cursor is not possible or practical.

Remember that you can only insert and select from temporary tables (no
updates or deletes) and that once 'something' (like an SP, batch program,
etc) inserts into a temporary table, that 'something' has an 'instance' of
that table that exists until that 'something' is terminated.

A common way to 'export' data from a temporary table through a stored
procedure is with a cursor defined on that temporary table 'with return'
like as follows:

PROCEDURE DIVISION USING ...

EXEC SQL
DECLARE RET-CUR CURSOR WITH RETURN FOR
SELECT ...
FROM T.TTEMP01
WHERE ...
END-EXEC.

<PROGRAM LOGIC>
EXEC SQL
INSERT INTO
T.TEMP01
VALUES (...)
END-EXEC.

<OTHER PROGRAM LOGIC>
EXEC SQL
INSERT INTO
T.TEMP01
VALUES (...)
END-EXEC.

EXEC SQL
OPEN RET-CUR
END-EXEC.

PERFORM PROG-END
...

Dean




Ed Mullikin
<[login to unmask email] To: [login to unmask email]
.EDU> cc:
Sent by: DB2 Subject: export from Temp tables
Data Base
Discussion
List
<[login to unmask email]
M>


01/03/02 11:33
AM
Please respond
to DB2 Data
Base
Discussion
List






Is there a way with DB2 to use SQL to export data from temp tables with in
the same session? As soon as the session is done so is the data we want
from temp tables. Was able to do this with Informix, has anybody done this
with DB2?

Thanks,
Ed Mullikin
Santa Clara University



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]







****************************************************************************

The information contained in this transmission, which may be
confidential and proprietary, is only for the intended recipients.
Unauthorized use is strictly prohibited. If you receive this
transmission in error, please notify me immediately by telephone
or electronic mail and confirm that you deleted this transmission
and the reply from your electronic mail system.
****************************************************************************