IBM Support

PM66095: HELP WITH HANDLING THE RELEASE INCOMPATIBLE CHANGE FOR VARCHAR( DECIMAL), CAST (DECIMAL AS CHAR), CAST(DECIMAL AS VARCHAR)

A fix is available

Subscribe

You can track all active APARs for this component.

 

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