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

Obtain the fix for this APAR.

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 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.