PM93676: ABEND04E RC00C90101 DSNIDM DSNINUKY 5001 ERQUAL5001

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND04E RC00C90101 DSNIDM DSNINUKY 5001 ERQUAL5001
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of the  *
  *                 EXTENDEDINDICATOR bind option.               *
  ****************************************************************
  * PROBLEM DESCRIPTION: An ABEND04E rc00C90101 can occur at     *
  *                      location DSNINUKY 5001 for an SQL       *
  *                      UPDATE statement in an application that *
  *                      tries to update a key column in a hash  *
  *                      unique index when using the             *
  *                      EXTENDEDINDICATOR bind option.          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An ABEND04E rc00C90101 can occur at location DSNIDM DSNINUKY
  5001 (ERQUAL5001) when an UPDATE statement in an application
  tries to update a key column in a hash unique index when using
  the EXTENDEDINDICATOR bind option.
  
  The failure occurs because DB2 does not handle a deferred
  SQLCODE -151 properly. Instead, an SQLCODE151 should be returned
  to the application. This failure can occur for both a local or
  a distributed application.
  
  The following example illustrates a failing case.
  
  STEP 1. Create a Table with a HASH Index.
  
    CREATE TABLE TEST_02
     (RECID              BIGINT NOT NULL,
      SUBSEQNO           SMALLINT NOT NULL,
      RECDATE            DATE NOT NULL,
      RECTYPE            CHAR(2) FOR MIXED DATA WITH DEFAULT NULL,
      ...
                        )
       IN DBTEST.TSTEST
       ORGANIZE BY HASH UNIQUE (RECDATE,
                                RECID,
                                SUBSEQNO)
       HASH SPACE 16M
       PARTITION BY SIZE
       AUDIT NONE
       DATA CAPTURE NONE
       CCSID      UNICODE
       NOT VOLATILE
       APPEND NO  ;
  
  
  STEP 2. Populate the Table.
  
    INSERT INTO  TEST_02
     VALUES (0001, 0001, CURRENT DATE, 'BB', ...);
  
  
  STEP 3. Prepare the following application which contains an
          UPDATE statement to update a HASH INDEX KEY column.
  
    Set IND2 to a value of 0.
  
    MOVE  1 to HV1.
    EXEC SQL
      SET :HV2 = '07/10/2013'
    END-EXEC
  
  
    EXEC SQL UPDATE TEST_02 SET RECDATE=:HV2:IND2
                  WHERE RECID = :HV1
    END-EXEC.
  
  
  STEP 4. Bind the application using the EXTENDEDINDICATOR bind
          option.
  
    BIND PACKAGE(PKGSAMP) VALIDATE(RUN) ACTION(REP) -
         MEMBER(PROGA) RELEASE(COMMIT) EXTENDEDINDICATOR(YES)
    BIND PLAN(PLNSAMP) ACTION(REP)   -
         PKLIST(*.PKGSAMP.PROGA) VALIDATE(RUN) -
         RELEASE(COMMIT)
  
  
  STEP 5. Execute the package. The abend above should occur.
  
    RUN  PLAN(PLNSAMP) PROGRAM(PROGA)
  
  
  Alternatively, the problem can also occur if an index is altered
  to be a hash index before some update operation.
  
  Please note:
  The same failure can occur whether using an indicator variable
  for the host variable being updated or not, or whether the
  application is bound to run locally or via a distributed flow.
  An SQLCODE -151 is expected instead of the abend.
  
  
  Additionally, this apar corrects a problem regarding an
  SQLCODE798 not being returned to the application when using the
  EXTENDEDINDICATOR bind option when trying to insert into a
  GENERATED ALWAYS column.  Instead, the insert is performed when
  it shouldn't be.
  
  The failure occurs because DB2 does not handle an SQLCODE -798
  properly.  This failure can occur for both a local or a
  distributed application.
  
  
  The following example illustrates a failing case.
  
  STEP 1. Create a Table T1 with a GENERATED ALWAYS column.
  
    CREATE TABLE T1 (ID INT GENERATED ALWAYS AS IDENTITY
                      (START WITH -1,
                             INCREMENT BY 1,
                             CYCLE,
                             MINVALUE -1,
                             MAXVALUE 100),
                     C2 CHAR(3), C3 VARCHAR(6));
  
  
  STEP 2. With an application, perform the following INSERT
          operation.
  
  05  HV1                  PIC S9(4) BINARY.
  
  05  IND1                 PIC S9(4) BINARY VALUE 0.
  
    MOVE  1 to HV1.
  
    EXEC SQL
     INSERT INTO T1 VALUES(:HV1 :IND1, 'FFF', 'TEST1')
    END-EXEC.
  
  STEP 3. Bind the application using the EXTENDEDINDICATOR bind
          option.
  
    BIND PACKAGE(PKGSAMP) VALIDATE(RUN) ACTION(REP) -
         MEMBER(PROGA) RELEASE(COMMIT) EXTENDEDINDICATOR(YES)
    BIND PLAN(PLNSAMP) ACTION(REP)   -
         PKLIST(*.PKGSAMP.PROGA) VALIDATE(RUN) -
         RELEASE(COMMIT)
  
  
  STEP 4. Execute the package. The insert is performed when it
          should not be.
  
    RUN  PLAN(PLNSAMP) PROGRAM(PROGA)
  
  
  STEP 5. Check the results.
  
   SELECT * FROM T1;
  
               +-------------------------------+
               |       ID       | C2  |   C3   |
               +-------------------------------+
             1_|              1 | FFF | TEST1  |
               +-------------------------------+
  
  The insert operation was successful when it should not be.
  Instead, an SQLCODE -798 should be issued.
  

Problem conclusion

• The code in DB2 is modified to properly issue and return an
  SQLCODE -151 error to the application when trying to update a
  hash key index column from an application using the
  EXTENDEDINDICATOR bind option.
  
  Also, when performing an insert to a GENERATED ALWAYS column
  from an application with the EXTENDEDINDICATOR bind option, an
  SQLCODE -798 will be issued and returned to the application.
  
  Additional Keywords: SQLCODE151 SQLCODE798 SQLUPDATE SQLINSERT
                       SQLINCORR SQLINCORROUT DB2INCORR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK97296 UK97297

Modules/Macros

• DSNXRIHB DSNXRIHD DSNXRIHR DSNXRIHS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK97296

     UP13/09/19 P F309 ½

• RB10 PSY UK97297

     UP13/09/19 P F309 ½

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.