IBM Books

Administration Guide

"Roadmap" to Updatable Catalog Views


Description Catalog View
columns "SYSSTAT.COLUMNS"
indexes "SYSSTAT.INDEXES"
detailed column statistics "SYSSTAT.COLDIST"
tables "SYSSTAT.TABLES"
user-defined functions "SYSSTAT.FUNCTIONS"

SYSCAT.BUFFERPOOLS

Contains a row for every buffer pool in every nodegroup.

Table 44. SYSCAT.BUFFERPOOLS Catalog View
Column Name Data Type Nullable Description
BPNAME VARCHAR(18)
Name of buffer pool
BUFFERPOOLID INTEGER
Internal buffer pool identifier
NGNAME VARCHAR(18) Yes Nodegroup name (NULL if the buffer pool exists on all nodes in the database)
NPAGES INTEGER
Number of pages in the buffer pool
PAGESIZE INTEGER
Pagesize for this buffer pool
ESTORE CHAR(1) 
  N=This buffer pool does not use extended storage
  Y=This buffer pool uses extended storage

SYSCAT.BUFFERPOOLNODES

Contains a row for each node in the buffer pool for which the size of the buffer pool on the node is different from the default size in SYSCAT.BUFFERPOOLS column NPAGES.

Table 45. SYSCAT.BUFFERPOOLNODES Catalog View
Column Name Data Type Nullable Description
BUFFERPOOLID INTEGER
Internal buffer pool identifier
NODENUM SMALLINT
Node Number
NPAGES INTEGER
Number of pages in this buffer pool on this node

SYSCAT.CHECKS

Contains one row for each CHECK constraint.

Table 46. SYSCAT.CHECKS Catalog View
Column Name Data Type Nullable Description
CONSTNAME VARCHAR(18)
Name of the check constraint (unique within a table.)
DEFINER CHAR(8)
Authorization ID under which the check constraint was defined.
TABSCHEMA CHAR(8)
Qualified name of the table to which this constraint applies.
TABNAME VARCHAR(18)
CREATE_TIME TIMESTAMP
The time at which the constraint was defined. Used in resolving functions that are used in this constraint. No functions will be chosen that were created after the definition of the constraint.
FUNC_PATH VARCHAR(254)
The current function path that was used when the constraint was created.
TEXT CLOB(32K)
The text of the CHECK clause.

SYSCAT.COLAUTH

Contains one or more rows for each user or group who is granted a column level privilege, indicating the type of privilege and whether or not it is grantable.

Table 47. SYSCAT.COLAUTH Catalog View
Column Name Data Type Nullable Description
GRANTOR CHAR(8)
Authorization ID of the user who granted the privileges or SYSIBM.
GRANTEE CHAR(8)
Authorization ID of the user or group who holds the privileges.
GRANTEETYPE CHAR(1) 
  U=Grantee is an individual user
  G=Grantee is a group

TABSCHEMA CHAR(8)
Qualified name of the table or view.
TABNAME VARCHAR(18)
COLNAME VARCHAR(18)
Name of the column to which this privilege applies.
COLNO SMALLINT
Number of this column in the table or view.
PRIVTYPE CHAR(1)
Indicates the type of privilege held on the table or view: 
  U=update privilege.
  R=reference privilege.
GRANTABLE CHAR(1)
Indicates if the privilege is grantable. 
  G=grantable.
  N=not grantable.

SYSCAT.COLCHECKS

Each row represents some column that is referenced by a CHECK constraint.

Table 48. SYSCAT.COLCHECKS Catalog View
Column Name Data Type Nullable Description
CONSTNAME VARCHAR(18)
Name of the check constraint. (Unique within a table. May be system generated.)
TABSCHEMA CHAR(8)
Qualified name of table containing referenced column.
TABNAME VARCHAR(18)
COLNAME VARCHAR(18)
Name of column.

SYSCAT.COLDIST

Contains detailed column statistics for use by the optimizer. Each row describes the Nth-most-frequent value of some column.

Table 49. SYSCAT.COLDIST Catalog View
Column Name Data Type Nullable Description
TABSCHEMA CHAR(8)
Qualified name of the table to which this entry applies.
TABNAME VARCHAR(18)
COLNAME VARCHAR(18)
Name of the column to which this entry applies.
TYPE CHAR(1)
F=Frequency (most frequent value) Q=Quantile value
SEQNO SMALLINT
If TYPE=F, then N in this column identifies the Nth most frequent value. If TYPE=Q, then N in this column identifies the Nth quantile value.
COLVALUE VARCHAR(33) Yes The data value, as a character literal or a null value.
VALCOUNT INTEGER
If TYPE=F, then VALCOUNT is the number of occurrences of COLVALUE in the column. If TYPE=Q, then VALCOUNT is the number of rows whose value is less than or equal to COLVALUE.
DISTCOUNT INTEGER Yes If TYPE=Q, this column records the number of distinct values that are less than or equal to COLVALUE (null if unavailable).

SYSCAT.COLUMNS

Contains one row for each column that is defined for a table or view. All of the catalog views have entries in the SYSCAT.COLUMNS table.

Table 50. SYSCAT.COLUMNS Catalog View
Column Name Data Type Nullable Description
TABSCHEMA CHAR(8)
Qualified name of the table or view that contains the column.
TABNAME VARCHAR(18)
COLNAME VARCHAR(18)
Column name.
COLNO SMALLINT
Numerical place of column in table or view, beginning at zero.
TYPESCHEMA CHAR(8)
Contains the qualified name of the type, if the data type of the column is distinct. Otherwise TYPESCHEMA contains the value SYSIBM and TYPENAME contains the data type of the column (in long form, for example, CHARACTER). If FLOAT or FLOAT(n) with n greater than 24 is specified, TYPENAME is renamed to DOUBLE. If FLOAT(n) with n less than 25 is specified, TYPENAME is renamed to REAL. Also, NUMERIC is renamed to DECIMAL.
TYPENAME VARCHAR(18)
LENGTH INTEGER
Maximum length of data. 0 for distinct types. The LENGTH column indicates precision for DECIMAL fields.
SCALE SMALLINT
Scale for DECIMAL fields; 0 if not DECIMAL.
DEFAULT VARCHAR(254) Yes Default value for the column of a table expressed as a constant, special register, or cast-function appropriate for the data type of the column. May also be the keyword NULL.

Values may be converted from what was specified as a default value. For example, date and time constants are presented in ISO format and cast-function names are qualified with schema name and the identifiers are delimited (see Note 3).

Null value if a DEFAULT clause was not specified or the column is a view column.

NULLS CHAR(1) 
  Y=Column is nullable.
  N=Column is not nullable.

The value can be N for a view column that is derived from an expression or function. Nevertheless, such a column allows nulls when the statement using the view is processed with warnings for arithmetic errors.

See Note 1.

CODEPAGE SMALLINT
Code page of the column. For character-string columns not defined with the FOR BIT DATA attribute, the value is the database code page. For graphic-string columns, the value is the DBCS code page implied by the (composite) database code page. Otherwise, the value is 0.
LOGGED CHAR(1)
Applies only to columns whose type is LOB or distinct based on LOB (blank otherwise). 
  Y=Column is logged.
  N=Column is not logged.
COMPACT CHAR(1)
Applies only to columns whose type is LOB or distinct based on LOB (blank otherwise). 
  Y=Column is compacted in storage.
  N=Column is not compacted.
COLCARD INTEGER
Number of distinct values in the column; -1 if statistics are not gathered.
HIGH2KEY VARCHAR(33)
Second highest value of the column. This field is empty if statistics are not gathered. See Note 2.
LOW2KEY VARCHAR(33)
Second lowest value of the column. Empty if statistics not gathered. See Note 2.
AVGCOLLEN INTEGER
Average column length. -1 if a long field or LOB, or statistics have not been collected.
KEYSEQ SMALLINT Yes The column's numerical position within the table's primary key. This field is null or 0 if the column is not part of the primary key.
PARTKEYSEQ SMALLINT Yes The column's numerical position within the table's partitioning key. This field is null or 0 if the column is not part of the partitioning key.
NQUANTILES SMALLINT
Number of quantile values recorded in SYSCAT.SYSCOLDIST for this column; -1 if no statistics.
NMOSTFREQ SMALLINT
Number of most-frequent values recorded in SYSCAT.COLDIST for this column; -1 if statistics not gathered.
REMARKS VARCHAR(254) Yes User-supplied comment.
Note:

  1. Starting with Version 2, value D (indicating not null with a default) is no longer used. Instead, use of WITH DEFAULT is indicated by a non-null value in the DEFAULT column.

  2. Starting with Version 2, representation of numeric data has been changed to character literals. The size has been enlarged from 16 to 33 bytes.

  3. For Version 2.1.0, cast-function names were not delimited and may still appear this way in the DEFAULT column. Also, some view columns included default values which will still appear in the DEFAULT column.

SYSCAT.CONSTDEP

Contains a row for every dependency of a constraint on some other object.

Table 51. SYSCAT.CONSTDEP Catalog View
Column Name Data Type Nullable Description
CONSTNAME VARCHAR(18)
Name of the constraint.
TABSCHEMA CHAR(8)
Qualified name of the table to which the constraint applies.
TABNAME VARCHAR(18)
BTYPE CHAR(1)
Type of object that the constraint depends on. Possible values: 
  F=function instance.
  I=index instance.
BSCHEMA CHAR(8)
Qualified name of object that the constraint depends on.
BNAME VARCHAR(18)

SYSCAT.DATATYPES

Contains a row for every data type, including built-in and user-defined types.

Table 52. SYSCAT.DATATYPES Catalog View
Column Name Data Type Nullable Description
TYPESCHEMA CHAR(8)
Qualified name of the data type (for built-in types, TYPESCHEMA is SYSIBM).
TYPENAME VARCHAR(18)
DEFINER CHAR(8)
Authorization ID under which type was created.
SOURCESCHEMA CHAR(8) Yes Qualified name of the source type for distinct types. Null for other types.
SOURCENAME VARCHAR(18) Yes
METATYPE CHAR(1) 
  S=System predefined type
  T=Distinct type

TYPEID SMALLINT
Internal type ID.
SOURCETYPEID SMALLINT Yes Internal type ID of source type (null for built-in types).
LENGTH INTEGER
Maximum length of the type. 0 for system predefined parameterized types (for example, DECIMAL and VARCHAR).
SCALE SMALLINT
Scale for distinct types based on the system predefined DECIMAL type. 0 for all other types (including DECIMAL itself).
CODEPAGE SMALLINT
Code page for character and graphic distinct types; 0 otherwise.
CREATE_TIME TIMESTAMP
Creation time of the data type.
REMARKS VARCHAR(254) Yes User-supplied comment, or null.

SYSCAT.DBAUTH

Records the database authorities held by users.

Table 53. SYSCAT.DBAUTH Catalog View
Column Name Data Type Nullable Description
GRANTOR CHAR(8)
SYSIBM or authorization ID of the user who granted the privileges.
GRANTEE CHAR(8)
Authorization ID of the user or group who holds the privileges.
GRANTEETYPE CHAR(1) 
  U=Grantee is an individual user
  G=Grantee is a group

DBADMAUTH CHAR(1)
Whether grantee holds DBADM authority over the database: 
  Y=Authority is held
  N=Authority is not held
CREATETABAUTH CHAR(1)
Whether grantee can create tables in the database (CREATETAB): 
  Y=Privilege is held
  N=Privilege is not held
BINDADDAUTH CHAR(1)
Whether grantee can create new packages in the database (BINDADD): 
  Y=Privilege is held
  N=Privilege is not held
CONNECTAUTH CHAR(1)
Whether grantee can connect to the database (CONNECT): 
  Y=Privilege is held
  N=Privilege is not held
NOFENCEAUTH CHAR(1)
Whether grantee holds privilege to create non-fenced functions. 
  Y=Privilege is held
  N=Privilege is not held
IMPLSCHEMAAUTH CHAR(1)
Whether grantee can implicitly create schemas in the database (IMPLICIT_SCHEMA): 
  Y=Privilege is held
  N=Privilege is not held

SYSCAT.EVENTMONITORS

Contains a row for every event monitor that has been defined.

Table 54. SYSCAT.EVENTMONITORS Catalog View
Column Name Data Type Nullable Description
EVMONNAME VARCHAR(18)
Name of event monitor.
DEFINER CHAR(8)
Authorization ID of definer of event monitor.
TARGET_TYPE CHAR(1)
The type of the target to which event data is written. Values: 
  F=File
  P=Pipe
TARGET VARCHAR(246)
Name of the target to which event data is written. Absolute pathname of file, or absolute name of pipe.
MAXFILES INTEGER Yes Maximum number of event files that this event monitor permits in an event path. Null if there is no maximum, or if the target-type is not FILE.
MAXFILESIZE INTEGER Yes Maximum size (in 4K pages) that each event file can reach before the event monitor creates a new file. Null if there is no maximum, or if the target-type is not FILE.
BUFFERSIZE INTEGER Yes Size of buffers (in 4K pages) used by event monitors with file targets; otherwise null.
IO_MODE CHAR(1) Yes Mode of file I/O. 
  B=Blocked
  N=Not blocked.
Null if target-type is not FILE.
WRITE_MODE CHAR(1) Yes Indicates how this monitor handles existing event data when the monitor is activated. Values: 
  A=Append
  R=Replace
Null if target-type is not FILE.
AUTOSTART CHAR(1)
The event monitor will be activated automatically when the database starts. 
  Y=Yes
  N=No
NODENUM SMALLINT
The number of the partition (or node) on which the event monitor runs and logs events
MONSCOPE CHAR(1)
Monitoring scope: 
  L=Local
  G=Global
REMARKS VARCHAR(254) Yes Reserved for future use.

SYSCAT.EVENTS

Contains a row for every event that is being monitored. An event monitor, in general, monitors multiple events.

Table 55. SYSCAT.EVENTS Catalog View
Column Name


Data Type


Nullable


Description


EVMONNAME


VARCHAR(18)






Name of event monitor that is monitoring this event.






TYPE





VARCHAR(18)









Type of event being monitored.  Possible values:


 

  DATABASE
  CONNECTIONS
  TABLES
  STATEMENTS
  TRANSACTIONS
  DEADLOCKS
  TABLESPACES






FILTER





CLOB(32K)





Yes





The full text of the WHERE-clause
that applies to this event.









SYSCAT.FUNCPARMS





Contains a row for every parameter or result of a function defined in
SYSCAT.FUNCTIONS.



Table 56. SYSCAT.FUNCPARMS Catalog View





Column Name
Data Type
Nullable
Description


FUNCSCHEMA
CHAR(8)


Qualified function name.

FUNCNAME
VARCHAR(18)



SPECIFICNAME
VARCHAR(18)


The name of the function instance (may be system-generated).


ROWTYPE
CHAR(1)




  P=parameter
  R=result before casting
  C=result after casting




ORDINAL
SMALLINT


If ROWTYPE=P, the parameter's numerical position within the
function signature. Otherwise 0.


PARMNAME
VARCHAR(18)


Name of parameter or result column, or null if no name
exists.


TYPESCHEMA
CHAR(8)


Qualified name of data type of parameter or result.

TYPENAME
VARCHAR(18)



LENGTH
INTEGER


Length of parameter or result. 0 if parameter or result is a distinct
type. See Note 1.


SCALE
SMALLINT


Scale of parameter or result. 0 if parameter or result is a distinct
type. See Note 1.


CODEPAGE
SMALLINT


Code page of parameter. 0 denotes either not applicable or a column for
character data declared with the FOR BIT DATA attribute.


CAST_FUNCID
INTEGER
Yes
Internal function ID.


AS_LOCATOR
CHAR(1)




  Y=Parameter or result is passed in 
    the form of a locator
  N=Not passed in the form of a locator.






Note:

    

  1. LENGTH and SCALE are set to 0 for sourced functions (functions defined
with a reference to another function) because they inherit the length and
scale of parameters from their source.











SYSCAT.FUNCTIONS





Contains a row for each user-defined function (scalar, table or sourced).
Does not include built-in functions.



Table 57. SYSCAT.FUNCTIONS Catalog View





Column Name
Data Type
Nullable
Description


FUNCSCHEMA
CHAR(8)


Qualified function name.

FUNCNAME
VARCHAR(18)



SPECIFICNAME
VARCHAR(18)


The name of the function instance (may be system-generated).


DEFINER
CHAR(8)


Authorization ID of function definer.


FUNCID
INTEGER


Internally-assigned function ID.


RETURN_TYPE
SMALLINT


Internal type code of return type of function.


ORIGIN
CHAR(1)




  B=Built-in
  E=User-defined, external
  U=User-defined, based on a source
  S=System-generated




TYPE
CHAR(1)




  S=Scalar function
  C=Column function
  T=Table function




PARM_COUNT
SMALLINT


Number of function parameters.


PARM_SIGNATURE
VARCHAR(180) FOR BIT DATA


Concatenation of up to 90 parameter types, in internal format. Zero
length if function takes no parameters.


CREATE_TIME
TIMESTAMP


Timestamp of function creation. Set to 0 for Version 1 functions.


VARIANT
CHAR(1)




  Y=Variant (results may differ)
  N=Invariant (results are consistent)
  Blank if ORIGIN is not E




SIDE_EFFECTS
CHAR(1)




  E=Function has external side-effects
    (number of invocations is important)
  N=No side-effects
  Blank if ORIGIN is not E




FENCED
CHAR(1)




  Y=Fenced
  N=Not fenced
  Blank if ORIGIN is not E




NULLCALL
CHAR(1)




  Y=Nullcall
  N=No nullcall (function result is
    implicitly null if operand(s) are null).
  Blank if ORIGIN is not E.




CAST_FUNCTION
CHAR(1)




  Y=This is a cast function
  N=This is not a cast function




ASSIGN_FUNCTION
CHAR(1)




  Y=Implicit assignment function
  N=Not an assignment function




SCRATCHPAD
CHAR(1)




  Y=This function has a scratch pad
  N=This function does not have a scratch pad
  Blank if ORIGIN is not E




FINAL_CALL
CHAR(1)




  Y=Final call is made to this function at run time
    end-of-statement.
  N=No final call is made.
  Blank if ORIGIN is not E




PARALLELIZABLE
CHAR(1)




  Y=Function can be executed in parallel
  N=Function cannot be executed in parallel
  Blank if ORIGIN is not E




CONTAINS_SQL
CHAR(1)


Indicates wheter an external function contains SQL. 

  N=Function does not contain SQL statements.
  R=Contains read-only SQL statements.
  M=Contains SQL statements that modify data.
  Blank if ORIGIN is not E




DBINFO
CHAR(1)


Indicates whether a DBINFO parameter is passed to an external function. 

  Y=DBINFO is passed.
  N=DBINFO is not passed.
  Blank if ORIGIN is not E




RESULT_COLS
SMALLINT


For a table function (TYPE=T) contains the number of columns in the
result table; otherwise contains 1.


LANGUAGE
CHAR(8)


Implementation language of function body. Possible values are C, JAVA or
OLE. Blank if ORIGIN is not E.


IMPLEMENTATION
VARCHAR(254)
Yes
If ORIGIN=E, identifies the path/module/function that implements this
function. If ORIGIN=U and the source function is built-in, this column
contains the name and signature of the source function. Null otherwise.


PARM_STYLE
CHAR(8)


Indicates the parameter style declared in the CREATE FUNCTION statement.
Values: 

        DB2SQL
        DB2GENRL




SOURCE_SCHEMA
CHAR(8)
Yes
If ORIGIN=U and the source function is a user-defined function,
contains the qualified name of the source function. If ORIGIN=U and the
source function is built-in, SOURCE_SCHEMA is 'SYSIBM' and
SOURCE_SPECIFIC is 'N/A for built-in'. Null if ORIGIN is not U.

SOURCE_SPECIFIC
VARCHAR(18)
Yes


IOS_PER_INVOC
DOUBLE


Estimated number of I/Os per invocation; -1 if not known (0 default).


INSTS_PER_INVOC
DOUBLE


Estimated number of instructions per invocation; -1 if not known (450
default).


IOS_PER_ARGBYTE
DOUBLE


Estimated number of I/O's per input argument byte; -1 if not known
(0 default).


INSTS_PER_ARGBYTE
DOUBLE


Estimated number of instructions per input argument byte; -1 if not known
(0 default).


PERCENT_ARGBYTES
SMALLINT


Estimated average percent of input argument bytes that the function will
actually read; -1 if not known (100 default).


INITIAL_IOS
DOUBLE


Estimated number of I/O's performed the first/last time the function
is invoked; -1 if not known (0 default).


INITIAL_INSTS
DOUBLE


Estimated number of instructions executed the first/last time the
function is invoked; -1 if not known (0 default).


CARDINALITY
INTEGER
Yes
The predicted cardinality of a table function. -1 if not known or
if function is not a table function.


REMARKS
VARCHAR(254)
Yes
User-supplied comment, or null.







SYSCAT.INDEXAUTH





Contains a row for every privilege held on an index.



Table 58. SYSCAT.INDEXAUTH Catalog View





Column Name
Data Type
Nullable
Description


GRANTOR
CHAR(8)


Authorization ID of the user who granted the privileges.


GRANTEE
CHAR(8)


Authorization ID of the user or group who holds the privileges.


GRANTEETYPE
CHAR(1)




  U=Grantee is an individual user
  G=Grantee is a group




INDSCHEMA
CHAR(8)


Name of the index.

INDNAME
VARCHAR(18)



CONTROLAUTH
CHAR(1)


Whether grantee holds CONTROL privilege over the index: 

  Y=Privilege is held
  N=Privilege is not held









SYSCAT.INDEXES





Contains one row for each index that is defined for a table.



Table 59. SYSCAT.INDEXES Catalog View







Column Name





Data Type





Nullable





Description




INDSCHEMA
CHAR(8)


Name of the index.

INDNAME
VARCHAR(18)





DEFINER





CHAR(8)









User who created the index.




TABSCHEMA
CHAR(8)


Qualified name of the table on which the index is defined.

TABNAME
VARCHAR(18)





COLNAMES





VARCHAR(320)









List of column names, each preceded by + or - to
indicate ascending or descending order respectively.






UNIQUERULE





CHAR(1)









Unique rule:


 

  D=duplicates allowed
  U=unique entries only allowed
  P=primary index.






MADE_UNIQUE





CHAR(1)










 

  Y=Index was originally
      non-unique but was converted
      to a unique index to support
      a unique or primary key constraint.
      If the constraint is dropped, the
      index will revert to non-unique.
  N=Index remains as it was created.






COLCOUNT





SMALLINT









Number of columns in the key.






UNIQUE_COLCOUNT





SMALLINT









The number of columns required for 
a unique key.
Always <=COLCOUNT.
-1 if index has no unique key
(permits duplicates)






INDEXTYPE





CHAR(4)









Type of index.
REG =Regular






PCTFREE





SMALLINT









Percentage of each index page to
be reserved during initial building of the
index.  This space is available for future
inserts after the index is built.






IID





SMALLINT









Internal index ID.






NLEAF





INTEGER









Number of leaf pages;
-1 if statistics are not gathered.






NLEVELS





SMALLINT









Number of index levels;
-1 if statistics are not gathered.






FIRSTKEYCARD





INTEGER









Number of distinct first key values;
-1 if statistics are not gathered.






FIRST2KEYCARD





INTEGER









Number of distinct keys using the first 
two columns of the index (-1 if no statistics
or inapplicable)






FIRST3KEYCARD





INTEGER









Number of distinct keys using the first 
three columns of the index (-1 if no statistics
or inapplicable)






FIRST4KEYCARD





INTEGER









Number of distinct keys using the first 
four columns of the index (-1 if no statistics
or inapplicable)






FULLKEYCARD





INTEGER









Number of distinct full key values;
-1 if statistics are not gathered.






CLUSTERRATIO





SMALLINT









Degree of data clustering with the index;
-1 if statistics are not gathered
or if detailed index statistics are gathered
(in which case, CLUSTERFACTOR will be used instead).






CLUSTERFACTOR





DOUBLE









Finer measurement of degree of
clustering, or -1
if detailed index statistics have not been gathered.






SEQUENTIAL_PAGES





INTEGER









Number of leaf pages located on disk in index
key order with few or no large gaps between them.
(-1 if no statistics are available.)






DENSITY





INTEGER









Ratio of SEQUENTIAL_PAGES to number
of pages in the range of pages occupied by the index,
expressed as a percent (integer between 0 and 100,
-1 if no statistics are available.)






USER_DEFINED





SMALLINT









1 if this index was defined by a
user and has not been dropped;
otherwise 0.






SYSTEM_REQUIRED





SMALLINT









1 if this index is required for
primary key or unique key
constraint; otherwise 0.






CREATE_TIME





TIMESTAMP









Time when the index was created.






STATS_TIME





TIMESTAMP





Yes





Last time when any change was
made to recorded statistics for this
index.  Null if no statistics available.






PAGE_FETCH_PAIRS





VARCHAR(254)









A list of pairs of integers, represented
in character form.
Each pair represents the number of
pages in a hypothetical buffer, and
the number of page fetches
required to scan the table with this index using
that hypothetical buffer.
(Zero-length
string if no data available.)






REMARKS





VARCHAR(254)





Yes





User-supplied comment, or null.






TEXT





CLOB(32K)





Yes



Reserved for future use.







SYSCAT.KEYCOLUSE





Lists all columns that participate in a key defined by a unique, primary key, or foreign key constraint.



Table 60. SYSCAT.KEYCOLUSE Catalog View







Column Name





Data Type





Nullable





Description






CONSTNAME





VARCHAR(18)









Name of the constraint (unique within a table).




TABSCHEMA
CHAR(8)


Qualified name of the table containing the column.

TABNAME
VARCHAR(18)





COLNAME





VARCHAR(18)









Name of the column.






COLSEQ





SMALLINT









Numeric position of the column in the
key (initial position=1).







SYSCAT.NODEGROUPDEF





Contains a row for each partition that is contained in a nodegroup.



Table 61. SYSCAT.NODEGROUPDEF Catalog View





Column Name
Data Type
Nullable
Description


NGNAME
VARCHAR(18)


The name of the nodegroup that contains the partition (or node).


NODENUM
SMALLINT


The partition (or node) number of a partition contained in the nodegroup.
A valid partition number is between 0 and 999 inclusive.


IN_USE
CHAR(1)


Status of the partition (or node). 



A

The newly added partition is not in the partitioning map but the containers for the table
spaces in the nodegroup are created. The partition is added to the partitioning map when a Redistribute Nodegroup
operation is successfully completed.

D

The partition will be dropped when a Redistribute Nodegroup operation is
completed.

T

The newly added partition is not in the partitioning map and it was added using the WITHOUT
TABLESPACES clause. Containers must be specifically added to the table spaces
for the nodegroup.

Y

The partition is in the partitioning map.







SYSCAT.NODEGROUPS





Contains a row for each nodegroup.



Table 62. SYSCAT.NODEGROUPS Catalog View





Column Name
Data Type
Nullable
Description


NGNAME
VARCHAR(18)


Name of the nodegroup.


DEFINER
CHAR(8)


Authorization ID of the nodegroup definer.


PMAP_ID
SMALLINT


Identifier of the partitioning map in SYSCAT.PARTITIONMAPS.


REBALANCE_PMAP_ID
SMALLINT


Identifier of the partitioning map currently being used for
re-distribution. Value is -1 if re-distribution is currently not in progress.


CREATE_TIME
TIMESTAMP


Creation time of nodegroup.


REMARKS
VARCHAR(254)
Yes
User-provided comment.







SYSCAT.PACKAGEAUTH





Contains a row for every privilege held on a package.



Table 63. SYSCAT.PACKAGEAUTH Catalog View





Column Name
Data Type
Nullable
Description


GRANTOR
CHAR(8)


Authorization ID of the user who granted the privileges.


GRANTEE
CHAR(8)


Authorization ID of the user or group who holds the privileges.


GRANTEETYPE
CHAR(1)




  U=Grantee is an individual user
  G=Grantee is a group




PKGSCHEMA
CHAR(8)


Name of the package on which the privileges are held.

PKGNAME
CHAR(8)



CONTROLAUTH
CHAR(1)


Indicates whether grantee holds CONTROL privilege on the package: 

  Y=Privilege is held.
  N=Privilege is not held.




BINDAUTH
CHAR(1)


Indicates whether grantee holds BIND privilege on the package: 

  Y=Privilege is held.
  N=Privilege is not held.




EXECUTEAUTH
CHAR(1)


Indicates whether grantee holds EXECUTE privilege on the package: 

  Y=Privilege is held.
  N=Privilege is not held.









SYSCAT.PACKAGEDEP





Contains a row for each dependency that packages have on indexes, tables,
views, functions, and aliases.



Table 64. SYSCAT.PACKAGEDEP Catalog View







Column Name





Data Type





Nullable





Description




PKGSCHEMA
CHAR(8)


Name of the package.

PKGNAME
CHAR(8)





BINDER





CHAR(8)





Yes





Binder of the package.






BTYPE





CHAR(1)









Type of object BNAME:


 

  A=alias
  F=function-instance
  I=index
  T=table
  V=view




BSCHEMA
CHAR(8)


Qualified name of an object on which the package is dependent.

BNAME
VARCHAR(18)





TABAUTH





SMALLINT





Yes





If BTYPE is T(table) or V(view),
encodes the privileges that are
required by this package (Select, Insert, Delete, Update).






Note:

    

  1. When a depended-on function-instance is dropped, the package is placed
into an "inoperative" state from which it must be explicitly rebound.
When any other depended-on object is dropped, the package is placed into an
"invalid" state from which the system will attempt to rebind it
automatically when a package is first referenced.











SYSCAT.PACKAGES





Contains a row for each package that has been created by binding an
application program.



Table 65. SYSCAT.PACKAGES Catalog View





Column Name
Data Type
Nullable
Description


PKGSCHEMA
CHAR(8)


Name of the package.

PKGNAME
CHAR(8)



BOUNDBY
CHAR(8)


Authorization ID of the binder of the package.


DEFINER
CHAR(8)


Userid under which package was bound.


DEFAULT_SCHEMA
CHAR(8)


Default schema name used for unqualified names in static SQL
statements.


VALID
CHAR(1)




  Y=Valid
  N=Not valid
  X=Package is inoperative because some
    function instance that it depends on
    has been dropped.  Explicit rebind
    is needed. See Note 1 on


 "SYSCAT.PACKAGEDEP"


UNIQUE_ID
CHAR(8)


Internal date and time information indicating when the package was first
created.


TOTAL_SECT
SMALLINT


Total number of sections in the package.


FORMAT
CHAR(1)


Date and time format associated with the package: 

  0=Format associated with country code
     of the database
  1=USA date and time
  2=EUR date, EUR time
  3=ISO date, ISO time.
  4=JIS date, JIS time.
  5=LOCAL date, LOCAL time.




ISOLATION
CHAR(2)
Yes
Isolation level: 

  RR=Repeatable read
  RS=Read stability
  CS=Cursor stability
  UR=Uncommitted read.




BLOCKING
CHAR(1)
Yes
Cursor blocking option: 

  N=No blocking
  U=Block unambiguous cursors
  B=Block all cursors




INSERT_BUF
CHAR(1)


Insert option used during bind: 

  Y=Inserts are buffered
  N=Inserts are not buffered




LANG_LEVEL
CHAR(1)
Yes
LANGLEVEL value used during BIND: 

  0=SAA1
  1=SQL92E or MIA




FUNC_PATH
VARCHAR(254)


The function path used by the last BIND command for this package. This is
used as the default path for REBIND. SYSIBM for pre-Version 2 packages.


QUERYOPT
INTEGER


Optimization class under which this package was bound. Used for rebind.
The classes are: 0, 1, 3, 5 and 9. .


EXPLAIN_LEVEL
CHAR(1)


Indicates whether Explain was requested using the EXPLAIN or EXPLSNAP
bind option. 

  Blank=No Explain requested
  P=Plan Selection level




EXPLAIN_MODE
CHAR(1)


Value of EXPLAIN bind option: 

  Y=Yes (static)
  N=No
  A=All (static and dynamic)




EXPLAIN_SNAPSHOT
CHAR(1)


Value of EXPLSNAP bind option: 

  Y=Yes (static)
  N=No
  A=All (static and dynamic)




SQLWARN
CHAR(1)


Are positive SQLCODES resulting from dynamic SQL statements returned to
the application? 

  Y=Yes
  N=No, they are suppressed




SQLMATHWARN
CHAR(1)


Value of database configuration parameter DFT_SQLMATHWARN at time of
bind. Are arithmetic errors and retrieval conversion errors in static SQL
statements handled as nulls with a warning? 

  Y=Yes
  N=No, they are suppressed




EXPLICIT_BIND_TIME
TIMESTAMP


The time at which this package was last explicitly bound or rebound. When
the package is implicitly rebound, no function instance will be selected that
was created later than this time.


LAST_BIND_TIME
TIMESTAMP


Time at which the package last explicitly or implicitly bound or rebound.


CODEPAGE
SMALLINT


Application codepage at bind time (-1 if not known).


DEGREE
CHAR(5)


Indicates the limit on intra-partition parallelism (as a bind option)
when package was bound. 

   1 = No intra-partition parallelism.
   2 - 32767 = Degree of intra-partition parallelism.
   ANY = Degree was determined by the database manager.




MULTINODE_PLANS
CHAR(1)




Y =Package was bound in a 
multiple partition environment.
N =Package was bound in a  single partition environment.




INTRA_PARALLEL
CHAR(1)


Indicates the use of intra-partition parallelism by static SQL statements
within the package. 

Y     = one or more static SQL statement in package 
       uses intra-partition parallelism.
N     = no static SQL statement in package uses 
       intra-partition parallelism.
F     = one or more static SQL statement in package can 
       use intra-partition parallelism; this parallelism has been 
       disabled for use on a system that is not configured for 
       intra-partition parallelism.




REMARKS
VARCHAR(254)
Yes
User-supplied comment, or null.





SYSCAT.PARTITIONMAPS





Contains a row for each partitioning map that is used to distribute the
rows of tables among the partitions in a nodegroup, based on hashing the tables partitioning key.



Table 66. SYSCAT.PARTITIONMAPS Catalog View





Column Name
Data Type
Nullable
Description


PMAP_ID
SMALLINT


Identifier of the partitioning map.


PARTITIONMAP
LONG VARCHAR FOR BIT DATA


The actual partitioning map, a vector of 4096 two-byte integers for a
multiple node nodegroup. For a single node nodegroup, there is one entry
denoting the partition (or node) number of the single node.





SYSCAT.PROCEDURES





Contains a row for each stored procedure that is created.



Table 67. SYSCAT.PROCEDURES Catalog View





Column Name
Data Type
Nullable
Description


PROCSCHEMA
CHAR(8)


Qualified procedure name.

PROCNAME
VARCHAR(18)



SPECIFICNAME
VARCHAR(18)


The name of the procedure instance (may be system generated).


PROCEDURE_ID
INTEGER


Internal ID of stored procedure.


DEFINER
CHAR(8)


Authorization of the procedure definer.


PARM_COUNT
SMALLINT


Number of procedure parameters.


PARM_SIGNATURE
VARCHAR(180) FOR BIT DATA


Concatenation of up to 90 parameter types, in internal format. Zero
length if procedure takes no parameters.


ORIGIN
CHAR(1)


Always 'E' = User defined, external


CREATE_TIME
TIMESTAMP


Timestamp of procedure registration.


DETERMINISTIC
CHAR(1)




  Y=Results are deterministic.
  N=Results are not deterministic.




FENCED
CHAR(1)




  Y=Fenced
  N=Not Fenced




NULLCALL
CHAR(1)


Always Y=NULLCALL


LANGUAGE
CHAR(8)


Implementation language of procedure body. Possible values are C and
JAVA.


IMPLEMENTATION
VARCHAR(254)
Yes
Identifies the path/module/function or class/method that implements the
procedure.


PARM_STYLE
CHAR(8)




  DB2DARI=Language is C
  DB2GENRL=Language is Java




RESULT_SETS
SMALLINT


Estimated upper limit of returned result sets.


REMARKS
VARCHAR(254)
Yes
User supplied comment, or null.





SYSCAT.PROCPARMS





Contains a row for each parameter of a stored procedure.



Table 68. SYSCAT.PROCPARMS Catalog View





Column Name
Data Type
Nullable
Description


PROCSCHEMA
CHAR(8)


Qualified procedure name.

PROCNAME
VARCHAR(18)



SPECIFICNAME
VARCHAR(18)


The name of the procedure instance (may be system generated).


ORDINAL
SMALLINT


The parameter's numerical position within the procedure signature.


PARMNAME
VARCHAR(18)


Parameter name.


TYPESCHEMA
CHAR(8)


Qualified name of data type of the parameter.

TYPENAME
VARCHAR(18)



LENGTH
INTEGER


Length of the parameter.


SCALE
SMALLINT


Scale of the parameter.


CODEPAGE
SMALLINT


Code page of parameter. 0 denotes either not applicable or a parameter
for character data declared with the FOR BIT DATA attribute.


PARM_MODE
VARCHAR(5)


IN=Input, OUT=Output, INOUT=Input/output


AS_LOCATOR
CHAR(1)


Always 'N'







SYSCAT.REFERENCES





Contains a row for each defined referential constraint.



Table 69. SYSCAT.REFERENCES Catalog View





Column Name
Data Type
Nullable
Description


CONSTNAME
VARCHAR(18)


Name of constraint.


TABSCHEMA
CHAR(8)


Qualified name of the constraint.

TABNAME
VARCHAR(18)



DEFINER
CHAR(8)


User who created the constraint.


REFKEYNAME
VARCHAR(18)


Name of parent key.


REFTABSCHEMA
CHAR(8)


Name of the parent table.

REFTABNAME
VARCHAR(18)



COLCOUNT
SMALLINT


Number of columns in the foreign key.


DELETERULE
CHAR(1)


Delete rule: 

  A=NO ACTION
  C=CASCADE
  N=SET NULL
  R=RESTRICT




UPDATERULE
CHAR(1)


Update rule: 

  A=NO ACTION
  R=RESTRICT




CREATE_TIME
TIMESTAMP


The timestamp when the referential constraint was defined.


FK_COLNAMES
VARCHAR(320)


List of foreign key column names.


PK_COLNAMES
VARCHAR(320)


List of parent key column names.




Note:

    

  1. The SYSCAT.REFERENCES view is based on the SYSIBM.SYSRELS
table from Version 1.









SYSCAT.SCHEMAAUTH





Contains one or more rows for each user or group who is granted a privilege
on a particular schema in the database. All schema privileges for a single
schema granted by a specific grantor to a specific grantee appear in a single
row.



Table 70. SYSCAT.SCHEMAAUTH Catalog View





Column Name
Data Type
Nullable
Description


GRANTOR
CHAR(8)


Authorization ID of the user who granted the privileges or SYSIBM.


GRANTEE
CHAR(8)


Authorization ID of the user or group who holds the privileges.


GRANTEETYPE
CHAR(1)






U=Grantee is an individual user

G=Grantee is a group




SCHEMANAME
CHAR(8)


Name of the schema.


ALTERINAUTH
CHAR(1)


Indicates whether grantee holds ALTERIN privilege on the schema: 



Y=Privilege is held

G=Privilege is held and grantable

N=Privilege is not held.




CREATEINAUTH
CHAR(1)


Indicates whether grantee holds CREATEIN privilege on the schema: 



Y=Privilege is held

G=Privilege is held and grantable

N=Privilege is not held.




DROPINAUTH
CHAR(1)


Indicates whether grantee holds DROPIN privilege on the schema: 



Y=Privilege is held

G=Privilege is held and grantable

N=Privilege is not held.







SYSCAT.SCHEMATA





Contains a row for each schema.



Table 71. SYSCAT.SCHEMATA Catalog View





Column Name
Data Type
Nullable
Description


SCHEMANAME
CHAR(8)


Name of the schema.


OWNER
CHAR(8)


Authorization id of the schema. The value for implicitly created schemas
is SYSIBM.


DEFINER
CHAR(8)


User who created the schema.


CREATE_TIME
TIMESTAMP


Timstamp indicating when the object was created.


REMARKS
VARCHAR(254)
Yes
User-provided comment.







SYSCAT.STATEMENTS





Contains one or more rows for each SQL statement in each package in the
database.



Table 72. SYSCAT.STATEMENTS Catalog View





Column Name
Data Type
Nullable
Description


PKGSCHEMA
CHAR(8)


Name of the package.

PKGNAME
CHAR(8)



STMTNO
SMALLINT


Line number of the SQL statement in the source module of the application
program.


SECTNO
SMALLINT


Number of the package section containing the SQL statement.


SEQNO
SMALLINT


Sequence number of this row; the first portion of the SQL text is
stored on row one, and successive rows have increasing values for SEQNO.


TEXT
VARCHAR (3600)


Text or portion of the text of the SQL statement.







SYSCAT.TABAUTH





Contains one or more rows for each user or group who is granted a privilege
on a particular table or view in the database. All the table privileges for a
single table or view granted by a specific grantor to a specific grantee
appear in a single row.



Table 73. SYSCAT.TABAUTH Catalog View





Column Name
Data Type
Nullable
Description


GRANTOR
CHAR(8)


Authorization ID of the user who granted the privileges or SYSIBM.


GRANTEE
CHAR(8)


Authorization ID of the user or group who holds the privileges.


GRANTEETYPE
CHAR(1)




  U=Grantee is an individual user
  G=Grantee is a group




TABSCHEMA
CHAR(8)


Qualified name of the table or view.

TABNAME
VARCHAR(18)



CONTROLAUTH
CHAR(1)


Indicates whether grantee holds CONTROL privilege on the table or
view: 

  Y=Privilege is held.
  N=Privilege is not held.




ALTERAUTH
CHAR(1)


Indicates whether grantee holds ALTER privilege on the table: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.




DELETEAUTH
CHAR(1)


Indicates whether grantee holds DELETE privilege on the table or
view: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.




INDEXAUTH
CHAR(1)


Indicates whether grantee holds INDEX privilege on the table: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.




INSERTAUTH
CHAR(1)


Indicates whether grantee holds INSERT privilege on the table or
view: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.




SELECTAUTH
CHAR(1)


Indicates whether grantee holds SELECT privilege on the table or
view: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.




REFAUTH
CHAR(1)


Indicates whether grantee holds REFERENCE privilege on the table or
view: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.




UPDATEAUTH
CHAR(1)


Indicates whether grantee holds UPDATE privilege on the table or
view: 

  Y=Privilege is held.
  N=Privilege is not held.
  G=Privilege is held and grantable.









SYSCAT.TABCONST



Each row represents a table constraint of type CHECK, 
UNIQUE, PRIMARY KEY, or FOREIGN KEY.



Table 74. SYSCAT.TABCONST Catalog View







Column Name





Data Type





Nullable





Description






CONSTNAME





VARCHAR(18)









Name of the constraint (unique within a table).




TABSCHEMA
CHAR(8)


Qualified name of the table to which this constraint applies.

TABNAME
VARCHAR(18)





DEFINER





CHAR(8)









Authorization ID under which the constraint was defined.






TYPE





CHAR(1)









Indicates the constraint type:


 

  K=CHECK
  P=PRIMARY KEY
  F=FOREIGN KEY
  U=UNIQUE 






REMARKS





VARCHAR(254)





Yes





User-supplied comment, or null.









SYSCAT.TABLES





Contains one row for each table, view, or alias that is created. All of the
catalog tables and views have entries in the SYSCAT.TABLES catalog
view.



Table 75. SYSCAT.TABLES Catalog View





Column Name
Data Type
Nullable
Description


TABSCHEMA
CHAR(8)


Qualified name of the table, view, or alias.

TABNAME
VARCHAR(18)



DEFINER
CHAR(8)


User who created the table, view, or alias.


TYPE
CHAR(1)


The type of object: 

  A=Alias
  T=Table
  V=View




STATUS
CHAR(1)


The type of object: 

  N=Normal table, view or alias
  C=Check pending on table
  X=Inoperative view




BASE_TABSCHEMA
CHAR(8)
Yes
If TYPE=A, these columns identify the table, view, or alias that is
referenced by this alias; otherwise they are null.

BASE_TABNAME
VARCHAR(18)
Yes


CREATE_TIME
TIMESTAMP


The timestamp indicating when the object was created.


STATS_TIME
TIMESTAMP
Yes
Last time when any change was made to recorded statistics for this table.
Null if no statistics available.


COLCOUNT
SMALLINT


Number of columns in table.


TABLEID
SMALLINT


Internal table identifier.


TBSPACEID
SMALLINT


Internal identifier of primary table space for this table.


CARD
INTEGER


Total number of rows in the table; -1 if statistics are not
gathered or the row describes a view or alias.


NPAGES
INTEGER


Total number of pages on which the rows of the table exist; -1
if statistics are not gathered or the row describes a view or alias.


FPAGES
INTEGER


Total number of pages; -1 if statistics are not gathered or
the row describes a view or alias.


OVERFLOW
INTEGER


Total number of overflow records in the table; -1 if
statistics are not gathered or the row describes a view or alias.


TBSPACE
VARCHAR(18)
Yes
Name of primary table space for the table. If no other table space is
specified, all parts of the table are stored in this table space. Null for
aliases and views.


INDEX_TBSPACE
VARCHAR(18)
Yes
Name of table space that holds all indexes created on this table. Null
for aliases and views, or if the INDEX IN clause was omitted or specified with
the same value as the IN clause of the CREATE TABLE statement.


LONG_TBSPACE
VARCHAR(18)
Yes
Name of table space that holds all long data (LONG or LOB column types)
for this table. Null for aliases and views, or if the LONG IN clause was
omitted or specified with the same value as the IN clause of the CREATE TABLE
statement.


PARENTS
SMALLINT
Yes
Number of parent tables of this table (the number of referential
constraints in which this table is a dependent).


CHILDREN
SMALLINT
Yes
Number of dependent tables of this table (the number of referential
constraints in which this table is a parent).


SELFREFS
SMALLINT
Yes
Number of self-referencing referential constraints for this table (the
number of referential constraints in which this table is both a parent and a
dependent).


KEYCOLUMNS
SMALLINT
Yes
Number of columns in the primary key of the table.


KEYINDEXID
SMALLINT
Yes
Index ID of the primary index. This field is null or 0 if there is no
primary key.


KEYUNIQUE
SMALLINT


Number of unique constraints (other than primary key) defined on this
table.


CHECKCOUNT
SMALLINT


Number of check constraints defined on this table.


DATACAPTURE
CHAR(1)




  Y=Table participates in data
         replication
  N=Does not participate




CONST_CHECKED
CHAR(32)


Byte 1 represents foreign key constraints. Byte 2 represents check
constraints. Other bytes are reserved. Encodes constraint information on
checking. Values: 

  Y=Checked by system
  U=Checked by user
  N=Not checked (pending)




PMAP_ID
SMALLINT
Yes
Identifier of the partitioning map used by this table. Null for aliases
and views.


PARTITION_MODE
CHAR(1)


Mode used for tables in a partitioned database. 



H

hash on the partitioning key



Blank for aliases, views and tables in single partition nodegroups with no
partitioning key defined.


LOG_ATTRIBUTE
CHAR(1)




  0=Default logging
  N=Table created not
         logged initially




PCTFREE
SMALLINT


Percentage of each page to be reserved for future inserts. Can be changed
by ALTER TABLE.


REMARKS
VARCHAR(254)
Yes
User-provided comment.







SYSCAT.TABLESPACES





Contains a row for each table space.



Table 76. SYSCAT.TABLESPACES Catalog View





Column Name
Data Type
Nullable
Description


TBSPACE
VARCHAR(18)


Name of table space.


DEFINER
CHAR(8)


Authorization ID of table space definer.


CREATE_TIME
TIMESTAMP


Creation time of table space.


TBSPACEID
INTEGER


Internal table space identifier.


TBSPACETYPE
CHAR(1)


The type of the table space: 

  S=System managed space
  D=Database managed space




DATATYPE
CHAR(1)


Type of data that can be stored: 

  A=All types of permanent data
  L=Long data only
  T=Temporary tables only




EXTENTSIZE
INTEGER


Size of extent, in 4K pages. This many pages are written to one container
in the table space before switching to the next container.


PREFETCHSIZE
INTEGER


Number of 4K pages to be read when prefetch is performed.


OVERHEAD
DOUBLE


Controller overhead and disk seek and latency time in milliseconds.


TRANSFERRATE
DOUBLE


Time to read one 4K page into the buffer.


PAGESIZE
INTEGER


Size (in bytes) of pages in the table space.


NGNAME
VARCHAR(18)


Name of the nodegroup for the table space.


BUFFERPOOLID
INTEGER


ID of buffer pool used by this tablespace (1 indicates default buffer
pool).


REMARKS
VARCHAR(254)
Yes
User-provided comment.







SYSCAT.TRIGDEP





Contains a row for every dependency of a trigger on some other object.



Table 77. SYSCAT.TRIGDEP Catalog View





Column Name
Data Type
Nullable
Description


TRIGSCHEMA
CHAR(8)


Qualified name of the trigger.

TRIGNAME
VARCHAR(18)



BTYPE
CHAR(1)


Type of object that is depended on. 

  A=Alias
  F=Function instance
  T=Table
  V=View




BSCHEMA
CHAR(8)


Qualified name of object depended on by a trigger.

BNAME
VARCHAR(18)



TABAUTH
SMALLINT
Yes
If BTYPE=T or V, encodes the privileges on the table or view that are
required by this trigger; otherwise null.







SYSCAT.TRIGGERS





Contains one row for each trigger.



Table 78. SYSCAT.TRIGGERS Catalog View





Column Name
Data Type
Nullable
Description


TRIGSCHEMA
CHAR(8)


Qualified name of the trigger.

TRIGNAME
VARCHAR(18)



DEFINER
CHAR(8)


Authorization ID under which the trigger was defined.


TABSCHEMA
CHAR(8)


Qualified name of the table to which this trigger applies.

TABNAME
VARCHAR(18)



TRIGTIME
CHAR(1)


Time when triggered actions are applied to the base table, relative to
the event that fired the trigger: 

  B=Trigger applied before event
  A=Trigger applied after event




TRIGEVENT
CHAR(1)


Event that fires the trigger. 

  I=Insert
  D=Delete
  U=Update




GRANULARITY
CHAR(1)


Trigger is executed once per: 

  S=Statement
  R=Row




VALID
CHAR(1)




  Y=Trigger is valid
  X=Trigger is inoperative;
    must be re-created.




TEXT
CLOB(32K)


The full text of the CREATE TRIGGER statement, exactly as typed.


CREATE_TIME
TIMESTAMP


Time at which the trigger was defined. Used in resolving functions and
types.


FUNC_PATH
VARCHAR(254)


Function path at the time the trigger was defined. Used in resolving
functions and types.


REMARKS
VARCHAR(254)
Yes
User-supplied comment, or null.







SYSCAT.VIEWDEP





Contains a row for every dependency of a view on some other object. Also
encodes how privileges on this view depend on privileges on underlying tables
and views.



Table 79. SYSCAT.VIEWDEP Catalog View





Column Name
Data Type
Nullable
Description


VIEWSCHEMA
CHAR(8)


Name of the view.

VIEWNAME
VARCHAR(18)



DEFINER
CHAR(8)
Yes
Authorization ID of the creator of the view.


BTYPE
CHAR(1)


Type of object that the specified view has a dependency on. 

  T=Table
  V=View
  F=Function instance
  A=Alias




BSCHEMA
CHAR(8)


Qualified name of object depended on by the view.

BNAME
VARCHAR(18)



TABAUTH
SMALLINT
Yes
Encodes the privileges on the underlying table or view that this view
depends on. Otherwise null.







SYSCAT.VIEWS





Contains one or more rows for each view that is created.



Table 80. SYSCAT.VIEWS Catalog View





Column Name
Data Type
Nullable
Description


VIEWSCHEMA
CHAR(8)


Name of the view.

VIEWNAME
VARCHAR(18)



DEFINER
CHAR(8)


Authorization ID of the creator of the view.


SEQNO
SMALLINT


Sequence number of this row; the first portion of the view is on row
one, and successive rows have increasing values of SEQNO.


VIEWCHECK
CHAR(1)


States the type of view checking: 

  N=No check option
  L=Local check option
  C=Cascaded check option




READONLY
CHAR(1)




  Y=View is read-only because of its definition.
  N=View is not read-only.




VALID
CHAR(1)




  Y=View definition is valid.
  X=View definition is inoperative;
    must be re-created.




FUNC_PATH
VARCHAR(254)


The function path of the view creator at the time the view was defined.
When the view is used in data manipulation statements, this path must be used
to resolve function calls in the view. SYSIBM for views created before Version
2.


TEXT
VARCHAR(3600)


Text or portion of the text of the CREATE VIEW statement.









SYSSTAT.COLDIST






Each row describes the Nth-most-frequent value or Nth quantile value of
some column.



Table 81. SYSSTAT.COLDIST Catalog View





Column Name
Data Type
Nullable
Description
Updatable


TABSCHEMA
CHAR(8)


Qualified name of the table to which this entry applies.


TABNAME
VARCHAR(18)





COLNAME
VARCHAR(18)


Name of the column to which this entry applies.



TYPE
CHAR(1)


Type of statistic collected: 

  F=Frequency (most frequent value)
  Q=Quantile value





SEQNO
SMALLINT


If TYPE=F, then N in this column identifies the Nth most frequent
value. If TYPE=Q, then N in this column identifies the Nth quantile value.



COLVALUE
VARCHAR(33)
Yes
The data value, as a character literal or a null value. 

This column can be updated with a valid representation of the value
appropriate to the column that the statistic is associated with. If null is
the required frequency value, the column should be set to NULL.

Yes


VALCOUNT
INTEGER


If TYPE=F, then VALCOUNT is the number of occurrences of COLVALUE in
the column. If TYPE=Q, then VALCOUNT is the number of rows whose value is
less than or equal to COLVALUE. 

This column can be only updated with the following values: 

    

  • >= 0 (zero)


Yes


DISTCOUNT
INTEGER


If TYPE=q, this column records the number of distinct values
that are less than or equal to COLVALUE (null iv unavailable.) the number of
rows whose value is less than or equal to COLVALUE.
Yes







SYSSTAT.COLUMNS





Contains one row for each column that is defined for the specified table.



Table 82. SYSSTAT.COLUMNS Catalog View





Column Name
Data Type
Nullable
Description
Updatable


TABSCHEMA
CHAR(8)


Qualified name of the table that contains the column.


TABNAME
VARCHAR(18)





COLNAME
VARCHAR(18)


Column name.



COLCARD
INTEGER


Number of distinct values in the column; -1 if statistics are
not gathered. 

For any column, COLCARD cannot have a value higher than the cardinality of
the table containing that column. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


HIGH2KEY
VARCHAR(33)


Second highest value of the column. This field is empty if statistics are
not gathered. 

This column can be updated with a valid representation of the value
appropriate to the column that the statistic is associated with. 

LOWKEY2 should not be greater than HIGH2KEY.

Yes


LOW2KEY
VARCHAR(33)


Second lowest value of the column. Empty if statistics not gathered. 

This column can be updated with a valid representation of the value
appropriate to the column that the statistic is associated with.

Yes


AVGCOLLEN
INTEGER


Average column length. -1 if a long field or LOB, or statistics have not
been collected. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes







SYSSTAT.FUNCTIONS





Contains a row for each user-defined function (scalar or aggregate). Does
not include built-in functions.



Table 83. SYSSTAT.FUNCTIONS Catalog View





Column Name
Data Type
Nullable
Description
Updatable


FUNCSCHEMA
CHAR(8)


Qualified function name.


FUNCNAME
VARCHAR(18)





SPECIFICNAME
VARCHAR(18)


Function specific (instance) name.



IOS_PER_INVOC
DOUBLE


Estimated number of I/Os per invocation; -1 if not known (0 default). 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


INSTS_PER_INVOC
DOUBLE


Estimated number of instructions per invocation; -1 if not known (450
default). 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


IOS_PER_ARGBYTE
DOUBLE


Estimated number of I/O's per input argument byte; -1 if not known
(0 default). 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


INSTS_PER_ARGBYTE
DOUBLE


Estimated number of instructions per input argument byte; -1 if not known
(0 default). 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


PERCENT_ARGBYTES
SMALLINT


Estimated average percent of input argument bytes that the function will
actually read; -1 if not known (100 default). 

This column can only be updated with the following values: 

    

  • -1 or between 100 and 0 (zero)


Yes


INITIAL_IOS
DOUBLE


Estimated number of I/O's performed the first/last time the function
is invoked; -1 if not known (0 default). 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


INITIAL_INSTS
DOUBLE


Estimated number of instructions executed the first/last time the
function is invoked; -1 if not known (0 default). 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


CARDINALITY
INTEGER


The predicted cardinality of a table function. -1 if not known, or
if function is not a table function.
Yes







SYSSTAT.INDEXES





Contains one row for each index that is defined for a table.



Table 84. SYSSTAT.INDEXES Catalog View





Column Name
Data Type
Nullable
Description
Updatable


INDSCHEMA
CHAR(8)


Qualified name of the index.


INDNAME
VARCHAR(18)





NLEAF
INTEGER


Number of leaf pages; -1 if statistics are not gathered. 

This column can only be updated with the following values: 

    

  • -1 or > 0 (zero)


Yes


NLEVELS
SMALLINT


Number of index levels; -1 if statistics are not gathered. 

This column can only be updated with the following values: 

    

  • -1 or > 0 (zero)


Yes


FIRSTKEYCARD
INTEGER


Number of distinct first key values; -1 if statistics are not
gathered. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


FIRST2KEYCARD
INTEGER


Number of distinct keys using the first two columns of the index
(-1 if no statistics or inapplicable) 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


FIRST3KEYCARD
INTEGER


Number of distinct keys using the first three columns of the index
(-1 if no statistics or inapplicable) 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


FIRST4KEYCARD
INTEGER


Number of distinct keys using the first four columns of the index
(-1 if no statistics or inapplicable) 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


FULLKEYCARD
INTEGER


Number of distinct full key values; -1 if statistics are not
gathered. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


CLUSTERRATIO
SMALLINT


This is used by the optimizer. It indicates the degree of data clustering
with the index; -1 if statistics are not gathered or if detailed
index statistics have been gathered. 

This column can only be updated with the following values: 

    

  • -1 or between 0 and 100


Yes


CLUSTERFACTOR
DOUBLE


This is used by the optimizer. It is a finer measurement of degree of
clustering, or -1 if detailed index statistics have not been gathered. 

This column can only be updated with the following values: 

    

  • -1 or between 0 and 1


Yes


SEQUENTIAL_PAGES
INTEGER


Number of leaf pages located on disk in index key order with few or no
large gaps between them. (-1 if no statistics are available.) 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


DENSITY
INTEGER


Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages
occupied by the index, expressed as a percent (integer between 0 and 100,
-1 if no statistics are available.) 

This column can only be updated with the following values: 

    

  • -1 or between 0 and 100


Yes


PAGE_FETCH_PAIRS
VARCHAR(254)


A list of pairs of integers, represented in character form. Each pair
represents the number of pages in a hypothetical buffer, and the number of
page fetches required to scan the index using that hypothetical buffer.
(Zero-length string if no data available.) 

This column can be updated with the following input values: 

    

  • The pair delimiter and pair separator characters are the only non-numeric
characters accepted

  • Blanks are the only characters recognized as a pair delimiter and pair
separator

  • Each number entry must have an accompanying partner number entry with the
two being separated by the pair separator character

  • Each pair must be separated from any other pairs by the pair delimiter
character

  • Each expected number entry must between 0-9 (only positive values)


Yes







SYSSTAT.TABLES





Contains one row for each base table. Views or aliases are,
therefore, not included.



Table 85. SYSSTAT.TABLES Catalog View





Column Name
Data Type
Nullable
Description
Updatable


TABSCHEMA
CHAR(8)


Qualified name of the table.


TABNAME
VARCHAR(18)





CARD
INTEGER


Total number of rows in the table; -1 if statistics are not
gathered. 

An update to CARD for a table should not attempt to assign it a value less
than the COLCARD value of any of the columns in that table. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


NPAGES
INTEGER


Total number of pages on which the rows of the table exist; -1
if statistics are not gathered. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


FPAGES
INTEGER


Total number of pages in the file; -1 if statistics are not
gathered. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes


OVERFLOW
INTEGER


Total number of overflow records in the table; -1 if
statistics are not gathered. 

This column can only be updated with the following values: 

    

  • -1 or >= 0 (zero)


Yes





[ Top of Page | Previous Page | Next Page | Table of Contents | Index ] 


[ DB2 List of Books |
Search the DB2 Books ]