Defining SQL tables using a reference file

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.