Application and SQL release incompatibilities for migration from DB2 9

When you migrate from DB2® 9 to DB2 10, be aware of the application and SQL release incompatibilities.

Begin general-use programming interface information.

Plan for the following changes in DB2 10 that might affect your migration.

Automatic rebind of plans and packages created before Version 6

If you have plans and packages that were bound before Version 6 and you specified YES or COEXIST in the AUTO BIND field of panel DSNTIPO, DB2 10 automatically binds these packages. Thus, you might experience an execution delay the first time that such a plan is loaded. Also, DB2 might change the access path due to the autobind, potentially resulting in a more efficient access path.

If you specify NO in the AUTO BIND field of panel DSNTIPO, DB2 10 returns SQLCODE -908, SQLSTATE 23510 for each attempt to use such a package or plan until it is rebound.

Start of change

IBMREQD is no longer reliable as a release dependency mark

The IBMREQD field in DB2 catalog tables is no longer a reliable indicator for determining release dependencies. Use the RELCREATED or RELBOUND fields instead.

End of change
Start of change

Changes to string formatting of decimal data

Explanation

DB2 10 changed the formatting of decimal data by the CHAR and VARCHAR built-in functions and CAST specifications with a CHAR or VARCHAR result type. For input data that contains decimals, leading zeros are removed, and leading zeros are not added to values that did not already contain leading zeros. If the scale of the decimal value is zero, the decimal character is not returned. Also, the CHAR function no longer returns leading blanks for positive decimal values. The result of the CHAR function for decimal data is now consistent with the result of CAST(decimal-expression AS CHAR).

After migration to DB2 10, packages that were bound before DB2 10 use the old behavior for these functions. Materialized query tables and indexes on expressions that were created before DB2 10 also continue to use the old behavior.

Views and inline SQL functions use the behavior of the SQL statement that references the object. It is possible for references to the same view or function in different applications to get different behavior for these functions or casts.

Possible impact to your DB2 environment

These changes might cause unexpected output from applications that use the CHAR or VARCHAR functions for decimal data or the CAST(decimal-expression AS CHAR) or CAST(decimal-expression AS VARCHAR) specifications.

Actions to take

These changes occur in DB2 10 conversion mode (from both Version 8 and Version 9). You can temporarily override these changes on a subsystem level by using the BIF_COMPATIBILITY subsystem parameter. You can also temporarily override these changes on an application level by adding schema SYSCOMPAT_V9 to the front of the PATH bind option or CURRENT PATH special register. This approach works for CHAR and VARCHAR functions and does not affect CAST specifications. The recommended approach is to modify your applications to handle the DB2 10 behavior for these functions, as described in the following steps.

To modify your applications to handle the DB2 10 behavior for CHAR, VARCHAR, and CAST:

  1. Identify applications that need to be modified to handle this change. You can use IFCID trace 0366 to identify affected applications.
  2. Ensure that the BIF_COMPATIBILITY subsystem parameter is set to V9_DECIMAL_VARCHAR.

    To handle the change for the CHAR function only, you can set the subsystem parameter to V9 and complete the following steps for the CHAR function.

  3. Change any affected applications to handle the new DB2 10 CHAR and VARCHAR behavior, including stored procedures, non-inline user-defined functions, and trigger packages. Rewrite affected CAST specifications with the appropriate CHAR or VARCHAR function and a CAST to the correct length if needed.
  4. Rebind and prepare packages with the PATH(SYSCURRENT,SYSIBM) rebind option to use the new DB2 10 CHAR and VARCHAR built-in functions. Repeat this step for native stored procedures (SQLPL) and non-inline SQL scalar functions.
  5. For views that reference these casts or built-in functions, determine whether the view needs to be changed to have the expected output. Drop and re-create the views with the PATH(SYSCURRENT,SYSIBM) rebind option only if necessary. Rebind any applications that reference the views with the PATH(SYSCURRENT,SYSIBM) option to use the new DB2 10 CHAR and VARCHAR built-in functions. Repeat this step for inline SQL scalar functions.
  6. For materialized query tables or indexes on expressions that reference these casts or built-in functions, drop and re-create the materialized query tables or indexes on expressions with the PATH(SYSCURRENT,SYSIBM) rebind option. Issue the REFRESH TABLE statement for materialized query tables. Rebind any applications that reference the materialized query tables or indexes on expressions with the PATH(SYSCURRENT,SYSIBM) option to use the new DB2 10 CHAR and VARCHAR built-in functions.
  7. Change the value of the BIF_COMPATIBILITY subsystem parameter to CURRENT. When the subsystem parameter value is CURRENT, new applications, rebinds, and CREATE statements use the new CHAR, VARCHAR, and CAST behavior.

Materialized query tables and indexes on expressions use the CHAR, VARCHAR, and CAST behavior that is specified during its creation. If a reference statement has a different behavior that is specified by the BIF_COMPATIBILITY parameter or a different path, the materialized query table or expression-based index is not used.

End of change
Start of change

Change to maximum result length of VARCHAR function

In DB2 10, the maximum result length of the VARCHAR function is changed from 32767 to the maximum length of a VARCHAR.

End of change

Changes to VARCHAR_FORMAT function length attribute

In DB2 10, for VARCHAR_FORMAT functions, the length attribute of the result is the length attribute of the format string, up to a maximum of 255. To apply this change, use the following guidance:

  • Existing view definitions that reference the VARCHAR_FORMAT function should be regenerated with an ALTER VIEW statement.
  • Existing materialized query statements that reference the VARCHAR_FORMAT function should be dropped and re-created.
  • Bound SQL statements that reference the VARCHAR_FORMAT function will only use the new behavior when they have been bound in DB2 10 conversion mode (from Version 8 or Version 9) or later.
  • DESCRIBE statements will only determine the result data type for the VARCHAR_FORMAT function using the modified rules in the DESCRIBE statements have been bound in DB2 10 conversion mode (from Version 8 or Version 9) or later.
  • Existing indexes that involve an expression that reference the VARCHAR_FORMAT function should be dropped and re-created.

Start of changeIf an application is using the DSNTIAUL program, the result string is padded with characters '00'X. Consider this incompatible change for any applications that use the VARCHAR_FORMAT function and are dependent on the output from DSNTIAUL.End of change

New format element for VARCHAR_FORMAT function

In DB2 10, a new format element, "RRRR", is supported. In previous versions, this format element was interpreted as two adjacent specifications of the "RR" format element.

Change to result of VARCHAR_FORMAT function with "HH12" format element

In DB2 10, if the "HH12" format element is specified in a VARCHAR_FORMAT function and the time component of the first argument is 24:00:00, the input timestamp value is adjusted to 00:00:00 and the date is changed to the next day. In previous releases, the timestamp value is adjusted to 12:00:00 and the day is not changed.

Change to result of VARCHAR_FORMAT function with "J" format element

In DB2 10, if the "J" format element is specified in a VARCHAR_FORMAT function, the result is different from the result of the JULIAN_DAY function for dates earlier than October 15, 1582.

New supported data types for VARCHAR_FORMAT function

The VARCHAR_FORMAT function has been extended to allow date, character, and graphic string input for the first argument, and graphic string input for the second argument. If the function is invoked with one of the newly supported data types, and an existing user-defined function named VARCHAR_FORMAT also supports the data type, the function might resolve to the built-in function rather than the user-defined function. If the reference to the existing function uses the unqualified name and SYSIBM precedes the schema that was used for the user-defined function, the new function will be invoked rather than the user-defined function.

Start of change

Change to maximum result length of REPEAT function

In DB2 10, the maximum result length of the REPEAT function is changed from 32767 to the maximum length of a VARCHAR.

End of change
Start of change

Change to maximum result length of XMLTABLE function

In DB2 10, the maximum length of a VARCHAR data type result column of the XMLTABLE function is changed from 32767 to the maximum length of a VARCHAR.

End of change
Start of change

Change to how a positive, signed integer in an ORDER BY clause is treated

Explanation

Beginning in DB2 10 conversion mode (from both Version 8 and Version 9), a positive, signed integer in an ORDER BY clause is treated as a sort-key-expression. Such integers were previously interpreted as column numbers.

For example, in previous versions, ORDER BY +1 in the following SELECT statement meant order by column 1 (C1).

SELECT C1, C2 FROM T1 ORDER BY +1;

Starting in DB2 10, +1 means the constant +1, which has no effect on the order of the rows.

Possible impact to your DB2 environment

This change might cause unexpected results of queries that contain an ORDER BY clause with a positive, signed integer. However, no error is issued when such queries are run.

Actions to take

To prepare for this change, identify any queries that use a positive, signed integer in an ORDER BY clause to refer to a column in the result table. Modify these queries to use unsigned integers to identify column numbers.

End of change

Binding DBRMs directly into plans is no longer supported

For pre-existing plans that are bound from DBRMs, you can use the COLLID parameter of the REBIND PLAN command to create packages. If you execute a plan that is bound from DBRMs, DB2 performs an automatic rebind that creates packages from the DBRMs and binds those packages into a plan. However, it is recommended that you run REBIND PLAN with the COLLID option so that you can specify bind options and receive more diagnostic information.

Important: Start of changeIf the same DBRM is in multiple plans, and you run REBIND PLAN with the same COLLID option value on more than one of those plans, DB2 overlays the previously created package in the collection each time you run REBIND with COLLID. To avoid overlaying packages, specify a different COLLID value for each plan that contains DBRMs that are also in other plans.End of change

If the installation uses the RACF® access control module, owners of plans with DBRMs need to explicitly rebind the plans to convert the DBRMs to packages.

Start of changeFor more information on developing a strategy for converting your plans to include only packages, see Conversion to packages (DB2 9 for z/OS®: Packages Revisited).End of change

Some BIND PLAN and REBIND PLAN command options are no longer supported

The ACQUIRE(ALLOCATE) option of the BIND PLAN and REBIND PLAN commands is no longer supported. If you specify ACQUIRE(ALLOCATE), DB2 issues a warning message and uses ACQUIRE(USE).

Also, the MEMBER option of BIND PLAN and REBIND PLAN is no longer supported. If you specify MEMBER, DB2 issues a warning message, binds the specified DBRM into a package, and binds the package into a plan.

Plans and packages should be converted to DRDA protocol

Plans and packages that were previously bound using DBPROTOCOL(PRIVATE) should be converted to DRDA protocol before migration to DB2 10. In DB2 10, plans and packages that were bound with the DBPROTOCOL(PRIVATE) bind option and access remote locations cannot run. Applications that use packages or plans that were bound with DBPROTOCOL(PRIVATE) and access remote locations fail with SQLCODE -904. A rebind of those plans and packages must be explicitly performed before they can execute successfully. Job DSNTIJPM identifies the objects that must be converted to use DRDA protocol.

Change to GRANT statement

The PUBLIC AT ALL LOCATIONS clause is no longer allowed in the GRANT statement for table and view privileges as an alternative to PUBLIC. The DSNTPPCK program in Version 8 and DB2 9 analyzes the embedded SQL statements in packages and plans for private protocol SQL, which is invalid in DB2 10. The program produces a report that states which packages and member DBRMs of plans contain the invalid syntax. The program scans either the SYSIBM.SYSSTMT catalog table, the SYSIBM.SYSPACKSTMT catalog table, or both. Applications that issue dynamic SQL statements with the invalid PUBLIC AT ALL LOCATIONS clause will receive SQLCODE -199.

Change to IMMEDWRITE option of BIND PACKAGE command

In DB2 10, if IMMEDWRITE is not specified on a BIND PACKAGE command, the default is "I", or INHERITFROMPLAN. In previous versions, the default was NO.

Changes to conversion of special characters in collection IDs and package names

In DB2 10, DRDA character type parameter data is sent between client drivers and DB2 in UTF-8 Unicode, if those client drivers also have this support. Character type parameter data includes package names and collection IDs. Prior to DB2 10, collection IDs and package names were sent in EBCDIC, and then converted to Unicode before being stored in the DB2 catalog. A collection ID or package name that is in the DB2 catalog from a bind that was initiated by an older driver might not match an ID or name that is sent to DB2 by a newer driver. This mismatch, which is caused by the way that some special characters are converted, can cause package-not-found errors. Job step DSNTGEN of job DSNTIJPM identifies package names and collection IDs that contain special characters that cause mismatches. From the upgraded client drivers, those packages need to be bound with the ACTION(REPLACE) option.

Changes to the RELEASE bind option

In releases prior to DB2 10, the RELEASE bind option had no effect on database access threads. Starting in DB2 10, by default, DB2 honors the RELEASE bind option for database access threads. You can modify this behavior by using the new MODIFY DDF PKGREL command.

Database metadata stored procedures are converted to Unicode

In DB2 10, supplied database metadata stored procedures are encoded in Unicode. The DB2 10 migration process redefines the stored procedures to use the following new load modules:

Table 1. Metadata stored procedures and associated load modules
Stored procedure Load module
SYSIBM.SQLCOLPRIVILEGES DSNACPRU
SYSIBM.SQLCOLUMNS DSNACOLU
SYSIBM.SQLFOREIGNKEYS DSNAFNKU
SYSIBM.SQLFUNCTIONCOLS DSNAFCOU
SYSIBM.SQLFUNCTIONS DSNAFUNU
SYSIBM.SQLGETTYPEINFO DSNATYPU
SYSIBM.SQLPRIMARYKEYS DSNAPRKU
SYSIBM.SQLPROCEDURECOLS DSNAPCOU
SYSIBM.SQLPROCEDURES DSNAPRCU
SYSIBM.SQLSPECIALCOLUMNS DSNASPCU
SYSIBM.SQLSTATISTICS DSNASTAU
SYSIBM.SQLTABLEPRIVILEGES DSNATBPU
SYSIBM.SQLTABLES DSNATBLU
SYSIBM.SQLUDTS DSNAUDTU

Some applications call the database metadata stored procedures to retrieve double-byte (DBCS) data. Those applications must be modified to use Unicode if they are bound on a DB2 server that has an EBCDIC SBCS CCSID, and the MIXED parameter is set to NO in the application defaults load module, dsnhdecp.

Start of change

AUTHID is the default owner of packages that are bound by DSNTRIN

In previous releases, the stored procedures and user-defined functions that are provided as part of the DB2 base product (routine that supplied routines) were created and bound by processing inline DDL and bind statements in DSNTIJSG and other installation jobs. By default, the ID that was used to run the job was also the authorization ID for creating the routines and the default package owner for those routines.

In DB2 10, DB2-supplied routines are created and bound by running program DSNTRIN in job DSNTIJRT. The AUTHID parameter of DSNTRIN specifies the authorization ID for creating the routines. This ID is also the default owner of packages that are bound for those routines.

End of change

New default DEFINE attribute for dependent objects

As of DB2 10, if the DEFINE attribute is not specified in the CREATE statement for explicitly created dependent objects (auxiliary indexes, XML indexes, and base table indexes), DB2 uses the DEFINE attribute of the base table space.
Exception: If the DEFINE attribute is not specified for explicitly created LOB table spaces, there is no correlation with the base table space until the auxiliary table is created. The DEFINE attribute is not inherited from the base, and the default is DEFINE YES.
Implicitly created dependent objects (base table indexes, LOB and XML table spaces, and their dependent indexes) inherit the DEFINE attribute of the base table space if it is DEFINE NO. Otherwise, if the base table space attribute is DEFINE YES, the value of the IMPDSDEF subsystem parameter is used for the dependent objects.

Change for creating partitioned table spaces

Start of changeTo create a partitioned (non-universal) table space in DB2 10 new-function mode, you must specify SEGSIZE 0 and the NUMPARTS keyword of the CREATE TABLESPACE statement. Before new-function mode, do not specify the SEGSIZE to create a partitioned table space.End of change

Start of change

Change to default for CREATE TABLESPACE statements

In previous releases of DB2, if a CREATE TABLESPACE statement contains the NUMPARTS clause but neither the MAXPARTITIONS clause nor the SEGSIZE clause, a partitioned (non-universal) table space is created. Beginning in DB2 10 new-function mode, the same statement results in a range-partitioned table space with a segment size of 32 by default. You might observe this difference when a subsequent CREATE INDEX statement with the specified ranges fails because index partitioning of a range-partitioned table space is not supported.

In response, you can change the default segment size through the DSN6SYSP.DPSEGSZ subsystem parameter, which is externalized as the DEFAULT PARTITION SEGSIZE field on panel DSNTIP7. The value of the DPSEGSZ parameter can range from 0 to 64 in increments of 4; for example, 0, 4, 8, and so on up to 64. The default is 32.

When DPSEGSZ is set to 0, a CREATE TABLESPACE statement that contains the NUMPARTS clause but neither the MAXPARTITIONS clause nor the SEGSIZE clause results in a partitioned (non-universal) table space, which is the behavior of previous releases. Note that the DPSEGSZ parameter is provided for compatibility only. It is deprecated in DB2 10, and you should take steps to modify affected CREATE TABLESPACE statements or plan to use partition-by-growth table spaces instead of partitioned (non-universal) table spaces.

End of change

Change to default SEGSIZE value for universal table spaces

In DB2 10, the default SEGSIZE value for universal table spaces has changed from 4 to 32.

Upgrade to supported COBOL and PL/I compilers

If you use the DB2 10 precompiler, you must upgrade to COBOL compilers that DB2 10 supports. See the DB2 Program Directory for information about supported compilers. The generated SQLCA and SQLDA for COBOL have changed. The generated attribute for binary data items is now COMP-5 instead of COMP-4 or COMP. Applications that are compiled on compilers that do not support the COMP-5 attribute no longer work.

For some COBOL and PL/I compilers that are no longer supported, you can use a version of the precompiler that allows you to precompile applications that have dependencies on these unsupported compilers. You can use this version of the precompiler with the following unsupported compilers:

  • OS/VS COBOL V1.2.4
  • OS PL/I 1.5 (PL/I Opt. V1.5.1)
  • VS/COBOL II V1R4
  • OS PL/I 2.3

The load module for this precompiler is DSNHPC7. This precompiler is meant only to ease the transition from unsupported compilers to supported compilers. This precompiler has the following restrictions:

  • There is no corresponding DB2 coprocessor function to match this precompiler.
  • The precompiler does not support SQL procedures.
  • Only COBOL and PL/I are supported.
  • The SQL flagger is not supported.
  • The precompiler produces DB2 Version 7 DBRMs, and does not support any capability that is newer than DB2 Version 7.
  • The application defaults module must be named DSNHDECP.

Support of this precompiler is deprecated in DB2 10.

GRAPHIC and NOGRAPHIC SQL processing options are removed

If you specify the SQL processing options GRAPHIC or NOGRAPHIC, DB2 issues a standard warning message about an invalid option. These options are superseded by the CCSID SQL processing option.

SELECT FROM data change statements in BEFORE triggers no longer supported

The following statements are no longer allowed in the body of a BEFORE trigger:

  • SELECT FROM DELETE
  • SELECT FROM INSERT
  • SELECT FROM MERGE
  • SELECT FROM UPDATE

RETURN statement in scalar functions must follow option-list

In previous DB2 versions, RETURN statements in scalar functions could be in any order, relative to other clauses. After migration to DB2 10 conversion mode (from Version 8 or Version 9), the RETURN statement in CREATE FUNCTION or ALTER FUNCTION statements must follow option-list. If a RETURN statement precedes option-list in one of these statements, DB2 issues SQLCODE -199.

Changed behavior of LOCATE_IN_STRING function

In DB2 10, a negative value for start in the LOCATE_IN_STRING function results in the search starting at the end of the source string. When start is negative, the starting position is LENGTH(source-string) + start + 1.

Changes to ROUND_TIMESTAMP and TRUNC_TIMESTAMP functions

In DB2 10, the ROUND_TIMESTAMP and TRUNC_TIMESTAMP functions return the first day of the first ISO week of the ISO year when an ISO year format is specified.

Also, whenever a CC or SCC format is specified, a start of a century is considered to be year 01.

Changes to result of NEXT_DAY function

In DB2 10, the result data type for the NEXT_DAY function is determined from the input data. If the first input argument is a string, the result is TIMESTAMP(6) WITHOUT TIME ZONE. Otherwise, the data type of the result is the same as the data type of the first input argument. For example, if the input is a date data type, the result is also a date data type. The following rules apply to the change in the NEXT_DAY function:
  • Existing view definitions that reference the NEXT_DAY function should be regenerated with an ALTER VIEW statement.
  • Existing materialized query statements that reference the NEXT_DAY function should be dropped and re-created.
  • Bound SQL statements that reference the NEXT_DAY function only use the modified rules to determine the result data type if the statements are bound in DB2 10 conversion mode (from Version 8 or Version 9) or later.
  • DESCRIBE statements only use the modified rules to determine the result data type for the NEXT_DAY function if the statements are bound in DB2 10 conversion mode (from Version 8 or Version 9) or later.
  • Existing indexes that involve an expression that reference the NEXT_DAY function should be dropped and re-created.

Changes to MONTHS_BETWEEN function

In previous releases, the MONTHS_BETWEEN function ignored the time portion of arguments. In DB2 10, the MONTHS_BETWEEN function takes the time portion of arguments into consideration.

Changes to TIMESTAMPDIFF function

In previous releases, the TIMESTAMPDIFF function allowed string input values that had more than six digits to the right of the decimal point. In DB2 10, an error is issued if the string input value for TIMESTAMPDIFF function has more than six digits to the right of the decimal point.

Start of change

Static SQL applications that use parallelism

DB2 incrementally rebinds the statements that use parallelism after migration to DB2 10. Incremental rebinds can cause performance degradation. If you use the access control authorization exit (DSNX@XAC) for authorization, incremental rebinds can cause authorization failures because they trigger authorization checks of static SQL statements when the package is executed. The authorization checks are performed on the primary authorization ID during incremental rebinds.

You should manually rebind those statements that use parallelism after migration. You can run a query in job DSNTIJPM before you migrate to determine which statements can use parallelism, and are therefore candidates for incremental rebinds. You should consider rebinding those statements after migration, as soon as your DB2 10 system is stable. After you migrate to DB2 10, you can also run a performance trace, class 3 or class 10 for IFCID 360, to identify the plans and packages that contain static SQL queries that use parallelism, and therefore need to be rebound.

End of change

Enforced SELECT authorization checking for UPDATE and DELETE statements

DB2 10 checks for the SELECT privilege or appropriate administrative privilege before allowing a user to execute UPDATE or DELETE statements that reference an existing value in the target table. This authorization checking applies regardless of how the statements are executed (for example, bound in a package or executed in a dynamic statement). The authorization now also applies regardless of the setting of the SQLRULES(STD) bind option for static statements or the CURRENT RULES special register for dynamic statements. If the user does not have the necessary SELECT authorization or administrative privilege, a negative SQLCODE is returned.

Increased limit for work file record length

In DB2 10 new-function mode, the limit for the row length in the result of a JOIN or the row length of a SORT record is increased from 32 767 bytes (1 page) to 65 529 bytes. The sort key maximum length is also increased from 16 000 bytes to 32 000 bytes. Applications that exceed the old limits fail in conversion mode (from both Version 8 and Version 9) with SQLCODE -670 or SQLCODE -136.

New restrictions for EXPLAIN tables

In DB2 10 conversion mode (from both Version 8 and Version 9), EXPLAIN tables must be in Version 8 or later format and preferably encoded in Unicode. When EXPLAIN tables are in a format prior to the Version 8 format, DB2 returns SQLCODE -20008 reason code 2 for statements or commands that invoke EXPLAIN processing. Statements or commands that invoke EXPLAIN processing return SQLCODE +20520 reason code 2 if an EXPLAIN table is in Version 8 or DB2 9 format, regardless of the encoding type. If an EXPLAIN table is in Version 10 format and encoded in EBCDIC, SQLCODE -878 is returned. When you convert EXPLAIN tables to Unicode encoding, applications that join with EXPLAIN tables might have different results because of the CCSID conversion. For more information, see Objects with different CCSIDs in the same SQL statement.

Recommendation: Before you begin migration to DB2 10, convert all EXPLAIN tables to the current version (8 or 9.1) format and Unicode encoding, and then check for joins to those tables. You can use job DSNTIJXA to convert most tables to current release format. You can use jobs DSNTIJXB and DSNTIJXC to migrate EBCDIC-encoded EXPLAIN tables to Unicode.

MEMBER CLUSTER table spaces indicated by MEMBER_CLUSTER column

In previous versions of DB2, a "K" or "I" in the TYPE column of the SYSTABLESPACE catalog table indicated that the table space had MEMBER CLUSTER structure. In Version 10, a new MEMBER_CLUSTER column on the SYSTABLESPACE catalog table is populated during the enabling-new-function mode (from both Version 8 and Version 9) migration process. For existing MEMBER CLUSTER table spaces, values of "K" in the TYPE column of SYSTABLESPACE are replaced with "L", and values of "I" are replaced with blank. The MEMBER_CLUSTER column is populated with "Y". After migration to enabling-new-function mode (from Version 8 or Version 9), applications that query "K" or "I" in the TYPE column must query the new MEMBER_CLUSTER column instead.

Changed values for the modification level in the product signature

The DB2 10 product signature has the form DSN1001m, where m is the modification level. Values 0 and 1 are reserved for maintenance levels in conversion mode from Version 8, conversion mode* from Version 8, enabling-new-function mode from Version 8, and enabling-new-function mode* from Version 8. Values 2 and 3 are for maintenance levels in conversion mode from Version 9, conversion mode* from Version 9, enabling-new-function mode from Version 9, and enabling-new-function mode* from Version 9. Values 5, 6, 7, 8, and 9 are for maintenance levels in new-function mode. Value 4 is undefined.

Changed behavior for the CREATE FUNCTION statement

In all forms of the CREATE FUNCTION statement, a parameter list is required. Functions without parameters must have empty parentheses specified, as in the following example: CREATE FUNCTION F1 ( ) RETURNS INT RETURN 1.

In previous releases, if you specified CREATE FUNCTION without a parameter list (for example, CREATE FUNCTION F1 RETURNS INT RETURN 1), you received no errors. In DB2 10, if you specify CREATE FUNCTION without a parameter list, DB2 issues an error.

Also, a CREATE FUNCTION statement for a non-inline SQL scalar function cannot have a parameter that is a distinct type that is based on a LOB data type. The inline SQL scalar functions have supported, and will continue to support, distinct type parameters where the underlying base data type is a LOB. For the syntax of this statement, see CREATE FUNCTION in the DB2 for z/OS SQL Reference Guide.

Different SQLSTATE returned for some DELETE or UPDATE statements

In previous releases of DB2, a SQLSTATE value of "00000" was returned for some DELETE or UPDATE statements that did not contain a WHERE clause, when SQLWARN flags were set. Those situations occurred during bind processing. As of DB2 10, a SQLSTATE value of "01504" is returned in those situations. This change does not affect SQL statements that are executed on behalf of a DB2 for z/OS requester.

Changed default behavior of multiple-row inserts for ODBC z/OS applications

As of DB2 10, the default behavior for multiple-row inserts is changed from non-atomic to atomic. To change the default behavior back to non-atomic, set keyword PARAMOPTATOMIC = 0 in the data source section of the ODBC initialization file.

Changes to ALTER TABLESPACE statement error codes

Before DB2 10 conversion mode (from Version 8 or Version 9), if you execute an ALTER TABLESPACE MAXPARTITIONS statement on catalog objects, DB2 issues SQLCODE -644. After you migrate to DB2 10 conversion mode (from Version 8 or Version 9), DB2 issues SQLCODE -607.

Also before DB2 10 conversion mode (from Version 8 or Version 9), if you execute an ALTER TABLESPACE MAXPARTITIONS statement with a value of MAXPARTITIONS that is invalid with the page size and DSSIZE values, DB2 issues SQLCODE -4701. After you migrate to DB2 10 conversion mode (from Version 8 or Version 9), DB2 issues SQLCODE -644.

Change to CREATE and ALTER statements

CREATE and ALTER statements for native SQL procedures no longer support the DEFAULT keyword for the SQL PATH option.

Change to ALTER PROCEDURE statement

When the REGENERATE option is specified for the ALTER PROCEDURE statement for native SQL procedure, any existing comment in the catalog for the routine is retained. In previous DB2 versions, the comment was cleared in this situation.

Change to DESCRIBE statement

In DB2 10, when a DESCRIBE statement is used on a result table that includes a distinct type, it will now return information about the distinct type.

New restrictions on using DSNTIAUL

DSNTIAUL can no longer be used to process CREATE FUNCTION (SQL scalar) statements that would result in a package or CREATE TRIGGER statements. DSNTIAUL also cannot be used to process any other statement that contains SQL-routine-body. These statements are CREATE PROCEDURE (SQL external), CREATE PROCEDURE (SQL native), CREATE FUNCTION (SQL table), ALTER PROCEDURE (SQL native) with an ADD or REPLACE clause, and ALTER FUNCTION (SQL scalar) with an ADD or REPLACE clause.

Start of change

Changes to SYSROUTINES

As of DB2 10, the CREATEDTS column of SYSIBM.SYSROUTINES will always reflect the time that a CREATE statement was first issued for a routine. In previous releases of DB2, the CREATEDTS column might have contained different values if multiple versions of a routine were created.

End of change
Start of change

Catalog restructured

In DB2 10, the DB2 catalog is restructured to reduce lock contention. As a result, binding takes longer in DB2 10 than it did in previous versions. Also, execution of the DECLARE GLOBAL TEMPORARY TABLE statement takes longer in DB2 10.

End of change
Start of change

Changed data type for an untyped parameter marker

In previous releases, an untyped parameter marker for a unary minus operator has an assumed DOUBLE data type. In DB2 10, the assumed data type is DECFLOAT(34).

End of change
Start of change

Changes to handling of special values Infinity, sNaN, and NaN

In previous releases, when DB2 returns a decimal floating-point (DECFLOAT) value for Infinity, NaN, or sNaN as a CHAR or VARCHAR string, the string is in mixed case. In DB2 10, the values are returned in upper case as follows: INFINITY, NAN, or SNAN.

End of change
Start of change

Changes for INSTEAD OF triggers

Changes are introduced for existing INSTEAD OF triggers that were defined on a view that has a ROWID column or a column that is based on an underlying column of any of the following types:

  • A security label column.
  • A row change timestamp column.
  • A column that is defined with AS ROW BEGIN.
  • A column that is defined with AS ROW END.
  • A column that is defined with AS TRANSACTION START ID.

For such INSTEAD OF triggers, the following actions now fail with a negative SQLCODE:

  • Rebinding the INSTEAD OF trigger package.
  • Dropping and re-creating the INSTEAD OF trigger.
  • Running the REPAIR DBD utility on a database that includes the INSTEAD OF trigger.
  • Issuing ALTER TABLE ALTER COLUMN SET DATA type on a table that has a view on which the INSTEAD OF trigger is defined.
End of change
Start of change

Change to positioned update or delete statements

Explanation

In DB2 10, packages must be rebound if both of the following statements are true:
  • The package contains static cursors that were bound in a previous version.
  • The package contains dynamic UPDATE or DELETE statements that contain the WHERE CURRENT of clause. The WHERE CURRENT of clause indicates a positioned UPDATE or DELETE statement.

Possible impact to your DB2 environment

If any such packages are not rebound in DB2 10, positioned UPDATE and DELETE statements fail with SQLCODE -20249 when they run against a down-level cursor.

Actions to take

After migration to DB2 10 conversion mode (from Version 8 or Version 9), rebind packages that contain static cursors with positioned UPDATE or DELETE statements.

End of change
Start of change

Change to stored procedure parameter values returned to non-Java clients

In previous releases, when a remote application calls a DB2 for z/OS stored procedure, the data types of the returned output data match the data types of the corresponding CALL statement arguments. Starting in DB2 10 conversion mode (from both Version 8 and Version 9), the data types of the returned output data match the data types of the parameters in the stored procedure definition. This change can cause conversion failures for some applications that use non-Java client drivers such as .NET to call stored procedures on DB2. To prepare for this change, modify the CALL requests of your non-Java client applications to specify argument types that conform to the semantics of the client driver. If you want to temporarily override the DB2 10 behavior, you can set the DDF_COMPATIBILITY subsystem parameter to SP_PARMS_NJV.

The SP_PARMS_NJV option of the DDF_COMPATIBILITY subsystem parameter is deprecated. Although the option is supported in DB2 10, it might be removed in a later release.

End of change
Start of change

Change to results of JDBC method PreparedStatement.setTimestamp

TIMESTAMP WITH TIME ZONE is first supported in DB2 10 new-function mode. Before DB2 10 new-function mode, if the value that is assigned to a column (the second parameter of PreparedStatement.setTimestamp) has the java.sql.Timestamp data type, and the column data type is not known, the IBM® Data Server Driver for JDBC and SQLJ chooses TIMESTAMP as the target data type. However, starting with DB2 10 new-function mode, unless the value that is assigned to the column is 0001-01-01-00:00:00.000000 or 9999-12-31-23:59:59.999999, the driver chooses TIMESTAMP WITH TIME ZONE as the column data type. If the driver chooses the TIMESTAMP data type, and the column type is actually TIMESTAMP WITH TIME ZONE, the database manager sets the time zone in the target column using the value of the IMPLICIT_TIMEZONE DECP value. This value might differ from the value that is inserted prior to Version 10 new-function mode.

To produce the same results before and after new-function mode when PreparedStatement.setTimestamp is executed, specify a com.ibm.db2.jcc.DBTimestamp value as the second parameter.

End of change
Start of change

Change to behavior of comma operator in XQuery path expression

Comma operators in XQuery path expressions result in SQLCODE -16031. In previous releases, comma operators in XQuery path expression predicates were interpreted as the and operator.

End of change
Start of change

Change in how DB2 returns stored procedure output parameter data to remote clients

When an application on a client system calls a stored procedure on a DB2 10 for z/OS server, DB2 now handles the output parameters differently. Previously, DB2 returned stored procedure output parameters that were formatted according to the SQL type of the corresponding argument in the CALL statement. DB2 10 now returns output parameters that are formatted according to the SQL type of the corresponding parameter in the stored procedure declaration. This new behavior provides improved performance at the server by avoiding unnecessary server data conversions. Also, this new behavior is consistent with the existing server behavior for the return of query and select output data and it is consistent with the behavior of other DB2 family servers. Therefore, it provides applications with a more consistent and predictable interface to DB2 10.

In general, for applications that conform to client standards, this change has no impact to the calling application. In some cases, however, application changes might be needed. To prepare for this change, examine your remote applications that call DB2 stored procedures. If necessary, modify the CALL statements in your remote applications to specify argument data types that match the data types of the parameters in the stored procedure definitions.

When the change in behavior occurs

In general, the new behavior occurs after migration to DB2 10 conversion mode from Version 8 (CM8) or conversion mode from DB2 9 (CM9). However, the following exceptions apply:

  • For applications using IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to access a DB2 10 data sharing group in CM8 or CM9, where the enableSysplexWLB client property is set to true:

    In such a configuration, the change in behavior occurs when the data sharing group is migrated to DB2 10 new-function mode (NFM). As long as the data sharing group is in CM8 or CM9 to support coexistence, DB2 10 uses the old behavior. The old behavior is used because the IBM Data Server Driver for JDBC and SQLJ might be caching data descriptors for outputs from the server. When the enableSysplexWLB property is set to true, the old behavior is maintained to ensure that the cached client descriptors are valid regardless of which member of the data sharing group is accessed.

    To prepare for this change in behavior while DB2 10 is in CM8 or CM9 to support coexistence, test your applications with the enableSysplexWLB client property set to false. Or, test the applications against a stand-alone DB2 10 server, if such a system is available for testing. The new DB2 10 behavior will be used for each of those applications even though the DB2 data sharing group is in CM8 or CM9.

  • For applications using the IBM Data Server Driver for JDBC and SQLJ or non-Java clients (such as .NET), DB2 9 or earlier:

    In such a configuration, the change in behavior occurs, by default, after migration to DB2 10 CM8 or CM9. However, the change in behavior can be temporarily deferred. To temporarily override the DB2 10 behavior for returning stored procedure output parameter data to the IBM Data Server Driver for JDBC and SQLJ or non-Java clients only (such as .NET), set the DDF_COMPATIBILITY subsystem parameter to SP_PARMS_NJV. The SP_PARMS_NJV option of the DDF_COMPATIBILITY subsystem parameter is deprecated. Although the option is supported in DB2 10 , it will be removed in a later release of DB2. For more information about DDF_COMPATIBILITY, see Subsystem parameters that are not on installation panels.

Examples of application impacts

The following examples use the IBM Data Server Driver for JDBC and SQLJ and .NET. Other drivers might behave differently. Because DB2 10 no longer converts data types when returning stored procedure output parameter data, there might be similar impacts to applications for other data types that are not discussed in the examples. If the calling application specifies arguments that are different from the declared parameter types, then the mismatch between the argument and parameter data types is handled by the client system, according to the client's programming model and the client's capabilities. If the client performs data type conversions such as for numeric data, the result of the conversion performed by the client might differ from the result that was previously returned when the DB2 server performed the conversion.

Example 1

A stored procedure parameter is declared as an INTEGER data type, but the application CALL statement specifies a SMALLINT argument.

  • Behavior before DB2 10: DB2 converts the INTEGER data and returns a 2-byte SMALLINT value, which maps to an Int16 .NET data type.
  • New behavior in DB2 10: DB2 returns a 4-byte INTEGER value, which maps to an Int32 .NET data type.

Sample application impact (.NET): If the application uses an IBM Data Server .NET client, the stored procedure CALL statement now fails with an invalid conversion error because the Int16 and Int32 .NET data types are not compatible with each other. The application must be changed to specify an Int32 argument in the CALL statement as required by the .NET programming model. Applications that are coded with an Int32 argument and call stored procedures that have an INTEGER parameter require no change.

The .NET programming model enforces strong data typing. Thus, even though the argument type and the parameter type are compatible SQL types, the .NET driver enforces strong data type checking according to the .NET semantics. Refer to the .NET documentation for information about .NET strong type checking.

Example 2

A stored procedure parameter is declared as a REAL data type, but the application CALL statement specifies a DOUBLE argument.

  • Behavior before DB2 10: DB2 converts the output data to a DOUBLE value before returning it to the client.
  • New behavior in DB2 10: DB2 returns the data to the client as a REAL value.

Sample application impact (Java): If an application that uses the IBM Data Server Driver for JDBC and SQLJ uses the registerOutParameter() method to register the output parameter as a DOUBLE value before calling a stored procedure that has a REAL parameter, the following behavior occurs if the Java application uses the getObject() method to retrieve the output value:

  • Before Version DB2 10 , DB2 converted the REAL value to a DOUBLE value and returned it to the client. The IBM Data Server Driver for JDBC and SQLJ returned the DOUBLE value directly to the application as the result of the getObject() method.
  • Beginning in DB2 10 , DB2 returns the REAL value to the client. The IBM Data Server Driver for JDBC and SQLJ converts the REAL value to a DOUBLE value and returns it to application as the result of the getObject() method. APAR IC80974 for LUW clients and APAR PM58951 (JCC 3.63.131) and PM58952 (JCC 4.13.136) for z/OS clients are required in the IBM Data Server Driver for JDBC and SQLJ to ensure that the conversion is completed by the getObject() method. If the APAR fix is not applied, the Java application gets a ClassCastException.

Example 3

A stored procedure parameter is declared as a TIMESTAMP data type, but the application CALL statement specifies a VARCHAR argument.

  • Behavior before DB2 10: DB2 converts the fixed-length TIMESTAMP data and returns a 26-byte VARCHAR value, containing a TIMESTAMP value in ISO format (yyyy-mm-dd-hh.mm.ss[.fffffffff]). The VARCHAR value maps to a String .NET data type.
  • New behavior in DB2 10: DB2 returns a 26-byte fixed-length TIMESTAMP value, containing a TIMESTAMP value in ISO format. The TIMESTAMP value maps to a DateTime .NET data type.

Sample application impact (Java): If the application uses the IBM Data Server Driver for JDBC and SQLJ, then an application change might be required, depending on what method the Java application uses to retrieve the parameter data.

  • Before DB2 10 , the getString() method previously returned TIMESTAMP data in ISO format, while the getTimestamp() method returned TIMESTAMP data in Java format
  • Beginning in DB2 10 , both the getString() and getTimestamp() methods return the TIMESTAMP value in Java format (yyyy-mm-dd hh:mm:ss[.fffffffff]).

An application change might be required if the Java application uses the getString() method and the application depends on receiving TIMESTAMP values in ISO format. Applications that use the getTimestamp() method require no change.

Similar considerations apply for TIME parameters. DB2 returns TIME parameters as TIME data values in ISO format (hh.mm.ss), regardless of the SQL type of the corresponding argument in the CALL statement. For Java applications, the getString() method now returns TIME data in Java format (hh.mm.ss). If a Java application uses the getString() method and requires TIME data in ISO format, the application must be examined for possible changes.

End of change
Start of change

Change to IBM Data Server Driver for JDBC and SQLJ handling of TIMESTAMP WITH TIME ZONE data type

Before DB2 10 new-function mode, the TIMESTAMP WITH TIME ZONE data type was not supported. If a Java client application passed a timestamp input value to a TIMESTAMP column, the IBM Data Server Driver for JDBC and SQLJ did not include the local time zone with the timestamp value. Starting with DB2 10 new-function mode, the TIMESTAMP WITH TIME ZONE data type is supported. If a Java client application passes a timestamp input value to a TIMESTAMP column, the IBM Data Server Driver for JDBC and SQLJ constructs a timestamp input value that includes the local time zone. If the value that the driver sends to the server is out of supported range for the server, the application receives SQLCODE -181. You can temporarily prevent this error by including IGNORE_TZ in the settings for the DDF_COMPATIBILITY subsystem parameter.

End of change
Start of change

Changes to datetime built-in functions

Explanation

Many datetime functions allow arguments containing string representations of datetime values. Valid formats for those strings are described in String representations of datetime values.

In DB2 10 conversion mode (from Version 8 or Version 9), the following additional string formats are allowed in the specified limited contexts:
  • A string value of seven characters representing a date is allowed as an argument to the DATE function only.
  • A string value of 8, 13, or 14 characters representing a point in time is allowed as an argument to the TIMESTAMP function only.

Possible impact to your DB2 environment

After migration to DB2 10, applications that provide a seven-character string argument to represent a date for built-in functions other than the DATE function will return an error. Applications that provide a string value of 8, 13, or 14 characters to represent a point in time as an argument for built-in functions other than the TIMESTAMP function also return an error.

Actions to take

To prepare for this change, use valid string formats that are described in String representations of datetime values in arguments for all of your datetime functions.

End of change
Start of change

SQLCODE change for subsequent CAF CONNECT attempts

Explanation

In previous releases, a call attachment facility (CAF) CONNECT request that is followed by another CONNECT request without an intervening disconnect results in a zero return code. In DB2 10, if the second CONNECT request is for a different or unknown subsystem, group attachment, or subgroup attachment name, a -924 SQLCODE is returned. If the second CONNECT request is for the same subsystem, group attachment, or subgroup attachment name, a +361 SQLCODE is returned. A failed attempt to connect to the CAF does not change the current connection. Therefore, in both of these scenarios, the existing CAF connection persists.

Possible impact to your DB2 environment

Some of your applications might receive a -924 or +361 return code where a zero return code was previously returned.

Actions to take

Review your applications for subsequent CONNECT requests for CAF. Modify these applications to handle the new SQLCODEs or rewrite the applications to remove subsequent CONNECT requests.

End of change
Start of change

Delimiters used for accessing tables on DB2 for Linux, UNIX, and Windows

Explanation

DB2 10 resolves aliases prior to sending SQL statements to a remote site for applications that use system-directed access. During a remote package bind against the remote site, modified SQL statement text is bound on the remote system. The DRDA_RESOLVE_ALIAS subsystem parameter is provided in Version 8 and DB2 9 to help verify applications that are affected by this change of behavior before you migrate to DB2 10.

Possible impact to your DB2 environment

This change can impact applications that access a DB2 for Linux, UNIX, and Windows server if the SQL preprocessing option QUOTE or QUOTESQL is used. QUOTE or QUOTESQL specifies that a quotation mark (") is used as the string delimiter and an apostrophe (') is used for SQL identifiers in SQL statements. This option does not control how the COBOL compiler processes string delimiters within the application program statements. DB2 for Linux, UNIX, and Windows does not support statement strings that have been precompiled under the QUOTE or QUOTESQL option and returns a warning on the BIND command. DB2 for z/OS does use the precompiler option to govern which string delimiter to use for SQL identifiers when modifying the SQL text. This causes a bind or rebind to fail on DB2 for Linux, UNIX, and Windows in DB2 10 when the QUOTE or QUOTESQL precompiler option is used to generate the DBRM that is the source of the remote bind package processing.

Actions to take

When accessing a remote table on a DB2 for Linux, UNIX, and Windows server using an alias, applications must be precompiled using the APOST or APOSTSQL option. Character string literals must be delimited by apostrophes and SQL identifiers must be delimited by quotation marks.

End of change

Changes to the LTRIM, RTRIM and STRIP scalar functions

Explanation

In DB2 10, the LTRIM, RTRIM, and STRIP scalar functions are changed in the following ways:
  • When the string-expression argument to LTRIM, RTRIM, or STRIP is a mixed data string but contains an invalid sequence of characters, the function raises SQLCODE -171. Prior versions of DB2 tolerated invalid sequences of characters. For example, suppose that you issue the following SQL statement:
    SELECT HEX(RTRIM(C1)) FROM T;
    Also suppose column C1 contains X'40400F0F4040'. This is an invalid mixed data string, because two consecutive shift-in control characters (X'0F') is illegal. In prior versions, this statement would return X'40400F0F'. In DB2 10, this statement would return SQLCODE -171 because the argument is an invalid EBCDIC mixed data string.
  • When the string-expression argument to LTRIM, RTRIM, or STRIP is an EBCDIC mixed data string, a shift-out control character (X'0E') followed immediately by a shift-in control character (X'0F') are trimmed, along with instances of the trim-character. In prior versions of DB2, the shift-out character and shift-in character are not returned. For example, suppose that you issue the following SQL statement:
    SELECT HEX(RTRIM(C1)) FROM T;
    Also suppose that column C1 contains X'40400E0F4040'. In DB2 10, this statement returns an empty string. The X'0E0F' is removed, and then all of the blanks are trimmed, leaving no other characters. In DB2 9, this statement returns X'40400E0F'. The X'0E0F' is not removed, and no blanks to the left of those control characters are removed.

Possible impact to your DB2 environment

In rare cases, these changes might cause applications running the LTRIM, RTRIM or STRIP functions to produce different results, or introduce an error for jobs that would originally complete successfully.

Actions to take

These changes occur in DB2 10 conversion mode. You can temporarily override these changes on a subsystem level by setting the BIF_COMPATIBILITY subsystem parameter to V9_TRIM. You can temporarily override these changes on an application level by adding schema SYSCOMPAT_V9 to the front of the PATH bind option or CURRENT PATH special register.

When BIF_COMPATIBILITY is set to V9_TRIM, or you use the schemas SYSCOMPAT_V9.LTRIM, SYSCOMPAT_V9.RTRIM or SYSCOMPAT_V9.STRIP, those functions behave as they did in DB2 9. In DB2 9, invalid EBCDIC mixed data strings are tolerated, and a sequence of shift-out control characters (X'0E') followed immediately by shift-in control characters (X'0F') is not trimmed. DB2 10 allows new syntax for LTRIM and RTRIM in the form of a parameter that specifies the trim-character. This new syntax is not affected by the BIF_COMPATIBILITY subsystem parameter. If two parameters (string-expression and trim-expression) are specified, DB2 10 behavior is returned.

To modify your applications to handle the DB2 10 behavior for these functions:
  1. Set the BIF_COMPATIBILITY subsystem parameter to V9_TRIM only if you suspect that an application is adversely affected. The kind of EBCDIC data that would be affected is usually malformed and is not common.
  2. Identify applications that need to be modified to handle this change. You can use IFCID trace 0366 to identify the affected applications. A value of 10 for QH0366FN indicates that the application is using the DB2 9 compatible LTRIM, RTRIM, or STRIP.
  3. Change any affected applications to handle the new DB2 10 LTRIM, RTRIM or STRIP behavior, including stored procedures, non-inline user-defined functions, and trigger packages.
  4. Rebind and prepare packages with the PATH(SYSCURRENT,SYSIBM) rebind option to use the new DB2 10 LTRIM, RTRIM and STRIP built-in functions. Repeat this step for native stored procedures (SQLPL) and non-inline SQL scalar functions.
  5. For views that reference these built-in functions, determine whether the view needs to be changed to have the expected output. Drop and re-create the views with the PATH(SYSCURRENT,SYSIBM) option, if necessary. Rebind any applications that reference the views with the PATH(SYSCURRENT,SYSIBM) option to use the new DB2 10 LTRIM, RTRIM and STRIP built-in functions. Repeat this step for inline SQL scalar functions.
  6. For materialized query tables or extended indexes that reference these built-in functions, drop and re-create the materialized query tables or extended indexes with the PATH(SYSCURRENT,SYSIBM) option. Issue the REFRESH TABLE statement for materialized query tables. Rebind any applications that reference the materialized query tables or extended indexes with the PATH(SYSCURRENT,SYSIBM) option to use the new built-in functions.

    Materialized query tables and extended indexes use the LTRIM, RTRIM and STRIP behavior that is specified during their creation. If a reference statement has a different behavior that is specified by the BIF_COMPATIBILITY parameter (or a different path), the materialized query table or expression-based index is not used.

  7. Change the value of the BIF_COMPATIBILITY subsystem parameter to CURRENT. This setting enables the new applications, rebinds, and CREATE statements to use the new LTRIM, RTRIM and STRIP functions.

Qualify user-defined function names

If you use a user-defined function that has the same name as a built-in function that has been added to DB2 10, ensure that you fully qualify the function name. If the function name is unqualified and "SYSIBM" precedes the schema that you used for this function in the SQL path, DB2 invokes one of the built-in functions.

For a list of built-in functions, including those that have been added in DB2 10, see Built-in functions.

SQLCODE changes

Some SQLCODE numbers and message text might have changed in DB2 10. Also, the conditions under which some SQLCODEs are issued might have changed.

SQL reserved words

DB2 10 has several new SQL reserved words. Refer to Reserved words for the list of reserved words, and adjust your applications accordingly.

End general-use programming interface information.