PH07132: INCORROUT WITH FIELDPROC ON FIXED LENGTH DATA TYPE (CHAR or GRAPHIC) DEFINED AS NULL

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• On a second update to a fixed length data type (CHAR or GRAPHIC)
  column defined as NULL with a FIELDPROC, Db2 sets the NULL
  indicator for the update, but the column data from the first
  update is not reset. Thus, the result of the second update is a
  NULL Indicator set with a data value.
  
  
  
  This INCORROUT is found when the data is retrieved using index
  access. A tablespace scan will give the correct result.
  
  Additional Keywords:
  DB2INCORR/K SQLFIELDPROC SQLINCORR SQLINCORROUT
  SQLNULL SQLACCESSPATH ZSA3
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 11 and Db2 12 for z/OS users of      *
  *                 Field Procedures (or Fieldprocs) defined on  *
  *                 NULLABLE CHAR or GRAPHIC columns.            *
  ****************************************************************
  * PROBLEM DESCRIPTION: Db2 may not generate an internal bit    *
  *                      representation properly for the value   *
  *                      of NULL when updating a nullable CHAR   *
  *                      or nullable GRAPHIC column whose        *
  *                      declaration includes a Field Procedure  *
  *                      (FIELDPROC) to NULL. Later, if this     *
  *                      column is included in some query, an    *
  *                      incorrect result could be returned.     *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available.         *
  ****************************************************************
  Db2 may not generate an internal bit representation properly for
  the value of NULL when updating a nullable CHAR or nullable
  GRAPHIC column (containing a Field Procedure) to NULL.  This can
  lead to an incorrect result being returned for a query
  referencing the column.
  
  
  The following example can help to illustrate the problem.
  
  Step 1.
  Create Table T1 with a NULLABLE CHAR column UID with FIELDPROC
  FPCVD4 defined on it, and create Index IDX on column UID.
  
    CREATE TABLE T1
      (ID     INTEGER NOT NULL,
       UID    CHAR(36) FIELDPROC FPCVD4);
  
    CREATE INDEX IDX1 ON T1 (UID ASC);
  
  
  Step 2.
  Populate Table T1 with 2 rows.
  
    INSERT INTO T1 VALUES
      (101,'12345678-ffff-ffff-ffff-ffffffffffff');
    INSERT INTO T1 VALUES
      (102,'12345678-ffff-ffff-ffff-ffffffffffff');
  
  
  Step 3.
  Run a stored procedure to update column UID in a loop.
  A snippet in PL/I is provided.
  
    DCL VAR      CHAR(36);
    DCL VAR_IND  BIN(15) FIXED INIT(0);
    DCL ID       BIN(31) FIXED INIT(0);
  
    VAR = 'A2345678-FFFF-FFFF-FFFF-FFFFFFFFFFFF';
    VAR_IND = 0;
    ID = 101;
  
    DO WHILE(ID <= 102);
      EXEC SQL UPDATE T1
        SET UID = :VAR:VAR_IND
        WHERE ID = :ID;
      VAR = 'B2345678-FFFF-FFFF-FFFF-FFFFFFFFFFFF';
      VAR_IND = -1;
      ID = ID + 1;
    END;
  
  The above code snippet updates column UID for the 2 rows in
  Table T1, and updates UID to NULL because of the null
  indicator of "-1" for the second row.
  Note that Db2 doesn't generate the correct bit representation
  for the value of NULL in the second update in this scenario.
  
  Step 4.
  Run the following query.
  
    SELECT COUNT(*) FROM T1 WHERE UID IS NULL;
  
  This query uses an index scan since there is an index IDX1
  created on this single column, UID.  The query returns 0 rows
  instead of 1 because the NULL value is not represented properly.
  

Problem conclusion

• Db2 code has been modified to generate the correct bit
  representation of the NULL value for a nullable CHAR or GRAPHIC
  column containing a Field Procedure (FIELDPROC) definition.
  
  Please note that the problem only occurs with fixed length data
  types CHAR and GRAPHIC.  Using the variable length data types
  VARCHAR and VARGRAPHIC will prevent this problem.
  
  Additional Keywords: DB2INCORR/K SQLINCORR SQLINCORROUT
                       INCORROUT SQLFIELDPROC SQLNULL
  

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:

  UI62289 UI62290

Modules/Macros

•    DSNXRECD DSNXRRP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI62290

     UP19/04/18 P F904 Ž

• RC10 PSY UI62289

     UP19/04/18 P F904 Ž

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.