DB2 FOR LUW / DB2FORLUW / A50 - DB2

While capturing an Oracle error code -2292 in DB2 by using 
PRAGMA EXCEPTION_INIT, DB2 only maps SQLSTATE '23001' to the 
Oracle -2292 integrity constraint violation.  SQLSTATE '23504' 
should be mapped as well. 
 
DB2 recognizes more than one distinct error.  Specifically, we 
have SQL0531N and SQL0532N, and possible SQLSTATE values of 
either '23001' or '23504'. 
 
2.  Operating system and level: 
 
db2level output: 
[db2inst1@db2whse ~]$ db2level 
DB21085I  This instance or install (instance name, where 
applicable: 
"db2inst1") uses "64" bits and DB2 code release "SQL10054" with 
level 
identifier "0605010E". 
Informational tokens are "DB2 v10.5.0.4", "s140813", "IP23623", 
and Fix Pack 
"4". 
Product is installed at "/u01/ibm/db2/V10.5". 
 
uname ?a output: 
[db2inst1@db2whse ~]$ uname -a 
Linux db2whse.cdm001.com 2.6.39-200.24.1.el6uek.x86_64 #1 SMP 
Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux 
 
 
3.  How to reproduce the problem 
 
Here is the test case. 
 
a. create two tables to create parent-child relationship: 
 
Create table ken_parent1 
(col1 integer constraint pk_ken_parent1 primary key not null 
) 
@ 
create table ken_child1 
(col1 integer constraint fk_ken_child_parent1 references 
ken_parent1 (col1) not null 
,col2 integer constraint pk_ken_child1 primary key not null 
) 
@ 
 
b. insert records to setup the test case: 
insert into ken_parent1 values (1)@ 
insert into ken_child1 values (1,1)@ 
 
c. Problem PL/SQL block: 
declare 
  child_record_exist exception; 
  pragma exception_init(child_record_exist ,-2292); 
begin 
  delete ken_parent1 where col1 = 1; 
exception 
  when child_record_exist then 
    dbms_output.put_line('capture success!'); 
  when others then 
    dbms_output.put_line('fail to capture'); 
    dbms_output.put_line('sql error:' || sqlcode || ', sqlerrm:' 
|| sqlerrm); 
end; 
@ 
 
The output is: 
 
fail to capture 
 
sql error:-532, sqlerrm:SQL0532N  A parent row cannot be deleted 
because the relationship 
"ADM_WHSE.KEN_CHILD1.FK_KEN_CHILD_PARENT1" restricts the 
deletion.  SQLSTATE=23504 
 
The exception will be captured if it is caused by a key 
constraint declared with the RESTRICT action (SQLSTATE '23001'), 
but not if the constraint has associated action NO ACTION 
(SQLSTATE '23504').

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 Version 10.5 Fix Pack 7.                       * 
****************************************************************

Can work around this issue by using two named conditions and 
initializing each using PRAGMA DB2_EXCEPTION_INIT: 
 
    declare 
      child_record_exist_1 exception; 
      pragma DB2_EXCEPTION_INIT (child_record_exist_1, '23001'); 
 
      child_record_exist_2 exception; 
      pragma DB2_EXCEPTION_INIT (child_record_exist_2, '23504'); 
 
    begin 
      delete parent1 where col1 = 1; 
 
    exception 
      when child_record_exist_1 or child_record_exist_2 then 
        dbms_output.put_line('capture success!'); 
      when others then 
        dbms_output.put_line('fail to capture'); 
        dbms_output.put_line('sql error:' || sqlcode || ', 
sqlerrm:' || sqlerrm); 
    end;

This problem is first fixed in DB2 Version 10.5 Fix Pack 7.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

30.01.2015
22.01.2016
22.01.2016