IZ36567: INSERT and IMPORT command can receive SQLO811N as a result of the trigger processing after offending rows were already deleted

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 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