A fix is available
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 number
PH07132
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
YesHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-01-11
Closed date
2019-04-02
Last modified date
2019-05-02
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
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 May 2019