This section identifies incompatibilities introduced in DB2 Universal Database Version 6.
| WIN | UNIX | OS/2 |
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 type code "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 type code. In Version 5.2, the type code "T" was used for all tables, and "V" was used for all views. In Version 6, untyped tables will continue to have a type code of "T" and typed tables will have a new type code of "U". Similarly, untyped views will continue to have a type code of "V" and typed views will have a new type code 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 type code 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 type codes "T", "U", "V", and "W" should be used. If the tool or application needs a physical view of tables, including hierarchy tables, then type codes "T" and "H" should be used.
| WIN | UNIX | OS/2 |
Data type change to two SYSCAT.REFERENCES columns, PK_COLNAMES and FK_COLNAMES, from VARCHAR(320) to VARCHAR(640).
Primary key 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 a 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 names following the column whose length (n) is greater than 18 will be shifted n-18 bytes to the right. As well, if the list of column names exceeds 640 bytes, the column will contain the empty string.
The SYSCAT.KEYCOLUSE view 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.
| WIN | UNIX | OS/2 |
View text in the SYSCAT.VIEWS column TEXT will no longer 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 the application.
Tools or applications that were coded to expect no more than 3600 (or perhaps 3900) bytes returned from the TEXT column at one time are not handling the increased size of this field. The mechanism for retrieving multiple rows and reconstructing the view text using the SEQNO field is no longer necessary. The SEQNO value will always be 1.
Change the tool or application to be able to handle values from the TEXT column that are greater than 3600 bytes. Alternatively, the view TEXT could be rewritten to fit within 3600 bytes.
| WIN | UNIX | OS/2 |
Statement text in the SYSCAT.STATEMENTS column TEXT will no longer 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 the application.
Tools or applications that were coded to expect no more than 3600 (or perhaps 3900) bytes returned from the TEXT column at one time are not handling the increased size of this field. The mechanism for retrieving multiple rows and reconstructing the statement text using the SEQNO field is no longer necessary. The SEQNO value will always be 1.
Change the tool or application to be able to handle values from the TEXT column that are greater than 3600 bytes. Alternatively, the statement TEXT could be rewritten to fit within 3600 bytes.
| WIN | UNIX | OS/2 |
The SYSCAT.INDEXES column COLNAMES data type is changed from VARCHAR(320) to VARCHAR(640).
Column names are missing from an index.
Tools or applications coded to retrieve data from a column with data type VARCHAR(320) cannot handle the increased size of this field.
The SYSCAT.INDEXCOLUSE view contains the list of columns that make up an index, and should be used instead of the COLNAMES column. Alternatively, remove a column from the index, or reduce the size of the column name, so that the list of column names (with the leading + or -) will fit within 320 bytes.
| WIN | UNIX | OS/2 |
CHECKS Column TEXT data type is changed from CLOB(32K) to CLOB(64K).
Check constraint clause is incomplete.
Tools or applications coded to retrieve data from a column with data type CLOB(32K) cannot handle the increased size of this field.
Change the tool or application to be able to handle values from the TEXT column that are longer than 32 KB. Alternatively, rewrite the check constraint clause to use fewer characters, so that it will fit within 32 KB.
| WIN | UNIX | OS/2 |
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 coded to retrieve data from a column with data type INTEGER cannot handle the increased size of this field.
Change the tool or application to be able to handle values that are greater than the maximum, or less than the minimum value that can be stored in an INTEGER field. Alternatively, change the underlying structure or SQL code that causes the value to fall outside of what can be represented in an INTEGER field.
| WIN | UNIX | OS/2 |
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 last.
Explicitly name the columns in the select list instead of coding "SELECT *".
| WIN | UNIX | OS/2 |
SYSCAT.COLUMNS and SYSCAT.ATTRIBUTES now contain entries for inherited columns and attributes.
Queries against SYSCAT.COLUMNS to retrieve the columns of a typed table or view, and queries against SYSCAT.ATTRIBUTES to retrieve the attributes of a structured type, may return more rows in Version 6 than in Version 5.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 Version 6, 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.
| WIN | UNIX | OS/2 |
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 running the CLP script called objcatdp.db2, found under the misc subdirectory of the sqllib directory.
You can also create your own set of OBJCAT views that are equivalent to the catalog views supported in Version 5.2.
In version 5.2, "Appendix E" of the SQL Reference warned users that the OBJCAT catalog views were temporary, and would not be supported in future releases.
| WIN | UNIX | OS/2 |
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 by code "H". In Version 6, these dependencies are denoted by code "O".
Revise these queries to search for code "O".
| WIN | UNIX | OS/2 |
Following are changes to the SYSIBM base catalog tables, which you may still be using instead of the SYSCAT views:
(Was in the view, but reserved for future use only.)
| WIN | UNIX | OS/2 |
Maximum possible size of VARCHAR (VARGRAPHIC) data type has increased from 4000 characters (2000 double byte characters) to 32672 characters (16336 double byte characters) in Version 6.
An application that uses fixed length buffers of 4000 bytes for a VARCHAR (VARGRAPHIC) data type has the potential for buffer overwrite or truncation, if it fetches a VARCHAR field that is longer than 4000 bytes into a buffer that is too small. The CLI function - SQLGetTypeInfo() now returns the size of VARCHAR as 32672. CLI applications that use this value in table DDLs may get errors because table spaces of sufficient page size are not available. For more information about table space page size, see User Table Data.
When coding the application, it is recommended that you first describe the columns of the result set (using the DESCRIBE statement), and then use buffers whose size is based on the length returned from the DESCRIBE statement.
| WIN | UNIX | OS/2 |
When programming 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, in which 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 in the package. Grant the correct privileges and then rebind the package.
| WIN | UNIX | OS/2 |
In Version 5.2, the FOR UPDATE clause in a SELECT statement can be used in an SQLJ program to identify the columns that can be updated in subsequent positioned UPDATE statements. The syntax has 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.
For more information about positioned iterator declarations, refer to the Application Development Guide.
| WIN | UNIX | OS/2 |
DB2 Universal Database Version 6 supports 128-byte table, view, and alias names, and 30-byte column names. Previous 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 was CHAR(8), and is now VARCHAR(128).
If applications that were developed before Version 6 are run against a Version 6 database that does not use the longer limits, application behavior should not change at all. However, running these applications against a Version 6 database that does use longer names could result in certain side effects, depending on how these applications were coded.
Following are some examples:
The best way to resolve problems of this type is to recode the application to handle longer table and column names. Otherwise, ensure that these applications are not run against Version 6 databases that use greater than 18-byte names.
| WIN | UNIX | OS/2 |
DB2 Universal Database Version 6 supports 128-byte table, view, and alias names, and 30-byte column names. Previous support was for 18-byte names for each of these entities.
A DB2 Universal Database Version 5 client cannot import a PC/IXF file that was exported by a DB2 Universal Database Version 6 client (error SQL3059N). A PC/IXF file (exported from a DB2 Universal Database Version 6 client) cannot be loaded into a DB2 Universal Database Version 5 database (error SQL3059N).
Use compatible versions of DB2 Universal Database when importing or loading PC/IXF data.
| WIN | UNIX | OS/2 |
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; for system-generated names and for user-specified column names specified in an "AS" clause, "0x00" is also placed in the 30th byte.
In Version 6, "0xFF" is returned in the 30th byte only if the name is system-generated.
Any applications that rely on the 30th byte of the SQLNAME field 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 that field is less than 30. In this case, the name is user-generated.
| WIN |
|
|
When migrating to a new version of DB2 UDB, 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 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. For information about these keywords, refer to the CLI Guide and Reference.
| WIN | UNIX | OS/2 |
Event monitor output streams have no version control. As a result, adding support for greater than 18-byte table names requires moving to an output stream format.
Applications that parse the event monitor output streams will no longer work properly.
There are two options:
DB2OLDEVMON=evmonname1,evmonname2,...where evmonname is the name of the event monitor that you want written in the old data format. Note that any new fields in the event monitor will not be accessible under the old data format.
|
| UNIX |
|
DATALINK values inserted under 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.
| WIN | UNIX | OS/2 |
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 in Version 6. This occurs because the built-in functions (under the SYSIBM schema) are usually resolved before functions in the SYSFUN schema are resolved.
No action is required. The built-in function is usually preferred over the function in the SYSFUN schema. The previous version behavior can be restored by switching the SQL path (so that SYSFUN precedes SYSIBM), but performance will be degraded. The previous version function can also 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 recreating the view, summary table, trigger, or constraint.
| WIN | UNIX | OS/2 |
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 the CONST_CHECKED column changed to an "N" state when a SET INTEGRITY ... OFF statement was run. The "U" state now changes to a "W" state.
No action is required. The new "W" state in the CONST_CHECKED column is used to indicate that the constraints type was previously checked by the user, and that some data in the table may need to be checked for integrity.
The "N" state does not clarify whether there exists any old data that has not yet been verified by the database manager. On a subsequent SET INTEGRITY ... IMMEDIATE CHECKED INCREMENTAL statement, the database manager must return an error, because data integrity cannot be guaranteed if only new changes have been checked. On the other hand, 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 integrity of data in the table. If the INCREMENTAL option is not specified, the database manager will choose full processing, change the "W" state to a "Y" state, and assume responsibility for maintaining data integrity.
| WIN | UNIX | OS/2 |
The method used by clients to create a database.
Using a back-level client to create a database will result in errors.
When using a client to create a database, ensure that the client and the server are running the same level of DB2 code.
| WIN 32-bit | UNIX | OS/2 |
Specification of the ONLY keyword (for a table) now requires that the user have SELECT privilege on all subtables of the specified typed table. Similarly, specification of the ONLY keyword (for a view) now requires that the user have SELECT privilege on all subviews of the specified typed table. Previous versions of DB2 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 create 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.
| WIN | UNIX | OS/2 |
The following profile registry or environment variables are obsolete:
These variables are no longer needed.
| WIN | UNIX | OS/2 |
The type of the "CURRENT EXPLAIN MODE" special register has changed from CHAR(8) to VARCHAR(254).
If the application assumes that the type is still CHAR(8), the value may be truncated from 254 to 8 bytes.
Redefine the type of all host variables that 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".
| WIN | UNIX | OS/2 |
As of Version 6, the USING and SORT BUFFER parameters of the LOAD command are no longer supported. These parameters are ignored.
A warning message is returned, stating that the USING and SORT BUFFER parameters are no longer supported, and will be ignored by the load utility.
Ignore the warning message. For additional information, refer to the Data Movement Utilities Guide and Reference.
| WIN |
|
|
In Version 6, RUMBA is replaced by PCOMM on Windows NT, Windows 98, and Windows 95 (but not on Windows 3.1).
None.
None.
| WIN | UNIX | OS/2 |
The following database configuration parameters are obsolete:
Remove all references to these parameters from your applications.