A fix is available
APAR status
Closed as new function.
Error description
Customer complains, that there is a release incompatible change in the behaviour of SQL scalar funtion CHAR in V10, when casting a decimal value to character. I provide a simplified test scenario, that shows the different behaviour in V9 NFM and V10 CM: CREATE TABLE RGTBXXX (COL1 DEC(004 , 000)); INSERT INTO RGTBXXX VALUES(9); INSERT INTO RGTBXXX VALUES(99); INSERT INTO RGTBXXX VALUES(999); INSERT INTO RGTBXXX VALUES(9999); Query: SELECT SUBSTR(CHAR(COL1), 2 , 4) ,HEX(SUBSTR(CHAR(COL1), 2 , 4)) FROM RGTBXXX ; . output in V9 NFM: 0009 F0F0F0F9 0099 F0F0F9F9 0999 F0F9F9F9 9999 F9F9F9F9 . output in V10 CM: 40404040 9 F9404040 99 F9F94040 999 F9F9F940 . The release incompatibility is recorded in V10 Install Guide Section : "Application and SQL release incompatibilities for migration from Version 9.1 " . Where Dynamic INCOMP FUNC CHAR(DEC) EXEC, use STMT ID (QW0366SI) values with EXPLAIN STMTCACHE ALL; & a SELECT query FROM DSN_STATEMENT_CACHE_TABLE in matching up STMT_ID and identifying query STMT_TEXT where changes must be made. .
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: DB2 10 for z/OS users of the CHAR built-in * * scalar function with decimal input values * **************************************************************** * PROBLEM DESCRIPTION: In Version 10, the formatting of * * decimal data has changed for the CHAR * * function. The following changes have * * been made so that the result of the * * CHAR function is now consistent with * * the result of the * * CAST (decimal-expression as CHAR(n)). * * * * 1. Leading zeroes in the input value * * are removed. * * 2. Leading zeroes are not added to an * * input value. * * 3. If the scale of the decimal value * * is zero, the decimal character is * * not returned. * * 4. A leading blank is not returned * * for a positive decimal value. * **************************************************************** * RECOMMENDATION: * **************************************************************** To help with migration to DB2 10 for z/OS and the impact of this change to the CHAR function on DB2 applications, the following support is added with this PTF: 1. After application of this PTF, the default behavior for the CHAR function will revert to the DB2 9 for z/OS result. Please refer to the DB2 9 for z/OS SQL Reference for documentation of the formatting of decimal data by the CHAR function. It is important that the ++HOLD directions are followed when applying this PTF. 2. A new DB2 subsystem parameter, BIF_COMPATIBILITY, has been added in DSN6SPRM. It specifies whether the CHAR function with decimal input should return the format provided by releases prior to DB2 10 (old) or the format provided by DB2 10 (new). This parameter is effective in any subsystem parameter (DSNZPxxx) module that is built after this PTF is applied. 3. A new trace record, IFCID 366, has been added to trace when the old format is returned by DB2. This can be used to identify which applications need to be changed to handle the new format returned in DB2 10. Applications should be changed to use the new format for DB2 10 before migrating to the next release of DB2. 4. Support for two new system schemas, SYSCOMPAT_V9 and SYSCURRENT, have been added. Including these two new schemas in the SQL path is a way to override the DB2 subsystem parameter (BIF_COMPATIBILITY) for unqualified invocations of the CHAR function in a particular application (i.e., where the CHAR function is not explicitly qualified with a schema). This will provide the ability to migrate applications to accept the new format.
Problem conclusion
Temporary fix
********* * HIPER * *********
Comments
This PTF provides relief for customers so that they can MIGRATE to DB2 10 and make the changes to the applications to accept the new formatting of decimal with the CHAR function as time permits. 1.This PTF adds a new DB2 subsystem parameter in DSN6SPRM called BIF_COMPATIBILITY that specifies whether the DB2 built-in function (BIF) CHAR should return results for decimal input in the format provided by DB2 9 for z/OS or that provided by the current release. DB2 10 for z/OS introduced an incompatible change in the formatting of decimal data by the CHAR built-in function. When the input data is decimal, the CHAR function no longer returns: * leading zeros * the trailing decimal point character * leading blanks for positive decimal values Valid settings for BIF_COMPATIBILITY are: V9 For decimal input, the CHAR built-in function returns data in the DB2 Version 9 format. When you run the installation CLIST in MIGRATE mode, V9 is the default setting for this parameter. CURRENT For decimal input, the CHAR built-in function returns data in the DB2 Version 10 format. When you run the installation CLIST in INSTALL mode, CURRENT is the default setting for this parameter. +-------------------------------------------------+ | BIF_- | Example inputs and results | | COMPAT- |---------------------------------------| | ABILITY | CHAR(000.1) | CHAR(1000.) | CHAR(1.1) | |---------|-------------|-------------|-----------| | CURRENT | '.1' | '1000' | '1.1' | |---------|-------------|-------------|-----------| | V9 | ' 000.1' | ' 1000.' | ' 1.1' | +-------------------------------------------------+ For DB2 data sharing it is recommended, but not required, that all members use the same setting. BIF_COMPATIBILITY is externalized on installation panel DSNTIPX as BIF COMPATIBILITY. The value specified in this field will be assigned by the DB2 installation CLIST to the BIF_COMPATIBILITY parameter in the customized DSNTIJUZ job. To reduce the impact of the migration incompatibility for the CHAR BIF, the DB2 10 installation CLIST selects the setting displayed in the BIF INCOMPATIBILITY field as follows: * If you run the DB2 10 installation CLIST in INSTALL mode, the BIF INCOMPATIBILITY field will contain BIF_COMPATIBILITY setting specified in the CLIST input (DSNTIDxx) member. In input members DSNTIDXA and DSNTIDXB, the default setting is CURRENT for new installations. * If you run the DB2 10 install CLIST in MIGRATE mode -and- the CLIST input member is named DSNTIDXA or DSNTIDXB then the BIF INCOMPATIBILITY field will contain V9. * If you run the DB2 10 install CLIST in MIGRATE mode and the CLIST input member is -not- named DSNTIDXA or DSNTIDXB then the BIF INCOMPATIBILITY field will contain the BIF_COMPATIBILITY setting specified in the CLIST input (DSNTIDxx) member You can override the BIF INCOMPATIBILITY field setting when installing or migrating a DB2 subsystem or the first member of a data sharing group. The BIF INCOMPATIBILITY field setting cannot be updated when installing or migrating a subsequent member of a data sharing group. If you have already installed or migrated to this version of DB2 you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update your customized copy of job DSNTIJUZ (4) Update private copies of the DSNTIDxx CLIST input member (5) Modify and rebind any applications using the CHAR(decimal) function. Drop and recreate any MQTs or IOEs using the CHAR(decimal) function. See below and in the hold actions for more details about application and object changes. Detailed guidance for these actions is provided in the hold actions track of this PTF. 2. DB2 will write a new IFCID 366 trace record when the old function is executed on DB2 10. This will occur only if the input to the CHAR function is decimal data. The trace record will be written out once per thread for a particular SQL statement. The trace record will help identify which applications need to be changed to support the new behavior. Note that if an index on expression(IOE) is created with the CHAR BIF in the index key, that the trace will be written during the execution of the INSERT SQL statement that inserts into the index. Likewise for a materialized query table (MQT), the trace record will be written on REFRESH TABLE. The trace can be started using the command -start trace(p) class(32) ifcid(366). Following is the description of the new record. ** IFCID 0366 is a serviceability trace. If a 366 record ** is written, this indicates that the DB2 9 CHAR ** built-in function has been invoked. There is an ** incompatible change to the output of the CHAR function ** for some decimal data. The zparm BIF_COMPATIBILITY ** and/or the SYSCOMPAT_V9 schema have been used by this ** application to get the old behavior. Please make the ** appropriate changes and rebind with the SYSCURRENT ** schema to use the DB2 10 CHAR(decimal) built-in function. QW0366 DSECT QW0366FN F The value '1' indicates that the DB2 9 for z/OS SYSIBM.CHAR(decimal-expr) function has been executed. QW0366SN F Statement number of the query QW0366PL DS CL8 Plan name for this query QW0366TS DS CL8 Timestamp for this query QW0366SI DS CL8 Statement Identifier QW0366TY DS XL2 Statement information QW0366DY EQU X'8000' Statement is dynamic QW0366SC EQU X'4000' Statement is static QW0366SE DS H Section number QW0366PC_Off DS H Offset from QW0366 to Package Collection ID QW0366PN_Off DS H Offset from QW0366 to Program name QW0366VL DS H Version length QW0366VN DS CL64 Version QW0366PC_D DSECT QW0366PC_Len DS H Length of Package Collection ID QW0366PC_Var CL128 %U Package Collection ID QW0366PN_D DSECT QW0366PN_Len DS H Length of Program Name QW0366PN_Var CL128 %U Program Name 3. Two new system schemas, SYSCOMPAT_V9 and SYSCURRENT, can be used in the current path special register or PATH bind option. These schemas can be used to override the zparm behavior for a particular application. The only function that exists in these schemas is the CHAR(decimal data) built-in function. SYSCURRENT indicates to use the new behavior and SYSCOMPAT_V9 indicates to use the old behavior. The new schema names MUST be in front of SYSIBM in the SQL path to be effective. The schemas and zparm value are used during function resolution to determine which CHAR function will be invoked (new or old). A BIND, REBIND, PREPARE OR CREATE MUST BE DONE TO CHANGE ANY EXISTING APPLICATION OR OBJECT (MQT or IOE ) TO USE THE NEW CHAR FUNCTION. After migration to DB2 10, packages that were bound before DB2 10 will use the old behavior for CHAR. Materialized query tables, and indexes on expressions that were created before DB2 10 also continue to use the old behavior. To convert to the DB2 10 behavior for CHAR: 1.Leave the BIF_COMPATIBILITY subsystem parameter setting as V9. 2.Change any affected applications to handle the new DB2 10 CHAR behavior. (This includes stored procedures, user-defined functions (non-inline), and trigger packages. ) The IFCID 366 trace record can be used to identify applications using the old CHAR(decimal) function. 3.Rebind and prepare packages using the SQL PATH with SYSCURRENT to use the new DB2 10 CHAR built-in function. 4.For views that reference the CHAR(decimal) built-in function, determine if the view needs to be changed to have the desired output. Drop and re-create the view using the SQL path with SYSCURRENT if necessary. Rebind any applications that reference the views using the SQL PATH with SYSCURRENT to use the new DB2 10 CHAR built-in function. Repeat this step for inline SQL scalar functions. 5.For materialized query tables or indexes on expressions that reference the CHAR(decimal) built-in function, drop and re-create the materialized query tables or indexes on expressions using the SQL PATH with SYSCURRENT. Issue the REFRESH TABLE statement for materialized query tables. Rebind any applications that reference the materialize query tables or indexes on expressions using the SQL PATH with SYSCURRENT to use the new DB2 10 CHAR built-in function. 6. When all applications and objects have been changed to handle the new behavior, change the value of the BIF_COMPATIBILITY subsystem parameter to CURRENT. When the subsystem parameter value is CURRENT, new applications, rebinds, and CREATE statements will use the new CHAR built-in function (unless SYSCOMPAT_V9 is explicitly specified in the path or the function is explictly qualified with SYSCOMPAT_V9). Materialized query tables and indexes on expressions use the CHAR(decimal) behavior that is specified during CREATE. CREATE will resolve the function based on the zparm and SQL path. If an SQL statement has been optimized to use an MQT or IOE, the CHAR(decimal) function must resolve to the same behavior (new or old) that was used during CREATE. If it does not the SQL statement will not be optimized to use the MQT or IOE. Views and inline SQL functions use the behavior of the SQL statement that references the view object. It is possible for references to the same view/function in different applications to get different behavior for the CHAR (decimal) function. WARNING : Currently packages bound on DB2 10 are returning the new behavior. After application of this PTF, the packages will return DB2 9 behavior (without rebinding). This applies to static SQL only. For dynamic SQL, the behavior returned will be the same as specified by the BIF_COMPATIBILITY parameter setting. It is important that the BIF_COMPATIBILITY parameter is set to the desired behavior, V9 or CURRENT, when applying this PTF. If V9 is chosen, then rebinds/drops/recreates are not necessary. If CURRENT is chosen, then rebind ALL packages referencing the CHAR(decimal) function. This applies to packages bound on previous releases and also on packages bound on DB2 10 prior to applying this PTF. All MQTs and IOEs that reference the CHAR(decimal)function must also be dropped and recreated after the PTF is applied with BIF_COMPATIBILITY set to CURRENT. Additional keywords : IFCID366 SQLCHAR SQLCODE420 SQLCAST SQLINCORR SQLINCORROUT INCORROUT DB2INCORR/K
APAR Information
APAR number
PM29124
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED UR1
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt
Submitted date
2010-12-17
Closed date
2011-05-10
Last modified date
2014-07-10
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK67578
Modules/Macros
DSN@XAZP DSNDFNR DSNDQWPZ DSNDQW05 DSNDSPRM DSNTIDXA DSNTIDXB DSNTIJUZ DSNTINST DSNTIPX DSNTXAZH DSNTXAZP DSNWVINT DSNWZIFA DSNXCOM DSNXEBIS DSNXFN DSNXGSFN DSNXOADT DSNXOBFA DSNXOBFF DSNXOBF5 DSNXODTR DSNXODTX DSNXOFN2 DSNXOGEX DSNXOOS2 DSNXOPT DSNXORFN DSNXOSCF DSNXOV0 DSNXRSBC DSNXRSB9 DSNXRT DSNXRUTL DSN6SPRM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK67578
UP11/05/25 P F105
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
10 July 2014