A fix is available
APAR status
Closed as new function.
Error description
V10 made an incompatible changes to: VARCHAR(decimal) built-in function CAST (decimal AS VARCHAR) CAST (decimal AS CHAR) For example the incompatible changes for VARCHAR are : 1 - remove leading zero VARCHAR('00.10') V10 result is '.10' V9 result is '0.10' 2 - no trailing decimal point VARCHAR('1.') V10 result is '1' V9 result is '1.' The release incompatibility is recorded in V10 Install Guide Section : "Application and SQL release incompatibilities for migration from Version 9.1 " This APAR will deliver functionality in DB2, so for CHAR, VARCHAR, and CAST it semantically bahaves like v9. with BIF_COMPATIBILITY = 'V9_DECIMAL_VARCHAR' ( new ZPARM value) . IFCID 366 trace record also would be updated, to be written when the old semantics are used for the VARCHAR function and CAST specifications . The record will be the same as documented in APAR PM29124 IFCID 366 trace record except the field function type QW0366FN will have the value 2 instead of 1.
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 for z/OS users of decimal data * * with the VARCHAR function or CAST * * specification * **************************************************************** * PROBLEM DESCRIPTION: In Version 10, the formatting of * * decimal data has changed for the * * VARCHAR function and the CAST * * specification with decimal input * * and VARCHAR or CHAR output. * * * * There are two changes: * * * * 1. Leading zeroes are removed * * from the input decimal value. * * * * 2. The decimal point is not returned * * if the scale of the decimal value * * is zero. * **************************************************************** * RECOMMENDATION: * **************************************************************** After application of this PTF, the default behavior for the VARCHAR function and CAST specification with decimal input will revert to the DB2 9 for z/OS result if the BIF_COMPATIBILITY system parameter is set to the new value V9_DECIMAL_VARCHAR. Please refer to the DB2 9 for z/OS SQL Reference for documentation of the formatting of decimal data. Following are examples of the differences in the output of VARCHAR and CAST between V9 and V10 : - Remove leading zero VARCHAR(00.10) or CAST(00.10 AS VARCHAR(4)) or CAST(00.10 AS CHAR(4)) V10 result is '.10' V9 result is '0.10' - No trailing decimal point VARCHAR(1.) CAST(1. AS VARCHAR(2)) or CAST(1. AS CHAR(2)) V10 result is '1' V9 result is '1.' To help with migration to DB2 10 for z/OS and the impact of this change on DB2 applications, the following support is added: 1. This PTF adds a new setting to DB2 subsystem parameter BIF_COMPATIBILITY. The new value is V9_DECIMAL_VARCHAR. APAR PM29124 provided relief for the CHAR function incompatibility and provided a DB2 subsystem parameter, BIF_COMPATIBILITY with options V9 and CURRENT, to enable and disable it. This PTF increases the scope of DB2 subsystem parameter BIF_COMPATIBILITY to include VARCHAR, CAST( dec AS CHAR) and CAST( dec AS VARCHAR). It also adds a third option, V9_DECIMAL_VARCHAR. These settings allow you to specify whether: * The current-release format should be returned by all of these expressions (BIF_COMPATIBILITY=CURRENT) * The V9 format should be returned by all of these expressions (BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR) * The current release format should be used by all of these expressions except the CHAR function which should return the V9 format (BIF_COMPATIBILITY=V9) +-------------------------------------------------+ | BIF_- | Example inputs and results (1) | | COMPAT- |---------------------------------------| | IBILITY | CHAR(000.1) | CHAR(1000.) | CHAR(1.1) | |---------|-------------|-------------|-----------| | CURRENT | '.1' | '1000' | '1.1' | |---------|-------------|-------------|-----------| | V9 | ' 000.1' | ' 1000.' | ' 1.1' | |---------|-------------|-------------|-----------| | V9_DEC- | | | | | IMAL_- | ' 000.1' | ' 1000.' | ' 1.1' | | VARCHAR | | | | +-------------------------------------------------+ +-------------------------------------------------+ | BIF_- | Example inputs and results (2) | | COMPAT- |---------------------------------------| | IBILITY | VARCHAR( 00.10 ) | VARCHAR( 1. ) | |---------|--------------------|------------------| | CURRENT | '.10' | '1' | |---------|--------------------|------------------| | V9 | '.10' | '1' | |---------|--------------------|------------------| | V9_DEC- | | | | IMAL_- | '0.10' | '1.' | | VARCHAR | | | +-------------------------------------------------+ For DB2 data sharing it is recommended that all members use the same setting. After applying this PTF, please refer to the ++HOLD directions to set the zparm value to V9_DECIMAL_VARCHAR if V9 results are desired. a. Static SQL If a package has been bound on V10 with static SQL then it must be rebound to pick up the new zparm value and return V9 results. If a package has been bound on V9 with static SQL then it will return V9 results. No rebind is necessary. b. Dynamic SQL Dynamic SQL will return V9 results once the new zparm value is set. Views, materialized query tables(MQTs),and index on expressions (IOEs) created before Version 10 will continue to get V9 behavior. 2. DB2 will write an IFCID 366 trace record when the V9 format is returned on DB2 10. The trace 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 VARCHAR/CAST 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) IFCID 366 was added by apar PM29124. This apar adds a new value for QW0366FN to indicate VARCHAR or CAST executed using the V9 semantics. QW0366FN F The value '1' indicates that the DB2 9 for z/OS SYSIBM.CHAR(decimal-expr) function has been executed. The value '2' indicates that the DB2 for z/OS Version 9 SYSIBM.VARCHAR(decimal-expr) function, CAST (decimal AS CHAR) or CAST (decimal AS VARCHAR) expression has been executed. 3. 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 VARCHAR function has been added to these schemas by this PTF. Currently the CHAR function only exists in these schemas. The new schema names must be in front of SYSIBM in the path to be effective. The schemas and zparm value are used during function resolution to determine which CHAR or VARCHAR function will be invoked (V10 or V9). A bind, rebind, prepare or create must be done to change any existing application or object (MQT or IOE ) to use the new CHAR or VARCHAR function. The CAST specifications will only be controlled by the zparm. The schemas do not apply to CAST. --------------------------------------------------------------- To switch to new behavior in V10 : 1. Specify BIF_COMPATIBILITY zparm as V9_DECIMAL_VARCHAR 2. Monitor the 366 trace records to identify applications that may need changes for the new V10 behavior. 3. Change any affected applications to handle the new V10 VARCHAR and CHAR behavior. For CAST, rewrite it using the appropriate CHAR or VARCHAR function and a CAST to the correct length if needed. 4. Rebind/Prepare the package using the PATH with SYSCURRENT to use the new V10 CHAR and VARCHAR built-in functions. SYSCURRENT does not apply to CAST so CAST needs to be rewritten using one of the built-in functions. Note : For native stored procedures (SQLPL) and non-inline SQL scalar functions, follow the directions in bullet 3 for a package. 5. For views referencing these functions/casts, determine if the view needs to be changed to get the desired output. Drop and recreate the view using the path bind option with SYSCURRENT only if necessary. Then rebind any applications referencing the view using the path bind option with SYSCURRENT to use the new V10 CHAR or VARCHAR built-in functions. Note : For inline SQL scalar functions, follow the directions in bullet 4 for views. 6. For MQTs or IOEs referencing these functions/casts, drop and recreate the MQT or IOE using the path bind option with SYSCURRENT. Execute REFRESH TABLE for MQTs. Then rebind any applications referencing the MQT or IOE using the path bind option with SYSCURRENT to use the new V10 CHAR or VARCHAR built-in function. 7. Change the BIF_COMPATIBILITY zparm to CURRENT when all applications have been changed. New applications, rebinds, creates will use the new VARCHAR or CHAR or CASTs. Additional keywords : SQLMIGRATION SQLVARCHAR SQLCAST SQLDECIMAL
Problem conclusion
Temporary fix
Comments
APAR Information
APAR number
PM66095
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
2012-06-04
Closed date
2012-09-06
Last modified date
2012-11-27
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK81581
Modules/Macros
DSNDQW05 DSNTINST DSNTIPX DSNWZIFA DSNXGSFN DSNXOCF DSNXODTR DSNXODTX DSNXOEX1 DSNXOFN2 DSNXONZQ DSNXONZS DSNXOOS1 DSNXOOS2 DSNXORFN DSNXOSCF DSNXOV0 DSNXOXEX DSNXRSBC DSNXRSB9 DSN6SPRM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK81581
UP12/09/25 P F209
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:
27 November 2012