IBM Support

PI55903: SQLCODE919 WHEN CALL SP FROM CLI WHICH OFFLOADS INSERT/UPDATE/DELETE/DDL TO IDAA.

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When calling native SQL stored procedure that inserts /
    updates / deletes IDAA AOT table from db2 CLP (db2cmd or
    command editor) we get SQL error -919. Calling the SQL stored
    procedure from Data Studio via jdbc runs with rc0.
    
    Sample :
    CREATE TABLE xxx.AOTtab (ID TIMESTAMP) IN ACCELERATOR "accel";
    
    --#SET TERMINATOR ?
    CREATE PROCEDURE xxx.sp_ins_AOT ( )
     VERSION VERSION1
     ISOLATION LEVEL CS
     RESULT SETS 1
     LANGUAGE SQL
     PACKAGE OWNER xxx
     QUERY ACCELERATION ALL
     P1: BEGIN
     INSERT INTO xxx.AOTtab VALUES (CURRENT_TIMESTAMP);
     END P1 ?
    --#SET TERMINATOR ?
    
    CALL xxx.sp_ins_AOT() from CLP (DB2 command line processor)
    results in SQL error "-919" and message
    
    SQL0969N  There is no message text corresponding to SQL error
    "-919" in the message file on this workstation.  The error
    was returned from module "DSNLTPUN" with original tokens "".
    SQLSTATE=56045
    

Local fix

  • run stored procedure via Data Studio
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of the  *
    *                 IBM Data Analytics Accelerator (IDAA) from   *
    *                 a remote stored procedure.                   *
    ****************************************************************
    * PROBLEM DESCRIPTION: An SQLCODE919 can result when calling a *
    *                      remote stored procedure which offloads  *
    *                      insert, update, or delete statements to *
    *                      IDAA, the IBM DB2 Analytics             *
    *                      Accelerator.                            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    An IBM Data Server Driver for ODBC and CLI client connects to a
    DB2 for z/OS server, and calls a user stored procedure. During
    CALL statement processing, the client driver first calls the DB2
    metadata stored procedure SYSIBM.SQLPROCEDURES. The metadata
    stored procedure issues a query against the DB2 catalog. This
    results in work file materialization including a work file
    insert. The transaction is flagged as having local updates.
    After the metadata stored procedure completes, the client driver
    proceeds to call the user's stored procedure. This stored
    procedure offloads an insert, update, or delete statement. This
    DML 'offload' results in an IDAA server update. However, IDAA
    does not support two-phase commit as is generally required for
    two-site updates. Because there are updates on both DB2 and
    IDAA servers within the same transaction, DB2 returns an SQLCODE
    -919 to the client.
    

Problem conclusion

  • DB2 code has been modified to not treat work file use which can
    result in a local update. This will prevent the two-site update
    scenario and the unexpected SQLCODE -919 from being issued.
    
    Additional Keywords: SQLCODE919 SQLSP SQLSTOREDPROC SQLINSERT
                         DB2D2PC IDAAV4R1/K IDAAV5R1/K
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI55903

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-01-25

  • Closed date

    2016-03-21

  • Last modified date

    2016-05-04

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

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

    UI36380 UI36381

Modules/Macros

  •    DSNXRIWS DSNXRIW9
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI36380

       UP16/04/05 P F604

  • RB10 PSY UI36381

       UP16/04/05 P F604

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

Modified date: 04 May 2016


Translate this page: