DB2 will not INSERT into Created Temp Table that I created

db2

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.

CREATE GLOBAL TEMPORARY TABLE TEST_TBL(
    KEY_ID BIGINT,
    SOMETEXT VARCHAR(10)
);

INSERT INTO USERID.TEST_TBL
VALUES(123456,'TEST TEST');

SELECT * FROM USERID.TEST_TBL;
SELECT COUNT(*) FROM USERID.TEST_TBL;

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.

Best Answer

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

Related Question