IBM Support

PI18866: AB04E RC00E70005 AT DSNXERT2 :P110 WHEN ACCELERATED STATIC QUERY IS A RESULT SET FROM REMOTE STORED PROCEDURE OR NATIVE SQL PROC

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When an accelerated static query is also a result set returned
    from a remote stored procedure or native SQL procedure (aka
    SQLPL procedure), the following abend occurs when the remote
    procedure terminates:
       ABEND04E RC00E70005 at DSNXERT2 :P110
    .
    The abend does not occur for a 'local' procedure result set.
    .
    Search keywords: IDAAV4R1/K SQLPROCEDURE SQLSP SQLSTOREDPROC
                     OFFSETP110 SQLNATIVESQLPL
    

Local fix

  • n.a.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 for z/OS users of an accelerated     *
    *                 static SQL query in one of the following     *
    *                 scenarios:                                   *
    *                  - as a result set returned from a DB2       *
    *                    remote stored procedure or remote native  *
    *                    SQL procedure (or SQL PL procedure)       *
    *                  - as a static query in an SQLJ application  *
    *                    that is run using the IBM  Data Server    *
    *                    Driver for JDBC and SQLJ, where           *
    *                    the user's SQLJ application DB2 package   *
    *                    was bound using db2sqljcustomize command  *
    *                    parameter ' -onlinecheck NO '.            *
    *                    Parameter ' -onlinecheck YES ' is         *
    *                    the default.                              *
    ****************************************************************
    * PROBLEM DESCRIPTION: In the reported problem, a DB2 remote   *
    *                      stored procedure or remote native SQL   *
    *                      procedure (or SQL PL procedure)         *
    *                      returned a result set that is also      *
    *                      an accelerated static SQL query.        *
    *                      When that procedure terminated          *
    *                      the following abend occurred:           *
    *                                                              *
    *                       ABEND04E RC00E70005 at DSNXERT2 :P110  *
    *                                                              *
    *                      The abend can also occur when using     *
    *                      an accelerated static query in an SQLJ  *
    *                      application bound as a DB2 package      *
    *                      using db2sqljcustomize command          *
    *                      parameter ' -onlinecheck NO ', where    *
    *                      there is no DB2 routine procedure used  *
    *                      in the scenario.                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The user invoked a remote DB2 stored procedure that returns
    a result set that is also an accelerated static SQL query, and
    when the remote procedure terminated, the following
    DB2 application abend occurred:
                                                                   .
     ABEND04E RC00E70005 at DSNXERT2 :P110
                                                                   .
    The abend can also occur if the remote procedure is a DB2
    native SQL procedure (or SQL PL procedure).  The abend does
    *not* occur for the local DB2 procedure scenario, nor when
    an accelerated dynamic query is returned as the result set from
    the remote procedure.
                                                                   .
    The abend can also occur without using DB2 routine procedures
    for an accelerated static SQL query in an SQLJ application that
    is run using the IBM  Data Server Driver for JDBC and SQLJ,
    where the user's SQLJ application was bound as a DB2 package
    using db2sqljcustomize command parameter ' -onlinecheck NO '.
    Parameter '-onlinecheck YES ' is the default, and the abend does
    not occur for an SQLJ application when using this default.
                                                                   .
    The abend occurred when DB2 attempted to return SQL descriptor
    information about the accelerated static SQL query, but DB2 had
    not correctly saved the SQL descriptor information for
    the accelerated static query, resulting in the abend.
    This occurrence of the DSNXERT2 :P110 abend is limited to
    the specific accelerated static query scenarios described above,
    where SQL descriptor information about the static query
    is needed. It is not applicable to other typical usages of
    accelerated static SQL queries.
                                                                   .
    To avoid this reported abend for an SQLJ application, bind the
    DB2 SQLJ application package again but using
    the db2sqljcustomize command parameter '-onlinecheck YES'
    instead  of 'onlinecheck NO'.
                                                                   .
    To make this fix effective for the accelerated static SQL query,
    after application of this PTF,
     - the stored procedure package must be rebound or for a native
       SQL procedure an ALTER PROCEDURE must be done.
     - for the SQLJ application, the associated DB2 package must be
       bound or rebound via the method you normally use for
       your SQLJ applications for DB2 for z/OS.
    

Problem conclusion

  • DB2 was changed to correctly return SQL descriptor information
    about the accelerated static SQL query used in the scenarios
    described above.
                                                                   .
    Search keywords: IDAAV4R1/K SQLPROCEDURE SQLSP SQLSTOREDPROC
                     OFFSETP110 JCCSQLJ JCCT2ZOS JCCT4
    

Temporary fix

  • AI18866
    BI18866
    

Comments

APAR Information

  • APAR number

    PI18866

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-05-29

  • Closed date

    2014-08-25

  • Last modified date

    2015-11-23

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

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

    UI20848 UI20849

Modules/Macros

  •    DSNXELX  DSNXEPM  DSNXEPP  DSNXERT2 DSNXODML
    DSNXOQ2
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI20848

       UP14/09/10 P F409

  • RB10 PSY UI20849

       UP14/09/10 P F409

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 #: PI18866

Modified date: 23 November 2015


Translate this page: