Wednesday, December 16, 2015

Almost everything you wanted to know about files and tables

systables systablestat dspobjd

When looking through my previous posts I realized I had not written about one of what I consider to be one of the most useful SQL Views, SYSTABLES. I have come to use this View any time I need to retrieve information about files or tables in my IBM i environment, I say that as it does not include the files in the IFS. It is not a new View, it has been around for many years and releases. I find that by using SYSTABLES I can get information a lot quicker than I can using the Display Object Description command, DSPOBJD. When I join SYSTABLES with the View SYSTABLESTAT I have found that I rarely use DSPBOJD any more.

SYSTABLES is found in the library QSYS2, and it contains a row for each table, physical file, view, alias, and logical file. I am not going to list all of its columns as IBM does a good job of it here. I am going to mainly use the short names in my examples to save space in the code below. The columns I have most useful are:

  • SYS_DNAME - library
  • SYS_TNAME - table/file
  • FILETYPE - is it source or data file?
  • TYPE - what type of table/file is it?
  • COLCOUNT - number of columns in the table
  • RECLENGTH - maximum length of any record
  • SYSTABLE - is this an IBM system table?
  • SELECTOMIT - is this a select/omit logical?
  • INSERTABLE - is INSERT allowed on the table?

When I join SYSTABLES to the view SYSTABLESTAT, also found in QSYS2, I can get to the following information:

  • NUMBER_ROWS - number of rows/records
  • DELETED - number of deleted rows/records
  • DATA_SIZE - total size of table/files (for all partitions/members)
  • LASTUSED - Last used time stamp

While SYSTABLES contains information for all file types, SYSTABLESTAT only contains information for tables/physical files. Therefore, I must be careful how I join these two Views in a SELECT statement.

I have written about using SYSTABLESTAT to identify the number of deleted records in file in the post Discovering the number of deleted records in a file.

Let me start with a simple example retrieving the information for a physical file from SYSTABLES

SELECT SYS_DNAME,SYS_TNAME,FILETYPE,TYPE,COLCOUNT,
       RECLENGTH,SYSTABLE,SELECTOMIT,INSERTABLE
  FROM QSYS2.SYSTABLES
 WHERE SYS_DNAME = 'LIB1'
   AND SYS_TNAME = 'MFGMST'

When executed the results look like:


SYSTEM_TABLE_SCHEMA  SYS_TNAME  FILETYPE  TYPE  COLCOUNT  RECLENGTH  SYSTABLE
    LIB1              MFGMST        D       P         99        500     N

SELECTOMIT  INSERTABLE
    N          YES

The 'D' in FILETYPE denotes that this is a data file

Column Description
SYSTEM_TABLE_SCHEMA Library
SYS_TNAME Table/file name
FILETYPE D = Data file
TYPE P = Physical file
COLCOUNT 99 columns/fields
RECLENGTH The maximum record length is 500
SYSTABLE N = Not a system table
SELECTOMIT N = Not a select/omit logical file
INSERTABLE YES = I can insert rows/records into this file

I can expand the SELECT statement to include SYSTABLESTAT, as MFGMST is a physical file I can just use a JOIN between the two views.

SELECT A.SYS_DNAME,A.SYS_TNAME,A.FILETYPE,A.TYPE,A.COLCOUNT,
       A.RECLENGTH,A.SYSTABLE,A.SELECTOMIT,A.INSERTABLE,
       B.NUMBER_ROWS,B.DELETED,B.DATA_SIZE,B.LASTUSED
  FROM SYSTABLES A JOIN SYSTABLESTAT B
    ON A.SYS_DNAME = B.SYS_DNAME AND A.SYS_TNAME = B.SYS_TNAME
 WHERE A.SYS_DNAME = 'LIB1' AND A.SYS_TNAME = 'MFGMST'

I now have the four extra columns from SYSTABLESTAT:


SYSTEM_TABLE_SCHEMA  SYS_TNAME  FILETYPE  TYPE  COLCOUNT  RECLENGTH  SYSTABLE
    LIB1              MFGMST        D       P         99        500     N

SELECTOMIT  INSERTABLE  NUMBER_ROWS  DELETED    DATA_SIZE
    N          YES          120,584  77,701   102,895,616

LASTUSED
2015-11-17-00.00.00.000000

Some of the values returned are a bit cryptic, therefore, I have a View I built to join SYSTABLES and SYSTABLESTAT:

01  CREATE VIEW MYLIB/TABSTAT(
02    LIBRARY,
03    TABLE,
04    FILE_TYPE,
05    TABLE_TYPE,
06    SYSTEM_TABLE,
07    SELECT_OMIT,
08    COLUMN_COUNT,
09    RECORD_LENGTH,
10    INSERTABLE,
11    NUMBER_ROWS,
12    NUMBER_DELETED,
13    DATA_SIZE,
14    LAST_USED)
15  AS SELECT A.SYS_DNAME,A.SYS_TNAME,
16    CASE WHEN A.FILETYPE = 'D' THEN 'DATA'
17         WHEN A.FILETYPE = 'S' THEN 'SOURCE'
18    END,
19    CASE WHEN A.TYPE = 'A' THEN 'ALIAS'
20         WHEN A.TYPE = 'L' THEN 'LOGICAL FILE'
21         WHEN A.TYPE = 'M' THEN 'MATERIALIZED QUERY TABLE'
22         WHEN A.TYPE = 'P' THEN 'PHYSICAL FILE'
23         WHEN A.TYPE = 'T' THEN 'TABLE'
24         WHEN A.TYPE = 'V' THEN 'VIEW'
25    END,
26    CASE WHEN A.SYSTABLE = 'N' THEN 'NOT SYSTEM TABLE'
27         WHEN A.SYSTABLE = 'Y' THEN 'SYSTEM TABLE'
28    END,                                 
29    CASE WHEN A.SELECTOMIT = 'D' THEN 'DYNAMIC SELECT/OMIT LF'
30         WHEN A.SELECTOMIT = 'N' THEN 'NOT SELECT/OMIT LF'
31         WHEN A.SELECTOMIT = 'Y' THEN 'SELECT/OMIT LF'
32    END,
33    A.COLCOUNT,A.RECLENGTH,A.INSERTABLE,
34    B.NUMBER_ROWS,B.DELETED,B.DATA_SIZE,B.LASTUSED
35    FROM SYSTABLES A LEFT OUTER JOIN SYSTABLESTAT B
36      ON A.SYS_DNAME = B.SYS_DNAME
37     AND A.SYS_TNAME = B.SYS_TNAME ;

Lines 1 – 14: Defines the name of the view, line 1, and what the columns are going to be called.

Lines 15 – 37: The SELECT statement that maps the columns in the SYSTABLES and SYSTABLESTAT Views to this new View.

I am using several CASE statements in the SELECT to map the values from the SYSTABLES to a more meaningful description. If you are unfamiliar with the CASE statement you should read Creating derived columns in SQL View.

Lines 16 – 18: With this CASE statement rather than display 'D' and 'S' for the File Type my new View will be display 'DATA' or 'SOURCE'.

Lines 19 – 25: This CASE statement I am going to give a description for the Type of the file, rather than the single character.

Lines 26 – 28: Another CASE statement to replace the System Table 'Y' or 'N' with a description.

Lines 29 – 32: in this final CASE statement I am giving the SELECTOMIT column a better description.

Line 35: I mentioned that I need to be careful of the type of JOIN I use to join SYSTABLES and SYSTABLESTAT. As SYSTABLESTAT only contains information of Tables and Physical files if I had only used a JOIN then only when a match is found in both Tables will it be displayed, only Tables and Physical files. Using LEFT OUTER JOIN all rows from SYSTABLES are included and any SYSTABLESTAT rows that match. For the unmatched, non-Table and Physical files, the values from SYSTABLESTAT will be null.

For the example below I have selected my source file:

SELECT * FROM TABSTAT
 WHERE LIBRARY = 'MYLIB'
   AND TABLE = 'DEVSRC'

In the results the File type shows that this is a source rather than a data file.


LIBRARY  TABLE   FILE_TYPE  TABLE_TYPE     SYSTEM_TABLE      SELECT_OMIT
MYLIB    DEVSRC   SOURCE    PHYSICAL FILE  NOT SYSTEM TABLE  NOT SELECT/OMIT LF

COLUMN_COUNT  RECORD_LENGTH  INSERTABLE  NUMBER_ROWS  NUMBER_DELETED  DATA_SIZE
           3            112   YES            3,437               0      684,032

LAST_USED
2015-12-09-00.00.00.000000

Now let me select a few of the rows from my View with a wildcard selection for the Table name:

SELECT LIBRARY,TABLE,FILE_TYPE,TABLE_TYPE,SYSTEM_TABLE,SELECT_OMIT
  FROM MYLIB/TABSTAT
 WHERE LIBRARY = 'LIB1'
   AND TABLE LIKE 'MFGMST%'

I can see various types of files and a View:


LIBRARY  TABLE     FILE_TYPE  TABLE_TYPE     SYSTEM_TABLE       SELECT_OMIT
LIB1     MGFMST      DATA     PHYSICAL FILE  NOT SYSTEM TABLE   NOT SELECT/OMIT LF
LIB1     MFGMSTL0    DATA     LOGICAL FILE   NOT SYSTEM TABLE   NOT SELECT/OMIT LF
LIB1     MFGMSTV0    DATA     VIEW           NOT SYSTEM TABLE   NOT SELECT/OMIT LF
LIB1     MFGMSTL3    DATA     LOGICAL FILE   NOT SYSTEM TABLE   SELECT/OMIT LF

With this functionality in SYSTABLES the only time I need to use DSPOBJD is to find which source a file was compiled from.

 

You can learn more about this from the IBM website:

 

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

3 comments:

  1. I totally agree, this is much easier than using DSPOBJD command and navigating thru the screen. Very useful tool and thanks for sharing.

    ReplyDelete
  2. This can come in handy too in stead of DSPFFD:
    select dbifld dbifld
    , cast( dbifln as dec(7, 0)) dbifln
    , dbiitp dbiitp
    , cast(coalesce(dbinsc, 99) as dec(2, 0)) dbinsc
    , coalesce(trim(dbitxt), trim(dbihdg)) dbitxt
    from qsys/qadbifld
    where dbilib = 'MYLIB'
    and dbifil = 'MYFILE'
    and dbifmt = 'MYRECFMT'

    Regards
    Jan

    ReplyDelete
  3. You should include Table_Type P & T:
    Table_Type P = Physical file
    Table Type T = (SQL?) Table

    ReplyDelete

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.