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.
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'
Table types include: BASE TABLE, VIEW, ALIAS and MATERIALIZED QUERY TABLE
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
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'
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
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
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'
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
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'
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.
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
complete list here...
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzcatalogtbls.htm