DB2/400 SQL Metadata tables
(plus some other interesting tables and functions)
The IBM i maintains a set of 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 can be found in libraries SYSIBM, QSYS and QSYS2. There appear to be several generations of these objects with overlapping information.
List all tables in a specific library using table QSYS2.SYSTABLES
This would include P=Physical file, T=Table, L=Logical, V=View and A=Alias. Note that Physicals/Tables are broken out seperately. A subset of information provided by QSYS2.SYSTABLES can be found in QSYS2.TABLES
SELECT SUBSTR(TABLE_SCHEMA, 1, 10) AS LIBRARY ,
SUBSTR(TABLE_NAME, 1, 10) AS TABLE,
SUBSTR(TABLE_TEXT, 1, 30) AS DESCRIPTION,
TABLE_TYPE, LAST_ALTERED_TIMESTAMP, SYSTEM_TABLE_NAME,
SYSTEM_TABLE_SCHEMA, TABLE_OWNER, SYSTEM_TABLE,
SELECT_OMIT, IS_INSERTABLE_INTO,
TABLE_DEFINER, COLUMN_COUNT, ROW_LENGTH
FROM QSYS2.SYSTABLES
WHERE TABLE_TYPE IN ('L','P', 'T', 'V') AND
TABLE_SCHEMA = 'MYLIB'
List all tables in a specific library using table QSYS.QADBXREF
The QSYS.QADBXREF is similar to QSYS2.SYSTABLES table containing information about all tables, physical files, indexes, logical files, source physical files and SQL views on your AS/400 system. Note that access to this file is often restricted.
SELECT DBXLIB, DBXFIL, DBXTXT, DBXATR, DBXTYP, DBXATR, DBXTYP,DBXUNQ,DBXLFI FROM QSYS.QADBXREF WHERE DBXLIB = 'MYLIB'
Find information about a table's columns with SYSIBM.SQLCOLUMNS
SELECT
SUBSTR(SQLCOLUMNS.TABLE_SCHEM, 1, 10) as Library,
SUBSTR(SQLCOLUMNS.TABLE_NAME, 1, 50) as Long_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 SYSIBM.SQLCOLUMNS
WHERE SQLCOLUMNS.TABLE_SCHEM ='MYLIB'
Get table name, description and column names by joining SYSTABLES and SYSCOLUMNS
SELECT SYSTABLES.SYSTEM_TABLE_SCHEMA AS LIBRARY,
SUBSTR(SYSTABLES.TABLE_NAME, 1, 10) AS TABLE,
SUBSTR(SYSTABLES.TABLE_TEXT, 1, 25) AS DESCRIPTION,
SUBSTR(SYSCOLUMNS.COLUMN_NAME, 1, 10) AS COLUMN_NAME,
SYSCOLUMNS.ORDINAL_POSITION,
SYSCOLUMNS.DATA_TYPE, SYSCOLUMNS.LENGTH, NUMERIC_SCALE,
SUBSTR(SYSCOLUMNS.LONG_COMMENT, 1, 25) AS LONG_COMMENT,
SUBSTR(SYSCOLUMNS.COLUMN_HEADING, 1, 20) AS HDG1,
SUBSTR(SYSCOLUMNS.COLUMN_HEADING, 21, 20) AS HDG2,
SUBSTR(SYSCOLUMNS.COLUMN_HEADING, 41, 20) AS HDG3,
COLUMN_TEXT, "CCSID"
FROM QSYS2.SYSTABLES LEFT JOIN QSYS2.SYSCOLUMNS
ON SYSCOLUMNS.TABLE_SCHEMA =
SYSTABLES.TABLE_SCHEMA AND SYSCOLUMNS.TABLE_NAME = SYSTABLES.TABLE_NAME
WHERE SYSTABLES.SYSTEM_TABLE_SCHEMA = 'MYLIB' AND
SYSTABLES.TABLE_NAME = 'MYTABLE'
Get Physical File to Logical file relationships and other dependencies using table QSYS.QADBFDEP
Note that access to this file is often restricted. Dependencies come in several flavors including: D = Data, V = View and I = Indirect
SELECT DBFLIB as Master_Lib, substr(DBFFIL, 1, 10) AS Master_File, DBFLDP Dependent_Lib, substr(DBFFDP, 1, 10) AS Dependent_file, DBFTDP FROM QSYS.QADBFDEP WHERE DBFFIL LIKE '%MYFILE%' AND DBFLIB = 'MYLIB'
Just for fun, let's see what are the dependents on table QSYS.QADBFDEP.
SELECT DBFLIB as Master_Lib, substr(DBFFIL, 1, 10) AS Master_File, DBFLDP Dependent_Lib, substr(DBFFDP, 1, 10) AS Dependent_file, DBFTDP FROM QSYS.QADBFDEP WHERE DBFFIL = 'QADBFDEP'
MASTER_LIB MASTER_FILE DEPENDENT_LIB DEPENDENT_FILE DEP
TYPE
QSYS QADBFDEP SYSIBM SQLSTATS D
QSYS QADBFDEP QSYS2 SYSIXSTAT I
QSYS QADBFDEP QSYS QADBLDEP D
QSYS QADBFDEP QSYS QADBLDNC D
QSYS QADBFDEP QSYS QADBLDNC D
QSYS QADBFDEP QSYS2 SYSINDEXES D
QSYS QADBFDEP QSYS2 SYSVIEWDEP D
QSYS QADBFDEP QSYS2 SYSTABDEP D
QSYS QADBFDEP SYSIBM SQLSPECCOL D
******** End of data ********
Get key fields in logical/view using table QSYS.QADBKFLD
We can get key field information by using Database file key field information (QSYS.QADBKFLD). Note that access to this file is often restricted.
SELECT DBKLIB, DBKFIL, DBKFMT, DBKFLD, DBKPOS, DBKORD, DBKATR, DBKFMP FROM QSYS.QADBKFLD WHERE DBKLIB = 'MYLIB' AND DBKFIL ='MYFILE'
Get logical/view + description + key field information with QSYS.QADBFDEP, QSYS.QADBXREF, QSYS.QADBKFLD and QSYS.QADBIFLD
Here is where things start getting useful. Below we combine name and description of logical/view with key fields including their description and attributes.
SELECT QADBFDEP.DBFLDP AS LIBRARY, SUBSTR(QADBFDEP.DBFFDP, 1, 10) AS FILENAME, QADBXREF.DBXTXT AS SHORT_DESC, QADBXREF.DBXATR, QADBKFLD.DBKPOS AS ORDINAL_POS, QADBKFLD.DBKFLD AS KEY_FIELD, QADBIFLD.DBITYP AS DATA_TYPE, QADBIFLD.DBITXT AS FIELD_DESC, QADBIFLD.DBICLN AS CHARACTER_LEN, QADBIFLD.DBINLN AS NUMERIC_LEN, QADBIFLD.DBINSC AS NUMERIC_SCALE, QADBXREF.DBXREL AS RELATIONAL FROM QSYS.QADBFDEP LEFT JOIN QSYS.QADBXREF ON (QADBFDEP.DBFFDP=QADBXREF.DBXFIL AND QADBFDEP.DBFLDP=QADBXREF.DBXLIB) LEFT JOIN QSYS.QADBKFLD ON (QADBFDEP.DBFFDP=DBKFIL AND DBFLDP = DBKLIB AND DBFRDP = DBKFMT) LEFT JOIN QSYS.QADBIFLD ON (DBFFDP=DBIFIL AND DBFLDP=DBILIB AND DBFRDP=DBIFMT AND DBKFLD=DBIFLD) WHERE DBFFIL='MYFILE' AND DBFLIB='MYLIB' AND QADBXREF.DBXATR='LF' ORDER BY FILENAME, ORDINAL_POS
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 QHST) CL command. Added in OS/400 release 7.2
Return a list history log messages for all of yesterday and today. This is default period for this command.SELECT ORDINAL_POSITION, MESSAGE_ID, MESSAGE_TYPE, MESSAGE_SUBTYPE, SEVERITY, MESSAGE_TIMESTAMP, FROM_USER, FROM_JOB, FROM_JOB_NAME, FROM_JOB_USER, FROM_JOB_NUMBER, FROM_PROGRAM, MESSAGE_LIBRARY, MESSAGE_FILE, MESSAGE_TOKENS, MESSAGE_TEXT, MESSAGE_SECOND_LEVEL_TEXT, SYSLOG_EVENT, SYSLOG_FACILITY, SYSLOG_SEVERITY FROM TABLE(QSYS2.HISTORY_LOG_INFO())
example:
Return a list of all history log messages for the last 7 days.
SELECT * FROM TABLE(QSYS2/HISTORY_LOG_INFO(CURRENT TIMESTAMP - 7 DAY))
Return a list of Allocation and I/O errors (RNQ and RNX) errors for specific date period.
SELECT MESSAGE_TIMESTAMP, MESSAGE_ID, SEVERITY, FROM_USER, FROM_JOB,
FROM_JOB_NAME, FROM_JOB_USER, FROM_JOB_NUMBER, FROM_PROGRAM,
MESSAGE_TEXT, MESSAGE_SECOND_LEVEL_TEXT, MESSAGE_LIBRARY, MESSAGE_FILE
FROM TABLE(QSYS2.HISTORY_LOG_INFO('2023-10-01-12.00.00.000000', '2023-10-08-12.00.00.000000'))
WHERE MESSAGE_ID LIKE 'RNQ%' OR MESSAGE_ID LIKE 'RNX%'
ORDER BY MESSAGE_TIMESTAMP DESC
MESSAGE_FILE_DATA - Information in message files
Find message across multiple message files like DSPMSGD. By default it checks all message files.
SELECT * FROM QSYS2.MESSAGE_FILE_DATA WHERE MESSAGE_FILE_LIBRARY = 'QSYS' and MESSAGE_FILE = 'QSQLMSG'
See similar tables, functions and views:
- MESSAGE_QUEUE_INFO() table function
- MESSAGE_QUEUE_INFO view
- REPLY_LIST_INFO view
- SEND_MESSAGE procedure
- OBJECT_LOCK_INFO
Other tables to look at:
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 (can 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
complete list here...
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzcatalogtbls.htm