Tuesday, December 23, 2014

Getting field definitions using SYSCOLUMNS

Use SYSCOLUMNS to get list of fields in file

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_TABLE_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:

 

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

8 comments:

  1. Your better off using the List Fields API (QUSLFLD) to get at this info. This will work with files in QTEMP, plus you will not have the authority issues you will need to deal with when using file SYSCOLUMNS.

    ReplyDelete
    Replies
    1. I did mention the advantages of using QUSLFLD in an earlier post here.

      There is still a place for using SYSCOLUMNS despite its shortcomings as it an easy way to look for fields/columns across multiple files/tables. I trust it more than I do HAWKEYE.

      As for the authority issue. Only IT staff should be using the objects in QSYS2. If admins do not feel comfortable granting authority to their staff to use the objects in QSYS2 do what one site I know did. They made a duplicate SYSCOLUMNS in QGPL and gave all their IT staff *USE rights to it there.

      Delete
    2. The system Tables provide a lot of useful info, but I agree beyond administrative tasks or for use by IT staff they shouldn't be used. I see a lot of posts by people mentioning using these as if they as a quick way to get at this data, but don't realize that there can be some serous limitations and very real security implications from using them.

      Delete
  2. Old but Gold!

    ReplyDelete
  3. Awesome article!!!

    ReplyDelete
  4. Awesome article! I can imagine all I will do with this!!!

    ReplyDelete
  5. DSPFFD FILE(WM*) OUTPUT(*OUTFILE) OUTFILE(QTEMP/MYFILE)

    What is wrong with the above command and then

    select * from myfile SQL statement

    Gives you so much more info re the file

    I have also written an RPGLE program to recreate the source from the info gathered here

    ReplyDelete
    Replies
    1. There is nothing wrong with the DSPFFD command.

      I only gave a subset of the columns available in SYSCOLUMNS, there are a lot more.

      It was a good idea to have created a command to recreate the source for a file. Does it work just for PF? Or for LF, DSPF, & PRTF too?

      Delete

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.