Wednesday, July 5, 2023

Finding which fields were defined using a reference field

In my opinion one of the best things about the DDS database is the use of Reference fields. I can define a field in a one file, a "Reference file", and then use it to define fields in other files. These fields will inherit the properties of the "Reference field".

If I need to make a change to the database, changing the size on one field, I can compile all the files that use the "Reference field" and the change will made to the file.

But how can I know which files use a particular "Reference field"?

Fortunately a SQL View gives me the information I need to do this.

Let me start by creating some DDS files. First is a very small "Reference file", which I will call REFFILLE:

A          R REFFILER
A            FLD001        10A         COLHDG('First' 'Field')
                                       TEXT('First field')
                                       ALIAS(FIRST_FIELD)
A            FLD002        11P 2       COLHDG('Second' 'Field')
                                       TEXT('Second field')
                                       ALIAS(SECOND_FIELD)
                                       EDTCDE(J)

I have defined two "Reference fields", FLD001 and FLD002. Both have been defined with column headings, field text, alias which is the long name I can use, and with FLD002 the default edit code for the numeric field.

I have three files I will be using in this example. In each I have used a different way to define the "Reference fields".

The first is TESTFILE:

A                                      REF(REFFILE)
A          R TESTFILER
A            FLD001    R
A            FLD002    R

The REF keyword on the first line allows me to define the "Reference fields" with the same name as the fields in the Reference file.

The second file is TESTFILE1:

A          R TESTFILE1R
A            FLD001    R               REFFLD(FLD001 TESTFILE)
A            FLD002        20A

I have defined FLD001 with the "Reference field". Even though the Reference contains a field called FLD002 I can define a field with the same name, but different attributes, in this file.

Lastly is TESTFILE2:

A          R TESTFILE2R
A            MY_FIELD  R               REFFLD(FLD001 TESTFILE)

Here I given the field a different name to that of the "Reference field".

Now I have my files I can use the SYSCOLUMNS2 View to find if the fields in the files are defined using "Reference fields", if so which ones.

01  SELECT SYSTEM_TABLE_SCHEMA,SYSTEM_TABLE_NAME,
02         SYSTEM_COLUMN_NAME,
03         REFERENCE_LIBRARY,REFERENCE_FILE,
04         REFERENCE_FORMAT,REFERENCE_FIELD
05    FROM QSYS2.SYSCOLUMNS2
06   WHERE TABLE_SCHEMA = 'MYLIB'
07     AND TABLE_NAME LIKE 'TESTFILE%'
08   ORDER BY 1,2,3

The important columns here are:

  • REFERENCE_LIBRARY:  The library the "Reference file" is in
  • REFERENCE_FILE:  The "Reference file"
  • REFERENCE_FORMAT:  The "Reference file" record format
  • REFERENCE_FIELD:  The "Reference field"

Lines 6 and 7: I narrowed to the search for the results just to my library, MYLIB, and to all files that start with "TESTFILE" as this speeds up the generation of the results.

The results are:

SYSTEM_  SYSTEM_    SYSTEM
TABLE_   TABLE_     COLUMN_    REFERENCE  REFERENCE  REFERENCE  REFERENCE
SCHEMA   NAME       NAME       _LIBRARY   _FILE      _FORMAT    _FIELD
-------  ---------  ---------  ---------  ---------  ---------  ---------
MYLIB    TESTFILE   FLD001     MYLIB      REFFILE    REFFILER   FLD001
MYLIB    TESTFILE   FLD002     MYLIB      REFFILE    REFFILER   FLD002
MYLIB    TESTFILE1  FLD001     MYLIB      TESTFILE   TESTFILER  FLD001
MYLIB    TESTFILE1  FLD002     <NULL>     <NULL>     <NULL>     <NULL>
MYLIB    TESTFILE2  MY_FIELD   MYLIB      TESTFILE   TESTFILER  FLD001

The reference data for FLD002 in TESTFILE1 is all null as that was the field I defined without using a reference.

By changing the selection criteria, the WHERE clause, I can expand my search to more or all libraries. If I perform the statement for all libraries it will take a long time to return the results.

Now I want to know how many files contain fields that have been defined with one of the "Reference fields". I can use the following:

01  SELECT REFERENCE_LIBRARY,REFERENCE_FILE,
02         REFERENCE_FORMAT,REFERENCE_FIELD,
03         COUNT(*) AS "Count"
04  FROM QSYS2.SYSCOLUMNS2
05  WHERE REFERENCE_LIBRARY = 'MYLIB'
06  AND TABLE_SCHEMA = 'MYLIB'
07  GROUP BY REFERENCE_LIBRARY,REFERENCE_FILE,
08           REFERENCE_FORMAT,REFERENCE_FIELD
09  ORDER BY 1,2,3,4

Lines 1 and 2: I have the reference columns as I want to subtotal the results by them.

Lines 5 and 6: Again I am limiting my results to my library, just to make the statement fast.

Lines 7 and 8: The GROUP BY defines the subtotal criteria.

Line 9: And I have to sort the results by those columns too.

The results are:

REFERENCE  REFERENCE  REFERENCE  REFERENCE 
_LIBRARY   _FILE      _FORMAT    _FIELD     Count
--------   ---------  ---------  ---------  -----
MYLIB      REFFILE    REFFILER   FLD001         3
MYLIB      REFFILE    REFFILER   FLD002         1

The is a "gotcha" here. I create another file with a "Reference field" in it.

A          R TESTFILE9R                                       
A            F001      R               REFFLD(FLD001 TESTFILE)

I have referred to a field that was created using a "Reference field". How does SYSCOLUMNS2 handle that? Using the previous SQL statement my results are:

REFERENCE  REFERENCE  REFERENCE  REFERENCE 
_LIBRARY   _FILE      _FORMAT    _FIELD     Count
--------   ---------  ---------  ---------  -----
MYLIB      REFFILE    REFFILER   FLD001         3
MYLIB      REFFILE    REFFILER   FLD002         1
MYLIB      TESTFILE   TESTFILER  FLD001         1

The View does not handle this in a way that makes it easy for me tracing fields back to their original "Reference field". This illustrates why I would need to perform more analysis to determine which files have fields that were defined with "Reference fields", that might have been defined using "Reference fields" themselves

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.

1 comment:

  1. ... and the DSPF and PRTF files also have fields defined with reference files?

    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.