Hi all,

I'm trying to declare a global temporary table and insert some rows from a regular table, but it doesn't work.

Here are my commands:

step1: DECLARE GLOBAL TEMPORARY TABLE session.test LIKE user.userdata IN USERTEMP NOT LOGGED

note:The USERTEMP is a user-temporary tablespace created by myself, and the DB2ADMIN user is authorized to user this temporary tablespace.

step2: INSERT INTO session.test SELECT * FROM user.userdata

note:The USER.USERDATA is a regular table,it has 230,000 rows.

The result is: it reports this command in step2 is executed succesfully without any errors, but there is no data in SESSION.TEST.

Please, who can tell me what's the problem? and how should I insert data from a regular table to a temporary table?

My DB2 version is 7.2.0.

Thanks!!

Hi,

can you record a transcript of your statements and DB2's messages and return codes? This will be of great help, because if it turns out to be a bug, you should be able to document this for IBM support.

And I would like to have a look at this, too.

Johann

Thank you for your help, I have solved that problem. When I declare a temporary table, there is not a ON COMMIT PRESERVE ROWS parameter in the SQL command, and the default parameter value is ON COMMIT DELETE ROWS.It means when the transaction is committed, all rows in the temporary table will be deleted. When I added this parameter to the command, it can save the data now.

The right SQL should be:

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST LIKE USER.USERDATA IN USERTEMP ON COMMIT PRESERVE ROWS NOT LOGGED

Thanks again!