DB2 for z: system catalog tables
http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_catalogtablesintro.html
Table space | Table | Indexes (Type1 ) | Description |
---|---|---|---|
SYSALTER | SYSOBDS |
DSNDOB01 (N)
DSNDOB02 (N)
|
Contains one row for each table space or index that can be recovered to an image copy that was made before the first version was generated |
SYSCONTX | SYSIBM.SYSCONTEXT table |
DSNCTX01 (UC)
DSNCTX02 (U)
DSNCTX03 (U)
DSNCTX04 (N)
|
Contains one row for each trusted context. |
SYSIBM.SYSCTXTTRUSTATTRS table | DSNCAX01 (UC) | Contains the attributes for a given trusted context | |
SYSIBM.SYSCONTEXTAUTHIDS table |
DSNCDX01 (UC)
DSNCDX02 (N)
|
Contains the authorization ID under which a trusted context can be used. | |
SYSCOPY | SYSIBM.SYSCOPY table |
DSNUCH01 (N)
DSNUCX01 (N)
|
|
SYSDDF | SYSIBM.IPLIST table | DSNDUX01 (U) | Contains a row for each IP address that corresponds to a remote DRDA® server |
SYSIBM.IPNAMES table | DSNFPX01 (UC) | Contains a row for each remote DRDA server that DB2 can access using TCP/IP | |
SYSIBM.LOCATIONS table | DSNFCX01 (UC) | Contains a row for every accessible remote server | |
SYSIBM.LULIST table |
DSNFLX01
DSNFLX02
|
Contains a row for each real LU name that is associated with the dummy LU name for a data sharing group | |
SYSIBM.LUNAMES table | DSNFNX01 (UC) | Contains a row for each remote SNA client or server | |
SYSIBM.LUMODES table | DSNFMX01 (UC) | Contains a row for the conversation limit for each combination of LU name and VTAM logon mode description | |
SYSIBM.MODESELECT table | DSNFDX01 (UC) | Contains a row for each VTAM logon mode and combination of authorization ID, plan name, and LU name | |
SYSIBM.USERNAMES table | DSNFEX01 (U) | Contains a row for each authorization ID to be translated or checked as it is sent to or from this DB2 | |
SYSEBCDC | SYSIBM.SYSDUMMY1 table | — | Contains one row. This table is used by SQL statements that do not use any tables but must specify a table name. |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSGPAUT | SYSIBM.SYSRESAUTH table |
DSNAGH01 (N)
DSNAGX01 (N)
|
Records the privileges that are held by users over buffer pools, storage groups, table spaces, and collections |
SYSGRTNS | SYSIBM.SYSROUTINES_OPTS table | DSNROX01 (UC) | Contains one row to record the build options for each generated routine |
SYSIBM.SYSROUTINES_SRC table |
DSNRSX01 (N)
DSNRSX02 (U)
|
Contains one or more rows for the source code for each generated routine | |
SYSHIST | SYSIBM.SYSCOLDIST_HIST table | DSNHFX01 (N) | Contains accumulated rows from SYSCOLDIST |
SYSIBM.SYSCOLUMNS_HIST table | DSNHEX01 (N) | Contains accumulated rows from SYSCOLUMNS | |
SYSIBM.SYSINDEXES_HIST table |
DSNHHX01 (NC)
DSNHHX02 (N)
|
Contains accumulated rows from SYSINDEXES | |
SYSIBM.SYSINDEXPART_HIST table | DSNHGX01 (N) | Contains accumulated rows from SYSINDEXPART | |
SYSIBM.SYSINDEXSTATS_HIST table | DSNHIX01 (NC) | Contains accumulated rows from SYSINDEXSTATS | |
SYSIBM.SYSKEYTARGETS_HIST table | DSNHKX01 (N) | Contains accumulated rows from SYSKEYTARGETS | |
SYSIBM.SYSKEYTGTDIST_HIST table | DSNTDX02 (N) | Contains accumulated rows from SYSKEYTGTDIST | |
SYSIBM.SYSLOBSTATS_HIST table | DSNHJX01 (NC) | Contains accumulated rows from SYSLOBSTATS | |
SYSIBM.SYSTABLEPART_HIST table | DSNHCX01 (N) | Contains accumulated rows from SYSTABLEPART | |
SYSIBM.SYSTABLES_HIST table | DSNHDX01 (N) | Contains accumulated rows from SYSTABLES | |
SYSIBM.SYSTABSTATS_HIST table | DSNHBX01 (NC) | Contains accumulated rows from SYSTABSTATS | |
SYSJAUXA | SYSIBM.SYSJARDATA table | DSNJDX01 (A) | Contains the contents of the JAR file for each Java stored procedure |
SYSJAUXB | SYSIBM.SYSJARCLASS_SOURCE table | DSNJSX01 (A) | Contains the source code for a Java stored procedure |
SYSJAVA | SYSIBM.SYSJARCONTENTS table | DSNJCX01 (NC) | Records the classes for each JAR file for a Java stored procedure |
SYSIBM.SYSJAROBJECTS table | DSNJOX01 (UC) | Records the contents of each JAR file for a Java stored procedure | |
SYSIBM.SYSJAVAOPTS table | DSNJVX01 (UC) | Records the build options for a Java stored procedure | |
SYSIBM.SYSJAVAPATHS table |
DSNJPX01 (U)
DSNJPX02 (N)
|
Contains the complete JAR class resolution path, and records the dependencies that one JAR has on the JARs in its Java path. | |
SYSPLUXA | SYSIBM.SYSROUTINESTEXT table | DSNPLX01 (A) | Contains LOB data for the column SYSIBM.SYSROUTINES.TEXT |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSROLES | SYSIBM.SYSROLES table | DSNRLX01 (UC) | Contains one row for each role |
SYSIBM.SYSOBJROLEDEP table |
DSNRDX01 (UC)
DSNRDX02 (N)
|
Contains one row for each dependent role | |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNRTX02 (U)
DSNRTX03 (N)
![]() |
![]() ![]() |
SYSSEQ | SYSIBM.SYSSEQUENCES table |
DSNSQX01 (U)
DSNSQX02 (UD)
DSNSQX03 (N)
|
Contains one row for each identity column |
SYSSEQ2 | SYSIBM.SYSSEQUENCEAUTH table |
DSNWCX01 (N)
DSNWCX02 (N)
DSNWCX03 (N)
|
Records the privileges that users hold on sequences |
SYSIBM.SYSSEQUENCESDEP table |
DSNSRX01 (N)
DSNSRX02 (N)
|
Records the dependencies of identity columns on the corresponding entries in SYSIBM.SYSSEQUENCES | |
SYSSTATS | SYSIBM.SYSCOLDIST table | DSNTNX01 (NC) | Contains cardinality, frequency, and histogram statistics for a single column or a column group. |
SYSIBM.SYSCOLDISTSTATS table | DSNTPX01 (NC) | Contains cardinality, frequency, and histogram statistics for a single column or a column group. Each partition can have 0 or more rows. | |
SYSIBM.SYSCOLSTATS table | DSNTCX01 (NC) | Contains partition statistics for selected columns. Each column contains a row for each partition in the table | |
SYSIBM.SYSINDEXSTATS table | DSNTXX01 (UC) | Contains one row for each partition of a partitioning index or data-partitioned secondary index (DPSI) | |
SYSIBM.SYSKEYTARGETSTATS table | DSNTKX01 (U) | Contains partition statistics for selected key-targets | |
SYSIBM.SYSKEYTGTDIST table | DSNTDX01 (N) | Contains one or more rows for the first key-target of an extended index key | |
SYSIBM.SYSKEYTGTDISTSTATS table | DSNTSX01 (N) | Contains zero or more rows per partition for the first key-target of a data-partitioned secondary index (DPSI) | |
SYSIBM.SYSLOBSTATS table | DSNLNX01 (UC) | Contains one row for each LOB table space | |
SYSIBM.SYSTABSTATS table |
DSNTTX01 (U)
DSNTTX02 (N)
|
Contains one row for each partition of a partitioned table space and no rows for nonpartitioned table spaces | |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSIBM.SYSSTRINGS table | DSNSSX01 (U) | Contains information necessary to perform string translations | |
SYSTARG | SYSIBM.SYSKEYTARGETS table |
DSNRKX01 (UC)
DSNRKX02 (N)
|
Contains one row for each key-target participating in an extended index definition |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNALX01 (UC)
DSNALX02 (N)
DSNALX03 (N)
DSNALX04 (N)
DSNALX05 (N)
DSNALX06 (N)
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSAUX | SYSIBM.SYSAUXRELS table | ![]() DSNOXX01 (NC)
DSNOXX02 (N)
![]() |
Contains one row for each auxiliary table for a LOB column |
SYSTSCOL | SYSIBM.SYSCOLUMNS table |
DSNDCX01 (U)
DSNDCX02 (N)
DSNDCX05 (N)
|
Contains one row for every column of each table and view |
SYSTSCON | SYSIBM.SYSCONSTDEP table |
DSNCCX01 (N)
DSNCCX02 (N)
|
Records dependencies on check constraints or user-defined defaults for a column |
SYSTSCPY | SYSIBM.SYSCOPY table |
DSNUCH01 (N)
DSNUCX01 (U)
|
Contains information that is needed for recovery |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNCLX01 (U)
DSNCLX02 (U)
DSNCLX03 (N)
DSNCLX04 (N)
DSNCLX05 (N)
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSDAT | SYSIBM.SYSDATATYPES table |
DSNODX01 (U)
DSNODX02 (U)
|
Contains one row for each distinct data type |
SYSTSDBA | SYSIBM.SYSDATABASE table |
DSNDDH01 (U)
DSNDDX02 (N)
|
Contains one row for each database, except for database DSNDB01 |
SYSTSDBR | SYSIBM.SYSDBRM table |
DSNDBX01 (N)
DSNDBX02 (U)
|
Contains one row for each DBRM of each application plan |
SYSTSDBU | SYSIBM.SYSDBAUTH table |
DSNADH01 (N)
DSNADX01 (N)
DSNADH02 (N)
|
Records the privileges held by users over databases |
SYSTSDEP | SYSIBM.SYSDEPENDENCIES table |
DSNONX01 (U)
DSNONX02 (N)
|
Records the dependencies between objects |
SYSTSENV | SYSIBM.SYSENVIRONMENT table | DSNOEX01 (U) | Records the environment variables when an object is created |
SYSTSFAU | SYSIBM.SYSCOLAUTH table |
DSNACX01 (N)
DSNACX02 (N)
DSNACX03 (N)
DSNACX04 (N)
|
Records the UPDATE privileges that are held by users on individual columns of a table or view |
SYSTSFLD | SYSIBM.SYSFIELDS table | DSNDFX01 (N) | Contains one row for every column that has a field procedure |
SYSTSFOR | SYSIBM.SYSFOREIGNKEYS table | DSNDRH01 (N) | Contains one row for every column of every foreign key |
SYSTSIPT | SYSIBM.SYSINDEXPART table |
DSNDRX01 (U)
DSNDRX02 (N)
DSNDRX03 (N)
|
Contains one row for each nonpartitioned index and one row for each partition of a partitioning index or DPSI |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSIXS | SYSIBM.SYSINDEXES table |
DSNDXX01 (U)
DSNDXX02 (U)
DSNDXX03 (U)
DSNDXX04 (N)
DSNDXX07
|
Contains one row for every index |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSKEY | SYSIBM.SYSKEYS table |
DSNDKX01 (U)
DSNDKX02 (N)
DSNDKX03 (U)
|
Contains one row for each column of an index key |
SYSTSKYC | SYSIBM.SYSKEYCOLUSE table | DSNCUX01 (N) | Contains a row for every column in a unique constraint (primary key or unique key) |
![]() ![]() |
![]() ![]() |
![]() DSNPOX01 (N)
DSNPOX02 (N)
DSNPOX03 (N)
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNPDX01 (N)
DSNPDX02 (N)
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSPKA | SYSIBM.SYSPACKAUTH table |
DSNKAX01 (N)
DSNKAX02 (N)
DSNKAX03 (N)
|
Contains the privileges that are held by users over packages |
SYSTSPKC | SYSIBM.SYSPACKCOPY table | DSNPCX01 (UC) | |
SYSTSPKD | SYSIBM.SYSPACKDEP table |
DSNKDX01 (N)
DSNKDX02 (N)
DSNKDX03 (N)
|
Records the dependencies of packages on local tables, views, synonyms, table spaces, indexes, aliases, and triggers |
SYSTSPKG | SYSIBM.SYSPACKAGE table |
DSNKKX01 (U)
DSNKKX02 (U)
|
Contains one row for each package |
SYSTSPKL | SYSIBM.SYSPACKLIST table |
DSNKLX01 (N)
DSNKLX02 (U)
|
Contains one row for every package list entry for a plan |
SYSTSPKS | SYSIBM.SYSPACKSTMT table | DSNKSX01 (UC) | Contains one row for every SQL statement that belongs to a package |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNAPH01 (N)
DSNAPX01 (N)
DSNAPX02 (N)
![]() |
![]() ![]() |
SYSTSPLD | SYSIBM.SYSPLANDEP table |
DSNGGX01 (N)
DSNGGX05 (N)
|
Records the dependencies of plans on tables, views, aliases, synonyms, table spaces, and indexes |
SYSTSPLN | SYSIBM.SYSPLAN table |
DSNPPH01 (U)
|
Contains one row for each application plan |
SYSTSPLY | SYSIBM.SYSPLSYSTEM table | DSNKPX01 (N) | Contains one row of system information for each plan |
![]() ![]() |
![]() ![]() |
![]() DSNPHX01 (UC)
DSNPHX02 (N)
DSNPHX03 (N)
![]() |
![]() ![]() |
SYSTSPRM | SYSIBM.SYSPARMS table |
DSNOPX01 (U)
DSNOPX02 (N)
DSNOPX03 (N)
DSNOPX04 (N)
|
Contains a row for each parameter of a routine or a row for each column of a table that is passed as a parameter to a routine |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNQYX01 (N)
DSNQYX02 (U)
DSNQYX03 (N)
![]() |
![]() ![]() |
SYSTSRAU | SYSIBM.SYSROUTINEAUTH table |
DSNOAX01 (N)
DSNOAX02 (U)
DSNOAX03 (N)
|
Records the privileges that users hold on routines |
SYSTSREL | SYSIBM.SYSRELS table |
DSNDLX01 (N)
DSNDLX02 (N)
DSNDLX03 (N)
DSNDLX04 (U)
|
Contains one row for every relationship |
SYSTSROU | SYSIBM.SYSROUTINES table |
DSNOFX01 (U)
DSNOFX02 (U)
DSNOFX03 (N)
DSNOFX04 (U)
DSNOFX05 (N)
DSNOFX06 (N)
DSNOFX07 (U)
DSNOFX08 (N)
|
Contains a row for every routine |
SYSTSSCM | SYSIBM.SYSSCHEMAAUTH table |
DSNSKX01 (N)
DSNSKX02 (N)
|
Contains one or more rows for each grantee of a privilege on a schema |
SYSTSSTG | SYSIBM.SYSSTOGROUP table | DSNSSH01 (U) | Contains one row for each storage group |
SYSTSSTM | SYSIBM.SYSSTMT table |
DSNPSX01 (N)
DSNPSX02 (N)
|
Contains one or more rows for each SQL statement of each DBRM |
SYSTSSYN | SYSIBM.SYSSYNONYMS table |
DSNDYX01 (U)
DSNDYX02 (N)
|
Contains one row for each synonym of a table or view |
SYSTSTAB | SYSIBM.SYSTABLES table |
DSNDTX01 (U)
DSNDTX02 (N)
DSNDTX03 (N)
DSNDTX05
|
Contains one row for each table, view, and alias |
SYSTSTAU | SYSIBM.SYSTABAUTH table |
DSNATX01 (N)
DSNATX02 (N)
DSNATX03 (N)
DSNATX04 (N)
DSNATX05 (U)
|
Records the privileges that are held by users on tables, views, and triggers |
SYSTSTBC | SYSIBM.SYSTABCONST table |
DSNCNX01 (U)
DSNCNX02 (N)
|
Contains one row for each unique constraint that was created in DB2 for z/OS Version 7 or later |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSTPT | SYSIBM.SYSTABLEPART table |
DSNDPX01 (U)
DSNDPX02 (N)
DSNDPX03 (N)
DSNDPX04 (N)
DSNDPX05 (N)
|
Contains one row for each nonpartitioned table space and one row for each partition of a partitioned table space |
SYSTSTRG | SYSIBM.SYSTRIGGERS table |
DSNOTX01 (UC)
DSNOTX02 (N)
DSNOTX03 (N)
|
Contains one row for each trigger |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSTSP | SYSIBM.SYSTABLESPACE table | DSNDSX01 (U) | Contains one row for each table space |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSVEW | SYSIBM.SYSVIEWS table | DSNVVX01 (U) | Contains one or more rows for each view |
SYSTSVOL | SYSIBM.SYSVOLUMES table | DSNSSH02 (N) | Contains one row for each volume of each storage group |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
SYSTSVWD | SYSIBM.SYSVIEWDEP table |
DSNGGX02 (N)
DSNGGX03 (N)
DSNGGX04 (N)
DSNGGX06
|
Records the dependencies of views on tables and other views |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNMSX01 (U)
DSNMSX02 (N)
![]() |
![]() ![]() |
![]() ![]() |
![]() ![]() |
![]() DSNAUH01 (N)
DSNAUX02 (N)
![]() |
![]() ![]() |
SYSXML | SYSIBM.SYSXMLRELS table |
DSNXRX01 (N)
DSNXRX02 (N)
|
Contains one row for each XML table that is created for an XML column |
SYSIBM.SYSXMLSTRINGS table |
DSNXSX01 (UC)
DSNXSX02 (U)
|
Each row holds a single string and its unique ID. They are used to condense XML data. The string can be an element name, attribute name, name space prefix, or a name space URI. |