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.
What advantage does this DECLARE GLOBAL TEMPORARY TABLE XXXXXX AS have over CREATE TABLE QTEMP/XXXXXX AS... ?
ReplyDeleteMain advantage is the ON COMMIT clause and the DECLARE syntax is more portable.
ReplyDeleteMy question would is why create a temporary table? Most of the time I find the same processing can be accomplished with common table expressions or views - both of these offer a better performing solution.
I may only add that, in my experience and for most of my actually limited needs, one or more common table expressions - "With tablea as (select blah blah blah), tableb as..." - or one or more views in QTEMP most of the time do the trick and are quite fast.
DeleteI share the view of Stefano. Using temporary dynamic tables using with clause is pretty efficient. I have made queries with over 15 temp tables, using each other in the sub select, on rather large data files (millions records) and results where pretty fast and efficient...
DeleteFor one, a temp table created by DECLARE GLOBAL... will not accessible to non-SQL procedures like CL or RPG f-specs. That is both advantage and disadvantage.
ReplyDeleteI had a huge table with and a query with 12-18 subquery select statements to get amounts for accounts for each month of the period. The temporary table reduced the run time from hours to very few minutes. The system is also very busy during the day and it is partitioned so some processing power was diverted to Domino e-mail. errrrr.
ReplyDeleteA couple clarifications:
ReplyDelete-- You mention the statement has two forms, but you give examples of three:
1) Explicit column definitions
2) Cloning an existing table using the LIKE clause
3) Defining the columns using as-result-table [ AS (SELECT ...) ]
-- When using as-result-table, the WITH DATA clause can be used to populate the table with rows when it is created. So the WITH DATA form is similar to doing:
1) DECLARE GLOBAL TEMPORARY TABLE TEST1 (NAME CHAR(30), ... ) ...
2) INSERT INTO TEST1 SELECT NAME, CITY, ... FROM CUSTOMER ...
I used this technique to create a CSV parser based on a given record layout. The Record Layout is defined as a normal DDS, the Record Layout (the PF) is mapped to a mnemonic (i.e. PMT = PAYMENTPF).
ReplyDeleteIn the initialization phase this is executed:
declare global temporary table
outfile
like qadspffd
We then loop through all the various possible transactions and execute the DSPFD command to this outfile to load up the layouts.
The payment transaction comes in from a client via a Socket, and the data is parsed and mapped into a large Variable Length field which is then passed onto the appropriate processing program which receives the Parameter into an externally defined data structure and do the magic that needs to be done with the transaction.
The code is flexible enough, that all one need do is add an entry to a table that defines the mnemonic and the record layout, for additional transactions to support, as well as making the call Program/Procedure that will be using the transaction.
Fine !!
ReplyDeleteHi Roberto, I agree with Simon.
ReplyDeleteI would add another way ,
exec sql
declare global temporary table session/temporal_table
like original_table;
Hi, You can also create SQL temporary tables by using DECLARE CURSOR in the RPG program
ReplyDelete