PM29124: HELP WITH HANDLING THE RELEASE INCOMPATIBLE CHANGE FOR THE CHAR(decimal) BUILT-IN FUNCTION ON DB2 V10

A fix is available

Subscribe

You can track all active APARs for this component.

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.



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM29124

Modified date:

2014-07-10

Translate my page

Machine Translation

Content navigation