DBPedias

All your database knowledge are belong to you

DB2 zOS

SYSIBM.SYSTABLES (V8)

Description: Contains one row for each table, view, or alias.

Tablespace: SYSDBASE

Indexes(Fields):
DSNDTX01(CREATOR.NAME)
DSNDTX02(DBID.OBID.CREATOR.NAME)
DSNDTX03(TBCREATOR.TBNAME)

Column name Description Data type
NAME Name of the table, view, or alias.
NOT NULL
CREATOR Authorization ID of the owner of the table, view, or alias.
NOT NULL
TYPE Type of object: ; A
Alias
G
Created global temporary table
M
Materialized query table
T
Table
V
View
X
Auxiliary table

NOT NULL
DBNAME For a table, or a view of tables, the name of the database that contains the table space named in TSNAME. For a created temporary table, an alias, or a view of a view, the value is DSNDB06.
NOT NULL
TSNAME For a table, or a view of one table, the name of the table space that contains the table. For a view of more than one table, the name of a table space that contains one of the tables. For a created temporary table, the value is SYSPKAGE. Although SYSPKAGE is used as the value, created temporary tables are not stored in the SYSPKAGE table space. For a view of a view, the value is SYSVIEWS. For an alias, it is SYSDBAUT.
NOT NULL
DBID Internal identifier of the database; 0 if the row describes a view, alias, or created temporary table.
NOT NULL
OBID Internal identifier of the table; 0 if the row describes a view, an alias, or a created temporary table.
NOT NULL
COLCOUNT Number of columns in the table or view. The value is 0 if the row describes an alias.
NOT NULL
EDPROC Name of the edit procedure; blank if the row describes a view or alias or a table without an edit procedure.
NOT NULL
VALPROC Name of the validation procedure; blank if the row describes a view or alias or a table without a validation procedure.
NOT NULL
CLUSTERTYPE Whether RESTRICT ON DROP applies: ; blank
No
Y
Yes. Neither the table nor any table space or database that contains the table can be dropped.

NOT NULL
Not used
NOT NULL
Not used
NOT NULL
NPAGES Total number of pages on which rows of the table appear. The value is -1 if statistics have not been gathered, or the row describes a view, an alias, a created temporary table, or an auxiliary table. This is an updatable column.
NOT NULL
PCTPAGES Percentage of active table space pages that contain rows of the table. A page is termed active if it is formatted for rows, regardless of whether it contains any. If the table space is segmented, the percentage is based on the number of active pages in the set of segments assigned to the table. The value is -1 if statistics have not been gathered, or the row describes a view, alias, created temporary table, or auxiliary table. This is an updatable column.
NOT NULL
IBMREQD A value of Y indicates that the row came from the basic machine-readable material (MRM) tape.
NOT NULL
REMARKS A character string provided by the user with the COMMENT statement.
NOT NULL
PARENTS Number of relationships in which the table is a dependent. The value is 0 if the row describes a view, an alias, a created temporary table, or a materialized query table.
NOT NULL
CHILDREN Number of relationships in which the table is a parent. The value is 0 if the row describes a view, an alias, a created temporary table, or a materialized query table.
NOT NULL
KEYCOLUMNS Number of columns in the table's primary key. The value is 0 if the row describes a view, an alias, or a created temporary table.
NOT NULL
RECLENGTH For user tables, the maximum length of any record in the table. Length is 8+N+L, where: * The number 8 accounts for the header (6 bytes) and the ID map entry (2 bytes).
  • N is 10 if the table has an edit procedure, or 0 otherwise.
  • L is the sum of the maximum column lengths. In determining a column's maximum length, take into account whether the column allows nulls and the data type of the column. If the column can contain nulls and is not a LOB or ROWID column, add 1 byte for a null indicator. Use 4 bytes for the length of a LOB column and 19 bytes for the length of a ROWID column. If the column has a varying-length data type (for example, VARCHAR, CLOB, or BLOB), add 2 bytes for a length indicator.

The value is 0 if the row describes a view, alias, or auxiliary table.


NOT NULL
STATUS Indicates the status of the table definition: ; I
The definition of the table is incomplete. The TABLESTATUS column indicates the reason for the table definition being incomplete.
R
An error occurred when an attempt was made to regenerate the internal representation of the view.
X
The table has a primary index and the table definition is complete.
blank
The table has no primary index, the table is a catalog table, or the row describes a view or alias. The definition of the table, view, or alias is complete.

NOT NULL
KEYOBID Internal DB2 identifier of the index that enforces uniqueness of the table's primary key; 0 if not applicable.
NOT NULL
LABEL The label as given by a LABEL statement; otherwise, the value is an empty string.
NOT NULL
CHECKFLAG ; C
The table space that contains the table is in a check pending status. One of the following conditions is true:* There are rows in the table that violate referential constraints, table check constraints, or both
  • The table is a materialized query table that might contain inconsistent data


blank
Indicates the following:* The table contains no rows that violate referential constraints, table check constraints, or both
  • The table is a materialized query table that contains consistent data
  • The row describes a view, an alias, or a temporary table



NOT NULL WITH
DEFAULT
Not used
NOT NULL WITH
DEFAULT
FOR BIT DATA
AUDITING Value of the audit option: ; A
AUDIT ALL
C
AUDIT CHANGE
blank
AUDIT NONE, or the row describes a view, an alias, or a created temporary table.

NOT NULL WITH
DEFAULT
CREATEDBY Primary authorization ID of the user who created the table, view, or alias.
NOT NULL WITH
DEFAULT
LOCATION Location name of the object of an alias. Blank for a table, a view, or for an alias that was not defined with a three-part object name.
NOT NULL WITH
DEFAULT
TBCREATOR For an alias, the authorization ID of the owner of the referred to table or view; blank otherwise.
NOT NULL WITH
DEFAULT
TBNAME For an alias, the name for the referred to table or view; blank otherwise.
NOT NULL WITH
DEFAULT
CREATEDTS Time when the CREATE statement was executed for the table, view, or alias
NOT NULL WITH
DEFAULT
ALTEREDTS For a table, the time when the latest ALTER TABLE statement was applied. If no ALTER TABLE statement has been applied, or if the row is for a view or alias, ALTEREDTS has the value of CREATEDTS.
NOT NULL WITH
DEFAULT
DATACAPTURE Records the value of the DATA CAPTURE option for a table: ; blank
No
Y
Yes

For a created temporary table, DATACAPTURE is always blank.


NOT NULL WITH
DEFAULT
RBA1 The log RBA when the table was created. Otherwise, RBA1 is , indicating that the log 
     RBA is not known, or that the object is a view, an alias, or a created
     temporary table. In a data sharing environment, RBA1 is the LRSN (Log
     Record Sequence Number) value.

NOT NULL WITH
DEFAULT
FOR BIT DATA
RBA2 The log RBA when the table was last altered. Otherwise, RBA2 is indicating 
     that the log RBA is not known, or that the object is a view, an alias, or
     a created temporary table. RBA1 will equal RBA2 if the table has not been
     altered. In a data sharing environment, RBA2 is the LRSN (Log Record
     Sequence Number) value.

NOT NULL WITH
DEFAULT
FOR BIT DATA
PCTROWCOMP Percentage of rows compressed within the total number of active rows in the table. This includes any row in a table space that is defined with COMPRESS YES. The value is -1 if statistics have not been gathered, or the row describes a view, alias, created temporary table, or auxiliary table. This is an updatable column.
NOT NULL WITH
DEFAULT
STATSTIME If RUNSTATS updated the statistics, the date and time when the last invocation of RUNSTATS updated the statistics. The default value is '0001-01-01.00.00.00.000000'. For a created temporary table, the value of STATSTIME is always the default value. This is an updatable column.
NOT NULL WITH
DEFAULT
CHECKS Number of check constraints defined on the table. The value is 0 if the row describes a view, an alias, a created temporary table, or a materialized query table,or if no constraints are defined on the table.
NOT NULL WITH
DEFAULT
CARDF Total number of rows in the table or total number of LOBs in an auxiliary table. The value is -1 if statistics have not been gathered or the row describes a view, alias, or created temporary table. This is an updatable column.
NOT NULL WITH
DEFAULT -1
CHECKRID5B Blank if the table or partition is not in a check pending status (CHECKFLAG is blank), if the table space is not partitioned, or if the table is a created temporary table. Otherwise, the RID of the first row of the table space partition that can violate referential constraints, check constraints, or both; or the value is X'0000000000', indicating that any row can violate referential constraints.
NOT NULL WITH
DEFAULT
FOR BIT DATA
ENCODING_SCHEME Encoding scheme for tables, views, and local aliases: ; E
EBCDIC
A
ASCII
M
Multiple CCSID set or multiple encoding schemes
U
UNICODE
blank
For remote aliases

The value is 'E' for tables in non work file databases and blank for tables in work file databases created prior to Version 5 or the default database, DSNDB04.


NOT NULL WITH
DEFAULT 'E'
TABLESTATUS Indicates the reason for an incomplete table definition: ; L
Definition is incomplete because an auxiliary table or auxiliary index has not been defined for a LOB column.
P
Definition is incomplete because the table lacks a primary index.
R
Definition is incomplete because the table lacks a required index on a row ID.
U
Definition is incomplete because the table lacks a required index on a unique key.
V
An error occurred when an attempt was made to regenerate the internal representation of the view.
blank
Definition is complete.

NOT NULL WITH
DEFAULT
NPAGESF Number of pages used by the table. The value is -1 if statistics have not been gathered. This is an updatable column.
NOT NULL WITH
DEFAULT -1
SPACEF Kilobytes of DASD storage. The value is -1 if statistics have not been gathered. This is an updatable column.
NOT NULL WITH
DEFAULT -1
AVGROWLEN Average length of rows for the tables in the table space. If the table space is compressed, the value is the compressed row length. If the table space is not compressed, the value is the uncompressed row length. The value is -1 if statistics have no t been gathered.
NOT NULL WITH
DEFAULT -1
RELCREATED Release of DB2 that was used to create the object: ; blank
Created prior to Version 7
K
Created on Version 7

NOT NULL WITH
DEFAULT
NUM_DEP_MQTS Number of dependent materialized query tables. The value is zero if the row describes an alias or a created temporary table, or if no materialized query tables are defined on the table.
NOT NULL WITH
DEFAULT
VERSION The version of the data row format for this table. A value of zero indicates that a version-creating alter operation has never occurred against this table. A value of 800 indicates that a successful CREATE VIEW or ALTER VIEW statement has occurred against this table. A value of -1 indicates that the view needs to be regenerated.
NOT NULL WITH
DEFAULT
PARTKEYCOLNUM The number of columns in the partitioning key. This value is zero for tables that do not have partitioning or use index-controlled partitioning. The value is non-zero for tables that use table-controlled partitioning.
NOT NULL WITH
DEFAULT
SPLIT_ROWS Value is blank, except for VOLATILE tables, which will have 'Y' in the field to indicate to DB2 to use index access on this table whenever possible.
NOT NULL WITH
DEFAULT
SECURITY_LABEL This column is only meaningful if the TYPE column is a T (for table) or M (for materialized query table). The value indicates whether the table has multilevel security: ; Blank
The table does not have multilevel security.
R
The table has multilevel security with row granularity.

NOT NULL

Adapted from material found in the IBM DB2 Universal Database for z/OS V8 SQL Reference (SC18-7426-02).

Retrieved from "http://dbpedias.com/w/index.php?title=DB2_zOS:SYSIBM.SYSTABLES_(V8)&oldid=22052"