Getting field definitions using SYSCOLUMNS

In several previous posts where I have used the DSPFFD command, see here and here, or the QUSLFLD API, see here, to retrieve the Column Heading of fields in a file someone has commented that I should retrieve the column headings using the SYSCOLUMNS file. In the scenarios described in those post using SYSCOLUMNS would not have worked as it only contains data for files not in QTEMP.

SYSCOLUMNS resides in the library QSYS2, and is a SQL view built over the physical file QABDIFLD. It contains the field/column information for all the files and tables in every library on your IBM i, except those in QTEMP. I am not going to list all the columns in SYSCOLUMNS as the IBM documentation does a good job doing it here.

I am going to show two scenarios how I could use SYSCOLUMNS:

1. List all of the fields in a file.
2. List all the occurrences of a field name in all the files in a library.

Before I start the scenarios I want to explain what data I am going to extract from SYSCOLUMNS. The view contains a whole lot of information that I do not need, therefore, I will only be extracting the following columns (fields):

Column name	Column description
SYSTEM_COLUMN_NAME	Field/column name.
SYSTEM_TABLE_NAME	File/table name. This does include logical files and SQL views.
SYSTEM_TABLE_SCHEMA	Library name.
DATA_TYPE	The field type. My most commonly encountered data types are: CHAR – Alphanumeric DECIMAL – Packed numeric NUMERIC – Signed numeric DATE – Date TIME – Time TIMESTMP – Timestamp The other values for this field can be found here.
LENGTH	Length of field, or in the case of numeric fields its precision.
NUMERIC_SCALE	For numeric field the number of decimal places.
IS_NULLABLE	Can the field contain null?
CCSID	CCSID value of the field.
COLUMN_TEXT	The field/column text.
COLUMN_HEADING	The field/column headings combined into one. First column heading is in positions 1-20, Second column heading 21-40, Third column heading 41-60.

In my experience these columns will cope with most of your needs.

In these scenarios all of my SQL is embedded in a SQLRPGLE program.

 

List all of the fields in a file

This is, perhaps, the simplest use of the SYSCOLUMNS. All I want is a list of all the fields in my file TESTFILE, which is in the library MYLIB, in a file in QTEMP that I am going to call WORKFILE (I know I am not going to get any points for exciting file and library names). I am going to use the SQL CREATE TABLE function I described in the earlier post Creating a SQL table "on the fly". Let me show the code below and then I will describe what I am doing.

01 exec sql CREATE TABLE QTEMP/WORKFILE AS 02 (SELECT SYSTEM_COLUMN_NAME AS FIELD, 03 SYSTEM_TABLE_NAME AS FILE, 04 SYSTEM_TABLE_SCHEMA AS LIBRARY, 05 DATA_TYPE,LENGTH, 06 NUMERIC_SCALE AS DECIMAL_PLACES, 07 IS_NULLABLE,CCSID, 08 CAST(COLUMN_TEXT AS CHAR(50) CCSID 37) 09 AS COL_TEXT, 10 CAST(COLUMN_HEADING AS CHAR(60) CCSID 37) 11 AS COL_HDG 12 FROM SYSCOLUMNS 13 WHERE SYSTEM_TABLE_NAME = 'TESTFILE' 14 AND SYSTEM_TABLE_SCHEMA = 'MYLIB') 15 WITH DATA ;

On the first line I state the name of the table I am going to create. In this example I have hard-coded the file to be built in QTEMP. The SQL SELECT that defines the table that follows must be contained with parentheses (brackets to non-US folks).

The SELECT statement is pretty straight forward, except that I want to rename some of the columns to give them names, that in my opinion, that are easier to understand:

SYSCOLUMNS name	New name
SYSTEM_COLUMN_NAME	FIELD
SYSTEM_TABLE_NAME	FILE
SYSTEM_TABLE_SCHEMA	LIBRARY
NUMERIC_SCALE	DECIMAL_PLACES

On my IBM iThe COLUMN_TEXT and COLUMN_HEADING columns contain all kinds of strange characters and I need to convert them into something that is understandable. I do this by using the CAST, see lines 8 and 10, which converts those columns, using CCSID 37, into values I define as new columns COL_TEXT and COL_HDG. You may need to convert these fields from a different CCSID, check the column CCSID for the value you would need to use.

On line 12 I have defined that this data comes from SYSCOLUMNS.

On lines 13 and 14 I have defined the data I want to select:   SYSTEM_TABLE_NAME = 'TESTFILE' and SYSTEM_TABLE_SCHEMA = 'MYLIB'.

The on line 15 I have used WITH DATA this means that the data will be extracted from SYSCOLUMNS and inserted into QTEMP/WORKFILE.

Now I have my file where I can view the information for my TESTFILE:

FIELD FILE LIBRARY DATA_TYPE LENGTH DECIMAL_PLACES IS_NULLABLE CCSID COL_TEXT FLD001 TESTFILE MYLIB CHAR 1 - N 37 Alpha field 1 FLD002 TESTFILE MYLIB DECIMAL 3 0 N - Packed numeric FLD003 TESTFILE MYLIB DATE 4 - N 37 Date field FLD004 TESTFILE MYLIB TIME 3 - N 37 Time field FLD005 TESTFILE MYLIB TIMESTMP 10 - N 37 Timestamp field FLD006 TESTFILE MYLIB NUMERIC 5 0 N - Signed field FLD007 TESTFILE MYLIB CHAR 1 - N 37 REFFLD of FLD001 ******** End of data ********

 

List all the occurrences of a field name in all the files in a library.

In this scenario I have been charged with increasing the alphanumeric 'Ship via' code field from two to three long. How can I see what files contain the 'Ship via' field?

Unfortunately when this application was built it was done so using what I call the RPGIII field name standard, all of the field names are six long, and start with two characters that identify the file. Fortunately the fields are all called: XXVIA, XXVIAC, or XXSVIA. There are some files that were developed later that have the same name for the field: SHIPVIA.

I can use a similar CREATE TABLE as my previous example, this time with a wildcard for the SYSTEM_COLUMN_NAME, see line 13 below.

01 exec sql CREATE TABLE QTEMP/WORKFILE AS 02 (SELECT SYSTEM_COLUMN_NAME AS FIELD, 03 SYSTEM_TABLE_NAME AS FILE, 04 SYSTEM_TABLE_SCHEMA AS LIBRARY, 05 DATA_TYPE,LENGTH, 06 NUMERIC_SCALE AS DECIMAL_PLACES, 07 IS_NULLABLE,CCSID, 08 CAST(COLUMN_TEXT AS CHAR(50) CCSID 37) 09 AS COL_TEXT, 10 CAST(COLUMN_HEADING AS CHAR(60) CCSID 37) 11 AS COL_HDG 12 FROM SYSCOLUMNS 13 WHERE SYSTEM_COLUMN_NAME LIKE '%VIA%' 14 AND SYSTEM_TABLE_SCHEMA = 'MYLIB') 15 WITH DATA ;

Now I can see what files contain a column with 'VIA' somewhere in its name:

FIELD FILE LIBRARY DATA_TYPE LENGTH A1VIAC SHP001L1 MYLIB CHAR 2 A1VIAC SHP001P MYLIB CHAR 2 A3VIAC SHP002P MYLIB CHAR 2 D1VIA INV001L1 MYLIB CHAR 2 D1VIA INV001P MYLIB CHAR 2 D7SVIA INV007P MYLIB CHAR 2 SHIPVIA INV101P MYLIB CHAR 2

Notice how logical files and SQL views will be included.

 

These are just two examples. I am sure that you can find many more ways of using SYSCOLUMNS to improve your productivity.

 

More information about these keywords can be found on the IBM website:

• SYSCOLUMNS description
• CREATE TABLE SQL function
• CAST SQL specification

 

This article was written for IBM i 7.2, and it should work with earlier releases too.