PM93676: ABEND04E RC00C90101 DSNIDM DSNINUKY 5001 ERQUAL5001

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM93676

  • 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

    2013-07-23

  • Closed date

    2013-09-04

  • Last modified date

    2013-10-04

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



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM93676

Modified date:

2013-10-04

Translate my page

Machine Translation

Content navigation