Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I normally use SQL Server but for this project I'm having to learn DB2. If I can get the below code to work I'm set for the whole project. Below is a elementary example of what I cannot get to work. All I want to do is make a Created Global Temp Table (CGTT) and append data to it. The same code below will work if I use a Declared Global Temp Table, but for my purposes I must have a CGTT.




The above executes fine with no errors. When the insert is executed it even tells me "1 row updated". However, the select * and count(*) both give me 0 results; I'm wondering if this is a permissions issue, but can I have permissions to create a CGTT but not insert to it??

Any help provided would be most appreciated. Thanks in advance.

share|improve this question

migrated from May 15 '13 at 7:53

This question came from our site for professional and enthusiast programmers.

Have you checked your execution plan for anything that seems unusual? – Aushin May 14 '13 at 14:30
up vote 8 down vote accepted

By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table.

You should either create the table using the ON COMMIT PRESERVE ROWS option, or disable autocommit while running your commands and issue an explicit COMMIT when you are done. Which option you choose depends on your business logic.

Screenshot of successful execution

share|improve this answer
Works for me. You're probably executing the entire script as a single statement, which obviously won't work. Did you change the statement terminator character defined in Data Studio, by chance? – mustaccio May 14 '13 at 15:26
Thx 4 the quick response. I'm using IBM Data Studio 3.2. Code is now: CREATE GLOBAL TEMPORARY TABLE TEST_TBL( KEY_ID BIGINT, SOMETEXT VARCHAR(10) ) ON COMMIT PRESERVE ROWS; INSERT INTO USERID.TEST_TBL VALUES(123456,'TEST TEST'); SELECT * FROM USERID.TEST_TBL; SELECT COUNT(*) FROM USERID.TEST_TBL; I receive error: "ILLEGAL USE OF KEYWORD ON. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.64.106" I unchecked "Commit Changes to the database after running" under SQL Development->Routines->Process under preferences. Am I doing something wrong where I entered ON COMMIT? – user2381870 May 14 '13 at 15:26
You should not be running this as a routine, because it's not a routine. Execute the statements in the SQL Editor. – mustaccio May 14 '13 at 15:27
Just checked and statement terminator is still ";" I changed the commit option is preferences back and it still errors out where the ON COMMIT is – user2381870 May 14 '13 at 15:30
I don't believe I'm running it as a routine, (forgive me this is only day 3 on Data Studio) I selected SQL or XQuery option to create. – user2381870 May 14 '13 at 15:36

Are you sure you have only one table? I mean, it seems that you create a table TEST_TBL without schema (taking current/default schema), and you are accessing USERID.TEST_TBL. Probably, the current schema for TEST_TBL is not USERID, and you already have a second table with tha name (USERID.TEST_TBL) that is empty.

For declared temporary tables, the schema is SESSION. For created global temporary tables, it seems it has a specific schema.

share|improve this answer
AngocA - I've tried adding the schema to the create statement as well with exact same results. I just moved this over into the Command Editor turned off the auto commit and added a commit after the insert. And still the same results. I'm thinking there's a permission setting that will allow me to create but not insert. Is that possible? – user2381870 May 14 '13 at 16:02
Even if the CREATE was happening in a different schema, the INSERT and the SELECT statements are qualified to the same place, therefore the same table. So multiple tables do not explain why the SELECT does not show the results of the INSERT – WarrenT May 14 '13 at 22:51

Please try using following steps


ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED IN USER_TEMP_TBSP" DB20000I The SQL command completed successfully. [db2inst9@jaimatadi ~]$ db2 "insert into SESSION.DEP6 select * from V_TOTAL_SALES"" " DB20000I The SQL command completed successfully. [db2inst9@jaimatadi ~]$ db2 "select * from SESSION.DEP6"


UK London 2100.00 UK Manchester 1290.00 USA Alaska 2130.00 USA California 1420.00 USA Los Angeles 1110.00 USA New York 1420.00

Thanks, Prashant

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.