Wednesday, January 8, 2014

Creating a SQL table "on the fly"

sql create table

I am rarely asked to create reports anymore. More frequently I am asked to create extracts of data into output files. The users then upload the data from the output files into Microsoft's Excel, and "slice-and-dice" the data to generate the format they desire.

The traditional approach works well. The programmer creates:

  • An output file to contain the extracted data.
  • A program, usually RPGLE/RPG IV to extract the desired data from the input files.

In this post I am going to show an example, of what I consider, a simpler way to extract data from the input files and create an output file "on the fly", i.e. without a pre-existing output file, using SQL.

In this simple example I have a request to create an extract from the file INFILE. I can add the following into a SQLRPGLE member.

03                  FROM INFILE
04                 WHERE SOURCE = '04')
05              WITH DATA ;

All of you familiar with SQL will recognize CREATE TABLE. I am creating the table (file) in QTEMP, as once the output file has been sent I have no longer need for it and it will be deleted when the job completes.

The SELECT statement on lines 2 - 4 gather the appropriate records from the input file.

The WITH DATA on line 5 is the critical part. This is informs the SQL processor that the data selected by the SELECT should be inserted into the output file.

When this completes I have an output file, and all of the columns (fields) in the output file have the same attributes as the fields in INFILE.

I prefer using this as I do not have to have a pre-existing output file that would have to be copied from one library to QTEMP before I could use it. The empty output file would take up space in a library. By all means one empty file does not take up much room, but hundreds start to.

If wanted to create an empty table I would use the DEFINITION ONLY in place of the WITH DATA, see below.

03                  FROM INFILE)
04              DEFINITION ONLY ;

I have to admit I am yet to find a reason to use the DEFINITION ONLY.

You can learn more about the CREATE 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.


  1. its a good trick to create a outfile directly from sql query .. easier way ..

  2. If you have PTF SI50412 installed (7.1), you could use the RUNSQL command to execute the SQL statement from a command line and bypass having to code and compile an RPG program.

    1. RUNSQL will be covered in a future post.

  3. Re the DEFINITIONS ONLY clause, a shop could create a table (PF) with column definitions for an application and then build an SQL script to create the application tables that will contain data using this form of CREATE TABLE AS. In other words, it could serve a similar purpose to REF and REFFLD in DDS, for a "field reference file", when SQL is used to create tables/files.

    Although it may be rare, another way to use DEFINITION ONLY could be to ensure OUTFILE exists as a first step, do some processing that might involve maintenance of data in INFILE, and finally populate OUTFILE using SQL such as:

    Also, if a clone of an existing table is desired (same column definitions), the LIKE option may also be useful:

    I was not familiar with the DEFINITION ONLY syntax, but found the following under CREATE TABLE notes in Infocenter:
    Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
    < snip >
    DEFINITION ONLY is a synonym for WITH NO DATA

  4. Thank you for this! I've been doing it the hard way (creating the table first) for way too long. This will be MUCH easier!

  5. Great tip. Another tool for the ol' tool box. Thank you.

  6. Excellent tip Simon. Thanks :)

  7. Several people have sent me messages about using DECLARE GLOBAL TEMPORARY TABLE as an alternative to the above.

    I do not want to publish their comments here as it will be a spoiler for a future post.

    I thank you all for your information about that SQL statement and ask you to be patient until the post about it is published.


  8. Just right now I would like to create a temporaly table but it must have a key.

    What could be the apropiated sentence for that?

    1. Sébastien JulliandJanuary 10, 2014 at 8:51 AM

      Hi Manuel,
      you can't use Primary Key to add a key constraint to a temporary table. DB/2 for i won't let you run such a query.

      Your only hope is to create the table anywhere but in QTEMP and drop it afterward. You can still create it on the fly, no probleme here.

      And don't try to create a table with a key out of QTEMP and move it in QTEMP afterward because the key would be automatically removed.

    2. You could SQL's CREATE INDEX statement to create an index (LF) in QTEMP over the table in QTEMP. Then you would use the index to access the data in the table.

  9. I think that I have an example of where 'definition only' would be used...

    Using embedded SQL in an RPG program with program variables in the where clause.
    For example:
    Exec sql
    Create Table qtemp/robstuff as (
    Select item, desc, class from itemmaster where crtdte >= :mydate
    ) with data;

    At least for v5r4 this create table command will not work with program variables. I used two different ways to work around this problem:
    1. First, I would create the sql statement in a string with program variables concatenated. Then run the query with prepare/execute or ‘execute immediate’
    Sqlstring = ‘create table qtemp/robstuff as (‘ +
    ‘Select item, desc, class from itemmaster where crtdte >= ’ + mydate +
    ‘) with data’;
    Exec sql execute immediate :sqlString;

    2. Second, I execute the create table command without the where clause and the key words ‘with no data’ to create the table. Then I would use an insert statement to load the table.
    Exec sql create table qtemp/robstuff as (
    Select item, desc, class from itemmaster
    ) with no data;
    Exec sql insert into qtemp/robstuff
    Select item, desc, class from itemmaster where crtdte >= :mydate;

  10. Wow I didn't know you could use a select statement to create a table! COOL!
    I think you can skip the string build though.

    Exec sql select ctdate into :mydate from from itemctrl;
    Exec sql create table qtemp/robstuff as (Select item, desc, class from itemmaster where crtdte >= :mydate‘) with data;

    Or if you want todays date

    Exec sql select current_date into :mydate from sysibm/sysdummy1
    Exec sql create table qtemp/robstuff as (Select item, desc, class from itemmaster where crtdte >= :mydate‘) with data;

    I try to avoid building strings for the SQL, that way I can develop the SQL interactively using an SQL scripter such as iNav. The completed SQL is then copied into the RPG.

  11. SQL is awesome, keep 'em coming Simon!

  12. Unsure why you have the extra overhead of creating a new temporary table and the need for a RPGLE program object ? Why not just use a SQL View and reference that from Excel ? No need to duplicate data into QTEMP, no need for a RPGLE program. Data will be current whenever accessed from Excel.

  13. STRSQL

    1 - to Change Session Attributes
    Select Output: 3 - for File
    Select an output file, library, member and option to create, replace,..etc
    You are now back to enter an SQL Statement. The result of your selection will go into the above file.

  14. This is a great idea, especially for temporary work tables (in QTEMP) for batch jobs, sales reporting and the like.

  15. Hi Simon, I'm using this source as you explain, but I can´t see the file at the end of the sql command.
    What I'm missing?

    exec sql create table marios/poliza as
    WHERE IONKC1 = '08' AND IORHST = '2')
    *inlr = *on;

    1. The table you are creating will be in the library MARIOS rather than QTEMP. Is the table created in the MARIOS?
      If the table is not being created in MARIOS then look at the job log for the SQL error code.

  16. Now how do you use that new table in your RPG program?

    1. Rather than try and fit that information into these comments I think it is best I create a new post to answer that question.

  17. I tried your code and it doesn't seemed to work when updating the file with data, but it does work for creating the columns. I'm on V6R1 does that make a difference?

    1. I have checked the documentation for IBM i 6.1 and it does include the "WITH DATA". See here.

      Alas, I do not have a server I can use that is running 6.1.

      When I compile a test program with TGTRLS(V6R1M0) with the "WITH DATA" the data is populated into the output file.

      I am not sure what else to say except double and triple check your code.

    2. I do not answer individual's programming questions. If I did I would be overwhelmed with people asking for me to analyze their programming issues.

      What I would recommend in this situation is...:

      - Can you create the table using the SQL interface (STRSQL)?

      - If you check the SQLCOD immediately following the SQL command if it is not zero then it will give you a code you can look up to diagnose your problem.

    3. compile you program with COMINT *NONE

  18. Hello !!
    I have been using your Approach to generate a file in Qtemp. Works fine. But I have a Problem, if I run my program a second time in the same Job, as the generated file in Qtemp is locked by my process. I tried to use SET OPTION CLOSQLCSR=*ENDMOD, but it still has a lock on the file (generating SQL0615 when I try to delete it or CPF3220, when trying to do it with DLTF). Any ideas, what I could do ?
    Thanks a lot, Sascha

    1. I have not seen these messages myself but having a quick Google it would appear:

      CPF3220 would appear to be caused by the table/file not being close. If you are using a SQL cursor that you have used the SQL OPEN statement make sure you have a CLOSE.
      Or make sure you have COMMIT = *NONE specified.
      Or if the file is overridden (OVRDBF) you must have a matching DLTOVR.

      SQL0615 if your IBM I is at 6.1 then there is a PTF that could fix this, SI34327.
      I see references to a missing SQL CLOSE statement too.

    2. I am in the same boat... and a newbie to the 400... how does one get rid of this lock?

  19. For users that want data extracts in Excel, the simplest and shortest way is to put the SQL code directly into the Excel transfer itself. No file or program needed. Assuming you have the add-in for Data Transfer to/from AS400 setup in Excel, then just set the option on the SQL tab in properties to "Process SELECT as native SQL". You can then just paste your SQL statement in the window on the Data Options button when going through the transfer wizard. The iSeries receives the SQL statement and processes it as it would if it was coming from a program. Save the finished transfer and the user runs it whenever that want to.

  20. Excellent Simon, thanks for this post!


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.