Pages

Wednesday, November 10, 2021

New columns added to SYSCOLUMNS2 SQL View

new columns added to sql view syscolumns2

I need to keep reminding myself to use the SYSCOLUMNS2 SQL View rather that SYSCOLUMNS as IBM tell us that it gets the results faster than the old View. IBM keeps enhancing the new View, and as part of the latest Technology Refreshes, IBM i 7.4 TR5 and 7.3 TR11, they added 14 new columns.

These new columns are particularly useful when working with DDS files as some of these columns deal with field attributes that are not available in DDL Tables and Views.

Prior to these additions I would use the Display Field Description command, DSPFFD, to generate an output file for the files I desired. This meant that this was always a two-step process:

  1. Create the output file
  2. "Read" the data in the output file

Now I can get the information I want in just one step, by accessing the data directly from the SYSCOLUMNS2 View.

I am not interested in all 14 of the new columns, the ones I am interested in are:

Description DSPFFD field SYSCOLUMNS2 column
Internal field name WHFLDI INTERNAL_FIELD_NAME
Date/time format WHFMT DATE_FORMAT
TIME_FORMAT
Date/time separator WHSEP DATE_SEPERATOR
TIME_SEPERATOR
Edit code and fill character WHECDE EDIT_CODE
EDIT_CODE_FILL
Edit word WHEWRD EDIT_WORD
Reference library WHRLIB REFERENCE_LIBRARY
Reference file WHRFIL REFERENCE_FILE
Reference record format WHRFMT REFERENCE_FORMAT
Reference field WHRFLD REFERENCE_FIELD

Before I start showing the kinds of information that is returned in these new columns I need some DDS files and a DDL table.

The first file is TESTFILE:

01 A          R TESTFILER
02 A            F_DATE          L
03 A            F_TIME          T
04 A            F_NBR1         3P 0
05 A            F_NBR2         3P 0

The file contains one date field, line 2, a time field, line 3, and two number fields, 4 and 5.

Next file is TESTFILE1:

01 A                                      REF(TESTFILE)
02 A          R TESTFILE1R
03 A            F_DATE    R               DATFMT(*USA)
04 A            F_TIME    R               TIMFMT(*USA)
05 A            F_NBR1    R               EDTCDE(J)
06 A            F_NBR2    R               EDTWRD('0 - - ')

Line 1: The REF keyword means that I can define fields in this file based upon the fields in TESTFILE. The "R" by the field names means that these fields will be defined to be the same as their namesakes in TESTFILE.

Lines 3 and 4: I have defined the date and time formats for the date and time fields to be USA format.

Lines 5 and 6: I have defined the number fields with an Edit code and an Edit word. "J" is my favorite Edit code.

One more DDS file, TESTFILE2:

01 A                                      REF(TESTFILE)
02 A          R TESTFILE2R
03 A            F_DATE    R               DATFMT(*MDY)
04                                        ALIAS(FILE_DATE)
05 A            F_TIME    R               ALIAS(FILE_TIME)
06 A            F_NBR1    R               EDTCDE(M *)
                                          ALIAS(FILE_NUMBER_1)
07 A            F_NBR2    R               EDTWRD(' @  ')
08                                        ALIAS(FILE_NUMBER_2)

I have added the ALIAS keyword to all of the fields in this file. These will become the long names for these fields.

Line 3: The date is defined with the date format of MDY.

Line 5: There is no formatting to the time field.

Line 6: The Edit code is now "M" and the unused part of the field will be filled with asterisks ( * ).

Line 7: I have to admit I was struggling to come up with an original Edit word for this field.

Lastly I created a DDL table, TESTTABLE:

01  CREATE TABLE MYLIB.TESTTABLE
02  (COLUMN_DATE DATE,
03   COLUMN_TIME TIME,
04   COLUMN_NUMBER_1 DECIMAL(3,0),
05   COLUMN_NUMBER_2 DECIMAL) ;

I cannot use a date or time format in a DDL table. I also cannot use Edit codes or Edit words.

Due to limited space in this post I have created three different SQL statements showing these new columns in SYSCOLUMNS2. I have also included other columns I find useful when learning about the fields or columns in a file or table. All of the new columns are marked with an asterisk ( * ).

The first statement covers the field names, type, and size.

01  SELECT SYSTEM_TABLE_NAME AS "File",
02         COLUMN_NAME AS "Long name",
03         SYSTEM_COLUMN_NAME AS "Sys name",
04         INTERNAL_FIELD_NAME AS "Int name*",
05         DATA_TYPE AS "Type",
06         DDS_TYPE AS "DDS",
07         LENGTH AS "Len",
08         NUMERIC_SCALE AS "Dec"
09    FROM QSYS2.SYSCOLUMNS2 
10   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 
11     AND SYSTEM_TABLE_NAME LIKE 'TEST%' 
12   ORDER BY 1,ORDINAL_POSITION

I am only going to talk about the new columns in this view.

Line 4: This is the name of the physical format field. In a logical file this is the name of the field that the logical field is based.

Line 12: I have sorted the results by the file name and the ordinal position. I am not showing the ordinal position in my results, but it is a generated number that denotes the field's position in the file.

My results look like:

File       Long name      Sys name     Int name*  Type    DDS Len Dec
---------- -------------- ------------ ---------- ------- --- --- ------
TESTFILE   F_DATE          F_DATE      F_DATE     DATE    L     4 <NULL>
TESTFILE   F_TIME          F_TIME      F_TIME     TIME    T     3 <NULL>
TESTFILE   F_NBR1          F_NBR1      F_NBR1     DECIMAL P     3      0
TESTFILE   F_NBR2          F_NBR2      F_NBR2     DECIMAL P     3      0

TESTFILE1  F_DATE          F_DATE      F_DATE     DATE    L     4 <NULL>
TESTFILE1  F_TIME          F_TIME      F_TIME     TIME    T     3 <NULL>
TESTFILE1  F_NBR1          F_NBR1      F_NBR1     DECIMAL P     3      0
TESTFILE1  F_NBR2          F_NBR2      F_NBR2     DECIMAL P     3      0

TESTFILE2  FILE_DATE       F_DATE      F_DATE     DATE    L     4 <NULL>
TESTFILE2  FILE_TIME       F_TIME      F_TIME     TIME    T     3 <NULL>
TESTFILE2  FILE_NUMBER_1   F_NBR1      F_NBR1     DECIMAL P     3      0
TESTFILE2  FILE_NUMBER_2   F_NBR2      F_NBR2     DECIMAL P     3      0

TESTTABLE  COLUMN_DATE     COLUM00001  COLUM00001 DATE    L     4 <NULL>
TESTTABLE  COLUMN_TIME     COLUM00002  COLUM00002 TIME    T     3 <NULL>
TESTTABLE  COLUMN_NUMBER_1 COLUM00003  COLUM00003 DECIMAL P     3      0
TESTTABLE  COLUMN_NUMBER_2 COLUM00004  COLUM00004 DECIMAL P     5      0

I have manually inserted a blank line after each file's results to make it easier to understand the data shown.

As I did not given the columns in the DDL Table short names the operating system has created them for me, which are just plain ugly!

Next statement is just concerned with the new columns to do with date and time formatting, date and time separators, edit codes, and edit words.

01  SELECT SYSTEM_TABLE_NAME AS "File",
02         SYSTEM_COLUMN_NAME AS "Sys name",
03         DATE_FORMAT AS "DatFmt*",
04         DATE_SEPARATOR AS "DatSep*",
05         TIME_FORMAT AS "TimFmt*",
06         TIME_SEPARATOR AS "TimSep*",
07         EDIT_CODE AS "EdtCde*",
08         EDIT_CODE_FILL AS "EdtFill*",
09         EDIT_WORD AS "EdtWrd*"
10    FROM QSYS2.SYSCOLUMNS2 
11   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 
12     AND SYSTEM_TABLE_NAME LIKE 'TEST%' 
13   ORDER BY 1,ORDINAL_POSITION

I think the names of the new columns describes what they contain well, there is no need for me to repeat.

The results are:

File       Sys name   DatFmt* DatSep* TimFmt* TimeSep* EdtCde* EdtFill* EdtWrd*
--------- ---------- ------- ------- ------- -------- ------- -------- --------
TESTFILE  F_DATE     ISO     <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE  F_TIME     <NULL>  <NULL>  ISO     <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE  F_NBR1     <NULL>  <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE  F_NBR2     <NULL>  <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>

TESTFILE1 F_DATE     USA     <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE1 F_TIME     <NULL>  <NULL>  USA     <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE1 F_NBR1     <NULL>  <NULL>  <NULL>  <NULL>   J       <NULL>   <NULL>
TESTFILE1 F_NBR2     <NULL>  <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   0 - -

TESTFILE2 F_DATE     MDY     /       <NULL>  <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE2 F_TIME     <NULL>  <NULL>  ISO     <NULL>   <NULL>  <NULL>   <NULL>
TESTFILE2 F_NBR1     <NULL>  <NULL>  <NULL>  <NULL>   M       *        <NULL>
TESTFILE2 F_NBR2     <NULL>  <NULL>  <NULL>  <NULL>   <NULL>  <NULL>    @   

TESTTABLE COLUM00001 ISO     <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>
TESTTABLE COLUM00002 <NULL>  <NULL>  ISO     <NULL>   <NULL>  <NULL>   <NULL>
TESTTABLE COLUM00003 <NULL>  <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>
TESTTABLE COLUM00004 <NULL>  <NULL>  <NULL>  <NULL>   <NULL>  <NULL>   <NULL>

In these results I can clearly see the different date and time formats I used, by not giving a date format in the DDS ISO format is the default.

I can also see the Edit codes and Edit words I used for the fields F_NBR1 and F_FLD2 in the files TESTFILE1 and TESTFILE2.

Last statement shows the reference file, format, file, and library that were used to define the fields in the tables. But before I show that statement I build a second DDL Table that was based upon the existing DDL Table TESTTABLE:

01  CREATE TABLE MYLIB.TESTTABLE1
02  AS (SELECT * FROM TESTTABLE)
03  DEFINITION ONLY

My statement to see all the reference field information is:

SELECT SYSTEM_TABLE_NAME AS "File",
       SYSTEM_COLUMN_NAME AS "Sys name",
       REFERENCE_LIBRARY AS "Ref Lib*",
       REFERENCE_FILE AS "Ref File*",
       REFERENCE_FORMAT AS "Ref Fmt*",
       REFERENCE_FIELD AS "Ref Fld*"
FROM QSYS2.SYSCOLUMNS2 
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' 
AND SYSTEM_TABLE_NAME LIKE 'TEST%' 
ORDER BY 1,ORDINAL_POSITION

I do not feel a need to describe that the columns in these results show as the column names to an adequate job.

The results include the new Table too:

File       Sys name   Ref Lib* Ref File* Ref Fmt*  Ref Fld*
---------- ---------- -------- --------- --------- --------
TESTFILE   F_DATE     <NULL>   <NULL>    <NULL>    <NULL>
TESTFILE   F_TIME     <NULL>   <NULL>    <NULL>    <NULL>
TESTFILE   F_NBR1     <NULL>   <NULL>    <NULL>    <NULL>
TESTFILE   F_NBR2     <NULL>   <NULL>    <NULL>    <NULL>

TESTFILE1  F_DATE     MYLIB    TESTFILE  TESTFILER F_DATE
TESTFILE1  F_TIME     MYLIB    TESTFILE  TESTFILER F_TIME
TESTFILE1  F_NBR1     MYLIB    TESTFILE  TESTFILER F_NBR1
TESTFILE1  F_NBR2     MYLIB    TESTFILE  TESTFILER F_NBR2

TESTFILE2  F_DATE     MYLIB    TESTFILE  TESTFILER F_DATE
TESTFILE2  F_TIME     MYLIB    TESTFILE  TESTFILER F_TIME
TESTFILE2  F_NBR1     MYLIB    TESTFILE  TESTFILER F_NBR1
TESTFILE2  F_NBR2     MYLIB    TESTFILE  TESTFILER F_NBR2

TESTTABLE  COLUM00001 <NULL>   <NULL>    <NULL>    <NULL>
TESTTABLE  COLUM00002 <NULL>   <NULL>    <NULL>    <NULL>
TESTTABLE  COLUM00003 <NULL>   <NULL>    <NULL>    <NULL>
TESTTABLE  COLUM00004 <NULL>   <NULL>    <NULL>    <NULL>

TESTTABLE1 COLUM00001 MYLIB    TESTTABLE TESTTABLE COLUM00001
TESTTABLE1 COLUM00002 MYLIB    TESTTABLE TESTTABLE COLUM00002
TESTTABLE1 COLUM00003 MYLIB    TESTTABLE TESTTABLE COLUM00003
TESTTABLE1 COLUM00004 MYLIB    TESTTABLE TESTTABLE COLUM00004

The results for the DDS tables were as I expected. I am really pleased that the results for TESTTABLE2 include the DDL columns that were used to create it.

Another set of good additions to an already useful SQL View.

The SQL View SYSCOLUMNS2_SESSION is identical to SYSCOLUMNS2, including these new fields. But it will only show results for DDL Tables and Views.

For example I copied the following to the library QTEMP:

  • DDS file TESTFILE1
  • DDL table TESTTABLE
                         Work with Objects Using PDM
Library . . . . .   QTEMP          Position to . . .
                                    Position to type 

Opt  Object      Type        Attribute   Text
     TESTFILE1   *FILE       PF-DTA
     TESTTABLE   *FILE       PF-DTA

When I run the following SQL statement:

01  SELECT DISTINCT SYSTEM_TABLE_NAME
02  FROM QSYS2.SYSCOLUMNS2_SESSION

I only see SQL Tables in the results:

SYSTEM_TABLE_NAME
-----------------
QSQ_S00001
TESTTABLE

Personally I think this is an oversight by IBM.

 

You can learn more about the SYSCOLUMNS2 SQL View from the IBM website here.

 

This article was written for IBM i 7.4 TR5 and 7.3 TR11.

No comments:

Post a Comment

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.