Unable to update a Created Global Temporary table.

[vsaraog]

Hi everybody,

I asked the following question but didn't get any reply. If anyone
knows something about the problem, then please reply since I am really
in a bind. Here is the question...

I am using a Created Global Temporary Table in DB2 OS/390 version
7.1.2 with "ON COMMIT DELETE ROWS". The DBA says that I they don't have
the option of "ON COMMIT PRESERVE ROWS" for Created Global Temp.
tables. Anyway... I am inserting some data in it and then updating the
table using ODBC but while I try to update it, I am getting following
message.

"SQL0526N The requested function does not apply to declared temporary
tables. SQLSTATE=42995 "

Following is the code snippet.
CDatabase* db;
....
db->BeginTrans()
....

CString sql_insert = "INSERT INTO TempTbl(ARRAY_I D, PROD_ID,
POOL_NUM, POOL_TYPE, SEC_ID, SEC_TYPE_ID, FACTOR, FACTOR_DATE, COUPON)
VALUES (11, 10199,'SARAOG', 'ZZ', 55555,7, 5.55, 123, 6.66);";

db->ExecuteSQL(sql _insert); // Works fine

CString sql_update = "UPDATE TempTbl SET PROD_ID = 777777 where
array_id = 11";

db->ExecuteSQL(sql _update); // Gives error here
....

Thanks for your replies,
Vikas

[Thiru]

Hi,

Created tablespace is not a candidate for update command. For more
information see http://www.craigsmullins.com/dbu_0802.htm. why are you
moving for created temporary table? Hope you can use declared temporary
table instead(if possible).

Cheers,
Thiru.
WantedToBeDBA.

[vsaraog]

Thanks Thiru for the reply. I found the same thing from the DBA.
Unfortunately in our environment, we are not allowed to have declared
temporary table. Due to the restriction and this limitation on
temporary tables, we are getting data from some other place.

Regards, Vikas

[Thiru]

If its a restriction, then nothing could be done..

Thiru.
WantedToBeDBA.