Fixes are available
DB2 Version 9.5 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
Re-running original INSERT or IMPORT command again should be successful, but it fails with -811 error. Steps to reproduce -811 error: 1. Create a database 2. Create the following table and a triger db2 "CREATE TABLE T1 (T1C1 INTEGER NOT NULL , T1C2 VARCHAR(20) NOT NULL)" db2 "CREATE TABLE T2 (T2C1 INTEGER NOT NULL , T2C2 VARCHAR(20) NOT NULL)" db2 "CREATE TRIGGER INS_T1 NO CASCADE BEFORE INSERT ON T1 REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL BEGIN ATOMIC set T1C1 = (select T2C1 from T2 where new.T1C2 = T2C2); END" 3. Get a legitimate -811, having duplicates in T2 table db2 "INSERT INTO T2 VALUES (1,'AAAAA')" db2 "INSERT INTO T2 VALUES (2,'AAAAA')" <--- creating -811 error 4. Run command (4.1 or 4.2): 4.1 db2 "INSERT INTO T1 VALUES ( 1, 'AAAAA' )" 4.2 Create t.del file, having 1 line: 1|"AAAAA" Run command: db2 "import from t.del of del modified by delprioritychar coldel| replace into T1" Note: running the above command (4.1 or 4.2) returns -811 in trigger INS_T1, because select T2C1 from T2 where T2C2 = 'AAAAA' returns 2 rows SQL3148W A row from the input file was not inserted into the table. SQLCODE "-723" was returned. SQL0723N An error occurred in a triggered SQL statement in trigger "TOMA.INS_T1". Information returned for the error includes SQLCODE "-811", SQLSTATE "21000" and message tokens "". SQLSTATE=09000 5. Remove -811 condition db2 "DELETE FROM T2 WHERE T2C1 = 2" <--- removing -811 error 6. Re-run command (6.1 or 6.2): 6.1 db2 "INSERT INTO T1 VALUES ( 1, 'AAAAA' )" 6.2 db2 "import from t.del of del modified by delprioritychar coldel| replace into T1" Note: running the above command should not return -811 in trigger INS_T1, because select T2C1 from T2 where T2C2 = 'AAAAA' returns only 1 row 7. Recycle database db2 terminate db2 connect to <db name> 8. Re-run command (8.1 or 8.2: 8.1 db2 "INSERT INTO T1 VALUES ( 1, 'AAAAA' )" 8.2 db2 "import from t.del of del modified by delprioritychar coldel| replace into T1" Note: running the above command does not return -811 in trigger INS_T1, because select T2C1 from T2 where T2C2 = 'AAAAA' returns only 1 row Customer is db2level: DB2 v9.5FP2, s080811
Local fix
Recycle database and re-run the original INSERT command.
Problem summary
Problem Summary: Re-running original INSERT or IMPORT command again should be successful, but it fails with -811 error. Users affected: All, level 9.5 and below.
Problem conclusion
First Fixed in DB2 UDB V9.5 FP4
Temporary fix
Comments
APAR Information
APAR number
IZ36567
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-11-04
Closed date
2009-09-11
Last modified date
2009-09-11
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 UDB ESE SOL
Fixed component ID
5765F4102
Applicable component levels
R950 PSY
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
11 September 2009