IBM Support

PI50881: IDAA OFFLOADED QUERY RETURN LEADING BLANK WHEN TRANSLATING DECIMAL TO VARCHAR IF ZPARM BIF_COMPATIBILITY IS SET TO V9

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • IDAA offloaded query return leading blank when translating
    decimal to varchar if zparm BIF_COMPATIBILITY is set to V9
    
    create table tbdec ( col01 decimal ( 8 , 0 ));
    insert into tbdec values ( 12345678 );
    
    ==> offload query to idaa:
    
    select varchar(col01) from tbdec;
    
    => DB2:    col01
             -+----------+
               19511207.  length(varchar(col01)) = 8
    
    => IDAA:   col01
             -+----------+
                19511207. length(varchar(col01)) = 9
               ! here the trailing blank
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 11 for z/OS users of QUERY           *
    *                 ACCELERATION, where the query uses           *
    *                 the built-in function VARCHAR to convert     *
    *                 a decimal value to a varchar string          *
    ****************************************************************
    * PROBLEM DESCRIPTION: User specified QUERY ACCELERATION for   *
    *                      a query that used the built-in function *
    *                      VARCHAR to convert a decimal type       *
    *                      column to a varchar string, and         *
    *                      the DB2 zparm BIF_COMPATIBILITY =       *
    *                      V9_DECIMAL_VARCHAR . The accelerated    *
    *                      query result output for that column     *
    *                      incorrectly contained leading blanks    *
    *                      and leading zeros as shown below --     *
    *                             +------------+                   *
    *                             |            |                   *
    *                             +------------+                   *
    *                           1_|  12345678. |                   *
    *                           2_|  00000000. | << leading zeroes *
    *                           3_|  00000001. |                   *
    *                             +------------+                   *
    *                               |_leading blank                *
    *                                                              *
    *                      When using zparm BIF_COMPATIBILITY =    *
    *                      V9_DECIMAL_VARCHAR , no leading blanks  *
    *                      or leading zeroes should be seen in     *
    *                      the output result for this column using *
    *                      VARCHAR built-in function.              *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The user requested QUERY ACCELERATION behavior for a query where
    all of the following applied:
     1. QUERY ACCELERATION behavior was either ENABLE , ENABLE WITH
        FAILBACK , ELIGIBLE , or ALL , and
     2. the query used the built-in function (BIF) VARCHAR to
        convert a decimal type column of a table to a varchar string
        for the output result, and
     3. the DB2 subsystem parameter (zparm) BIF_COMPATIBILITY =
        V9_DECIMAL_VARCHAR .
                                                                   .
    The accelerated query result returned from the accelerator
    contained leading zeroes and leading blanks for that VARCHAR of
    the decimal type column (depending upon the column's value).
    However, when using zparm BIF_COMPATIBILITY=V9_DECIMAL_VARCHAR,
    no leading blanks or leading zeroes should have been returned in
    the column VARCHAR result.
    When the query is "not" accelerated and run on DB2, no leading
    blanks or leading zeroes appear in the that column VARCHAR
    result.
                                                                   .
    The following simple example illustrates what occurred:
                                                                   .
      CREATE TABLE DEC_TABLE (DEC_COL DECIMAL (8,0));
      INSERT INTO DEC_TABLE VALUES (12345678);
      INSERT INTO DEC_TABLE VALUES (0);
      INSERT INTO DEC_TABLE VALUES (1);
                                                                   .
      SET CURRENT QUERY ACCELERATION = ELIGIBLE;
                                                                   .
      SELECT VARCHAR(DEC_COL) FROM DEC_TABLE FOR FETCH ONLY;
                                                                   .
       - returned result from accelerated query was
                                                                   .
         +------------+
         |            |
         +------------+
       1_|  12345678. |
       2_|  00000000. | <<< leading zeroes
       3_|  00000001. |
         +------------+
           |_leading blank
                                                                   .
      - however the expected result (and the result if query was
        run on DB2z and not accelerated) should be
                                                                   .
         +------------+
         |            |
         +------------+
       1_| 12345678.  |
       2_| 0.         | << leading zeroes removed
       3_| 1.         |
         +------------+
           |_leading blank removed
                                                                   .
     -------  End of example  ------------------------------------
                                                                   .
    The VARCHAR BIF conversion of the decimal type column resulted
    in leading blank and leading zeroes for the accelerated query
    because, when DB2 transformed the query to be run on the
    accelerator, DB2 did not consider the DB2 zparm
    BIF_COMPATIBILITY = V9_DECIMAL_VARCHAR setting. As a result,
    DB2 did not specify the correct functions in the transformed
    query to remove leading blanks and leading zeroes from
    the decimal input to the VARCHAR BIF, so that the returned
    data would not include leading blanks and leading zeroes.
                                                                   .
    

Problem conclusion

  • DB2 code for transforming a query for acceleration was modified
    to apply the DB2 subsystem parameter BIF_COMPATIBILITY value
    V9_DECIMAL_VARCHAR to the VARCHAR BIF used in the query and
    generate the correct specifications in the query to remove
    the leading blanks and leading zeroes from the decimal input to
    the VARCHAR BIF. This prevents the accelerated query from
    returning leading blanks and zeroes for that VARCHAR BIF
    output.
                                                                   .
    To make this fix effective for a static application containing
    accelerated static SQL queries, the application must be rebound
    after application of this PTF.
                                                                   .
    Additional search keywords: IDAAV4R1/K
                                INCORROUT SQLINCORROUT SQLINCORR
                                DB2INCORR/K
    

Temporary fix

  • AI50881
    

Comments

APAR Information

  • APAR number

    PI50881

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-10-21

  • Closed date

    2015-11-20

  • Last modified date

    2016-01-04

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UI33175

Modules/Macros

  •    DSNXONZQ DSNXONZS
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI33175

       UP15/12/08 P F512

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.



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PI50881

Modified date: 04 January 2016


Translate this page: