This section focuses on the DB2 Universal Database Version 5 incompatibilities.
| UNIX | OS/2 | WIN |
A variety of changes have been made to the system catalog views. 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.
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.
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.
| UNIX | OS/2 | WIN | Extended |
In Version 5.2, the user has been given explicit control over scratchpad duration.
For User Defined Functions (UDFs) which are table functions:
Or, you can elect to have the scratchpad content be preserved across OPEN calls, by specifying FINAL CALL. If you do this, you need not release resources during the CLOSE call processing, because a FINAL call will be made to the table function at end-of-statement. The FINAL CALL is new with this change, as is the balancing FIRST call which takes place before the first OPEN call. These two new call types occur only on table functions which specify FINAL CALL.
The introduction of new call types may generate unexpected calls and may result in bad results being returned from the function depending on how the UDF is written to examine the call-type arguments.
Customers who have existing table functions should examine them knowing the changes introduced by the new call types and the new execution model. The following changes should be considered:
Refer to the Application Development Guide for detailed information on managing these changes.
| UNIX | OS/2 | WIN | DB2 PE |
Due to the addition of NS and NX lock modes in DB2 Version 5, there is a difference in the behavior of index scans with isolation level Cursor Stability (CS) or Read Stability (RS).
In DB2 Version 5, an index scan with isolation level, CS or RS, will not see an uncommitted delete of a row that is within the scanned range. In DB2 Version 2, the scanner would not see an uncommitted delete of a row that was at the end of the scanned range. However, if the deleted row was within the range, the scanner would remain in a lock wait until the delete was committed or rolled back.
For example in DB2 Version 5, the following can occur with an index on
Column A:
| Sequence | Application 1 | Application 2 |
|---|---|---|
| 1 | delete from t1 where a=3 |
|
| 2 |
| select a from t1 where a>1 and a<5
A
-------------
2
4
5
|
| 3 | rollback |
|
| 4 |
| select a from t1 where a>1 and a<5
A
-------------
2
3
4
5
|
The same scenario in previous versions of DB2 would result in application 2 being in lock wait until Application 1 committed or rolled back.
There is no resolution as this is an enhancement to isolation level Cursor Stability or Read Stability.
Symptom: The previous example showed what occurs with an uncommitted deletion. A similar situation could also arise when inserting new values.
For example, you could have a scenario where you are scanning a table using an index on a column and looking for a value greater than or equal to two, but less than or equal to six, while using an isolation level of RS. The existing values that qualify in this example are two, four, and six. Then another user inserts five. An NS lock is obtained on columns returning two, four, and six; and the NW lock attempt on the column containing six succeeds, so the insertion of five is not blocked by the scan.
In Version 2, an S lock would be obtained on columns with the values two, four, and six; and the attempt to get an X lock on the column returning six would wait. The insert of five would wait for the S lock on six to be released.
Resolution: In general, since more concurrency is supported in Version 5, applications built with a previous version of DB2 that were created with dependencies on some lock waiting may require modification.
| UNIX | OS/2 | WIN |
When moving from Version 2 to Version 5, the default values for AUTOCOMMIT and CURSORHOLD have changed. Both AUTOCOMMIT and CURSORHOLD will now default to ON.
If an application was written assuming that AUTOCOMMIT was OFF or that WITH HOLD semantics was NOT used for cursors, then these default changes could cause the application to fail.
Add one or both of the following two lines to your DB2CLI.INI file.
| UNIX | OS/2 | WIN |
When moving from Version 2 to Version 5, a more explicit set of SQLSTATEs (in the S1090 to S1110 range) has replaced the generic SQLSTATE S1009.
SQLSTATE values returned to the application calling DB2 CLI APIs have changed.
Update your application to check for the new SQLSTATEs. Refer to the Message Reference for a complete list of these SQLSTATEs.
| UNIX | OS/2 | WIN |
When moving from Version 2 to Version 5, the default value for the CLI/ODBC configuration keyword DEFERREDPREPARE has changed. In DB2 CLI Version 5 deferred prepare is now on by default.
Applications that rely on the prepare to be executed as soon as it is issued will not function as expected. In particular, the row and cost estimates normally returned in the SQLERRD(3) and SQLERRD(4) of the SQLCA of a prepare statement may become zeros. The application will not be able to use this information to decide whether or not to continue the execution of the SQL statement.
Add the following line to your db2cli.ini file:
DEFERREDPREPARE = 0
| UNIX | OS/2 | 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 5, the AUTOCOMMIT keyword 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 |
When moving from Version 2 to Version 5, the category of SQLSTATEs that started with S1 in DB2 CLI Version 2 have been renamed to begin with HY in Version 5. For example, the SQLSTATE S1010 is now HY010.
SQLSTATE values returned to the application calling DB2 CLI APIs have changed.
Applications should be updated to expect the new HY class of SQLSTATEs. Alternatively, the environment attribute SQL_ATTR_ODBC_VERSION can be set to SQL_OV_ODBC2 using the DB2 CLI function SQLSetEnvAttr(). The DB2 CLI/ODBC driver will then return the S1 class of SQLSTATEs.
| UNIX | OS/2 | WIN |
When moving from Version 2 to Version 5, Version 5 now has 2 system catalog views used to store information about all the stored procedures on the server (SYSCAT.PROCEDURES and SYSCAT.PROCPARMS). These replace the Version 2 pseudo catalog table for stored procedures
By default the server will look in the new system catalog views for information about stored procedures, not the older pseudo catalog table. DB2 CLI functions such as SQLProcedureColumns() and SQLProcedures() will therefore not return the appropriate information.
Register the stored procedures using the CREATE PROCEDURE SQL command. See the SQL Reference for more information. Alternatively, the DB2 CLI/ODBC configuration keyword PATCH1 can be set to 262144 to force the DB2 CLI/ODBC driver to use the pseudo catalog table as it did in Version 2.
| UNIX | OS/2 | WIN |
Integer constants in the range -32,768 to 32,767 are now treated as INTEGER types, rather than SMALLINT. This resolves an incompatibility with IBM SQL, as well as simplifying the rules for determining literal types.
It is also worth mentioning that the smallest INTEGER constant in Version 1 (-2 147 483 648) is a DECIMAL constant with a precision of 10 and a scale of 0 in Version 5.
Further, the smallest literal representation of a large INTEGER constant is -2 147 483 647 and not -2 147 483 648 (which is the limit for large INTEGER values). The INTEGER constant -2 147 483 648 is a BIGINT, not a DECIMAL (as it was before Version 5.2).
In general, if an integer constant is outside the range of a large integer and within the range of a BIGINT, then it is a BIGINT. If it is too big for a BIGINT, then it is a DECIMAL.
This affects the result precision and scale of decimal operations. (Which impacts, for example, the print width of decimal fields.)
There is no resolution. This change in handling integers results in an increase in precision.
| UNIX | OS/2 | WIN | DB2 PE |
A distinct type based on BIGINT in a Version 5.2 server is reported in a DESCRIBE to a down-level client as a DECIMAL(19,0) instead of as a BIGINT which is not supported by the client. This data type cannot be implicitly cast on assignment to the distinct type on which it is based. This is different than other situations where the client perceives a distinct type as a built-in data type and is able to assign host variables of the built-in type to columns of the associated distinct type.
An SQLCODE of -408 (SQLSTATE 42821) is returned when using a data type of DECIMAL(19,0) for the host variable (or parameter marker) assigned to the distinct type value that was described to the down-level client as DECIMAL(19,0).
The database should include a function that will cast a DECIMAL(19,0) to the distinct type. This can be defined as a sourced function based on the function that casts a BIGINT to the distinct type. The application (at the client) must then explicitly apply this function to the DECIMAL(19,0) host variable (or parameter marker) in the INSERT or UPDATE statement.
For example, if the distinct type sourced on BIGINT is called DT1, then updating the column C1 of type DT1 would require the following sourced function to be defined:
CREATE FUNCTION DT1(DECIMAL(19,0)) RETURNS DT1 SOURCE DT1(BININT);
And then the update statement in the application would be:
UPDATE table SET c1=DT1(:dechv1);
| UNIX | OS/2 | WIN |
The limit for the maximum number of sections in a package has changed from 400 to whatever the storage allows. This limit used to be hard-coded at 400, but now depends on the type of SQL statements in the program. As a result of this change, the constant for the maximum number of SQL statements has been removed from the common include files sql.h, sql.cbl, and sql.f.
Symptom: If an application program references the following constants, it will not compile successfully in Version 5:
Remove references to these constants or define them directly within your application.
| UNIX | OS/2 | WIN |
The new SQLWARN bind option has a default value of 'YES'.
By default, positive SQLCODEs may now be returned on DESCRIBE, PREPARE, and EXECUTE IMMEDIATE requests which were previously not returned. (For instance, a SQLCODE of +236 may be returned).
Rebind with SQLWARN NO if your application cannot tolerate positive SQLCODEs or treats them as errors.
| UNIX | OS/2 | WIN |
The supported level of the JDBC (Java Support) API has changed. DB2 Version 5 provides a driver for JDBC 1.1 instead of JDBC 1.0, which came with DB2 Version 2.1.2.
Compiled JDBC 1.0 clients fail when executed directly as a DB2 Version 5 client. Old Java classes are not found.
To continue using JDBC 1.0 clients, run them on a DB2 Version 2.1.2 client, with a remote DB2 Version 5 server. Modify the client source code to upgrade to the JDBC 1.1 API. Run the JDBC 1.1 clients on a Java Development Kit Version 1.1-compatible virtual machine.
| UNIX | OS/2 | WIN |
The level of the Java runtime environment required for Java stored procedures, user-defined functions, and JDBC clients has changed in DB2 Version 5.
The JDBC DLL will not load when JDBC 1.1 clients are run. Java stored procedures and UDFs will fail.
Install a compatible Java 1.1.2 or Java 1.2 runtime environment at the client and server. At the server, set the jdk11_path configuration parameter.
| UNIX | OS/2 | WIN | DB2 PE |
In DB2 PE Version 1.2, partitioning key columns could be updated if the table was in a single-node nodegroup. In DB2 Version 5, partitioning key columns can be updated if the table is in a table space in a single-node nodegroup, and there is no partitioning key defined.
An update statement fails with SQL270N (SQLCODE -270, SQLSTATE 42997) with reason code 2. The same error is returned if the table is in a table space in a single or multiple node nodegroup.
If the table is in a table space in a single node nodegroup, then use the ALTER TABLE statement to DROP the partitioning key. As with DB2 PE Version 1.2, if the table is in a table space in a multiple node nodegroup, the nodegroup must be changed to a single-node nodegroup and REDISTRIBUTE NODEGROUP must be issued before attempting to update partitioning key columns.
| UNIX | OS/2 | WIN | DB2 PE |
In DB2 PE Version 1.2, a table was directly associated with a nodegroup. In DB2 Version 5, a table is in a table space, which is within a nodegroup. Since there is no longer a direct relationship with a nodegroup, there is no need for a column, named NGNAME in the SYSIBM.SYSTABLES catalog table.
An SQL statement that refers to the NGNAME column from SYSIBM.SYSTABLES catalog table will return an SQLCODE of -206 (SQLSTATE 42703).
Remove the column NGNAME from the SQL statement. To determine the nodegroup name for the table, refer to NGNAME in the row of SYSCAT.TABLESPACES catalog view, that relates to the table space in which the table is stored.
| UNIX | OS/2 | WIN | DB2 PE |
When moving from Parallel Edition Version 1. 2 to DB2 Universal Database Version 5, and using a temporary table that requires row identifiers, the amount of space needed is increased to include the node number. The space limit for temporary tables is 4 005 bytes. If temporary tables are close to the 4 005 byte limit, any further increase can exceed this limit.
There are two possible symptoms of this change.
You should review and use the directions in the Actions section of the message details for SQL0670N to fix the error.
| UNIX | OS/2 | WIN | DB2 PE |
In Version 5, the authorization required for creating or dropping a nodegroup changed from SYSADM or DBADM to SYSADM or SYSCTRL. A user ID with DBADM authority cannot create, alter, or drop nodegroups.
A user ID, with DBADM authority, issuing a CREATE NODEGROUP or DROP NODEGROUP statement will receive an SQL00551N (SQLSTATE 42501).
Issue the statement using a user ID that has SYSADM or SYSCTRL authority. For your convenience, you may wish to include the user ID in the SYSCTRL group. Refer to the Administration Guide for further information.
| UNIX | OS/2 | WIN | DB2 PE |
Beginning in Version 5, the specification of a target map in the REDISTRIBUTE NODEGROUP command or API no longer causes database partitions to be implicitly added or dropped from the node group. This means that the target map cannot include nodes that are not defined to the node group. An undefined node that is included in the target map file will cause an error to be returned. A database partition, which has been defined to the node group, can be excluded from the target map file and will not appear in the partition map.
If a node is included in the target map file and was not defined to the node group, the REDISTRIBUTE NODEGROUP command will return an SQLCODE-6053 with a reason code 6.
Before issuing the REDISTRIBUTE NODEGROUP command, add the database partition to the node group, using the ALTER NODEGROUP statement. You can also drop the node from the node group using the ALTER NODEGROUP statement, either before or after issuing the REDISTRIBUTE NODEGROUP command. Refer to the SQL Reference for further information on the ALTER NODEGROUP statement.
| UNIX | OS/2 | WIN | DB2 PE |
In DB2 PE Version 1, a table was directly associated with a node group. In DB2 Version 5, a table is in a table space within a node group. When a user issues a CREATE TABLE statement, the name following the IN keyword is a table space name, not a node group name. The default table space selected may not be defined in the IBMDEFAULTGROUP node group, which was the default node group in DB2 PE Version 1.
If you use existing CREATE TABLE statements from DB2 PE Version 1, they may fail with an SQLCODE of SQL0204N (SQLSTATE 42704), with the name specified following the IN keyword in the message. This will occur if a table space with the same name as the node group has not been automatically created during database migration.
If you are using CREATE TABLE statements that do not specify the IN keyword, the table space selected, by default, may not be using the node group, IBMDEFAULTGROUP, and will not include data on all the database nodes. You can check the partition map for the table to confirm this.
Ensure that any name specified following the IN keyword on the CREATE TABLE statement is the name of a defined table space. For existing statements, you could set up a table space for each node group with the same name.
To ensure that tables default to the IBMDEFAULTGROUP for all users, define a table space called IBMDEFAULTGROUP, defined in the node group, IBMDEFAULTGROUP. This ensures that tables created by any users will default to use this table space.
| Note: | This is done automatically during database migration from DB2 PE Version 1 to DB2 Version 5. |
| UNIX | OS/2 | WIN | DB2 PE |
A user can grant privileges on a table or view using the CONTROL privilege. In DB2 Version 5, the WITH GRANT OPTION provides a mechanism to determine a user's authorization to grant privileges on tables and views to other users. This mechanism is used in place of CONTROL to determine whether a user may grant privileges to others. When CONTROL is revoked, users will continue to be able to grant privileges to others.
A user can still grant privileges on tables or views, following the revocation of CONTROL privilege.
If a user should no longer be authorized to grant privileges on tables or views to others, revoke all privileges on the table or view and grant only those required.
| UNIX | OS/2 | WIN | DB2 PE |
In DB2 PE Version 1, users would create a table, view, index or package with any schema name or qualifier with the exception of SYSIBM. This differs from other IBM database products and is not compliant with SQL92. In DB2 Version 5, there are limits of the schema names that you can use.
| Note: | The schema names for all other objects must not start with SYS. |
Users require IMPLICIT_SCHEMA authority to implicitly create a schema. Once a schema is created, specific privileges allow users to create objects (CREATEIN privilege), drop any object in the schema (DROPIN privilege), or alter (comment on) any object in the schema (ALTERIN privilege). The change to supporting schemas, as objects with privileges, has resulted in changes to privileges associated with various statements.
| Note: | For altering or commenting on a table, the ALTER privilege on the table is also valid. |
If you create an object with an invalid schema name, the CREATE statement returns an SQLCODE of SQL0553N. This message indicates that the object cannot be created with the schema name.
If a CREATE, ALTER, COMMENT ON or DROP statement returns an SQLCODE of SQL0551N, you did not have the necessary privilege. This may be the result of schema-related privileges and could indicate that:
Depending on the symptom:
| 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.
|
|
|
| DB2 PE |
The LOAD TERMINATE command has a different function in DB2 UDB than it did in DB2 Parallel Edition Version 1.x. In DB2 Parallel Edition, you could use LOAD TERMINATE if an error occurred during the load operation to ensure that the table data was consistent. In DB2 Universal Database Version 5 however, if you use LOAD TERMINATE, the table space is moved into the recovery pending state. (When the table space is in the recovery pending state, you must either restore the table space or drop it.)
Instead of being placed in a consistent state, the table space is placed in a recovery pending state.
Instead of using LOAD TERMINATE to clean up after a failed load operation, you should use LOAD RESTART or LOAD REPLACE. You also have the option of dropping and re-creating the table space.
| UNIX | OS/2 |
When moving from Version 2 to Version 5, the REORG command and API no longer support an "alternate path" as a work area, but rather support the name of a table space to be used as a work area. APIs and commands will not fail, however, this option will be ignored.
Symptom: REORG invocations from downlevel clients will ignore the alternate work path and arbitrarily choose a temporary table space to use as a work area.
Another symptom is you may run out of disk space.
Your applications will continue to function, but you should consider upgrading to the DB2 Version 5 calls which contain valid options.
There are no incompatibilities for this area.
| OS/2 | WIN | UNIX | DB2 PE |
The data type of this database configuration parameter has changed from being an unsigned 2-byte integer to an unsigned 4-byte integer. A new token has been added for the configuration APIs indicating a 4-byte integer.
The configuration API will still recognize the Version 2 token, but the full range of values of this parameter is greater than what is supported by a 2-byte integer.
Existing applications will continue to work using the configuration API or via REXX, but the results might be unpredictable because of the larger range in DB2 Version 5.
Re-code the application or REXX script to use the new token. For users of the Command Line Processor or the Control Center, this change in the token would not affect your applications.
| UNIX | OS/2 | WIN | DB2 PE |
Before Version 5, each database had one buffer pool, which was created when the database was created. You could change the size of the buffer pool using the buffpage parameter. In DB2 Version 5 and later, each database can have multiple buffer pools. You can create additional buffer pools of possibly different page sizes or change the size of a buffer pool through the CREATE BUFFERPOOL or ALTER BUFFERPOOL statements or through the Control Center using the appropriate command.
If the buffer pool size is specified to be -1, then the value of the database configuration parameter is used as the size of the buffer pool.
| Note: | When the BUFFPAGE database configuration parameter is updated, you will receive an SQLCODE SQL1482W warning. |
In DB2 Version 5, a new or migrated database has a default buffer pool. For a new database created in DB2 Version 5, the size of the default buffer pool is determined by the operating system. For a migrated database, the size of the buffer pool is set to -1, which then refers to the buffpage configuration parameter.
To resolve this problem, you will need to do the following:
| OS/2 | WIN | UNIX | DB2 PE |
In DB2 Version 5, in a partitioned database, the node number is appended to the path in the form path_name \NODExxxx (path_name /NODExxxx on UNIX-based systems), where xxxx is the 4 digit node number. This maintains the uniqueness of the path across the database partitions.
When updating the NEWLOGPATH configuration parameter, the node number is automatically appended to the path name. This may result in path names that are too long (greater than 242 characters), and the configuration parameter update may fail.
Be aware that the log files will reside in the path that includes the node numbering designation. If the configuration parameter update failed, ensure that the path length, including the node number designation, is less than or equal to 242 characters.
|
|
|
| DB2 PE |
In DB2 PE Version 1.2, this database configuration parameter was known as MULTIPGAL and the data type of this database configuration parameter was an unsigned 1-byte integer. In DB2 Version 5, the data type of this parameter is an unsigned 2-byte integer, using a new token.
Existing applications will continue to work using either the SQLF_DBTN_MULTIPGAL or the SQLF_DBNR_MULTIPAGE_ALLOC tokens.
While the configuration APIs support both tokens, applications should be updated to use the new tokens.
| UNIX | OS/2 |
In DB2 Version 5, the size of a lock request block has been changed to 36 bytes. As a result, fewer lock request blocks will fit in the configured amount of space allocated for the lock list.
This may result in more frequent lock escalations.
You should increase the setting of the LOCKLIST configuration parameter accordingly.