DB2 Server for VSE & VM: SQL Reference

Appendix C. DB2 Server for VSE & VM Catalog

This appendix is intended to help you to use the catalog for your database manager. It contains Product-Sensitive Programming Interface and Associated Guidance Information.

The DB2 Server for VSE & VM database manager automatically maintains information about the database in a set of tables called the catalog. The catalog tables are created by the database manager during database generation. They describe tables, columns, indexes, keys, packages, authorities, and other objects in the database. Data in the catalog tables is available to authorized users through normal SQL query facilities; however, the catalog is primarily intended for use by the database manager.

During database generation, the catalog is defined as normal tables with PUBLIC read authorization. After database generation, a user with DBA authority can revoke the select privilege from PUBLIC. Usually all users are allowed to access the catalog, so you can use SQL statements to retrieve information in the catalog. For example, this SQL statement finds what column names in table SALARY begin with the letter 'D':

   SELECT CNAME FROM SYSTEM.SYSCOLUMNS
      WHERE TNAME = 'SALARY'
      AND CNAME LIKE 'D%'

SYSTEM is the owner of all catalog tables except SYSLANGUAGE (which is owned by SQLDBA). You must qualify all references to catalog tables with the owner name, unless you have a synonym defined.

After database generation, the only information in the tables not available to everyone is password information. You must have DBA authority to access the catalog table that contains passwords (SYSUSERAUTH). A view, called SYSUSERLIST, is defined on SYSUSERAUTH when the catalog tables are created. The owner of the view is SQLDBA, so you must refer to the view as SQLDBA.SYSUSERLIST. This view is accessible to all users and contains all the columns of SYSUSERAUTH except the passwords. If you do not have DBA authority, you must query the view (SYSUSERLIST) instead of the underlying table (SYSUSERAUTH).

Some of the information in the catalog is of little interest to most users. Statistics maintained in the catalog, for example, are used by the database manager to determine optimal access paths. These statistics may be quite meaningless to you. If you wish, you can define views on the catalog tables containing only columns that are meaningful to you.

Some of the information in the catalog is maintained in a form for internal use by the database manager and is provided as additional guidance on database administration tasks. Two special data types are used: DBAINT and DBAHW. These appear externally like INTEGER and SMALLINT data. However, DBAINT and DBAHW do not sort as expected when they contain negative values. Consequently, queries that use ORDER BY, GROUP BY, or predicates that involve > or < operations on these values may not work as expected.

The database manager updates its catalog during normal operation in response to SQL data definition and control statements. It also updates its catalog when programs are preprocessed.

You can create and maintain your own installation-dependent catalog tables using SQL statements.

Note:

Data in the catalog tables is available to authorized users through normal SQL query facilities; however, the catalog is primarily intended for use by the database manager, and is therefore subject to change.

"Roadmap" to Catalog

Updateable Columns

Only someone with DBA authority may enter UPDATE, INSERT and DELETE statements against catalog tables. Furthermore, only the following columns may be altered. It is not possible to add columns to the catalog.

Catalog Column	Update	Insert	Delete
SYSACCESS
VALID 1	X
SYSCATALOG
CLUSTERTYPE	X
CLUSTERROW	X
AVGROWLEN	X
ROWCOUNT	X
NPAGES	X
PCTPAGES	X
SYSCCSIDS
all columns	X	X	X
SYSCHARSETS
all columns	X	X	X
SYSCOLSTATS
VAL10	X
VAL50	X
VAL90	X
FREQ1VAL	X
FREQ1PCT	X
FREQ2VAL	X
FREQ2PCT	X
SYSCOLUMNS
COLCOUNT	X
HIGH2KEY	X
LOW2KEY	X
AVGCOLLEN	X
COLINFO	X
SUBTYPE 2	X
SYSDBSPACES
NACTIVE	X
NPAGES 3	X
SYSDROP
all columns			X
SYSINDEXES
CLUSTER	X
KEYLEN	X
FIRSTKEYCOUNT	X
FULLKEYCOUNT	X
NLEAF	X
NLEVELS	X
CLUSTERRATIO	X
SYSLANGUAGE
all columns	X	X	X
SYSOPTIONS
all columns	X	X	X
SYSSTRINGS
all columns	X	X	X

Notes:

1. It is advisable to enter a REBIND command, rather than updating VALID to force dynamic re-preprocessing,

2. Updating this field is only effective if the corresponding CCSID field value for the row is null. Upon successful update of the SUBTYPE value, all packages which reference the column whose SUBTYPE has been updated must be re-preprocessed.

3. Caution:
   Changing NPAGES makes the dbspace appear to be a different size without actually changing it. NPAGES should not be changed in a production environment, to do so may cause errors to occur. It is intended for testing purposes only.

 SYS ACCESS

Packages are stored in tables. The database manager uses SYSACCESS to record information about the tables in which packages are stored. For package tables that are in use, SYSACCESS records information about:

• Packages created by the preprocessors or by a CREATE PACKAGE statement.
• View definitions (views are stored as packages).

When a package table is not in use, SYSACCESS indicates whether the table is available or unavailable.

The columns in SYSACCESS are:

Column Name	Data Type	Description and Comments
TNAME	VARCHAR(18) NOT NULL	When the package table is in use, TNAME is either the name of the package or the name of a view. A view definition is stored as a package; the name of the package is the name of the view. The TABTYPE field indicates whether this row describes a real package or a view. When the package table is unused, TNAME is either '!0x  AVAILABLE' or '¢0x  UNAVAILABLE' to indicate whether the table is available or unavailable. A package table is available when it is unused and the DBSPACE is not full. A package table is unavailable when it is unused and the DBSPACE is full. A package table may also be marked as unavailable when a package is dropped from a DBSPACE that was previously marked full. Such package tables are marked as available the next time the database manager pre-allocates packages. The x is a number from one to five that is used internally.
CREATOR	CHAR(8) NOT NULL	The owner of the package or view who either preprocessed the program associated with this package, explicitly created the package (by CREATE PACKAGE), or created this view. If the package table is unused, CREATOR is a non-readable unique value that is based on the system clock. (The database manager generates this value for unused package tables because TNAME and CREATOR serve as a key for an index on SYSTEM.SYSACCESS.)
DBSPACENO	DBAHW NOT NULL	The number of the DBSPACE that contains this package. (When DBSPACEs are defined by database generation or by ADD DBSPACE processing, the database manager assigns each DBSPACE a number for internal use.)
TABID	DBAHW NOT NULL	Packages are stored as tables. TABID contains the internal identifier of that table. (In the DB2 Server for VSE & VM Diagnosis Guide and Reference manual, this identifier is known as the DBSS RID.)
LINKID	DBAHW NOT NULL	A package may occupy more than one row of the table in which it is stored. The database manager connects these rows in the correct order by a mechanism called a unary link. LINKID is the identifier of that unary link.
FIRSTROW	DBAINT NOT NULL	The internal identifier for the first row of the unary link. (In the DB2 Server for VSE & VM Diagnosis Guide and Reference manual, row identifiers are known as DBSS TIDs.) FIRSTROW is 0 for unused package tables.
TIMESTAMP	CHAR(17) NOT NULL	The date and time when this package was created. The field has the format MM/DD/YY HH:MM:SS. It is updated when the database manager automatically preprocesses the package. (The database manager attempts to preprocess a package when some dependency is lost; for example, when a package tries to use an index that was dropped.) TIMESTAMP is blank if the package table is unused.
VALID	CHAR(1) NOT NULL	The possible values are: Y if the package is valid. N if the package is not valid because: a view, index, table, or DBSPACE has been dropped the application server CHARNAME has been changed and the package or view definition has a dependency on a changed system table. In this case, please refer to the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual for a list of all affected tables. blank if the package table is unused.
TABTYPE	CHAR(1) NOT NULL	The possible values are: X if this row describes a package. V if this row describes a view definition. blank if the package table is unused.
CONSTKN	CHAR(8) FOR BIT DATA	The consistency token for this package. The field is one of: eight blanks If this entry is for a view, the view was created or repreprocessed on an SQL/DS database Version 3 Release 1 or later. If not for a view, CTOKEN(NO) was specified or allowed to default in the preprocessor options. timestamp CTOKEN(YES) was specified in the preprocessor options. null The package or view was migrated from an SQL/DS database prior to Version 3 Release 1.
PLABEL	VARCHAR(30)	The label for this package. The field is one of: thirty blanks If this entry is for a view, the view was created or repreprocessed on an SQL/DS database Version 3 Release 1 or later. If for a package, the LABEL option was not specified in either the preprocessor options or in a CREATE PACKAGE statement. label-text LABEL(label-text) was specified in the preprocessor options or for CREATE PACKAGE. null The package or view was migrated from an SQL/DS database prior to Version 3 Release 1.

 SYS CATALOG

The SYSCATALOG table contains a row for each table or view in the database, including itself and other catalog tables.

The columns in SYSCATALOG are:

Column Name	Data Type	Description and Comments
TNAME	VARCHAR(18) NOT NULL	The name of the table or view being described.
CREATOR	CHAR(8) NOT NULL	The owner of the table or view. (The CREATOR of the catalog is SYSTEM.)
TABLETYPE	CHAR(1) NOT NULL	The possible values are: V if the object is a view. R if the object is a real table.
NCOLS	SMALLINT NOT NULL	The number of columns in the table or view.
REMARKS	VARCHAR(254) NOT NULL	The information from a COMMENT statement entered for the table or view. The remarks are deleted from SYSCATALOG when the table or view is dropped. If the DBCS option is enabled, users can store mixed data (EBCDIC and DBCS) in the REMARKS column.
DBSPACENO	DBAHW NOT NULL	The possible values are: 0 if the object is a view. number if the object is a real table. This number is the internal number of the DBSPACE in which the table is stored. This is the DBSPACE number to which some of the SHOW operator commands refer (such as SHOW DBSPACE).
DBSPACENAME	VARCHAR(18) NOT NULL	The name of the DBSPACE containing the table.
TABID	DBAHW NOT NULL	The possible values are: 0 if the object is a view. number if the object is a real table. This number is the internal identifier of the table. (In the DB2 Server for VSE & VM Diagnosis Guide and Reference manual, the internal identifier is referred to as the DBSS RID.)
CLUSTERTYPE	CHAR(1) NOT NULL	The possible values are: I if the rows are clustered by an index. D if the rows are clustered by default rules. This is its initial value. Internally, the physical placement of rows is determined by an index or (if no index is available) by default rules. The default rules place each new row near the previously inserted row. CLUSTERTYPE is updated by CREATE and DROP INDEX statement on this table. In addition to the above values, the possible values for catalog tables are: L if the rows are clustered by link rules. For certain catalog tables, a direct addressing link is set up to enable faster access to a specific row. A CLUSTERTYPE value of 'L' indicates that rows are clustered in link order. N if the rows are clustered by internal RDS rules.
CLUSTERROW	DBAINT NOT NULL	The possible values are: 0 if the object is a view. This is also an initial value. number if the object is a real table. This number is the highest internal row identifier (DBSS TID) for any row in the table. The database manager uses this value when it is clustering rows by default rules. See Note 1 for update rules on this column.
AVGROWLEN	DBAHW NOT NULL	The average length of the rows in this table, rounded to the nearest integer. This field is set to -1 when the table is created. See Note 1 for update rules on this column.
ROWCOUNT	DBAINT NOT NULL	The total number of rows in this table. This is updated to the following values by CREATE TABLE, UPDATE STATISTICS, and DATALOAD/RELOAD as indicated: -2 When a DATALOAD with COMMITCOUNT option reaches the commit threshold and commits the loaded rows if statistics are collected while data is being loaded. -1 When the table is initially created with CREATE TABLE. >=0 When an UPDATE STATISTICS is performed, or when a CREATE/REORGANIZE INDEX is performed, or when data is loaded using DATALOAD/RELOAD (and update statistics is not set off). The integer is equal to the total number of rows in this table and it is updated only if one of the previously listed operations is performed.
NPAGES	DBAINT NOT NULL	The number of pages on which rows of this table appear. This number is approximate because it does not contain those pages that contain only long fields. Thus, the sum of the NPAGES for all tables in a DBSPACE might be less than NACTIVE in SYSDBSPACES. (NACTIVE is the total number of active data pages in a DBSPACE.) This field is set to -1 when the table is created, and is updated to a non-negative integer according to the same rules as the ROWCOUNT column.
PCTPAGES	DBAHW NOT NULL	The approximate percentage of the total active pages in the DBSPACE that have rows from this table on them. The initial value in this field is -1. Loading, updating or dropping of any table can affect the actual percentage of used pages for all tables in a given DBSPACE and PCTPAGES may not reflect this. The database manager takes this into account and dynamically calculates (but does not update) PCTPAGES whenever it is used. The calculated value is NPAGES/SYSDBSPACES.NACTIVE. See Note 1 for update rules on this column.
NOVERFLOW	DBAINT NOT NULL	The number of rows in this table that have overflowed from their original page in storage to another page. If this number is large, it may be time to reorganize the table by dumping it out of the database and reloading it. See Note 1 for update rules on this column.
LFDTABID	DBAHW NOT NULL	The internal table identification (referred to in the DB2 Server for VSE & VM Diagnosis Guide and Reference manual as the DBSS RID) of a secondary table the database manager uses to store any long fields that exist in this table. The secondary table is transparent to users. This field is zero if the described table has no long fields.
LFDLINK	DBAHW NOT NULL	The rows in the secondary table that contains long field data are linked together by an internal mechanism called a unary link. LFDLINK is the identifier of that unary link. (In the DB2 Server for VSE & VM Diagnosis Guide and Reference manual, the internal identifier is referred to as a DBSS LID.) If the described table contains no long fields, LFDLINK is zero.
LFDDBSPACE	DBAHW NOT NULL	The number of the DBSPACE that contains the long field data table. LFDDBSPACE is zero if there are no long fields in the described table.
TLABEL	VARCHAR(30)	A table label supplied by a user using a LABEL statement. The table labels are deleted from SYSCATALOG when the table or view is dropped. If the DBCS option is enabled, users can store mixed data (EBCDIC and DBCS) in the TLABEL column.
PARENTS	SMALLINT	The number of parent relationships in which the table is a dependent. Can be NULL if migrated from SQL/DS Version 2 Release 1 or earlier.
DEPENDENTS	SMALLINT	The number of dependent relationships in which the table is a parent. Can be NULL if migrated from SQL/DS Version 2 Release 1 or earlier.
INACTIVE	SMALLINT	The number of inactive keys for the table. This includes inactive primary keys, inactive foreign keys, and foreign keys that reference an inactive primary key in another table. Can be NULL if migrated from SQL/DS Version 2 Release 1 or earlier.
DATACAPTURE	CHAR(1)	Records the value of the DATA CAPTURE specification for a table. This value can be NULL if the database was migrated from Version 3 Release 5 or earlier, 'blank' if DATA CAPTURE NONE was specified for the table, or Y if DATA CAPTURE CHANGES was specified.

Note 1:

The value is always updated by an UPDATE STATISTICS statement on this table or by the DATALOAD/RELOAD DBS Utility commands if the collecting of statistics has not been turned off by SET UPDATE STATISTICS OFF.

 SYS CCSIDS

The SYSCCSIDS table contains a row for every CCSID supported by the installation.

Column Name	Data Type	Description and Comments
CCSID	INTEGER NOT NULL	Identifies the CCSIDs supported by the installation. The values in this field identify valid CCSIDs when columns are created by the CREATE TABLE or ALTER TABLE statement. This column is defined with a UNIQUE constraint.
SUBTYPE	CHAR(1) NOT NULL	Identifies the subtype of the CCSID. The possible values are: B for bit data. M for mixed data. S for SBCS data. blank for anything other than non-character.
SBCSID	INTEGER NOT NULL	Identifies the SBCS portion of a mixed CCSID.
DBCSID	INTEGER NOT NULL	Identifies the DBCS portion of a mixed CCSID.
CHARNAME	CHAR(18) NOT NULL	The name of the character set specified by the SQLINIT EXEC (for example, FRENCH, INTERNATIONAL, 937).

More information on CCSIDs can be found in the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

 SYS CHARSETS

The rows in SYSCHARSETS contain information about various EBCDIC character sets. The database manager reads a row from this table during initialization based on the name specified by the CHARNAME parameter of the SQLSTART command.

The database manager uses the character sets to identify valid characters, to fold lowercase characters to uppercase properly, and for the TRANSLATE function.

IBM supplies sample DBS Utility control files that you can use for loading character set information into SYSCHARSETS. Or, you can define your own character sets and have them loaded by someone with DBA authority. For more information on how to define your own character set, see the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

SYSCHARSETS is only for SBCS character sets.

Column Name	Data Type	Description and Comments
NAME	VARCHAR(18) NOT NULL	The name used to identify the character set. NAME is usually the national language name of the character set (for example, FRENCH) and corresponds exactly to the CHARNAME in the SYSCCSIDS catalog table. A CCSID is associated with each name.
CHARCLASS	CHAR(192) NOT NULL FOR BIT DATA	This contains the character classifications for this character set.
CHARTRANS	CHAR(192) NOT NULL FOR BIT DATA	This contains the character translation values for this character set. The character translation values are used for lowercase to uppercase folding.

 SYS COLAUTH

SYSCOLAUTH records grants of the UPDATE privilege on tables and views when the privilege is granted on a column-by-column basis. Each entry in SYSCOLAUTH has a corresponding entry in SYSTABAUTH with a matching timestamp. (SYSTABAUTH records privileges granted on entire tables, but not on individual columns.) A SYSCOLAUTH entry identifies a particular column on which an UPDATE privilege has been granted. For example, if the UPDATE privilege is granted on several columns in one GRANT statement, the grant is represented as one entry in SYSTABAUTH, and several entries in SYSCOLAUTH, all having matching timestamps.

Some of the entries in SYSCOLAUTH represent privileges that are exercised by preprocessed programs. These entries appear as though the creator of the program (the user who preprocessed the program) granted the privilege to the program itself. The columns in SYSCOLAUTH are:

Column Name	Data Type	Description and Comments
GRANTOR	CHAR(8) NOT NULL	The user ID of the person who granted the UPDATE privilege on this column.
GRANTEE	CHAR(8) NOT NULL	The user ID of the person who holds the UPDATE privilege. If the userid is PUBLIC, the privilege is held by all users.
CREATOR	CHAR(8) NOT NULL	The owner of the table that contains the column.
TNAME	VARCHAR(18) NOT NULL	The name of the table that contains the column. (CREATOR.TNAME uniquely identifies the table that contains the column.)
TIMESTAMP	CHAR(12) NOT NULL	The value of the System/390 time of day clock when the grant was made. This value is used internally when privileges are revoked, and is stored as a string of numbers and letters.
COLNAME	VARCHAR(18) NOT NULL	The name of the column on which the UPDATE privilege has been granted.

Note:

The authorization for update by column appears in a separate table, one column per row, only because it is possible to grant the UPDATE privilege on specific columns of the table. If the user has the UPDATE privilege on all columns of a table, that information does not appear in SYSCOLAUTH; rather, UPDATECOLS in SYSTABAUTH is set to ' '. Otherwise, UPDATECOLS contains '*' to indicate that more information is in the SYSCOLAUTH table.)

 SYS COLSTATS

The SYSCOLSTATS table keeps the column statistics listed below for a column which is the first column of an index. SYSCOLSTATS is updated whenever an index is created, reorganized or dropped, or UPDATE STATISTICS is run. The statistics in SYSCOLSTATS are used internally by the database manager.

Because SYSCOLSTATS records the first and second-most frequent values in the first column used by every index on every table in the database, you should consider revoking public access to SYSCOLSTATS if any of these values could be sensitive data.

The columns in SYSCOLSTATS are:

Column Name	Data Type	Description and Comments
CNAME	VARCHAR(18) NOT NULL	The name of the column described.
TNAME	VARCHAR(18) NOT NULL	The name of the table in which the column (CNAME) is located.
CREATOR	CHAR(8) NOT NULL	The owner who created the table identified by TNAME. (Thus, CNAME is the name of a column in the table identified by CREATOR.TNAME.)
VAL10	VARCHAR(12) NOT NULL FOR BIT DATA	The value of column CNAME at the tenth percentile. If the table TNAME has N rows and all N values of CNAME are arranged in ascending order, then VAL10 is at position 0.1 * N in this sequence.
VAL50	VARCHAR(12) NOT NULL FOR BIT DATA	The value of column CNAME at the 50th percentile. If the table TNAME has N rows and all N values of CNAME are arranged in ascending order, then VAL50 is at position 0.5 * N in this sequence.
VAL90	VARCHAR(12) NOT NULL FOR BIT DATA	The value of column CNAME at the 90th percentile. If the table TNAME has N rows and all N values of CNAME are arranged in ascending order, then VAL90 is at position 0.9 * N in this sequence.
FREQ1VAL	VARCHAR(12) NOT NULL FOR BIT DATA	The most frequent value in the column. If there is more than one value, the smaller is used. If the column is not a character data type, FREQ1VAL may be unprintable.
FREQ1PCT	SMALLINT NOT NULL	The percent frequency of FREQ1VAL
FREQ2VAL	VARCHAR(12) NOT NULL FOR BIT DATA	The second most frequent value in the column. If there is more than one value, the smaller is used. If the column is not a character data type, FREQ2VAL may be unprintable.
FREQ2PCT	SMALLINT NOT NULL	The percent frequency of FREQ2VAL

 SYS COLUMNS

The SYSCOLUMNS table contains a more detailed description of the database than that contained in SYSCATALOG. Recall that SYSCATALOG contains a row for each table or view in the database; SYSCOLUMNS contains a row for every column of every table or view in the database (including the columns of the catalog tables).

The columns in SYSCOLUMNS are:

Column Name	Data Type	Description and Comments
CNAME	VARCHAR(18) NOT NULL	The name of the column described.
TNAME	VARCHAR(18) NOT NULL	The name of the table or view in which the column (CNAME) is located.
CREATOR	CHAR(8) NOT NULL	The owner of the table or view identified by TNAME. (Thus, CNAME is the name of a column in the table or view identified by CREATOR.TNAME.)
COLNO	SMALLINT NOT NULL	The number of the column in the table. The value in COLNO corresponds to the sequence that columns are specified in the CREATE TABLE statement or added in the ALTER TABLE statement.
COLTYPE	CHAR(8) NOT NULL	The data type of the column: INTEGER, SMALLINT, CHAR, VARCHAR, LNGVCHAR, DATE, TIME, TIMESTMP, GRAPHIC, VARGRAPH, LONGVARG, FLOAT, DECIMAL, DBAINT, or DBAHW. These last two data types are for information used only internally by the database manager. (DBAINT data appears externally as INTEGER data; DBAHW data appears externally as SMALLINT data.)
LENGTH	CHAR(7) NOT NULL	The size of the column as specified in the CREATE TABLE or ALTER TABLE statements. If the column has a data type of CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC, LENGTH contains the value specified on the CREATE TABLE or ALTER TABLE statements. If the data type is LONG VARCHAR, LENGTH contains 32767. If the data type is LONG VARGRAPHIC, LENGTH contains 16383. If the data type is DATE, TIME, or TIMESTAMP, LENGTH is blank. For INTEGER and SMALLINT, LENGTH is blank. For FLOAT, LENGTH contains the length supplied for FLOAT in the CREATE TABLE or ALTER TABLE statements. If no value was supplied for a FLOAT column, LENGTH is blank. If the data type is DECIMAL, the precision and scale of the column are in this field, in the form: (pp,ss). For example: (11, 2).
SYSLENGTH	DBAHW NOT NULL	If COLTYPE is CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC, then this field contains the (maximum) length of the data, in bytes. If COLTYPE is DATE, then SYSLENGTH contains 4. If COLTYPE is TIME, then SYSLENGTH contains 3. If COLTYPE is TIMESTAMP, then SYSLENGTH contains 10. If COLTYPE is DECIMAL, then the first byte gives the number of digits in the number, and the second gives the number of digits after the decimal point. In other words, if COLTYPE is DECIMAL and LENGTH is (pp,ss), then SYSLENGTH contains (256 x pp) + ss. If COLTYPE is any one of the other numeric types, SYSLENGTH contains the number of bytes occupied by a datum of that type: 2, 4, or 8. SYSLENGTH never reflects the additional byte used internally to indicate nulls or the halfword prefix for the length of VARCHAR or VARGRAPHIC fields.
NULLS	CHAR(1) NOT NULL	The possible values are: Y if null values are allowed in this column. N if null values are not allowed in this column.
REMARKS	VARCHAR(254) NOT NULL	Information about the column supplied by a user by a COMMENT statement. The remarks are deleted from SYSCOLUMNS when the table or view is dropped. If the application server default CHARNAME setting supports mixed data (that is, CCSIDMIXED is not 0), users can store mixed data (both SBCS and DBCS characters) in the REMARKS column.
COLCOUNT	DBAINT NOT NULL	The number of unique values in the column. COLCOUNT initially contains -1 or 0. COLCOUNT captures only an approximate value for a column which is not the first column of any index. See Note 1 for update rules on this column.
HIGH2KEY	VARCHAR(12) NOT NULL FOR BIT DATA	The first eight bytes of the second highest value in the column. (This value is needed internally.) This field initially contains blanks. If COLTYPE is not CHAR, VARCHAR, LNGVCHAR, GRAPHIC, VARGRAPH, or LONGVARG, then HIGH2KEY may be unprintable. See Note 1 for update rules on this column.
LOW2KEY	VARCHAR(12) NOT NULL FOR BIT DATA	The first eight bytes of the second lowest value in this column. (This value is needed internally.) This field initially contains blanks. If COLTYPE is not CHAR, VARCHAR, LNGVCHAR, GRAPHIC, VARGRAPH, or LONGVARG, then LOW2KEY may be unprintable. See Note 1 for update rules on this column.
AVGCOLLEN	DBAHW NOT NULL	The average length of the values in this column. (This value is needed internally.) This value is initially -1. See Note 1 for update rules on this column. The value is always -1 for long fields.
ORDERFIELD	CHAR(1) NOT NULL	The possible values are: Y if the rows are physically clustered in accord with the values in this column. N when the table is created, then set to 'Y' if further information about this column exists. This value is reset to X'FF' when the index is dropped. This entry is valid only for a column in a single-column index.
CLABEL	VARCHAR(30)	A column label supplied by a user using a LABEL statement. The column labels are deleted from SYSCOLUMNS when the table or view is dropped. If the application server default CHARNAME setting supports mixed data (that is, CCSIDMIXED is not 0), users can store mixed data (both SBCS and DBCS characters) in the CLABEL column.
COLINFO	CHAR(1)	This indicates the presence of additional information about the column which will be found in the table SYSTEM.SYSCOLSTATS. Y further information exists, see SYSTEM.SYSCOLSTATS. NULL when the table is created, then set to 'Y' if further information about this column exists. If this is the first column of an index and the index is dropped, and there is no other index with this column as the first column, this value is also set to NULL.
SUBTYPE 2	CHAR(1)	The subtype is applicable for CHAR, VARCHAR, and LONG VARCHAR columns only. The possible values are: . B for bit data. M for mixed data. S for SBCS data. NULL if any of the following cases is true: The column is in a table migrated from SQL/DS Version 2 Release 2 or earlier and has a data type of CHAR, VARCHAR, or LONG VARCHAR. The data type is not character.
CCSID 3	INTEGER	If CCSID conversion is required, it is done before data is stored in the column. CCSID is applicable for CHAR, VARCHAR, LONG VARCHAR, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC columns only. The possible values are: 1 to 65534 a valid CCSID for character or graphic data 65535 uniquely identifies bit character data NULL if any of the following conditions exist: the data type is character or graphic and the column is in a table migrated from a release previous to SQL/DS Version 3 Release 1 the data type is neither character nor graphic.
FLDPROC	CHAR(1)	Indicates whether the column has a field procedure. The possible values are: NULL if a column belongs to a table migrated to SQL/DS Version 3 Release 1 or later Y if the column has a field procedure N if the column does not have a field procedure.

Note 1:

The value is updated for all columns of the table by an UPDATE ALL STATISTICS statement on the table or a DBSPACE containing the table. UPDATE STATISTICS has the same effect but only for the columns which are the first columns of an index. CREATE or REORGANIZE INDEX updates the value for one column, the first one in the index.

Note 2:

The SUBTYPE value is only used when the CCSID value is null. If a SUBTYPE is encountered that is not valid and the CCSID value is null, a SUBTYPE of SBCS is assumed.

Note 3:

More information on CCSIDs can be found in the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

 SYS DBSPACES

The SYSDBSPACES table contains a row for each PUBLIC and PRIVATE DBSPACE in the database, including those DBSPACEs that no user has yet acquired. The number of DBSPACEs available is determined during database generation. The size of each DBSPACE is also specified at that time.

Additional DBSPACEs may be added from time to time by the ADD DBSPACE operation.

The columns in SYSDBSPACES are:

Column Name	Data Type	Description and Comments
DBSPACENAME	VARCHAR(18) NOT NULL	The name given to the DBSPACE by the user who acquired it. If the DBSPACE has not been acquired, the field contains an empty string.
DBSPACENO	DBAHW NOT NULL	When the database is generated, the database manager assigns each DBSPACE a number (for internal use). DBSPACENO is that number. This is the number you would use in the SHOW DBSPACE operator command.
OWNER	CHAR(8) NOT NULL	The possible values are: blank if the DBSPACE is not yet assigned. owner if the DBSPACE is PRIVATE. PUBLIC if the DBSPACE is PUBLIC. ¬¬¬¬¬¬¬¬ if the DBSPACE has been dropped, but has not yet been removed from the database. In this case, DBSPACENAME will contain the number of the DBSPACE to be removed.
DBSPACETYPE	SMALLINT NOT NULL	The possible values are: 1 if the DBSPACE is PUBLIC. 2 if the DBSPACE is PRIVATE.
NTABS	DBAHW NOT NULL	The number of tables contained in this DBSPACE. This field is updated when CREATE TABLE and DROP TABLE statements are issued.
NPAGES	INTEGER NOT NULL	The number of usable pages in the DBSPACE. NPAGES is specified in the PAGES parameter of the ACQUIRE DBSPACE statement. Caution: Changing NPAGES makes the dbspace appear to be a different size without actually changing it. NPAGES should not be changed in a production environment, to do so may cause errors to occur. It is intended for testing purposes only.
NRHEADER	DBAHW NOT NULL	The number of pages to be used for the DBSPACE header. This number must be between 1 and 8. NRHEADER is specified in the NHEADER parameter of the ACQUIRE DBSPACE statement.
PCTINDX	DBAHW NOT NULL	The percentage of pages to be used for indexes. PCTINDX is specified in the PCTINDEX parameter of the ACQUIRE DBSPACE statement.
FREEPCT	SMALLINT NOT NULL	The percentage of space on each page to be kept free when rows are inserted. Initially, the database manager gets the value for FREEPCT from the PCTFREE parameter of the ACQUIRE DBSPACE statement. FREEPCT can be updated by the PCTFREE parameter of the ALTER DBSPACE statement. For package DBSPACEs, FREEPCT is either 0 or 1. A FREEPCT of 0 indicates that the package DBSPACE is full. A FREEPCT of 1 indicates that it is not full.
LOCKMODE	CHAR(1) NOT NULL	The possible values are: S if the entire DBSPACE is to be locked. P if page locking is to be done in this DBSPACE. T if row locking is to be done in this DBSPACE. LOCKMODE is updated by the LOCK parameter of the ACQUIRE DBSPACE and ALTER DBSPACE statements.
NACTIVE	DBAINT NOT NULL	The number of active data pages in this DBSPACE (set to -1 when the DBSPACE is acquired). This value is the number of data pages that must be read for a complete DBSPACE scan. The value includes all data pages that contain stored rows for this DBSPACE. NACTIVE is set by an UPDATE STATISTICS statement issued for this DBSPACE, or for any table in this DBSPACE, or by creating or reorganizing any index in this DBSPACE, or by the DATALOAD/RELOAD DBS Utility commands if the statistics collection has not been turned off by SET UPDATE STATISTICS OFF. NACTIVE is set to zero for an UPDATE STATISTICS statement issued on a package DBSPACE.
POOL	DBAHW NOT NULL	The number of the storage pool into which the database manager places pages that belong to this DBSPACE. POOL is specified using the STORPOOL parameter of the ACQUIRE DBSPACE statement. If the value of POOL is negative, the storage pool is nonrecoverable. (The absolute value of POOL is the storage pool number.)

 SYS DROP

SYSDROP contains a list of tables and DBSPACEs waiting to be dropped. The database manager uses this table when tables or DBSPACEs are dropped from the database. When a DBSPACE or table is dropped, its description is dropped from the catalog immediately, but the object is not dropped until the end of a logical unit of work (LUW). Instead, the database manager makes an entry in SYSDROP identifying the dropped table or DBSPACE. When the LUW is committed (implicitly or explicitly), all objects identified in SYSDROP are dropped. This allows the LUW containing the DROP statement to proceed without waiting on any locks held against the object being dropped, while guaranteeing that the object will be dropped. It also minimizes the performance cost if the LUW must be rolled back. Any LUW accessing the table or DBSPACE when the DROP statement is issued will complete, but no further access to the object is possible. For information on diagnosing problems associated with the DROP statement, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.

Column Name	Data Type	Description and Comments
DBSPACENO	DBAHW NOT NULL	The internal number of the DBSPACE containing an object to be dropped.
TABID	DBAHW NOT NULL	The internal identifier of a table to be dropped. (In the DB2 Server for VSE & VM Diagnosis Guide and Reference manual, an internal table identifier is known as a DBSS RID.)
QUALF	CHAR(1) NOT NULL	The possible values are: S if the object to be dropped is a DBSPACE. T if the object to be dropped is a table.

 SYS FIELDS

The SYSFIELDS table contains a row for each column that has a field procedure associated with it. The columns in SYSFIELDS are:

Column Name	Data Type	Description and Comments
CREATOR	CHAR(8) NOT NULL	The owner of the package or view who created the table containing the column with the field procedure.
TNAME	VARCHAR(18) NOT NULL	The name of the table containing this column.
COLNO	SMALLINT NOT NULL	The number of this column in the table.
CNAME	VARCHAR(18) NOT NULL	The name of this column
FLDTYPE	CHAR(8) NOT NULL	The data type of the encoded value in the field. Possible values are: INTEGER for large integer. SMALLINT for small integer. DECIMAL for decimal. FLOAT for floating-point. CHAR for fixed length character string. VARCHAR for varying length character string. GRAPHIC for fixed length graphic string. VARGRAPH for varying length graphic string.
FLDLENGTH	SMALLINT NOT NULL	The length attribute of the field, or the precision for decimal fields. The number does not include the internal prefixes that may be used to record the actual length and null state. The value in this column depends on the data type of the field as follows: For INTEGER 4 For SMALLINT 2 For DECIMAL 1 byte - precision of number 1 byte - scale of number For FLOAT 8 For CHAR Length of the string For VARCHAR Maximum length of the string For GRAPHIC Number of DBCS characters For VARGRAPHIC Maximum number of DBCS characters.
FPNAME	CHAR(8) NOT NULL	Name of the field procedure. Field procedure names are unique within an installation.
FPWORKAREA	SMALLINT NOT NULL	Size, in bytes, of the work area required for the encoding and decoding functions of the field procedure.
FPEXITPARML	SMALLINT NOT NULL	Length of the field procedure parameter value block.
FPPARMLIST	VARCHAR(254) NOT NULL	The parameter list given after FIELDPROC in the statement that created the column. Insignificant blanks are removed.

 SYS FPARMS

The SYSFPARMS table holds the field procedure value block contents for each field procedure. Blocks longer than 254 characters will have more than one row in the table. All field procedure value blocks of length greater than 0 will be recorded in this table. The columns in SYSFPARMS are:

Column Name	Data Type	Description and Comments
FPNAME	CHAR(8) NOT NULL	Name of the field procedure. Field procedure names are unique within an installation.
CREATOR	CHAR(8) NOT NULL	The owner of the package or view who created the table that contains the column with the field procedure.
TNAME	VARCHAR(18) NOT NULL	Name of the table that contains the column with the field procedure.
CNAME	VARCHAR(18) NOT NULL	Name of the column that has the field procedure.
SEQNO	SMALLINT NOT NULL	Indicates the sequence of the portion of the parameter value block contained in this row. A long block may be divided among several rows of the SYSFPARMS table. The value for the first portion of a block is 1. Successive rows have sequential values.
FPEXITPARM	VARCHAR(254) NOT NULL	The parameter value block (or a portion of it) of the field procedure. This control block is passed to the field procedure when it is invoked.

 SYS INDEXES

The SYSINDEXES table contains a row for every index currently in existence, including the indexes that the database manager maintains on its own catalog tables.

The columns in SYSINDEXES are:

Column Name	Data Type	Description and Comments
INAME	VARCHAR(18) NOT NULL	The name of the index.
ICREATOR	CHAR(8) NOT NULL	The user ID of the person who created the index. The combination of INAME and ICREATOR uniquely identifies the index.
TNAME	VARCHAR(18) NOT NULL	The table on which the index is defined.
CREATOR	CHAR(8) NOT NULL	The owner of the package or view who created the table on which the index is defined.
COLNAMES	VARCHAR(100) NOT NULL	This contains the first 100 characters of the names of the columns on which the index is defined. Each name is preceded by + (for ascending) or - (for descending), and separated by commas and blanks. For example: +AGE, +SALARY, -NEXM
INDEXTYPE	CHAR(1) NOT NULL	The possible values are: U if the index is unique (duplicates not allowed). D if duplicates are allowed.
CLUSTER	CHAR(1) NOT NULL	The possible values are: C if the index is clustered. N if the index is not clustered. F if the index was the first index created and is now clustered (used for default insert clustering). W if the index was the first index created and is now not clustered (still the default insert index). blank if this is an inactive primary key index. The value of CLUSTER is not directly related to the CLUSTERRATIO value. See Note 1 for update rules on this column.
IID	DBAHW NOT NULL	The internal index identifier assigned to the index by DBSS.
COLNUMBERS	VARCHAR(34) NOT NULL FOR BIT DATA	An indicator array of binary integers of 15 bits (plus sign); it has one more element than the number of columns in the index. The first element is the number of columns in the index. The second element is the column number defining the major ordering of the index; the remaining elements define the minor orders of the index. The column number is positive if the index is ascending on that column, and negative if it is descending. Each of these binary integer halfwords is stored in internal format. The size of this field restricts index definitions to 16 columns.
KEYLEN	DBAHW NOT NULL	This is used internally by the database manager. It is the average length of the key field. See Note 1 for update rules on this column.
FIRSTKEYCOUNT	DBAINT NOT NULL	This is used internally by the database manager. It gives the number of distinct values for the index, considering the first column only. See Note 1 for update rules on this column.
FULLKEYCOUNT	DBAINT NOT NULL	This is used internally by the database manager. It gives the number of distinct values for the index, considering all key columns. See Note 1 for update rules on this column.
LOCKMODE	CHAR(1) NOT NULL	This is used internally by the database manager. It is: K if key-interval locking is being performed on the index. P if the pages of the index are being locked. LOCKMODE is updated using the LOCK parameter of the ACQUIRE DBSPACE and ALTER DBSPACE statements. When you specify LOCK=ROW on either the ACQUIRE DBSPACE or ALTER DBSPACE statements, the database manager internally uses key-interval locking for that DBSPACE. (Note that this applies only to PUBLIC DBSPACEs because you cannot specify a different lock size for PRIVATE DBSPACEs.)
NLEAF	DBAINT NOT NULL	This is used internally by the database manager. It is the number of lowest-level pages in the index. See Note 1 for update rules on this column.
NLEVELS	DBAHW NOT NULL	This is used internally by the database manager. It is the number of levels in the index tree. See Note 1 for update rules on this column.
IPCTFREE	SMALLINT NOT NULL	The amount of free space reserved in the index for later insertions and updates. IPCTFREE is specified in the CREATE INDEX statement and the REORGANIZE INDEX command via the PCTFREE parameter.
CLUSTERRATIO	SMALLINT	This is used internally by the optimizer. The value here is a measure of how clustered an index is. The value is a number between 0 and 10 000 where 10 000 represents a totally clustered index and 0 represents a totally unclustered index. The value in this column is not directly related to the CLUSTER value. See Note 1 for update rules on this column.
RELEASE	CHAR(5)	This column identifies the release for which the index was created. It contains the value "2.1.0" for any release up to and including SQL/DS Version 2 Release 1. For later releases, possible values are: 2.2.0 for SQL/DS Version 2 Release 2 3.1.0 for SQL/DS Version 3 Release 1 3.2.0 for SQL/DS Version 3 Release 2 3.3.0 for SQL/DS Version 3 Release 3 3.4.0 for SQL/DS Version 3 Release 4 3.5.0 for SQL/DS Version 3 Release 5 5.1.0 for DB2 Server for VSE & VM Version 5 Release 1 6.1.0 for DB2 Server for VSE & VM Version 7 Release 1 Non-unique indexes created under SQL/DS Version 2 Release 2 or later have better performance characteristics than non-unique indexes from previous releases.
KEYTYPE	CHAR(1)	This identifies whether the index is used for a primary key. The possible values are: P if the index was created for a primary key which is active. I if the index was created for a key which is inactive. U if the index was created for a unique constraint which is active. blank if the index was not created for a primary key or unique constraint.

Note 1:

The value is updated for all indexes on a table or in the DBSPACE by the UPDATE STATISTICS and UPDATE ALL STATISTICS statements. CREATE and REORGANIZE INDEX updates the value for the created or reorganized index.

 SYS KEYCOLS

This table contains a row for every column in every key.

Column Name	Data Type	Description and Comments
TNAME	VARCHAR(18) NOT NULL	The name of the table on which the key is defined.
TCREATOR	CHAR(8) NOT NULL	The owner of the package or view who created the table on which the key is defined.
KEYTYPE	CHAR(1) NOT NULL	The possible values are: P primary key F foreign key U unique constraint
KEYNAME	CHAR(18) NOT NULL	This is the key name specified in the FOREIGN KEY clause (KEYTYPE = F) or the constraint name specified in the UNIQUE clause (KEYTYPE = U). If the key or constraint name is not specified or it is a primary key, the system generated name will be stored here.
CNAME	VARCHAR(18) NOT NULL	The column name.
KEYORD	SMALLINT NOT NULL	The position of the column within the key.
TABLEORD	SMALLINT NOT NULL	The position of the column within the table.
DATACODE	SMALLINT NOT NULL	The data type of the column in internal form.
SYSLENGTH	SMALLINT NOT NULL	This contains the length of the column. Its interpretation is the same as the SYSLENGTH column in the SYSCOLUMNS table.
TIMESTAMP	TIMESTAMP NOT NULL	The date and time when this key was activated.
FLDPROC	CHAR(1)	Indicates whether this column has a field procedure associated with it. Possible values are: Y if yes. N if no. NULL if the table containing the key was migrated from SQL/DS Version 2 Release 2 or earlier.
CCSID 1	INTEGER	If CCSID conversion is required, it is done before data is stored in the column. CCSID is applicable for CHAR, VARCHAR, LONG VARCHAR, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC columns only. The possible values are: 1 to 65534 a valid CCSID for character or graphic data 65535 uniquely identifies bit character data NULL if any of the following conditions exist: the data type is character or graphic and the column is in a table migrated from a release previous to SQL/DS Version 3 Release 1 the data type is neither character nor graphic.

Note 1:

More information on CCSIDs can be found in the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

 SYS KEYS

This table contains a row for each primary key, each foreign key, and each unique constraint.

Column Name	Data Type	Description and Comments
TNAME	VARCHAR(18) NOT NULL	The name of the table on which the key or constraint is defined.
TCREATOR	CHAR(8) NOT NULL	The owner of the package or view who created the table on which the key or constraint is defined.
KEYTYPE	CHAR(1) NOT NULL	The possible values are: P primary key F foreign key U unique constraint
KEYNAME	CHAR(18) NOT NULL	This is the key name specified in the FOREIGN KEY clause (KEYTYPE = F) or the constraint name specified in the UNIQUE clause (KEYTYPE = U). If the key name or the unique constraint name is not specified or it is a primary key, the system generated name will be stored here. The format of the generated name is 'PKEY', 'FKEY' or 'UKEY' followed by a special 12-bytes timestamp. The timestamp is the value of the System/390 time of day clock when the key is defined and it is a string of numbers and letters in base 35 representation. It is the same type of timestamp that the database manager uses in the SYSTABAUTH table.
KEYCOLS	SMALLINT NOT NULL	This is the number of columns that form the primary or foreign key, or unique constraint.
INAME	VARCHAR(18) NOT NULL	For a primary key this contains the name of the primary key index. For a foreign key, this field is blank. For a unique constraint this contains the name of the index.
REFTNAME	VARCHAR(18) NOT NULL	For a foreign key, this field contains the name of the parent table. For a primary key and unique constraint, this field is blank.
REFTCREATOR	CHAR(8) NOT NULL	For a foreign key, this field contains the owner of the package or view who created the parent table. For a primary key and unique constraint this field is blank.
DELETERULE	CHAR(1) NOT NULL	For a foreign key, this column gives the associated DELETE rule. The possible values are: R if the delete rule is RESTRICT. C if the delete rule is CASCADE. N if the delete rule is SET NULL. For a primary key and unique constraint this field is blank.
STATUS	CHAR(1) NOT NULL	The current status of the key. The possible values are: A if the key is active. I if the key is inactive. D if the foreign key is implicitly inactive. (Dependent on an inactive primary key).
TIMESTAMP	TIMESTAMP NOT NULL	The date and time when this key or constraint was activated.

 SYS LANGUAGE

The SYSLANGUAGE table contains the names of all currently installed national languages (for example, English or French); that is, it is not a programming language such as COBOL. A unique four-character code identifies each language and a brief description, if necessary, is contained in the REMARKS column.

Unlike all other catalog tables, the owner of SYSLANGUAGE is SQLDBA. To view SYSLANGUAGE use the statement:

   SELECT * FROM SQLDBA.SYSLANGUAGE

The columns in SYSLANGUAGE are:

Column Name	Data Type	Description and Comments
LANGUAGE	VARCHAR(40) NOT NULL	The name of the national language.
LANGKEY	CHAR(4) NOT NULL	The language key.
REMARKS	VARCHAR(254)	Comments or description of the language.
LANGID	VARCHAR(5)	The VM-compatible language ID of the installed language.

 SYS OPTIONS

The SYSOPTIONS table describes the options and defaults that may be implemented for this database. The table summarizes the information contained in the text that follows the table.

Column Name	Data Type	Description and Comments
SQLOPTION	VARCHAR(18) NOT NULL	The name of the option being described by this row. SQLOPTION can be: RELEASE if this row describes the release level of the database manager. CHARNAME if this row describes the name of the character set that is currently in effect. DBCS if this row describes the setting of the DBCS option. CHARSUB if this row describes the default subtype for character columns. DATE if this row describes the default format of DATE for the database manager. TIME if this row describes the default format of TIME for the database manager. LDATELEN if this row describes the length of the local (user defined) DATE format. LTIMELEN if this row describes the length of the local (user defined) TIME format. DEFAULT LANGUAGE if this row describes the default language used for ISQL HELP text. CCSIDSBCS if this row describes the default CCSID for SBCS character data and newly-created SBCS character columns. CCSIDMIXED if this row describes the default CCSID for mixed character data and newly-created mixed character columns. CCSIDGRAPHIC if this row describes the default CCSID for graphic data and newly-created graphic columns. MCCSIDSBCS if this row describes the default CCSID for migrated SBCS character columns. MCCSIDMIXED if this row describes the default CCSID for migrated mixed character columns. MCCSIDGRAPHIC if this row describes the default CCSID for migrated graphic columns.
VALUE	VARCHAR(18) NOT NULL	This describes the option. If SQLOPTION is RELEASE, then VALUE indicates the release level, such as "7.1.0" for DB2 Server for VSE & VM Version 6 Release 1. If SQLOPTION is CHARNAME, then VALUE indicates the value of CHARNAME that was specified when the database manager was last started. The database management system is shipped with CHARNAME set to INTERNATIONAL. (Character set information is stored in the SYSCHARSETS catalog table.) If SQLOPTION is DBCS, then VALUE indicates whether the DBCS option is enabled. YES, in this case, indicates that the DBCS option is enabled. NO indicates that it is not enabled. Note that a "YES" does not necessarily mean that the DBCS option is currently in effect. It could be that the database administrator just updated the value. If the value was just updated, then the DBCS option will not take effect until the database manager is restarted. If SYSOPTION is CHARSUB, then VALUE indicates the default character subtype to be used for the database. The default is SBCS. MIXED is the other possible value. Note that the value indicated here does not mean that value is currently in effect. It could be that the database administrator just updated the value. If the value was just updated, then the value indicated will not be in effect until the database manager is restarted. If SQLOPTION is DATE, then VALUE indicates the data format to be used for the database. The default for DATE is ISO. However, JIS, USA, EUR or LOCAL may be used. Note that the value indicated here does not necessarily mean that the value indicated for the option is currently in effect. It could be that the database administrator just updated the value. If the value was just updated, then the value indicated will not be in effect until the database manager is restarted. If SQLOPTION is TIME, then VALUE indicates the TIME format to be used for the database. The default for TIME is ISO. However, JIS, USA, EUR or LOCAL may be used. Note that the value indicated here does not necessarily mean that the value indicated for the option is currently in effect. It could be that the database administrator just updated the value. If the value was just updated, then the value indicated will not be in effect until the database manager is restarted. If SQLOPTION is LDATELEN, then VALUE indicates the length of the local DATE format. The default for LDATELEN is 0, if no local DATE format is used. If a local DATE format is used, the length LDATELEN must be greater than 9 and less than 255. Note that the value indicated here does not necessarily mean that the value indicated for the option is currently in effect. It could be that the database administrator just updated the value. If the value was just updated, then the value indicated will not be in effect until the database manager is restarted. If SQLOPTION is LTIMELEN, then VALUE indicates the length of the local TIME format. The default for LTIMELEN is 0, if no local TIME format is used. If a local TIME format is used, the length LTIMELEN must be greater than 7 and less than 255. Note that the value indicated here does not necessarily mean that the value indicated for the option is currently in effect. It could be that the database administrator just updated the value. If the value was just updated, then the value indicated will not be in effect until the database manager is restarted. If SQLOPTION is DEFAULT LANGUAGE, then VALUE indicates the default language to be used for ISQL HELP text. If SQLOPTION is CCSIDSBCS, then VALUE indicates the default CCSID for SBCS character data and newly-created SBCS character columns. If SQLOPTION is CCSIDMIXED, then VALUE indicates the default CCSID for mixed character data and newly-created mixed character columns. If SQLOPTION is CCSIDGRAPHIC, then VALUE indicates the default CCSID for graphic data and newly-created graphic columns. If SQLOPTION is MCCSIDSBCS, then VALUE indicates the default CCSID for migrated SBCS character columns. If SQLOPTION is MCCSIDMIXED, then VALUE indicates the default CCSID for migrated mixed character columns. If SQLOPTION is MCCSIDGRAPHIC, then VALUE indicates the default CCSID for migrated graphic columns.
REMARKS	VARCHAR(254) NOT NULL	This contains remarks describing each row. The database manager places remarks in this column when the SYSOPTIONS table is created.

The following table shows the actual entries you would see in a newly-installed SYSOPTIONS table.

SQLOPTION	VALUE	REMARKS
RELEASE	7.1.0	VERSION, RELEASE, MODIFICATION
CHARNAME	INTERNATIONAL 1	CHARACTER SET FOR SQL STATEMENTS
DBCS	NO 1	WHETHER SO/SI CHARACTERS ARE RECOGNIZED
CHARSUB	SBCS	DEFAULT CHARACTER SUBTYPE COLUMNS. POSSIBLE VALUES: SBCS,MIXED
DATE	ISO	DEFAULT DATE: ISO, JIS, USA, EUR, LOCAL
TIME	ISO	DEFAULT TIME: ISO, JIS, USA, EUR, LOCAL
LDATELEN	0	LOCAL DATE LENGTH: 0 OR 9 < LEN < 255
LTIMELEN	0	LOCAL TIME LENGTH: 0 OR 7 < LEN < 255
DEFAULT LANGUAGE	S001	DEFAULT LANGUAGE FOR HELP TEXT
CCSIDSBCS	500	DEFAULT CCSID FOR SBCS DATA AND NEWLY CREATED SBCS CHARACTER COLUMNS 2
CCSIDMIXED	0	DEFAULT CCSID FOR MIXED DATA AND NEWLY CREATED MIXED CHARACTER COLUMNS
CCSIDGRAPHIC	0	DEFAULT CCSID FOR GRAPHIC DATA AND NEWLY CREATED GRAPHIC COLUMNS
MCCSIDSBCS	37 3	DEFAULT CCSID FOR MIGRATED SBCS CHARACTER COLUMNS
MCCSIDMIXED	0	DEFAULT CCSID FOR MIGRATED MIXED CHARACTER COLUMNS
MCCSIDGRAPHIC	0	DEFAULT CCSID FOR MIGRATED GRAPHIC COLUMNS

Note 1:

The CHARNAME value must be a mixed CHARNAME and DBCS value must be set to YES for DBCS character support.

Note 2:

More information on CCSIDs can be found in the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

Note 3:

Though 37 is the default CCSID migration value, 500 is the installation default.

 SYS PARMS

The SYSPARMS table describes the parameters for the stored procedures defined. It contains a row for each parameter of each stored procedure. Table Table 24 shows the definition of this catalog table.

Table 24. Definition of SYSTEM.SYSPARMS

Column Name	Data Type	Description
NAME	CHAR(18) NOT NULL	The name of the STORED procedure with which this parameter is associated.
AUTHID	CHAR(8) NOT NULL	The authorization ID associated with this version of the stored procedure. See Table 28 for an example of using the AUTHID column.
PARMNAME	CHAR(18) NOT NULL	The name of the parameter, or blank. This column is included for compatibility with other database products. It is ignored by DB2 Server for VSE & VM.
ROUTINEID	INTEGER NOT NULL	Internal identifier of the stored procedure.
ROWTYPE	CHAR(1) NOT NULL	The type of the parameter. Possible values are: P Input parameter O Output parameter B Both input and output
ORDINAL	SMALLINT NOT NULL	The ordinal number of the parameter within the parameter list.
TYPENAME	CHAR(18) NOT NULL	The name of the data type of the parameter.
DATATYPEID	SMALLINT NOT NULL	The internal ID of the data type of the parameter.
LENGTH	INTEGER NOT NULL	Maximum length of the data type or the precision of the parameter.
SCALE	SMALLINT NOT NULL	Scale of the parameter, if the data type is decimal. 0 otherwise.
SUBTYPE	CHAR(1) NOT NULL	If the data type of the parameter is character, this column contains the character subtype. Possible values are: B The subtype is FOR BIT DATA. S The subtype is FOR SBCS DATA. M The subtype is FOR MIXED DATA. blank The data type of the parameter is not character.
CCSID	INTEGER NOT NULL	For all character and graphic data types, this column contains the CCSID that the stored procedure assumes this parameter will be tagged with. 0 for datatypes other than character and graphic.

 SYS PROGAUTH

SYSPROGAUTH records privileges of users to run packages, and to grant these privileges to other users. For the DB2 Server for VSE & VM database manager, a program is a package stored in the database. The columns in SYSPROGAUTH are:

Column Name	Data Type	Description and Comments
GRANTOR	CHAR(8) NOT NULL	The user ID of the person who granted the RUN privilege.
GRANTEE	CHAR(8) NOT NULL	The user ID of the person who holds the RUN privilege. If the userid is PUBLIC, the program may be run by all users.
CREATOR	CHAR(8) NOT NULL	The owner who preprocessed the program. CREATOR.PROGNAME uniquely identifies the package that may be run by the grantee.
PROGNAME	VARCHAR(8) NOT NULL	The name of the package that may be run by the grantee. The name is obtained from the PREPNAME preprocessor parameter. CREATOR.PROGNAME is the complete name of the package.
TIMESTAMP	CHAR(12) NOT NULL	The value of the System/390 time of day clock when the grant was made. This value is used internally when privileges are revoked; it is stored as a string of numbers and letters.
RUNAUTH	CHAR(1) NOT NULL	The possible values are: Y if the user is allowed only to run the package. G if the user may also grant the RUN privilege on the package to someone else.

 SYS PSERVERS

SYSTEM.SYSPSERVERS is added to allow the database administrator to define the stored procedure servers at which stored procedure run, and to put them in groups. This allows the database administrator to tune the stored procedure workload. This table is unique to DB2 Server for VSE & VM. Table Table 25 shows the definition of this catalog table.

Table 25. Definition of SYSTEM.SYSPSERVERS

Column Name	Data Type	Description
PSERVER	CHAR(8) NOT NULL	The name of the stored procedure server. This name must not contain any embedded blanks. If it does, any attempt to start the stored procedure server fails. Note that a PSERVER can be in only one group. See  SYS PSERVERS for more information on the PSERVER column.
SERVGROUP	CHAR(18)	The group that this server is in. Grouping the stored procedures enables the database administrator to tune the stored procedure workload. For example, if the database manager wanted to dedicate servers ACT1, ACT2, and ACT3 to accounting-related stored procedures, he could define them all in the same group, perhaps called ACCOUNT. In SYSTEM.SYSROUTINES, the row for any accounting-related stored procedure would specify ACCOUNT in the SERVGROUP column. When one of these stored procedures is invoked, the database manager will select a free server from the ACCOUNT group to run the stored procedure. Any server for which the SERVGROUP column is NULL is in the default server group.
AUTOSTART	CHAR(1)	Indicates whether the stored procedure server should be started when the database manager is started. A value of 'Y' indicates that the server should be autostarted. 'N' or NULL indicates that it should not be autostarted. The default value for this column is NULL.
DESCRIPTION	CHAR(254)	An optional column, in which the database manager can provide information such as the stored procedures that use this server group, specifications (for example virtual storage requirements) for servers in this group, and so on. The default value for this column is NULL.

Table 26 shows an example of a SYSTEM.SYSPSERVERS table.

Table 26. Sample SYSTEM.SYSPSERVERS Table

	PSERVER	SERVGROUP	AUTOSTART	DESCRIPTION
1	PROCSRV1		Y	Default server group
2	PROCSRV2	BILLING	Y
3	PROCSRV3	BILLING	Y
4	PROCSRV4	DAY_RPT	Y

In Table 26, the first row identifies the only server in the default server group. The second and third rows identify the servers that are in the group BILLING. The fourth row identifies the only server in the group DAY_RPT.

 SYS ROUTINES

SYSROUTINES allows the database administrator to specify the load module or phase name and package name for a given stored procedure, and to specify the stored procedure server at which it will run. Note that several of the columns in SYSTEM.SYSROUTINES in DB2 Server for VSE & VM correspond to columns in stored procedure related catalog tables in DB2 for MVS and have been given the same name. However, both tables have system-unique columns, and as a result the definitions of the tables are not identical. Table 27 shows the definition of SYSTEM.SYSROUTINES.

Table 27. Definition of SYSTEM.SYSROUTINES

Column Name	Data Type	Description
NAME	CHAR(18) NOT NULL	The name of the STORED procedure. This is the name that is specified in the SQL CALL statement.
AUTHID	CHAR(8) NOT NULL	The authorization ID that will be running this stored procedure. The AUTHID column can be used to qualify which SYSTEM.SYSROUTINES row is used to determine the load module, run time options, and so on, to use when a particular stored procedure is invoked. Possible reasons to use the AUTHID column include: To restrict the use of a stored procedure to a particular authorization ID To enable a particular authorization ID to test a new version of a stored procedure To allow different authorization IDs to use different versions of a stored procedure If AUTHID for a stored procedure is blank, any authorization ID can run that stored procedure. See Table 28 for an example of using the AUTHID column.
LOADMOD	CHAR(8) NOT NULL	The name of the load module or phase associated with the stored procedure.
ROUTINEID	INTEGER NOT NULL	Internal identifier of the routine.
PARMCOUNT	SMALLINT NOT NULL	The number of parameters for the routine.
LANGUAGE	CHAR(8) NOT NULL	Specifies the programming language used to create the stored procedure. Possible values are ASSEMBLE, PLI, COBOL, and C.
PARAMETERSTYLE	CHAR(1) NOT NULL	Specifies which parameter linkage convention should be used for this stored procedure. There are two possibilities: blank The GENERAL linkage convention is used. When a stored procedure is called, input parameters cannot be null. Also, the stored procedure cannot nullify output parameters. N The GENERAL WITH NULLS linkage convention is used. The input parameters can be null, and an array of indicator variables is passed to the stored procedure by DB2 Server for VSE & VM This is the default.
STAYRESIDENT	CHAR(1) NOT NULL	Determines whether the stored procedure load module or phase is removed from memory when the stored procedure ends. Possible values are Y The load module or phase remains in memory after the stored procedure ends. blank The load module or phase is removed from memory after the stored procedure ends.
PROGRAMTYPE	CHAR(1) NOT NULL	Indicates whether the stored procedure runs as a main routine or as a subroutine. The possible valiues are: M The routine runs as a LE main routine. S The routine runs as a LE subroutine.
COMMITON RETURN	CHAR(1) NOT NULL	If 'Y', a COMMIT WORK will be issued on return from the stored procedure. The default is 'N'. Note that since DB2 Server for VSE & VM does not have CURSOR WITH HOLD support, any cursors that are open on return will be closed if COMMITONRETURN is 'N'. This means that stored procedures that are to return result sets must have a value of 'N' in this column.
RESULTSETS	SMALLINT NOT NULL	Specifies the maximum number of result sets that the stored procedure can return to a DRDA client. A value of 0 indicates that no result sets will be returned.
SERVGROUP	CHAR(18)	Contains the name of the group of stored procedure servers that is used to run this stored procedure. The servers are defined in the SYSTEM.SYSPSERVERS catalog table, in the SERVGROUP column. If this column is blank or NULL, the stored procedure must run in the default server group. This implies that the column DEFSERV cannot contain the value 'N' in a row in which the column SERVGROUP is blank or NULL.
DEFSERV	CHAR(1)	Determines whether the stored procedure can run in the default stored procedure server group. 'Y' or NULL Indicates that the procedure can run in the default server group. 'N' Indicates that the procedure cannot run in the default server group. If this column contains 'N' then the SERVGROUP column must contain the name of a stored procedure server group.
RUNOPTS	VARCHAR(254) NOT NULL	The IBM Language Environment run-time options to use for this stored procedure. If RUNOPTS is blank, the installation default IBM Language Environment run-time options are used.
REMARKS	VARCHAR(254) NOT NULL	A character string provided by the user with the COMMENT ON statement.

Table 28 shows an example of a SYSTEM.SYSROUTINES table.

Table 28. Sample SYSTEM.SYSROUTINES Table

	PROCEDURE	AUTHID	SPECIFICNAME	SERVGROUP	DEFSERV
1	PROC1		PROG1	GROUP1	Y
2	PROC1	USER1	PROG2	GROUP1	N
3	PROC2	USER2	PROG3	Y

Note that in Table 28 rows 1 and 2 refer to the PROC1 stored procedure. By creating multiple rows in the SYSTEM.SYSROUTINES table with the same value for the NAME column, you can indicate that specified users have access to different versions of the stored procedure. In this case, in row 1 the AUTHID column is blank. Any user without a specific entry can use row 1. Row 2 applies only to SQL CALL requests coming from AUTHID USER1. When this user invokes the PROC1 stored procedure, a different load module or phase (PROG2) is loaded. The load module or phase can be a test version of the stored procedure or a version that is specific for that user.

Row 3 applies to stored procedure PROC2 and AUTHID USER2. Because there is no other row for stored procedure PROC2, user USER2 is the only one who can call this stored procedure.

As shown in Table 28, it is possible to have more than one row in SYSTEM.SYSROUTINES for a given stored procedure. The search precedence used to determine which row is selected for a specific client is as follows:

1. A row with AUTHID matching the caller's AUTHID
2. A row with AUTHID blank

 SYS STRINGS

The SYSSTRINGS table contains a list of the valid conversion combinations of source and target CCSIDs. More information on CCSIDs can be found in the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manual.

Column Name	Data Type	Description and Comments
INCCSID	INTEGER NOT NULL	The CCSID of the string that is a candidate for conversion.
OUTCCSID	INTEGER NOT NULL	The CCSID to which the string is to be converted.
TRANSTYPE	CHAR(2) NOT NULL	Classifies the CCSIDs as follows: SS for EBCDIC and ASCII SBCS to EBCDIC SBCS data conversion SM for EBCDIC and ASCII SBCS to EBCDIC mixed data conversion MS for EBCDIC mixed to EBCDIC SBCS data conversion MM for EBCDIC mixed to EBCDIC mixed data conversion PS for ASCII mixed to EBCDIC SBCS data conversion PM for ASCII mixed to EBCDIC mixed data conversion GG for ASCII graphic to EBCDIC graphic data conversion US for UCS-2 to EBCDIC SBCS data conversion UI for UCS-2 to a single byte component of an EBCDIC mixed data conversion UM for UCS-2 to EBCDIC mixed data conversion UG for UCS-2 to EBCDIC graphic data conversion
ERRORBYTE	CHAR(1) FOR BIT DATA	Specifies the byte that is used in the conversion table as an error indicator. An error occurs whenever a code point maps to the byte specified in this field.1 Null indicates the absence of an error indicator.
SUBBYTE	CHAR(1) FOR BIT DATA	Specifies the byte that is used in the conversion table as a substitution character.2 Null indicates the absence of a substitution character.
TRANSPROC	CHAR(8) NOT NULL	Name of the conversion procedure for MM, PM and GG TRANSTYPE. This procedure only applies to the DBCS portion of mixed data for the MM and PM TRANSTYPE.
TRANSTAB1	CHAR(64)	The first 64 bytes of the 256-byte conversion table or a blank. Used for SS, SM, MM, MS, PS and PM TRANSTYPEs.
TRANSTAB2	CHAR(192)	The last 192 bytes of the 256-byte conversion table or a blank. If either TRANSTAB1 or TRANSTAB2 contains an empty string, then both are considered an empty string. Used for SS, SM, MM, MS, PS and PM TRANSTYPEs.

Note 1:

If ERRORBYTE is X'3E', for example, that error byte indicates that no conversion is defined for the code points that map to X'3E'. An error (-330 or -331 assigned to SQLCODE and 22021 assigned to SQLSTATE) or warning (+331 assigned to SQLCODE and 01520 assigned to SQLSTATE) occurs whenever a code point maps to it.

Note 2:

If SUBBYTE is X'3F', for example, that byte is substituted for the code points that map to X'3F'. A warning (Z assigned to SQLWARN8, W assigned to SQLWARN0, and 01517 assigned to SQLSTATE) occurs whenever a code point maps to it.

 SYS SYNONYMS

The SYSSYNONYMS table contains a row for every synonym currently in effect. (A synonym is effective only for the user who defined it.) The columns in SYSSYNONYMS are:

Column Name	Data Type	Description and Comments
USERID	CHAR(8) NOT NULL	The owner who defined the synonym. The synonym is effective for this user only.
ALTNAME	VARCHAR(18) NOT NULL	The user's synonym for a table or view. USERID.ALTNAME uniquely identifies the synonym.
CREATOR	CHAR(8) NOT NULL	The owner of the table or view for which user USERID defined a synonym.
TNAME	VARCHAR(18) NOT NULL	CREATOR.TNAME is the real name of the table or view for which user USERID defined a synonym.

Note:

SYSSYNONYMS helps resolve unqualified table references in SQL statements. If a user does not qualify the name of the table (by preceding it with "creator."), the preprocessor first looks to see if the user has a table by that name. If the user does not, the name is assumed to be a synonym for another user's table, and the preprocessor consults SYSSYNONYMS to determine the real table. The object of the synonym must be a table or a view; it cannot be another synonym.

 SYS TABAUTH

SYSTABAUTH records:

• Privileges owned by users to access tables and views. For each privilege, it also records the source of the privilege (for example, a grant from another user).
• Privileges on tables and views exercised by packages. Each such privilege appears in SYSTABAUTH as if it were granted to the program by the user who preprocessed the program. The database manager uses SYSTABAUTH to find and invalidate packages when the necessary privileges are revoked from the creator of a program.

The columns in SYSTABAUTH are:

Column Name	Data Type	Description and Comments
GRANTOR	CHAR(8) NOT NULL	The owner who granted the privileges. If this row records the privileges exercised by a package, then GRANTOR is the creator of the corresponding program.
GRANTEE	VARCHAR(8) NOT NULL	The owner who holds the privileges or the name of the package that exercises the privileges. If the value of GRANTEE is 'PUBLIC', the privileges are held by all users.
GRANTEETYPE	CHAR(1) NOT NULL	The possible values are: blank if the grantee is a user. P if the grantee is a package.
SCREATOR	CHAR(8) NOT NULL	The owner who created the source table or source view on which privileges have been granted.
STNAME	VARCHAR(18) NOT NULL	The name of the source table or source view on which privileges have been granted. SCREATOR.STNAME uniquely identifies the source table or view.
TCREATOR	CHAR(8) NOT NULL	The owner who created the target table or view on which the grantee possesses some privileges.
TTNAME	VARCHAR(18) NOT NULL	The name of the target table or view on which the grantee possesses some privileges. TCREATOR.TTNAME uniquely identifies the target table or view. Usually, TCREATOR.TTNAME is the same as SCREATOR.STNAME. An exception occurs when a view is defined: an entry is made in SYSTABAUTH, showing the underlying table(s) in SCREATOR.STNAME and the view in TCREATOR.TTNAME.
TIMESTAMP	CHAR(12) NOT NULL	The value of the System/390 time of day clock when the grant was made. This value is used internally when privileges are revoked; it is stored as a string of numbers and letters.
UPDATECOLS	CHAR(1) NOT NULL	The possible values are: blank if the grant did not involve the UPDATE privilege, or if the UPDATE privilege was granted on all of the columns. * if the UPDATE privilege was granted on some of the columns. In this case, the SYSCOLAUTH table gives the names of the columns on which the UPDATE privilege was granted.
SELECTAUTH	CHAR(1) NOT NULL	The possible values are: Y if the user is allowed to select rows from this table. G if the user is allowed to grant this SELECT privilege. blank otherwise. The SELECT privilege is not automatically granted because a user might be authorized to insert into a table, but not to read it.
INSERTAUTH	CHAR(1) NOT NULL	The possible values are: Y if the user is allowed to insert into this object. G if the user is allowed to grant this INSERT privilege. blank otherwise.
UPDATEAUTH	CHAR(1) NOT NULL	The possible values are: Y if the user is allowed to update this object. G if the user is allowed to grant this UPDATE privilege. blank otherwise. The field UPDATECOLS, possibly together with several rows of the SYSCOLAUTH table, identifies the columns on which the UPDATE privilege was granted.
DELETEAUTH	CHAR(1) NOT NULL	The possible values are: Y if the user is allowed to delete rows. G if the user is allowed to grant this DELETE privilege. blank otherwise.
ALTERAUTH	CHAR(1) NOT NULL	The possible values are: Y if the object is a base table and the user is allowed to alter it. G if the user is allowed to grant this ALTER privilege. blank otherwise.
INDEXAUTH	CHAR(1) NOT NULL	The possible values are: Y if the object is a table and the user is allowed to create an index on it. G if the user is allowed to grant this INDEX privilege. blank otherwise.
REFAUTH	CHAR(1)	The possible values are: Y if the user is allowed to form, drop, activate, or deactivate a relationship where the object table is the parent table. G if the user is allowed to grant this REFERENCES privilege. NULL if the table or view was created prior to SQL/DS Version 2 Release 2. blank otherwise.

Note:

For information on updating columns see "Updateable Columns".

 SYS USAGE

SYSUSAGE records dependencies of one object on another. For example, a package is dependent on the tables and indexes that it uses, or a view is dependent on the tables on which it is defined. Each entry in SYSUSAGE describes one dependent object and one base object. (The base object is the object that is depended upon.) The columns in SYSUSAGE are:

Column Name	Data Type	Description and Comments
BNAME	VARCHAR(18) NOT NULL	The name of the base object (table, view, index, or DBSPACE).
BCREATOR	CHAR(8) NOT NULL	The owner of the creator of the table or index, or the owner of the DBSPACE. BCREATOR.BNAME uniquely identifies the base object.
BTYPE	CHAR(1) NOT NULL	A code indicating what the base object is: R real table. V view. I index. S DBSPACE.
DNAME	VARCHAR(18) NOT NULL	The name of the dependent view or package that is derived from or that uses the object BNAME.
DCREATOR	CHAR(8) NOT NULL	The owner who defined the dependent view or package DNAME. DCREATOR.DNAME uniquely identifies the dependent object.
DTYPE	CHAR(1) NOT NULL	The possible values are: V if the dependent object is a view. X if the dependent object is a package. Views can depend on tables and other views; packages can depend on any object.
TIMESTAMP	CHAR(8) NOT NULL	For packages, it is the value of the System/390 time of day clock when the package was created; the value is used internally and is represented as a string of numbers and letters. For views, it is the date when the view was created, in the format MM/DD/YY.

For each view or package defined, at least one entry is normally made in SYSUSAGE.

Note:

If you preprocess an application program that SELECTs an undefined table, a package will be defined, but no entry will be made in the SYSUSAGE table. In this case, the preprocessor will issue a warning.

If the view or package involves only one base object (for example, CREATE VIEW V AS SELECT * FROM EMP), then one entry is made, with BNAME being the name of that base object. If the view or package involves more than one base object, then an entry is made for each such base object involved. SYSUSAGE enables the database manager to find the packages and views that are affected if a given base object is dropped.

 SYS USERAUTH and  SYS USERLIST

The database manager uses SYSUSERAUTH to record system authorizations. The system authorizations are DBA, RESOURCE, SCHEDULE, and CONNECT authority. As in SYSTABAUTH, an entry in SYSUSERAUTH indicates either a system authorization held by a user or a special privilege exercised by a program.

Only users with DBA authority can access SYSUSERAUTH; other users must access the view SYSUSERLIST. The creator of the view is SQLDBA; thus, you must refer to the view as SQLDBA.SYSUSERLIST. The SYSUSERLIST view contains all columns of SYSUSERAUTH except PASSWORD. The columns in SYSUSERAUTH (and SYSUSERLIST) are:

Column Name	Data Type	Description and Comments
NAME	CHAR(8) NOT NULL	Either the user ID of a user, or the name of a program. The two possibilities are distinguished by the contents of the AUTHOR field: if AUTHOR is blank, this field contains a user ID; if not, it contains the name of a program, and AUTHOR contains the user ID of the creator of the program.
AUTHOR	CHAR(8) NOT NULL	This is blank (ignored) if NAME is the name of a DB2 Server for VSE & VM user; if NAME is the name of a program, then this field contains the user ID of the person who preprocessed the program.
RESOURCEAUTH	CHAR(1) NOT NULL	The possible values are: Y if this user is authorized to create new tables and authorized to acquire a private dbspace by issuing the ACQUIRE DBSPACE statement. blank otherwise.
DBAAUTH	CHAR(1) NOT NULL	The possible values are: Y if this user has DBA authority. blank otherwise. A user with DBA authority is entitled to see everything in the database, including the catalog tables, and may issue any SQL statement. A user with DBA authority may also acquire and drop PUBLIC DBSPACEs. Changes to the SYSUSERAUTH table may be made only by SQL statements issued by a user with DBA authority (there is at least one DBA at catalog generation time).
PASSWORD	CHAR(8) NOT NULL	This verifies the identity of a user on a CONNECT statement to a DB2 Server for VSE & VM system. It is updated using a GRANT CONNECT or GRANT DBA statement.
SCHEDULEAUTH	CHAR(1) NOT NULL	The possible values are: Y if this user is authorized to CONNECT another user without specifying a password. (Used for CICS support by the DB2 Server for VSE database manager.) blank otherwise.

 SYS VIEWS

The SYSVIEWS table contains the definitions of all views. The views are stored in the form of the original SQL statements that defined the views. The columns in SYSVIEWS are:

Column Name	Data Type	Description and Comments
VIEWNAME	VARCHAR(18) NOT NULL	The name of the view.
VCREATOR	CHAR(8) NOT NULL	The owner who defined the view. VCREATOR.VIEWNAME uniquely identifies the view.
SEQNO	SMALLINT NOT NULL	Because a view definition may consist of more than 254 characters, it may have to be divided among several rows of SYSVIEWS. The row that contains the first portion of a view definition has SEQNO = 1; successive rows have increasing values of SEQNO. You can use SEQNO to order the view definitions properly when you query this table.
VIEWTEXT	VARCHAR(254) NOT NULL	This contains the SQL statement that defined the view.
VIEWMAT	CHAR(1)	Indicates whether this view references another view resulting in view materialization. Possible values are: Y if a view materialization is involved. N if a view materialization is not involved. NULL if the view was created on an SQL/DS database prior to SQL/DS Version 3 Release 1 and subsequently migrated to a later version of the database manager.
VIEWCHECK	CHAR(1)	Indicates whether the view was created with the WITH CHECK OPTION clause. Possible values are: Y if a view was created with the clause N if a view was created without the clause NULL if a view was created prior to SQL/DS Version 3 Release 2.