Wednesday, October 1, 2014

Defining SQL tables using a reference file

reference file, create table, alter table, label on column, label on table

I am old enough to have been working with IBM midrange computers before the introduction of the AS400 in 1987, see 25th anniversary of IBM i (AS400). Twenty six years ago I was a programmer on the System/36 using RPG II. I can remember the first day I started programming on the AS400 how I was blown-away by the relational database, which was not present on the System/36. The files I created could refer back to a reference master file, and I would no longer have to worry about ensuring that all the invoice number fields were the same size, etc.

With the modernization drive to replace DDS files with SQL tables how do I do the same as a reference file in SQL. All the examples I have seen show the DDL (Data Definition Language) for the SQL tables where the data type and size of the columns (fields) have been defined. In my opinion this is a step back from the beauty of the relational database.

So how can I use a reference file in SQL?

I could use the method I described in the post Creating a SQL table on the fly, but there must be a better way.

With DDS files it was easy to use a reference file. You would create the reference file like the one below:

 A          R REFFILER
  *
 A            AMOUNT         7P 0       TEXT('Amount')
                                        COLHDG('Amount')
                                        EDTCDE(J)
 A            CUSTOMER       9A         TEXT('Customer number')
                                        COLHDG('Customer' 'No.')
 A            INVOICE       12A         TEXT('Invoice number')
                                        COLHDG('Invoice' 'No.')
 A            REFDATE         L         DATFMT(*ISO)
                                        TEXT('Date field')
                                        COLHDG('Date')

Yes I am pedantic enough to have the fields in my reference file sorted in alphabetical order.

By using the reference file the definition for an Invoice header file could look like this:

 A                                      REF(REFFILE)
 A                                      UNIQUE
 A          R INVHDRR
 A            INVOICE   R
 A            CUSTOMER  R
 A            INVDATE   R               REFFLD(REFDATE)
                                        TEXT('Invoice date')
                                        COLHDG('Inv' 'date')
 A            INVAMT    R               REFFLD(AMOUNT)
                                        TEXT('Invoice amount')
                                        COLHDG('Inv' 'amt')
 A          K INVOICE

For those of you who are familiar with my code in this blog know that I do not use what I call the RPG III naming convention for fields:

  • My field names will be longer than 6 characters… OK, the majority of the field names will be longer than 6.
  • The fields do use the first 2 characters to denote which file they are in. The introduction of the PREFIX when RPGLE was first introduced made this redundant.
  • I use the same name for a field in multiple files. INVOICE will always be the invoice number in all the files that contain the invoice number.

With the INVDATE and INVAMT I have used generic fields in the reference file, REFDATE and AMOUNT, to define them. I have also given them a different field text and column headings to make them more meaningful than the generic ones.

Below is how I could create the DDL for the equivalent of the DDS file:

01  CREATE TABLE MYLIB/INVHDR AS 
02    (SELECT
03      INVOICE AS INVOICE,
04      CUSTOMER AS CUSTOMER,
05      REFDATE AS INVDATE,
06      AMOUNT AS INVAMT
07      FROM MYLIB/REFFILE)
08    DEFINITION ONLY
09    INCLUDING COLUMN DEFAULTS
10    RCDFMT INVHDRR ;

11  ALTER TABLE MYLIB/TESTTABLE
12     ADD PRIMARY KEY (INVOICE) ;

13  LABEL ON TABLE MYLIB/INVHDR IS 'Invoice header' ;

14  LABEL ON COLUMN MYLIB/INVHDR (
15      INVDATE IS 'Inv                 date',
16      INVAMT  IS 'Inv                 amt'
17      ) ;

18  LABEL ON COLUMN MYLIB/INVHDR (
19     INVDATE TEXT IS 'Invoice date',
20     INVAMT TEXT IS 'Invoice amount'
21     ) ;

In the first part of the above I use the CREATE TABLE to create the table. Lines 2-7 are where I list the columns I want in this table. It is also where I define what the columns names are in my table. Lines 5 and 6 are where I have renamed the generic fields in the reference file to INVDATE and INVAMT. Line 7 is where I give the name of the reference file, in fact any other DDS file or SQL table can be used. Line 8 means that I am only using this statement to create the table, not fill it with data. By using the INCLUDING COLUMN DEFAULTS, line 9, means that any default values defined in the reference file will be applied to the columns in this file too. And on line 10 I have given the record format name that a RPG program can use when using the file.

I have to admit I struggled to add a key to the file to the table as part of the CREATE TABLE. The only way I found to add one was to use the ALTER TABLE. Although I have defined a PRIMARY KEY the key is unique.

Line 13 uses the LABEL ON TABLE to give the table its file text.

As I did in the DDS file I want to change the column headings and the field/column text for the INVDATE and INVAMT columns. Lines 14-17 I used the LABEL ON COLUMN to change the column headings. The reason there is a big gap between the Inv and the date or amt is that SQL parses the label into the three column heading fields:

  • Column heading 1 = label positions 1-20
  • Column heading 2 = label positions 21-40
  • Column heading 3 = label positions 41-60

The field/column text is given using the same, LABEL ON COLUMN lines 18-21, but for the field/column text I had to use the TEXT IS, rather than just the IS.

Notice how each statement ends with a semi colon ( ; ).

To create the table I use the RUNSQLSTM command.

Below is the output from the DSPFFD command for the SQL table. You can see how the columns have been defined from the reference file, and that the column headings and field/column text has been changed for INVDATE and INVAMT.

                             Display File Field Description
 Input parameters
   File  . . . . . . . . . . . . . . . . . . . :  INVHDR
     Library . . . . . . . . . . . . . . . . . :  *LIBL
 File Information
   File  . . . . . . . . . . . . . . . . . . . :  INVHDR
     Library . . . . . . . . . . . . . . . . . :  MYLIB
   File location . . . . . . . . . . . . . . . :  *LCL
   Externally described  . . . . . . . . . . . :  Yes
   Number of record formats  . . . . . . . . . :      1
   Type of file  . . . . . . . . . . . . . . . :  Physical
   SQL file type . . . . . . . . . . . . . . . :  TABLE
   File creation date  . . . . . . . . . . . . :  08/05/14
   Text 'description'. . . . . . . . . . . . . :  Invoice header
 Record Format Information
   Record format . . . . . . . . . . . . . . . :  INVHDRR
   Format level identifier . . . . . . . . . . :  24FF20AA0414D
   Number of fields  . . . . . . . . . . . . . :      4
   Record length . . . . . . . . . . . . . . . :     35
 Field Level Information
              Data        Field  Buffer    Buffer        Field    Column
   Field      Type       Length  Length  Position        Usage    Heading
   INVOICE    CHAR           12      12         1        Both     Invoice
                                                                  No.
     Field text  . . . . . . . . . . . . . . . :  Invoice number
     Referenced information
       Referenced file . . . . . . . . . . . . :  REFFILE
         Library . . . . . . . . . . . . . . . :  MYLIB
       Referenced record format  . . . . . . . :  REFFILER
       Referenced field  . . . . . . . . . . . :  INVOICE
       Attributes changed  . . . . . . . . . . :  None
     Coded Character Set Identifier  . . . . . :     37
              Data        Field  Buffer    Buffer        Field    Column
   Field      Type       Length  Length  Position        Usage    Heading
   CUSTOMER   CHAR            9       9        13        Both     Customer
                                                                  No.
     Field text  . . . . . . . . . . . . . . . :  Customer number
     Referenced information
       Referenced file . . . . . . . . . . . . :  REFFILE
         Library . . . . . . . . . . . . . . . :  MYLIB
       Referenced record format  . . . . . . . :  REFFILER
       Referenced field  . . . . . . . . . . . :  CUSTOMER
       Attributes changed  . . . . . . . . . . :  None
     Coded Character Set Identifier  . . . . . :     37
              Data        Field  Buffer    Buffer        Field    Column
   Field      Type       Length  Length  Position        Usage    Heading
   INVDATE    DATE           10      10        22        Both     Inv
                                                                  date
     Field text  . . . . . . . . . . . . . . . :  Invoice date
     Referenced information
       Referenced file . . . . . . . . . . . . :  REFFILE
         Library . . . . . . . . . . . . . . . :  MYLIB
       Referenced record format  . . . . . . . :  REFFILER
       Referenced field  . . . . . . . . . . . :  REFDATE
       Attributes changed  . . . . . . . . . . :  None
     Date Format . . . . . . . . . . . . . . . :  *ISO
     Coded Character Set Identifier  . . . . . :     37
              Data        Field  Buffer    Buffer        Field    Column
   Field      Type       Length  Length  Position        Usage    Heading
   INVAMT     PACKED       7  0       4        32        Both     Inv
                                                                  amt
     Field text  . . . . . . . . . . . . . . . :  Invoice amount
     Referenced information
       Referenced file . . . . . . . . . . . . :  REFFILE
         Library . . . . . . . . . . . . . . . :  MYLIB
       Referenced record format  . . . . . . . :  REFFILER
       Referenced field  . . . . . . . . . . . :  AMOUNT
       Attributes changed  . . . . . . . . . . :  None

 

There is more about defining SQL tables using a reference file here.

 

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

10 comments:

  1. Excellent blog entry, Simon!! You show a very easy way to use a reference file in DDL. Looking forward to comments regarding other methods...

    ReplyDelete
    Replies
    1. Thank you!
      There will be more on this subject. "Modernization" is still a voyage of discovery for us all.

      Delete
  2. Very good article. I have been using SQL for a while and did not know we could do that. Thanks.
    Question: How would you do that in some type of a Source Control Management application?
    Promote the table to production and then run the Alter table command?
    Again, thank you,
    Esdras

    ReplyDelete
    Replies
    1. That is a very good question, and the answer would make a good subject for a future post.

      Delete
    2. Hi!

      In our case, what we do is separate the scripts with alter, and promote them alone.

      So, we have one script for the CREATE, and other for CONSTRAINTS, PRIMARY KEY...

      Delete
  3. As always, thanks for your post!

    One question, is there any way to mix referenced columns with common ones (defined)?
    In the same way we do that in DDS.


    Thanks!

    ReplyDelete
    Replies
    1. As with DDS you can intermix columns defined from referring to another with those where they are defined with the data type, size, etc

      Delete
    2. Old post, but I think it might be useful to share how to create a "mixed" table like this. Here an example:

      CREATE OR REPLACE TABLE testlog2 AS (SELECT
      CAST(NULL AS TIMESTAMP) AS TimeUpd ,
      CAST(NULL AS CHAR(28)) AS JobId ,
      CAST(NULL AS CHAR(2)) AS Operation ,
      a.*
      FROM Param00f a
      ) DEFINITION ONLY

      First 3 columns are "manually" defined, all other ones are duplicated from PARAM00F table (labels included !)

      Delete
  4. I needed to use a reference file to keep the standard fields in order, but I needed an identity field for a file with poor keys. I added CAST(0 AS INTEGER) AS Record_ID to the list of fields. It did not like the NULL as show in the example. Then I added ALTER TABLE TABLEPF
    ALTER COLUMN RECORD_ID SET DATA TYPE INTEGER GENERATED ALWAYS AS IDENTITY;

    This allowed me to have both worlds. The program generating the data I need to track is standard I/O. When I checked, the identity fields looked great.

    ReplyDelete
  5. Is there a way to find all files that use a field reference field?

    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.