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.

   /free
01    exec sql  CREATE TABLE QTEMP/OUTFILE AS
02               (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT 
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.

   /free
01    exec sql  CREATE TABLE QTEMP/OUTFILE AS
02               (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT
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.

35 comments:

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

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

    ReplyDelete
  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:
    INSERT INTO QTEMP/OUTFILE
    SELECT SOURCE ... FROM INFILE WHERE SOURCE = '04'

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

    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

    ReplyDelete
  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!

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

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

    Thanks

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

    ReplyDelete
    Replies
    1. Sébastien JulliandJanuary 10, 2014 at 10: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.

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

      Delete
  8. 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;

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

    ReplyDelete
  10. SQL is awesome, keep 'em coming Simon!

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

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

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

    ReplyDelete
  14. 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
    (SELECT IONXC1,IOPYV1,IOJSDT,IOP0V1
    FROM UUIOREP
    WHERE IONKC1 = '08' AND IORHST = '2')
    WITH DATA;
    *inlr = *on;

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

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

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

      Delete
  16. 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?

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

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

      Delete
    3. compile you program with COMINT *NONE

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

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

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

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

    ReplyDelete
  19. Excellent Simon, thanks for this post!

    ReplyDelete
  20. Can I create a file joining two files??

    ReplyDelete
    Replies
    1. I do not see why you should not be able to create a table from an SQL statement that contains a join.

      Delete
  21. I need to check if file is exists or not. If not need to create a file in strsql. I tried create table if not exists tablea as (select * from tableb) with no data. It is not working. Please help me in giving answer for this question.

    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.