Administration Guide

System Catalog Tables/Views

System Catalog Views

Change

A set of views have been created in DB2 Version 2 with the qualifiers (also known as schema names) of SYSCAT and SYSSTAT. For this reason, the SYSCAT and SYSSTAT schemas are now reserved.

Symptom

If there are any objects belonging to these schemas in a Version 1 database, migration will fail with SQLCODE SQL1704N (reason code 1).

Resolution

The only way to get through the migration successfully will be to recreate the objects currently under the SYSCAT and SYSSTAT schemas under new high level qualifiers.

System Catalog Tables

Change

A variety of changes have been made to the SYSIBM tables. This section will discuss the subset which could cause incompatibilities. To see a description of all changes (for example, new columns, new values in a column, and so on) refer to the SQL Reference.

SYSCOLUMNS

COLTYPE:

Changed values: "FLOAT" to "DOUBLE"

NULLS:

Changed values: "D" to "N". (Default flag now found in SYSCAT.COLUMNS.DEFAULT)

HIGH2KEY:

Changed type: VARCHAR(16) to VARCHAR(33). Changed values: Values now stored in printable format rather than binary format

LOW2KEY:

Changed type: VARCHAR(16) to VARCHAR(33). Changed values: Values are now stored in printable format rather than binary format for all datatypes.

SYSINDEXES

CLUSTERRATIO:

Changed value: Value will always be -1 if the columns CLUSTERFACTOR and PAGE_FETCH_PAIRS are populated.

SECT_INFO:

Changed type: LONG VARCHAR to BLOB(1M).

HOST_VARS:

Changed type: LONG VARCHAR to BLOB(1M).

ISOLATION:

Changed type: CHAR(1) to CHAR(2). Changed values: "R" to "RR", "S" to "RS", "C" to "CS", "U" to "UR".

SYSRELS

RELNAME:

Changed type: CHAR(8) to VARCHAR(18).

SYSSECTION

SECTION:

Changed type: VARCHAR(3900) to VARCHAR(3600)

SYSSTMT

TEXT:

Changed type: VARCHAR(3900) to VARCHAR(3600)

SYSTABLES

PACKED_DESC:

Changed type: LONG VARCHAR to BLOB(10M)

VIEW_DESC:

Changed type: LONG VARCHAR to BLOB(32K)

REL_DESC

Changed type: LONG VARCHAR to BLOB(32K)

FID

Will no longer uniquely identify a table on its own. Must be used with TID to uniquely identify a table.

SYSVIEWS

CHECK:

Changed values: "Y" to "L".

TEXT:

Changed type: VARCHAR(3900) to VARCHAR(3600). Contains the full text of the create view statement (including the CREATE VIEW). In Version 1, only the select portion was shown.

Symptom

A variety of symptoms could occur.

If you have an application which has a qualified search on a field that has had a value change (for example, ISOLATION in SYSIBM.SYSPLAN) this will cause your application to react differently than you would want.

If you have an application which accesses some field where the field type or size has changed (such as SECTION in SYSIBM.SYSSECTION), you could retrieve an incomplete set of data, too much data, or have the wrong type defined in your application to represent the data type of the table column.

Resolution

If you use the SYSIBM tables for application processing or anything else, you must review the changes listed above to decide whether or not you are affected and what the appropriate action to correct the situation is. You may need to refer to the SQL Reference to understand what the new columns, new values for columns and other changes that were made to these tables.

If you need a rough approximation of the degree of clustering, select both CLUSTERRATIO and CLUSTERFACTOR and choose the "greater" one.

Unique Table Identification

Change

With the introduction of table spaces, the TID column of SYSIBM.SYSTABLES is now used to identify a table space. The FID column of SYSIBM.SYSTABLES will no longer uniquely identify a table is a database. FID now uniquely identifies a table in a table space. This means that to uniquely identify a table in a database you need the combination of TID and FID.

Symptom

Any application which assumes FID will uniquely identify a table in a database may process incorrectly should the FID be duplicated in multiple table spaces.

Resolution

Change the application to use TBSPACEID and TABLEID from the SYSCAT.TABLES view as the unique identifier. You can also use the columns TID and FID from SYSIBM.SYSTABLES.

[ DB2 List of Books | Search the DB2 Books ]