IBM Support

IC77942: INSTANCE ABEND INSTEAD OF SQL0120N FOR CERTAIN QUERIES CONTAINING OLAP FUNCTIONS WITHIN CASE EXPRESSION

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A query which uses an OLAP function in the WHEN clause of a CASE
    expression
    
    eg. select ... ( case when ... <OLAP function> end from <table1>
    )  from <table2>
    
    may be rewritten internally by the query compiler to push down
    the OLAP function into a predicate.
    This should result in error SQL0120N, but currently this is not
    intercepted and leads to an unstable
    internal representation of the query.
    
    This can lead to problems of several symptoms.
    
    e.g. A Segmentation violation with the following stack :
    
    -------Frame------ ------Function + Offset------
    0x090000000D0F0414 op__18sqlnn_xiiphyparentFv@glue1AE + 0x0
    0x090000000D11B524 drop__9sqlnq_qtbFP13sqlnq_memPool + 0xC
    0x090000000E2D92A8 box_drop_ts__9sqlnq_qtbFiN21 + 0x998
    0x090000000D5E8E58 box_drop__9sqlnq_qtbFiT1 + 0x44
    0x090000000EBCBC64 box_drop__9sqlnq_qtbFiT1@glueD1E + 0x78
    0x090000000D3CE180
    drop_dependent_counting_quns__9sqlnq_pidFP13sqlnq_memPool + 0x80
    0x090000000D0FE85C destroy_ptree__9sqlnq_pidFP3loci + 0xF0
    0x090000000FBFD258
    sqlnr_selbox_merge__FP9sqlnq_qtbP9sqlnq_qunT1Pii + 0xDA8
    0x090000000FC48324
    sqlnr_sel_mrg_action__FP10sqlnr_qrwaPiP14sqlnr_progress + 0x44
    0x090000000DEE1B68
    sqlnr_comp__FPiiP16sqlnr_rule_stateP10sqlnr_qrwaP14sqlnr_progres
    s@AF494_280
    + 0x170
    0x090000000DEBFB54
    sqlnr_comp__FPiiP16sqlnr_rule_stateP10sqlnr_qrwaP14sqlnr_progres
    s
    + 0x44
    0x090000000DEBF978
    sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass +
    0xBC
    ...
    
    or SQL0901 error :
    
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "Build S_X
    OP : Invalid
    Opcode 0 found, ref_arity 2, sqlng_pid ID is 94".)
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Possible instance abend using OLAP functions and CASE        *
    * expresssions                                                 *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to V9.7 Fix Pack 6                                   *
    ****************************************************************
    certain types of yet unsupported queries may lead to
    aninstance abend. This apar will change this behaviour so
    thaterror SQL0120N will be returned instead.
    

Problem conclusion

  • This problem has first been fixed in V9.7 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77942

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-04

  • Closed date

    2012-06-13

  • Last modified date

    2012-06-13

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

    IC70958

  • 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 #: IC77942

Modified date: 13 June 2012