IBM Support

LI74703: SQL1224N from select count() using XMLTABLE with COLUMN PATH that uses FLWR statement

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A select count() query is crashing the database because we are
    marking the "elements" node for extraction but not allocating a
    sqlz value for binding out the result. During navigation tuple
    construction we try to initialize the sqlz value but trap
    because it was never allocated previously.
    
    Sample Query:
    
    select count(1)
    FROM networkmessage n,
         XMLTABLE ('$n/NetworkMessage' passing n.value as "n"
           COLUMNS
         cardId                         VARCHAR(100)    PATH
    '*:cardId',
         groupId                        VARCHAR(100)    PATH
    '*:groupId',
         res_p3_processing_cd_blast     VARCHAR(100)    PATH 'for $i
    in (*:response/*:elements) where $i/*:id = "3" return
    $i/*:value',
         res_p3_processing_cd_visa      VARCHAR(100)    PATH 'for $i
    in (*:response/*:elements/*:elements) where $i/../*:id = "3" and
    $i/*:id = "1" return $i/*:value'
    ) AS X
    LEFT JOIN Card c ON X.cardId = c.id;
    
    SQL ERROR:
    
    SQL1224N  The database manager is not able to accept new
    requests, has
    terminated all requests in progress, or has terminated your
    particular request
    due to an error or a force interrupt.  SQLSTATE=55032
    
    STACK:
    
    00002ADCF95E1BBC _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm + 0x00b4
    00002ADCF95E1C83 _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x0009
    00002ADCF5B4FA35 sqlo_trce + 0x03f3
    00002ADCF5B8E45D sqloEDUCodeTrapHandler + 0x0107
    0000003FA320DE70 address: 0x0000003FA320DE70 ; dladdress:
    0x0000003FA3200000 ; offset in lib: 0x000000000000DE70 ;
    00002ADCF4B3667F
    _ZN21XmlnvTupleConstructor12m_initZValueEP10sqlz_valueP16XmlnvBu
    fferArrayi + 0x0011
    00002ADCF65FBED8
    _ZN21XmlnvTupleConstructor22m_initTupleConstructorEP12sqlriXNavO
    bjP14XmlnvAutomaton + 0x03be
    00002ADCF65ED981
    _ZN14XmlnvAutomaton15m_initAutomatonEP14XmlnvNavigatorP12sqlriXN
    avObj + 0x02a1
    00002ADCF65F729B
    _ZN14XmlnvNavigator15m_initNavigatorEP13SQLO_MEM_POOLP11XMLSTORE
    _CBP12sqlriXNavObjP12XmlrnNodeMgrP8sqlrr_cb + 0x02d5
    00002ADCF65F6EC9
    _ZN14XmlnvNavigatorC9EP13SQLO_MEM_POOLP11XMLSTORE_CBP12sqlriXNav
    ObjP8sqlrr_cbPi + 0x0065
    00002ADCF65F6FC4
    _ZN14XmlnvNavigatorC1EP13SQLO_MEM_POOLP11XMLSTORE_CBP12sqlriXNav
    ObjP8sqlrr_cbPi + 0x0006
    00002ADCF5F45FD0 _Z12sqlriXMLScanP8sqlrr_cb + 0x0350
    00002ADCF675562C _Z14sqlriNljnPipedP8sqlrr_cb + 0x0230
    00002ADCF674E338 _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm +
    0x00b2
    00002ADCF498394E
    _Z10sqlrr_openP14db2UCinterfaceP15db2UCCursorInfo + 0x076a
    00002ADCF46D00B4
    _Z16sqljs_ddm_opnqryP14db2UCinterfaceP13sqljDDMObject + 0x015c
    00002ADCF46C7202
    _Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U
    Cinterface + 0x03b2
    00002ADCF46C757D _Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterface
    + 0x030d
    00002ADCF46C5244 address: 0x00002ADCF46C5244 ; dladdress:
    0x00002ADCF35E3000 ; offset in lib: 0x00000000010E2244 ;
    00002ADCF46C4FD6 address: 0x00002ADCF46C4FD6 ; dladdress:
    0x00002ADCF35E3000 ; offset in lib: 0x00000000010E1FD6 ;
    00002ADCF46C2DF5 address: 0x00002ADCF46C2DF5 ; dladdress:
    0x00002ADCF35E3000 ; offset in lib: 0x00000000010DFDF5 ;
    00002ADCF46C2C9B _Z17sqljsDrdaAsDriverP18SQLCC_INITSTRUCT_T +
    0x0051
    00002ADCF462947A _ZN8sqeAgent6RunEDUEv + 0x00c2
    00002ADCF4B28291 _ZN9sqzEDUObj9EDUDriverEv + 0x006d
    00002ADCF4B28221 _Z10sqlzRunEDUPcj + 0x0009
    00002ADCF4913B04 sqloEDUEntry + 0x02d0
    0000003FA32062F7 address: 0x0000003FA32062F7 ; dladdress:
    0x0000003FA3200000 ; offset in lib: 0x00000000000062F7 ;
    0000003FA26D1B6D clone + 0x006d
            (/lib64/libc.so.6)
    

Local fix

  • Workaround query, replace "for" with "let" to avoid bad
    codepath.
    
    select count(1)
    FROM networkmessage n,
         XMLTABLE ('$n/NetworkMessage' passing n.value as "n"
           COLUMNS
         cardId        VARCHAR(100)
      PATH '*:cardId',
         groupId       VARCHAR(100)
     PATH '*:groupId',
         res_p3_processing_cd_blast     VARCHAR(100)    PATH
    '*:response/*:elements[*:id = "3"]/*:value',
         res_p3_processing_cd_visa      VARCHAR(100)    PATH
    '*:response/*:elements/*:elements[../*:id = "3" and *:id =
    "1"]/*:value'
    ) AS X
    LEFT JOIN Card c ON X.cardId = c.id;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 LUW All Platforms                                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * A select count() query is crashing the database because we   *
    * are                                                          *
    * marking the "elements" node for extraction but not           *
    * allocating a                                                 *
    * sqlz value for binding out the result. During navigation     *
    * tuple                                                        *
    * construction we try to initialize the sqlz value but trap    *
    *                                                              *
    * because it was never allocated previously.                   *
    *                                                              *
    *                                                              *
    *                                                              *
    * Sample Query:                                                *
    *                                                              *
    *                                                              *
    *                                                              *
    * select count(1)                                              *
    *                                                              *
    * FROM networkmessage n,                                       *
    *                                                              *
    *      XMLTABLE ('$n/NetworkMessage' passing n.value as "n"    *
    *                                                              *
    *        COLUMNS                                               *
    *                                                              *
    *      cardId                         VARCHAR(100)    PATH     *
    *                                                              *
    * '*:cardId',                                                  *
    *                                                              *
    *      groupId                        VARCHAR(100)    PATH     *
    *                                                              *
    * '*:groupId',                                                 *
    *                                                              *
    *      res_p3_processing_cd_blast     VARCHAR(100)    PATH     *
    * 'for $i                                                      *
    * in (*:response/*:elements) where $i/*:id = "3" return        *
    *                                                              *
    * $i/*:value',                                                 *
    *                                                              *
    *      res_p3_processing_cd_visa      VARCHAR(100)    PATH     *
    * 'for $i                                                      *
    * in (*:response/*:elements/*:elements) where $i/../*:id = "3" *
    * and                                                          *
    * $i/*:id = "1" return $i/*:value'                             *
    *                                                              *
    * ) AS X                                                       *
    *                                                              *
    * LEFT JOIN Card c ON X.cardId = c.id;                         *
    *                                                              *
    *                                                              *
    *                                                              *
    * SQL ERROR:                                                   *
    *                                                              *
    *                                                              *
    *                                                              *
    * SQL1224N  The database manager is not able to accept new     *
    *                                                              *
    * requests, has                                                *
    *                                                              *
    * terminated all requests in progress, or has terminated your  *
    *                                                              *
    * particular request                                           *
    *                                                              *
    * due to an error or a force interrupt.  SQLSTATE=55032        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v95 Fixpack 5                                 *
    ****************************************************************
    

Problem conclusion

  • The fix allocates the sqlz which will be initialized to bind out
    the results during navigation.
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI74703

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-06-22

  • Closed date

    2010-02-22

  • Last modified date

    2010-02-22

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

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

    IC63463 IC63464 IC63470

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
22 February 2010