New columns added to SYSCOLUMNS2 SQL View

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.