IBM Support

PI27501: BIND PACKAGE WITH QUERYACCELERATION(ALL) RETURN SQLCODE4742N RC15 FOR STATIC QUERY WITH :HV WITH DATATYPE TIMESTAMP

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • BIND PACKAGE for acceleration fails with sqlcode4742n rc15 (
    dsn_querinfo_table.qi_data = (EXPR) ) if there is a query with a
    predicate that has <timestamp column> <op> :HV DB2 and
    datatype of the hostvar with timestamp precision 12.
    
    e.g.
    
    hv-ts1  PIC X(26) VALUE SPACE.
    hv-ts2  PIC X(26) VALUE SPACE.
    
    select * from table
     where timestampcol between :hv-ts1 and :hv-ts2
    
    bind package with queryacceleration(all) fails with
    
    DSNX200I  @ BIND SQL ERROR
               USING xxxxx AUTHORITY
               PLAN=(NOT APPLICABLE)
               DBRM=dbrmname
               STATEMENT=351
               SQLCODE=-4742
               SQLSTATE=560D5
               TOKENS=15
               CSECT NAME=DSNXODML
               RDS CODE=-434
    DSNT233I  @ UNSUCCESSFUL BIND FOR
               PACKAGE =
    loc.collid.pkg.(version)
    

Local fix

  • use
    
    select * from table
     where timestampcol >= :hv-ts1
       and timestampcol <= :hv-ts2
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 for z/OS who use            *
    *                 query acceleration.                          *
    ****************************************************************
    * PROBLEM DESCRIPTION: A static SQL statement that contains    *
    *                      a predicate with a timestamp datatype   *
    *                      and a host variable is incorrectly      *
    *                      blocked from routing to an accelerator. *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    A static SQL statement that contains a predicate with a
    timestamp datatype and a host variable is incorrectly
    blocked from routing to an accelerator. EXPLAIN of the
    statement will populate DSN_QUERYINFO_TABLE with REASON_CODE=15
    and QI_DATA='(EXPR)'. If BIND option QUERYACCELERATION(ALL) or
    GETACCELARCHIVE(YES) is used, SQLCODE -4742 reason code 15
    is issued for the statement.
    
    For example, the column C1 is a TIMESTAMP datatype:
    DECLARE CURS01 WITH ROWSET POSITIONING FOR
    SELECT MONTH
    FROM T1
    WHERE C1 > :HV WITH UR
    END-EXEC
    
    
    For static SQL queries that have been bound for acceleration
    and have SQL statements similar to the above example, REBIND
    the package for the failing query after applying this PTF.
    
    Keywords:
    IDAAV3R1/K IDAAV4R1/K SQLTIMESTAMP SQLCODE4742
    

Problem conclusion

  • DB2 code is fixed for expected results and behavior.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI27501

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-10-14

  • Closed date

    2014-11-04

  • Last modified date

    2014-12-01

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

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

    UI22766 UI22767

Modules/Macros

  •    DSNXOEX1 DSNXOOS1
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI22766

       UP14/11/19 P F411

  • RB10 PSY UI22767

       UP14/11/19 P F411

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: A10

Reference #: PI27501

Modified date: 01 December 2014


Translate this page: