Mainframe Blog

Catalog Tables in Db2 12

138 minute read
Stephen Watts

Check out the interactive Db2 12 for z/OS Catalog Tables index

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

SYSIBM.IPLIST

Allows multiple IP addresses to be specified for a given LOCATION

Column name Data type Description
LINKNAME VARCHAR(24) Associated with value specified in LINKNAME column in LOCATIONS table and IPNAMES table. Values of other columns in IPNAMES table apply to server identified by LINKNAME
IPADDR VARCHAR(254) Contains an IPv4 or IPv6 address, or domain name of a remote TCP/IP host of server
IBMREQD CHAR(1) A value of Y means row came from MRM tape

SYSIBM.IPNAMES

Defines the remote DRDA servers DB2 can access using TCP/IP

Column name Data type Description
LINKNAME VARCHAR(24) Value specified must value specified in LINKNAME column of associated role in LOCATIONS
SECURITY_OUT CHAR(1) DRDA security option used when local DB2 SQL applications connect to remote server associated with this TCP/IP host:

A “already verified”

D “userid and security sensitive data encryption”

E “userid, password and security sensitive data encryption”

R “RACF PassTicket”

p “password”

USERNAMES CHAR(1) Controls outbound auth ID translation. Performed when an auth ID is sent by DB2 to a remote server:

O An outbound ID is subject to translation. USERNAMES table are used to perform ID translation. No translation or ‘come from’ checking is performed an inbound IDs.

S USERNAMES table is used to obtain system

AUTHID used to establish a trusted connection Blank No translation occurs

IBMREQD CHAR(1) Y indicates row came a MRM tape
IPADDR VARCHAR(254) IP address or domain name of a remote TCP/IP host

SYSIBM.LOCATIONS

Contains a row for every accessible remote server

Column name Data type Description
LOCATION VARCHAR(128) A unique location name for accessible server. Name by which the remote server is known to local DB2 SQL applications
LINKNAME VARCHAR(128) Identifies VTAM or TCP/IP attributes associated with this location
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
PORT VARCHAR(96) TCP/IP is used for outbound DRDA connections when following statements is true: Row exists in IPNAMES, where LINKNAME column matches value specified in LOCATIONS LINKNAME column
TPN VARCHAR(192) Used only when local DB2 begins an SNA conversation with another server
DBALIAS VARCHAR(128) Database alias. Name associated with server. Name is used to access a remote database server
TRUSTED CHAR(1) Connection to remote server can be trusted. Restricted to TCP/IP only. Ignored for connections using SNA.

Y Location is trusted. Access to remote location requires trusted context defined at remote location

N Location is not trusted

SECURE CHAR(1) Use Secure Socket Layer (SSL) protocol for outbound DRDA connections when local DB2 applications connects to remote database server using TCP/IP

Y Secure connection using SSL required for outbound DRDA

N Secure connection is not required for outbound DRDA

SYSIBM.LULIST

Allows multiple LU names to be specified for a given LOCATION

Column name Data type Description
LINKNAME VARCHAR(24) Value of LINKNAME column LOCATIONS with which this row is associated. Also value of LUNAME column in LUNAMES table. Values of other columns in LUNAMES row apply to LU identified by LUNAME column in this row of LULIST
LUNAME VARCHAR(24) VTAM logical unit name (LUNAME) of remote database system. LUNAME must not exist in LUNAME column of LUNAMES
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.LUMODES

Provides VTAM w/conversation limits for a specific combination of LUNAME and MODENAME

Column name Data type Description
LUNAME VARCHAR(24) LU name of the server involved in the CNOS processing
MODENAME VARCAHR(24) Name of a logon mode description in the VTAM logon mode table
CONVLIMIT SMALLINT Maximum number of active conversations between local DB2 and other system for this mode. Used to override number in the DSESLIM parameter of the VTAMAPPL definition statement for this mode
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.LUNAMES

Contains a row for each remote SNA client or server that communicates with DB2

Column name Data type Description
LUNAME VARCHAR(24) Name of LU for one or more accessible systems
SYSMODENAME VARCHAR(24) Mode used to establish inter-system conversations
SECURITY_IN CHAR(1) Define security options when an SNA client connects to DB2:

V ‘verify’

A ‘already verified’

SECURITY_OUT CHAR(1) Defines security option used when local DB2 SQL applications connect to any remote server associated with this LUNAME

A ‘already verified’

R ‘RACF PassTicket’

P ‘password’

ENCRYPTPSWDS CHAR(1) Only applies to DB2 for z/OS partners. Provided to support connectivity to prior releases of DB2 unable to support RACF PassTickets

N Passwords are not in internal RACF encrypted format(default)

Y For outbound requests, encrypted password is extracted from RACF and sent to the server. For inbound requests, the password is treated as encrypted

MODESELECT CHAR(1) Use MODESELECT table:

N Use default modes: IBMDB2LM (DB2 private protocol) and IBMRDB (DRDA)

Y Searches MODESELECT for appropriate mode name

USERNAMES CHAR(1) Controls inbound and outbound authorization ID translation, and ‘come from’ checking. Inbound translation and ‘come from’ checking are performed when an authorization ID is received from a remote client. Outbound translation is performed when an authorization ID is sent by DB2 to a remote server. When I, O, or B is specified in this column, rows in USERNAMES are used to perform ID translation.

I An inbound ID is subject to translation and ‘come from’ checking. No translation is performed on outbound IDs

O No translation or ‘come from’ checking is performed on inbound IDs. An outbound ID is subject to translation.

B An inbound ID is subject of translation and ‘come from’ checking. An outbound ID is subject to translation

Blank No translation occurs

GENERIC CHAR(1) DB2 should use its real LU name or generic LU name to identify itself to the partner LU, which is identified by this row

N Real VTAMLU name of this DB2 subsystem

Y VTAM generic LU name of this DB2 subsystem

IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.MODESELECT

Associates a mode name with any conversation created to support an outgoing SQL request

Column name Data type Description
AUTHID VARCHAR(128) Auth ID of SQL request. Blank (default) = MODENAME specified for row is to apply to all authorization IDs
PLANNAME VARCHAR(24) Plan name associated with SQL request. Blank (default)= MODENAME specified is to apply to all plan names
LUNAME VARCHAR(24) LU name associated with SQL request
MODENAME VARCAHR(24) Name of logon mode in VTAM logon mode table to be used in support of the outgoing SQL request. If blank, IBMDB2LM is used for DB2 private protocol and IBMRDB is used for DRDA
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.AUDITPOLICIES

Contains one row for each order policy

Column name Data type Description
AUDITPOLICY

NAME

VARCHAR(128) Name of audit policy

 

OBJECTSCHEMA VARCHAR(128) Schema of audited object. Object schema only applies to categories. OBJMAINT and EXECUTE
OBJECTNAME VARCHAR(128) Name of object
OBJECTTYPE CHAR(1) Type of object for categories OBJMAINT and EXECUTE:

A Alias

C Clone table

P Implicit table created for XML columns

T Table

blank All the above object types

CREATEDTS TIMESTAMP Time when the row was inserted
ALTERDTS TIMESTAMP Time when the row was last updated
CHECKING CHAR(1) Authorization and authentication failures are audited:

A Audit all failures (authorization and authentication)

Blank Audit none

VALIDATE CHAR(1) Auditing is enabled for when a trusted connection is established or used by different user:

A Audit all

Blank Audit none

OBJMAINT CHAR(1) Auditing is enabled for when table identified by OBJECTSCHEMA, OBJECTNAME and OBJECTTYPE columns is altered or dropped:

A Audit when specified table is altered or dropped

Blank Audit none

EXECUTE CHAR(1) Auditing is enabled for when table identified by OBJECTSCHEMA, OBJECTNAME and OBJECTTYPE columns is accessed during first operation performed by each unit of work. Records bind time information about SQL statements that involve tables identified by the OBJECTSCHEMA, OBJECTNAME, and OBJECTTYPE

A Audit when specified table is accessed during the first operation of any kind performed by each unit of work a utility or application process

C Audit when specified table is accessed during first insert, update or delete operation performed by each unit of work

Blank Audit none

CONTEXT CHAR(1) If auditing is enabled for start of a utility, a change to a utility object or phase, and end of utility:

A Audit all utilities

Blank Audit none

SECMAINT CHAR(1) If auditing is enabled for when a grant or revoke is made or a trusted context is created or altered:

A Audit all utilities

Blank Audit none

SYSADMIN VARCHAR(128) If auditing is enabled for when an operation is performed using administrative authority to perform system administration tasks:

Blank Audit none

* Audit all the authorities

I Installation SYSADM

L SYSCTRL

O SYSOPR

R Installation SYSOPR

S SYSADM

Can be concantenated string of all supported values

DBADMIN VARCHAR(128) Indicates if auditing is enabled for when an operation is performed using an administrative authority to perform database administration tasks:

Blank Audit none

* Audit all the authorities

B System DBADM

C DBCTRL

D DBADM

E SECADM

G ACCESSCTRL

K SQLADM

M DBMAINT

P PACKADM

T DATAACCESS

Can be a concantenated string of all supported values

DBNAME VARCHAR(24) Database name
COLLID VARCHAR(128) Name of package collection
DB2START CHAR(1) Indicates if audit policies are to be started automatically during DB2 start up. Up to 8 audit policies can be specified

Y Audit policy will be started automatically

N Audit policy will not be started automatically

IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value

SYSIBM.SYSAUTOALERTS

Contains one row for each recommendation from autonomic procedures

Column name Data type Description
ALERT_ID BIGINT ID of alert
HISTORY_ENTRY_ID BIGINT ID of entry in ADMIN_UTLPROCEDURES_HIST procedure that produced this alert
ACTION VARCHAR(32) Type of action requested by this alert
TARGET_QUALIFER VARCHAR(128) Qualifier of object (database name) to which alert applies
TARGET_OBJECT VARCHAR(128) Name of object (tablespace name) to which alert applies
TARGET_PARTITION SMALLINT Partition number of DB2 object to which alert applies. 0, if it applies to all partitions or if object is not partitioned
OPTIONS VARCHAR(4000) Options specified and corresponding action is run:

USE PROFILE use options specified in profile

TABLE Options only apply for this table

COLUMNS Options only apply for these columns

SAMPLE Sampling is allowed

CREATEDTS TIMESTAMP Timestamp when alert was issued
DURATION INTEGER Estimate of time, in seconds, to run corresponding action
STATUS VARCHAR(32) Status od actual planned task

OPEN Alert is not yet resolved

IN PROGRESS Alert execution is in progress

COMPLETED Alert execution is complete

STARTTS TIMESTAMP Timestamp for when alert execution started
ENDTS TIMESTAMP Timestamp for when alert execution ended
RETURN_CODE INTEGER Return code written directly by autonomic stored procedure that resolved alert
ERROR_MESSAGE VARCHAR(1331) Error message that indicates why alert was not resolved successfully
OUTPUT CLOB(2M) Output written directly by the autonomic stored procedure that executes the planned task
ROWID ROWID ROWID value for CLOB column of this table

SYSIBM.SYSAUTOALERTS_OUT

An auxiliary table for OUTPUT column of the SYSIBM.SYSAUTOALERTS table

Column name Data type Description
OUTPUT CLOB(2M) The output of the autonomic stored procedure

SYSIBM.SYSAUTORUNS_HIST

Contains one row for each time an autonomic procedure has been run

Column name Data type Description
HISTORY_ENTRY_ID BIGINT ID of the entry in history table
PROC_NAME VARCHAR(128) Name of autonomic stored procedure that produced entry
STARTTS TIMESTAMP Timestamp when autonomic stored procedure started
ENDTS TIMESTAMP Timestamp when autonomic stored procedure ended
OUTPUT CLOB(2M) Output of autonomic stored procedure
ERROR_MESSAGE VARCHAR(1331) An error message that indicates why the autonomic stored procedure was not successful
RETURN_CODE INTEGER Return code written directly by autonomic stored procedure
ROWID ROWID ROWID value for OUTPUT column of this table

SYSIBM.SYSAUTORUNS_HISTOU

An auxiliary table for the OUTPUT column of the SYSIBM.SYSAUTORUNS_HIST table

Column name Data type Description
OUTPUT CLOB(2M) The output of the autonomic stored procedure

SYSIBM.SYSAUTOTIMEWINDOWS

Contains one row for each time period during which autonomic procedures can be run

Column name Data type Description
WINDOW_ID BIGINT ID of time window described in this row
DB2_SSID CHAR(4) DB2 member name on which planned tasks have to be run
MONTH_WEEK CHAR(1) How value of DAY column is interpreted:

M Value of DAY column is interpreted as day of month

W Value of DAY column is interpreted as day of week

MONTH INTEGER Month in which time window applies
DAY INTEGER Day of the month or day of the week for which the time window applies
FROM_TIME TIME Time of day at which time window applies
TO_TIME TIME Time of day at which time window ends
ACTION VARCHAR(256) Comma-separated list of actions allowed during this time window
MAX_TASKS INTEGER Number of concurrent actions allowed during time window

SYSIBM.SYSAUXRELS

Contains one row for each auxiliary table created for a LOB column

Column name Data type Description
TBOWNER VARCHAR(128) Schema of base table
TBNAME VARCHAR(128) Name of base table
COLNAME VARHCAR(128) Name of LOB column in base table
PARTITION SMALLINT Partition number if base tablespace is partitioned. Else, 0
AUXTBOWNER VARCHAR(128) Schema of owner of the auxiliary table
AUXTBNAME VARCHAR(128) Name of auxiliary table
AUXRELOBID INTEGER Internal identifier relationship between base and auxiliary table
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
RELCREATED CHAR(1) Release of DB2 was used to create object. Blank if pre-9

SYSIBM.SYSCHECKDEP

Contains one row for each reference to a column in a table check constraint

Column name Data type Description
TBOWNER VARCHAR(128) Schema of owner of table on which check constraint is defined
TBNAME VARCHAR(128) Name of table on which the check constraint is defined
CHECKNAME VARCHAR(128) Name of check constraint
COLNAME VARCHAR(128) Name of column that the table check constraint refers to
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.SYSCHECKS

Contains one row for each table check constraint

Column name Data type Description
TBOWNER VARCHAR(128) Schema of owner of table on which constraint is defined
CREATOR VARCHAR(128) Authorization ID of the creator of the table check constraint
DBID SMALLINT Internal identifier of the database for the table check constraint
OBID SMALLINT internal identifier of the table check constraint
TIMESTAMP TIMESTAMP Time table check constraint was created
RBA CHAR(10) Log RBA when table check constraint was created
IBMREQD CHAR(1) Y indicates row came from the basic (MRM) tape
TBNAME VARCHAR(128) Name of table on which check constraint is defined
CHECKNAME VARCHAR(128) Table check constraint name
CHECKCONDITION VARCHAR(7400) Text of table check constraint
RELCREATED CHAR(1) Release of DB2 is used to create the object. Blank if pre-9
ENVID INTEGER Internal environment identifier
PERIOD CHAR(1) Type of business associated with check constraint:

B: BUSINESS_TIME check constraint

S: SYSTEM_TIME check constraint

Blank: not applicable

SYSIBM.SYSCHECKS2

Contains one row for each table check constraint

Column name Data type Description
TBOWNER VARCHAR(128) Schema of owner of table on which constraint is defined
TBNAME VARCHAR(128) Name of the table on which the check constraint is defined
CHECKNAME VARCHAR(128) Table check constraint name
PATHSCHEMA VARCHAR(2048) SQL path at time check constraint was created
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
RELCREATED CHAR(1) Release of DB2 is used to create the object. Blank if pre-9

SYSIBM.SYSCOLAUTH

UPDATE or REFERENCES privileges held by users on individual columns of table or view

Column name Data type Description
GRANTOR VARCHAR(128) Auth ID of user who granted privileges. Can be PUBLIC or PUBLIC*
GRANTEE VARCHAR(128) Authorization ID of user who holds privilege or name of plan or package that uses privilege
GRANTEETYPE CHAR(1) Type of grantee:

Blank: An authorization ID

L: Role

P: An application plan or package. Grantee is a package if COLLID is not blank

CREATOR VARCHAR(128) Schema of owner of table or view on which update privilege is held
TNAME VARCHAR(128) Name of table or view
COLNAME VARCHAR(128) Name of columns to which UPDATE privilege applies
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
COLLID CHAR(128) If GRANTEE is a package, its collection name. Else, blank
CONTOKEN CHAR(8) If GRANTEE is a package, consistency token of DBRM from which package was derived. Else, blank
PRIVILEGE CHAR(1) Privileged row describes:

R REFERENCES privilege

Blank UPDATE privilege

GRANTEDTS TIMESTAMP Time when GRANT was executed
GRANTEDORTYPE CHAR(1) Type of grantor

L Role

Blank Authorization ID that is not a role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value

SYSIBM.SYSCOLDIST

Rows for cardinality, frequency, and histogram statistics for single column or a column group

Column name Data type Description
STATSTIME TIMESTAMP Date and time when RUNSTATS updated statistics
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
TBOWNER VARCHAR(128) Schema of table that contains column
TBNAME VARCHAR(128) Name of table that contains column
VARCAHR(128) Name of column
COLVALUE VARCHAR(2000) Data of a frequently occurring value
TYPE CHAR(1) Type of statistics gathered:

C Cardinality

F Frequent value

H Histogram statistics

N Nonpadded frequent value

CARDF FLOAT TYPE=’C’, number of distinct values for column group

TYPE=’H’, number of distinct values for column group in a quantile indicated by QUANTILENO

COLGROUPCOLNO VARCHAR(254) Set of columns associated with statistics
NUMCOLUMNS SMALLINT Number of columns associated with the statistics
FREQUENCYF FLOAT Percentage of rows in table with value specified in COLVALUE when the number is multiplied by 100
QUANTILENO SMALLINT Ordinary sequence number of quantile in whole consecutive value range, from low-to-high. Not updatable
LOWVALUE VARCHAR(2000) TYPE=’H’, lower bound for quantile in QUANTILENO. Not used if type is not ‘H’. Not updatable
HIGHVALUE VARCHAR(2000) TYPE=’H’, higher bound for quantile in QUANTILENO. Not used if type is not ‘H’. Not updatable

SYSIBM.SYSCOLDISTSTATS

Rows per partition for cardinality, frequency, and histogram statistics

Column name Data type Description
SMALLINT Not used
STATSTIME TIMESTAMP Date and time when RUNSTATS updated statistics
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
PARTITION SMALLINT Partition number for tablespace that contains table in which column is defined
TBOWNER VARCHAR(128) Schema of owner of table that contains column
TBNAME VARCHAR(128) Name of table that contains column
NAME VARCHAR(128) Name of column
COLVALUE VARCHAR(2000) Data of a frequently occurring value. FOR BIT DATA value has non-character data type, data may not be printable
TYPE CHAR(1) Type of statistics gathered:

C Cardinality

F Frequent value

H Histogram statistics

N Non-padded frequent value

CARDF FLOAT TYPE=C, number of distinct values for column group.

TYPE=N or F, number of rows or keys in partition for which FREQUENCYF value applies. TYPE=H, number of distinct values of column group in a quantile in QUANTILENO

COLGROUPCOLNO VARCHAR(254) Set of columns associated with statistics
NUMCOLUMNS SMALLINT Number of columns associated with the statistics
FREQUENCYF FLOAT Percentage of rows in table with value specified and COLVALUE when number is multiplied by 100
QUANTILENO SMALLINT Ordinary sequence number of a quantile in the whole consecutive value range, from low to high. Not updatable
LOWVALUE VARCHAR(2000) For TYPE=’H’, lower bound for quantile indicated by QUANTILENO. Not used if TYPE is not ‘H’. Not updatable
HIGHVALUE VARCHAR(2000) For TYPE=’H’, higher bound for quantile indicated by QUANTILENO. Not used if type is not ‘H’. Not updatable

SYSIBM.SYSCOLDIST_HIST

Contains rows from SYSCOLDIST

Column name Data type Description
STATSTIME TIMESTAMP Date and time when RUNSTATS updated statistics
TBOWNER VARCHAR(128) Schema of table that contains column
TBNAME VARCHAR(128) Name of table that contains column
NAME VARCHAR(128) Name of column. If NUMCOLUMNS is greater than 1, first column name of set of columns associated with statistics
COLVALUE VARCHAR(2000) Contains data of a frequently occurring value
TYPE CHAR(1) Type of statistics gathered:

C Cardinality

F Frequent value

H Histogram statistics

N Non-padded frequent value

CARDF FLOAT(8) TYPE=’C’, number of distinct values for column group

TYPE=’H’, number of distinct values for column group in quantile in QUANTILENO. -1 if statistics not gathered

COLGROUPCOLNO VARCHAR(254) Set of columns associated with statistics
NUMCOLUMNS SMALLINT Number of columns associated with statistics
FREQUENCYF FLAOT(8) Percentage of rows in table, value specified in COLVALUE when number is multiplied by 100
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
QUANTILENO SMALLINT Ordinary sequence number of a quantile in the whole consecutive value range, from low to high. Not updatable
LOWVALUE VARCHAR(2000) TYPE=’H’, the lower bound for the quantile indicated by QUANTILENO. Not used if TYPE is not ‘H’. Not updateable
HIGHVALUE VARCHAR(2000) TTYPE=’H’, the higher bound for the quantile indicated by QUANTILENO. Not used if type is not ‘H’. Not updatable

SYSIBM.SYSCOLSTATS

Contains partition statistics with selected columns

Column name Data type Description
HIGHKEY VARCHAR(2000) Highest value of column within partition
HIGH2KEY VARCHAR(2000) Second highest value of column within partition
LOWKEY VARCHAR(2000) Lowest value of column within partition
LOW2KEY VARCHAR(2000) Second lowest value of the column within the partition
COLCARD INTEGER Number of distinct column values in the partition
STATSTIME TIMESTAMP Date and time when RUNSTATS updated statistics
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
PARTITION SMALLINT Partition number for tablespace that contains table on which column is defined
TBOWNER VARCHAR(128) Schema or qualifier of the table that contains the column
TBNAME VARCHAR(128) Name of the table that contains the column
NAME VARCHAR(128) Name of the column
COLCARDDATA VARCHAR(1000) Internal use only
STATSFORMAT CHAR(1) Table statistics gathered:

Blank Statistics have not been collected or varchar column statistical values are padded

N VARCHAR column statistical values are not padded

An updateable column

SYSIBM.SYSCOLUMNS

Contains one row for every column in a table or view

Column name Data type Description
NAME VARCHAR(128) Name of the column
TBNAME VARCHAR(128) Name of the table or view that contains the column
TBCREATOR VARCHAR(128) Schema of the table or view that contains the column
COLNO SMALLINT Numerical place of the columns in the table or view
COLTYPE CHAR(8) Type of the column specified in the definition of the column
LENGTH SMALLINT length of column
SCALE SMALLINT If column type is DECIMAL, value represent scale
NULLS CHAR(1) Column contain null values:

N = no

Y = yes

Can be N for a view column derived from an expression or a function. Allows nulls when an outer select list refers to it

HIGH2KEY VARCHAR(2000) Second highest value of column
LOW2KEY VARCHAR(2000) Second lowest value of column
UPDATES CHAR(1) Whether the column can be updated:

N = no

Y = yes

The values is N the column is:

* Derived from a functional or expression

* A column that is defined with AS IDENTITY and GENERATED ALWAYS attributes.

Value can be Y for columns of a read-only view

IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
REMARKS VARCHAR(72) A character string provided by user with COMMENT ON
DEFAULT CHAR(1) Meaningful only if TYPE column for associated SYSTABLES row indicates a Table(T)or a created temporary table (G)
KEYSEQ SMALLINT Column’s numeric position within primary key. 0 if not part of a primary key
FOREIGNKEY CHAR(1) Applies to character or CLOB columns, indicates subtype of data:

B BIT data

M MIXED data

S SBCS data

Blank

FLDPROC CHAR(1) Column has a field procedure:

N No

Y Yes

Blank For a view defined prior to V7

LABEL VARCHAR(90) Column label provided by user with a LABEL ON; else blank
STATSTIME TIMESTAMP Date and time when RUNSTATS updated statistics
DEFAULTVALUE VARCHAR(1536) Meaningful only if column being described is for a table (TYPE column of associated SYSTABLES row is T for table or G for created temporary table)
COLCARDF FLOAT Estimated number of distinct values in column
COLSTATUS CHAR(1) Status of definition of a column:

I Definition is incomplete because a LOB tablespace, auxiliary table or index on an auxiliary table not created

Blank Definition of complete

LENGTH2 INTEGER Maximum length of the data retrieved from the column:

0 Not a LOB or ROWID column

40 For a ROWID column, the length of the returned value

1 to 2 147 483 647 bytes

For a LOB column, the maximum length

DATATYPEID INTEGER For a built-in data type, internal ID of built-in type. For a distinct type, internal ID of distinct type. If created prior to V6, value is 0
SOURCETYPEID INTEGER For a built-in data type, 0. For a distinct type, internal ID of distinct type upon which distinct type is based. If created prior to V6, value is 0
TYPESCHEMA VARCHAR(128) If COLTYPE is DISTINCT, schema of distinct type. Else SYSIBM
TYPENAME VARCHAR(128) If COLTYPE is DISTINCT, name of the distinct type. Else, value is same as value of COLTYPE column
CREATEDTS TIMESTAMP Timestamp when column was created
STATS_FORMAT CHAR(1) Type of statistics gathered:

Blank Statistics have not been collected or varchar column statistical columns are padded

N Varchar column statistical values are padded

An updatable column

PARTKEY_COLSEQ SMALLINT Numeric position of column within partitioning key of table. 0 if it is not part of partitioning key. Applicable only if table uses table controlled partitioning
PARTKEY_

ORDERING

CHAR(1) Order of column in partitioning key

A Ascending

D Descending

Blank Column is not used as part of a partitioning key

ALTERDTS TIMESTAMP Time stamp when the alter occurred
CCSID INTEGER CCSID of column
HIDDEN INTEGER Column is hidden

P Partially hidden

N Not hidden

RELCREATED CHAR(1) Release of DB2 that is used to create the object
CONTROL_ID INTEGER Internal identifier of column across control mask defined for this column. 0 if no column access control mask is defined for column
XML_

TYPEMOD_ID

INTEGER Idea of the XML type modifier

 

PERIOD CHAR(1) Indicates whether the column is the start or the end of the period for a SYSTEM_TIME or BUSINESS_TIME period:

B Start of period BUSINESS_TIME

C End of period BUSINESS_TIME with exclusive period

I End of period BUSINESS_TIME with inclusive period

S Start of period SYSTEM_TIME

T End of period SYSTEM_TIME

Blank Not used as start or the end of a period

GENERATE_

ATTR

CHAR(1) Indicates column generated attribute:

A Defined as GENERATED_ALWAYS

B Defined as GENERATEDBY_DEFAULT

blank Not applicable or value of DEFAULT column is A, D, E, F, I or J or defined from a prior release of DB2

HASHKEY_

COLSEQ

SMALLINT Contains numeric position within tables hash key
ENCODING_

SCHEME

CHAR(1) Encoding scheme of column.

A ASCII

E EBCDIC

U UNICODE

SYSIBM.SYSCOLUMNS_HIST

Contains rows from SYSCOLUMNS.

Column name Data type Description
NAME VARCHAR(128) Name of column
TBNAME VARCHAR(128) Name of table or view that contains the column
TBCREATOR VARCHAR(128) Schema or qualifier of the table or view that contains the column
COLNO SMALLINT Numeric place of the column in the table or view
COLTYPE CHAR(8) Type of column specified in definition of column
LENGTH SMALLINT Length of column
LENGTH2 INTEGER Maximum length of the data retrieved from the column

0 Not a LOB or ROWID column

40 For a ROWID column, length of the returned value

2 to 2 147 483 647 bytes. For a LOB column, maximum length

NULLS CHAR(1) Column can contain null values: N=No, Y=Yes
HIGH2KEY VARCHAR(2000) Second highest value of the column
LOW2KEY VARCHAR(2000) Second lowest value of the column
STATSTIME TIMESTAMP Date and time when RUNSTATS updated statistics
COLCARDF FLOAT Estimated number of distinct values in column
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
STATS_FORMAT CHAR(1) Type of statistics gathered

SYSIBM.SYSCONSTDEP

Records dependencies on check constraints or user-defined defaults for a column

Column name Data type Description
BNAME VARCHAR(128) Name of the object on which the dependency exists
BSCHEMA VARCHAR(128) Schema of the object on which the dependency exists
BTYPE CHAR(1) Type of object on which the dependency exists:

F=Function instance

DTBNAME VARCHAR(128) Name of the table to which the dependency applies
DTBCREATOR CHAR(8) Schema of the owner of the table to which dependency applies
DCONSTNAME VARCHAR(128) If DTYPE=’C’, unqualified name of check constraint. If DTYPE=’D’, a column name
DTYPE CHAR(1) Type of object:

C Check constraint

D User-defined default constraint

IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
DTBOWNER VARCHAR(128) Auth ID of owner of table or a zero if created prior to V9
OWNERTYPE CHAR(1) Type of owner:

blank Authorization ID

R Role

SYSIBM.SYSCONTEXT

Contains one row for each trusted context

Column name Data type Description
NAME VARCHAR(128) Name of the trusted context
CONTEXTID INTEGER Internal context ID
DEFINER VARCHAR(128) Authorization ID or role that defined the trusted context
DEFINERTYPE CHAR(1) Type of the definer:

L Role

Blank Authorization ID

SYSTEMAUTHID VARCHAR(128) Primary authorization ID used to establish connection
DEFAULTROLE VARCHAR(128) Name of the trusted context default role
OBEJECTOWNERTYPE CHAR(1) ROLE AS OBJECT OWNER on the trusted context

L ROLE AS OBJECT OWNER is specified

Blank ROLE AS OBJECT OWNER not specified

CREATEDTS TIMESTAMP Time when the trusted context is created
ALTEREDTS TIMESTAMP Time when the trusted context is last altered
ENABLED CHAR(1) Status of the trusted context:

Y Enabled

N Disabled

ALLOWPUBLIC CHAR(1) Connection is allowed to be reused for PUBLIC:

Y Connection reuse is allowed

N Connection reuse is not allowed

AUTHENTICATEPUBLIC CHAR(1) Authentication required for PUBLIC
RELCREATED CHAR(1) Release of DB2 that is used to create object
IBMREQD CHAR(1) Y indicates row camp from basic (MRM) tape
REMARKS VARCHAR(762) A character string that is provided COMMENT statement
DEFAULT_

SECURITYLABEL

VARCHAR(24) Name of the context default RACF security label
SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value

SYSIBM.SYSCONTEXTAUTHIDS

Contains one row for each authorization ID with which the trusted context can be used

Column name Data type Description
CONTEXTID INTEGER Internal trusted context ID
AUTHID VARCHAR(128) Primary auth ID that can be reuse a connection
AUTHENTICATE CHAR(1) Authentication is required for auth ID in AUTHID column:

Y Authentication token is required for auth ID. For local requests, token is the password. For remote requests, token can be password, a RACF passticket or a KERBEROS token

N Authentication is not required

ROLE VARCHAR(128) Role for the auth ID in AUTHID. Role supersedes default role defined for the trusted context
CREATEDTS TIMESTAMP Time when auth ID is added to the trusted context
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
SECURITYLABEL VARCHAR(24) RACF security label for AUTHID. Security label supersedes the default security label defined for context
SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value

SYSIBM.SYSCONTROLS

Contains one row for each row permission and column mask

Column name Data type Description
SCHEMA VARCHAR(128) Schema of row permission or column mask
NAME VARCHAR(128) Name of row permission or column mask
OWNER VARCHAR(128) Owner of row permission or column mask
OWNERTYPE CHAR(1) Type of the owner:

blank authorization ID

L Role

TBSCHEMA VARCHAR(128) Schema of table for which row permission or column mask is defined
TBNAME VARCHAR(128) Name of table for which row permission or column mask is defined
TBCORRELATION VARCHAR(128) If specified, correlation name of table for which row permission or column mask is defined. Else, empty
COLNAME VARCHAR(128) Column name for which column mask is defined
COLNO SMALLINT Column number for which column mask is defined
CONTROL_ID INTEGER Internal access control ID
CONTROL_TYPE CHAR(1) Type of access control object:

R Row permission

M Column mask

ENFORCED CHAR(1) Type of access enforced by row permission. Column mask always has a value of ‘A’.

A All Access

IMPLICIT CHAR(1) Row permission was implicitly created:

N Row permission was explicitly created or is a column mask

Y Row permission was implicitly created

ENABLE CHAR(1) Row permission or column mask is enabled:

N Not enabled

Y Enabled

STATUS CHAR(1) Status of row permission or column mask definition:

Blank Definition is complete

R Error occurred where an attempt was made to regenerate row permission or column mask

CREATEDTS TIMESTAMP Timestamp row permission or column mask was created
RELCREATED CHAR(1) Release of DB2 when row permission or column mask was created
ALTEREDTS TIMESTAMP Timestamp when row permission or column mask was last changed
REMARKS VARCHAR(762) Character string provided by COMMENT ON statement
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
ENVID INTEGER Internal identifier of the environment
ROWID ROWID Row identifier to support LOB columns in the table
RULETEXT CLOB(2M) Source text of search condition or expression portion of CREATE PERMISSION or CREATE MASK
DESCRIPTOR BLOB(2M) Internal description of row permission or column mask
SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value
REGENERATET TIMESTAMP(12) Time when object was regenerated

SYSIBM.SYSCOPY

Contains information needed for recovery

Column name Data type Description
DBNAME CHAR(8) Name of database
TSNAME CHAR(8) Name of the target tablespace or index space
DSNUM INTEGER Data set number with in tablespace
ICTYPE CHAR(1) Type of operation:

A ALTER

B REBUILD INDEX

C CREATE

D CHECK DATA LOG(NO) (no log records for the range are available for RECOVER utility)

E RECOVER (to current point)

F COPY FULL YES

I COPY FULL NO

J RECORD TABLESPACE or LOAD REPLACE compression dictionary write to log

L SQL (type of operation)

M MODIFY RECOVERY utility

P RECOVER TO COPY or RECOVER TORBA (partial recovery point)

Q QUIESCE

R LOAD REPLACE LOG(YES)

S LOAD REPLACE LOG(NO)

V REPAIR VERSIONS utility

W REORG LOG(NO)

X REORG LOG(YES)

Y LOAD LOG(NO)

Z LOAD LOG(YES)

T TERM UTILITY command (terminated utility)

START_RBA CHAR(10) 80-bit positive integer for RBA/LRSN of a point in recovery log
FILESEQNO INTEGER Tape file sequence number of the copy
DEVTYPE CHAR(8) Device type the copy is on
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
DSNAME CHAR(44) For ICTYPE=’P’ (RECOVER TOCOPY only), ‘I’, or ‘F’, contains data set name
SHRLEVEL CHAR(1) SHRLEVEL parameter on COPY for (ICTYPE F or I only):

C Change

R Reference

blank Does not describe an image copy or was from V 1.1

DSVOLSER VARCHAR(1784) Volume serial numbers of the data set
TIMESTAMP TIMESTAMP Date and time row was inserted
ICBACKUP CHAR(2) Type of image copy contained in data set:

Blank LOCALSITE primary copy

FC FlashCopy copy

LB LOCALSITE backup copy

RP RECOVERYSITE primary copy

RB RECOVERYSITE backup copy

ICUNIT CHAR(1) Media that image copy data set is stored on:

D DASD

T Tape

Blank

STYPE CHAR(1) When ICTYPE=A

A Partition was added to a table

B MEMBER CLUSTER was changed

C Column was added to a table and an index in different commit scopes, or column was dropped from a table

D DSSIZE attribute of the table space was altered

E Data set numbers of a base table and its associated clone table are exchanged

F Page size attribute of table space or index was altered

G Index was regenerated

I Inline attribute of LOB column was altered by REORG

L Logging attribute was altered to NOT LOGGED

M MAXPARTITIONS attribute was altered

N An index was altered to not padded

O Logging attribute was altered to NOT LOGGED P Index was altered to padded

R Table was altered to rotate partitions

S SEGSIZE attribute of the table space was altered

V Column was altered for numeric data type change and the column is in an index

X REORG dropped one or more empty partitions from the related table space

Z Column in key of an index that was versioned prior to DB2 V8 was altered

When ICTYPE=C:

L Logging attribute was altered to LOGGED

O Logging attribute was altered to NOT LOGGED

When ICTYPE=E:

B RECOVER utility with BACKOUT

blank RECOVER utility without BACKOUT

When ICTYPE=F, the values are:

C DFSMS concurrent copy (‘I’ instance of table space)

J DFSMS concurrent copy (‘J’ instance of tablespace)

N A FlashCopy copy is not consistent

Q Sequential copy is consistent

S LOAD REPLACE(NO)

T FlashCopy is consistent

U Sequential copy is not consistent

V ALTER INDEX NOT PADDED

W REORG LOG(NO)

X REORG LOG(YES)

blank DB2 image copy

When ICTYPE=L

M Mass DELETE, TRUNCATE TABLE, or DROP TABLE. LOWDSNUM contains table OBID of affected table

B Recover to a point in time with BACKOUT YES option ran

C Recover to a point in time without logonly with consistency

L Recover to a point in time logonly without consistency

M Recover to a point in time using logonly with consistency

blank Recover to a point in time without logonly without consistency

When ICTYPE=R or S, the values are:

A Resetting REORG pending status

T First materialized default value for a row change timestamp column

F COPY FULL YES

I COPY FULL NO

When ICTYPE=W or X:

A Restting REORG pending status or REBALANCE

H Hash organization attributes of the table were altered

T First materializing the default value for a row change timestamp column

For other values ICTYPE, the value is blank

PIT_RBA CHAR(10) ICTYPE=P, contains LRSN for point in DB2 log
GROUP_NAME CHAR(8) Member name of DB2 subsystem that performed the operation
OTYPE CHAR(1) Type object at the recovery information is for:

I Index space

T Tablespace

LOWDSNUM INTEGER Partition number of lowest partition in range for SYSCOPY records created for REORG and LOAD REPLACE for resetting a REORG pending status
HIGHDSNUM INTEGER Partition number of highest partition in range
COPYPAGESF FLOAT(8) Number of pages written to copy data set
NPAGESF FLOAT(8) Number of pages in table space or index at time of COPY
CPAGESF FLOAT(8) Total number of changed pages
JOBNAME CHAR(8) Job name of the utility
AUTHID CHAR(8) Authorization ID of the utility
OLDEST_VERSION SMALLINT When ICTYPE= B, F, I, S, W, or X, version number of oldest format of data for an object. Else, value -1
LOGICAL_PART INTEGER Logical partition number
LOGGED CHAR(1) Logging attribute of table space at time SYSCOPY record is written:

Y LOGGED

N NOT LOGGED

Blank Row was inserted prior to VP

TTYPE CHAR(8) When ICTYPE=A and STYPE=B, indicates if previous value for the MEMBER CLUSTER attribute is being used:

Y Previous member cluster attribute is used

N Previous member cluster attribute is not being used

When ICTYPE=A and STYPE=C, indicates if a column is added or dropped from a table:

blank A column was added to a table

D A column was dropped from a table

CMP=N index compression activated

CMP=Y index compression deactivated

When ICTYPE=A and STYPE=I

D REORG decremented the inline length of LOB column

I REOG incremented the inline length of LOB column

n Previous value of MAXPARTITIONS attribute for table space

S Table space was converted from single-table segmented table space to a partition-by-growth universal table space

When ICTYPE=A and STYPE=P

ABSOLUTE – table space converted from absolute to relative page numbering.

When ICTYPE=A and STYPE=S

n Previous value of SEGSIZE attribute for table space

P Table space was converted from a partitioned table space to a range-partitioned universal table space

When ICTYPE=E

blank Full recovery reset the object

N Full recovery did not reset the object

When ICTYPE=F and STYPE=N, Q, T, or U,

A LOAD RESUME LOG NO

B REBUILD

C COPY

D LOAD RESUME LOG YES

E LOAD SHRLEVEL CHANGE

L LOAD

P REPAIR

R LOAD RPELACE LOG YES

S LOAD REPLACE LOG NO

T COPYTOCOPY

W REORG TABLESPACE LOG NO

X REORG TABLESPACE LOG YES

When ICTYPE=P, R, S, W, X

B RBA or LRSN format changed to 6-byte format

BRF Basic row format

BRF I Basic row format, and FORMAT INTERNAL used

E RBA or LRSN format changed to extended 10-byte

F REORG utility was run with

RRF Reordered row format

RRF I Reordered row format and FORMAT INTERNAL used

S REORG utility was run with FASTSWITCH NO option

When ICTYPE=M and STYPE=R

blank MODIFY RECOVERY deleted rows from SYSLGRRNX

N MODIFY RECOVERY did not delete rows from SYSLGRNX

ICTYPE=T, TTYPE of B

ICTYPE=W or X and STYPE=H

ICTYPE=Y or Z

Blank indicates that the FORMAT INTERNAL option was not specified during LOAD

I indicates that the FORMAT INTERNAL option was specified during LOAD

ICTYPE=A-A, A-R, B, C, P, R, S, W, or X

B Page format was converted to basic page format with 6 byte RBA or LRSN values

E Page format was converted to extended page format with 10-byte RBA or LRSN values

When ICTYPE=A and STYPE=A or R:

B Page format was converted to basic page format with 6 byte RBA or LRSN values

E Page format was converted to extended page format with 10-byte RBA or LRSN values

INSTANCE SMALLINT When STYPE=E and ICTYPE=A, INSTANCE indicates data set instance number of a base object after an EXCHANGE statement completes
RELCREATED CHAR(1) Release used to create the object. blank if created prior to V9
MODECREATED CHAR(2) Latest mode to which DB2 subsystem had been migrated when the SYSCOPY record was written:

C Conversion mode

E Enabling-new-function mode

N New-function mode

SYSIBM.SYSCTXTTRUSTATTRS

Contains one row for each list of attributes for a given trusted context

Column name Data type Description
CONTEXTID INTEGER Internal trusted context ID
NAME VARCHAR(128) Name of trust attribute
VALUE VARCHAR(254) Value of trust attribute
CREATEDTS TIMESTAMP Time when the attribute is created
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
SYS_START TIMESTAMP(12) Start time associated with the most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value

SYSIBM.SYSDATABASE

Contains one row for each database, except for database DSNDB01

Column name Data type Description
NAME VARCHAR(24) Database name
CREATOR VARCHAR(128) Authorization ID of the owner of the database
STGROUP VARCHAR(128) Name of default storage group of database
BPOOL CHAR(8) Name of default buffer pool of tablespace
DBID SMALLINT Internal identifier of database
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
CREATEDBY VARCHAR(128) Prime authorization ID of user who created database
TYPE CHAR(1) Type of database:

blank Not a work file database or a TEMP database

W A work file database. Database is DSNDB07, or it was created with WORKFILE clause and used as a work file database by a data-sharing member

GROUP_MEMBER VARCHAR(24) Member name of DB2 subsystem using the work file database
CREATEDTS TIMESTAMP Time when CREATE statement is executed
ALTEREDTS TIMESTAMP Time when most recent ALTER DATABASE statement was applied
ENCODING_

SCHEME

CHAR(1) Default encoding scheme for database:

E EBCDIC

A ASCII

U UNICODE

blank For DSNDB04, a work file database and a TEMP database

SBCS_CCSID INTEGER Default SBCS CCSID for the database
DBCS_CCSID INTEGER Default DBCS CCSID for the database
MIXED_CCSID INTEGER Name of the default buffer pool for indexes
INDEXBP CHAR(8) Database was implicitly created:

Y Implicitly created

N Explicitly created

CREATORTYPE CHAR(1) Type of creator:

Blank Authorization ID

L Role

RELCREATED CHAR(1) Release of DB2 that is used to create the object

SYSIBM.SYSDATATYPES

Contains one row for each user defined-type defined to the system

Column name Data type Description
SCHEMA VARCHAR(128) Schema of the data type
OWNER VARCHAR(128) Owner of the data type
NAME VARCHAR(128) Name of the data type
CREATEDBY VARCHA(128) Primary authid under which the data type was created
SOURCESSCHEMA VARCHAR(128) Schema of the source data type
SOURCETYPE VARCHAR(128) Name of the source type
METATYPE CHAR(1) Class of data type:

A User-defined ordinary array type

L User-defined associative array type

T Distinct type

DATATYPEID INTEGER Internal identifier of the data type
SOURCETYPEID INTEGER Internal ID of the built-in data type upon which the distinct type or array elements are based
LENGTH INTEGER Maximum length of a data type based on DECIMAL data type. Can be distinct type or an array type
SCALE SMALLINT Scale of decimal data type
SUBTYPE CHAR(1) Sub type of data type, if source type is one of the character types. Data type can be distinct type or array type

B FOR BIG DATA

S FOR SBCS DATA

M FOR MIXED DATA

Blank Source type is not a character type

CREATEDTS TIMESTAMP Time when the data type was created
ENCODING_SCHEME CHAR(1) Encoding scheme of the distinct type:

A ASCII

E EBCDIC

U UNICODE

IBMREQD CHAR(1) Y indicates the row came from the (MRM) tape
REMARKS VARCHAR(762) A character string provided by ser with COMMENT ON
OWNERTYPE CHAR(1) Type of owner:

blank Authorization ID

L Role

RELCREATED CHAR(1) Release of DB2 that is used to create the object
INLINE_LENGTH INTEGER In line length attribute of type if based on LOB source type:

-1 Type does not specify INLINE LENGTH

greater than or equal to 0

Inline length attribute (in byte) of the type if it is based on LOB source type

ARRAYLENGTH BIGINT Maximum cardinality, if data type is an array type. For all other data types value is 0
ARRAYINDEXTYPEID INTEGER Data type of index, if the data type is an associative array type. For all other data types, value is 0
ARRAYINDEXTYPELEN BIGINT Max length of an array index, if data types is an associative
ARRAYINDEXSUBTYPE CHAR(1) Subtype of the array index:

B FOR BIT DATA

S FOR SBCS DATA

M FOR MIXED DATA

blank Array index is not a character type

SYSIBM.SYSDAUTH

Records the privileges that are held by users over databases

Column name Data type Description
GRANTOR VARCHAR(128) Auth ID or role of user who granted privileges
GRANTEE VARCHAR(128) Application ID of user who holds privileges
NAME VARCHAR(24) Database name
GRANTEETYPE CHAR(1) Type of owner:

blank Authorization ID

L Role

AUTHHOWGOT CHAR(1) Authorisation level of user from whom privileges were received. Not necessarily highest authorization level of grantor.

Blank Not applicable

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

S SYSADM

CREATETABAUTH CHAR(1) GRANTEE can create tables within the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

CREATETSAUTH CHAR(1) GRANTEE can create tablespaces within the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

DBADMAUTH CHAR(1) GRANTEE can has DBADM authority over database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

DBCTRLAUTH CHAR(1) GRANTEE can has DBCTRL authority over database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

DBMAINTAUTH CHAR(1) GRANTEE can has DBMAINT authority over database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

DISPLAYDBAUTH CHAR(1) GRANTEE can issue the DISPLAY command for the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

DROPAUTH CHAR(1) GRANTEE can issue ALTER and DROP DATABASE statement:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privilege is held without the GRANT option

IMAGCOPYAUTH CHAR(1) GRANTEE can use the COPY, MERGECOPY, MODIFY and QUIESCE utilities on the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

LOADAUTH CHAR(1) GRANTEE can use LOAD utility to load tables in the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

REORGAUTH CHAR(1) GRANTEE can use the REORG utility to reorganize tablespaces an indexes on the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

RECOVERDBAUTH CHAR(1) GRANTEE can use the RECOVER and REPORT utilities on tablespaces in the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

REPAIRAUTH CHAR(1) GRANTEE can use the DIAGNOSE and REPAIR utilities on tablespaces and indexes in the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

STARTDBAUTH CHAR(1) GRANTEE can use START command against the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

STATSAUTH CHAR(1) GRANTEE can use CHECK and RUNSTATS utilities against the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

STOPAUTH CHAR(1) GRANTEE can issue the STOP command against the database:

blank Privilege is not held

G Privilege held with the GRANT option

Y Privileges held without the GRANT option

IBMREQD CHAR(1) Y indicates row came from (MRM) tape
GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
GRANTORTYPE CHAR(1) Indicates the type of owner:

Blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Delete time deleted from system period temporal table
TRANS_START TIMESTAMP(12) Unique timestamp per transaction or null value

SYSIBM.SYSDBRM

Contains one row for each DBRM of each application plan

Column name Data type Description
NAME VARCHAR(24) Name of the DBRM
TIMESTAMP CHAR(8) Consistency token
PDSNAME CHAR(132) Name of the partitioned data set of which the DBRM is a member
PLNAME VARCHAR(24) Name of the application plan of which this DBRM is a part
PLCREATOR VARCHAR(128) Authorization ID of the owner of the application plan
QUOTE CHAR(1) SQL string delimiter for the SQL statements in the DBRM:

N Apostrophe

Y Quotation mark

COMMA CHAR(1) Decimal point representation for SQL statements in the DBRM:

N Period

Y Comma

HOSTLANG CHAR(1) The host language used:

B Assembler language

C OS/VS COBOL

D C

F Fortran

P PL/I

2 VS COBOL II or IBM COBOL R1

3 IBM COBOL (Release 2 or subsequent releases)

4 C++

IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
CHARSET CHAR(1) System CCSID for SCBS data was 290 (Katakuna) when program was precompiled:

A No

K Yes

MIXED CHAR(1) Mixed data was in effect when program was precompiled

N No

Y Yes

DEC31 CHAR(1) DEC31 was in effect when program was precompiled

Blank No

Y Yes

VERSION VARCHAR(122) Version identifier for the DBRM
PRECOMPTS TIMESTAMP Time when DBRM was precompiled
PLCREATOR

TYPE

CHAR(1) Indicates the type of creator:

Blank Authorization ID

L Role

RELCREATED CHAR(1) The release of DB2 that is used to create the object

SYSIBM.SYSDEPENDENCIES

Records the dependencies between objects

Column name Data type Description
BNAME VARCHAR(128) Name of the object on which another object is dependent
BSCHEMA VARCHAR(128) Schema/qualifier of object on which another object is dependent
BCOLNAME VARCHAR(128) Column name of object on which another object is dependent
BCOLNO SMALLINT Column number of object on which another object is dependent
BTYPE CHAR(1) The type of object that is identified by BNAME, BSCHEMA, and BCOLNAME:

C Column

E INSTEAD OF trigger

F Function

G Global temporary table

I Index

M Materialized query table

O Procedure

P Partitioned table space

Q Sequence

R Table space

S Synonym

T Table

U Distinct type

V View

W SYSTEM_TIME period

Z BUSINESS_TIME period

0 zero

BOWNER VARCHAR(128) Auth ID of owner of object on which another object is dependent
BOWNERTYPE CHAR(1) Type of creator of object on which another object is dependent:

L Role

Blank Authorization ID that is not a role

DNAME VARCHAR(128) Name of the object that has dependencies on another object
DSCHEMA VARCHAR(128) Schema or qualifier of object that has dependencies on another
DVERSION VARCHAR(122) Version identifier of object identified by DSCHEMA and DNAME if the object has a version
DCOLNAME VARCHAR(128) Column name of object that has dependencies on another object
DCOLNO SMALLINT Column number of object that has dependencies on another
DTYPE CHAR(1) Type of object identified by DNAME, DSCHEMA, DCOLNAME:

B Trigger package for basic trigger

C Generated column

F Function

I Index

M Materialized query table

O Procedure

V View

X Row permission

Y Column mask

1 Trigger package for advanced trigger

DOWNER VARCHAR(128) Auth ID of owner of object with dependencies on another object
DOWNERTYPE CHAR(1) Type of creator of object with dependencies on another object:

L Role

Blank Authorization ID

IBMREQD CHAR(1) Y indicates row came from basis (MRM) tape
BAUTH SMALLINT Privilege held on object on which another object is dependent

SYSIBM.SYSDUMMY1

Contains one row in an EBCDIC table space

Column name Data type Description
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.SYSDUMMYA

Contains one row in ASCII table space

Column name Data type Description
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.SYSDUMMYE

Contains one row in EBCDIC table space

Column name Data type Description
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.SYSDUMMYU

Contains one row in a UNICODE table space

Column name Data type Description
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.SYSDYNQRY

Contains information for stabilization of access path for dynamic SQL statement

Column name Data type Description
SDQ_STMT_ID BIGINT Identifier of stabilized dynamic query
STBLGRP VARCHAR(128) Name of stabilization group
COPYID SMALLINT Copy type of stabilized runtime structures for query

0 Current copy

4 Invalid copy

CURSQLID VARCHAR(128) Current SQLID for stabilized dynamic query
CURSCHEMA VARCHAR(128) Current schema for stabilized dynamic query
CURAPPLCOMPAT VARCHAR(10) Current application compatibility for stabilized dynamic query
QUERY_HASH CHAR(16) Hash key generated by SQL statement text for stabilized dynamic query
QUERY_HASH_

VERSION

INTEGER Version of the query hash
VALID CHAR(1) Whether stabilized dynamic query is valid

A – ALTER changed table or base table of view

H – ALTER of object created prior to DB2 V5

N – Stabilized access path is not valid

Y – Stabilized access path for dynamic query is valid

LASTUSED DATE Date query that uses stabilized runtime structures was last run
RELBOUND CHAR(1) DB2 release when query was stabilized
GROUP_MEMBER VARCHAR(24) Date sharing member name that updates row
STBLTIME TIMESTAMP Timestamp when statement was stabilized
ROWID ROWID Internal use
STMTTEXT CLOB(2M) Text of SQL statement and any attribute string
FUNCTION_LVL VARCHAR(10) Function level of query when row was inserted

SYSIBM.SYSDYNQRYDEP

Contains information for dependencies for dynamic query packages

Column name Data type Description
SDQ_STMT_ID BIGINT Identifier of stabilized dynamic query
COPYID SMALLINT Copy type of stabilized runtime structures for query

0 Current copy

1 Previous copy

2 Original copy

BQUALIFIER VARCHAR(128) Valued depends on type of object

BTYPE=R – value is database

BTYPE=B or C – value is table

BTYPE=F, O or Q – value is schema name

Blank if BNAME is role

Else value is schema of BNAME

BNAME VARCHAR(128) Name of object query depends on
BTYPE CHAR(1) Type of object

B BUSINESS_TIME

C SYSTEM_TIME

E INSTEAD OF trigger

F UDF or cast function

G Global temporary table

I Index

M materialized query table

O Stored procedure

P Partitioned tablespace with LARGE or DSSIZE

Q Sequence object

R Table space

S Synonym

T Table

U Distinct type

V View

W SYSTEM_TIME period

Z BUSINESS_TIME period

0 Alias

CLASS CHAR(1) A Authorization dependency

D DDL dependency

BAUTH SMALLINT Privilege held on object if CLASS = A

50 SELECTAUTH

51 INSERTAUTH

52 DELETEAUTH

53 UPDATEAUTH

64 EXECUTE AUTH

263 USAGEAUTH

291 READAUTH

292 WRITEAUTH

0 not used – CLASS = D

AUTHID_TYPE CHAR(1) Type of authorization

Blank Value of CLASS = D or CLASS = A (authid)

L AUTHID contains name of role

AUTHID VARCHAR(128) Owner of privilege on object query is dependent
DBNAME VARCHAR(128) Database on which user or role holds DBADM authority
BADMINAUTH VARCHAR(128) Authority that allowed access on object

B SDBAMAUTH

D DBADMAUTH

G ACCESSCTRLAUTH

K SQL ADMCUTH

L SYSCTRLAUTH

S SYSADMAUTH

T DATAACCESSAUTH

Blank authority not held

PUBLICAUTH CHAR(1) Y privilege held by public

Blank privilege not held by public or CLASS = D

ALLOBJAUTH CHAR(1) Y privilege held on all objects within schema

Blank privilege not held on all objects or CLASS = D

QUERYHASH BINARY(16) Hash key of statement text if CLASS = D

SYSIBM.SYSDYNQRY_EXPL

Contains internal information for stabilized dynamic SQL statement

Column name Data type Description
DATA2 BLOB(2G) Internal use only

SYSIBM.SYSDYNQRY_OPL

Contains internal information for stabilized dynamic SQL statement

Column name Data type Description
DATA4 BLOB(2G) Internal use only

SYSIBM.SYSDYNQRY_SHTEL

Contains internal information for stabilized dynamic SQL statement

Column name Data type Description
DATA3 BLOB(2G) Internal use only

SYSIBM.SYSDYNQRY_SPAL

Contains internal information for stabilized dynamic SQL statement

Column name Data type Description
DATA1 BLOB(2G) Internal use only

SYSIBM.SYSDYNQRY.TXTL

Contains internal information for stabilized dynamic SQL statement

Column name Data type Description
STMTTEXT CLOB(2M) Text of SQL statement

SYSIBM.SYSENVIRONMENT

Records the environment variables when an object is created

Column name Data type Description
ENVID INTEGER Internal identifier of the environment
CURRENT_SCHEMA VARCHAR(128) The current schema
RELCREATED CHAR(1) The release when the environment information is created
PATHSCHEMAS VARCHAR(2048) The schema path
APPLICATION_

ENCODING_CCSID

INTEGER The CCSID of the application environment
ORIGINAL_

ENCODING_CCSID

INTEGER The original CCSID of the statement text string
DECIMAL_POINT CHAR(1) The decimal point indicator:

C Comma

P Period

MIN_DIVIDE_

SCALE

CHAR(1) The minimum divide scale:

N The usual rules apply for decimal division in SQL

Y Retain at lease three digits to the right of the decimal point after any decimal division

STRING_

DELIMITER

CHAR(1) The string delimiter that is used in COBOL string constraints:

A Apostrophe (‘)

Q Quote (“)

SQL_STRING_

DELIMITER

CHAR(1) The SQL string that is used in string constraints:

A Apostrophe (‘)

Q Quote (“)

MIXED_DATA CHAR(1) Uses mixed DBCS data:

N No mixed data

Y Mixed data

DECIMAL_

ARITHMETIC

CHAR(1) Rules used for CURRENT PRECISION and when both operands in decimal operation have precision of 15 or less:

1 DEC15 specifies that the rules do not allow a precision greater than 15 digits

2 DEC31 specifies that the rules allow a precision of up to 31 digits

DATE_FORMAT CHAR(1) The date format:

I ISO-yyyy-mm-dd

J JIS-yyyy-mm-dd

U USA-mm/dd/yyyy

E EUR-dd.mm.yyyy

L Locally defined by an installation exit routine

TIME_FORMAT CHAR(1) The time format:

I ISO-hh.mm.ss

J JIS-hh.mm.ss

U USA-hh:mm AM or hh:mm PM

E EUR-hh.mm.ss

L Locally defined by an installation exit routine

FLOAT_FORMAT CHAR(1) The floating point format:

I IEEE floating point format

S System/390 floating point format

HOST_LANGUAGE CHAR(8) The host language: ASM,C,CPP,IBMCOB,PLI,FORTRAN
CHARSET CHAR(1) The character set:

A Alphanumeric

FOLD CHAR(1) Applicable when HOST_LANGUAGE is C or CPP

N Lower case letters in SBCS ordinary identifiers are not folded to uppercase

Y Lower case letters in SBCS ordinary identifiers are folded to uppercase

Blank Not applicable

IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
ROUNDING CHAR(1) Rounding mode that is used when arithmetic and casting operations are performed on DECFLOAT data:

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP

CREATEDTS TIMESTAMP Time the row was created
APPLCOMPAT VARCHAR(10) Application compatibility associated with the environment

SYSIBM.SYSFIELDS

Contains one row for every column that has a field procedure

Column name Data type Description
TBCREATOR VARCHAR(128) Schema or qualifier the table that contains the column
TBNAME VARCHAR(128) Name of the table that contains the column
COLNO SMALLINT Numeric place of this column in the table
NAME VARCHAR(128) Name of a column
FLDTYPE VARCHAR(24) Data type of the encoded values in the field
LENGTH SMALLINT Length attribute field; or, for a decimal field, its prescision
SCALE SMALLINT Scale if FLDTYPE is DECIMAL; otherwise the value is 0
FLDPROC VARCHAR(24) For field procedure, name of the procedure
WORKAREA SMALLINT For field procedure, the size, in bytes, of the work area
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape
EXITPARML SMALLINT Length of the field procedure parameter value block
PARMLIST VARCHAR(735) Parameter list following FIELDPROC
EXITPARM VARCHAR(1530) Parameter value block of field procedure

SYSIBM.SYSFOREIGNKEYS

Contains one row for every column of every foreign key

Column name Data type Description
CREATOR VARCHAR(128) Authorization ID of the owner of the table that contains the column
TBNAME VARCHAR(128) Name of the table that contains the column
RELNAME VARCHAR(128) Constraint name for constraint which column is part of foreign key
COLNAME VARCHAR(128) Name of the column
COLNO SMALLINT Numeric place of the column in its table
COLSEQ SMALLINT Numeric place of the column in the foreign key
IBMREQD CHAR(1) Y indicates row came from basic (MRM) tape

SYSIBM.SYSINDEXCONTOL

Contains one row time windows to control the use of memory allocated for an index

Column name Data type Description
SSID CHAR(4) DBb2 subsystem. If data showing value is null and applies to all
PARTITION SMALLINT Partition number. Null applies to all
IXNAME VARCHAR(128) Name of index
IXCREATOR VARCHAR(128) Schema of index
TYPE CHAR(1) Purpose for which memory is used

F Fast Index Traversal(FTB)

ACTION CHAR(1) Action been performed

F Force FTB creation

D Disable FTB creation

A Automatic FTB creation

MONTH_WEEK CHAR(1) Meaning of value of DAY column

M Day of month

W Day of week

MONTH SMALLINT Months during which time window applies. Values 1-12
DAY SMALLINT Day of month or day of week for which time window applies
FROM_TIME TIME Time of day at which time window begins
TO_TIME TIME Time of day at which time window ends

SYSIBM.SYSINDEXCLEANUP

Specifies the time windows to control index cleanup processing

Column name Data type Description
DBNAME VARCHAR(24) The name of the database that contains the index space
INDEXSPACE VARCHAR(24) The name of the index space
ENABLE_DISABLE CHAR(1) Enables or disables cleanup for the specified index space

‘E’ Enabled

‘D’ Disabled

MONTH_WEEK CHAR(1) Indicates meaning of the value of the DAY column

‘M’ Day of the month

‘W’ Day of the week

MONTH SMALLINT Month in which time window applies. 1-12 = January-December..

G Yes, and it is an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT

X Yes, and it is an index used to enforce the uniqueness of values in a column that contains XML data

DAY SMALLINT Day of month or day of the week for which time window applies, as specified by value of MONTH_WEEK
START_TIME TIME Local time at beginning of the time window specified by row
END_TIME TIME Local time at end of the time window specified by the row

SYSIBM.SYSINDEXES

Contains one row for every index

Column name Data type Description
NAME VARCHAR(128) Name of the index
CREATOR VARCHAR(128) Schema of the index
TBNAME VARCHAR(128) Name of the table on which the index is identified
TBCREATOR VARCHAR(128) Schema of the table
UNIQUERULE CHAR(1) Whether the index is unique:

C Yes, and it is used to enforce uniquenessof a UNIQUE constraint or hash key columns

D No (duplicates are allowed)

U Yes

P Yes, and it is a primary index

C Yes, it is an index used to enforce UNIQUE constraint

N Yes, and it is defined with UNIQUE WHERE

R Yes, it is an index used to enforce uniqueness of a non-primary parent key

G Yes, and it is as an index used to enforce the uniqueness of values in a column defined as ROWID GENERATED BY DEFAULT

X Yes, and it is an index used to enforce the uniqueness of values in a column that contains XML data

COLCOUNT SMALLINT The number of columns in the key
CLUSTERING CHAR(1) Whether CLUSTER was specified when index was created:

N No

Y Yes

CLUSTERED CHAR(1) Whethere the table is actually clustered by the index:

N A significant number of rows are not in clustering order, or statistics have not been gathered

Y Most of the rows are in clustering order

blank Not applicable

An updateable column that can be changed by RUNSTATS.

For sparse index, is based on actual contents of index

DBID SMALLINT Internal identifier of the database
OBID SMALLINT Internal identifier of the index fan set descriptor
ISOBID SMALLINT Internal identifier of the index page set descriptor
DBNAME VARCHAR(24) Name of the database that contains the index
INDEXSPACE VARCHAR(24) Name of the index space
NLEAF INTEGER Number of active leaf pages in the index
NLEVELS SMALLINT Number of levels in the index tree. An updatable column
BPOOL CHAR(8) Name of the buffer pool used for the index
PGSIZE SMALLINT Size, in KB, of the leaf pages in the index. 4, 8, 16 or 32
ERASERULE CHAR(1) Data sets are erased when dropped. Meaningless if the index is partitioned: N No, Y Yes
CLOSERULE CHAR(1) Data sets are candidates for closure when limit on number of open data sets is reached: N No, Y Yes
SPACE INTEGER Number of kilobytes of DASD storage allocated to the index, as determined by the last execution of the STOSPACE utility
IBMREQD CHAR(1) Y indicates row came MRM tape
CLUSTERRATIO SMALLINT Percentage of rows in clustering order
CREATEDBY VARCHAR(128) Primary authorization ID of the user who created the index
STATSTIME TIMESTAMP Date and time when last invocation of RUNSTATS
INDEXTYPE CHAR(1) 2 Type 2 index or hash overflow index on non-partitioned tables

blank Type 1 index

D Data partition secondary index

P Both partitioned and is a partitioning index

FIRSTKEYCARDF FLOAT Number of distinct values of first key column. Is an estimate if updated while collecting statistics on a single partition. -1 if statistics have not been gathered. An updateable column. For sparse index, is based on actual contents of index
FULLKEYCARDF FLOAT Number of distinct values of key
CREATEDTS TIMESTAMP Time when CREATE was executed for index
ALTEREDTD TIMESTAMP Time when most recent ALTER INDEX was executed
PIECESIZE INTEGER Maximum size of a data set in kilobytes for NPIs
COPY CHAR(1) COPY YES was specified for index. Index can be copied and SYSLGRNX recording is enabled for the index

N No

Y Yes

COPYLRSN CHAR(10) Value can be either an RBA or LRSN
CLUSTERRATIOF FLOAT Percentage of rows that are in clustering order (x 100)
SPACEF FLOAT(8) Kilobytes of DASD storage
REMARKS VARCHAR(762) A character field string provided by user with COMMENT ON
PADDED CHAR(1) Whether keys within index will be padded for varying-length column data

Y Index is padded

N Index is not padded

Blank Index does not contain varying length or graphic data

VERSION SMALLINT Version of data row format for this index
OLDEST_VERSION SMALLINT Version number describing the oldest format of the data in the index space and any image copies of the index
CURRENT_

VERSION

SMALLINT Version number describing the newest format of data in the index space
RELCREATED CHAR(1) Release of DB2 use to the object. Blank if before V8
AVGKEYLEN INTEGER Average key length within the index
KEYTARGET_

COUNT

SMALLINT Number of key-targets for an extended index
UNIQUE_COUNT SMALLINT Number of columns or key targets that make up the unique constraint if an index, when other non-constraint enforcing columns or key-targets exist. Otherwise value is 0
IX_EXTENSION_

TYPE

CHAR(1) Identifies the type of extended index:

blank Simple index

S Index on a scalar expression

N Node ID index

T Spatial index

V XML index

COMPRESS CHAR(1) Indicates for the index compression is active:

N Index compression is not active

Y Index compression is active

OWNER VARCHAR(128) Authorization ID of the owner of the index
OWNERTYPE CHAR(1) Indicates the type of owner:

blank Authorization ID

L Role

DATAREPEAT

FACTOROF

FLOAT Anticipated number of data pages that will be touched following an index key order
ENVID INTEGER Internal environment identifier
HASH CHAR(1) Hash overflow index for a hash table

N No. (default)

Y Yes

SPARSE CHAR(1) Index is sparse or not

N No. (default) Every data row has an index entry

Y Yes. Index might not have an entry for each data row in the table

X Excluded, Index will not have an index entry when every data row for a key column contains the NULL value

ROWID ROWID ROWID column, created for the lob columns in this table
DSSIZE INTEGER Maximum size in KB of partitioned index data set. 0 for NPI
PAGENUM CHAR(1) Format of page numbers for index

A Absolute

R Relative

PARTKEYCOLUMN SMALLINT Not used
STATUS CHAR(1) Not used
INDEXSTATUS SMALLINT Not used
PARTITIONS VARCHAR(765) Not used
PQTY INTEGER For user managed data sets. Value is primary space allocation
STORTYPE CHAR(1) Type of storage allocation

E Explicit(storage group not used)

I Implicit(storage group used)

STORNAME VARCHAR(128) Name of storage group used for space allocation
VCATNAME VARCHAR(24) Name of ICF catalogue used for space allocation
FREEPAGE SMALLINT Number of pages loaded before page is left free
PCTFREE SMALLINT Percentage of each page left as free space
GBPCACHE CHAR(1) Group bufferpool cache option

blank Only changed pages

A Changed and unchanged pages

N No data is cached

SECQTY1 INTEGER Secondary space allocation for user managed data sets
ENFORCED_CONS CHAR(1) Whether index enforces a non-unique constraint

Blank Does not enforce a non-unique constraint

F Enforces a foreign key for a temple referential constraint

IMPLICIT CHAR(1) Whether index was implicitly created

Blank n/a

N Explicitly created

Y Implicitly created

REGENERATETS TIMESTAMP(12) Time when the object was regenerated

SYSIBM.SYSINDEXES_HIST

Contains rows from SYSINDEXES

Column name Data type Description
NAME VARCHAR(128) Name of the index
CREATOR VARCHAR(128) Schema of the index
TBNAME VARCHAR(128) Name of the table on which the index is defined
TBCREATOR VARCHAR(128) Schema of the table
CLUSTERING CHAR(1) CLUSTER was specified when the index was created

N No

Y Yes

NLEAF INTEGER Number of active leaf pages in the index
NLEVELS SMALLINT Number of levels in the index tree
STATSTIME TIMESTAMP Date and time when the last invocation of RUNSTATS
FIRSTKEYCARDF FLAOT(8) Number of distinct values in the first key column
FULLKEYCARDF FLOAT(8) Number of distinct values of the key
CLUSTERRATIOF FLOAT(8) Percentage of rows that are in clustering order
SPACEF FLOAT(8) Number of kilobytes of DASD storage allocated
IBMREQD CHAR(1) Y indicates row came MRM tape
AVGKEYLEN INTEGER Average key length within the index
DATAREPEAT

FACTORF

FLOAT Anticipated name of data pages touched following an index key order

SYSIBM.SYSINDEXES_RTSECT

An auxiliary table for the RTSECTION column of the SYSIBM.SYSINDEXES table

Column name Data type Description
BLOB(1G) Internal use only

SYSIBM.SYSINDEXES_TREE

An auxiliary table for the PARSETREE column of the SYSIBM.SYSINDEXES table

Column name Data type Description
BLOB(1G) Internal use only

SYSIBM.SYSINDEXPART

One row for each non-partitioning index and one for each partition of a partitioned index

Column name Data type Description
PARTITION SMALLINT Partition number; 0 if index is not partitioned
IXNAME VARCHAR(128) Name of partition
IXCREATOR VARCHAR(128) Schema of partition
PQTY INTEGER For user-managed data sets, the value is the primary space allocation units of 4KB storage blocks or -1
SQTY SMALLINT For user-managed data sets, the value is the primary space allocation units of 4KB storage blocks or -1
STORTYPE CHAR(1) Type of storage allocation:

E Explicit, and STORNAME, names an ICF catalog

I Implicit, and STORNAME, names in storage group

STORNAME VARCHAR(128) Name of storage group or integrated catalog facility catalog used for space allocation
VCATNAME VARCHAR(24) Name of ICF catalog used for space allocation
LEAFDIST INTEGER Average number of leaf pages between successive active leaf pages of the index (x 100)
IBMREQD CHAR(1) Y indicates row came from MRM tape
LIMITKEY VARCHAR(512) High value of limit key of the partition in an internal format
FREEPAGE SMALLINT Number of pages loaded before a page is left free
PCTFREE SMALLINT Percentage of each leaf or non-leaf page left as free
SPACE INTEGER KBs storage allocated to index space partition
STATSTIME TIMESTAMP Date and time of the last invocation of RUNSTATS
GBPCACHE CHAR(1) Group buffer pool cache option specified for index or index partition.

blank only changed pages are cached

A Changed and unchanged pages are cached

N No data is cached

FAROFFPOSF FLOAT Number of referred to rows from optimal position because of an insert into a full page
NEAROFFPOST FLOAT Number of referred to rows near, but not at optimal position, due to insert into full page
CARDF FLOAT Number of RIDs in index that refer to data rows or LOBs
SECQTYI INTEGER Secondary space allocation in units of 4KB storage
IPREFIX CHAR(1) First character of instance qualifier for index’s data set name. ‘I’ or ‘J’ are the only valid values. Default is ‘I’.
ALTEREDTS TIMESTAMP Time when the most recent ALTER INDEX statement
SPACEF FLOAT(8) Kilobytes of DASD storage
DSNUM INTEGER Number of data sets
EXTENTS INTEGER Number of data set extents
PSEUDO_DEL_

ENTRIES

INTEGER Number of pseudo deleted entries
LEAFNEAR INTEGER Number of leaf pages physically near previous leaf page for successive active leaf pages
LEAFFAR INTEGER Number of leaf pages located physically far away from previous leaf pages for successive (active leaf) pages accessed in an index sca
OLDEST_VERSION SMALLINT Version numbers describing oldest form of data in the index part and any image copies of the index part
CREATEDTS TIMESTAMP Time when the partition was created
AVGKEYLEN INTEGER Average length of keys in the index
RBA_FORMAT CHAR(1) Indicates the format of the RBA/LRSN

B Basic, 6-byte RBA/LRSN format

E Extended, 10-byte RBA/LRSN format

U Undefined

blank for migrated objects

DSSIZE INTEGER Maximum size in KB of partitioned index data set
PAGENUM CHAR(1) Format of page numbers for index

A Absolute

R Relative

LIMITKEY_

EXTERNAL

VARCHAR(765) Not used

SYSIBM.SYSINDEXPART_HIST

Contains rows from SYSINDEXPART

Column name Data type Description
PARTITION SMALLINT Partition number, zero if index is not partitioned
IXNAME VARCHAR(128) Name of the index
IXCREATOR VARCHAR(128) Schema of the index
PQTY INTEGER For user-managed data sets, value is primary space allocation in units of 4KB storage blocks or -1
SECQTYI INTEGER For user-managed data sets, value is secondary space allocation in units of 4KB storage blocks or -1
LEAFDIST INTEGER Average number of leaf pages between successive active leaf pages of the index (x100)
SPACEDF INTEGER KBs of DASD storage allocated to index space partition
STATSTIME TIMESTAMP Date and time of last invocation of RUNSTATS
FAROFFPOSF FLOAT(8) Number for referred to rows far from optimal position because of an insert into a full page
NEAROFFPOSF FLOAT(8) Number for referred to rows near but not at optimal position, because of an insert into a full page
CARDF FLOAT(8) Number of RIDs in index referring to data rows or LOBs
EXTENTS INTEGER Number of data set extents
PSEUDO_DEL_

ENTRIES

INTEGER Number of pseudo deleted entries
DSNUM INTEGER Data set number within the table space
IBMREQD CHAR(1) Y indicates row came from MRM tape
LEAFNEAR INTEGER Number of leaf pages physically near previous leaf page for successive active leaf pages
LEAFFAR INTEGER Number of leaf pages located physically far away from previous leaf pages for successive (active leaf) pages accessed in an index scan
AVGKEYLEN INTEGER Average length of keys within the index

SYSIBM.SYSINDEXSPACESTATS

Contains real-time statistics for index basis

Column name Data type Description
UPDATESTATTIME TIMESTAMP Timestamp when row was inserted or last updated
NLEVELS SMALLINT Number of levels in the index tree
NPAGES INTEGER Number of pages in the index tree that contain only pseudo-deleted index entries. Updatable column
NLEAF INTEGER Number of leaf pages in the index. Updateable column
NACTIVE INTEGER Number of active pages in the index base or partition
SPACE INTEGER Amount of space, in KB, allocated to the index space or partition
EXTENTS SMALLINT Number of extents in index space or partition
LOADRLASTTIME TIMESTAMP Timestamp of last LOAD REPLACE on index space or partition
REBUILDLASTTIME TIMESTAMP Timestamp of last REBUILD INDEX on index space or partition
REORGLASTTIME TIMESTAMP Timestamp when REORG INDEX utility was last run on the index piece or partition, or if REORG INDEX utility has not been run, the time of creation
REORGINSERTS INTEGER Number of index entries inserted into the index space or partition since the last time REORG, REBUILD INDEX, or LOAD REPLACE utilities were run, or since creation
REORGDELETES INTEGER Number of index entries deleted from the index space or partition since the last time REORG, REBUILD INDEX, or LOAD REPLACE utilities were run, or since creation
REORGAPPENDINSERT INTEGER Number of index entries that have a key value that is greater than the maximum key value in the index or partition inserted into index space or partition since the last REORG, REBUILD INDEX, or LOAD REPLACE, or since the object was created
REORG

PSEUDODELETES

INTEGER Number of index entries pseudo-deleted since last REORG, REBUILD INDEX, or LOAD REPLACE on the index space or partition, or since the object was created
REORGMASSDELETE INTEGER Number of mass deletes from a segmented or LOB table space, or number of dropped tables from a segmented table space since last time REORG or LOAD REPLACE utilities were run, or since object was created
REORGLEAFNEAR INTEGER Net number of leaf pages located physically near previous pages for successive active leaf pages that occurred since last REORG, REBUILD INDEX, or LOAD REPLACE, or since object was created
REORGLEAFFAR INTEGER Net number of leaf pages located physically far away from previous leaf pages for successive active leaf pages that occurred since the last REORG, REBUILD INDEX, or LOAD REPLACE, or object was created
REORGNUMLEVELS INTEGER Number of levels in the index tree that were added or removed since last REORG, REBUILD INDEX or LOAD REPLACE, or object was created
STATSLASTTIME TIMESTAMP Timestamp of last RUNSTATS on index or partition
STATSINSERTS INTEGER Number of records or LOBs inserted into the table space or partition since last time RUNSTATS UTILITY was run, or since object was created
STATSDELETES INTEGER Name of index entries deleted since last RUNSTATS on the index space or partition, or since object was created
STATSMASSDELETE INTEGER Number of times index or index space partition was mass deleted since object was created
COPYLASTTINE TIMESTAMP Timestamp of last full copy on index or partition
COPYUPDATEPAGES INTEGER Number of distinct types that have been updated since the last time that the COPY utility was run, or since the object was created
COPYCHANGES INTEGER Number of insert, update and delete operations since last time COPY utility was run, or since object was created
COPYUPDATELRSN CHAR(10) LRSN or RBA of first update after last COPY
COPYUPDATETIME TIMESTAMP Timestamp of first update after last COPY
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DBID SMALLINT Internal identifier of database
ISOBID SMALLINT Internal identifier of index space page set descriptor
PSID SMALLINT Internal identifier of table space page set descriptor for table space associated with index
PARTITION SMALLINT Data set number within index
INSTANCE SMALLINT Indicates if the object is associated with data set 1 or 2
TOTALENTRIES BIGINT Number of entries, including duplicate entries, in the index or partition
DBNAME VARCHAR(24) Name of database
NAME VARCHAR(128) Name of index
CREATOR VARCHAR(128) Schema of index
INDEXSPACE VARCHAR(24) Name of index space
LASTUSED DATE Date when index is used for SELECT, FETCH, searched UPDATE/DELETE, or is used to enforce RI constraints
REORGINDEXACCESS BIGINT Number of times index was used for SELECT, FETCH, searched UPDATE/DELETE, or used to enforce RI constraints, or since the object was created
DRIVETYPE CHAR(3) Drive type on which index or partition data set is defined

HDD Hard Disk Drive

SDD Solid State Drive

BIGINT Reserved for future IBM use
GETPAGES BIGINT Number of getpages since last reorg or creation
SYS_START TIMESTAMP(12) Start time of the most recent transaction
SYS_END TIMESTAMP(12) Time when row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSINDEXSTATS

Contains one of each partition of a partitioning index or a data partitioned secondary index

Column name Data type Description
FIRSTKEYCARD INTEGER For index partition, number of distinct values of first key column. For a sparse index, statistic is based on actual contents of index
FULLKEYCARD INTEGER For index partition, number of distinct values of a key. For a sparse index, statistic is based on actual contents of index
NLEAF INTEGER Number of active leaf pages in index partition
NLEVELS SMALLINT Number of levels in partition index tree
CLUSTERRATIO SMALLINT For index partition, percentage of rows in clustering order
STATSTIME TIMESTAMP Date/time of last invocation of RUNSTATS
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
PARTITION SMALLINT Partition number of index
OWNER VARCHAR(128) Schema of the owner of index
NAME VARCHAR(128) Name of index
KEYCOUNT INTEGER Total number of all RIDs in index partition
FIRSTKEYCARDF FLOAT For the index partition, number of distinct values of the first key column
FULLKEYCARDF FLOAT For index partition, number of distinct values of the key
KEYCOUNTF FLOAT Total number of RIDs in the index partition
CLUSTERRATIOF FLOAT For index partition, value, when multiplied by 100, is percentage of rows in clustering order
DATAREPEAT

FACTORF

FLOAT Anticipated number of data pages touched following an index key order

SYSIBM.SYSINDEXSTATS_HIST

Contains rows from SYSINDEXSTATS

Column name Data type Description
NLEAF INTEGER Number of active leaf pages in index partition
NLEVELS SMALLINT Number of levels in partition index tree
STATSTIME TIMESTAMP If RUNSTATS updated statistics, date and time when last invocation of RUNSTATS updated statistics
PARTITION SMALLINT Partition number of index
OWNER VARCHAR(128) Schema of index
NAME VARCHAR(128) Name of index
FIRSTKEYCARDF FLOAT For index partition, number of distinct values of first key column
FULLKEYCARDF FLOAT For index partition, number of distinct values of key
KEYCOUNTF FLOAT Total number of rows in partition
CLUSTERRATIOF FLOAT For index partition, value, when multiplied by 100, is percentage of rows that are in clustering order
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DATAREPEAT

FACTORF

FLOAT Anticipated number of data pages touched following an index key order

SYSIBM.SYSJARCLASS_SOURCE

Auxiliary table for SYSIBM.SYSCONTENTS

Column name Data type Description
CLASS_SOURCE CLOB(10M) The contents of the class in the jar file

SYSIBM.SYSJARCLASS_SOURCE

Contains Java class source for installed jar

Column name Data type Description
JARSCHEMA VARCHAR(128) The schema of the jar file
JAR_ID VARCHAR(128) The name of the jar file
OWNER VARCHAR(128) Authorization ID of the owner of the jar object
JAR_DATA_ROWID ROWID ID used to support BLOB data type
JAR_DATA BLOB(100M) Contents of the jar file. This is an updatable column
PATH VARCHAR(2048) URL path of the source jar file. This is an updatable column
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
CREATEDTS TIMESTAMP Time when the JAR object was created
ALTEREDTS TIMESTAMP Time when the JAR object was altered
OWNERTYPE CHAR(1) Indicates the type of owner:

blank Authorization ID

L Role

SYSIBM.SYSJAVAOPTS

Contains build options used during INSTALL_JAR

Column name Data type Description
JARSCHEMA VARCHAR(128) The schema of the jar file
JAR_ID VARCHAR(128) The name of the jar file
BUILDSCHEMA VARCHAR(128) Schema name for BUILDNAME
BUILDNAME VARCHAR(128) Procedure used to create the routine
BUILDOWNER VARCHAR(128) Authorization ID used to create the routine
DBMLIB VARCHAR(256) PDS name where DBRM is located
HPJCOMPILE_OPTS VARCHAR(512) HPJ compile options used to install the routine
BIND_OPTS VARCHAR(2048) Bind options used to install the routine
POBJECT_LIB VARCHAR(256) PDSE name where program object is located
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSJAVAPATHS

Contains the complete JAR class resolution path

Column name Data type Description
JARSCHEMA VARCHAR(128) Schema of the JAR file
JAR_ID VARCHAR(128) Name of the jar file
OWNER VARCHAR(128) Authorization ID of the owner of the JAR object
ORDINAL SMALLINT Ordinal number of path element within the JAR’s Java path
PE_CLASS_

PATTERN

VARCHAR(2048) Pattern for names of classes that are to be searched for in this path element’s JAR file
PE_JARSCHEMA VARCHAR(128) Schema of this path element’s JAR file
PE_JAR_ID VARCHAR(128) Name of this path element’s JAR file
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSKEYCOLUSE

Row for every column in a unique constraint (primary key or unique key)

Column name Data type Description
CONSTNAME VARCHAR(128) Name of the constraint
TBCREATOR VARCHAR(128) Schema or qualifier of table on which constraint is defined
TBNAME VARCHAR(128) Name of the table on which the constraint defined
COLNAME VARCHAR(128) Name of the column
COLSEQ SMALLINT Position of the column in the key
COLNO SMALLINT Position of column in table which constraint is defined
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
PERIOD CHAR(1) Column is start or end column for BUSINESS_TIME period:

B Start of the period BUSINESS_TIME

C End of the period BUSINESS_TIME

I End of period BUSINESS_TIME w/inclusive end

blank Not used as either start or end of BUSINESS_TIME

SYSIBM.SYSKEYS

Contains one row for each column of an index key

Column name Data type Description
IXNAME VARCHAR(128) Name of index
IXCREATOR VARCHAR(128) Schema or qualifier of the index
COLNAME VARHCAR(128) Name of the column of the key
COLNO SMALLINT Numeric postition of the column in the table
COLSEQ SMALLINT Numeric posititon of the column in the key. Meaningless for an index that is based on an expression
ORDERING CHAR(1) Order of the column in the key:

blank index is based on an expression or column is specified for the index using the INCLUDE clause

A Ascending

D Descending

R Random

IBMREQD CHAR(1) Y indicates rows came from MRM tape
PERIOD CHAR(1) Column is start or end column for BUSINESS_TIME period:

B Start of the period BUSINESS_TIME

C End of the period BUSINESS_TIME

I End of period BUSINESS_TIME w/inclusive end

blank Not used as start or end of a BUSINESS_TIME period

SYSIBM.SYSKEYTARGETS

Contains one row for each key-target that is participating in extended index definition

Column name Data type Description
IXNAME VARCHAR(128) Qualifier of the index
IXSCHEMA VARCHAR(128) Position of the key-target in the index
KEYSEQ SMALLINT Position of the key-target in the index
COLNO SMALLINT Position of common in table if expression is single column
ORDERING CHAR(1) Order of the key: A Ascending
TYPESCHEMA VARCHAR(128) Schema of the data type
TYPENAME VARCHAR(128) Name of the data type
DATATYPEID INTEGER The internal ID of the data type
SOURCETYPEID INTEGER For a built-in data type = 0. For a distinct type, the internal ID of the built-in type on which the distinct type is based
LENGTH SMALLINT Length attribute of key-target of precision for a decimal key-target
LENGTH2 INTEGER Maximum length of a data retrieved from the column.

0 Not a ROWID column

40 For a ROWID

SCALE SMALLINT Scale of decimal data or number of fractional second digits of timestamp or timestamp with time zone data
NULLS CHAR(1) Whether the key can contain null values:

N No

Y Yes. Y also indicates that the index is an XML index

CCSID INTEGER The CCSID of the key. 0 if the key is a non-character type key
SUBTYPE CHAR(1) Applies to character keys only and indicates subtype of data:

B BIT data

M MIXED data

S SBCS data

blank non-character data

CREATEDTS TIMESTAMP The timestamp for when the key-target is created
RELCREATED CHAR(1) The release of DB2 which the key-target is created
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DERIVED_FROM VARCHAR(4000) For an index on a scalar expression, DERIVED_FROM contains the text of the scalar expression that is used to generate key-target value. For an XML index, this is the XML pattern that is used to generate the key-target value. Otherwise, empty
STATSTIME TIMESTAMP Timestamp of the most recent RUNSTATS
CARDF FLOAT The number of distinct values for the key-target
HIGH2KEY VARCHAR(2000) Second highest key-value. HIGH2KEY is an updatable column
LOW2KEY VARCHAR(2000) Second lowest key-value. LOW2KEY is an updateable column
STATS_FORMAT CHAR(1) The type of statistics that are gathered:

N VARCHAR column statistical values are not padded blank Statistics have not been collects or VARCHAR column statistical values are padded

An updateable column

SYSIBM.SYSKEYTARGETSTATS

Contains partition statistics for selected key-targets

Column name Data type Description
IXSCHEMA VARCHAR(128) Qualifier of the index
IXNAME VARCHAR(128) Name of the index
KEYSEQ SMALLINT Numeric position of the key-target in the index
HIGHKEY VARCHAR(2000) Highest key value
HIGH2KEY VARCHAR(2000) Second highest key-value
LOWKEY VARCHAR(2000) Lowest key value
LOW2KEY VARCHAR(2000) Second lowest key-value
PARTITION SMALLINT Partition number of the table space
STATSTIME TIMESTAMP Timestamp of the most recent RUNSTATS
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
STATS_FORMAT FLOAT The type of statistics that are gathered:

N VARCHAR column statistical values are not padded

blank Statistics have not been collected or VARCHAR column statistical values are padded

CARDF FLOAT Number of distinct values for the key target

SYSIBM.SYSKEYTARGETS_HIST

Contains rows from the SYSKEYTARGETS table

Column name Data type Description
IXNAME VARCHAR(128) Name of the index
IXSCHEMA VARCHAR(128) Qualifier the index
KEYSEQ SMALLINT Numeric position of the key-target in the index
TYPESCHEMA VARCHAR(128) Schema of the data type
TYPENAME VARCHAR(128) Name of the data type
DATATYPEID INTEGER The internal ID of the data type
SOURCETYPEID INTEGER For a built-in data type=0. For a distinct type, the internal ID of the built-in type on which the distinct type is based
LENGTH SMALLINT Length attribute of the column, or in the case of a decimal column, its precision
LENGTH2 INTEGER Maximum length of data that is retrieved from the column

0 Not a ROWID column

40 For a ROWID

SCALE SMALLINT Scale of decimal data or number of fractional second digits of timestamp with time zone data. Otherwise the value is 0. If the column is a timestamp type, the LENGTH is 10 and the SCALE is 0, the number of fractional second digits is 6
NULLS CHAR(1) Whether the key can contain null values

N No

Y Yes

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
STATSTIME TIMESTAMP Timestamp of the most recent RUNSTATS
CARDF FLOAT Number of distinct values for the key-target
HIGH2KEY VARCHAR(2000) The second highest key-value
LOW2KEY VARCHAR(2000) The second lowest key-value
STATS_FORMAT CHAR(1) The type of statistics that are gathered:

N VARCHAR column statistical values are not padded blank Statistics have not been collects or VARCHAR column statistical values are padded

SYSIBM.SYSKEYTGTDIST

Contains one or more rows for the first key-target of an extended index key

Column name Data type Description
STATSTIME TIMESTAMP Date and time of the last invocation of RUNSTATS
IBMREQD CHAR(1) The qualifier of the index
IXSCHEMA VARCHAR(128) The name of the index
IXNAME VARCHAR(128) The numeric position of the key-target in the index
KEYSEQ SMALLINT The numeric position of the key-target in the index
KEYVALUE VARCHAR(2000) KEYVALUE contains the data of a frequently occurring value
TYPE CHAR(1) The type of statistics that are gathered:

C Cardinality

F Frequent value

N Non-padded frequent value

H Histogram statistics

CARDF FLOAT TYPE=’C’- number of distinct values for key group

TYPE=’H’= number of distinct values for the key group in a quantile indicated by QUANTILENO

KEYGROUPKEYNO VARCHAR(254) Identifies set of keys that are associated with the statistics. 0 if statistics are only associated with a single key
NUMKEYS SMALLINT The number of keys are associated with the statistics
FREQUENCYF FLOAT TYPE=’F’ or ‘N’- percentage of entries in index that have the value that is contained in KEYVALUE.

TYPE=’H’- percentage of entries in index that have a value that is in the range of the quantile in QUANTILENO column

QUANTILENO SMALLINT QUANTILENO contains an ordinary sequence number of a quantile in whole consecutive value range, from low to high
LOWVALUE VARCHAR(2000) TYPE=’H’- lower bound for the quantile in QUANTILENO.

Not used if TYPE does not equal ‘H’

HIGHVALUE VARCHAR(2000) TYPE=’H’- upper bound for the quantile in QUANTILENO. Not used if TYPE does not equal ‘H’

SYSIBM.SYSKEYTGTDISTSTATS

Contains rows per partition for first key-target of a data-partitioned secondary index

Column name Data type Description
STATSTIME TIMESTAMP Timestamp of the most recent RUNSTATS
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
PARTITION SMALLINT Part number of that contains index in which key is defined
IXSCHEMA VARCHAR(128) Qualifier of the index
IXNAME VARCHAR(128) Name of the index
KEYSEQ SMALLINT Numeric position of the key-target in the index
KEYVALUE VARCHAR(2000) Date of a frequently occurring value
TYPE CHAR(1) The type of statists that are gathered:

C Cardinality

F Frequent value

N Non-padded frequent value

H Histogram statistics

CARDF FLOAT TYPE=’C’- number of distinct values for the key group TYPE=’H’- number of distinct values for the key group in the quantile in QUANTILENO
KEYGROUPKEYNO VARCHAR(254) Identifies set of keys associated with statistics
NUMKEYS SMALLINT Identify the number of keys associated with the statistics
FREQUENCYF FLOAT TYPE=’F’ or ‘N’- percentage of entries in the index that have the value that is specified in KEYVALUE when the number of entries is multiplied by 100.

TYPE=’H’- percetage of entries in the index that have a value that is in the range of the quantile in QUANTILENO

QUANTILENO SMALLINT QUANTILENO contains an ordinary sequence number of a quantile in consecutive value range, from low to high
LOWVALUE VARCHAR(2000) TYPE=’H’- lower bound for quantile in QUANTILENO. LOWVALUE is not used if TYPE does not equal ‘H’
HIGHVALUE VARCHAR(2000) TYPE=’H’- upper bound for the quantile in QUANTILENO. HIGHVALUE is not used if TYPE does not equal ‘H’
VARCHAR(1000) Internal use only

SYSIBM.SYSKEYTGTDIST_HIST

Contains rows from the SYSKEYTGTDIST table

Column name Data type Description
STATSTIME TIMESTAMP Date and time of last invocation of RUNSTATS
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
IXSCHEMA VARCHAR(128) The qualifier of the index
IXNAME VARCHAR(128) The name of the index
KEYSEQ SMALLINT The numeric position of the key-target in the index
KEYVALUE VARCHAR(2000) Contains data of a frequently occurring value
TYPE CHAR(1) The type of statistics that are gathered:

C Cardinality

F Frequent value

N Not a frequent value

H Histogram statistics

CARDF FLOAT TYPE=’C’- number of distinct values for key group. TYPE=’H’- number of distinct values for key group in QUANTILENO
KEYGROUPKEYNO VARCHAR(254) Value that identifies the set of keys that are associated with the statistics
NUMKEYS SMALLINT The number of keys that are associated with a statistic
FREQUENCYF FLOAT TYPE=’F’ or ‘N’- percentage of entries in the index that have the value that is specified in KEYVALUE when the number of entries is multiplied by 100. TYPE=’H’- percentage of entries in index that have a value that is in the range of the quantile in QUANTILENO
QUANTILENO SMALLINT QUANTILENO contains an ordinary sequence number of a quantile in whole consecutive value range, from low to high
LOWVALUE VARCHAR(2000) TYPE=’H’- lower bound for the quantile in QUANTILENO. LOWVALUE is not used to TYPE does not equal ‘H’
HIGHVALUE VARCHAR(2000) TYPE=’H’- upper bound for the quantile in QUANTILENO. HIGHVALUE is not used to type does not equal ‘H’

SYSIBM.SYSLEVELUPDATES

Contains information about function levels, catalog levels and code levels of DB2 subsystem

Column name Data type Description
FUNCTION_LVL VARCHAR(10) Function level
PREV_FUNCTION_

LVL

VARCHAR(10) Previous function level

 

HIGH_FUNCTION

_LVL

VARCHAR(10) Highest activated function level

 

CATALOG_LVL VARCHAR(10) Type of operation

C Catalog level change

F Function level change

M Code level change

EFFECTIVE_TIME TIMESTAMP(12) Time when operation completed
EFFECTIVE_LRSN VARCHAR(12) RBA or LRSN when operation completed
OPERATION_TEXT VARCHAR(256) Text of operation
GROUP_MEMBER VARCHAR(24) Name of group member on which operation was run

SYSIBM.SYSLOBSTATS

Contains one row for each LOB tablespace

Column name Data type Description
STATSTIME TIMESTAMP Timestamp of RUNSTATS statistics update
AVGSIZE INTEGER Average size of a LOB, measured in bytes, in the LOB tablespace
FREESPACE INTEGER Number of kilobytes of available space in the LOB tablespace
ORGRATIO DECIMAL(5,2) Percentage of organization in LOB tablespace. 100 = perfect organization. 1= disorganized. 0= totally disorganized.
DBNAME VARCHAR(24) Name of database that contains LOB tablespace
NAME VARCHAR(24) Name of the LOB tablespace
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSLOBSTATS_HIST

Contains rows from SYSLOBSTATS. Can be inserted, updated, and deleted

Column name Data type Description
STATSTIME TIMESTAMP Timestamp of RUNSTATS statistics update
FREESPACE INTEGER Number of kilobytes of available space in the LOB tablespace
ORGRATIO DECIMAL(5,2) Percentage of organization in LOB table space. 100= perfect organization. 1= disorganized. 0= totally disorganized
DBNAME VARHCAR(24) Name of the database that contains the LOB tablespace
NAME VARCHAR(24) Name of the LOB tablespace
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSOBJROLEDEP

Contains the dependent objects for each role

Column name Data type Description
DEFINER VARCHAR(128) The authorization ID or role that created the object
DEFINERTYPE CHAR(1) The type of definer:

L Role

blank Authorization ID

ROLENAME VARCHAR(128) Name of the role on which there is a dependency
DSCHEMA VARCHAR(128) Name of the schema of the dependent object
DNAME VARCHAR(128) Name of the dependent object
DTYPE CHAR(1) The type of dependent object in DNAME:

A Alias

B Trigger

D Database

E Distinct type

F User-defined function

I Index

J Jar

L Role

M Materialized query table

N Trusted context

O Stored procedure

Q Sequence

R Table space

S Storage group

T Table

V View

X Row permission

Y Column mask

0 Alias

IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSPACKAGE

Contains a row for every package

Column name Data type Description
LOCATION VARCHAR(128) Always contains blanks
COLLID VARCHAR(128) Name of package collection
NAME VARCHAR(128) Name of the package
CONTOKEN CHAR(8) Consistency token for package
OWNER VARCHAR(128) Authorization ID of the package owner
CREATOR VARCHAR(128) Auth ID of owner of creator of package version
TIMESTAMP TIMESTAMP Timestamp indicating when the package was created
BINDTIME TIMESTAMP Timestamp indicating when the package was last bound
QUALIFIER VARCHAR(128) Implicit qualifier for the unqualified table, view, index, and alias names in static SQL statements of the package
PKSIZE INTEGER Size of the base section of package in bytes
AVGSIZE INTEGER Average size, in bytes, of those sections of the plan that contain SQL statements processed at bind time
SYSENTRIES SMALLINT Number of enabled disabled entry for this packaging 04 types of connections are enabled
VALID CHAR(1) Whether the package is valid:

A Alter statement changed the description of the table of base table of a view referred to by the package. For a CREATE INDEX involving data sharing, VALID is also marked as ‘A’. Changes do not invalidate the package.

H ALTER TABLE statement changed description of the table or base table of a view referred to by the package

N No

Y Yes

OPERATIVE CHAR(1) Whether the package can be allocated:

N No

Y Yes

VALIDATE CHAR(1) Whether validity checking can deferred until run time:

B All checking must be performed at bind time

R Validation is done at run time for tables, views and privileges that do not exist at bind time

ISOLATION CHAR(1) Isolation level when package was last bound or rebound

I Local packages inheriting the value from the plan

R RR (repeatable read)

S CS (cursor stability)

T RS (read stability)

U UR (uncommitted read)

blank Not specified, and therefore at the level specified for the plan executing the package

RELEASE CHAR(1) Value for RELEASE when package was last bound/rebound:

C Value used was COMMIT

D Value used was DEALLOCATE

I Local package is inheriting value from plan

blank Not specified, and therefore the value specified for the plan executing the package

EXPLAIN CHAR(1) EXPLAIN option specified for the package

N No

Y Yes

QUOTE CHAR(1) SQL string delimiter for SQL statements in the package:

N Apostrophe

Y Quotation mark

COMMA CHAR(1) Decimal point representation for SQL statements in package:

N Period

Y Comma

HOSTLANG CHAR(1) Host language for the package’s DBRM:

B Assembler language

C OS/VS COBOL

D C

F Fortran

P PL/I

2 VS COBOL II or IBM COBOL Release 1

3 IBM COBOL (Release 2 or subsequent releases)

4 C++

Blank For remotely bound packages, trigger packages (TYPE=’T’), SQL procedure packages (TYPE=’N’), or non-inline SQL scalar function packages (TYPE=’F’)

CHARSET CHAR(1) CCSID for SBCS data was 290 (katakana) when program was precompiled:

K Yes

A No

MIXED CHAR(1) Mixed data was in affect when program was precompiled:

N No

Y Yes

DEC31 CHAR(1) DEC31 was in effect when program was pre-compiled:

N No

Y Yes

DEFERPREP CHAR(1) CURRENTDATA option package was bound or rebound:

A Data currency is required for all cursors. Inhibit blocking for all cursors

B Data currency is not required for ambiguous cursors

C Data currency is required for ambiguous cursors blank Package was created before CURRENTDATA option was available

SQLERROR CHAR(1) SQLERROR option on most recent subcommand that bound or rebound the package:

C Continue

N No package

REMOTE CHAR(1) Source of the package:

C Package was created by BIND COPY

D Package was created by BIND COPY with the OPTIONS(COMMAND) option

K Package was copied from a package that was originally bound on behalf of a remote requester

L Package was copied with the OPTIONS(COMMAND) option from a package that was originally bound on behalf of a remote requester

N Package was locally bound from a DBRM

Y Package was bound on behalf of a remote requester

PCTIMESTAMP TIMESTAMP Date and time the application program was precompiled
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
VERSION VARCHAR(122) Version identifier for package. Emtpy for SQL procedure package (TYPE=N), SQL scalar function package (TYPE=F), or trigger package (type=t OR 1)
PDSNAME VARCHAR(132) Name of PDS (library)in which package’s DBRM is a member
DEGREE CHAR(3) DEGREE option used when the package was last bound:

Any DEGREE(ANY)

1 or blank DEGREE(1)

blank if the package was migrated

GROUP_MEMBER VARCHAR(24) Member name of subsystem that performed most recent bind
DYNAMICRULES CHAR(1) DYNAMICRULES option used when package was last bound:

B BIND. Dynamic SQL statements are executed with DYNAMICRULES bind behaviour

D DEFINEBIND

E DEFINERUN

H INVOKERUN

R RUN

Blank DYNAMICRULES not specified for the package

REOPTVAR CHAR(1) If access path is determined again at execution time using input variable values:

A REOPT(AUTO)

N REOPT(NONE)

Y REOPT(ALWAYS)

1 REOPT(ONCE)

DEFERPREPARE CHAR(1) If PREPARE processing is deferred until OPEN is executed:

N NODEFER(PREPARE)

Y DEFER(PREPARE)

I Local package is inheriting value from the plan blank Bind option not specified. Inherited from plan

KEEPDYNAMIC CHAR(1) Whether prepared dynamic statements are purged at commit:

N KEEPDYNAMIC(NO)

Y KEEPDYNAMIC(YES)

PATHSCHEMAS VARCHAR(2048) SQL path specified on the BIND or REBIND command that bound the package
TYPE CHAR(1) Type of package. Identifies how the package was created:

F CREATE FUNCTION or ALTER FUNCTION statement, or a BIND PACKAGE DEPLOY command created the package, and this package is a non-inline SQL scalar function package

N CREATE PROCEDURE or ALTER PROCEDURE statement, or BIND PACKAGE DEPLOY command created the package, and this package is a native SQL routine package

R Reserved for IBM use

T CREATE TRIGGER statement created the package, and the package is a trigger package. blank BIND PACKAGE command created the package

DBPROTOCOL CHAR(1) Whether remote access for SQL is implemented with DRDA access or DRDA access with the capability for package-based continuous block fetch:

D DRDA

C DRDA access with package-based continuous block feature enabled

FUNCTIONTS TIMESTAMP Timestamp when function was resolved
OPTHINT VARCHAR(128) Identifies rows in authid.PLAN_TABLE to be used as input to the optimizer
ENCODING_CCSID INTEGER Encoding scheme specified on the bind command:

CCSID Specified or derived CCSID

0 EBCDIC default CCSID as specified on panel DSNTIPF at installation time

IMMEDWRITE CHAR(1) When writes of updated group bufferpool dependent pages are to be done. Only applicable for data-sharing

I Local package is inheriting the value from the plan

N IMMEDWRITE(NO)- normal write activity is done

Y IMMEDWRITES(YES)- immediate writes are done for updated group buffer pool dependent pages

1 IMMEDWRITES(PH1)- updated group bufferpool dependent pages are written at or before phase 1 commit

Blank if the package was migrated

RELBOUND CHAR(1) The release when the package was bound or rebound

blank Bound prior to V7

REMARKS VARCHAR(762) Character string provided by user with COMMENT statement
OWNERTYPE CHAR(1) Indicates the type of owner:

blank Authorization ID

L Role

ROUNDING CHAR(1) ROUNDING option used when the package was last bound:

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP blank

Blank The package created in a DB2 release prior to V9

DISTRIBUTE CHAR(1) Determines if DB2 should gather location names from SQL statements, and create remote packages for the user

A DB2 will collect remote location names from SQL statements during local bind, and automatically create remote packages at those sites

L DB2 will automatically create remote packages at sites specified in the list of location-names

LASTUSED DATE The last date that the corresponding objects are used
CONCUR_ACC_

RES

CHAR(1) CONCURRENTACCESSRESOLUTION option when package was bound or rebound:

blank Not specified

N WAITFOROUTCOME

Y USECURRENTLYCOMMITTED

EXTENDED

INDICATOR

CHAR(1) Value of the EXTENDEDINDICATOR bind option:

N EXTENDEDINDICATOR NO

Y EXTENDEDINDICATOR YES

PLANMGMT CHAR(1) Value of the PLANMGMTSCOPE bind option:

B PLANMGMT BASIC

E PLANMGMT EXTENDED

Blank PLANMGMT OFF

PLANMGMTSCOPE CHAR(1) Value of the PLANMGMTSCOPE bind option:

S PLANMGMTSCOPE STATIC

APREUSE CHAR(1) Value of the APREUSE bind option:

N No or none: Access paths are not reused

W WARN: DB2 tries to reuse access paths. Processing continues when an access path cannot be reused

E ERROR: DB2 tries to reuse access paths. Processing ends when an access path cannot be reused

APRETAINDUP CHAR(1) Value of the APRETAINDUP bind option:

Y Yes specified. All copies were retained

0 No specified; however, the previous or original package copy is still retained due to access path differences

1 No specified, and the previous package copy is not retained as access paths are identical to current copy

2 No specified, and previous and original package copies are retained as access paths are identical to current

SYSTIMESENSITIVE CHAR(1) Value of the SYSTIMESENSITIVE bind option:

Y References to system-period temporal tables are affected by CURRENT TEMPORAL BUSINESS_TIME

N References to application-period temporal tables are not affected by CURRENT TEMPORAL BUSINESS_TIME

BUSTIMESENSITIVE CHAR(1) Value of the BUSTIMESENSITIVE bind option@

Y References to application-period temporal tables are affected by CURRENT TEMPORAL BUSINESS_TIME

N References to application-period temporal tables are not affected by CURRENT TEMPORAL BUSINESS_TIME

APPLCOMPAT VARCHAR(10) Value of the APPLCOMPAT bind option:

V10R1 SQL statements in the package have V10R1 compatibility behavior

V11R1 SQL statements in the package V11R1 compatibility behaviour

Function-level – SQL statements in package of compatibility behaviour with the specified function level

ARCHIVESENSITIVE CHAR(1) Value of the ARCHIVESENSITIVE bind option

Y (default) references to archive-enabled tables are affected SYSIBMADM.GET_ARCHIVE built-in global variable

N References to archive-enabled tables are not affected by SYSIBMADM.GET_ARCHIVE built-in global variable

EXTSEQNO INTEGER For internal use
DESCSTAT CHAR(1) Value of the DESCSTAT bind option

Y DB2 database manager generates a DESCRIBLE SQLDA at bind time so DESCRIBE requests for static SQL can be satisfied during execution

N DB2 database managers does not generate a DESCRIBE SQLDA at bind time for static SQL statements

ORIGIN CHAR(1) Origin of EXPLAIN records

A Automatic bind

B Bind command

G Explicit ALTER REGENERATE

I Implicit automatic regeneration

R REBIND command

Blank existed before 12

APREUSE_NO_FL VARCHAR(10) Function level when package bound APREUSE(NO)
APREUSE_NO_TS TIMESTAMP Bind time when package bound APREUSE(NO)
CONC_STMT CHAR(1) Whether statement concentration is enabled

N No

Y Yes

FUNCTION_LVL VARCHAR(10) Function level of package when row was inserted

SYSIBM.SYSPACKCOPY

Contains a row for every previous and original package

Column name Data type Description
LOCATION VARCHAR(128) Always contains blanks
COLLID VARCHAR(128) Name of package collection
NAME VARCHAR(128) Name of the package
CONTOKEN CHAR(8) Consistency token for the package
OWNER VARCHAR(128) Authorization ID of package owner
CREATOR VARCHAR(128) Authorization ID of owner of package version
TIMESTAMP TIMESTAMP Timestamp indicating when the package was created
BINDTIME TIMESTAMP Timestamp indicating when the package was last bound
QUALIFIER VARCHAR(128) Implicit qualifier for unqualified table, view index, and alias names in the static SQL statements of the package
PKSIZE INTEGER Size of the base section of the package, in bytes
AVGSIZE INTEGER Average size (bytes) of sections of plan including SQL
SYSENTRIES SMALLINT Number of enabled or disabled entries for package in SYSPKSYSTEM. 0 is all types of connections are enabled
VALID CHAR(1) Whether the package is valid:

A ALTER statement changed description of table or base table of a view referred to by the package

H ALTER TABLE statement changed description of table or base table of a view referred to by package

N No

Y Yes

OPERATIVE CHAR(1) Whether the package can be allocated:

N An explicit BIND or REBIND is required before package can be allocated

Y Yes

VALIDATE CHAR(1) Whether validity checking can be deferred until run time:

B All checking must be performed at bind time

R Validtion is done at run time for tables, views, and privileges that do not exist at bind time

ISOLATION CHAR(1) Isolation level when packagewas last bound or rebound

R RR(repeatable read)

S CS(cursor stability)

T RS(read stability)

U UR(uncommitted read)

Blank Not specified, level specified for plan

RELEASE CHAR(1) Value for RELEASE when package was last bound/rebound:

C Value used was COMMIT

D Value used was DEALLOCATE

I Local package is inheriting the value from the plan

Blank Not specified, value for plan executing the package

EXPLAIN CHAR(1) EXPLAIN option specified for the package

N No

Y Yes

QUOTE CHAR(1) SQL String delimiter for SQL statements in the package:

N Apostrophe

Y Quotation mark

COMMA CHAR(1) Decimal point representation for SQL statements in package:

N Period

Y Comma

HOSTLANG CHAR(1) Host language for the package’s DBRM:

B Assembler language

C OS/VS COBOL

D C

F Fortran

P PL/I

2 VS COBOL II or IBM COBOL Release 1

3 IBM COBOL (Release 2 or subsequent release)

4 C++

Blank for remotely bound packages, trigger packages (TYPE=’T’), SQL procedure packages (TYPE=’N’), or non-inline SQL scalar function packages (TYPE=’F’)

CHARSET CHAR(1) CCSID for SBCS data was 290 (katakuna) when program was precompiled:

K Yes

A No

MIXED CHAR(1) Mixed data in effect when program was precompiled

N No

Y Yes

DEC31 CHAR(1) DEC31 in effect when program was precompiled

N No

Y Yes

DEFERPREP CHAR(1) CURRENTDATA option when package bound or rebound:

A Data currency is required for all cursors. Inhibit blocking for all cursors

B Data currency is not required for ambiguous cursors

C Data currency is required for ambiguous cursors

Blank Package was created before CURRENTDATA option was available

SQLERROR CHAR(1) SQLERROR option on most recent subcommand that bound or rebound the package:

C CONTINUE

N NOPACKAGE

REMOTE CHAR(1) Source of the package:

C Package was created by BIND COPY

D Package was created by BIND COPY with the OPTIONS(COMMAND) option

K Package was copied from a package that was originally bound on behalf of a remote requester

L Package was copied with the OPTIONS(COMMAND) option from a package that was originally bound on behalf of a remote requester.

N Package was locally bound from a DBRM

Y Package was bound on behalf of a remote requester

PCTIMESTAMP TIMESTAMP Date and time the application program was precompiled
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
VERSION VARHCAR(122) Version identifier for package. Empty for SQL procedure package (TYPE=N), SQL scalar function package (TYPE=F), or trigger package(TYPE=T or 1)
PDSNAME VARCHAR(132) Name of PDS (library) in which package’s DBRM is a member
DEGREE CHAR(3) DEGREE option used when the package was last bound:

ANY DEGREE(ANY)

1 or blank DEGREE(1)

Blank if the package was migrated

GROUP_MEMBER VARCHAR(24) Member name of subsystem that performed most recent bind
DYNAMICRULES CHAR(1) DYNAMICRULES option used when package was last bound:

B BIND. Dynamic SQL statements are executed with DYNAMICRULES bind behaviour

D DEFINEBIND

E DEFINERUN

H INVOKEBIND

I INVOKERUN

R RUN

Blank DYNAMICRULES not specified for package

REOPTVAR CHAR(1) If access path is determined to get execution time using input variable values

A REOPT(AUTO)

N REOPT(NONE)

Y REOPT(ALWAYS)

1 REOPT (ONCE)

DEFERPREPARE CHAR(1) If PREPARE processing is deferred until OPEN is executed:

N NODEFER(PREPARE)

Y DEFER(PREPARE)

I Local package is inheriting the value from the plan

blank Bind option not specified. Inherited from plan

KEEPDYNAMIC CHAR(1) Whether the prepared dynamic statements are purged at commit:

N KEEPDYNAMIC(NO)

Y KEEPDYNAMIC(YES)

PATHSCHEMAS VARCHAR(2048 SQL path specified on the BIND or REBIND command that bound the package
TYPE CHAR(1) Type of package. Identifies how the package was created:

F CREATE FUNCTION or ALTER FUNCTION statement, or a BIND PACKAGE DEPLOY command created the package, and this package is a non-inline SQL scalar function package

N CREATE PROCEDURE or ALTER PROCEDURE statement, or BIND PACKAGE DEPLOY command created the package, and this package is a native SQL routine package

R Reserved for IBM use

T CREATE TRIGGER statement created the package, and the package is a trigger package. Blank BIND PACKAGE command created the package

DBPROTOCOL CHAR(1) Whether remote access for SQL is implemented with DRDA access or DRDA access with the capability for package-based continuous block fetch:

D DRDA

C DRDA access with package-based continuous block fetch enabled

FUNCTIONTS TIMESTAMP Timestamp when function was resolved
OPTHINT VARCHAR(128) Identifies rows in authid.PLAN_TABLE to be used as input to the optimizer
ENCODING_

CCSID

INTEGER Encoding scheme specified on the bind command:

CCSID Specified or derived CCSID

0 EBCDIC default CCSID as specified on panel DSNTIPF at installation time

IMMEDWRITE CHAR(1) When writes of updated group bufferpool dependent pages are to be done. Only applicable for data-sharing.

I Local package is inheriting the value from the plan

N IMMEDWRITE(NO) – normal write activity is done

Y IMMEDWRITE(YES) – immediate writes are done for updated grup buffer pool dependent pages

1 IMMEDWRITE(PH1) – updated group bufferpool dependent pages are written at or before phase 1 commit

Blank if the package was migrated

RELBOUND CHAR(1) The release when the package was bound or rebound

blank Bound prior to V7

REMARKS VARCHAR(762) Character string provided by user with COMMENT statement
OWNERTYPE CHAR(1) Indicates the type of owner:

blank Authorization ID

L Role

ROUNDING CHAR(1) ROUNDING option used when the package was last bound:

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP blank

blank The package created in a DB2 release prior to V9

DISTRIBUTE CHAR(1) Determines if DB2 should gather location names for SQL statements, and create remote packages for the user

A DB2 will collect remote location names SQL statements during local bind, and automatically create remote packages at those sites

L DB2 will automatically create remote packages at sites specified in the list of location-names

LASTUSED DATE The last date that the corresponding objects are used
CONCUR_ACC_

RES

CHAR(1) CONCURRENTACCESSRESOLUTION option when package was bound or rebound:

blank Not specified

N WAITFOROUTCOME

Y USECURRENTLYCOMMITTED

EXTENDED

INDICATOR

CHAR(1) Value of the EXTENDEDINDICATOR bind option:

N EXTENDEDINDICATOR NO

Y EXTENDEDINDICATOR YES

PLANMGMT CHAR(1) Value of the PLANMGMT bind option:

B PLANMGMT BASIC

E PLANMGMT EXTENDED

Blank PLANMGMT OFF

PLANMGMTSCOPE CHAR(1) Value of the PLANMGMTSCOPE bind option:

S PLANMGMTSCOPE STATIC

APREUSE CHAR(1) Value of the APREUSE bind option:

N No or none: Access paths are not reused.

W WARN: DB2 tries to reuse access paths. Processing continues when an access path cannot be reused

E ERROR: DB2 tried to reuse access paths. Processing ends when an access path cannot be reused

APRETAINDUP CHAR(1) Value of the APRETAINDUP bind option:

Y YES specified. All copies were retained.

0 NO specified; however, the previous or original package copy is still retained due to access path differences

1 NO specified, and the previous package copy is not retained as access paths are identical to current copy

2 NO specified, and previous and original package copies are not retained as access paths are identical to current

SYSTIME

SENSITIVE

CHAR(1) Value of the SYSTIMESENSITIVE bind option:

Y References to system-period temporal tables are affected by CURRENT TEMPORAL SYSTEM_TIME

N References to system-period temporal tables are not affected by CURRENT TEMPORAL SYSTEM_TIME

BUSTIME

SENSITIVE

CHAR(1) Value of the BUSTIMESENSITIVE bind option:

Y References to application-period temporal tables are affected by value of CURRENT TEMPORAL BUSINESS_TIME

N References to application-period temporal tables are not affected by CURRENT TEMPORAL BUSINESS_TIME

APPLCOMPAT VARCHAR(10) Value of the APPLCOMAT bind option:

V10R1 SQL statements in the package have V10R1 compatibility behaviour

V11R1 SQL statements in the package have V11R1 compatibility behaviour

Function-level – SQL statements in package have compatibility behaviour with the specified function level

ARCHIVE

SENSITIVE

CHAR(1) Value of the ARCHIVESENSITIVE bind option

Y (default) references to archive-enabled tables are affected SYSIBMADM.GET_ARCHIVE built-in global variable

N References to archive-enabled tables are not affected by SYSIBMADM.GET_ARCHIVE built-in global variable

EXTSEQNO INTEGER For internal use
DESCSTAT CHAR(1) Value of the DESCSTAT bind option:

Y DB2 database manager generates a DESCRIBE SQLDA at bind time so DESCRIBE requests for static SQL can we satisfied during execution

N DB2 database manager does not generate a DESCRIBE SQLDA at bind time for static SQL statements

ORIGIN CHAR(1) Origin of EXPLAIN records:

A Automatic bind

B BIND command

G Explicit ALTER REGENERATE

I Implicit automatic regeneration

R REBIND command

blank existed before 12

APREUSE_NO_FL VARCHAR(10) Function level when package bound APREUSE(NO)
APRESUE_NO_TS TIMESTAMP Bind time when package bound APREUSE(NO)
CONC_STMT CHAR(1) Whether statement concentration is enabled

N No

Y Yes

FUNCTION_LVL VARCHAR(10) Function level of package when row was inserted

SYSIBM.SYSPACKAUTH

Records the privileges that are held by users over packages

Column Name Data type Description
GRANTOR VARCHAR(128) Auth ID of user who granted privilege. Could be PUBLIC
GRANTEE VARCHAR(128) Auth ID of user who holds the privileges, name of a plan that uses privileges or PUBLIC for a grant to PUBLIC
LOCATION VARCHAR(128) Always contains blanks
COLLID VARCHAR(128) Collection name for package(s) which privilege was granted
NAME VARCHAR(128) Name of package on which privileges are held
TIMESTAMP TIMESTAMP Timestamp indicating when the privilege was granted
GRANTEETYPE CHAR(1) Type of grantee:

blank An authorization ID

L Role

P An application plan

AUTHHOWGOT CHAR(1) Authorization level of user from whom privileges were received. Not necessarily highest authorization level of grantor

blank Not applicable

A PACKADM (on collection*)

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

P PACKADM (on a specific collection)

S SYSADM

T DATAACCESS

BINDAUTH CHAR(1) GRANTEE can use BIND and REBIND against package:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege iss held without the GRANT option

COPYAUTH CHAR(1) GRANTEE can COPY the package:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege iss held without the GRANT option

EXECUTEAUTH CHAR(1) GRANTEE can execute the package:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege iss held without the GRANT option

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
GRANTORTYPE CHAR(1) Indicates the type of grantor:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSPACKDEP

Dependencies of packages on tables, views, synonyms, tablespaces, indexes, aliases, functions and stored procedures

Column name Data type Description
BNAME VARCHAR(128) Name of an object a package depends on
BQUALIFIER VARCHAR(128) Qualifier of object
BTYPE CHAR(1) Type of object identified by BNAME and BQUALIFIER:

A Alias

B BUSINESS_TIME

C SYSTEM_TIME

E INSTEAD OF trigger

F User-defined function or cast function

G Global Temporary Table

I Index

M Materialized query table

O Stored procedure

P Partitioned tablespace if it is defined as LARGE or with DSSIZE

Q Sequence object

R Tablespace

S Synonym

T Table

V View

0 Alias

DLOCATION VARCHAR(128) Always contain blanks
DCOLLID VARCHAR(128) Name of the package collection
DNAME VARCHAR(128) Name of the package
DCONTOKEN CHAR(8) Consistency token for the package
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DOWNER VARCHAR(128) Owner of the package
DTYPE CHAR(1) Type of package:

F Non-inline SQL scalar function

N Native SQL routine package

O Original copy of a package

P Previous copy of a package

R Reserved for IBM use

T Trigger package for basic trigger

blank Not a trigger package or a native SQL routine package

1 Trigger package for an advanced trigger

DOWNERTYPE CHAR(1) Indicates the type of owner of the package:

blank Authorization ID

L Role

SYSIBM.SYSPACKLIST

Contains one or more roles for every local application plan bound with a package list

Column name Data type Description
PLANNAME VARCHAR(24) Name of the application plan
SEQNO SMALLINT Sequence number of the entry in the package list
LOCATION VARCHAR(128) Location of package. Blank if local. (*)- determined at run time
COLLID VARCHAR(128) Collection name for package. (*) determined at run time
NAME VARCHAR(128) Name of the package. (*)- an entire collection
TIMESTAMP TIMESTAMP Timestamp indicating when the row was created
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSPACKSTMT

Contains one or more rows for each statement in a package

Column name Data type Description
LOCATION VARCHAR(128) Always contains blanks
COLLID VARCHAR(128) Name of the package collection
NAME VARCHAR(128) Name of the package
CONTOKEN CHAR(8) Consistency token for the package
SEQNO INTEGER Not used
STMTNO SMALLINT Statement number of statement in source program
SECTNO SMALLINT The section number of the statement
BINDERROR CHAR(1) Whether an SQL error was detected at bind time:

N No

Y Yes

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
VERSION VARCHAR(122) Version identifier for the package
VARCHAR(3500) Internal use only
ISOLATION CHAR(1) Isolation level for the SQL statement:

R RR (repeatable read)

T RS (read stability)

S CS (cursor stability)

U UR (uncommitted read)

L KEEP UPDATE LOCKS or an RS isolation

X KEEP UPDATE LOCKS for and RR isolation

blank WITH clause was not specified on statement. Isolation level is recorded in SYSPACKAGE.ISOLATION and

SYSPLAN.ISOLATION

STATUS CHAR(1) Status of binding the statement:

A Distributed – statement uses DB2 private protocol access

B Distributed – statement uses DB2 private protocol access

C Compiled – statement was bound successfully using defaults for input variables during access path selection

E Explain – statement is an SQL EXPLAIN statement

F Parsed – statement is either a data definition statement or a statement that did not bind successfully and VALIDATE(RUN) was used

G Compiled – statement bound successfully, but REOPT is specified

H Parsed – statement is either a data definition statement or a statement that did not bind successfully and VALIDATE(RUN) was used

I Indefinite – statement is dynamic

J Indefinite – statement is dynamic

K Control – CALL statement

L Bad – statement has some allowable error

M Parsed – statement references a table qualified with SESSION and was not bound because table reference could be for a declared temporary table that will not be defined until package or plan is run

blank Statement is non-executable, or was bound prior to V5

ACCESSPATH CHAR(1) For static statements, indicates if the access path for the statement is based on user-specified optimization hints. ‘H’ indicates that optimization hints were used
STMTNOI INTEGER If the value of STMTNO is zero, the column contains the statement number of the statement in the source program
SECTNOI INTEGER The section number of the segment
EXPLAINABLE CHAR(1) Contains one of the following values:

Y Indicates that the SQL statement can be used with EXPLAIN function and may have rows describing its access path in the userid.PLAN_TABLE

N Indicates that the SQL statement does not have any rows describing its access path in the userid.PLAN_TABLE

Blank Indicates that the SQL statement was bound prior to V7

QUERYNO INTEGER Query number of SQL statement in source program
ROWID ROWID ROWID column, created for the lob columns in this table
STATEMENT CLOB(2M) The complete text for the SQL statement that the row represents
BLOB(2M) Internal use only
STMT_ID BIGINT A unique statement identifier
EXPANSION CHAR(2) Applies to only static statements that reference archive tables or temporal tables

A Statement was bound with implicit query transformation as a result of SYSIBMADM.GET_ARCHIVE built-in global variable

B Statement was bound with implicit query transformation as a result of the CURRENT TEMPORAL BUSINESS_TIME

S Statement was bound with implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME

SB Statement was bound with implicit query transformation as a result of CURRENT TEMPORAL SYSTEM_TIME register and CURRENT TEMPORAL BUSINESS_TIME

Blank

QUERYID BIGINT Identifier for record in SYSQUERY
QUERY_HASH CHAR(6) Hash key for records in SYSQUERY
QUERY_HASH_

VERSION

INTEGER Hash version for records in SYSQUERY

SYSIBM.SYSPACK.STMT_STMB

An auxiliary table for the STMTBLOB column of SYSIBM.SYSPACKSTMT

Column name Data type Description
BLOB(2M) Internal use only

SYSIBM.SYSPACKSTMT_STMT

An auxiliary table for the STMTBLOB column SYSIBM.SYSPACKSTMT

Column name Data type Description
STATEMENT CLOB(2M) The complete text for the SQL statement that the row represents

SYSIBM.SYSPARMS

Contains a row for each parameter of a routine or multiple rows for table parameters

Column name Data type Description
SCHEMA VARCHAR(128) Schema of the routine
OWNER VARCHAR(128) Owner of the routine
NAME VARCHAR(128) Name of the routine
SPECIFICNAME VARCHAR(128) Specific name of the routine
ROUTINETYPE CHAR(1) Type of routine:

F User-defined function or cast function

P Stored procedure

CAST_

FUNCTION

CHAR(1) Whether the routine is a cast function:

N Not a cast function

Y A cast function

PARMNAME VARCHAR(128) Name of parameter
ROUTINEID INTEGER Internal identifier to routine
ROWTYPE CHAR(1) Values indicate type of parameter describe by this row:

P Input parameter

O Output parameter; not applicable for functions

B Both an input and output; N/A for functions

R Result before casting; N/A for stored procedures

C Result after casting; not applicable for stored procedures

S Input parameter of the underlying built-in source function

ORDINAL SMALLINT If ROWTYPE is B, O, P, or S, value is the ordinal number of the parameter within the routine signature.

If ROWTYPE is C or R, value depends on type of function:

· For a scalar function the value is 0.

· For a table function, the value is the ordinal number of the column of the output table

If ROWTYPE is X, the value is 0

TYPESCHEMA CHAR(8) Schema of the data type of the parameter
TYPENAME CHAR(18) Name of the data type of the parameter
DATATYPEID INTEGER For a built-in data type, internal ID of the built-in type. For a distinct type, internal ID of distinct type
SOURCETYPEID INTEGER For a built-in data type, 0. For a distinct type, internal ID of the built-in data type upon which the distinct type is sourced
LOCATOR CHAR(1) Indicates whether a locator to a value, instead of actual value, is to be passed as input value when routine is called:

N Actual values to be passed

Y A locator to a value is to be passed

TABLE CHAR(1) The data type of a column for a table parameter

N This is not a table parameter

Y This is a table parameter

TABLE_COLNO SMALLINT For table parameters, the column number of the table. Otherwise, the value is 0
LENGTH INTEGER Length attribute of parameter or result
SCALE SMALLINT Scale of data type of parameter or number of fractional second digits of timestamp or timestamp with time zone parameter
SUBTYPE CHAR(1) If data type is a distinct type, subtype of the distinct type, which is based on the subtype of its source type:

B FOR BIT DATA

S FOR SBCS DATA

M FOR MIXED DATA

blank Source type is not a character type, or if parameter is an array type

CCSID INTEGER CCSID of data type for a character, date, time, timestamp or graphic data type
CAST_FUNCTION_ID INTEGER Internal function ID of function used to cast argument, if this function is sourced on another function, or result. Otherwise 0. Not applicable for stored procedures
ENCODING_

SCHEME

CHAR(1) Encoding scheme of the parameter:

A ASCII

E EBCDIC

U UNICODE

blank The source type is not a character type or is the parameter is an array type

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
VERSION VARCHAR(122) Version identifier for routine. Column is a zero-length string if values of ORIGIN is not ‘I’ or if rows were created prior to V9
OWNERTYPE CHAR(1) Indicates the type of owner:

blank Authorization ID

L Role

SYSIBM.SYSPENDINGDDL

Contains information which objects have pending definition changes

Column name Data type Description
DBNAME VARCHAR(24) Name of the database for the pending option
TSNAME VARCHAR(24) Name of the table space for the pending option
DBID SMALLINT Internal identifier of the database
PSID SMALLINT Internal identifier of the tablespace page set descriptor
OBJSCHEMA VARCHAR(128) The qualifier of the object that contains the pending option
OBJNAME VARCHAR(128) Name of the object that contains the pending option
OBJOBID SMALLINT Internal identifier of the object
OBJTYPE CHAR(1) Type of update identified by OBJSCHEMA and OBJNAME

I Index

S Table space

T Table

STATEMENT_

TYPE

CHAR(1) The type of the statement of the pending option

A An ALTER statement

R A RECOVER statement

OPTION_ENVID INTEGER Intend identifier of the environment for the pending option
OPTION_

KEYWORD

VARCHAR(128) If row is inserted into this table during execution of a data definition statement, this value is name of pending option
OPTION-VALUE VARCHAR(4000) If row is inserted into this table during execution of a data definition statement, this value is name of pending option
OPTION_SEQNO SMALLINT The sequence of the pending option within the statement
CREATEDTS TIMESTAMP(12) Timestamp in the pending option was created
RELCREATED CHAR(1) The release of DB2 that is used to create the object
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
ROWID ROWID ID to support LOB columns for source text
STATEMENT_

TEXT

CLOB(2M) Source text of the original statement for the pending option
COLNAME VARCHAR(128) Name of the column of the pending definition change
PARTITION SMALLINT Partition number for partition with pending definition change. 0 of pending change is for entire table space or index space
PARTITION_

KEYWORD

VARCHAR(18) Column is populated if PARTITION column has a non-zero value. The keyword that is associated with the PARTITION clause of the ALTER TABLE statement
COLUMN_

KEYWORD

VARCHAR(18) Contains the keyword that corresponds to the column that is listed in COLNAME
REORG_SCOPE_

LOWPART

SMALLINT Logical partition number for lowest partition in range for REORG to materialize change
REORG_SCOPE_

HIGHPART

SMALLINT Logical partition number for highest partition in range for REORG to materialize change

SYSIBM.SYSPENDINGOBJECTS

Contains the name of and OBID informationa about objects that are the pending creation

Column name Data type Description
DBNAME VARCHAR(24) Name of the database
TSNAME VARCHAR(24) Name of the data table space
DBID SMALLINT Internal identifier of the database
PSID SMALLINT Internal identifier of the base table space page set descriptor
PARTITION SMALLINT Partition number with which the object is associated
COLNAME VARCHAR(128) Name of column contained in base table space with which object is associated
OBJSCHEMA VARCHAR(128) Qualifier of the object
OBJNAME VARCHAR(128) Name of the object
OBJTYPE CHAR(1) Type of object identified by OBJSCHEMA and OBJNAME

I Index

S Table space

T Table

INDEXSPACE VARCHAR(24) Name of index space. Empty string if object is not an index
OBJOBID SMALLINT Internal identifier of the object
OBJPSID SMALLINT Internal identifier of the object page set descriptor, or 0 if the object does not have a page set descriptor

SYSIBM.SYSPKSYSTEM

Contains zero or more rows for every package

Column name Data type Description
LOCATION VARCHAR(128) Always contains blanks
COLLID VARCHAR(128) Name of the package collection
NAME VARCHAR(128) Name of the package
CONTOKEN CHAR(8) Consistency token for the package
SYSTEM VARCHAR(24) Environment. Values can be:

BATCH TSO batch

CICS Customer Information Control System

DB2CALL DB2 call attachment facility

DLIBATCH DLI batch support facility

IMSBMP IMS BMP region

IMSMPP IMS MPP and IFP region

REMOTE remote application server

ENABLE CHAR(1) Connections represented by the row are enabled or disabled:

N Disabled

Y Enabled

CNAME VARCHAR(60) Identifies connection or connections to each row applies
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSPLAN

Contains one row for each application plan

Column name Data type Description
NAME VARCHAR(24) Name of the application plan
CREATOR VARCHAR(128) Authorization ID of the owner of the application plan
CHAR(6) Not used
VALIDATE CHAR(1) Whether validity checking can be deferred until run time:

B All checking must be performed during bind

R Validation is done at run time for tables, views, and privileges that do not exist at bind time

ISOLATION CHAR(1) Isolation level for the plan:

R RR (repeatable read)

T RS (read stability)

S CS (cursor stability)

U UR (uncommitted read)

VALID CHAR(1) Whether the application plan is valid:

A ALTER TABLE changed the description of the table or base table of a view that is referred to by the plan

H ALTER TABLE changed the description of the table or base table of a view that is referred to by the plan

N No

Y Yes

OPERATIVE CHAR(1) Whether the application plan can be allocated:

N No, an explicit BIND or REBIND is required before the plan can be allocated

Y Yes

CHAR(9) Not used
PLSIZE INTEGER Size of the base section of the plan, in bytes
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
AVGSIZE INTEGER Average size, in bytes, of those sections of the plan that contain SQL statements processed at bind time
ACQUIRE CHAR(1) When resources are acquired:

A At allocation

U At first use

RELEASE CHAR(1) When resources are released:

C At commit

D At deallocation

EXPLAIN CHAR(1) EXPLAIN option specified for the plan

N No

Y Yes

EXPREDICATE CHAR(1) CURRENTDATA option when the plan was bound or rebound:

B Data currency is not required for ambiguous cursors. Allow blocking for ambiguous cursors

C Data currency is required for ambiguous cursors. Inhibit blocking for ambiguous cursors

N Blocking is inhibited for ambiguous cursors, but plan was created before the CURRENTDATA option was available

BOUNDBY VARCHAR(128) Primary authorization ID of the binder of the plan
QUALIFIER VARCHAR(128) Implicit qualifier for the unqualified table, view, index, and alias names in the static SQL statements of the plan
CACHESIZE SMALLINT Size, in bytes, of cache to be acquired for the plan. 0 indicates that no cache is used
PLENTRIES SMALLINT Number of package list entries for the plan
DEFERPREP CHAR(1) Package was last bound with the DEFER(PREPARE) option:

N No

Y Yes

CURRENT_

SERVER

VARCHAR(128) Location name specified with the CURRENTSERVER option when the plan was last bound
SYSENTRIES SMALLINT Number of rows associated with the plan in SYSPLSYSTEM
DEGREE CHAR(3) Degree option used when the plan was last bound:

ANY DEGREE(ANY)

1 or blank DEGREE(1). Blank if the plan was migrated

SQLRULES CHAR(1) SQLRULES option used when the plan was last bound:

D or blank SQLRULES(DB2)

S SQLRULES(STD)

Blank A migrated plan

DISCONNECT CHAR(1) DISCONNECT option used when the plan was last bound:

E or blank DISCONNECT (EXPLICIT)

A DISCONNECT (AUTOMATIC)

C DISCONNECT (CONDITIONAL)

blank A migrated plan

GROUP_MEMBER VARCHAR(24) Member name of subsystem that performed most recent bind
DYNAMICRULES CHAR(1) DYNAMICRULES option used when the plan was last bound:

B Bind

R Run

BOUNDTS TIMESTAMP Time when the plan was bound
REOPTVAR CHAR(1) Whether the access path is determined again at execution time using input variable values:

A REOPT(AUTO)

N REOPT(NONE)

Y REOPT(ALWAYS)

1 REOPT(ONCE)

KEEPDYNAMIC CHAR(1) Prepared dynamic statements are to be purged at each commit

N KEEPDYNAMIC(NO)

Y KEEPDYNAMIC(YES)

PATHSCHEMAS VARCHAR(254) SQL path specified on the BIND or REBIND command that bound the plan
DBPROTOCOL CHAR(1) Whether remote access for SQL with three-part names as implement with DRDA or DB2 private protocol access:

D DRDA

P DB2 private protocol

FUNCTIONTS TIMESTAMP Timestamp when the function was resolved. Set by the BIND and REBIND commands but not by AUTOBIND
OPTHINT CHAR(8) Value of the OPTHINT bind option. Identifies rows in the authid.PLAN_TABLE to be used as input to the optimizer. Blank if no rows in the authid.PLAN_TABLE are to be used as input
ENCODING_

CCSID

INTEGER CCSID corresponding to encoding scheme or CCSID as specified for bind option ENCODING.

CCSID Specified or derived CCSID

0 EBCDIC default CCSID as specified on panel DSNTIPF at installation time

IMMEDWRITE CHAR(1) When writes of updated group bufferpool dependent pages are to be done. Applicable only for data sharing environments.

N IMMEDWRITE(NO) – normal write activity is done

Y IMMEDWRITE(YES) – immediate writes are done for updated group buffer pool dependent pages

1 IMMEDWRITE(PH1) – updated bufferpool dependent pages are written at or before phase 1 commit

Blank A migrated package

RELBOUND CHAR(1) The release when the packages was bound or rebound.

blank Bound prior to V7

K Bound on V7

L Bound on V8

REMARKS VARCHAR(128) A character string provided by the user with COMMENT
CREATORTYPE CHAR(1) Indicator the type of creator

blank Authorization ID

L Role

ROUNDING CHAR(1) The ROUNDING option used when the plan was last bound:

C ROUND_CEILING

D ROUND_DOWN

F ROUND_FLOOR

G ROUND_HALF_DOWN

E ROUND_HALF_EVEN

H ROUND_HALF_UP

U ROUND_UP

Blank Plan was created prior to V9

DATE Not used
CONCUR_ACC_

RES

CHAR(1) CONCURRENTACCESSRESOLUTION option when the package was bound or rebound:

blank Not specified

N WAITFOROUTCOME

Y USECURRENTLYCOMMITTED

PROGAUTH CHAR(1) DB2 checks if a program is authorized to run a plan:

D DISABLE

E ENABLE

SYSIBM.SYSPLANAUTH

Records the privileges that are held by users over application plans

Column name Data type Description
GRANTOR VARCHAR(128) Authorization ID of user who granted privileges
GRANTEE VARCHAR(128) Authorization ID of user who holds privileges. Could be PUBLIC
NAME VARCHAR(24) Name of the application plan on which the privileges are held
AUTHOWGOT CHAR(1) Authorization level of the user from whom the privileges were received. Not necessarily highest authorization level of grantor.

Blank Not applicable

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

S SYSADM

BINDAUTH CHAR(1) GRANTEE can use the BIND, REBIND, or FREE subcommands against the plan:

blank Privilege not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

EXECUTEAUTH CHAR(1) GRANTEE can run application programs that use the plan:

blank Privilege not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
GRANTEETYPE CHAR(1) Indicates the type of grantee:

blank Authorization ID

L Role

GRANTORTYPE CHAR(1) Indicates the type of grantor:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with the most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSPLANDEP

Dependencies of plans on tables, views, aliases, tablespaces, indexes, functions and procedures.

Column name Data type Description
BNAME VARCHAR(128) The name of an object the plan depends on
BCREATOR VARCHAR(128) If BNAME is a tablespace, its database
BTYPE CHAR(1) Type of object identified by BNAME:

A Alias

E INSTEAD OF trigger

F User-defined function or cast function

I Index

L Role

M Materialized query table

O Stored procedure

P Partitioned table space if it is defined as LARGE or with the DSSIZE parm

Q Sequence object

R Tablespace

S Synonym

T Table

V View

DNAME VARCGHAR(24) Name of plan
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSPLSYSTEM

Each row represents one or more connections to an environment in which plan could be used

Column name Data type Description
NAME VARCHAR(24) Name of the plan
SYSTEM VARCHAR(24) Environment. Values can be:

BATCH TSO batch

DB2CALL DB2 call attachment facility

CICS Customer Information Control System

DLIBATCH DLI batch support facility

IMSBMP IMS BMP region

IMSMPP IMS MPP or IFP region

ENABLE CHAR(1) Connections represented by the row are enable or disabled:

N Disabled

Y Enabled

CNAME VARCHAR(60) Connection or connections to which the row applies
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSQUERY

Contains one row for each query in a set of queries

Column name Data type Description
QUERYID BIGINT Unique identifier for the query
QUERY_HASH CHAR(16) Hash key generated by statement text
SCHEMA VARCHAR(128) Default schema name for unqualified object in query or blank
QUERY_SEC_

HASH

CHAR(16) Hash key generated by the modified statement text
QUERY_HASH_

VERSION

INTEGER Version of the query hash

 

SOURCE SMALLINT The source of the query:

0 Instance-level plan hint

1 Plan stability static

2 Plan stability dynamic

USERFILTER CHAR(8) Filter name that is used to group a set of queries or blank
CHAR(128) Internal use only
PLAN_VALID CHAR(1) Whether plan hints are valid:

blank No plan hint exists for statement, but optimization

Y Plan hint exists in SYSQUERYPLAN for the statement

N Plan hint exists in SYSQUERYPLAN, but is invalid and not used

INVALID_

REASON

INTEGER When PLAN_VALID is N, contains reason code. -1, if PLAN_VALID is Y or blank
VARCHAR(128) Not used
COLLECTION VARCHAR(128) Name of the collection of the originating query or blank
PACKAGE VARCHAR(128) Name of the package of the originaing query or blank
VERSION VARCHAR(128) Version of the package of blank
AUTHID VARCHAR(128) Authorisation ID in effect when the query was captured or blank
BINDTIME TIMESTAMP Timestamp when package was bound or BIND QUERY was run
STMTNO INTEGER When SOURCE is 1, the statement number in the package. When SOURCE is 0 or 2, is -1
SECTNO INTEGER When SOURCE IS 1, section number in the package. When SOURCE is 0 or 2, is -1
STMTTEXT CLOB(2M) Text of SQL statement optimization hint or parameter applies to
QUERYNO INTEGER Query number
CLIENT_

USERID

VARCHAR(255) User ID of the client

 

CLIENT_

WRKSTNNAME

VARCHAR(255) Name of the client workstation

 

CLIENT_

APPLNAME

VARCHAR(255) Name of the client application

 

SELECTIVITY_

OVERRIDE

CHAR(1) Selectivity override in effect for the query:

Y In effect

N Not in effect

ACCESSPATH_

HINT

CHAR(1) Access paths are specified for matching statements:

Y Is specified and in effect

N Is not specified and in effect

blank might be specified. Must query SYSQUERYPLAN to determine whether an access path is specified

OPTION_

OVERRIDE

CHAR(1) Optimization parameters are in effect for matching statements:

Y Are in effect

N Not in effect

blank Might be in effect. Must query SYSQUERYOPTS catalog table to determine whether option override are in effect

 

SELECTIVITY_

VALID

CHAR(1) Selectivity overrides are valid:

blank None exist for the statement

Y Overrides exist for query. Are valid if statement has already been executed and overrides were used

N Overrides exist but are invalid and not used

FUNCTION_LVL VARCHAR(10) Function level of the query when row was inserted

SYSIBM.SYSQUERYPREDICATE

Information about preicates for queries in SYSQUERY identified for extended optimization

Column name Data type Description
QUERYID BIGINT Unique identifier for the query
QUERYNO INTEGER A number identifying statement being explained
QBLOCKNO SMALLINT A number that identifies each query block within a query. Not in any particular order, nor are they necessarily consecutive
APPLNAME VARCHAR(24) Name of plan
POGNAME VARCHAR(128) Name of program ora package containing the statement been explained
PREDNO INTEGER Predicate number – used to identify a predicate within a query
TYPE CHAR(8) Predicate type. The possible values are:

AND, OR EQUAL, RANGE, BETWEEN, IN, LIKE, NOT LIKE, EXISTS, NOTEXIST, SUBQUERY, HAVING, OTHERS

LEFT_HAND_

SIDE

VARCHAR(128) If LHS of predicate is a table column (LHS_TABNO>0), indicates the column name. Other possible values are:

VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, EXPRESSION

Blank

LEFT_HAND_

PHO

INTEGER If LHS of predicate is a table column (LHS_TABNO>0), indicates the column name. Other possible values are:

VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, EXPRESSION

Blank

LHS_TABNO SMALLINT If LHS of predicate is a table column, indicates a number which uniquely identifies corresponding table reference within a query
LHS_QBNO SMALLINT If LHS of predicate is a table column, indicates a number which uniquely identifies corresponding table reference within a query
RIGHT_HAND_

SIDE

VARCHAR(128) If RHS of the predicate is a table column (RHS_TABNO>0), then indicates the column name. Other possible values are:

VALUE, COLEXP, NONCOLEXP, CORSUB, NONCORSUB, SUBQUERY, blank

RIGHT_HAND_

PNO

INTEGER If predicate is a compound predicate (AND/OR), then indicates the second child predicate
PHS_TABNO SMALLINT If RHS of predicate is a table column, indicates a number which uniquely identifies corresponding table reference within a query
RHS_QBNO SMALLINT If RHS of predicate is a subquery, indicates a number which uniquely identifies the corresponding query block within a query
FILTER_FACTOR FLOAT The estimated filter factor
BOOLEAN_TERM CHAR(1) Whether predicate can be used to determine truth value of the whole WHERE clothes
SEARCHARG CHAR(1) Whether this predicate can be processed by data manager (DM)
JOIN CHAR(1) If predicate can be used as join predicate between two tables
AFTER_JOIN CHAR(1) Indicates the predicate evaluation phase:

‘A’ After join

‘D’ During join

blank Not applicable

ADDED_PRED CHAR(1) Whether it is generated by transitive closure
REDUNDANT_

PRED

CHAR(1) Whether it is a redundant predicate
DIRECT_ACCESS CHAR(1) Whether the predicate is direct access, which means one can navigate directly to the row through ROWID
KEYFIELD CHAR(1) Whether the predicate includes index key column of the involved table for all applicable indexes considered by DB2
EXPLAIN_TIME TIMESTAMP The time when the EXPLAIN information was captured:

All cached statements When the statement entered the cache

Non-cached static statements – When the statement was bound

Non-cached dynamic statements – When EXPLAIN executed

CATEGORY SMALLINT Internal use
CATEGORY_B SMALLINT Internal use
TEXT VARCHAR(2000 Transformed predicate text; truncated if exceeds 2000 characters
PRED_ENCODE CHAR(1) Internal use
PRED_CCSID SMALLINT Internal use
PRED_MCCSID SMALLINT Internal use
MARKET CHAR(1) Whether predicate includes host variables, parameter markers, or special registers
PARENT_PNO INTEGER Parent predicate number. If this predicate is a root predicate within a query block, then this column is 0
NEGATION CHAR(1) Whether this predicate is negated via NOT
LITERALS VARHAR(128) Indicates literal value or literal values separated by colon symbols
CLAUSE CHAR(8) The clause where the predicate exists:

HAVING HAVING clause

ON ON clause

WHERE WHERE clause

SELECT SELECT clause

GROUP_MEMBER VARCHAR(24) Member name of DB2 that executed EXPLAIN. Blank if subsystem was not in a data sharing when EXPLAIN was executed
ORIGIN CHAR(1) Indicates the origin of the predicate

Blank Generated by DB2

C Column mask

R Row permission

U Specified by the user

UNCERTAINTY FLOAT(4) Describe uncertainty factor of a predicate’s estimated filter factor
SECTNOI INTEGER Section number of statement
COLLID VARCHAR(128) The collection ID
VERSION VARCHAR(122) Version identifier for package

SYSIBM.SYSQUERYSEL

Selectivity of predicates for queries in SYSQUERY table identified for extended optimization

Column name Data type Description
QUERYID BIGINT Unique identifier for the query
QUERYNO INTEGER A number that identifies the statement that is being explained
QBLOCKNO SMALLINT A number that identifies each query block within a query
APPLNAME VARCHAR(24) Name of plan for row
PROGNAME VARCHAR(128) Program or package containing statement being explained
PREDNO INTEGER Identifies predicate
INSTANCE SMALLINT Selectivity instance, which is used to group related selectivities
SELECTIVITY FLOAT Selectivity of the predicate
WEIGHT FLOAT Weight of selectively instance
ASSUMPTION VARHCAR(128) Indicates how the selectivity was estimated, or will be used

NORMAL – Estimated using the normal selectivity assumptions

OVERRIDE – To be as input Optimizer and override it’s selectivity estimation

INSERT_TIME TIMESTAMP Time when the row was inserted
EXPLAIN_TIME TIMESTAMP Time when the EXPLAIN information was captured

All cached statements – When statement entered cache Non-cached static statements When the statement was bound

Non-cached dynamic statements – When EXPLAIN executed

SYSIBM.SYSQUERY_AUX

An auxiliary table for the STMTTEXT column of the SYSIBM.SYSQUERY table

Column name Data type Description
STMTTEXT CLOB(2M) The full text of the query

SYSIBM.SYSQUERYOPTS

Contains optimization parameters for the queries that are in SYSIBM.SYSQUERY

Column name Data type Description
QUERYID BIGINT Unique identifier for the query. Corresponds to QUERYID column in SYSQUERY table
COPYID SMALLINT Version of the plan hints for the query in this row

0 Current version of plan hints

1 Previou version of the plan hints used by PLAN STABILITY

2 Original version of the plan hints used by PLAN STABILITY

REOPT CHAR(1) Value of the REOPT bind option that is in effect for the plan:

1 REOPT(ONCE)

A REOPT(AUTO)

N REOPT(NONE)

Y REOPT(ALWAYS)

Blank REOPT is not specified

STARJOIN CHAR(1) Whether star join is enabled:

Y Star join is enabled

N Star join is disabled

blank Star join is not specified

MAX_PAR_

DEGREE

INTEGER Maximum parallel degree. Value between 0 and 254. If value of column is -1, maximum parallel degree is not specified
DEF_CURR_

DEGREE

CHAR(3) Whether query parallelism is enabled:

ONE Query parallelism is disabled

ANY Query parallelism is enabled

blank Query parallelism is disabled

SJTABLES INTEGER Number of tables specified to qualify for star join processing
VARCHAR(128) Internal use only
GROUP_MEMBER VARCHAR(24) Group member name to which parameters are to be applied
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSQUERYPLAN

Plan hint information for queries in SYSIBM.SYSQUERY table

Column name Data type Description
QUERYID BIGINT Unique identifier for query. Corresponds to the query column in SYSQUERY table
COPYID SMALLINT Version of the plan hints for the query in this row

0 Current version plan hints

1 Previous version of plan hints used by PLAN STABILITY

2 Original version of plan hints used by PLAN STABILITY

PLAN_VALID CHAR(1) Whether the plan hints are valid:

N Invalid

Y Valid

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
QBLOCKNO SMALLINT A number that identifies each query block within a query. Value of numbers are not in any particular order, or consecutive
PLANNO SMALLINT Number of steps in which query that is indicated in QBLOCKNO was processed. Indicates order in which steps were executed
METHOD SMALLINT Indicates the join method used for the step:

0 = First table accessed, continuation of previous table accessed, or not used

1 = Nested loop join

2 = Merge scan join

3 = Sorts needed by ORDER BY, GROUP BY, SELECT DINSTINCT, UNION, a quantified predicate, or an IN predicate. T.

4 = Hybrid join

CREATOR VARCAHR(128) Creator of new table accessed in this step; blank if METHOD is 3
TNAME VARCHAR(128) Name of a table, materialized query table, created or declared temporary table, materialized view, or materialized table expression
SMALLINT IBM use only
ACCESSTYPE CHAR(2) Method accessing new table:

DI= Intersection of multiple DOCID lists to return final DOCID list

DU= Union of multiple DOCID lists to return the final day DOCID list

DX= XML index scan of index in ACCESSNAME to return a DOCID list

E= Direct row using a row change timestamp column

H= Hash access. IF an overflow condition occurs, hash overflow index in ACCESSCREATOR and ACCESSNAME is used

HN= Hash access using an IN predicate

IN= Index scan when matching predicate contains an IN predicate and IN-list is accessed through an in-memory table

I= Index (identified in ACCESSCREATOR and ACCESSNAME)

I1= One-fetch index scan

M= Multiple index scan (followed by MX, MI, MH, or MU)

MH= Hash overflow index named in ACCESSNAME

MX= Index scan on index in ACCESSNAME. When the access method MX follows the access method DX, DI, or DU, the table is accessed by the DOCID index using the DOCID list returned by DX, DI, or DU

MI= Intersection of multiple indexes

MU= Union of multiple indexes

N= Index scan when matching predicate contains IN keyword or by an index scan when DB2 rewrites a query using IN keyword

NR= Range list access

O= Work file scan, as a result of a subquery

P= Dynamic pair-wise index scan

R= Table space scan

RW= Work file scan of a result of a materialized user-defined table function

V= Buffers for an INSERT statement within a SELECT

Blank= Not applicable to the current row

MATCHCOLS SMALLINT For ACCESSTYPE I, I1, N, NR, MX or DX, number of index keys used in an index scan; otherwise 0
ACCESSCREATOR VARCHAR(128) For ACCESSTYPE I, I1, N, NR, MX or DX, the creator of the index; otherwise, blank
ACCESSNAME VARCHAR(128) For ACCESSTYPE I, I1, H, MH, N, NR, MX or DX, the name of the index: for ACCESSTYPE P, DSNPJW (mixopseqno) is the starting pair-wise join leg in MIXOPSEQNO; otherwise, blank
INDEXONLY CHAR(1) Whether access to an index alone is enough to carry out the step, or whether data, too, must be accessed. Y=Yes; N=No
SORTN_UNIQ CHAR(1) If new table is sorted to remove duplicate rows. Y=Yes; N=No
SORTN_JOIN CHAR(1) If new table is sorted for join method 2 or 4. Y=Yes; N=No
SORTN_ORDERBY CHAR(1) If new table is sorted for ORDERBY. Y=Yes; N=No
SORTN_GROUPBY CHAR(1) If new table is sorted for GROUPBY. Y=Yes; N=No
SORTC_UNIQ CHAR(1) If composite table is sorted to remove duplicate. Y=Yes; N=No
SORTC_JOIN CHAR(1) If composite table is sorted for join method 1, 2 or 4. Y=Yes; N=No
SORTC_ORDERBY CHAR(1) If composite table is sorted for an ORDERBY clause or a quantified predicate. Y=Yes; N=No
SORTC_GROUPBY CHAR(1) If composite table is sorted for a GROUPBY clause. Y=Yes; N=No
TSLOCKMODE CHAR(3) An indication of the mode of lock to be acquired on the new table or its table space or table space partitions. If the isolation can be determined at bind time, the values are:

IS = Intent share lock

IX = Intent exclusive lock

S = Share lock

U = Update lock

X = Exclusive lock

SIX = Share with intent exclusive lock

N = UR isolation, no lock

If the isolation cannot be determined at bind time, the lock mode determined by the isolation at runtime is shown by the following values.

NS=For UR isolation, no lock; for CS, RS or RR, an S lock

NIS=For UR isolation, no lock; for CS, RS or RR, an IS lock

NSS=For UR isolation, no lock; for CS or RS, an IS lock; for RR, an S lock

SS=For UR,CS or RS isolation, an IS lock; for RR, an S lock

PREFETCH CHAR(1) Weather data pages are to be read in advance by prefetch:

D = Optimizer expects dynamic prefetch

S = Pure sequential prefetch

L = Prefetch through a page list

U = List prefetch with an unsorted RID list

Blank = unknown at bind time or no prefetch

COLUMN_FN_

EVAL

CHAR(1) When a SQL aggregate function is evaluated:

R = While the data is being read from the table or index

S = While performing a sort to satisfy a GROUP BY clause

Blank = After a data retrieval after any sorts

MIXOPSEQ SMALLINT Sequence number of a step in a multiple index operation:

1, 2,…n = steps of multiple index procedure (ACCESSTYPE is MX, MI, MU, DX, DI, or DU), sequence number of OR predicate in SQL statement (ACCESSTYPE is NR)

0 = For any other rows

ACCESS_DEGREE SMALLINT Number of parallel tasks or operations activated by query
ACCESS_PGROUP

_ID

SMALLINT Identifier of the parallel group for accessing the new table
JOIN_DEGREE SMALLINT Number of parallel operations or tasks used in joining a composite table with new table
JOIN_PGROUP_

ID

SMALLINT Identifier of parallel group for joining composite table with new table
SORTC_PGROUP_

ID

SMALLINT Parallel group identifier for parallel sort of the composite table
SORTN_PGROUP_

ID

SMALLINT Parallel group identifier for the parallel sort of the new table
PARALLELISM_

MODE

CHAR(1) The kind of parallelism, if any, that is used at bind time:

C=Query CP parallelism

MERGE_JOIN_

COLS

SMALLINT Number of columns joined during merge scan join (Method=2)
CORRELATION_

NAME

VARCHAR(128) Correlation name of a table or view specified in the statement
PAGE_RANGE CHAR(1) If table qualifies for page range screening, so that plans scan only the partitions that are needed. Y=Yes; blank=No
JOIN_TYPE CHAR(1) The type of an outer join:

F = Full outer join

L = Left outer join

P = Pair-wise join

S = Star join

Blank = Inner join or no join

RIGHT OUTER JOIN coverts to a LEFT OUTER JOIN when you use it, so that JOIN_TYPE contains L

QBLOCK_TYPE CHAR(6) For each query block, the type of SQL operation performed. For the outermost query, the column identifies a statement type. Possible values:

SELECT = SELECT

INSERT = INSERT

UPDATE = UPDATE

MERGE = MERGE

DELETE = DELETE

SELUPD = SELECT with FOR UPDATE OF

DELCUR = DELETE WHERE CURRENT OF CURSOR

UPDCUR = UPDATE WHERE CURRENT OF CURSOR

CORSUB = Correlated subquery

TRUNCA = TRUNCATE

NCOSUB = Noncorrelated subquery

TABLEX = Table expression

TRIGGR = WHEN clause on CREATE TRIGGER

UNION = UNION

UNIONA = UNION ALL

INTERS = INTERSECT

INTERA = INTERSECT ALL

EXCEPT = EXCEPT

EXCEPTA = EXCEPT ALL

PRIMARY_

ACCESSTYPE

CHAR(1) Indicates whether direct row access will be attempted first:

D = DB2 will try to use direct row access

T = Base table or result file is materialized into a work file, and work file is accessed via sparse index access

PARENT_QBLOCK SMALLINT Number that indicates QBLOCKNO of the parent query block
TABLE_TYPE CHAR(1) The type of new table:

B = Buffers for SELECT from INSERT, SELECT from UPDATE, SELECT from MERGE, or SELECT from DELETE statement

C = Common table expression

F = Table function

I = New table is generated from an IN-LIST predicate

M = Materialized query table

Q = Temporary intermediate result table (not materialized)

R = Recursive common table expression

S = Subquery (correlated or non-correlated)

T = Table

W = Work file

Value of column is null if the query uses GROUP BY, ORDER BY, or DISTINCT, which requires an implicit sort

TABLE_ENCODE CHAR(1) Encoding scheme of table. If table has a single CCSID set, possible values are:

A = ASCII

E = EBCDIC

U = UNICODE

M = The table contains multiple CCSID sets

TABLE_SCCSID SMALLINT SBCS CCSID value of table. If TABLE_ENCODE is M, Value 0
TABLE_MCCSID SMALLINT Mixed CCSID value of table. If TABLE_ENCODE is M, value is 0. If MIXED=NO in application defaults module, value is -2
TABLE_DCCSID SMALLINT DBCS CCSID value of table. If TABLE_ENCODE is M, value is 0. If MIXED=NO in application defaults module, value is -2
CTREF SMALLINT If reference table is a common table expression, value is top-level query block number
PARENT_PLANNO SMALLINT Corresponds to the plan number in parent query block where a correlated subquery is involved
EXPANSION_

REASON

SMALLINT Applies to only static statements that reference archive tables or temporal tables.

For dynamic statements, this is blank. For static statements:

A Bound with implicit query transformation as a result of SYSIBMADM.GET_ARCHIVE built-in global variable

B Bound with implicit query transformation as a result of the CURRENT TEMPORAL BUSINESS_TIME

S Bound with implicit query transformation as a result of the CURRENT TEMPORAL SYSTEM_TIME

SB Bound with implicit query transformation as a result of CURRENT TEMPORAL SYSTEM_TIME register and CURRENT TEMPORAL BUSINESS_TIME

Blank Statement does not contain implicity query transformation

SYSIBM.SYSRELS

Contains one row for every referential constraint

Column name Data type Description
CREATOR VARCHAR(128) Schema of owner of dependent table of the referential constraint
TBNAME VARCHAR(128) Name of the dependent table of the referential constraint
RELNAME VARCHAR(128) Constraint name
REFTBNAME VARCHAR(128) Name of the parent table of the referential constraint
REFTBCREATOR VARCHAR(128) Schema of the owner of the parent table
COLCOUNT SMALLINT Number of columns in the foreign key
DELETERULE CHAR(1) Type of delete rule for the referential constraint:

A NO ACTION

C CASCADE

N SET NULL

R RESTRICT

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
RELOBID1 SMALLINT Internal identifier of constraint with respect to database containing parents table
RELOBID2 SMALLINT Internal identifiers of constraint with respect to database that contains dependent table
TIMESTAMP TIMESTAMP Date and time constraint was defined
IXOWNER VARCHAR(128) Schema of unique non-primary index used for parent key
IXNAME VARCHAR(128) Name of unique non-primary index used for a parent key
ENFORCED CHAR(1) Enforced by the system or not:

Y Enforced by the system

N Not enforced by the system(trusted)

CHECKEXISTING

DATA

CHAR(1) Option for checking existing data

I Immediately check existing data

N Never check existing data

T Immediately check existing data for a temporal referential constraint

RELCREATED CHAR(1) Release of DB2 that is used to create the object

SYSIBM.SYSREAUTH

Records CREATE IN and PACKADM ON privileges for collections; USAGE privileges for distinct types; and USE privileges for buffer pools, storage groups and tablespaces

Column name Data type Description
GRANTOR VARCHAR(128) Authorization ID of the user who granted the privilege
GRANTEE VARCHAR(128) authorization ID of the user who holds the privilege.Could be PUBLIC
QUALIFIER VARCHAR(128) Qualifier of object
NAME VARCHAR(128) Name of buffer pool, collection, DB2 storage group, distinct type, or tablespace ALL if USE OF ALL BUFFERPOOLS is granted
AUTHHOWGOT CHAR(1) Authorization level of user from whom privileges were received. Level not necessarily highest authorization level of the grantor.

Blank Not applicable

A PACKADM (on collection*)

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

S SYSADM

P PACKADM (on a specific collection)

T DATAACCESS

OBTYPE CHAR(1) Type of object:

B Buffer pool

C Collection

D Distinct type

R Tablespace

S Storage group

J JAR (Java Archive file)

USEAUTH CHAR(1) Whether the privilege is held with the GRANT option:

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

Authority held is PACKADM when OBTYPE is C (a collection) and QUALIFIER is PACKADM. Authority held is CREATE IN when the OBTYPE is C and QUALIFIER is blank

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
GRANTEETYPE CHAR(1) Indicates the type of grantee:

blank Authorization ID

L Role

GRANTORTYPE CHAR(1) Indicates type of grantor:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Time was deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSROLES

Contains one row for each role

Column name Data type Description
NAME VARCHAR(128) Name of the role
DEFINER VARCHAR(128) Auth ID or role that defined this role listed in the NAME column
DEFINERTYPE CHAR(1) Type of definer:

L Role

Blank Authorization ID

CREATEDTS TIMESTAMP Time when the role is created
RELCREATED CHAR(1) Release of DB2 that is used to create the role
REMARKS VARCHAR(762) A character string that is provided using COMMENT statement
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSROUTINEAUTH

Privileges held by users on routines. (UDF, cast function, or stored procedure)

Column name Data type Description
GRANTOR VARCHAR(128) Authorization ID of the user who granted the privilege
GRANTEE VARCHAR(128) Authorization ID of the user who holds the privilege or the name of a plan or package that uses the privilege. Can also be PUBLIC
SCHEMA VARCHAR(128) Schema of the routine
SPECIFICNAME VARCHAR(128) Specific name of routine. * if privilege held on all routines in schema
GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
ROUTINETYPE CHAR(1) Type of routine:

F User-defined functions or cast function

P Stored procedure

GRANTEETYPE CHAR(1) Type of grantee:

blank An authorization ID

L Role

P Plan or package. Package of COLLID is not blank

R Internal use only

AUTHHOWGOT CHAR(1) Authorization level of user from privileges were received. Not necessarily highest authorization level of grantor. Also used to indicate that the privilege was held on all schemas by grantor.

blank Not applicable

1 Grantor had privilege on schema.* at time of grant

E SECADM

G ACCESSCTRL

L SYSCTRL

S SYSADM

T DATAACCESS

EXECUTEAUTH CHAR(1) GRANTEE can execute the routine:

Y Privilege is held without GRANT option

N Privilege is held with GRANT option

COLLID VARCHAR(128) If GRANTEE is a package, it’s collection name. Otherwise, blank
CONTOKEN CHAR(8) If GRANTEE is a package, consistency token of DBRM from which package was derived. Otherwise, blank
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
GRANTORTYPE CHAR(1) Indicates the type of grantor:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSROUTINES

Contains a row for every routine (User-defined function, cast function, or stored procedure)

Column name Data type Description
SCHEMA VARCHAR(128) Schema of the routine
OWNER VARCHAR(128) Owner of the routine
NAME VARCHAR(128) Name of the routine
ROUTINETYPE CHAR(1) Type of routine:

F User-defined function or cast function

P Store procedure

CREATEDBY VARCHAR(128) Primary auth ID under which the routine was created
SPECIFICNAME VARCHAR(128) Specific name of the routine
ROUTINEID INTEGER Internal identifier of the routine
RETURN_TYPE INTEGER Internal identifier of result data type of function. -2 if function is a table function
ORIGIN CHAR(1) Origin of the routine:

E External user-defined function or stored procedure

N Native SQL procedure

Q SQL function

U Sourced on user-defined function of built-in function

S System-generated function

FUNCTION_

TYPE

CHAR(1) Type of function:

C Column function

S Scalar function

T Table function

blank a stored procedure (ROUTINETYPE=’P’)

PARM_COUNT SMALLINT Number of parameters for the routine
LANGUAGE VARCHAR(24) Implementation language of the routine:

ASSEMBLE

C

COBOL

COMPJAVA

JAVA

PLI

REXX

SQL

Blank if ROUTINETYPE=’F’ and ORIGIN is not ‘E’ or not ‘Q’

COLLID VARCHAR(128) Name of package collection to be used when routine is executed
SOURCESCHEMA VARCHAR(128) If ORIGIN is ‘U’ and ROUTINETYPE is ‘F’, schema of source user-defined function (‘SYSIBM’ for a source built-in function). Otherwise, blank
SOURCE_

SPECIFIC

VARCHAR(128) If ORIGIN is ‘U’ and ROUTINETYPE is ‘F’. specific name of source user-defined function or source built-in function name. Otherwise, blank
DETERMINISTIC CHAR(1) Option of an external functional or stored procedure:

N Indeterminate (results may differ with a given set of input values)

Y Deterministic (results are consistent)

Blank ROUTINETYPE=’F’ and ORIGIN is not ‘E’ (routine is a function, but not an external function)

EXTERNAL_

ACTION

CHAR(1) External action option of an external function:

N Function has no side effects

E Function has external side effects so that number of invocations is important

Blank ORIGIN not ‘E’ for function (ROUTINETYPE=’F’), or it is a stored procedure (ROUTINETYPE=’P’)

NULL_CALL CHAR(1) CALLED ON INPUT option of an external function of stored procedure:

N Routine is not called if any parameter has a NULL value

Y Routine is called if any parameter has a NULL value

blank ROUTINETYPE=’F’ and ORIGIN is not ‘E’ (routine is a function, but not an external function)

CAST_FUNCTION CHAR(1) Whether the routine is a cast function:

N Not a cast function

Y Is a cast function

A cast function is generated by DB2 for a CREATE DISTINCT TYPE statement

SCRATCHPAD CHAR(1) SCRATCHPAD option of an external function:

N Does not have a SCRATCHPAD

Y Has a SCRATCHPAD

Blank ORIGIN is not ‘E’ for function (ROUTINETYPE=’F’), or it is a stored procedure (ROUTINETYPE=’P’)

SCRATCHPAD_

LENGTH

INTEGER Length of scratchpad if ORIGIN is ‘E’ for function (ROUTINETYPE=’F’) and NO SCRATCHPAD is not specified. Otherwise, 0
FINAL_CALL CHAR(1) FINAL CALL option of an external function:

N Final call will not be made to function

Y Final call will be made to function

blank ORIGIN is not ‘E’ for function (ROUTINETYPE=’F’), or it is a stored procedure (ROUTINETYPE=’P’)

PARALLEL CHAR(1) PARALLEL option of an external function:

A Function can be invoked by parallel tasks

D Function cannot be invoked by parallel tasks

blank ORIGIN is not ‘E’ for function (ROUTINETYPE=’F’), or it is a stored procedure (ROUTINETYPE=’P’)

PARAMETER_

STYLE

CHAR(1) PARAMETER STYLE option of an external function or stored procedure:

D DB2SQL

G GENERAL

N GENERAL CALL WITH NULLS

J JAVA

Blank if ORIGIN I not ‘E’ or if LANGUAGE is SQL

FENCED CHAR(1) Y Routing runs separately from DB2 address space in a WLM managed DB2 address space

Blank ORIGIN is ‘Q’ or ORIGIN is ‘N’

SQL_DATA_

ACCESS

CHAR(1) SQL statements are allowed in an external function or stored procedure:

C CONTAINS SQL: Only SQL that does not read or modify data is allowed

M MODIFIES SQL DATA: All SQL is allowed, including SQL that reads or modifies data

N NO SQL: SQL is not allowed

R READS SQL DATA: Only SQL that reads data is allowed

Blank Not applicable

DBINFO CHAR(1) Option of an external function or stored procedure:

N Parameter will not be passed to external function or stored procedure

Y Parameter will be passed to external function or stored procedure

Blank ORIGIN is not ‘E’

STAYRESIDENT CHAR(1) Determines whether routine is deleted from memory when routine ends

N Load module is to deleted from memory after routine terminates

Y Load module is to remain resident in memory after routine terminates

Blank ORIGIN is not ‘E’

ASUTIME INTEGER Number of CPU service units permitted for any single invocation of this routine
WLM_

ENVIRONMENT

VARCHAR(54) Name of WLM environment used to run this routine
WLM_ENV_FOR_

NESTED

CHAR(1) For nested routine calls, indicates whether address space of calling store procedure or user-defined function is used to run nested stored procedure or UDF:

N Nested stored procedure or UDF runs in an address space other than specified WLM environment. WLM ENVIRONMENT name was specified.

Y Nested stored procedure or UDF runs in the environment used by calling stored procedure or UDF. WLM ENVIRONMENT (name,*) was specified

Blank WLM_ENVIRONMENT is blank

PROGRAM_TYPE CHAR(1) Routine runs as a Language Environment main routine or a subroutine:

M Main routine

S Subroutine

Blank ORIGIN is not ‘E’

EXTERNAL_

SECURITY

CHAR(1) Specifies auth ID to be used if routine accesses resources protected by an external security product:

D DB2 – Auth ID associated with WLM stored procedure address space

U SESSION_USER – Auth ID of SQL user that invoked routine

C DEFINER – Aut ID of owner of routine

Blank ORIGIN is not ‘E’

COMMIT_ON_

RETURN

CHAR(1) If ROUTINETYPE=’P’, whether transaction is always to be committed immediately on successful return (non-negative SQLCODE) from stored procedure:

N Unit of work is to continue

Y Unit of work is to be committed immediately

If ROUTINETYPE=’F’, the value is blank

RESULT_SETS SMALLINT If ROUTINE=’P’, maximum number of ad hoc results sets that this stored procedure can return. If no ad hoc result exists or ROUTINETYPE=’F’, 0
LOBCOLUMNS SMALLINT If ORIGIN=’E’, number of LOB columns found in parameter list for this UDF. If no LOB columns are found in parameter list or ORIGIN is not ‘E’, 0
CREATEDTS TIMESTAMP Time CREATE statement was executed for routine
ALTEREDTS TIMESTAMP Time last ALTER statement was executed for routine
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
PARM1-30 SMALLINT Internal use only
IOS_PER_INVOC FLOAT Estimated number of I/Os required to execute routine
INSTS_PER_

INVOC

FLOAT Estimated number of machine instructions required to execute routine
INITIAL_IOS FLOAT Estimating number of I/Os performed first time or last time routine is invoked
INITIAL_INSTS FLOAT Estimating number of machine instructions performed first time or last time routine is invoked
CARDINALITY FLOAT Predicted cardinality of routine
RESULT_COLS SMALLINT For a table function, number of columns in result table
EXTERNAL_NAME VARCHAR(762) Path/module/function loaded to execute routine. Blank if ROUTINETYPE=’F’ and ORIGIN is not ‘E’
PARM_

SIGNATURE

VARCHAR(150) Internal use only
RUNOPTS VARCHAR(762) Language environment run-time options for routine.

Blank – installation default Language Environment run-time options are to be used, if ROUTINETYPE=’F’ and ORIGIN is not ‘E’

REMARKS VARCHAR(762) String provided by user with COMMENT ON
JAVA_

SIGNATURE

VARCHAR(3072 Signature of the jar file:

Blank When PARAMETER STYLE is not JAVA, or if ROUTINETYPE=’F’ and ORIGIN not ‘E’

CLASS VARCHAR(384) Class name contained in the jar file:

Blank When PARAMETER STYLE is not JAVA, or if ROUTINETYPE=’F’ and ORIGIN not ‘E’

JARSCHEMA VARCHAR(128) Schema of the jar file:

Blank When PARAMETER STYLE is not JAVA, or if ROUTINETYPE=’F’ and ORIGIN not ‘E’

JAR_ID VARCHAR(128) Name of the jar file:

Blank When PARAMETER STYLE is not JAVA, or if ROUTINETYPE=’F’ and ORIGIN not ‘E’

SPECIAL_REGS CHAR(1) SPECIAL REGISTER option for a routine:

I INHERIT SPECIAL REGISTER

D DEFAULT SPECIAL REGISTER

Blank ROUTINETYPE=’F’ and ORIGIN is not ‘E’ or not ‘Q’

NUM_DEP_MQTS SMALLINT Number of dependant MQTs
MAX_FAILURES SMALLINT Allowable failures for this routine (0-32767)
PARAMETER_

CCSID

INTEGER A CCSDI that specifies how character, graphic, date, time, and timestamp data types for system generated parameters to the routine such as message tokens and DBINFO should be passed

ASCII

EBCDIC

UNICODE

VERSION VARCHAR(122) Version identifier for a native SQL procedure
CONTOKEN CHAR(8) Consistency token for routine. X’20’ if ORIGIN not = ‘N’
ACTIVE CHAR(1) Identifies the active version of the routine:

Y Active version

N Not the active version

Blank ORIGIN not ‘N’ or row was created prior to V9

DEBUG_MODE CHAR(1) Whether or not routine is enabled for debugging:

1 Enabled for debugging and can be debunked in a client debug session using Unified Debugger

0 Not enabled for debugging

N Can never be enabled for debugging

blank LANGUAGE is not specified as JAVA, value of ORIGIN not ‘N’, or row was created prior to V9

TEXT_ENVID INTEGER Internal identifier environment
TEXT_ROWID ROWID ID to support LOB columns for source text
TEXT CLOB(2M) Source text of CREATE or ALTER with body for routine
OWNERTYPE CHAR(1) Indicates the type of owner:

blank Authorization ID

L Role

PARAMETER_

VARCHARFORM

INTEGER Non-zero value indicates actual representation, to a LANGUAGE C routine, of any varying length string parameter that appears in parameter list or RETURNS
RELCREATED CHAR(1) Release used to create object. Blank if prior to V9
PACKAGEPATH VARCHAR(4096 Value of PACKAGE PATH option of CREATE FUNCTION, CREATE PROCEDURE, ALTER FUNCTION, or ALTER PROCEDURE statement that created or last changed the routine
SECURE CHAR(1) Indicates if the routine is secured:

N The routine is not secured

Y The routine is secured

INLINE CHAR(1) Specifies if the SQL function is inline:

Y Is inline when referenced. No package is associated with this type of routine

N Has an associated package

blank Not an SQL function (ORIGIN not ‘Q’)

BLOB(1G)
SYSTEM_

DEFINED

CHAR(1) Identifies whether this routine is system defined:

blank Not system defined

S System defined

WRAPPED CHAR(1) Y Routine text is obfuscated

Blank Routine text is not obfuscated

REGENERATETS TIMESTAMP(12 Timestamp when object we regenerated

SYSIBM.SYSROUTINESTEXT

An auxiliary table for the TEXT column of SYSIBM.SYSROUTINES

Column name Data type Description
TEXT CLOB(2M) Source text of CREATE PROCEDURE for routine

SYSIBM.SYSROUTINES_OPTS

Contains a row for each generated routine. Can be inserted, updated. and deleted

Column name Data type Description
SCHEMA VARCHAR(128) Schema of the routine
ROUTINENAME VARCHAR(128) Name of the routine
BUILDDATE DATE Date the routine was built
BUILDTIME TIME Time the routine was built
BUILDSTATUS CHAR(1) Whether a version of the routine’s source is current version
BUILDSCHEMA VARCHAR(128) Schema name for BUILDNAME
BUILDNAME VARCHAR(128) Procedure used to create the routine
BUILDOWNER VARCHAR(128) Authorization ID used to create the routine
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
PRECOMPILE_

OPTS

VARCHAR(765) Precompiler options used to build the routine
COMPILE_OPTS VARCHAR(765) Compiler options used to build the routine
PRELINK_OPTS VARCHAR(765) Pre-link edit options used to build the routine
LINK_OPTS VARCHAR(765) Link edit options used to build the routine
BIND_OPTS VARCHAR(3072 Bind options used to build the routine
SOURCEDSN VARCHAR(765) Name of the source data set
DEBUG_MODE CHAR(1) Debugging is on or off for this object

0 Debugging is off

1 Debugging is on

SYSIBM.SYSROUTINES_PTREE

An auxiliary table for the PTREE column of the SYSIBM.SYSROUTINES table

Column name Data type Description
PTREE CLOB(2M) Internal use only

SYSIBM.SYSROUTINES.SRC

Contains source for generated routines. Can be inserted, updated, and deleted

Column name Data type Description
SCHEMA VARCHAR(128) Schema of the routine
ROUTINENAME VARCHAR(128) Name of the routine
BUILDDATE DATE Date the routine was built
BUILDTIME TIME Time the routine was built
BUILDSTATUS CHAR(1) Whether this version of routine’s source is current version
SEQNO INTEGER Number of the source statement piece in CREATESTMT
IBMREQD CHAR(1) Y indicates row came from the (MRM) tape
CREATESTMT VARCHAR(7500 Routine source statement

SYSIBM.SYSSCHEMAAUTH

One or more rows for each user granted a privilege on a particular schema in a database

Column name Data type Description
GRANTOR VARCHAR(128) Auth ID of user who granted privileges or SYSADM
GRANTEE VARCHAR(128) Auth ID of user or group that holds privileges. Can be PUBLIC
SCHEMANAME VARCHAR(128) Name of the schema or ‘*’ for all schemas
AUTHHOWGOT CHAR(1) Auth level of user from whom privileges were received

1 Grantor had privilege on all schemas at time of grant

E SECADM

G ACCESSCTRL

L SYSCTRL

S SYSADM

CREATEINAUTH CHAR(1) Grantee holds CREATIN privilege on schema:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

ALTERINAUTH CHAR(1) Grantee holds ALTERIN privilege on schema:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

DROPINAUTH CHAR(1) Grantee holds DROPIN privilege on schema:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
GRANTEETYPE CHAR(1) Type of grantee:

blank Authorization ID

L Role

GRANTORTYPE CHAR(1) Type of grantor:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSSEQUENCEAUTH

Records the privileges that are held by users over sequences

Column name Data type Description
GRANTOR VARCHAR(128) Auth ID of user who granted the privileges
GRANTEE VARCHAR(128) Auth ID of user or group that holds privileges or the name of an application plan or package that uses privileges
SCHEMA VARCHAR(128) Schema of the sequence
NAME VARCHAR(128) Name of the sequence
GRANTEETYPE CHAR(1) Type of grantee:

blank An authorization ID

L Role

P An application plan or package. The grantee is a package if COLLID is not blank

R Internal use only

AUHTHOWGOT CHAR(1) Auth level of user from whom the privileges were received. Not necessarily highest authorisation level of the grantor:

E SECADM

G ACCESSCTRL

L SYSCTRL

S SYSAMD

T DATAACCESS

ALTERAUTH CHAR(1) Grantee holds ALTER privilege on sequence:

Blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

USEAUTH CHAR(1) Grantee holds USAGE privilege on sequence:

Blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

COLLID VARCHAR(128) If GRANTEE is a package, it’s collection name. Else, 0
CONTOKEN CHAR(8) If GRANTEE is a package, consistency token of the DBRM from which package was derived. Otherwise, blank
GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
IBMREQD CHAR(1) Y indicates row came from the MRM tape
GRANTORTYPE CHAR(1) Type of grantor:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSSEQUENCES

Contains one row for each identity column or user-defined sequence

Column name Data type Description
SCHEMA VARCHAR(128) Schema of alias or sequence. For an identity column, value of TBCREATOR from SYSCOLUMNS entry for column
OWNER VARCHAR(128) Owner of alias or sequence. For an identity column, value of TBCREATOR from SYSCOLUMNS entry for column
NAME VARCHAR(128) Name of the identity column, alias or sequence. (Name for an identity is generated by DB2)
SEQTYPE CHAR(1) Type of sequence object:

A Alias

I Identity column

S User-defined sequence

X Implicitly created DOCID for a base table with XML

SEQUENCEID INTEGER Internal identifier of the identity column, alias or sequence
CREATEDBY VARCHAR(128) Primary auth ID of user who created sequence, alias or identity column
INCREMENT DECIMAL(31,0) Increment value (positive or negative) within INTEGER scope). 0 if alias
START DECIMAL(31,0) Start value. 0 if alias
MAXVALUE DECIMAL(31,0) Maximum value allowed for the data type. 0 if alias
MINVALUE DECIMAL(31,0) Minimum value allowed for the data type. 0 if alias
CYCLE CHAR(1) Whether cycling will occur when a boundary is reached:

N No

Y Yes

Blank if alias

CACHE INTEGER Number of sequence values to preallocate in memory for faster access. 0 indicates that values are not to be preallocated or an alias
ORDER CHAR(1) Whether the values must be generated in order:

Y Yes

N No

Blank if alias

DATATYPEID INTEGER For a built-in data type, the internal ID of the built-in type.

For a distinct type, the internal ID of the distinct type

SOURCETYPEID INTEGER For a built-in data type, 0. For a distinct type, internal ID of the built-in data type upon which distinct type is sourced
CREATEDTS TIMESTAMP Timestamp when identity column, alias, or sequence was created

 

ALTEREDTS TIMESTAMP Timestamp when identity column, alias, or sequence was ALTERed
MAXASSIGN_

EDVAL

DECIMAL(31,0) Last possible assigned value. Initialized to null when the sequence object is created. Updated each time next chunk of n values is cached, where n is the value for CACHE
IBMREQD CHAR(1) Y indicates row came from (MRM) tape
REMARKS VARCHAR(254) Character string provided by user with COMMENT statement. Blank for an identity column
PRECISION SMALLINT Precision defined for a sequence with a decimal or numeric type. 5 for SMALLINT, 10 for INTEGER, or actual precision specified be user for decimal data type. 0 for rows created prior to V8, or alias
RESTARTWITH DECIMAL(31,0) RESTART WITH value specified for a sequence during ALTER or NULL. RESTART WITH value is reset to NULL during first value generation after ALTER. NULL if no ALTER with RESTART WITH has happened or if an alias
OWNERTYPE CHAR(1) Type of owner:

Blank Authorization ID

L Role

RELCREATED CHAR(1) Release used to create object. Blank if created prior to V9
SEQSCHEMA VARCHAR(128) Schema of target sequence
SEQNAME VARCHAR(128) Name of target sequence

SYSIBM.SYSSEQUENCESDEP

Records the dependencies of identity columns on tables

Column name Data type Description
BSEQUENCEID INTEGER Internal identifier of the identity column or sequence
DCREATOR VARCHAR(128) Owner of object dependent on this identity column or sequence
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DNAME VARCHAR(128) Name of object dependent on this identity column or sequence
DCOLNAME VARCHAR(128) Name of the identity column. Blank for SQL function rows
DTYPE CHAR(1) Type of object is dependent on this sequence:

F SQL function

I Identity column

X Implicit DOCID column created on base table with XML

Blank

BSCHEMA VARCHAR(129) Schema name of sequence
BNAME VARCHAR(128) Sequence name (generated by DB2 for an identity column)
DSCHEMA VARCHAR(128) Qualifier of object dependent on sequence
DOWNER VARCHAR(128) Owner of object dependent on sequence
DOWNERTYPE CHAR(1) The type of owner:

Blank An authorization ID

L Role

SYSIBM.SYSSESSION

Stores the session token that was generated by the server and associated session data

Column name Data type Description
TOKEN CHAR(40) Session token for the session
CORRTKN VARCHAR(256) Extended client correlation token in use
GV_FLAGS CHAR(2) Flags for the internal classification of global variable
TOTAL CHAR(4) Number of entries in SYSSESSION_EX that correspond to session token
SPECIAL_

REGISTERS

VARCHAR(16000 Special register values

 

GLOBAL_

VARIABLES

BLOB(2G) Global variable values
ROWID ROWID Generated ROWID

SYSIBM.SYSSESSION_EX

Contains global variable data of LOB or array type that corresponds to the locator

Column name Data type Description
TOKEN CHAR(40) Session token for the session
LOCATOR CHAR(8) Locator value corresponding to one of the global variables with DATATYPE as an array of lobs
HEADER CHAR(89) Array static descriptor header when locator value corresponds to array type
GVID CHAR(8) Global variable identifier
DATATYPE CHAR(2) Datatype of the global variable
CSSID CHAR(2) CCSID of the global variable
GVSCHEMA VARCHAR(130) Schema name of the global variable
GVNAME VARCHAR(130) Name of the global variable
DATA BLOB(2G) Data value stored in the Global variable

SYSIBM.SYSSESSION_STATUS

Contain session token/timestamp value when session data last referenced

Column name Data type Description
TOKEN CHAR(40) Session token for the session
TOKEN_TS CHAR(16) Timestamp when row was last referenced
TOKEN_

MEMBER

CHAR(16) Flags for internal classification of global variable
STATUS CHAR(2) Status of session corresponding to the token

SYSIBM.SYSSTATFEEDBACK

Contains information about missing or conflicting catalog statistics SQL statements

Column name Data type Description
TBCREATOR VARCHAR(128) Creator of table
TBNAME VARCHAR(128) Name of table
COLNAME VARCHAR(128) Creator of the index
IXNAME VARCHAR(128) Name of the index
COLNAME VARCHAR(128) Name of the column
NUMCOLUMN SMALLINT Number of columns in the column group
COLGROUP

COLNO

VARCHAR(254) A hex representation that identifies set of column associated with statistics
TYPE CHAR(1) The type of statistic to collect:

C – Cardinality

F – Frequency

H – Histogram

I – Index

T – Table

DBNAME VARCHAR(24) Name of database
TSNAME VARCHAR(24) Name of tablespace
REASON CHAR(8) Reason that statistic was recommend:

BASIC – A basic statistical value for a column table or index is missing

KEYCARD – Cardinalities of index key columns are missing

LOWCARD – Cardinality of the column is a low value, which indicates that data skew is likely

NULLABLE – Distribution statistics are not available for a nullable column

DEFAULT – A predicate references a value that is probably a default value

RANGEPRD – Histogram statistics are not available for a range predicate

PARALLEL – Parallelism could be improved by uniform partitioning of key ranges

CONFLICT – Another statistic conflicts with this statistic

COMPFIX – Multi-column cardinality statistics are needed for an index compound filter factor

STALE – Out of sync with other statistics based on comparison of time statistics collection of related objects

BLOCK_

RUNSTATS

CHAR(1) Whether row is used when optimization tools collect statistics based on recommendations. DB2 inserts a blank value for new rows
REMARKS VARCHAR(254) Free form text for extensibility
LASTDATE DATE Last date satistics recommendation was updated by DB2

SYSIBM.SYSSTMT

Contains one or more rows for each SQL statement of each DBRM

Column name Data type Description
NAME VARCHAR(24) Name of the DBRM
PLNAME VARCHAR(24) Name of the application plan
PLCREATOR VARCHAR(128) Authorization ID of owner of application plan
SEQNO SMALLINT Sequence number of row with respect to a statement of plan
STMTNO SMALLINT Statment number of statement in source program
SECTNO SMALLINT Section number of statement
IBMREQD CHAR(1) Y indicates row came from (MRM) tape
TEXT VARCHAR(3800) Text or portion of the text of the SQL statement
ISOLATION CHAR(1) Isolation level for the SQL statement:

R RR(repeatable read)

T RS(read stability)

S CS(cursor stability)

U UR(uncommitted read)

L KEEP UPDATE LOCKS for an RS isolation

X KEEP UPDATE LOCKS for an RR isolation

Blank WITH clause was not specified

STATUS CHAR(1) Status of binding the statement
ACCESSPATH CHAR(1) For static statements, access path for statement is based on user-specified optimization hints. A value of ‘H’ indicates that optimization hints were used
STMTNOI INTEGER If value of STMTNOI is not zero, contains statement number of statement in source program
SECTNOI INTEGER The section number of the statement
EXPLAINABLE CHAR(1) Contains one of the following values:

Y Indicates statement can be used with EXPLAIN function and may have rows describing its access path in the userid.PLAN_TABLE

N Statement does not have any rows describing its access path in userid.PLAN_TABLE

Blank Statement was bound prior to V7

QUERYNO INTEGER Query number of statement in source program
PLCREATOR CHAR(1) Indicates type of creator:

blank Authorization ID

L Role

SYSIBM.SYSSTOGROUP

Contains one row for each storage group

Column name Data type Description
NAME VARCHAR(128) Name of the storage group
CREATOR VARCHAR(128) Authorization ID of the owner of the storage group
VCATNAME VARCHAR(128) Number of the integrated catalog facility catalog
SPACE INTEGER Number of kilobytes of DASD storage allocated to storage group as determined by the last execution of the STOSPACE utility
IBMREQD CHAR(1) Y indicates row came from (MRM) tape
CREATEDBY VARCHAR(128) Primary authorization ID of user who created storage group
STATSTIME TIMESTAMP If STOSPACE utility was executed for storage group, date and time when STOSPACE was last executed
CREATEDTS TIMESTAMP Time when CREATE was executed for the storage group
ALTEREDTS TIMESTAMP Time when most recent ALTER STOGROUP statement was executed for storage group. If no ALTER STOGROUP statement has been applied, ALTEREDTD has value of CREATEDTS
SPACEF FLOAT Kilobytes of DASD storage for storage group
DATACLAS VARCHAR(24) Name of SMS data class. Blank if not used
MGMTCLAS VARCHAR(24) Name of SMS management class. Blank if not used
STORCLAS VARCHAR(24) Name of the SMS storage class. Blank if not used
CREATOR

TYPE

CHAR(1) Indicates the type of creator:

blank Authorization ID

L Role

RELCREATED CHAR(1) Release of DB2 used to create object. Blank if created prior to V9

SYSIBM.SYSSTRINGS

Contains information character conversion

Column name Data type Description
INCCSID INTEGER Source CCSID for character conversion represented by this row
OUTCCSID INTEGER Target CCSID for character conversion represented by this row
TRANSTYPE CHAR(2) Indicate the nature of the conversion. Values can be:

GG GRAPHIC to GRAPHIC

MM EBCDIC MIXED to EBCDIC MIXED

MS EBCDIC MIXED to SBCS

PM ASCII MIXED to EBCDIC MIXED

PS ASCII MIXED to SBCS

SM SBCS to EBCDIC MIXED

SS SBCS to SBCS

MP EBCDIC MIXED to ASCII MIXED

PP ASCII MIXED to ASCII MIXED

SP SBCS to ASCII MIXED

ERRORBYTE CHAR(1) Byte used in conversion table as an error byte
SUBBYTE CHAR(1) Byte used in the conversion table as a substitution character
TRANSPROC VARCHAR(24) Name of a module or blanks
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
TRANSTAB VARCHAR(256) Either a conversion table or an empty string

SYSIBM.SYSSYNONYMS

Contains one row for each synonym of a table or view

Column name Data type Description
NAME VARCHAR(128) Synonym for the table or view
CREATOR VARCHAR(128) Authorization ID of the owner of the synonym
TBNAME VARCHAR(128) Name of the table or view
TBCREATOR VARCHAR(128) Schema of the owner of the table or view
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
CREATEDBY VARCHAR(128) Primary authorization ID of user who created the synonym
CREATEDTS TIMESTAMP Time when CREATE was executed for synonym
CREATORTYPE CHAR(1) Type of creator:

blank Authorization ID

L Role

RELCREATED CHAR(1) Release of DB2 used to create the object. Blank if prior to V9

SYSIBM.SYSTABAUTH

Records the privileges that users hold on tables and views

Column name Data type Description
GRANTOR VARCHAR(128) Auth ID of user who granted privileges. Could be PUBLIC
GRANTEE VARCHAR(128) Auth ID of user who holds privileges or name of a plan or package that uses the privileges
GRANTEETYPE CHAR(1) Type of grantee:

blank An authorization ID

L Role

P Plan or a package. Package if COLLID is not blank

DBNAME VARCHAR(24) If privileges were received from a user with DBADM, DBCTRL or DBMAINT authority, DBNAME is the name of the database on which the GRANTOR has that authority. Otherwise, blank
SCREATOR VARCHAR(128) If row of SYSTABAUTH was created as a result of a CREATE VIEW statement, SCREATOR is a schema owner of a table of view referred to in the fullselect of CREATE TABLE
STNAME VARCHAR(128) If row of SYSIBM.SYSTABAUTH was created as a result of a CREATE TABLE or MQT, STNAME is name of a table or view referred to in the fullselect of CREATE TABLE
TCREATOR VARCHAR(128) Schema of owner of table or view
TTNAME VARCHAR(128) Name of table or view
AUTHHOWGOT CHAR(1) Authorization level of user from whom privilege were received. Not necessarily highest authorization level of grantor.

Blank Not Applicable

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

S SYSADM

T DATAACCESS

UPDATECOLS CHAR(1) Blank if value of UPDATEAUTH applies uniformly to all columns of table or view. (*) if value of UPDATEAUTH applies to some columns but not to others
ALTERAUTH CHAR(1) GRANTEE can alter the table:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

DELETEAUTH CHAR(1) GRANTEE can delete rows from the table or view:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

INDEXAUTH CHAR(1) GRANTEE can create indexes on the table:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

INSERTAUTH CHAR(1) GRANTEE can insert rows into the table or view:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SELECTAUTH CHAR(1) GRANTEE can select rows from the table or view:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

UPDATEAUTH CHAR(1) GRANTEE can update rows of the table or view:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

IBMREQD CHAR(1) Y indicates row came from (MRM) tape
COLLID VARCHAR(128) If GRANTEE is a package, its collection name. Else, blank
CONTOKEN CHAR(8) If GRANTEE is a package, consistency talking of DBRM from which the package was derived. Else, blank
REFERENCES

AUTH

CHAR(1) GRANTEE can create or drop referential constraints in which table is a parent:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

REFCOLS CHAR(1) Blank if value of REFERENCESAUTH applied uniformly to all columns of table. (*) if value of REFERENCEAUTH applies to some columns but not others
GRANTEDTS TIMESTAMP Time when the GRANT statement was executed
TRIGGERAUTH CHAR(1) GRANTEE can create triggers in which table is named as the triggering table:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

GRANTORTYPE CHAR(1) Type of grantor:

blank Authorization ID

L Role

UNLOADAUTH CHAR(1) Whether GRANTEE can use the UNLOAD utility to unload data

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SYS_START TIMESTAMP(12) Start time associated with the most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSTABCONST

Contains one row for each unique constraint (primary or unique key)

Column name Data type Description
CONSTNAME VARCHAR(128) Name of the constraint
TBCREATOR VARCHAR(128) Schema of the table on which the constraint is defined
TBNAME VARCHAR(128) Name of the table on which the constraint is defined
CREATOR CHAR(8) Authorization ID under which the constraint is defined
TYPE CHAR(1) Type of constraint:

F Foreign key

P Primary key

U Unique key

IXOWNER VARCHAR(128) Schema of index enforcing constraint
IXNAME VARCHAR(128) Name of index enforcing constraint
CREATEDTS TIMESTAMP Time when statement to create the constraint was executed
IBMREQD CHAR(1) Y indicates row came from (MRM) tape
COLCOUNT SMALLINT Number of columns in the constraint
RELCREATED CHAR(1) Release of DB2 used to create object. Blank if prior to V9

SYSIBM.SYSTABLEPART

One row for each non-partitioned tablespace and each partition of a partitioned table space

Column name Data type Description
PARTITION SMALLINT Partition number; 0 If tablespace is not partitioned
TSNAME VARCHAR(24) Name of the tablespace
DBNAME VARCHAR(24) Name of the database that contains the tablespace
IXNAME VARCHAR(128) Name of the partitioning index. Blank if not partitioned
IXCREATOR VARCHAR(128) Schema of partition index. Blank if not partitioned
PQTY INTEGER For user managed data sets, primary space allocation in units of 4 KB storage blocks or -1
SQTY SMALLINT For user managed data sets, value is the secondary space allocation in units of 4 KB storage blocks or -1
STORTYPE CHAR(1) Storage allocation:

E Exlicit (storage group not used)

I Implicit (storage group used)

STORNAME VARCHAR(128) Name of storage group used for space allocation. Blank if storage group not used or for the catalog table spaces
VCATNAME VARCHAR(24) Name of ICF catalog used for space allocation
CARD INTEGER Number of rows in tablespace or partition or, if tablespace is a LOB tablespace, number of LOBs in tablespace
FARINDREF INTEGER Number of rows relocated far from their original page
NEARINDREF INTEGER Number of rows relocated near their original page
PERACTIVE SMALLINT Percentage of space occupied by rows of data from active tables
PERCDROP SMALLINT Percentage of space occupied by rows of dropped tables
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
LIMITKEY VARCHAR(765) High value of partition in external format
FREEPAGE SMALLINT Number of pages loaded before a page is left as free space
PCTFREE SMALLINT Percentage of each page left as free space
CHECKFLAG CHAR(1) C Tablespace partition is in a check pending status and there are rows in table that can violate referential constraints, table check constraints, or both.

D Inline length of LOB column associated with LOB table space was decremented when inline length was altered.

I Inline length of LOB column that is associated with this LOB table space was incremented when inline length was altered.

Blank Tablespace is not a partition, or does not contain rows that may violate referential constraints, table check constraints, or both

SPACE INTEGER Number of kilobytes of DASD storage allocated to the table space partition, as determined by the last execution of the STOSPACE utility or RUNSTATS utility

0 STOSPACE or RUNSTATS utility has not been run.

-1 Table space was defined with the DEFINE NO clause, which defers the physical creation of the data sets until data is first inserted into one partition, and data has yet to be inserted.

Non-zero or non-negative value

An auxiliary table in the LOB table space

COMPRESS CHAR(1) Indicates the following:

· For a tablespace partition, whether the COMPRESS attribute for the partition is YES.

· For a non-partitioned tablespace, whether COMPRESS attribute is YES for tablespace.

Y Compression is defined

blank no compression

PAGESAVE SMALLINT Percentage of pages saved in tablespace or partition as a result of defining with COMPRESS YES or other compression routines
STATSTIME TIMESTAMP Date and time when RUNSTATS was executed
GBPCACHE CHAR(1) Group buffer pool cache option for tablespace or partition.

A Changed and unchanged pages are cached

N No data is cached in the group buffer pool

S Only changed system pages

blank Only changed pages are cached

CHECKRID5B CHAR(5) Blank if table or partition is not in an check pending status (CHECKFLAG blank), or if table not partitioned. Else, RID of first row of partition that can violate referential constraints, table check constraint or both; or value is X’0000000000’, indicating any row can violate referential constraints
TRACKMOD CHAR(1) Track page modifications in space map pages:

N No

Y Yes

EPOCH INTEGER A number that is implemented whenever an operation that changes the location of rows in a table occurs
SECQTYI INTEGER Secondary space allocation of units of 4KB storage
CARDF FLOAT Number of rows in tablespace or partition, or if tablespace is a LOB tablespace, number of LOBs in tablespace
IPREFIX CHAR(1) First character of instance qualifier for data set name for table space or partition. Only ‘I’ or ‘J’ are valid. Default=’I’
ALTEREDTS TIMESTAMP Time when most recent ALTER INDEX was executed for the index
SPACEF FLOAT(8) Kilobytes of DASD storage
DSNUM FLOAT(8) Number of datasets
EXTENTS INTEGER Number of data set extents
LOGICAL_PART SMALLINT Logical partition (logical ascending or descending order) for table spaces created with either table-controlled partitioning or index-controlled partitioning
LIMITKEY_

INTERNAL

VARCHAR(512) Highest value of limit key of partition in an internal format
OLDEST_

VERSION

SMALLINT Version number of oldest format of data in table past and any image copies at the part level
CREATDTS TIMESTAMP Time when the partition was created
AVGROWLEN INTEGER Average length of rows for tables in table space or part. If table space or part is compressed, value is compressed row length. If table space or part is not compressed, value is uncompressed row length
FORMAT CHAR(1) Format of the rows in the table space or partition:

R Reordered row format

blank Basic row format or a LOB tablespace

RELCREATED CHAR(1) Release of DB2 used to create object. Blank if prior to V9
REORG_LR_TS TIMESTAMP Time when REORG or LOAD REPLACE utility last occurred
HASHSPACE BIGINT 0 for partition by growth table spaces. For range partitioned universal table spaces, this is the amount of space, in KB, specified at the partition level to override space specification at the table level. If no override is provided it will be the same as value of HASHSPACE in SYSTABLESPACE
HASHDATAPAGES BIGINT 0 for partition by growth table spaces. For ranged partitioned universal table spaces, number of hash data page that correspond to value of HASHSPACE column for each partition. 0 for table spaces which have been changed to use hash access but have not been recognized
RBA_FORMAT CHAR(1) Format of the RBA/LRSN

B Basic, 6-byte RBA/LRSN format

E Extended, 10-byte RBA/LRSN format

U Undefined. DEFINE NO was specified when creating table space, and the table space is not an XML table space with XML versions

Blank For migrated records

PCTFREE_UPD SMALLINT Percentage of free space reserved for updates to variable length records, as defined when object as created or altered
PCTFREE_UPD_

CALC

SMALLINT Percentage of free space reserved for updates to variable length records, calculated by DB2 or utilities
TYPE CHAR(1) Type of partition

Blank Without LOB or MEMBER CLUSTERS

G Defined with MAXPARTITIONS

L Can be greater than 64 gigabytes

O Defined with LOB

P Implicit table space for XML

R Range partitioned UTS

PAGENUM CHAR(1) Format of tablespace or index page numbering

A Absolute

R Relative

BPOOL CHAR(8) Bufferpool used for the partition
PGSIZE SMALLINT Size of pages in the tablespace in KB
DSSIZE INTEGER Maximum size on partition
MEMBER_

CLUSTER

CHAR(1) Whether or not MEMBER CLUSTER is specified

Y MEMBER CLUSTER is specified

Blank MEMBER CLUSTER is not specified

COMPRESSRATIO SMALLINT Average percentage of bytes saved by compression

-1 Value not been collected

0 No compression exists or average compressed record length is same or longer than uncompressed record

SYSIBM.SYSTABLEPART_HIST

Contains rows from SYSTABLEPART. Can be inserted, updated, and deleted

Column name Data type Description
PARTITION SMALLINT Partition number; 0 if tablespace not partitioned
TSNAME VARCHAR(24) Name of the table space
DBNAME VARCHAR(24) Name of the data set that contains the table space
PGTY INTEGER For user-managed data sets, value is primary space allocation in units of 4 KB storage blocks or -1
SECQTYI SMALLINT Secondary space allocation in units of 4KB blocks. For user-managed data sets, the value of the secondary space allocation in units of 4KB storage blocks or -1
FARINDREF INTEGER Number of rows relocated far from their original page
NEARINDREF INTEGER Number of rows relocated near their original page
PERACTIVE SMALLINT Percentage of space occupied by rows of data from active tables
PERCDROP SMALLINT Percentage of space occupied by rows of dropped tables
SPACEF FLOAT(8) Number of kilobytes of DASD storage allocated to tablespace partition
PAGESAVE SMALLINT Percentage of pages saved in the tablespace or partition as a result of defining the tablespace with COMPRESS YES or other compression routines
STATSTIME TIMESTAMP If RUNSTATS updated statistics, date and time when last invocation of RUNSTATS updated statistics
CARDF FLOAT(8) Number of rows in tablespace of partition, or if tablespace is a LOB tablespace, number of LOBs in tablespace
EXTENTS INTEGER Number of data set extents. Value is only for last DSNUM for object
DSNUM INTEGER Data set number within the table space
IBMREQD CHAR(1) Y indicates row came from (MRM) tape
AVGROWLEN INTEGER Average length of rows for tables in table space or part. If table space or part is compressed, value is compressed row length. If table space or part is not compressed, value is uncompressed row length

SYSIBM.SYSTABLES

Contains one row for each table View alias

Column name Data type Description
NAME VARCHAR(128) Name of the table, view or alias
CREATOR VARCHAR(128) Schema of the table, view, or alias
TYPE CHAR(1) Type of object:

A Alias

C Clone table

H History table

G Created global temporary table

M Materialized query table

P Implicit table created for XML columns

R Archive table

T Table

V View

X Auxiliary table

DBNAME VARCHAR(24) For a table, or a view of tables, name of the database that contains tablespace named in TSNAME
TSNAME VARCHAR(24) For a table, or a view of one table, name of table space that contains the table
DBID SMALLINT Internal identifier of database
OBID SMALLINT Internal identifier of table
COLCOUNT SMALLINT Number of columns in table or view
EDPROC VARCHAR(24) Name of the edit procedure; blank if the row describes a view or alias or a table without an edit procedure
VALPROC VARCHAR(24) Name of the validation procedure; blank if the row describes a view or alias or a table without an edit procedure
CLUSTERTYPE CHAR(1) Whether RESTRICT ON DROP applies:

blank No

Y Yes. Neither the table nor any tablespace or database that contains the table can be dropped

NPAGES INTEGER Total number of pages on which rows of table appear
PCTPAGES SMALLINT Percentage of active tablespace pages that contain rows of table
IBMREQD CHAR(1) Y indicates row came from MRM tape
REMARKS VARCHAR(254) A character string provided by user with COMMENT ON
PARENTS SMALLINT Number of relationships in which table is dependent. 0 if row describes a view, alias, created temporary table or MQT
CHILDREN SMALLINT Number of relationships in which table is a parent. 0 if row describes a view, an alias, created temporary table or MQT
KEYCOLUMNS SMALLINT Number of columns in a table’s primary key. 0 if row describes a view, an alias, or a created temporary table
RECLENGTH SMALLINT For user tables, maximum length of any record in table
STATUS CHAR(1) Status of table definition:

I Definition of table is incomplete. TABLESTATUS column indicates reason for table definition being incomplete

R An error occurred when an attempt was made to regenerate the internal representation of view

X Table has a parent index and definition complete

blank Table has no parent index, or is a catalog table, or row describes a view or alias. Definition of table, view, or alias is complete

KEYOBID SMALLINT Internal DB2 identifier of index that enforces uniqueness of table’s primary key; 0 if not applicable
LABEL VARCHAR(90) Label as given by a LABEL ON; otherwise an empty string
CHECKFLAG CHAR(1) C Tablespace that contains table is in a check pending status and there are rows in the table that can violate referential constraints, table check constraints, or both. Table is an MQT that may contain inconsistent data

blank Table contains no rows that violate referential constraints, table check constraints, or both; or the row describes a view, alias, or created temporary table

CHECKRID CHAR(4) ‘FFFFFF00’ indicates edit procedure on this table is defined without row attribute sensitivity. Any other value indicates edit procedure is defined with row attribute sensitivity.
AUDITING CHAR(1) Audit option:

A AUDIT ALL

C AUDIT CHANGE

Blank AUDIT NONE. or is view, alias, or created temp

CREATEDBY VARCHAR(128) Primary auth ID of user who created the table, view, or alias
LOCATION VARCHAR(128) Location name of an object of an alias. Blank for table, a view, or for an alias not defined with a three-part name
TBCREATOR VARCHAR(128) · For an alias, schema of the referred to table or view

· For a base table involved in a clone relationship, name of creator of clone table

· For a clone table involved in a clone relationship, name of creator of base table

· Otherwise, TBCREATOR is blank

TBNAME VARCHAR(128) · For an alias, schema of the referred to table or view

· For a base table involved in a clone relationship, name of creator of clone table

· For a clone table involved in a clone relationship, name of creator of base table

· Otherwise, blank

CREATEDTS TIMESTAMP Time when CREATE was executed for table, view or alias
ALTEREDTS TIMESTAMP For a table, time when latest ALTER TABLE was applied. If no ALTER TABLE statement has been applied, or if row is for an alias, ALTEREDTS has a value of CREATEDTS. For a view, time when last ALTER VIEW REGENERATE applied
DATACAPTURE CHAR(1) Records the value of the DATACAPTURE option for table:

blank No

Y Yes

For a created temporary table, is blank

RBA1 CHAR(10) Log RBA(LRSN) when table was created
RBA2 CHAR(10) Log RBA(LRSN) when table was last altered
PCTROWCOMP SMALLINT Percentage of throws compressed within total number of active rows in table. Includes any row in a table space that is defined with COMPRESS YES
STATSTIME TIMESTAMP If RUNSTATS updated statistics, date and time when last invocation of RUNSTATS updated the statistics
CHECKS SMALLINT Number of check constraints defined on the table
CARDF FLOAT Total number of rows in the table or total number of LOBs in an auxiliary table
CHECKRID5B CHAR(5) Blank if table or partition is not in a check pending status (CHECKFLAG is blank), if not partitioned, or if table is a created a temporary table. Otherwise, RID of first row of partition that can violate referential constraints, table check constraints, or both; or value is X’0000000000’, indicating that any row can violate referential constraints
ENCODING_

SCHEME

CHAR(1) Default encoding scheme for tables, views and aliases:

E EBCDIC

A ASCII

M Multiple CCSID set or multiple encoding schemes

U UNICODE

blank For remote aliases

‘E’ for tables in non-work file databases and blank for tables in work-file databases created prior to V5 or DSNDB04

TABLESTATUS VARCHAR(30) Reason for an incomplete table definition:

F Table lacks required BUSINESS_TIME WITHOUT OVERLAPS index on foreign key

L An auxiliary table or auxiliary index has not been defined for LOB column

P Table lacks a parent index

R Table lacks a required index on a row ID

U Table lacks a required index on a unique key

V An error occurred during a regeneration of the view

blank Definition is complete

NPAGESF FLOAT(8) Number of pages used by the table
SPACEF FLOAT(8) Kilobytes of DASD storage
AVGROWLEN INTEGER Average length of rows for tables. If compressed, value is compressed row length. If not compressed, value is uncompressed row
RELCREATED CHAR(1) Release of db2 used to create the object
NUM_DEPT_MQTS SMALLINT Number of dependent materialized query tables. 0 if is an alias or a created temporary table, or if no materialized query tables are defined on the table
VERSION SMALLINT Version of data raw format for this table

· 0 indicates a version-creating alter operation has never occurred against this table

· -1 indicates view has been regenerated because a column of base table has been altered

· 800 indicates a successful CREATE VIEW or ALTER VIEW occurred against table in V8 or later

· 900 indicates successful ALTER TABLE with DROP COLUMN clause occurred against view

PARTKEYCOLNUM SMALLINT Number of columns in partitioning key. 0 for tables that do not have partitioning or use index-controlled partitioning. Non-zero for tables that use table-controlled partitioning.
SPLIT_ROWS CHAR(16) Blank except for VOLATILE tables, else Y to indicate to DB2 to use index access on table whenever possible
SECURITY_

LABEL

CHAR(1) If type column is T or M. Table has multi-level security:

blank No multi-level security

R Table has multi-level security with row granularity

OWNER VARCHAR(128) Auth ID of owner of table, view, or alias, blank for tables, views or aliases created prior to V9
APPEND CHAR(1) APPEND option is specified for the table

Y Yes

N No

OWNERTYPE CHAR(1) Type of owner:

blank Authorization ID

L Role

CONTROL CHAR(1) Access enforced using row or column access control:

blank No access control enforcement

B Row and column access control

C Column access control

R Row access control

VERSIONING_

SCHEMA

VARCHAR(128) Schema name of history table if tables is a system-maintained temporal table with versioning or schema name of system-maintained temporal table if table is a history table
VERSIONING_

TABLE

VARCHAR(128) Either table name of history table if table is a system-maintained temporal table with versioning or table name of system-maintained temporal table if table is a history table
HASHKEY

COLUMNS

SMALLINT Number of columns in hash key of table. 0 if row describes a view, an alias, or a created temporary table
ARCHIVING_

SCHEMA

VARCHAR(128) Contains a schema name as follows:

· If table is an archived-enabled table, contains schema name of the archive table

· If table is an archive table, this column contains the schema name of the archive-enabled table

· If table is not an archive-enabled or an archive table, value is blank

ARCHIVING_

TABLE

VARCHAR(128) Contains a table name as follows:

· If table is an archive-enabled table, contains table name of the archive table

· If table is an archive table, contains the table name of the archive-enabled table

· If table is not an archive-enabled enabled table or an archive table, value is blank

STATS_

FEEDBACK

CHAR(1) When a query qualifies for statistics collection based on DSNZPARM STATFDBK_SCOPE, controls whether statistics recommendations for this table are placed in SYSSTATFEEDBACK. Can update ‘Y’ or ‘N’ to enable or disable collection for the table
REGENERATETS TIMESTAMP(12) Time when object was regenerated

SYSIBM.SYSTABLESPACE

Contains one row for each tablespace

Column name Data type Description
NAME VARCHAR(24) Name of tablespace
CREATOR VARCHAR(128) Authorisation ID of the owner of the tablespace
DBNAME VARCHAR(24) Name of the database that contains the tablespace
DBID SMALLINT Internal identifier of database that contains the tablespace
OBID SMALLINT Internal identifier of the tablespace file descriptor
PSID SMALLINT Internal identifier of the tablespace page set descriptor
BPOOL CHAR(8) Name of the buffer pool used for the table space
PARTITIONS SMALLINT Number of partitions of tablespace; 0 if not partitioned
LOCKRULE CHAR(1) Lock size of the tablespace:

A Any

L Large object (LOB)

P Page

R Row

S Tablespace

T Table

X Implicitly created XML table space

PGSIZE SMALLINT Size of pages in the tablespace in kilobytes
ERASERULE CHAR(1) Data sets are to be erased when dropped. Meaningless if tablespace is partitioned:

N No erase

Y Erase

STATUS CHAR(1) Availability status of the tablespace:

A Available

C Definition is incomplete because a partitioning index has not been created

P Tablespace is in a check pending status

S Tablespace is in a check pending status with scope less than the entire tablespace

T Definition is incomplete because table not created

IMPLICIT CHAR(1) Tablespace was created implicitly:

N No

Y Yes

NTABLES SMALLINT Number of tables defined in the tablespace
NACTIVE INTEGER Number of active pages in tablespace. A page is termed active if it is formatted for rows, even if it currently contains none. 0 if statistics not gathered. An updateable column
CLOSERULE CHAR(1) Datasets are candidates for closure when limit on number of open data sets is reached

N No

Y Yes

SPACE INTEGER Number of kilobytes of DASD storage allocated to tablespace, as determined by last execution of STOSPACE utility
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
SEGSIZE SMALLINT Number of pages in each segment. 0 if not segmented
CREATEDBY VARCHAR(128) Primary authorization ID of user who created tablespace
STATSTIME TIMESTAMP If RUNSTATS updated statistics, date and time when last invocation of RUNSTATS updated statistics
LOCKMAX INTEGER Maximum number of locks per user to acquire for table or tablespace before escalating to next locking level

0 Lock escalation does not occur

n n, where n>0, is maximum number of locks (row, page or LOB locks for the table or tablespace) an application process can acquire before lock escalation

-1 Represents LOCKMAX SYSTEM. Value of field LOCKS PER TABLE(SPACE) on installation panel DSNTIPJ determines lock escalation. If 0, lock escalation does not occur. If value is n, where n>0, lock escalation occurs as it does for LOCKMAX n

TYPE CHAR(1) Type of tablespace:

blank Tablespace was created without: DSSIZE, LARGE, LOB and MEMBER CLUSTER

I Defined with MEMBER CLUSTER and is not greater than 64GB

G Defined with MAXPARTITIONS option (a partitioned by-growth table space) with underlying structure of a universal table space

K Defined with MEMBER CLUSTER and can be greater than 64GB

L Table space can be greater than 64 gigabytes

O Table space was defined with the LOB column

P Implicit table space created for XML columns

R Range-partitioned universal table space

CREATEDTS TIMESTAMP Time CREATE was executed for tablespace. If tablespace created prior to V5, value is ‘0001-01-01.00.00.00.000000’
ALTEREDTS TIMESTAMP Time when most recent ALTER TABLESPACE was executed. If no ALTER TABLESPACE has been applied, ALTEREDTS has a value of CREATEDTS. If index was created prior to V5, value is ‘0001-01-01.00.00.00.000000’
ENCODING_

SCHEME

CHAR(1) Default encoding scheme for tablespace:

E EBCDIC

A ASCII

U UNICODE

Blank For tablespaces in a work file database or a TEMP database (a database created AS TEMP, which is for declared temporary tables)

‘E’ for tables in non-workfile databases and blank for tables in work-file databases created prior to V5 or DSNDB04

SBCS_CCSID INTEGER Default SBCS CCSID for tablespace
DBCS_CCSID INTEGER Default DBCS CCSDI for tablespace
MIXED_CCSID INTEGER Default mixed CCSID for the tablespace
MAXROWS SMALLINT Maximum number of rows on a data page. Default is 255
LOCKPART CHAR(1) Y LOCKPART YES is specified for the tablespace

N LOCKPART NO is specified, or LOCKPART is not specified or not a partitioned tablespace

LOG CHAR(1) Changes to a tablespace are to be logged:

N Tablespace has NOT LOGGED attribute

Y Tablespace has a LOGGED attribute

X LOB or XML table space has NOT LOGGED attribute

NACTIVEF FLOAT Number of active pages in table space
DSSIZE INTEGER Maximum size of dataset in kilobytes. Might be 0 if table space was created prior to V10, but will contain actual value after table space is converted to a partitioned by growth
OLDEST_

VERSION

SMALLINT Version number of oldest format of data in table space and any image copies
CURRENT_

VERSION

SMALLINT Version number describing newest format of data in table space. 0 indicates table space has never had versioning
AVGROWLEN INTEGER Average length of rows for tables in table space or part. If table space or part is compressed, value is compressed row length. If table space or part is not compressed, value is the uncompressed row length
SPACEF FLOAT Kilobytes of DASD storage for the storage group
CREATORTYPE CHAR(1) Type of creator:

Blank Authorization ID

L Role

RELCREATED CHAR(1) Release of used to create object. Blank if prior to V9
INSTANCE SMALLINT Column value of data set instance number of current base object (table and index)
CLONE CHAR(1) Tablespace contains objects involved in a clone relationship

Y Yes

N No

MAXPARTITIONS SMALLINT Maximum number of partitions table space can grow to. 0 if table space is not partitioned or is range partitioned but not a universal table space
MEMBER_

CLUSTER

CHAR(1) If member cluster is specified to the tablespace

Y Yes

blank No

ORGANIZATION

TYPE

CHAR(1) Table space organization

blank Not known. Blank is the default

H Hash organization

HASHSPACE BIGINT Amount of space, in KB, allocated to table space or partition as hash space. For partition by growth table spaces, space applies to the whole table space. For range partitioned table spaces, space is applicable for each partition
HASHDATAPAGES BIGINT Total number of hash data pages to preallocate for hash space. For partition by growth table spaces, includes all pages in fixed part of the table space. For range partitioned table spaces, number of pages in fixed hash space in each partition unless it is overridden by providing hash space at the partition level. Calculated by DB2 from value specified with HASH SPACE option or when REORG utility is run with automatic estimation of space. Calculated value is used in hash algorithm. 0 for non-hash table spaces. 0 for table spaces which have been changed to use hash access but have not been reorganized
PAGENUM CHAR(1) Format of page numbers for index.

A Absolute

R Relative

PQTY INTEGER For user managed data sets. Value is primary space allocation
STORTYPE CHAR(1) Type of storage allocation

E Explicit (storage group not used)

I Implicit (storage group used)

STORNAME VARCHAR(128) Name of storage group used for space allocation
VCATNAME VARCHAR(24) Name of ICF catalog used for space allocation
FREEPAGE SMALLINT Number of pages loaded before a page is left free
PCTFREE SMALLINT Percentage of each page left as free space
COMPRESS CHAR(1) Whether or not the table space partition was defined with COMPRESS YES

Y Compression is defined

blank No compression

GBPCACHE CHAR(1) Group bufferpool cache option

Blank Only changed pages

A Changed and unchanged pages

N No data is cached

S Only system pages are cached

TRACKMOD CHAR(1) Whether to track the page modification in the space map

N No

Blank Yes

SECQTY1 INTEGER Secondary space allocation for user managed data sets
PCTFREE_UPD SMALLINT Percentage of free space reserved for updates to variable length records defined on object
PCTFREE_UPD_

CALC

SMALLINT Percentage of free space reserved for updates to variable length records calculated by DB2 or utilities
COMPRESSRATIO SMALLINT Average percentage of bytes saved by compression

-1 Value not been collected

0 No compression exists or average compressed record length is same or longer that uncompressed record

INSERTALG SMALLINT Insert algorithm level for tables in default space

0 Determined by DEFAULT_INSERT_ALGORITHM

1 Basic

2 Fast (if MEMBER_CLUSTER used)

SYSIBM.SYSTABLESPACESTATS

Contains real time statistics for table spaces. Can be inserted, updated, and deleted

Column name Data type Description
UPDATESTATS

TIME

TIMESTAMP Timestamp when the row was inserted or last updated
NACTIVE INTEGER Number of active pages in the table space or partition
NPAGES INTEGER Number of distinct pages with active rows in partition of the table space. Updatable column
EXTENTS SMALLINT Number of extents in table space or partition. For multi-piece table spaces, value is a number of extents for last data set
LOADRLASTTIME TIMESTAMP Timestamp of last LOAD REPLACE on table space or partition
REORGLASTTIME TIMESTAMP Timestamp REORG utility was last run on table space or partition, or if REORG utility has not been run, time when table space or partition was created
REORGINSERTS INTEGER Number of records or LOBs inserted into table space or partition or loaded into the table space or partition using the LOAD utility specified without REPLACE option since the last time the REORG or LOAD REPLACE utilities were run, or since the object was created
REORGDELETES INTEGER Number of records or LOBs that have been deleted from table space or partition since last time the REORG or LOAD REPLACE utilities were run, or since object was created
REORGUPDATES INTEGER Number of rows updated in table space or partition since the last REORG or LOAD REPLACE, or since the object was created
REORGUNCLUSTINS INTEGER Number of records inserted that are not well-clustered with respect to clustering index since last REORG or LOAD REPLACE, or since object was created. A record is well-clustered if record is inserted into a page that is within 16 pages of ideal candidate page. Clustered index determines ideal candidate page
REORGDISORGLOB INTEGER Number of LOBs inserted that are not perfectly chunked since last REORG or LOAD REPLACE, or since object was created. A LOB is perfectly chunked if allocated pages are in minimum number of chunks
REORGMASSDELETE INTEGER Number of mass deletes from a segmented or LOB table space, or number of dropped tables from a segmented table space since last REORG or LOAD REPLACE, or since object was created
REORGNEARINDREF INTEGER Number of overflow records created and relocated near pointer record since last time REORG and LOAD REPLACE utilities were run, or since the object was created
REORGFARINDEF INTEGER Number of overflow records created and relocated far from pointer record since last time REORG and LOAD REPLACE utilities were run, or since object was created
STATSLASTTIME TIMESTAMP Timestamp of last RUNSTATS on tablespace or partition
STATSINSERTS INTEGER Number of records or LOBs inserted into table space or partition or loaded into table space or partition using LOAD utility without REPLACE option since last time RUNSTATS was run, or since object was created
STATSDELETES INTEGER Number of records or LOBs deleted from table space or partition since last time RUNSTATS was run, or since object was created
STATSUPDATES INTEGER Number of rows updated in the table space of partition since the last time that the RUNSTATS utility was run, or since the object was created
STATSMASSDELETE INTEGER Number of mass deletes from a segmented or LOB table space, or number of tables dropped from a segmented table space, since last time RUNSTATS utility was run, or since object was created
COPYLASTTIME TIMESTAMP Timestamp of last full or incremental image copy of table space or partition, or since object was created. A null indicates COPY utility has never been run or unknown
COPYUPDATED

PAGES

INTEGER Number of distinct types updated since last time COPY utility was run, or since object was created
COPYCHANGES INTEGER Number of insert, update, and delete operations, or number of records loaded, since last time COPY utility was run, or since object was created
COPYUPDATELRSN CHAR(10) LRSN or RBA of first update after last COPY
COPYUPDATETIME TIMESTAMP Timestamp of first update after last COPY
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DBID SMALLINT Internal identifier database. Used to map DBID to its statistics
PSID SMALLINT Internal identifier of the table space page set descriptor. Used to map a PSID to its statistics
PARTITION SMALLINT Data set number within table space. Used to map a data set number in a table space to its statistics. For partitioned table spaces, value corresponds to the partition number for a single partition. For non-partitioned table spaces, value is 0
INSTANCE SMALLINT Indicates if object is associated with data set instance 1 or 2. An updatable column
SPACE INTEGER Amount of space, in KB, allocated to table space or partition. For multi-piece linear page sets, value is amount of space in all data sets
TOTALROWS BIGINT Number of rows or LOBs in the table space or partition
DATASIZE BIGINT Total number of bytes row data occupy in data rows or LOB rows. An updatable column
UNCOMPRESSED

DATASIZE

BIGINT Not used. The value is always set to 0
DBNAME CHAR(8) Name of database. Used to map database to its statistics
NAME CHAR(8) Name of tablespace. Used to map tablespace to its statistics
REORGSCANACCESS BIGINT Number of times data is accessed for SELECT, FETCH, searched UPDATE, or searched DELETE since last CREATE, LOAD REPLACE or REORG, or since object was created
REORGHASHACCESS BIGINT Number of times data is accessed using hash access for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraint since the last CREATE, LOAD REPLACE or REORG, or since the object was created
HASHLASTUSED TIMESTAMP Date when hash access was last used for SELECT, FETCH, searched UPDATE/DELETE, or used to enforce RI
REORG

CLUSTERSENS

BIGINT Number of times data has been read by SQL statements that are sensitive to clustering sequence of the data since the last REORG or LOAD REPLACE, or since object was created
DRIVETYPE CHAR(3) Drive type on which table space or partition data set is defined

HDD Hard Disk Drive

SDD Solid State Drive

LPFACILITY CHAR(1) Whether disk control unit has high performance list prefetch

N No

Y Yes

A NULL value indicates unknown an updatable column

UPDATESIZE BIGINT Net number of bytes added or removed by UPDATE since object was created, or since last REORG or LOAD REPLACE
LATDATACHANGE TIMESTAMP Last time row was updated because data was modified in the table space or partition. Reflects the time at which real-time statistics table was updated, and not time at which data in table space or partition was modified
GETPAGES BIGINT Number of getpages since last REORG or creation
SYS_START TIMESTAMP(12 Start time associated with the most recent transaction
SYS_END TIMESTAMP(12 Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12 Timestamp value per transaction or null

SYSIBM.SYSTABLES_HIST

Contains rows from SYSTABLES. Can be inserted, updated, and deleted

Column name Data type Description
NAME VARCHAR(128) Name of the table, view, or alias
CREATOR VARCHAR(128) Schema of the owner of the table, view, or alias
DBNAME VARCHAR(24) For a table, or a view of tables, name of database that contains the tablespace named in TSNAME
TSNAME VARCHAR(24) For a table, or a view of one table, name of tablespace that contains the table. For a view of more than one table, the name of a tablespace that contains one of the tables
COLCOUNT SMALLINT Number of columns in table view. 0 if it is an alias
PCTPAGES SMALLINT Percentage of active tablespace pages that contain rows of table. A page is termed active if it is formatted for rows, regardless of whether it contains any. If tablespace is segmented, percentage is based on the number of active pages in the set of segments assigned to the table
PCTROWCOMP SMALLINT Percentage of rows compressed within the total number of active rows in the table. Includes any rows in a tablespace defined with COMPRESS YES
STATSTIME TIMESTAMP If RUNSTATS updates statistics, date and time of last invocation
CARDF FLAOT(8) Total number of rows in table or total number of LOBs in an auxiliary table
NPAGESF FLOAT(8) Total number of pages on which rows of the partition appear
AVGROWLEN INTEGER Average row length of the table specified in the tablespace
SPACEF FLOAT(8) Kilobytes of DASD storage
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSTABLES_PROFILES

Contains one row for each profile that is associated with a table in SYSIBM.SYSTABLES

Column name Data type Description
SCHEMA VARCHAR(128) The schema (qualifier) for the table
TBNAME VARCHAR(128) The table name
PROFILE_TYPE VARCHAR(32) The type of profile. Allowed values are ‘RUNSTATS’
VARCHAR(32) Internal use only
PROFILE_TEXT CLOB(1M) The text of the profile
ROWID ROWID The ROWID value for the LOB column of this table
PROFILE_UPDATE TIMESTAMP Last time the profile was updated, or timestamp when profile was inserted into the table
PROFILE_USED TIMESTAMP The last time the profile was used

SYSIBM.SYSTABLES_PROFILE_TEXT

Auxiliary table for the PROFILE_TEXT column of the SYSIBM.SYSTABLE_PROFILE table

Column name Data type Description
PROFILE_TEXT CLOB(2M) The complete text for the profile that the row represents

SYSIBM.SYSTABSTATS

Contains one row for each partition of a tablespace. Can be inserted, updated, and deleted

Column name Data type Description
CARD INTEGER Total number of rows in the partition
NPAGES INTEGER Total number of pages on which rows of the partition appear
PCTPAGES SMALLINT Percentage of total active pages in partition containing rows
NACTIVE INTEGER Number of active pages in the partition
PCTROWCOMP SMALLINT Percentage of rows compressed within the total number of active rows in the partition. Includes any row in a table space that is defined as COMPRESS YES
STATSTIME TIMESTAMP If RUNSTATS updated the statistics, the date and time when the last invocation of RUNSTATS updated the statistics
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
DBNAME VARCHAR(24) Database that contains the tablespace named in TSNAME
TSNAME VARCHAR(24) TableSpace that contains the table
PARTITION SMALLINT Partition number of the tablespace that contains the table
OWNER VARCHAR(128) Schema of the table
NAME VARCHAR(128) Name of the table
CARDF FLOAT Total number of rows in the partition

SYSIBM.SYSTABSTATS_HIST

Contains rows from SYSTABLES. Can be inserted, updated, and deleted

Column name Data type Description
NPAGES INTEGER Total number of pages on which rows of the partitions appear
STATSTIME TIMESTAMP If RUNSTATS updated statistics, date and time of last invocation
DBNAME VARCHAR(24) Database that contains the table space in TSNAME
TSNAME VARCHAR(24) Table space that contains the table
PARTITION SMALLINT Partition number of the tablespace that contains the table
OWNER VARCHAR(128) Schema of the table
NAME VARCHAR(128) Name of the table
CARDF FLOAT(8) Total number of rows in the partition
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSTRIGGERS

Contains one row for each trigger

Column name Data type Description
NAME VARCHAR(128) Name of the trigger and trigger package
SCHEMA VARCHAR(128) Schema of the trigger. This implicit or explicit qualifier for the trigger name is also used with collection ID of trigger package
DBID SMALLINT Internal identifier of the database for the trigger
OBID SMALLINT Internal identifier of the trigger
OWNER VARCHAR(128) Owner of the trigger
CREATEDBY VARCHAR(128) Primary authorization ID of the creator of the trigger
TBNAME VARCHAR(128) Name of the table or view
TBOWNER CHAR(1) Qualifier of the name of the table to which this trigger replies
TRIGTIME CHAR(1) Time when triggered actions are applied to the base table, relative to the event that activated the trigger:

B Trigger is applied before the event

A Trigger is applied after the event

I Trigger is applied instead of the event

TRIGEVENT CHAR(1) Operation that activates the trigger:

I Insert

D Delete

U Update

GRANULARITY CHAR(1) Trigger is executed once per

S Statement

R Row

CREATEDTS TIMESTAMP Time when the CREATE statement was executed for this trigger
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
VARCHAR(6000 Not used
REMARKS VARCHAR(762) A character string provided by user with COMMENT ON
TRIGNAME VARCHAR(8) Unused
OWNERTYPE CHAR(1) Indicates the type of creator:

blank Authorization ID

L Role

ENVID INTEGER Internal environment identifier
RECREATED CHAR(1) Release of DB2 used to create object. Blank if prior to V9
CHAR(1) Reserved for IBM use
CHAR(1) Reserved for IBM use
INTEGER Reserved for IBM use
VARCHAR(96) Reserved for IBM use
SECURE CHAR(1) Trigger is secured:

N No

Y Yes

ALTEREDTS TIMESTAMP Time when trigger was last changed
ROWID ROWID ROWID column, created for the lob columns in this table
SQLPL CHAR(1) Indicates whether trigger supports SQL PL

Y Advanced trigger that supports SQL PL

blank Basic trigger does not support SQL PL

ALTEREDTS TIMESTAMP Time when trigger was last changed
DEBUG_MODE CHAR(1) Whether trigger is enabled for debugging

1 Enabled for debugging

0 Not enabled for debugging

N Can never be enabled for debugging

Blank Basic trigger that cannot be debugged

ASUTIME INTEGER Number of service units allowed for a single invocation of this trigger
WLM_ENVIRONMENT VARCHAR(96) Name of WLM environment used in a trigger is debunked
STATEMENT CLOB(2M) Text of entire CREATE TRIGGER used to create the object
REGENERATES TIMESTAMP Time when this version of trigger was last regenerated
VERSION VARCHAR(122) Version identifier of the trigger. Zero if basic trigger
ORIGINAL_

CONTOK

CHAR(8) Consistency token for trigger
ACTIVE CHAR(1) Active version of trigger

Y Active version

N Not the active version

Blank value of VERSION is zero length

WRAPPED CHAR(1) Y Trigger text is obfuscated

Blank Trigger text is not obfuscated

SYSIBM.SYSTRIGGERS_STMT

An auxiliary table for the STATEMENT column of the SYSIBM.SYSTRIGGERS

Column name Data Type Description
STATEMENT CLOB(2M) Text of CREATE TRIGGER statement used to create the object

SYSIBM.SYSUSERAUTH

Records the system privileges that are held by users

Column name Data Type Description
GRANTOR VARCHAR(128) Authorization ID of user who granted the privileges
GRANTEE VARCHAR(128) Auth ID of user that holds privilege. Could also be PUBLIC
AUTHHOWGOT CHAR(1) Authorisation level of user from whom privileges were received. Not necessarily highest level of grantor

blank Not applicable

C DBCTL

D DBADM

E SECADM

G ACCESSCTRL

L SYSCTRL

M DBMAINT

O SYSOPR

S SYSADM

BINDADDAUTH CHAR(1) GRANTEE can use WITH with ADD option:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

BSDSAUTH CHAR(1) GRANTEE can issue RECOVER BSDS command:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

CREATEDBAAUTH CHAR(1) GRANTEE can create databases and automatically receive DBADM authority over the new databases

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

CREATEDBCAUTH CHAR(1) GRANTEE can execute the CREATE DATABASE statement to create new databases and automatically receive DBCTRL authority over the new databases

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

CREATESGAUTH CHAR(1) GRANTEE can execute CREATE STOGROUP statement to create new storage groups

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

DISPLAYAUTH CHAR(1) GRANTEE can use DISPLAY commands

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

RECOVERAUTH CHAR(1) GRANTEE can use RECOVER INDOUBT command

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

STOPALLAUTH CHAR(1) GRANTEE can use STOP command

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

STOSPACEAUTH CHAR(1) GRANTEE can use STOSPACE utility

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SYSADMAUTH CHAR(1) GRANTEE has system administration authority

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SYSOPRAUTH CHAR(1) GRANTEE has system operator authority:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

TRACEAUTH CHAR(1) Grantee can issue START TRACE and STOP TRACE commands:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
MON1AUTH CHAR(1) GRANTEE can obtain IFC serviceability data

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

MON2AUTH CHAR(1) GRANTEE can obtain IFC data

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

CREATEALIASAUTH CHAR(1) GRANTEE can execute CREATE ALIAS statement

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SYSCTRLAUTH CHAR(1) Grantee has SYSCTRL authority

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

BINDAGENTAUTH CHAR(1) GRANTEE has BINDAGENT privilege

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

ARCHIVEAUTH CHAR(1) GRANTEE can use ARCHIVE LOG command

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

GRANTEDTS TIMESTAMP Time when GRANT statement was executed. The value is ‘1985-04-01.00.00.00.000000’ for the one installation row
CREATETMTABAUTH CHAR(1) GRANTEE has CREATETMTABAUTH privilege:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

GRANTEETYPE CHAR(1) Type of grantee:

Blank Authorization ID

L Role

GRANTORTYPE CHAR(1) Indicate the type of grantor:

Blank Authorization ID

L Role

DEBUGSESSION

AUTH

CHAR(1) GRANTEE has DEBUGSESSION privilege:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

EXPLAINAUTH CHAR(1) GRANTEE can explain and prepare statements:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SQLADMAUTH CHAR(1) GRANTEE has SQLADM authority:

blank Privilege is not held

G Privilege is held with the GRANT option

Y Privilege is held without the GRANT option

SBBADMAUTH CHAR(1) GRANTEE has system DBADM authority:

blank Privilege is not held

Y Privilege is held without the GRANT option

DATAACCESSAUTH CHAR(1) Grantee has data ACCESSCTRL authority

blank Privilege is not held

Y Privilege is held without the GRANT option

ACCESSCTRLAUTH CHAR(1) Grantee has ACCESSCTRL authority:

blank Privilege is not held

Y Privilege is held without the GRANT option

CREATESECURE

AUTH

CHAR(1) GRANTEE can create secured objects (triggers and user-defined functions)

blank Privilege is not held

Y Privilege is held without the GRANT option

SYS_START TIMESTAMP(12 Start time associated with most recent transaction
SYS_END TIMESTAMP(12 Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12 Timestamp value per transaction or null

SYSIBM.SYSVARIABLES

Contains one row for each global variable that is created

Column data Data type Description
VARID BIGINT The identifier of the global variable
SCHEMA VARCHAR(128) Schema name of the global variable
NAME VARCHAR(128) Unqualified name of the global variable
OWNER VARCHAR(128) Authorization ID of the owner of the global variable
OWNERTYPE CHAR(1) Type of owner of the global variable:

L The owner is a role

Blank The owner is an authorization ID

RELCREATED CHAR(1) Release of DB2 used to create the object
CREATEDTS TIMESTAMP Time at which the global variable was created
TYPESCHEMA VARCHAR(128) Schema name of data type. For built-in data types, is SYSIBM
TYPENAME VARCHAR(128) The unqualified name of the data type
DATATYPEID INTEGER For a built-in data type, 0. For a distinct type, the internal ID of the distinct type
SOURCETYPEID INTEGER For a built-in data type, 0. For a distinct type, the internal ID of the built-in data type on which the distinct type is based
LENGTH INTEGER Maximum length of a global variable. Zero if array data type
SCALE SMALLINT The scale of the global variable
CCSID INTEGER The CCSID of the global variable. 0 if array type
DEFAULT CHAR(3) The default value of the global variable. Can contain one of the following values:

N – no default value

S – SQL authorisation ID of the process

1 – String constant

2 – Floating-point constant

3 – Decimal constant

4 – Integer constant

5 – Hexadecimal character string

6 – UX string

7 – Graphic data type with default character string constant

8 – Character data type default value a character string constant

9 – DECFLOAT constant

If this column is on one of the following values, the default value of the global variable is the value of the indicated special register at the time that a default value is used:

AES CURRENT APPLICATION ENCODING SCHEME

ACT CURRENT CLIENT_ACCTNG

APN CURRENT CLIENT_APPLNAME

CID CURRENT CLIENT_USERID

WSN CURRENT CLIENT_WRKSTNNAME

DAT CURRENT DATE

DBG CURRENT DEBUG MODE

DEC CURRENT DECFLOAT ROUNDING MODE

DEG CURRENT DEGREE

EXP CURRENT EXPLAIN MODE

LCT CURRENT LOCALE LC_TYPE

MTT CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

MEM CURRENT MEMBER

HNT CURRENT OPTIMIZATION HINT

CPP CURRENT PACKAGE PATH

CPS CURRENT PACKAGESET

PTH CURRENT PATH

PRC CURRENT PRECISION

RFA CURRENT REFRESH AGE

RVS CURRENT ROUTINE VERSION

RUL CURRENT RULES

SCH CURRENT SCHEMA

SVR CURRENT SERVER

TIM CURRENT TIME

TST CURRENT TIMESTAMP

STZ SESSION TIME ZONE

U SESSION_USER

ROWID ROWID ROWID value for the lob columns in this table
DEFAULTEXT CLOB(2M) The text of the default value of the global variable
BLOB(2M) Reserved for IBM use
ENVID INTEGER Internal environment identifier
REMARKS VARCHAR(762) A character string about global variable provided by COMMENT
IBMREQD CHAR(1) Y indicates row came from (MRM) tape

SYSIBM.SYSVARIABLEAUTH

One row for each privilege of each authorization ID that has privileges on a global variable

Column name Data type Description
GRANTOR VARCHAR(128) Grantor of the privilege
GRANTORTYPE CHAR(1) Type of grantor:

blank Grantor is an authorization ID

L Grantor is a role

GRANTEE VARCHAR(128) Holder of the privilege
GRANTEETYPE CHAR(1) Type of grantee:

blank Grantee is an authorization ID

L Grantee is a role

P Grantee is a package. Grantee is a package if COLLID is a value other than blank

SCHEMA VARCHAR(128) Schema name of the global variable
NAME VARCHAR(128) Unqualified name of the global variable
COLLID VARCHAR(128) If grantee is a package, this value is the COLLID of package
CONTOKEN CHAR(8) If grantee is a package, this value is the consistency token of the DBRM from which the package is derived. Otherwise, blank
READAUTH CHAR(1) Privilege to read the global variable:

blank the READ privilege is not held

G The READ privilege is held with the GRANT option

Y The READ privilege is held without the GRANT option

WRITEAUTH CHAR(1) Privilege to write in a global variable:

blank not held

G held with the GRANT option

Y held without the GRANT option

AUTHHOWGOT CHAR(1) The authorization level of the user who granted the privileges:

blank Not applicable

E SECADM

G ACCESSCTRL

S SYSADM

T DATAACCESS

Not necessarily the highest authority level of the grantor

GRANTEDTS TIMESTAMP The time when the GRANT statement was executed
IBMREQD CHAR(1) Indicates that the row came from the MRM tape
SYS_START TIMESTAMP(12) Start time associated with the most recent transaction
SYS_END TIMESTAMP(12) Time row is deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or no

SYSIBM.SYSVARIABLES_DESC

Table is an auxiliary table for the SYSIBM.SYSVARIABLES table

Column name Data type Description
BLOB(2M) IBM internal use only

SYSIBM.SYSVARIABLES_TEXT

Table is an auxiliary table for the DEFAULTTEXT column SYSIBM.SYSVARIABLES table

Column name Data type Description
DEFAULTTEXT CLOB(2M) Text of the default value of the global variable

SYSIBM.SYSVIEWDEP

Records the dependencies of views on tables, functions, and other views

Column name Data type Description
BNAME VARCHAR(128) Name of object on which view is dependent. If object type is a function (BTYPE=’F’), name is the specific name of the function
BCREATOR VARCHAR(128) Authorization ID of owner of BNAME. For function, schema name of BNAME
BTYPE CHAR(1) Type of object:

F Function

M Materialized query table

T Table

V View

DNAME VARCHAR(128) Name of the view
DCREATOR VARCHAR(128) Schema of the view
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
BSCHEMA VARCHAR(128) Schema of BNAME
DTYPE CHAR(1) Type of table

F SQL Function

M Materialized

V View

DOWNER VARCHAR(128) Auth ID of owner of view, blank for views created prior to V9
OWNERTYPE CHAR(1) Indicates type of owner:

blank Authorization ID

L Role

SYS_START TIMESTAMP(12) Start time associated with most recent transaction
SYS_END TIMESTAMP(12) Time row deleted from system-period temporal table
TRANS_START TIMESTAMP(12) Timestamp value per transaction or null

SYSIBM.SYSVIEWS

Contains one or more rows for each view

Column name Data type Description
NAME VARCHAR(128) Name of the view
CREATOR VARCHAR(128) Schema of the view
SMALLINT Not used
CHECK CHAR(1) Whether WITH CHECK OPTION clause was specified in the CREATE VIEW statement:

N No

C Yes with the cascaded semantic

Y Yes with the local semantic

The value is N if the view has no where clause

IBMREQD CHAR(1) Y indicates row came from basic MRM tape
VARCHAR(1500) Not used
PATHSCHEMAS VARCHAR(2048) SQL path at time view was defined. Path is used to resolve unqualified data type and function names used in view definition
RELCREATED CHAR(1) Release of DB2 used to create the object. Blank if prior to V9
TYPE CHAR(1) Type of table:

F SQL function

M Materialized Query Table

V View

REFRESH CHAR(1) Refresh mode

D An MQT with a deferred refresh mode

Blank Not an mqt

ENABLE CHAR(1) MQT is enabled or disabled for query optimization:

Y Enabled

N Disabled

Blank Row describes a view

MAINTENANCE CHAR(1) Maintenance mode

S Maintained by system

U Maintained by user

Blank Row describes a view

REFRESH_TIME TIMESTAMP For REFRESH = ‘D’ and MAINTENANCE = ‘S’, timestamp of REFRESH TABLE statement that last refreshed data
ISOLATION CHAR(1) Isolation level when MQT is created or altered from base table

R RR (repeatable read)

S CS (cursor stability)

T RS (read stability)

U UR (uncommitted read)

Blank Not a materialized query table

SIGNATURE VARCHAR(1024) Contains the internal description. Used for MQT tables
APP_ENCODING_

CCSID

INTEGER CCSID of the current application encoding scheme at the time object was created. For objects created priorto V8, value is 0
OWNER VARCHAR(128) Authorization ID of the owner of the view
OWNERTYPE CHAR(1) Type of owner:

blank authorisation ID

L Role

ENVID INTEGER Internal environment identifier
ROWID ROWID ROWID column, created for the lob columns in this table
STATEMENT CLOB(2M) Text of entire CREATE VIEW statement used to create object
BLOB(1G) Internal use only

SYSIBM.SYSVIEWS_STMT

Auxiliary table for the STATEMENT column of the SYSIB,.SYSVIEWS table

Column name Data type Description
STATEMENT CLOB(2M) The text of the statement that was used to create the object

SYSIBM.SYSVIEWS_TREE

An auxiliary table for the PARSETREE column of the SYSIBM.SYSVIEWS table

Column name Data type Description
BLOB(2M) Internal use only

SYSIBM.SYSVOLUMES

Contains one row for each volume of each storage group

Column name Data type Description
SGNAME VARCHAR(128) Name of the storage group
SGCREATOR VARCHAR(128) Authorization ID of the owner of the storage group
VOLID VARCHAR(18) Serial number of the volume or * if SMS-managed
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
RELCREATED CHAR(1) Release of DB2 used to create object. Blank if created prior to V9

SYSIBM.SYSXMLRELS

Contains one row for each XML table that is created for an XML column

Column name Data type Description
TBOWNER VARCHAR(128) Schema or qualifier of the base table
TBNAME VARCHAR(128) Name of the base table
COLNAME VARCHAR(128) Name of the XML column in the base table
XMLTBOWNER VARCHAR(128) Schema or qualifier of the XML table
XMLTBNAME VARCHAR(128) Name of the XML table
XMLRELOBID INTEGER Internal identifier of relationships between base table and XML table
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
CREATEDTS TIMESTAMP Time when the XML table was created
RELCREATED CHAR(1) The release of DB2 that is used to create the object

SYSIBM.SYSXMLSTRINGS

Each row contains a single string and its unique ID that are used to condense XML data. The string can be an element name, attribute name, name space prefix, or namespace URL

Column name Data type Description
STRINGID INTEGER Unique ID for the string
STRING VARCHAR(1000) The string data
IBMREQD CHAR(1) Y indicates row came basic MRM tape

SYSIBM.USERNAME

Each row is used to carry out one of the following operations: Outbound ID translation or Inbound ID translation and ‘come from’ checking

Column name Data type Description
TYPE CHAR(1) How the row is to be used:

O For outbound translation

I For inbound translation and ‘come from’ checking

S For outbound system AUTHID to establish a trusted connection

AUTHID VARCHAR(128) Authorisation ID to be translated. Applies to any auth ID if blank
LINKNAME VARCHAR(24) Identifies VTAM or TCP/IP network locations associated with this row
NEWAUTHID VARCHAR(128) Translated value of AUTHID. Blank specifies no translation
PASSWORD VARCHAR(24) Password to accompany an outbound request, if passwords are not encrypted by RACF
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

SYSIBM.SYSXMLTYPMOD

Contains rows for XML type modifiers of XML columns. Can be inserted, updated and deleted

Column name Data type Description
XML_TYPEMOD_

ID

INTEGER An ID generated for XML type modifier, it is an identity column and primary key
TYPE_

ANNOTATION

CHAR(1) Indicate whether there is a type annotation

Y WITH type annotation

N with no type annotation

CREATEDTS TIMESTAMP The timestamp when is type modifiers created
ALTEREDTS TIMESTAMP The timestamp when is type modifiers altered
RELCREATED CHAR(1) The release of DB2 that is used to create the object
IBMREQD CHAR(1) Y indicates row came from basic MRM tape
CREATEDBY VARCHAR(128) Premier authorization ID of the user who created the database

SYSIBM.SYSXMLTYPMSCHEMA

Contains the XML schema information for ann XML type modifier

Column name Data type Description
XML_

TYPEMOD_ID

INTEGER The ID for the XML type modifier
XSROBJECTID INTEGER The id for an XML schema registered in XSR
ELEMENT_

NAMESPACE

INTEGER String id for namespace name of root element node. By default, it is TARGETNAMESPACE of XML schema. 0 if it is NO NAMESPACE
ELEMENT_NAME INTEGER String id for local name of the root element node. 0 if not specified
CREATEDTS TIMESTAMP The timestamp when this type modifier is created
ALTEREDTS TIMESTAMP The timestamp when this type modifier is altered
RELCREATED CHAR(1) The the release of DB2 that is used to create the object
IBMREQD CHAR(1) Y indicates row came from basic MRM tape

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.