After posting Creating a SQL table on the fly I received a communication from John Erps asking why I have not used the SQL DECLARE GLOBAL TEMPORARY TABLE statement to create a temporary SQL table.
The DB2 SQL section of IBM’s Infocenter website describes DECLARE GLOBAL TEMPORARY TABLE as:
The DECLARE GLOBAL TEMPORARY TABLE statement defines a declared temporary table for the current application process. The declared temporary table resides in the work file database and its description does not appear in the system catalog. It is not persistent and cannot be shared with other application processes. Each application process that defines a declared temporary table of the same name has its own unique description and instance of the temporary table. When the application process terminates, the temporary table is dropped.
So how does it work on the IBM i?
The DECLARE GLOBAL TEMPORARY TABLE statement comes in two “forms". In the first I code the columns (fields) that will be in the table, and in the second I use another file’s fields to be the columns. Below are examples of the two “forms":
01 DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST1 02 (COLUMN1 CHAR(10) NOT NULL, 03 COLUMN2 DEC(3,0) NOT NULL) 04 ON COMMIT PRESERVE ROWS 05 NOT LOGGED 06 RCDFMT TMP_FMT1 07 DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST2 08 LIKE MYLIB/TESTPF 09 ON COMMIT PRESERVE ROWS 10 NOT LOGGED 11 RCDFMT TMP_FMT2
The first line, lines 1 and 7, of the two statements are where the name of the temporary file is defined. Notice that I do not give a library, I will explain more later in this post.
In the first statement in lines 2 and 3 I define the columns (fields).
In the second statement by using the LIKE, line 8, means that all of the fields in the given file, TESTPF, are created as columns in the temporary table.
The ON COMMIT PRESERVE ROWS, on lines 4 and 9, preserves the row (record) after a COMMIT. If a COMMIT is performed without this part of the statement the row can be deleted.
NOT LOGGED, lines 5 and 10, mean that when changes are made to the table they are no logged. And when a ROLLBACK is performed changes to these tables will not be performed.
I always like to give all my SQL tables a record format name, which lines 6 and 11 do. If this is not given the record format name will be the same name as the file.
I mentioned above that you do not give the library name when you use this statement because, on the IBM i, the table is created in QTEMP.
In the example below I am going to use the AS clause to select the records from the file CUSTOMER I want to include in the temporary table. The SELECT allows me to define just the columns (NAME, CITY, STATE, ZIP, TELEPHONE) and the rows (STATE = ‘AZ’) I want in the temporary table.
DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST AS (SELECT NAME,CITY,STATE,ZIP,TELEPHONE FROM MYLIB/CUSTOMER WHERE STATE = ‘AZ’) WITH DATA LIKE MYLIB/CUSTOMER ON COMMIT PRESERVE ROWS NOT LOGGED RCDFMT TMP_FMT
You can learn more about the DECLARE GLOBAL TEMPORARY TABLE SQL statement on IBM's website here.
This article was written for IBM i 7.1, and it should work with earlier releases too.