Hi,

What are the requirements to create a Global Temporary table in a Select Query. I mean:

1. Do we need to have sepearate tablespace in the DB
2. Do we require special access on the tables/tablespaces

Step 1:
I am trying to create a Global Temporary table using this query:

EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TNH_NUM_HIST (TNH_SEC_ID CHAR(10) NOT NULL ) ON COMMIT PRESERVE ROWS NOT LOGGED
;

Step 2: Inserting a record using :
EXEC SQL INSERT INTO SESSION.TNH_NUM_HIST VALUES ( :mc__5 )
;
where mc__5 is the Host variable where value is getting inserted from another DB query

Step 3elect record from Global Temporary table

EXEC SQL DECLARE DMTNH02_1 CURSOR FOR
SELECT tnh_SEC_ID
FROM
SESSION.TNH_NUM_HIST
;

EXEC SQL OPEN DMTNH02_1;

retValue = sqlca.sqlcode;

while(retValue == 0 )
{

EXEC SQL FETCH DMTNH02_1 INTO
:nms_sec_id
;


When I do this, I get DB error -501


What could be the possible reason for this error??
I am using UDB 8.1 FP2 running on AIX 5.1

Gulshan

Are you running these commands using a commandfile or the CLP?

If so, it's because autocommit is active and your cursor is automatically closed after it is openend. Use the -a switch to turn off autocommit and it should work.

Hope this helps

I am not running the query using a commandfile or CLP. CICS transaction (OLTP) is invoking the DB2 query.

Just to be sure

You do set retValue to sqlca.sqlcode within your while loop?

Yes, I do set and check the value for sqlca.sqlcode.
Some times, I get this db error '-1034' and the CICS system goes down.