IBM Support

IC91458: A QUERY MIGHT RETURN INCORRECT RESULTS OR TRAP DURING OPTIMIZATI ON IN QRW PHASE IN FUNCTION SQLNR_OR_PREDS_OPT, IN 9.7 FIXPACK 8

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • An impacted query might return the wrong results if it has the
    OR predicate with two sub-terms. One predicate sub-term is "colX
    IN (<literal1>, ...<literalN>)", and the other is "colX NOT IN
    (<literal1>, ...<literalN>)".  The symptom is that the optimized
    statement section inside the db2exfmt plan file contains the
    "colX IN (<literal1> sub-term, ...<literalN>)" but misses the
    "colX NOT IN (<literal1>, ...<literalN>)" sub-term.
    
    Furthermore, the impacted query also might return wrong results
    if it has the following OR predicate with two sub-terms: one
    sub-term is "colX LIKE <patternexp>", and the other is "colX NOT
    LIKE <patternexp>".  The symptom experienced by this issue is
    that the optimized statement section inside the db2exfmt plan
    file contains the "colX LIKE <patternexp>" sub-term, but misses
    the "colX NOT LIKE <patternexp>" sub-term.
    
    A query might trap when the query contains a compound-predicate
    that meets the following conditions:
    - the compound-predicate takes the form (pred1 OR (pred2 OR
    pred3))
    - pred1 and pred2 are exactly matched, or pred1 subsumes pred2
    (For example, c1 in (1,2,3) subsumes c1 in (1,2))
    
    When both conditions are satisfied, a query compilation might
    trap and the call stack will show the sqlnr_or_preds_opt
    function.
    
    This only occurs in the 9.7 fix pack 8 release of the DB2
    product.
    
    On a Linux platform a trap with SEGV might occur, with the trap
    file showing:
    
    0x00002AAAAD5CF043 sqloEDUCodeTrapHandler
    0x00002AAAABA681E6 _ZNK9sqlnq_pid2opEv
    0x00002AAAAD3C9BBA _Z17sqlnr_or_pred_optP9sqlnq_pidP3locRb
    0x00002AAAAD3C9ECF _Z18sqlnr_or_preds_optP9sqlnq_oprP3loc +
    0x00002AAAABF6090B _Z20sqlnr_qrwprep_phase2P3locPi + 0x1493
    0x00002AAAABF41A01
    _Z18sqlnr_prep2_actionP10sqlnr_qrwaPiP14sqlnr_progr
    0x00002AAAABF5E802
    _Z10sqlnr_compPiiP16sqlnr_rule_stateP10sqlnr_qrwaP1
    0x00002AAAABF5E62D
    _Z9sqlnr_seqPiP10sqlnr_qrwaP14sqlnr_progressP12sqln
    0x00002AAAABF5E49B _Z9sqlnr_rcciP10sqlnr_qrwaPiP14sqlnr_progress
    0x00002AAAABF3FCD5 _Z9sqlnr_exeP9sqlnq_qur + 0x04b9
    0x00002AAAABD3E842
    _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_comp
    ...
    
    On an AIX platform a -901 error might be observed instead of a
    trap:
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "New tree
    is not given".)
    SQLSTATE=58004
    
    The db2diag.log log shows:
    
    2013-04-09-10.18.20.879038+120 I4899A840    LEVEL: Severe
    PID     : 25886852          TID  : 1544     PROC : db2sysc 0
    INSTANCE: db2v978           NODE : 000      DB   : FOO
    APPHDL  : 0-103             APPID: *LOCAL.db2v978.130409081812
    AUTHID  : DB2V978
    EDUID   : 1544              EDUNAME: db2agent (FOO) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
    probe:300
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 21
     sqlerrmc: New tree is not given
     sqlerrp : SQLNQ21E
    ...
    2013-04-09-10.18.20.921390+120 E60158A1518  LEVEL: Info
    PID     : 25886852          TID  : 1544     PROC : db2sysc 0
    INSTANCE: db2v978           NODE : 000      DB   : FOO
    APPHDL  : 0-103             APPID:
    *LOCAL.db2v978.130409081812
    AUTHID  : DB2V978
    EDUID   : 1544                 EDUNAME: db2agent (FOO) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
              "unexpected error but state is OK"
    ...
    DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 21
     sqlerrmc: New tree is not given
     sqlerrp : SQLNQ21E
     sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
    0x00000000
               (4) 0x00000000      (5) 0xFFFFFFF6      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
    DATA #8 : Hex integer, 4 bytes
    0x00000040
    DATA #9 : String, 245 bytes
    Compiler error stack for rc = -2144272209:
    sqlnn_cmpl[370]
    sqlnr_exe[600]
    sqlnr_rcc[100]
    sqlnr_seq[100]
    sqlnr_comp[110]
    sqlnr_prep2_action[100]
    sqlnr_qrwprep_phase2[620]
    sqlnr_or_preds_opt[100]
    sqlnr_or_pred_opt[400]
    sqlnq_pid::updated_tree[10]
    

Local fix

  • There are two ways to fix this issue:
    
    (1) Reduce the query optimization level to below 5.
    For example, the DB2 product set the current query optimization
    level to 3.
    
    (2) Manually rewrite the predicate to:
    
    - remove the exact OR sub-term
    select * from t where (c1 >= 1 or c1 >= 2);
    
    Or, alternatively rewrite the predicate to:
    
    - move the exact OR sub-terms to the end of the OR predicate
    select * from t where (c1 >= 2 or c1 >= 1 or c1 >= 1);
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users running DB2 version 9.7.0.8.                       *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7.0.9.                              *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 9.7.0.9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC91458

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-10

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC91458

Modified date: 16 December 2013