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.