DB2/400 SQL Meta data tables

(plus some other interesting tables and functions)

The IBM i maintains a set of Catalog Tables that describe the metadata in each relational database. This includes information about tables, data types, parameters, procedures, packages, views, indexes, aliases, sequences, variables, constraints, triggers etc... The objects are of object type SQL View and can be found in libraries SYSIBM, QSYS and QSYS2. There appear to be several generations of of these objects with overlapping information.

List tables in library DEV using QSYS2.TABLES or SYSIBM.TABLES

This provides a quick way to list all tables on a system or in specific library.

SELECT 
    SUBSTR(TABLE00001, 1, 30) as Collection, 
    SUBSTR(DBNAME, 1, 30) as DataBase_Name, 
    SUBSTR(NAME, 1, 30) as Long_Table_Name, 
    TABLE_TYPE  
FROM QSYS2.TABLES WHERE DBNAME = 'DEV' 

List all fields in tables with SYSIBM.SQLCOLUMNS, using SYSTABLES to get the short table name.

Native IBM i field and table names have a 10 character limit. In order to be compatible with other SQL vendors, IBM added a system of long SQL alias names. The 10 character names are used more commonly on IBM i systems. Using the Catalog Tables is a simple way of listing both long and short names. More on DB2 UDB for iSeries Long and Short Identifiers by Kent Milligan, (2023-12-09: Looks like IBM blew away this article.)

SELECT
    SUBSTR(SQLCOLUMNS.TABLE_SCHEM, 1, 10)  as Library,   
    SUBSTR(SQLCOLUMNS.TABLE_NAME, 1, 50)   as Long_Table_Name,
    SYSTABLES.SYSTEM_TABLE_NAME            as Short_Table_Name,
    SUBSTR(SQLCOLUMNS.NAME, 1, 35)         as Long_Field_Name,    
    SQLCOLUMNS.SYS_CNAME                   as Short_Field_Name,
    SQLCOLUMNS.COLUMN_TEXT,
    SQLCOLUMNS.ORDIN00001                  as SEQUENCE,
    SUBSTR(SQLCOLUMNS.TYPE_NAME, 1, 20)    as Data_Type,
    SQLCOLUMNS.COLUMN_SIZE  ,
    SQLCOLUMNS.DECIMAL_DIGITS ,
    SQLCOLUMNS.NULLABLE,   SQLCOLUMNS.IS_NU00001
FROM QSYS2.SYSTABLES as SYSTABLES JOIN SYSIBM.SQLCOLUMNS as SQLCOLUMNS
    ON(SYSTABLES.TABLE_NAME  = SQLCOLUMNS.TABLE_NAME
    AND SYSTABLES.TABLE_SCHEMA  = SQLCOLUMNS.TABLE_SCHEM )
WHERE SQLCOLUMNS.TABLE_SCHEM ='MYLIB'

Show Information about referential constraints using QSYS2.SYSREFCST

SELECT 
     SUBSTR(CONSTRAINT_SCHEMA, 1, 20) as Constraint_Scheme,
     SUBSTR(CONSTRAINT_NAME, 1, 45) as Constraint_Name,
     SUBSTR(UNIQUE_CONSTRAINT_SCHEMA, 1, 20) as Unique_Constraint_Name,  
	 UPDATE_RULE, DELETE_RULE, COLUMN_COUNT    
	 FROM QSYS2.SYSREFCST
			

Information about constraints columns from QSYS2.SYSCSTCOL

SELECT 
    SUBSTR(CONSTRAINT_SCHEMA, 1, 20) as CONSTR_SCHEMA,                   
    SUBSTR(CONSTRAINT_NAME, 1, 45) as CONSTRAINT_NAME,                          
    SUBSTR(TABLE_SCHEMA, 1, 30) as TABLE_SCHEMA, 
    SUBSTR(COLUMN_NAME, 1, 30) as COLUMN_NAME,                   
    SUBSTR(TABLE_NAME, 1, 30) as TABLE_NAME,     
    SYSTEM_TABLE_NAME, 
    SYSTEM_TABLE_SCHEMA,               
    SYSTEM_CONSTRAINT_SCHEMA 
FROM QSYS2.SYSCSTCOL

Show Information all Tables and Fields, indicating any applicable referential constraints (QSYS2.SYSCSTCOL).

SELECT 
    SUBSTR(SQLCOLUMNS.TABLE_SCHEM, 1, 10)  as Library,    
    SUBSTR(SQLCOLUMNS.TABLE_NAME, 1, 50)   as Long_Table_Name,
    SYSTABLES.SYSTEM_TABLE_NAME            as Short_Table_Name,
    SUBSTR(SQLCOLUMNS.NAME, 1, 35)         as Long_Field_Name,     
    SQLCOLUMNS.SYS_CNAME                   as Short_Field_Name, 
    SUBSTR(SQLCOLUMNS.TYPE_NAME, 1, 20)    as Data_Type,
    SUBSTR(CONSTRAINT_NAME, 1, 45)         as CONSTRAINT_NAME
	FROM QSYS2.SYSTABLES as SYSTABLES 
JOIN SYSIBM.SQLCOLUMNS as SQLCOLUMNS
	ON(SYSTABLES.TABLE_NAME  = SQLCOLUMNS.TABLE_NAME
	  and SYSTABLES.TABLE_SCHEMA  = SQLCOLUMNS.TABLE_SCHEM )	
	LEFT OUTER JOIN QSYS2.SYSCSTCOL as SYSCSTCOL
	ON (SYSCSTCOL.COLUMN_NAME = SQLCOLUMNS.NAME and
	    SYSCSTCOL.TABLE_NAME  = SQLCOLUMNS.TABLE_NAME and
	    SYSCSTCOL.CONSTRAINT_SCHEMA  = SQLCOLUMNS.TABLE_SCHEM)	 
WHERE SQLCOLUMNS.TABLE_SCHEM ='MYLIB'	

The QSYS2.SYSCST allows us to add some information about constraint types by field.

Here we are using tables (QSYS2.SYSTABLES), Columns (QSYS2.SYSCSTCOL) and Constraints (QSYS2.SYSCST). There may be some duplicate records if field has multiple constraints

	
  SELECT
    SUBSTR(SQLCOLUMNS.TABLE_SCHEM, 1, 10)  as Library,  
    SUBSTR(SQLCOLUMNS.TABLE_NAME, 1, 50)   as Long_Table_Name,
    SYSTABLES.SYSTEM_TABLE_NAME            as Short_Table_Name,
    SQLCOLUMNS.ORDIN00001                  as Seq_No , 
    SUBSTR(SQLCOLUMNS.NAME, 1, 35)         as Long_Field_Name,   
    SQLCOLUMNS.SYS_CNAME                   as Short_Field_Name,
    SUBSTR(SQLCOLUMNS.TYPE_NAME, 1, 20) || ' / ' ||
    SQLCOLUMNS.COLUMN_SIZE   || ' / ' ||
    SQLCOLUMNS.DECIMAL_DIGITS  AS TYPE_SIZE_DIGITS,   
    SUBSTR(SYSCSTCOL.CONSTRAINT_NAME, 1, 45) as CONSTRAINT_NAME,
    SYSCST.TYPE, SYSCST.COLCOUNT
FROM QSYS2.SYSTABLES as SYSTABLES
    JOIN SYSIBM.SQLCOLUMNS as SQLCOLUMNS
    ON(SYSTABLES.TABLE_NAME  = SQLCOLUMNS.TABLE_NAME
    and SYSTABLES.TABLE_SCHEMA  = SQLCOLUMNS.TABLE_SCHEM )
LEFT OUTER JOIN QSYS2.SYSCSTCOL as SYSCSTCOL
    ON (SYSCSTCOL.COLUMN_NAME = SQLCOLUMNS.NAME and
    SYSCSTCOL.TABLE_NAME  = SQLCOLUMNS.TABLE_NAME and
    SYSCSTCOL.CONSTRAINT_SCHEMA  = SQLCOLUMNS.TABLE_SCHEM)
LEFT OUTER JOIN QSYS2.SYSCST  as SYSCST
    ON (
    SYSCSTCOL.TABLE_NAME  = SYSCST.TBNAME  and
    SYSCSTCOL.CONSTRAINT_SCHEMA  = SYSCST.CDBNAME and
    SYSCSTCOL.CONSTRAINT_NAME = SYSCST.RELNAME)
WHERE SQLCOLUMNS.TABLE_SCHEM ='MY_LIB' and   
    SYSTABLES.SYSTEM_TABLE_NAME = 'MY_TABLE' 
ORDER BY
    SQLCOLUMNS.TABLE_SCHEM,
    SQLCOLUMNS.TABLE_NAME,
    SQLCOLUMNS.ORDIN00001

Information about check constraints (QSYS2.SYSCHKCST)

SELECT  
     SUBSTR(CONSTRAINT_SCHEMA,1,10) AS SCHEMA,                     
     SUBSTR(TABLE_NAME,1,30) as TABLE_NAME, 
     SUBSTR(CONSTRAINT_NAME,1,30) AS CONSTRAINT_NAME, 
     CONSTRAINT_TYPE, IS_DEFERRABLE, INITIALLY_DEFERRED,           
     SYSTEM_TABLE_NAME, SYSTEM_TABLE_SCHEMA, CONSTRAINT_KEYS,               
     IASP_NUMBER, CONSTRAINT_STATE, ENABLED, CHECK_PENDING,                 
     CONSTRAINT_TEXT, LONG_COMMENT, SYSTEM_CONSTRAINT_SCHEMA 
 FROM QSYS2.SYSCST    
 WHERE CONSTRAINT_SCHEMA = 'ITG'                                     
	 

Table/Row statistics (QSYS2.SYSTABLESTAT)

This table was designed for tracking table/row statistics across partitions, but has some useful information for everyone

SELECT
    SYS_DNAME As SHORT_DATABASE ,
    substr(TABSCHEMA,1,30) AS SCHEMA, 
    SYS_TNAME AS TABLE_NAME ,
    substr(TABNAME,1,30) , 
    CARD as ROW_COUNT,
    DELETED as DELETED_ROW_COUNT,
    OPENS as OPENS,
    INSERTS as INSERTS,
    UPDATES as UPDATES
FROM QSYS2.SYSTABLESTAT                            
WHERE TABSCHEMA = 'QS36F'                          
                                              
where:
CARD	= Number of valid rows in all partitions or members of the table.
DELETED	= Number of deleted rows in all partitions or members of the table.
OPENS	= Number of full opens of all partitions or members of the table since the last IPL.
INSERTS	= Number of insert operations of all partitions or members of the table since the last IPL.
UPDATES	= Number of update operations of all partitions or members of the table since the last IPL.
DELETES	= Number of delete operations of all partitions or members of the table since the last IPL.

The HISTORY_LOG_INFO table function

The HISTORY_LOG_INFO table function returns one row for each message in the history log based on the timestamp range specified. It returns information similar to what is returned by the Display Log (DSPLOG) CL command. Added in OS/400 release 7.2

SELECT MESSAGE_ID, MESSAGE_TYPE, SEVERITY, MESSAGE_TIMESTAMP AS TIMESTAMP,                                  
  FROM_USER, FROM_JOB, CAST(MESSAGE_TEXT AS CHAR(200) CCSID 37) AS MESSAGE_TEXT_L1,     
  CAST(MESSAGE_SECOND_LEVEL_TEXT AS CHAR(1000) CCSID 37) AS        
  MESSAGE_TEXT_L1 
FROM TABLE(QSYS2/HISTORY_LOG_INFO())

SELECT MESSAGE_ID, MESSAGE_TYPE, SEVERITY, MESSAGE_TIMESTAMP AS TIMESTAMP,                                  
  FROM_USER, FROM_JOB, CAST(MESSAGE_TEXT AS CHAR(200) CCSID 37) AS MESSAGE_TEXT_L1,     
  CAST(MESSAGE_SECOND_LEVEL_TEXT AS CHAR(1000) CCSID 37) AS        
  MESSAGE_TEXT_L1 
FROM TABLE(QSYS2/HISTORY_LOG_INFO(CURRENT TIMESTAMP - 1 DAY))

SELECT MESSAGE_ID, MESSAGE_TYPE, SEVERITY, MESSAGE_TIMESTAMP AS TIMESTAMP,                                  
  FROM_USER, FROM_JOB, CAST(MESSAGE_TEXT AS CHAR(200) CCSID 37) AS MESSAGE_TEXT_L1,     
  CAST(MESSAGE_SECOND_LEVEL_TEXT AS CHAR(1000) CCSID 37) AS        
  MESSAGE_TEXT_L1 
FROM TABLE(QSYS2/HISTORY_LOG_INFO('2023-12-01-12.00.00.000000', '2023-12-10-12.00.00.000000'))                                      
WHERE SEVERITY >= 30 
ORDER BY MESSAGE_TIMESTAMP DESC

See similar tables, functions and views:

SYSCATALOGS                 Information about relational databases
QSYS2.SYSTABLES             Information about relational tables including Short/Long Table names
TABLES                      Information about relational tables (SQL long names only)
QSYS2.COLUMNS               Information about columns (cam be very slow, select by DBNAME for better performance)
QSYS2.SYSCOLUMNS  			
SYSIBM.SQLCOLUMNS 

SYSCOLUMNSTAT               Information about column statistics with some interesting usage and deletion statistics
SYSINDEXES                  Information about indexes
SYSPACKAGE                  Information about packages in the SQL schema. Packages are control-structure database objects 
                            that contain executable forms of SQL statements
SYSSEQUENCES                Information about sequences. Information about columns that use "GENERATED AS IDENTITY" clause
SYSTABLEDEP                 Information about materialized query table dependencies
SYSTABLEINDEXSTAT           Information about table index statistics
SYSTABLESTAT                Information about table statistics
SYSTYPES                    Information about built-in data types and distinct types
SYSJARCONTENTS              Information about jars for Java™ routines.
SYSJAROBJECTS               Information about jars for Java routines.
SYSPARMS                    Information about routine parameters

* Views and Indexes
SYSVIEWS                    Information about definition of a view
SYSVIEWDEP                  Information about view dependencies on tables
SYSINDEXES                  List of all Indexes
SYSINDEXSTAT                Statistics of the Indexes

* Triggers
SYSTRIGCOL                  Information about columns used in a trigger
SYSTRIGDEP                  Information about objects used in a trigger
SYSTRIGGERS                 Information about triggers
SYSTRIGUPD                  Information about columns in the WHEN clause of a trigger

* Constraints
SYSCST                      Information about all constraints
SYSCHKCST                   Information about check constraints
SYSCSTCOL                   Information about the columns referenced in a constraint
SYSCSTDEP                   Information about constraint dependencies on tables
SYSREFCST                   Information about referential constraints
SYSKEYCST                   Information about unique, primary, and foreign keys
SYSKEYS                     Information about index keys
SYSFOREIGNKEYS				Information about foreign keys

* Procedures and User defined functions
SYSPROCS                    Information about procedures
SYSROUTINES                 Information about functions and procedures
SYSROUTINEDEP               Information about function and procedure dependencies
SYSFUNCS                    Information about user-defined functions

* Statistics
SYSPARTITIONINDEXSTAT       Information about partition index statistics
SYSPARTITIONSTAT            Information about partition statistics