Wednesday, August 8, 2018

Using reference fields

using reference fields in dds, rpg, sql

When I first started programming on the AS400 the biggest improvement I encountered, compared to the System/36, was the external database. No longer did I have to have file specifications in my RPG programs, and I now had the ability to define fields by referencing another. Over the years I have used the referencing ability whenever I can had to define fields in files or variables in my RPG programs. By using reference fields makes database changes easier as a field's definition is changed in one place, then by recompiling all of the objects with the referencing, the new objects all have the new definition of the field in them.

In the past fortnight I have been asked several times about using reference fields, and in my experience there are many more who would like to ask the same question but feel intimated to do so. Therefore, I am writing this post to give examples of how I use them and why I do things the way I do.

All well-built databases have a data dictionary that defines the types of all the fields, or columns, within it. On AS400 and IBM i many of us achieved something similar using a reference file. This file contains the definition of every type of field that could be contained in any of the other files in the application.

01  A          R REFFILER                                             
02  A            ADDRESS       30A         TEXT('Address line')
03  A                                      COLHDG('Address' 'line')
04  A                                      VARLEN
05  A            CITY          20A         TEXT('City')
06  A                                      COLHDG('City')
07  A                                      VARLEN
08  A            STATE          2A         TEXT('State code')
09  A                                      COLHDG('State')
10  A            POSTALCDE     10A         TEXT('Post code')
11  A                                      COLHDG('Postal' 'code')
12  A                                      VARLEN
13  A                                      ALIAS(POSTAL_CODE)
14  A            CUSTNAME      30A         TEXT('Customer name')
15  A                                      COLHDG('Customer' 'name')
16  A                                      VARLEN
17  A                                      ALIAS(CUSTOMER_NAME)
18  A            CUSTNBR       10A         TEXT('Customer number')
19  A                                      COLHDG('Customer' 'number')
20  A                                      VARLEN
21  A                                      ALIAS(CUSTOMER_NUMBER)

The example above shows how I would define fields within a reference file. All would be defined with their length and type, of course, and the field text, column heading, and an alias. Originally I defined fields with aliases as when I would download the file using Client Access the alias would be the column heading in the downloaded file. More recently the alias can be used in your RPG code.

When I define a file, in this example a Customer file, I can just reference the field in the reference file, rather than have to remember the size of each field.

01  A                                      REF(REFFILE)
02  A          R CUSTOMERR
03  A            CUSTNBR   R
04  A            CUSTNAME  R
05  A            ADDRESS1  R               REFFLD(ADDRESS)
06  A                                      TEXT('Address line 1')
07  A                                      COLHDG('Address' 'line 1')
08  A                                      ALIAS(ADDRESS_1)
09  A            ADDRESS2  R               REFFLD(ADDRESS)
10  A                                      TEXT('Address line 2')
11  A                                      COLHDG('Address' 'line 2')
12  A                                      ALIAS(ADDRESS_2)
13  A            ADDRESS3  R               REFFLD(ADDRESS)
14  A                                      TEXT('Address line 3')
15  A                                      COLHDG('Address' 'line 3')
16  A                                      ALIAS(ADDRESS_3)
17  A            CITY      R
18  A            STATE     R
19  A            POSTALCDE R

Line 1: The REF file level keyword gives the name of the reference file.

Lines 3 and 4: The R reference column when defining a field tells the compiler to use the same named fields in the reference to define this field.

Lines 5 – 16: I want to have more than one line of address in my file. I need to give each address field an unique name, ADDRESS1, ADDRESS2, ADDRESS3, and refer to the ADDRESS field in the reference file using the REFFLD keyword. I also want each of the address fields to have its own unique text, column heading, and alias, therefore, I define them for each address field.

Lines 17 – 19: These three fields can be defined as they are in the reference file.

When this file is compiled the compiler will "look" at the reference file and take the definition from it.

A display file is just another file, therefore I can use referencing in it to define the fields within.

01  A                                      REF(CUSTOMER)


10  A          R SCREEN
11  A                                  1 31'R P G P G M . C O M'
12  A                                      DSPATR(HI)
13  A                                  3  3'Customer number  .'
14  A            CUSTNBR   R        B  3 22REFFLD(CUSTOMERR/CUSTNBR -
15  A                                      *LIBL/CUSTOMER)
16  A                                  4  3'Customer name  . .'
17  A            CUSTNAME  R        B  4 22REFFLD(CUSTNAME CUSTOMER)
18  A                                  6  3'Address  . . . . .'
19  A            ADDRESS1  R        B  6 22
20  A            ADDRESS2  R        B  7 22
21  A            ADDRESS3  R        B  8 22
22  A                                  9  3'City . . . . . . .'
23  A            CITY      R        B  9 22
24  A                                 10  3'State  . . . . . .'
25  A            STATE     R        B 10 22
26  A                                 11  3'Postal code  . . .'
27  A            POSTALCDE R        B 11 22

Line 1: In this situation I am using the Customer file as the reference file as all of these fields are in the Customer file.

Lines 14 and 15: If you define a field with a reference in SDA it adds all this extra code of: (file_member/field library/file)

Line 17: If I have references coming from multiple files I will not give a single reference like the first line. I will define each field with the file I am taking the data from. As you see all I need to give is: (field file)
I do this so it becomes documentation within the file as to where the value for that field comes from.

Lines 19 – 27: As there is a reference file at the top of the program these fields are defined to be the same as the same named field in the Customer file.

This example of a printer file gives a little more complicated example.

01  A          R CUSTHDR                   SPACEB(002)
02  A            CUSTNBR   R              1REFFLD(CUSTNBR CUSTOMER)
03  A            CUSTNAME  R             +1REFFLD(CUSTNAME CUSTOMER)
04  A            CITY      R             +1REFFLD(CITY CUSTOMER)
05  A            STATE     R             +1REFFLD(STATE CUSTOMER)
     *-------------------------------------------------------------
06  A          R CUSTDTL                   SPACEB(001)
07  A            ORDERNBR  R             10REFFLD(ORDERNBR ORDHDR)
08  A            ORDERSEQ  R             +1REFFLD(ORDERSEQ ORDDTL)
09  A            PARTNBR   R             +3REFFLD(PARTNBR ORDDTL)
10  A            ORDERQTY  R             +1REFFLD(ORDERQTY ORDDTL)
11  A            ORDERAMT  R             +1REFFLD(ORDERAMT ORDDTL)
     *-------------------------------------------------------------
12  A          R CUSTTOT                   SPACEB(001)
13  A                                     5'Count :'
14  A            TOTALCOUNT     6  0      5EDTCDE(J)
15  A                                    +3'Amount :'
16  A            TOTALAMT  R   +3        +1REFFLD(ORDERAMT ORDDTL)

Here I have three record formats:

  1. CUSTHDR lines 1 – 5
  2. CUSTDTL lines 6 – 11
  3. CUSTTOT lines 12 – 16

Here I have fields coming from multiple files:

  1. CUSTOMER
  2. ORDHDR
  3. ORDDTL

I have defined the fields with the reference field and file they are found in. As I said before I think this makes it easier for someone else to understand where the data for that file is coming from.

Line 16: The +3 in the size column tells the compiler that this fields will be three bigger than reference field. If ORDERAMT in ORDDTL is defined as 7,2 then TOTAMT in the printer file will be 10,2.

At the start of this post I mentioned that I can do the same for variables in RPG programs. I can either use a field from a file or another variable when defining a field.

Below is a snippet of RPG3 code.

01  C                     MOVE 0         FROM    72
02  C           *LIKE     DEFN FROM      TOFLD1
03  C           *LIKE     DEFN FROM      TOFLD2+ 2

Line 1: The variable FROM is defined as 7,2.

Line 2: The variable TOFLD1 is defined to be the same as the field FROM.

Line 3: The variable TOFLD2 is defined as two bigger than FROM, in other words it will be 9,2.

I have given this example in response to the person who assured me that you could not do this in RPG3.

I am so glad I don't have to use the RPG3 method of creating one field like another. In RPG fixed format definitions they could look like:

01  D FromField       S              7  2
02  D ToField1        S                   like(FromField)
03  D ToField2        S             +2    like(FromField)

Using free format definitions the equivalent code would look like:

01  dcl-s FromField packed(7:2) ;
02  dcl-s ToField1 like(FromField) ;
03  dcl-s ToField2 like(FromField:+2) ;

I guess I can include externally described data structures that I use a lot as data structure arrays especially when performing Fetches in SQL RPG programs. As I have written about them in other posts I will not give details here.

One of the questions was could I define a DDL (SQL) table using a field reference file. Yes, it possible here is the Customer file as a DDL table.

01  CREATE OR REPLACE TABLE MYLIB.CUSTOMER AS
02    (SELECT CUSTNBR,                            
03            CUSTNAME,
04            ADDRESS AS ADDRESS1,
05            ADDRESS AS ADDRESS2,
06            ADDRESS AS ADDRESS3,
07            CITY,
08            STATE,
09            POSTALCDE
10       FROM REFFILE)
11    DEFINITION ONLY
12    INCLUDING COLUMN DEFAULTS
13    RCDFMT CUSTOMERR ;

    -- Table description
14  LABEL ON TABLE CUSTOMER IS 'Customer master' ;

    -- Column headings
15  LABEL ON COLUMN CUSTOMER (
16    ADDRESS1 IS 'Address             1',
17    ADDRESS2 IS 'Address             2',
18    ADDRESS3 IS 'Address             3'
19  ) ;

    -- Text
20  LABEL ON COLUMN CUSTOMER (
21    ADDRESS1 TEXT IS 'Address line 1',
22    ADDRESS2 TEXT IS 'Address line 2',
23    ADDRESS3 TEXT IS 'Address line 3'
24  ) ;

As I had to do in the DDS example as I want three address fields I need to define ADDRESS1, ADDRESS2, and ADDRESS3 I need to define them based on the reference field ADDRESS, lines 4 – 6. Then I need to give the new fields unique column headings and text, lines 15 – 24.

If you are wanting to create reference file like functionality in DDL (SQL) you ought to consider using User Defined Types to do the same type of thing.

In this example these would be the User Defined Types I would create.

CREATE TYPE MYLIB.ADDRESS AS VARCHAR(30) ;
CREATE TYPE MYLIB.CITY AS VARCHAR(20) ;
CREATE TYPE MYLIB.STATE AS CHAR(2) ;
CREATE TYPE MYLIB.POSTAL_CODE AS VARCHAR(10) ;
CREATE TYPE MYLIB.CUSTOMER_NUMBER AS VARCHAR(10) ;
CREATE TYPE MYLIB.CUSTOMER_NAME AS VARCHAR(30) ;

When defining the DDL table where I could put the data type, for example VARCHAR(10), I use the User Defined Type name instead, CUSTOMER_NUMBER.

CREATE OR REPLACE TABLE MYLIB.CUSTOMER (
  CUSTOMER_NUMBER FOR COLUMN "CUSTNBR" CUSTOMER_NUMBER NOT NULL,
  CUSTOMER_NAME FOR COLUMN "CUSTNAME" CUSTOMER_NAME NOT NULL,
  ADDRESS_1 FOR COLUMN "ADDRESS1" ADDRESS NOT NULL,
  ADDRESS_2 FOR COLUMN "ADDRESS2" ADDRESS,
  ADDRESS_3 FOR COLUMN "ADDRESS3" ADDRESS,
  CITY CITY NOT NULL,
  STATE STATE NOT NULL,
  POSTAL_CODE FOR COLUMN "POSTALCDE" POSTAL_CODE NOT NULL
  )
  RCDFMT CUSTOMERR ;

In the above example I have omitted the code for the table description, column headings I would normally have included.

 

This article was written for IBM i 7.3, and should work for earlier releases too.

3 comments:

  1. Nice article. MS SQL can do this and now Db2 DDL can too.

    And it would be nice if Db2 DDL allowed an edit code like DDS does. For example, I really don't want to see commas in order numbers. Yes I can create a view but STRSQL honors the DDS edit codes so the mechanics are there. Thanks.

    Ringer

    ReplyDelete
  2. Even where DBA are present, the concept of data dictionaries seems to elude them.

    ReplyDelete
  3. ... but UDTs can only be modified if there is NO reference anywhere, i.e. you need to drop your tables, views etc. before it can changed.

    I'd prefer:
    CREATE OR REPLACE TABLE (Name1, Name2, ...)
    as (Select Fldx, Fldy, FldZ from ReferenceTable)
    With no Data.

    Now you can modify the SQL script or change the reference file and simply rerun the CREATE OR REPLACE statement.

    Birgitta

    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.