This section focuses on the DB2 Universal Database Version 6 incompatibilities.
| 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.)
| UNIX | OS/2 | WIN |
|
Maximum possible size of VARCHAR (VARGRAPHIC) data type has increased from 4 000 characters (2 000 double bytes characters), to 32 672 characters (16 336 double bytes characters) in Version 6.
An application that uses fixed length buffers of 4 000 bytes for a VARCHAR (VARGRAPHIC) data type has the potential for buffer overwrite or truncation, if it fetches a VARCHAR field which is longer than 4 000 bytes into a buffer that is too small. The CLI function - SQLGetTypeInfo() now returns the size of VARCHAR as 32 672. CLI applications that use this value in table DDLs may get errors due to sufficient page size table spaces not being available. See User Table Data for more information on table space page size.
The application should be coded, in the recommended manner, of first describing the columns of the result set by using the DESCRIBE statement, and then using buffers whose size is based on the data type's length as returned from the DESCRIBE of the column.
| UNIX | OS/2 | WIN |
|
When programming using Java in Version 6, positioned UPDATE and DELETE statements use the default authorization identifier of the person that bound the cursor package. This is different from Version 5.2 where the authorization identifier of the person running the package was used.
The package containing the positioned UPDATE and DELETE statements may not run because the authorization identifier of the person who bound the package does not have sufficient authority.
The authorization identifier of the person who binds the package must be granted sufficient authority to run the positioned UPDATE and DELETE statements found in the package. Grant the correct privileges and then re-bind the package.
| UNIX | OS/2 | WIN |
|
In Version 5.2, in an SQLJ program, the FOR UPDATE clause can be used in a SELECT statement to identify the columns that can be updated in subsequent positioned UPDATE statements. The syntax is changed for Version 6.
You will receive the error message SQJ0204E if a SELECT statement contains a FOR UPDATE clause.
Remove the FOR UPDATE clause from the SELECT statement. Specify an updatable iterator through the iterator declaration clause. For example:
#sql public iterator DelByName implements sqlj.runtime.ForUpdate(String EmpNo)
with updateColumns = (salary);
If you want to explicitly identify what columns are updateable, specify them through the updateColumns keyword, used in conjunction with the WITH clause.
Refer to Application Development Guide for more information on positioned iterator declarations.
| UNIX | OS/2 | WIN | Extended |
DB2 Universal Database Version 6 supports 128 byte table/view/alias names and 30 byte column names. The former support was for 18 byte names for each of these entities.
USER and CURRENT SCHEMA special registers were CHAR(8) and are now VARCHAR(128). The CURRENT EXPLAIN MODE special register was CHAR(8) and is now VARCHAR(254). The output for TYPE_SCHEMA and TABLE_SCHEMA built-in functions were CHAR(8) and are now VARCHAR(128).
If applications that were developed before Version 6 are run against a Version 6 database that does not utilize the longer limits, then the application behavior should not change at all. However, running these applications against a Version 6 database that does utilize longer names, could result in certain side effects, depending on how these applications were coded.
Here are some examples:
The best way to resolve problems of this type is to re-code the application to handle longer table and column names. Otherwise, ensure that these applications are not run against Version 6 databases that use > 18 byte names.
| UNIX | OS/2 | WIN | Extended |
DB2 Universal Database Version 6 supports 128 byte table/view/alias names and 30 byte column names. The former support was for 18 byte names for each of these entities.
A DB2 Universal Database Version 5 client is not able to import a PC/IXF that was exported by a DB2 Universal Database Version 6 export client. The error message SQL3059N will result.
Also, a PC/IXF file (an export from a DB2 Universal Database Version 6 client) cannot be loaded into a DB2 Universal Database Version 5 database. The error message SQL3059N will result.
Always be aware of the version level of the PC/IXF file when running.
| UNIX | OS/2 | WIN | Extended |
DB2 Universal Database Version 6 supports 30 byte column names. The former support was for 18 byte names. In Version 5, the documented behavior was that "0xFF" is placed in the 30th byte of an SQLNAME field for a non-doubled SQLVAR. Also in Version 5, for system-generated names and for user-specified column names specified in an "AS" clause, "0x00" is placed in the 30th byte.
In Version 6, the new behavior only returns "0xFF" in the 30th column if the name is system-generated.
Any applications that rely on the 30th-byte of the SQLNAME to determine whether it is a user-specified column name or a system-generated name may receive unexpected logic checks if the user-specifed column name is 30 characters long. This should be a rare occurrence.
These applications should be modified to only check for "0xFF" in the 30th byte of the SQLNAME field if the length of the SQLNAME is less than 30. In this case, the name is user-generated.
| WIN |
When moving from version to version, you can change the behavior of the DB2 CLI/ODBC driver by specifying a set of optional keywords in the db2cli.ini file.
In Version 6, the TRANSLATEDLL and the TRANSLATEOPTION keywords became obsolete.
These keywords will be ignored if they still exist. You may notice behavioral changes based on the removal of these settings.
You will need to review the new list of valid parameters to decide what the appropriate keywords and settings are for your environment. See the CLI Guide and Reference for information on these keywords.
| UNIX | OS/2 | WIN | Extended |
Event monitor output streams have no version control. As a result, adding support for the greater than 18 byte table names requires the move to an output stream format.
Applications that parse the event monitor output streams will no longer function as in previous releases.
There are two options:
| UNIX |
|
|
Datalink values inserted in DB2 Universal Database Version 6 will require an extra four bytes of space in the column value descriptor.
When datalink columns created in Version 5.2 are updated, an additional four bytes are required on the data page to store the new column value. As a result, there may not be enough space in the data page to complete the update and it may have to be moved to a new page. This could cause the update to run out of space.
You will need to add more space on your system to allow for updates.
| UNIX | OS/2 | WIN | Extended |
A number of string functions in the SYSFUN schema now have improved versions defined in the SYSIBM schema (built-in functions). The function names are LCASE, LTRIM, RTRIM, and UCASE.
When preparing statements or creating views, the returned data type from any of these functions may be different than running the same statement on versions previous to Version 6. This occurs because the built-in functions (under the SYSIBM schema) are usually resolved before functions in the SYSFUN schema.
No action is required. Usually, the behavior of the new built-in function is desired over the function as found in the SYSFUN schema. The previous version behavior can be restored by switching the SQL path so that SYSFUN precedes SYSIBM, but performance of function resolution is degraded. Alternatively, the previous version function can be invoked by qualifying the function name with the schema name SYSFUN.
Migrated packages, views, summary tables, triggers, and constraints that reference these functions continue to use the version from the SYSFUN schema unless explicit action is taken such as explicitly binding the package or re-creating the view, summary table, trigger, or constraint.
| UNIX | OS/2 | WIN | Extended |
The "U" states in the CONST_CHECKED column of SYSCAT.TABLES changes differently when a SET INTEGRITY ... OFF statement is run.
Prior to Version 6, any "U" state in CONST_CHECKED column changes to an "N" when a SET INTEGRITY ... OFF statement is run. There is now another state, "W", to which the "U" state is changed.
No action is required.
The new "W" state in a CONST_CHECKED byte is used to indicate that the type of constraint was previously checked by the user and some data in the table may need to be checked for integrity.
Without this new state, the "U" state would be changed to the "N" state on a SET INTEGRITY ... OFF as it did in previous versions. From the "N" state alone, the database manager would not be sure if there exists any old data that has not yet been verified by the database manager. On a subsequent a SET INTEGRITY ... IMMEDIATE CHECKED INCREMENTAL statement, the database manager has to return an error. The error is returned because the database manager will not be able to guarantee data integrity if only the new change (if any) were checked.
With the new "W" state, on a subsequent a SET INTEGRITY ... IMMEDIATE CHECKED INCREMENTAL statement, the "W" state can be changed back to the "U" state if the INCREMENTAL option is specified to indicate that the user is still responsible for the data integrity of the table. If the INCREMENTAL option is not specified, the database manager will pick full processing, change the "W" state to a "Y" state, and re-assume the responsibility of maintaining data integrity.
| UNIX | OS/2 | WIN | Extended |
The method used by clients to create a database.
Using a down-level client (from a previous release than that of the server) to create a database will result in errors.
When using a client to create a database, only use clients at the same level as that of the server.
| UNIX | OS/2 | Windows 32-bit | Extended |
Specification of the ONLY keyword with a table now requires that the user have SELECT privilege on all subtables of the specified typed table. Similarly, specification of the ONLY keyword with a view now requires that the user have SELECT privilege on all subviews of a specified typed table. Previous versions only required SELECT privilege on the specified table or view.
There are two possible symptoms:
The authorization ID that needs to rebind a package or to crate a new view or trigger should be granted SELECT privilege on all subtables (and subviews) of the table (or view) specified following the ONLY keyword.
| UNIX | OS/2 | WIN | DB2 PE |
The following profile registry values or environment variables are obsolete:
There is no longer a need for these profile registry or environment variables.
| UNIX | OS/2 | WIN |
The type of the "CURRENT EXPLAIN MODE" special register has changed from CHAR(8) to VARCHAR(254).
Symptom: If the application assumes that the type is still CHAR(8), then the value may be truncated from 254 to 8 bytes.
Redefine the type of all host variables which read the special register from CHAR(8) to VARCHAR(254).
This change is required to accommodate two new values for the "CURRENT EXPLAIN MODE" special register. These new values are "EVALUATE INDEXES" and "RECOMMEND INDEXES".
| UNIX | OS/2 | WIN | DB2 PE |
As of Version 6, the USING and SORT BUFFER parameters of the LOAD command are no longer supported (the parameters are ignored).
If specified on the LOAD command, the use receives a warning message saying that USING and SORT BUFFER parameters are no longer supported and are ignored by the LOAD utility.
Ignore the warning message. See Data Movement Utilities Guide and Reference for additional information.
| WIN |
In Version 6, RUMBA is replaced by PCOMM on Windows NT, Windows 95, and Windows 98 only. RUMBA on Windows 3.1 will not be replaced.
None.
Windows 3.1 users will continue using RUMBA.
| UNIX | OS/2 | WIN |
The following database configuration parameters are obsolete with this version:
Applications should be updated to not reference these parameters.