Wednesday, May 14, 2014

Creating a secure temporary file in SQL

sql db2 declare global temporary table

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.

11 comments:

  1. What advantage does this DECLARE GLOBAL TEMPORARY TABLE XXXXXX AS have over CREATE TABLE QTEMP/XXXXXX AS... ?

    ReplyDelete
  2. Main advantage is the ON COMMIT clause and the DECLARE syntax is more portable.

    My 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.

    ReplyDelete
    Replies
    1. Stefano ProcenzanoMay 17, 2014 at 8:54 AM

      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.

      Delete
    2. I 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...

      Delete
  3. For 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.

    ReplyDelete
  4. I 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.

    ReplyDelete
  5. A couple clarifications:

    -- 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 ...

    ReplyDelete
  6. 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).

    In 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.

    ReplyDelete
  7. Hi Roberto, I agree with Simon.
    I would add another way ,

    exec sql
    declare global temporary table session/temporal_table
    like original_table;

    ReplyDelete
  8. Hi, You can also create SQL temporary tables by using DECLARE CURSOR in the RPG program

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.