IBM Support

IZ47559: DB2XML-CE : 901 DURING OPTIMIZATION OF XMLAGG WITH DYNAMIC SQL S TATEMENTS INSIDE PSM

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • We currently optimize the xmlagg statement by preserializing.
    This optimization is turned off if a xmlagg statement is
    compiled inside a PSM but it turns out that for dynamic SQL
    statements inside PSM we are still preserializing. This is
    causing a 901 error.
    Fix is to add a compile-time check to catch both static SQL and
    dynamic SQL inside PSM and turn off the optimization.
    There is no risk involved, the changes are contained in one
    component (sqng), one file and are only a few lines of code.
    Repro:
    drop procedure psm_dynamic%
    create table tab_xml(docid int, xml xml);
    insert into tab_xml values(1, '<a>1</a>');
    insert into tab_xml values(1, '<a>1</a>');
    CREATE PROCEDURE psm_dynamic
    (OUT xml_data XML)
      LANGUAGE SQL
      SCOPE0:
      BEGIN
      declare dynstmt varchar(500) default 'SELECT xmlagg(xmlcol)
    FROM tab_xml';--
      declare stmt statement;
      prepare stmt from dynstmt;
      BEGIN
      declare cur1 cursor for stmt;
      open cur1 ;
      fetch from cur1 into xml_data;
      close cur1 ;
      set xml_data = xmlquery('$x/a' passing by ref xml_data as
    "x");
      END;
      END%
      call psm_dynamic(?)%
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "".)
    SQLSTATE=58004
    <StackTrace>
    ---FUNC-ADDR---- ------FUNCTION + OFFSET------
    0000002A9D940B45 ossDumpStackTraceEx + 0x0373
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2osse.so
    .1)
    0000002A9D93AE20 _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm +
    0x0270
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2osse.so
    .1)
    0000002A9D93ABAA _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x0050
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2osse.so
    .1)
    0000002A98C08FE2 sqlo_trce + 0x0332
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A98CF02DE sqloDumpDiagInfoHandler + 0x019c
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A95679650 address: 0x0000002A95679650 ; dladdress:
    0x0000002A9566D000 ; offset in lib: 0x000000000000C650 ;
      (/lib64/tls/libpthread.so.0)
    0000002A95676946 pthread_kill + 0x0036
      (/lib64/tls/libpthread.so.0)
    0000002A98CF09E1 sqloDumpEDU + 0x002d
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A97144E26 _Z15sqldDumpContextP8sqeAgentiiiiiPKcPvi +
    0x00e6
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A990734B3 _Z15sqlrr_dump_ffdcP8sqlrr_cbii + 0x07d9
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9A19C4B1 _Z13sqlzeDumpFFDCP8sqeAgentjP5sqlcai + 0x0037
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9A19BDA0
    _Z11sqlzeMapZrcP8sqeAgentjmjP5sqlcaiPK12sqlzeContextb + 0x0e76
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9915AB3A _Z11sqlrrMapZrcP8sqlrr_cbjmi + 0x007a
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A995390E4 _Z12sqlriXMLScanP8sqlrr_cb + 0x0710
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A993997F4 _Z15sqlriExecThreadP8sqlrr_cbP12sqlri_opparm +
    0x04de
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A99429BB8 _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm +
    0x0564
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9909F29A _Z29sqlrr_process_execute_requestP8sqlrr_cbi +
    0x1736
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A99076C40 _Z13sqlrr_executeP14db2UCinterfaceP9UCstpInfo
    + 0x02fe
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A96E8662C _ZN10pvmPackage14executeSectionEP5sqlcaj +
    0x053e
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A96E8D559 _ZN3PVM12executeQueryEj + 0x004f
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A96E8C406 _ZN3PVM3runEv + 0x082a
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A96E9BF34 pvm_entry + 0x0384
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A98C3CD70 sqloInvokeFnArgs + 0x00e4
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A995A8376
    _Z19sqlriInvokerTrustedP10sqlri_ufobP21sqlriRoutineErrorIntf +
    0x13ce
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A995A5B7D _Z18sqlriInvokeInvokerP10sqlri_ufob + 0x0441
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9959E815 _Z9sqlricallP8sqlrr_cb + 0x068d
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A993997F4 _Z15sqlriExecThreadP8sqlrr_cbP12sqlri_opparm +
    0x04de
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A99429BB8 _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm +
    0x0564
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9909F29A _Z29sqlrr_process_execute_requestP8sqlrr_cbi +
    0x1736
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A99076C40 _Z13sqlrr_executeP14db2UCinterfaceP9UCstpInfo
    + 0x02fe
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9774B192
    _Z19sqljs_ddm_excsqlsttP14db2UCinterfaceP13sqljDDMObject +
    0x0592
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9772463D
    _Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2
    UCinterface + 0x021d
    
    (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1)
    0000002A9772558F
    _Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterface + 0x01a3
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * DB2 LUW All platforms                                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * The problem is that we currently optimize the xmlagg         *
    * statement in xml queries BUT avoid doing so whenever the     *
    * xmlagg is found inside a PSM. In this case where the xmlagg  *
    * is used with Dynamic SQL inside a PSM we fail to turn off    *
    * optimization which results in 901 system error.              *
    *                                                              *
    * SQL0901N  The SQL statement failed because of a non-severe   *
    * system error.                                                *
    * Subsequent SQL statements can be processed.  (Reason "".)    *
    * SQLSTATE=58004                                               *
    *                                                              *
    * Example Query using xmlagg with Dynamic SQL inside PSM:      *
    *                                                              *
    * CREATE PROCEDURE psm_dynamic                                 *
    * (OUT xml_data XML)                                           *
    *   LANGUAGE SQL                                               *
    *   SCOPE0:                                                    *
    *   BEGIN                                                      *
    *   declare dynstmt varchar(500) default 'SELECT               *
    * xmlagg(xmlcol) FROM tab_xml';--                              *
    *   declare stmt statement;                                    *
    *   prepare stmt from dynstmt;                                 *
    *   BEGIN                                                      *
    *   declare cur1 cursor for stmt;                              *
    *   open cur1 ;                                                *
    *   fetch from cur1 into xml_data;                             *
    *   close cur1 ;                                               *
    *   set xml_data = xmlquery('$x/a' passing by ref xml_data as  *
    * "x");                                                        *
    *   END;                                                       *
    *   END%                                                       *
    *                                                              *
    * System Error statcktrace:                                    *
    *                                                              *
    * ---FUNC-ADDR---- ------FUNCTION + OFFSET------               *
    * 0000002A9D940B45 ossDumpStackTraceEx + 0x0373                *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2osse.s
    * .1)                                                          *
    * 0000002A9D93AE20 _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm +    *
    *                                                              *
    * 0x0270                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2osse.s
    * .1)                                                          *
    * 0000002A9D93ABAA _ZN11OSSTrapFile4dumpEmiP7siginfoPv +       *
    * 0x0050                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2osse.s
    * .1)                                                          *
    * 0000002A98C08FE2 sqlo_trce + 0x0332                          *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A98CF02DE sqloDumpDiagInfoHandler + 0x019c            *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A95679650 address: 0x0000002A95679650 ; dladdress:    *
    *                                                              *
    * 0x0000002A9566D000 ; offset in lib: 0x000000000000C650 ;     *
    *                                                              *
    *   (/lib64/tls/libpthread.so.0)                               *
    * 0000002A95676946 pthread_kill + 0x0036                       *
    *   (/lib64/tls/libpthread.so.0)                               *
    * 0000002A98CF09E1 sqloDumpEDU + 0x002d                        *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A97144E26 _Z15sqldDumpContextP8sqeAgentiiiiiPKcPvi +  *
    *                                                              *
    * 0x00e6                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A990734B3 _Z15sqlrr_dump_ffdcP8sqlrr_cbii + 0x07d9    *
    *                                                              *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9A19C4B1 _Z13sqlzeDumpFFDCP8sqeAgentjP5sqlcai +      *
    * 0x0037                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9A19BDA0                                             *
    * _Z11sqlzeMapZrcP8sqeAgentjmjP5sqlcaiPK12sqlzeContextb +      *
    * 0x0e76                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9915AB3A _Z11sqlrrMapZrcP8sqlrr_cbjmi + 0x007a       *
    *                                                              *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A995390E4 _Z12sqlriXMLScanP8sqlrr_cb + 0x0710         *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A993997F4                                             *
    * _Z15sqlriExecThreadP8sqlrr_cbP12sqlri_opparm +               *
    * 0x04de                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A99429BB8                                             *
    * _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm +               *
    * 0x0564                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9909F29A                                             *
    * _Z29sqlrr_process_execute_requestP8sqlrr_cbi +               *
    * 0x1736                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A99076C40                                             *
    * _Z13sqlrr_executeP14db2UCinterfaceP9UCstpInfo                *
    * + 0x02fe                                                     *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A96E8662C _ZN10pvmPackage14executeSectionEP5sqlcaj +  *
    *                                                              *
    * 0x053e                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A96E8D559 _ZN3PVM12executeQueryEj + 0x004f            *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A96E8C406 _ZN3PVM3runEv + 0x082a                      *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A96E9BF34 pvm_entry + 0x0384                          *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A98C3CD70 sqloInvokeFnArgs + 0x00e4                   *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A995A8376                                             *
    * _Z19sqlriInvokerTrustedP10sqlri_ufobP21sqlriRoutineErrorIntf *
    * +                                                            *
    * 0x13ce                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A995A5B7D _Z18sqlriInvokeInvokerP10sqlri_ufob +       *
    * 0x0441                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9959E815 _Z9sqlricallP8sqlrr_cb + 0x068d             *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A993997F4                                             *
    * _Z15sqlriExecThreadP8sqlrr_cbP12sqlri_opparm +               *
    * 0x04de                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A99429BB8                                             *
    * _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm +               *
    * 0x0564                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9909F29A                                             *
    * _Z29sqlrr_process_execute_requestP8sqlrr_cbi +               *
    * 0x1736                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A99076C40                                             *
    * _Z13sqlrr_executeP14db2UCinterfaceP9UCstpInfo                *
    * + 0x02fe                                                     *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9774B192                                             *
    * _Z19sqljs_ddm_excsqlsttP14db2UCinterfaceP13sqljDDMObject +   *
    *                                                              *
    * 0x0592                                                       *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9772463D                                             *
    * _Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db
    * UCinterface + 0x021d                                         *
    *                                                              *
    * (/view/db2_v95fp5_linuxamd64_n090311/vbs/INST/lib/libdb2e.so.1
    * 0000002A9772558F                                             *
    * _Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterface + 0x01a3     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Customers should upgrade to v95 FP5 to avoid this problem.   *
    ****************************************************************
    

Problem conclusion

  • The fix checks if an xmlagg is used in static SQL or Dynamic SQL
    inside a PSM and turns off the optimization accordingly.
    With the fix the user will not hit 901 system error.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ47559

  • Reported component name

    DB2 UDB ESE AIX

  • Reported component ID

    5765F4100

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-03-24

  • Closed date

    2010-02-19

  • Last modified date

    2010-02-19

  • 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 UDB ESE AIX

  • Fixed component ID

    5765F4100

Applicable component levels

  • R950 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 950

Reference #: IZ47559

Modified date: 19 February 2010


Translate this page: