IBM Support

PI40044: SQLCODE504 ISSUED INCORRECTLY WHEN STATEMENT REFERENCES A REMOTE TABLE AND A REMOTE CURSOR

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQLCODE504 issued incorrectly when statement references a REMOTE
    TABLE and a REMOTE CURSOR
    
    This is an example of the described scenario
    1. Declare the cursor CUR1 which references a remote table
    
    DECLARE CUR1 CURSOR WITH HOLD FOR
    SELECT C1 FROM REMAL_TB1
    FOR UPDATE OF C1;
    
    REMAL_TB1 is an alias referencing to a remote table
    
    2. Reference the cursor CUR1 using UPDATE and references a
    remote table
    
     UPDATE REMAL_TB1 SET C1 =1
       WHERE CURRENT OF CUR1;
    
    Now this UPDATE receives a SQLCODE -504
    
    Additional symptoms and search keywords
    SQLCODE504 SQLBIND SQLREBIND SQLREMOTE
    

Local fix

  • N/A
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 and 11 for z/OS users of an       *
    *                 UPDATE statement using a cursor              *
    *                 that references a remote table with          *
    *                 PTF UI24478 (V10) or UI22894 (V11)           *
    *                 applies.                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION: SQLCODE504 may be issued incorrectly    *
    *                      with PTF UI24478 (V10) or               *
    *                      UI22894 (V11) applies for an            *
    *                      UPDATE WHERE CURRENT OF                 *
    *                      statement that satisfies the            *
    *                      following conditions:                   *
    *                      1. The UPDATE WHERE CURRENT OF          *
    *                      statement references a remote object.   *
    *                      2. The cursor references a remote       *
    *                      object.                                 *
    *                      3. The statement was bound with         *
    *                      SQLERROR(NOPACKAGE) or VALIDATE(BIND)   *
    *                                                              *
    *                      After this apar fix, SQLCODE504         *
    *                      from DSNXODML during bind will only     *
    *                      be issued for an UPDATE WHERE CURRENT   *
    *                      OF statement that satisfies all of      *
    *                      the following conditions:               *
    *                      1. The cursor references the objects    *
    *                      from multiple locations thus it         *
    *                      receives SQLCODE512.                    *
    *                      2. The UPDATE WHERE CURRENT OF          *
    *                      statement references a remote object.   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    For example,
    
    1. Create an alias on STLEC1A for a remote table TBB
    CREATE ALIAS SYSADM.REMAL_TB FOR STLEC1B.SYSADM.TBB;
    
    2. Create table TBB on STLEC1B
    CONNECT TO STLEC1B;
    CREATE TABLE TBB (C1 INT);
    
    3. Create a cursor CUR1 which references a remote table
    DECLARE CUR1 CURSOR WITH HOLD FOR
    SELECT C1 FROM  REMAL_TB  WHERE C1 = 1;
    
    4. Update references a remote tabl and uses the cursor which is
    defined above.
    EXEC SQL UPDATE REMAL_TB SET C1 =1
    WHERE CURRENT OF CUR1;
    
    Bind and rebind receives an SQLCODE504 from DSNXODML because DB2
    did not handle the remote object referencing in the UPDATE WHERE
    CURRENT OF correctly.
    

Problem conclusion

  • DB2 was updated to remove SQLCODE504 from DSNXODML for the
    UPDATE WHERE CURRENT OF statement which references a remote
    object using a cursor which also references a remote object
    during bind and rebind.
    
    Additional Keywords: SQLBIND SQLREBIND SQLREMOTE SQLUPDATE
    

Temporary fix

Comments

APAR Information

  • APAR number

    PI40044

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    YesPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2015-04-29

  • Closed date

    2015-09-03

  • Last modified date

    2015-10-02

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

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

    UI30830 UI30831

Modules/Macros

  •    DSNXODML DSNXOTDA
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UI30830

       UP15/09/19 P F509

  • RB10 PSY UI30831

       UP15/09/19 P F509

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

Modified date: 02 October 2015


Translate this page: