Some DB2/400 SQL Metadata tables
plus some other interesting tables and functions.
The IBM i maintains a set of tables and views 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... These objects can be found in libraries SYSIBM, QSYS and QSYS2. There appear to be several generations of these objects with overlapping information. Because some of the table and field names exceed 10 characters, it is best to used an advanced SQL tool like the IBM ASC Run SQL Scripts tool.
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.
Tables for tracking SQL stored procedures and functions
The QSYS2.SYSROUTINES table contains one row for each procedure created by the CREATE PROCEDURE statement or CREATE FUNCTION statement.
SELECT substr(SPECIFIC_SCHEMA, 1, 20) as SPECIFIC_SCHEMA, substr(SPECIFIC_NAME, 1, 20) as SPECIFIC_NAME, substr(ROUTINE_SCHEMA, 1, 20) as ROUTINE_SCHEMA, substr(ROUTINE_NAME, 1, 20) as ROUTINE_NAME, substr(EXTERNAL_NAME, 1, 30) as EXTERNAL_NAME, substr(ROUTINE_TYPE, 1, 20) as ROUTINE_TYPE, EXTERNAL_LANGUAGE, PARAMETER_STYLE, IS_DETERMINISTIC FROM QSYS2.SYSROUTINES WHERE SPECIFIC_SCHEMA like '%MYLIB%'
see also:
SYSPROCS Information about procedures
SYSROUTINEDEP Information about function and procedure dependencies
SYSFUNCS Information about user-defined functions
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
complete list here...
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzcatalogtbls.htm