PI22542: ABEND0C4 RC3B AT DSNXOMRG OFFSET4EB8 WHEN MERGE STATEMENT WITH COLUMN DEFINED AS GENERATED ALWAYS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• ABEND0C4 RC3B at DSNXOMRG offset4EB8 may occur for a MERGE
  statement with a column defined as generated always.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and 11 for z/OS users of SQL      *
  *                 containing UPDATE, INSERT or MERGE statement *
  *                 with bind option EXTENDEDINDICATOR(YES).     *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR addresses TWO problems.       *
  *                                                              *
  *                      Problem 1.                              *
  *                                                              *
  *                      An ABEND0C4 RC0000003B at location      *
  *                      DSNXGRDS DSNXOST OFFSET2048, DSNXGRDS   *
  *                      DSNXOMRG OFFSET4EB8, or DSNXGRDS        *
  *                      DSNXOCAS OFFSET82A6 may occur when a    *
  *                      MERGE statement satisfies all of the    *
  *                      following list of conditions:           *
  *                                                              *
  *                      1. a target column of                   *
  *                         modification-operation is defined in *
  *                         a VIEW which isn't allowed to be     *
  *                         updated or defined in base table     *
  *                         with generated always in             *
  *                         generated-column-definition.         *
  *                      2. the right hand side of               *
  *                         modification-operation is a column   *
  *                         of the source-table.                 *
  *                      3. The EXTENDEDINDICATOR(YES) bind      *
  *                         option or the specification of WITH  *
  *                         EXTENDED INDICATORS prepare          *
  *                         attribute is used.                   *
  *                                                              *
  *                      Problem 2.                              *
  *                                                              *
  *                      SQLCODE -798 or -151 may issue at a     *
  *                      different location when an INSERT or    *
  *                      UPDATE statement satisfies all of the   *
  *                      following list of conditions:           *
  *                                                              *
  *                      1. a target column of insert or update  *
  *                         operation is defined with generated  *
  *                         always in                            *
  *                         generated-column-definition.         *
  *                      2. the right hand side of insert or     *
  *                         update operation is a host variable  *
  *                         or CAST specification with host      *
  *                         variable.                            *
  *                      3. The EXTENDEDINDICATOR(YES) bind      *
  *                         option or the specification of WITH  *
  *                         EXTENDED INDICATORS prepare          *
  *                         attribute is used.                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The following examples help to illustrate the problems.
  
  Step 1. Create a Table T1.
  
  CREATE TABLE T1(
    POLICY_ID CHAR(5),
    SYS_UPDATE NOT NULL GENERATED ALWAYS FOR EACH ROW
               ON UPDATE AS ROW CHANGE TIMESTAMP,
    SYS_START TIMESTAMP(12) NOT NULL
              GENERATED ALWAYS AS ROW BEGIN,
    SYS_END TIMESTAMP(12) NOT NULL
            GENERATED ALWAYS AS ROW END,
    CREATE_ID TIMESTAMP(12) NOT NULL
              GENERATED ALWAYS AS TRANSACTION START ID,
    PERIOD SYSTEM_TIME(SYS_START, SYS_END)
  );
  
  Step 2. The application uses the EXTENDEDINDICATOR bind option.
  
    BIND PACKAGE(...) MEMBER(...)-
    EXTENDEDINDICATOR(YES) ....
  
  Step 3. MERGE statement which can cause ABEND0C4.
  
  DCL IND1 BIN FIXED(15);
  DCL CHARHV1 CHAR(5);
  DCL ROWCHHV CHAR(32);
  IND1 = 0;
  CHARHV1 = 'AAAAA';
  ROWCHHV = '2013-12-30-00.00.00.000000000000';
  
  EXEC SQL
  MERGE INTO T1 AS V
    USING (VALUES(:ROWCHHV :IND1, :CHARHV1 :IND1) FOR 1 ROWS)
    AS T(C1, ID)
  ON(V.POLICY_ID = T.ID)
  WHEN MATCHED THEN
    UPDATE
      SET (V.SYS_UPDATE, V.POLICY_ID) = (T.C1, T.ID)
  WHEN NOT MATCHED THEN
    INSERT (V.SYS_UPDATE, V.POLICY_ID)
      VALUES(T.C1, T.ID)
  NOT ATOMIC CONTINUE ON SQLEXCEPTION;
  
  Step 4. An INSERT or UPDATE SQL statement can cause a SQLCODE
          -798 or -151 to be issued at a different location.
  
  DCL IND1 BIN FIXED(15);
  DCL ROWCHHV CHAR(32);
  IND1 = 0;
  ROWCHHV = '2013-12-30-00.00.00.000000000000';
  
  EXEC SQL
  INSERT INTO T1(SYS_UPDATE)
    VALUES(CAST(:ROWCHHV :IND1 AS TIMESTAMP(12)));
  
  EXEC SQL
  UPDATE T1
  SET (SYS_UPDATE) = (CAST(:ROWCHHV :IND2 AS TIMESTAMP(12)));
  
  SQLCODE -798 or -151 is issued at DSNXOCAS.
  After applying APAR fix, SQLCODE will be issued at DSNXOFF.
  
  DB2 does not process the extended indicator variable properly
  which can result in the abend and incorrect SQLCODE.
  

Problem conclusion

• The bind time code in DB2 is modified to process extended
  indicator variables properly to avoid the unexpected problems.
  
  Additional Keywords: SQLUPDATE SQLMERGE SQLINSERT SQLHOSTVAR
                       SQLCODE798 SQLCODE151 SQLCAST
                       EXTENDED INDICATORS
  

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:

  UI20852 UI20853

Modules/Macros

• DSNXOCAS DSNXOFF  DSNXOIN  DSNXOMRG DSNXOST
  DSNXOVC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI20852

     UP14/09/10 P F409

• RB10 PSY UI20853

     UP14/09/10 P F409

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.