AS/400 Links Partner with Rochester Institute Contact Us
Training Catalog Free Downloads Privacy / Usage


FREE AS/400, iSeries, and System i / iSeries Downloads
HOME >> FREE AS/400, iSeries, and System i / iSeries Downloads >> Create Table Like, But Different



Create Table Like, But Different
Author: Raymond Everhart


As iSeries programmers explore the use of embedded SQL in their applications, the natural tendency is to try to understand new capabilities in terms of familiar techniques. The CREATE TABLE LIKE statement is often equated to the CREATE DUPLICATE OBJECT (CRTDUPOBJ) command. Unfortunately, that comparison can lead to frustration.

But We've Always Done it This Way!
The usual application development cycle on the iSeries calls for the coding of file specifications using DDS as the first step. The reason is because the file must exist before a program that uses that file can be compiled. The benefit of this approach is that the operating system lets you know if the file structure has changed since the program was created. For years, we have been using CRTDUPOBJ to create a work file in the QTEMP library based on an existing file. This technique has become so common that we don't even think of doing it any other way.

New physical or logical files can be created ad-hoc from within programs, but it requires a call to the CREATE PHSYICAL FILE (CRTPF) or CREATE LOGICAL FILE (CRTLF) command. In most cases, if you're creating an ad-hoc or work file, it's because the next job in the stream needs to use it. Because the next program in the job stream needs a copy of the file to be compiled, the easiest way to handle this situation is by creating a "work" file that always exists on the system. Level checking on files protects you from having files that are out of sync with programs, but it also imposes some design constraints.

If a program uses embedded SQL only to access a certain file, no file level checking is performed against that file. This distinction becomes important if you are using a mixture of embedded SQL and native I/O methods in your programs. In the RPG example below, when this program is run, the ITEM file (see line 2) will be level checked, but the Customer file (see line 7) will not be level checked.

 

 

 

 

 

 

 

One thing to keep in mind is if you are using static SQL, as shown above, the SQL pre-compiler requires that the file be found on the system before the program can be compiled. If you want to create and use a truly ad-hoc file within your program, you must use a dynamic SQL statement. (I'll show you an example of that later.)

Is it New and Improved or Just New?
In OS400 v5r2, two new clauses were introduced to the CREATE TABLE statement. The CREATE TABLE AS statement allows you to create a table based on the fields you select in a sub-query. You can optionally populate the new table with the records that match the selection criteria of the sub-query. The CREATE TABLE LIKE statement creates a new table with the same columns (fields) as the specified table. This new capability is similar to the CREATE DUPLICATE OBJECT (CRTDUPOBJ) or COPY FILE (CPYF) command, but there are some important differences.

A table created with the CREATE TABLE LIKE statement may not have the same level ID as a file created with the COPY FILE or CREATE DUPLICATE OBJECT command. To understand the difference, you must remember that DB2 Universal Database on the iSeries has a few more features than other DB2 UDB and/or SQL compliant databases. Two of the iSeries features that are not supported by SQL are the creation of Multiple Member files and the ability to specify a format name on a file. When SQL is used to create a table on DB2 UDB for the iSeries, the table name is used as the format name.

At first glance, using the table name as the format name is just an annoyance to programmers who have to rename the format when the file is declared within their programs. However, when using the CREATE TABLE LIKE Statement, this behavior poses a larger problem. If the original file was created using DDS, the format name will differ from the file name. So if you use CREATE TABLE LIKE to make a copy of a file built using DDS, the format names will be different. Even though the old file and the new table have the same fields and layout, the file level IDs will be different because the format names will be different. In some cases, it may not be a big problem to use SQL to recreate the table and change the affected programs to rename the format name. Operations Navigator even has a facility that will analyze an existing file and create the correct SQL statement to create the table. However, making this change to some of your core application files and the hundreds of affected programs is unthinkable.

The Simplest Rule
The "Simplest" rule to follow in order to avoid level-check problems is this:

  • If the file was created with DDS use the COPY FILE or CREATE DUPLICATE OBJECT command.
  • If the table was created with SQL use the CREATE TABLE LIKE statement, the COPY FILE or CREATE DUPLICATE OBJECT command.

If you are looking for a standardized method for creating "work" files from within your applications, consider this approach:
To determine the method used to create the object, retrieve the Table Type from the SYSTABLES table.

  • If the Table Type is a 'T' then the table was created with SQL.
  • Create your "work" file using the CREATE TABLE LIKE statement.
  • If the Table Type is an "P" then the file was created with DDS
    Create your "work" file using the COPY FILE or CREATE DUPLICATE OBJECT command. 

The program shown below is a simple framework for you to build your own tool:

     D cmdlen          S             15  5
     D command         S            512
     D filename        S             10
     D library         S             10
     D newfile         S                   Like(filename)
     D newlibrary      S                   Like(library)
     D sqlstmt         S            512
     D tabletype       S              1
     C     *Entry        PList
     C                   Parm                    filename
     C                   Parm                    library
     C                   Parm                    newfile
     C                   Parm                    newlibrary
      * Retrieve the table type from the system table
     C                   Clear                   tabletype
     C/Exec SQL
     C+ SELECT
     C+ TABLE_TYPE into :tabletype
     C+ FROM systables
     C+ Where TABLE_SCHEMA = :library and
     C+       TABLE_NAME = :filename
     C/End-Exec
     C                   Select
      * Process tables & views created using SQL
     C                   When      tabletype = 'T' 
     C                   Eval      sqlstmt = 'Create Table '
     C                                       + %Trim(newlibrary)
     C                                       + '/'
     C                                       + %Trim(newfile)
     C                                       + ' Like '
     C                                       + %Trim(library)
     C                                       + '/'
     C                                       + %Trim(filename)
     C/Exec SQL
     C+ Execute Immediate :sqlstmt
     C/End-Exec
      * Process physical and logical files created with DDS
     C                   When      tabletype = 'P' 
     
     C
     C                   Eval      command = 'CRTDUPOBJ '
     C                                     + 'OBJ(' + %Trim(FILENAME) + ') '
     C                                     + 'FROMLIB(' + %Trim(LIBRARY) + ') '
     C                                     + 'OBJTYPE(*FILE) '
     C                                     + 'TOLIB(' + %Trim(NEWLIBRARY) + ') '
     C                                     + 'NEWOBJ(' + %Trim(NEWFILE) + ') '
     C                   Eval      cmdlen = %Len(%Trim(command))
     C                   Call      'QCMDEXC'
     C                   Parm                    command
     C                   Parm                    cmdlen
     C                   EndSL
      * End the Program
     C                   Eval      *INLR = *On

Why Bother?
You might be wondering why you should bother with CREATE TABLE LIKE at all. The answer is at the beginning of this article: design constraints. SQL gives you an alternative, so you can do things you can't do easily using DDS and native I/O. Of course, one alternative is to use CL or APIs to handle some of these functions. Just remember one thing, if you master DDS, RPG, and CL you're still limited to the iSeries or some RPG/Cobol emulator environment. If you master SQL you can work on any platform.

How can I use CREATE TABLE AS?
The code snippets below show some of the capabilities of CREATE TABLE AS. The example archives transactions from a table. The transactions are copied to a new table and then purged from the original table. The table contains a time-stamp field named TSTAMP. When the program is run, all of the transactions that are older than six months are archived. Each archive table will have a five-character prefix followed by a five digit sequential number. Our sample software application has twenty-eight transaction tables. The only column in common to all of the tables is the TSTAMP column. The challenge is to write one program module that can handle the archive function for all of these tables.

Step 1. Build the archive table name.
While SQL supports long names for tables, we know that OS/400 supports only 10 characters for an object's system name. DB2 UDB on the iSeries will automatically create a unique "system name" if the table name specified is over 10 characters long. Both the system name (10 characters) and the table name are stored in the DB2 UDB catalog. We are going to take advantage of this feature and allow the database manager to determine what the next system name will be. We are going to build a table name that is made up of a five-character prefix, the date and time that the job was run. This will result in a 31-character table name. The SQL statement shown below returns the file name as the result.

D newfile         S             31
D prefix          S              5 INZ('HIST_')

C/Exec SQL
C+ SELECT
C+ :Prefix || Char(Current_Timestamp)                             
C+ into :newfile
C/End-Exec


This code returns a value that looks like this:

HIST_2004-06-10-23.20.44.383996

This value should always be unique as an SQL file name because of the microsecond precision of the Current_Timestamp function. You need to enclose this value in double quotes when you use it in the CREATE TABLE AS statement. When the table is created, the system name will be HIST_00001 for the first table, HIST_00002 for the second table created and so on. When you refers to this table in SQL, you can use either the system name or the SQL name.

Step 2. Create the archive file and copy records into it.
The SQL statement shown below is called dynamic because it can be different each time the statement is executed. The SQL statement will not be syntax-checked until the program tries to execute it. The EVAL statement builds the SQL statement using variables, built-in functions. and constants. The Execute Immediate command does a syntax check on the statement stored in the sqlstmt variable and then submits it to the database manager for processing. Please note the WITH DATA clause at the end of the SQL statement. By adding WITH DATA to the statement, the data set created by the subquery will be added to the new table. 

 * Create a new table and copy the eligible records into it
C                   Eval      sqlstmt = 'Create Table '
C                                       + %Trim(newlibrary)
C                                       + '/"'
C                                       + %Trim(newfile) + '"'
C                                       + ' As (select * From '
C                  &nnbsp;                    + %Trim(library)
C                                       + '/'
C                                       + %Trim(filename)
C                                       + ' WHERE Date(TSTAMP) < '
C                                       + '(Current_Date - 6 months) '           
C                                       + 'With Data'           
                                       
C/Exec SQL
C+ Execute Immediate :sqlstmt
C/End-Exec

 
The code shown above will generate a value in the sqlstmt variable that will look something like this:

Create table FileLib/"HIST_2004-06-10-23.20.44.383996" as
(Select * from FileLib/ItemTrans Date(TSTAMP) < (Current_Date - 6 months))
With Data

Step 3. Delete the records from the original table.
Once again you use a dynamic SQL statement. This statement deletes the records from the original file using the same record selection used to copy records into the archive file.


C                   Eval      sqlstmt = 'Delete from '
C                                       + %Trim(library)
C                                       + '/'
C                                       + %Trim(filename)
C                                       + ' WHERE Date(TSTAMP) < '
C                                       + 'Current_Date - 6 months) '    
                                   
C/Exec SQL
C+ Execute Immediate :sqlstmt
C/End-Exec

The code shown above will generate a value in the sqlstmt variable that will look something like this:

Delete from FileLib/ItemTrans

Where Date(TSTAMP) < (Current_Date - 6 months)

An alternate way to delete these records is to delete only the records from the source file that exist in the archive file. The value in sqlstmt would need to look something like this:

Delete from FileLib/ItemTrans

Where TSTAMP in

(Select TSTAMP from FileLib/"HIST_2004-06-10-23.20.44.383996")

So in just three steps, you have accomplished all of your objectives. Of course, you will need to add a little more code to make this example fully functional. By adding journaling to your files and commitment control to the program, you can make the program recover gracefully in case of an error.


Raymond Everhart has over 18 years of IT experience with IBM midrange computers. He was a consultant, programmer, and educator for an IBM premier business partner in Upstate New York for 10 years, before relocating to the Dallas/Ft. Worth area in 1998. Since then, Raymond has been helping local companies implement new technologies with his consulting, programming, and mentoring services. You can reach Raymond with your questions at reverhart@raecodesign.com.