IBM Books

Administration Guide


Application Programming

NS, NW and NX Locks


UNIX OS/2 WIN DB2 PE

Change

Due to the addition of NS and NX lock modes in DB2 Version 5, there is a difference in the behaviour of index scans with isolation level Cursor Stability (CS) or Read Stability (RS).

Symptom

In DB2 Version 5, an index scan with isolation level, CS or RS, will not see an uncommited 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.

Resolution

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.

CREATE TABLE NOT LOGGED INITIALLY


UNIX OS/2 WIN DB2 PE

Change

In DB2 PE V1.2, in the unit of work in which a table is created with the NOT LOGGED INITIALLY option, an error on this table will cause the unit of work to be rolled back. In Version 5, the range of errors that will cause a roll back has been increased.

Symptom

In Version 5, in the unit of work in which a table is created with the NOT LOGGED INITIALLY option, an error in any operation involving any table will cause the unit of work to be rolled back.

Resolution

Correct the error and run the transaction again.

DB2 Call Level Interface (DB2 CLI) Defaults


UNIX OS/2 WIN

Change

The default values for AUTOCOMMIT and CURSORHOLD have changed. Both AUTOCOMMIT and CURSORHOLD will now default to ON.

Symptom

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.

Resolution

Add one or both of the following two lines to your DB2CLI.INI file.

Obsolete DB2 CLI Keywords


UNIX OS/2 WIN

Change

You can control DB2 configurable features by specifying a set of optional keywords in an DB2 CLI initialization file. In DB2 Version 2, some of these keywords become obsolete, as follows:

  1. UNDERSCORE
  2. TRANSLATEDLL
  3. TRANSLATIONOPTION

Symptom

These keywords will be ignored if they still exist. You may notice behavioral changes based on the removal of these settings.

Resolution

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.

DB2 CLI SQLSTATEs


UNIX OS/2 WIN

Change

A more explicit set of SQLSTATEs (in the S1090 to S1110 range) has replaced the generic SQLSTATE S1009.

Symptom

SQLSTATE values returned to the application calling DB2 CLI APIs have changed.

Resolution

Update your application to check for the new SQLSTATEs. Refer to the Messages Reference for a complete list of these SQLSTATEs.

DB2 CLI Mixing Embedded SQL, Without CONNECT RESET


UNIX OS/2 WIN

Change

DB2 CLI's Version 2 support of multiple connections may affect your existing applications. If your application connects to a database using any non-CLI interface (including embedded SQL using the command line processor or administrative APIs) and does NOT issue a reset before connecting to a database using DB2 CLI, your applications will be affected by this change.

Symptom

The second connect will fail with an SQLSTATE of 08001 since it is not same type of connection as the first connect.

Resolution

The application must issue a CONNECT RESET before calling a DB2 CLI connect function.

DB2 CLI Use of VARCHAR FOR BIT DATA


UNIX OS/2 WIN

Change

Character data defined with the FOR BIT DATA clause is now by default mapped to the new C buffer type, SQL_C_BINARY. If data is defined as FOR BIT DATA, it is transferred to:

Symptom

Existing applications that explicitly use SQL_C_CHAR with data defined as FOR BIT DATA, will get a different result and may receive only half of the original data.

Resolution

In order to have DB2 CLI treat FOR BIT DATA the same as it did in Version 1, add the following line to DB2CLI.INI:

   BITDATA = 0

DB2 CLI Data Conversion Values for SQLGetInfo


UNIX OS/2 WIN

Change

The SQL_CONVERT_xxxx fInfoType is defined by ODBC to indicate supported conversion functions. A change has been made in how we handle SQL_CONVERT_xxxx fInfoTypes which were used with the corresponding SQL_CVT_xxx comparison masks to correctly follow ODBC standards.

Symptom

DB2 CLI will no longer return bit masks for the SLQ_CONVERT_xxx fInfoTypes and corresponding SQL_CVT_xxx comparison masks. DB2 CLI Version 2 now returns zero for all SQL_CONVERT_xxx fInfoTypes.

Resolution

This is to correct Version 1 processing which was not ODBC compliant. There is no resolution.

DB2 CLI/ODBC Configuration Keyword Defaults


UNIX OS/2 WIN

Change

The default value for the CLI/ODBC configuration keyword DEFERREDPREPARE has changed. In DB2 CLI Version 5 deferred prepare is now on by default.

Symptom

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.

Resolution

Add the following line to your db2cli.ini file:

DEFERREDPREPARE = 0

Obsolete DB2 CLI/ODBC Configuration Keywords


UNIX OS/2 WIN

Change

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 has become obsolete.

Symptom

These keywords will be ignored if they still exist. You may notice behavioral changes based on the removal of these settings.

Resolution

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.

DB2 CLI SQLSTATEs


UNIX OS/2 WIN

Change

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.

Symptom

SQLSTATE values returned to the application calling DB2 CLI APIs have changed.

Resolution

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.

Stored Procedure Catalog Table


UNIX OS/2 WIN

Change

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

Symptom

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.

Resolution

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.

PREP Command - LANGLEVEL


UNIX OS/2 WIN

Change

When the LANGLEVEL MIA option of the PREP command is used, all C null-terminated strings are padded with blanks and the null-terminating character is placed in the last byte of the string.

Symptom

Although this change was made for MIA compliance, it has caused a change to the way C null-terminated strings are handled.

Resolution

There is another LANGLEVEL setting (SAA1) which may cause the behavior to better match your needs. You should review the options and decide what is best for your environment.

Change to SMALLINT Constants


UNIX OS/2 WIN

Change

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 (-2147483648) 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 -2147483647 and not -2147483648 (which is the limit for large INTEGER values). The INTEGER constant -2147483648 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.

Symptom

This affects the result precision and scale of decimal operations. (Which impacts, for example, the print width of decimal fields.)

Resolution

There is no resolution. This change in handling integers results in an increase in precision.

Down-level Client and Distinct Types Sourced on BIGINT


UNIX OS/2 WIN DB2 PE

Change

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.

Symptom

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).

Resolution

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);

Error Handling


UNIX OS/2 WIN

Change

Errors which were previously reported at bind time may now not occur until statement execution. For instance, if you create a table using incorrect SQL syntax such as:

CREATE TABLE T1 (C1 CHAR(5), C1 CHAR(10))

The error that a duplicate column name was used will be flagged at run time instead of bind time. For all DDL statements, syntax errors are reported at bind time and semantic errors are reported at run time.

Symptom

Some errors which were reported at bind time in Version 1 will now be reported at execution time.

Resolution

As long as the application has proper error handling routines, this should not cause a problem. There will be some additional errors which can now occur during execution.

Maximum Number of Sections in a Package


UNIX OS/2 WIN

Change

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:

Resolution

Remove references to these constants or define them directly within your application.

Bind Warnings


UNIX OS/2 WIN

Change

Version 1 reports a warning at bind time if the number of host variables in an INTO clause is less than the number of select list items. Version 2 reports the same bind time warning if there are more or less host variables than select list items.

Symptom

You will receive bind time warning messages where one was not received in Version 1.

Resolution

Rebind the application with the new bind option SQLWARN NO and warnings will not be reported.

Bind Options


UNIX OS/2 WIN

Change

The new SQLWARN bind option has a default value of 'YES'.

Symptom

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).

Resolution

Rebind with SQLWARN NO if your application cannot tolerate positive SQLCODEs or treats them as errors.

PREP with BINDFILE


UNIX OS/2 WIN

Change

In Version 2, under certain circumstances, the DB2 PREP (precompile) command allows a bind file to be created even if certain errors occur. If the BINDFILE option, but not the PACKAGE option, is specified on the prep command, the following object existence and authority errors will be tolerated:

SQL0117N
The number of values assigned is not the same as the number of specified or implied columns.

SQL0204N
"<name>" is an undefined name.

SQL0205N
"<name>" is not a column of table "<table-name>".

SQL0206N
"<name>" is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger.

SQL0440N
No function by the name "<function-name>" having compatible arguments was found in the function path.

SQL0551N
"<authorization-ID>" does not have the privilege to perform operation "<operation>" on object "<name>".

SQL0552N
"<authorization-ID>" does not have the privilege to perform operation "<operation>".

Symptom

This may cause precompilation of some applications to succeed with errors where they failed in previous versions. The resultant bind file will fail if it is bound to a database with similar omissions of objects or authorities.

Resolution

Check bind errors instead of precompiler errors for this condition.

Varchar Structures in COBOL


UNIX OS/2 WIN

Change

The COBOL precompiler in Version 2 and Version 5 supports declaration of group data items as host variables. (Refer to the Embedded SQL Programming Guide for more information.) This may cause some incompatibility with existing applications which did not adhere to the precise declaration format for VARCHAR host variables in COBOL.

The level numbers for subordinate items, as documented in DB2 manuals, must be 49. The following declaration would be accepted by the COBOL precompiler in Version 1, but not in Version 2 or Version 5:

       01 MY-VAR.
            10 MY-LENGTH PIC S9(4) COMP-5.
            10 MY-DATA   PIC X(100).

If not coded correctly, the Version 2 and Version 5 precompiler will treat declarations like the above as structures with two members, a short integer and a fixed-length character string. When such a variable is used in an SQL statement, the reference to the would-be VARCHAR would be replaced with references to the two subordinates.

Symptom

Depending on the situation, this may result in the following message:

    SQL0087N Host variable "<name>" is a structure used where
             structure references are not permitted.

Resolution

Applications being migrated to Version 5 that contain host variables which are intended to be VARCHARs should be declared with the subordinates at level 49.

Incompatible APIs


UNIX OS/2  

Change

Several APIs have been changed or removed since Version 1. See the charts in the API Reference showing descriptions of the changes.

Symptom

In most cases, the original API call will still work, however, you cannot take advantage of any of the new Version 5 functionality while using the old API calls or parameters.

Resolution

Applications should be upgraded to use the new Version 5 API calls as described in the API Reference.

Supported Level of JDBC


UNIX OS/2 WIN

Change

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.

Symptom

Compiled JDBC 1.0 clients fail when executed directly as a DB2 Version 5 client. Old Java classes are not found.

Resolution

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.

Calling Convention for Java Stored Procedures and UDFs


UNIX OS/2 WIN

Change

The calling convention for Java stored procedures and user-defined functions (UDFs) has changed in DB2 Version 5.

Symptom

Java stored procedures and UDFs written for DB2 Version 2.1.2 will not be found when run on DB2 Version 5.

Resolution

Change the Java stored procedure and UDF source code to use the new calling convention. Refer to the Embedded SQL Programming Guide for details.

Java Runtime Environment


UNIX OS/2 WIN

Change

The level of the Java runtime environment required for Java stored procedures, user-defined functions, and JDBC clients has changed in DB2 Version 5.

Symptom

The JDBC DLL will not load when JDBC 1.1 clients are run. Java stored procedures and UDFs will fail.

Resolution

Install a compatible Java 1.1 runtime environment at the client and server. At the server, set the jdk11_path configuration parameter.

Obsolete System Monitor Requests for DB2 PE Version 1.2





DB2 PE

Change

Some request types that were available with the DB2 PE Version 1.2 system monitor are no longer supported. See the tables in the System Monitor Guide and Reference showing descriptions of the changes.

Symptom

The old request types will not work.

Resolution

Applications should be upgraded to use the new DB2 Version 5 requests types as described in the System Monitor Guide and Reference.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]