| UNIX | OS/2 | WIN |
|
In the system catalog views, new codes have been introduced: "U" for typed tables, and "W" for typed views.
Queries that search for tables and views in the system catalogs, using the typecode "T" for tables and "V" for views, will no longer find typed tables and views.
Several system catalogs, including the system catalog views named TABLES, PACKAGEDEP, TRIGDEP, and VIEWDEP, have a column named TYPE or BTYPE containing a one-letter typecode. In Version 5.2, the typecode "T" was used for all tables, and "V" was used for all views. In Version 6, untyped tables will continue to have a typecode of "T" and typed tables will have a new typecode of "U". Similarly, untyped views will continue to have a typecode of "V" and typed views will have a new typecode of "W". Also, a new kind of table called a hierarchy table, not directly created by users but used by the system to implement table hierarchies, will appear in the system catalog tables with a typecode of "H".
Change the tool or application to recognize the codes for typed tables and views. If the tool or application needs a logical view of tables, then typecodes "T", "U", "V", and "W" should be used. If the tool or application needs a physical view of tables, including hierarchy tables, then typecodes "T" and "H" should be used.
| UNIX | OS/2 | WIN |
|
Data type change to two SYSCAT.REFERENCES columns, PK_COLNAMES and FK_COLNAMES, from VARCHAR(320) to VARCHAR(640).
Primary key and/or foreign key column names are truncated, are not correct, or are missing.
When column names greater than 18 bytes in length are used in a primary key or foreign key, the format under which the list of column names are stored in these two columns cannot remain the same. The 20-byte blank delimited column name(s) coming after the column whose length is greater than 18 will be shifted to the right the number of bytes that the column whose length is greater than 18 is over 18 bytes. As well, if the list of column names exceeds 640 bytes, the column will contain the empty string.
The view SYSCAT.KEYCOLUSE contains the list of columns that make up a primary, foreign, as well as a unique key and should be used instead of the columns in SYSCAT.REFERENCES. Alternatively, users can restrict the length of column names to 18 bytes or restrict the total length of the list of columns to 640 bytes.
| UNIX | OS/2 | WIN |
|
View text in the SYSCAT.VIEWS column TEXT will now not be split across multiple rows. The data type is changed from VARCHAR(3600) to CLOB(64K).
The complete view text is not given by the tool or in the application.
Tools or applications which were coded expecting no more than 3600 (or perhaps 3900) bytes to be returned from the TEXT column at one time are not handling the increased size of this field. The mechanism of retrieving multiple rows and reconstructing the view text using the SEQNO field is no longer necessary. The SEQNO value will now only ever be 1.
Change the tool or application to be able to handle values from the TEXT column which are greater than 3600 bytes. Alternatively, the view TEXT could be rewritten to fit within 3600 bytes.
| UNIX | OS/2 | WIN |
|
Statement text in the SYSCAT.STATEMENTS column TEXT will now not be split across multiple rows. The data type is changed from VARCHAR(3600) to CLOB(64K).
The complete statement text is not given by the tool or in the application.
Tools or applications which were coded expecting no more than 3600 (or perhaps 3900) bytes to be returned from the TEXT column at one time are not handling the increased size of this field. The mechanism of retrieving multiple rows and reconstructing the statement text using the SEQNO field is no longer necessary. The SEQNO value will now only ever be 1.
Change the tool or application to be able to handle values from the TEXT column which are greater than 3600 bytes. Alternatively, the statement TEXT could be rewritten to fit within 3600 bytes.
| UNIX | OS/2 | WIN |
|
The SYSCAT.INDEXES column COLNAMES data type is changed from VARCHAR(320) to VARCHAR(640).
Column names are missing in an index.
Tools or applications are coded to retrieve from a column with a data type of VARCHAR(320) and cannot handle the increased size of this field.
The view SYSCAT.INDEXCOLUSE contains the list of columns that make up an index and should be used instead of the column COLNAMES. Alternatively, remove a column from the index or reduce the size of the name of a column so that the list of column names (with the leading + or -) will fit in 320 bytes.
| UNIX | OS/2 | WIN |
|
CHECKS Column TEXT data type is changed from CLOB(32K) to CLOB(64K).
Check constraint clause is incomplete.
Tools or applications are coded to retrieve from a column with a data type of CLOB(32K) and cannot handle the increased size of this field.
Change the tool or application to be able to handle values from the TEXT column which are longer than 32K bytes. Alternatively, rewrite the check constraint clause to use fewer characters such that it will fit in 32K bytes.
| UNIX | OS/2 | WIN |
|
Several system catalog view columns have had their data type changed from INTEGER to BIGINT.
Values are much smaller (or larger) than expected, especially statistical information.
Tools or applications are coded to retrieve from a column with a data type of INTEGER and cannot handle the increased size of this field.
Change the tool or application to be able to handle values which are greater than the maximum or minimum value which can be stored in an INTEGER field. Alternatively, change the underlying structure or SQL code which causes the value to be greater than what can be represented in an INTEGER field.
| UNIX | OS/2 | WIN |
|
New columns are not inserted at the end of views in the SYSCAT view definition.
Re-preprocessing fails with several column mismatches or column data type mismatches.
New columns are introduced to the system catalog views and placed in a position that is useful in an ad-hoc query environment, specifically, shorter columns are placed before very long columns and the REMARKS column is always the last one.
Explicitly name the columns in the select list instead of coding "SELECT *".
| UNIX | OS/2 | WIN |
|
SYSCAT.COLUMNS and SYSCAT.ATTRIBUTES now contain entries for inherited columns and attributes.
Queries of SYSCAT.COLUMNS to retrieve the columns of a typed table or view, and queries of SYSCAT.ATTRIBUTES to retrieve the attributes of a structured type, may return more rows in V6 than in V5.2, if the subject of the query is a subtable, subview, or subtype.
In Version 5.2, for a given table, view, or structured type, the COLUMNS and ATTRIBUTES catalogs contained entries only for columns and attributes that were introduced by that table, view, or type. Columns and attributes that were inherited from supertables or supertypes were not represented in the catalogs. However, in V6, the COLUMNS and ATTRIBUTES catalogs will contain entries for inherited columns and attributes.
Change the tool or application to recognize the new entries in the COLUMNS and ATTRIBUTES catalogs.
| UNIX | OS/2 | WIN | Extended |
The recursive catalog views in the OBJCAT schema of Version 5.2 are no longer part of the shipped DB2 Universal Database product.
Queries written against the OBJCAT catalog views will no longer run successfully.
Most of the information formerly in the OBJCAT views has been incorporated into the regular SYSCAT catalog views. In most cases, you can obtain the information from the system catalog views. If you migrate from Version 5.2, and the OBJCAT catalog views exist, they should be dropped. This can be done by executing the CLP script called objcatdp.db2 found under the misc subdirectory of the sqllib subdirectory.
If you wish, you could also create your own set of OBJCAT views that are equivalent to the catalog views supported in Version 5.2.
In version 5.2, the SQL Reference in Appendix E warned users that the OBJCAT catalog views were temporary and would not be supported in future releases.
| UNIX | OS/2 | WIN | Extended |
In the system catalog views, the hierarchic dependencies formerly denoted by code "H" are now denoted by code "O".
Queries that search for hierarchic dependencies by code "H" in the catalog views will no longer work correctly.
Several system catalogs, including the system catalog views named PACKAGEDEP, TRIGDEP, and VIEWDEP have a column named BTYPE. In Version 5.2, the OBJCAT views denoted hierarchic dependencies with the code "H". In Version 6, these dependencies are denoted with the code "O".
These queries will need to be revised to search for code "O".
| UNIX | OS/2 | WIN | Extended |
The following are changes made to the SYSIBM base catalog tables, which customers may be using despite our encouragement for them to use the SYSCAT views. Customers who are not following our recommendation of coding to the SYSCAT views may experience incompatibilities due to the following changes:
(Was in the view, but reserved for future use only.)