Almost everything you wanted to know about files and tables

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:

• SYSTABLES
• SYSTABLESTAT

 

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