Defining SQL tables using a reference file, continued

Yesterday I showed how to create SQL tables using a reference file. Tommaso Arcieri message me with another approach, defining a table based on another file or table.

In this example I am going to use the physical file TESTFILE as the original reference file.

A R TESTFILER A FLD001 10 A FLD002 3P 0 A FLD003 10 A FLD004 5P 0 A FLD005 9P 2 A FLD006 7P 2 A FLD007 1 A FLD008 1 A K FLD001

This just a small file of only eight fields, so it would not be a big deal to enter each field name. But for a field with many more fields it would be a pain to do so. Fortunately the approach Tommaso showed me should be used if the original reference file has only one field or hundreds.

I will be using the CREATE TABLE as I did yesterday, but I will not be using a SELECT to list the columns I want in the table. As I want all the fields in TESTFILE to be columns in my new table I use a LIKE clause:

CREATE TABLE MYLIB/TESTFILE2 LIKE TESTFILE RCDFMT FORMAT2

As expected the LIKE clause specifies that the columns in the new table will be exactly the same as the fields or columns in the original reference file. If I run the 'Display File Field Description' command, DSPFFD, you can see that the columns are the same as the original file:

Data Field Field Type Length FLD001 CHAR 10 FLD002 PACKED 3 0 FLD003 CHAR 10 FLD004 PACKED 5 0 FLD005 PACKED 9 2 FLD006 PACKED 7 2 FLD007 CHAR 1 FLD008 CHAR 1

If your new table is not in the library QTEMP you can add a key to it using ALTER TABLE:

ALTER TABLE MYLIB TESTFILE2 ADD PRIMARY KEY (FLD003,FLD004)

If the new table is in QTEMP you need to read Add key to SQL table in QTEMP.

You can see how to add the equivalent of Column Headings and Text using LABEL ON COLUMN in yesterday's post.

 

More information about CREATE TABLE can be found on the IBM website here.

 

This article was written for IBM i 7.1, and it should work with earlier releases too.