A fix is available
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
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":"10.1","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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
04 October 2013