Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > sql0803n

Reply
Page 1 of 2 1 2
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-27-11, 19:23
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
sql0803n
J2EE application is running successfully with v9.1 db and "v9.7 migrated" (migrated from v9.1) db. The same application is experiencing intermittent -803 when it's used with "v9.7 new" (created under v9.7) db.


SQLSTATE 23505 SQLCODE -803 with JDBC
From WebSphere Application server log -
[1/18/11 4:20:02:025 EST] 00000731 SystemErr R xxxxxxxx.model.jdbc.
xxxxxxxxxJdbcSessionBean:QL Error from StoredProcedu
re! SQL0803N One or more values in the INSERT statement, UPDATE
statement, or foreign key update caused by a DELETE statement
are not valid because the primary key, unique constraint or unique
index identified by "1" constrains table "DB2.xxxxxx"
from having duplicate values for the index key. SQLSTATE=23505


I've been told that the application, DDL and data is exactly the same.


There are some db cfg differences between v9.1 and v9.7. Most of them have been updated to match what "v9.7 migrated" db has (cur_commit, etc..) except for those that can't be modified without recreating the db (codepage...) or should be unrelated to this error (memory settings). I'm attaching a file with db cfg differences prior to making any changes.

I've also asked to create a "v9.7 new" db using the backup image of "v9.7 migrated". So far, they haven't seen any -803 with this new db.


If the application, DDL, data and db2level is exactly the same, then why is this happening? The only difference I see are the codepage/codeset/collating sequence settings. Could that cause this error?


-803 is coming from a stored procedure. This stored procedure has several insert and update statements. Is there a way to know which one generated -803 by enabling some stored procedure setting? diaglevel 4 doesn't help.


In summary:

v9.1 - OK
mig v9.7 - OK
new v9.7 - sql0803n
Attached Files
File Type: txt db.cfg.diff.txt (3.1 KB, 56 views)
Reply With Quote
  #2 (permalink)  
Old 01-27-11, 22:00
n_i n_i is offline
:-)
  
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
In 9.7 you can use dbms_output.put_line to print an application trace, which should let you locate the statement.
Reply With Quote
  #3 (permalink)  
Old 01-27-11, 22:01
stiruvee stiruvee is offline
Registered User
  
Join Date: May 2010
Location: India
Posts: 34
Hi,

Did you rebind all the packages after changing cur_commit in new V9.7 database? Please check following link where IBM is suggesting rebind after changing cur_commit. The link is regarding replication process, but I guess it might be applicable to all packages.

IBM - Manual bind is needed for replication when CUR_COMMIT is set ON in DB2

Satya..
Reply With Quote
  #4 (permalink)  
Old 01-28-11, 09:05
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
Quote:
Originally Posted by n_i View Post
In 9.7 you can use dbms_output.put_line to print an application trace, which should let you locate the statement.
Do you have any examples of how to use dbms_output.put_line in the stored procedure other than what's in the manual or on the web?
Reply With Quote
  #5 (permalink)  
Old 01-28-11, 09:22
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
Quote:
Originally Posted by stiruvee View Post
Hi,

Did you rebind all the packages after changing cur_commit in new V9.7 database? Please check following link where IBM is suggesting rebind after changing cur_commit. The link is regarding replication process, but I guess it might be applicable to all packages.

IBM - Manual bind is needed for replication when CUR_COMMIT is set ON in DB2

Satya..

From reading about "bind ...concurrentaccessresolution wait_for_outcome" in the manual, it sounds like I'd used this option if I want to disable "currently committed" behaviour. I've asked to change cur_commit db cfg to "disabled" (to match "v9.7 migrated" db) so I think this should be enough. Please correct me if this doesn't sound right.
Reply With Quote
  #6 (permalink)  
Old 01-28-11, 09:54
n_i n_i is offline
:-)
  
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
Quote:
Originally Posted by BELLO4KA View Post
Do you have any examples of how to use dbms_output.put_line in the stored procedure other than what's in the manual or on the web?
There isn't much beyond what's in the manual, but if you must...

Code:
create or replace procedure lcl
begin
 declare v_i1 integer;
 declare v_i2 integer default 0;
 declare v_c1 varchar(10);
 declare rs1 result_set_locator varying;
 declare rs2 result_set_locator varying;

 call dbms_output.put_line('calling');
 call do_unnest_rmt(5);
 call dbms_output.put_line('called; i2 = '||v_i2);

 associate result set locators (rs1,rs2) with procedure do_unnest_rmt;
 call dbms_output.put_line('associated');

 allocate mycur1 cursor for result set rs1;
 allocate mycur2 cursor for result set rs2;
 call dbms_output.put_line('allocated');


 fetch mycur1 into v_i1;
 fetch mycur2 into v_i2, v_c1;

 call dbms_output.put_line('i1 = '||v_i1);
 call dbms_output.put_line('i2 = '||v_i2);
 call dbms_output.put_line('c1 = '||v_c1);

 close mycur1;
 close mycur2;
end
Reply With Quote
  #7 (permalink)  
Old 01-28-11, 10:40
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
Could I ask for do_unnest_rmt DDL? Just want to get a working example to try it myself.
Reply With Quote
  #8 (permalink)  
Old 01-28-11, 14:15
dav1mo dav1mo is offline
Registered User
  
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
do you happen to have a table that you are inserting to that has an identity column? I have seen this happen when someone copies data from one db to another and not reset the identity number to the next available after loading??
Dave
Reply With Quote
  #9 (permalink)  
Old 01-28-11, 14:24
n_i n_i is offline
:-)
  
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
Quote:
Originally Posted by BELLO4KA View Post
Could I ask for do_unnest_rmt DDL? Just want to get a working example to try it myself.
You could, but I don't have that - just remnants of some old forgotten code. Sorry.
Reply With Quote
  #10 (permalink)  
Old 01-28-11, 19:12
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
No, it doesn't have an identity column. The problem is intermittent, they have ~4 mil successful trans per hr and -803 occurs ~2000 times per hr. Hopefully, doing a trace for -803 and what Nick suggested (got our appl team to help out with this) will help to debug this further.

Thanks, everyone!
Reply With Quote
  #11 (permalink)  
Old 01-28-11, 23:15
Marcus_A Marcus_A is offline
Registered User
  
Join Date: May 2003
Location: USA
Posts: 5,198
Quote:
Originally Posted by BELLO4KA View Post
No, it doesn't have an identity column. The problem is intermittent, they have ~4 mil successful trans per hr and -803 occurs ~2000 times per hr. Hopefully, doing a trace for -803 and what Nick suggested (got our appl team to help out with this) will help to debug this further.

Thanks, everyone!
I agree with a previous post that all packages should be rebound, and I would recommend that all SP's and UDF's should be recreated from scratch (db2look output).

If after a rebind and recreate SP's and UDF's this is still happening with curr_committed enabled, then you may have discovered a bug in that logic, so I would definitely have them turn it off. Also, find out what the db2set variables are (such as skip inserted, etc).
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #12 (permalink)  
Old 01-29-11, 00:29
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
The problem persisted even after changing cur_commit db cfg from ON to DISABLED. Do you mean disabling cur_commit db cfg is not enough and we still need to do a rebind and recreate SP (no UDFs)? Skip inserted... registry variables are not set.
Reply With Quote
  #13 (permalink)  
Old 01-29-11, 04:44
stiruvee stiruvee is offline
Registered User
  
Join Date: May 2010
Location: India
Posts: 34
The access plan generated when SP is created with CUR_COMMIT ON remains same even after CUR_COMMIT is changed to OFF. The access plan changes only after SP is rebound after changing CUR_COMMIT to off.

You can verify this by creating small SP with CUR_COMMIT ON and extracting access plans for SP with CUR_COMMIT ON and CUR_COMMIT OFF. Both access plans will be same. If you rebound SP after changing CUR_COMMIT to OFF and extract the access plan, the new access plan will be different.
Reply With Quote
  #14 (permalink)  
Old 01-29-11, 11:19
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
Yes, I do see a difference in the access plan:


diff exfmt.sp.enabled.rebind exfmt.sp.disabled.rebind


13c13
< EXPLAIN_TIME: 2011-01-29-10.55.03.705116
---
> EXPLAIN_TIME: 2011-01-29-10.59.29.220282
119c119
< 53 milliseconds
---
> 26 milliseconds
195,196d194
< CUR_COMM: (Currently Committed)
< TRUE
207,208d204
< SKIP_INS: (Skip Inserted Rows)
< TRUE



I had to drop/recreate SP or rebind the package that got created with SP (don't know enough about SP's and forgot that db2 creates a package for it).

At this time, it's suspected that this is the INSERT statement that's failing with -803 so it could very well has something to do with cur_comm (cur_comm was the first thing I had suspected but was puzzled as to why the problem persisted after disabling it).


Thanks.
Reply With Quote
  #15 (permalink)  
Old 01-31-11, 15:37
db2girl db2girl is offline
∞∞∞∞∞∞
  
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
Dropping/recreating SP didn't help. I'm going to ask for db2exfmt to confirm that cur_commit is not being used.

Please let me know if you have any other thoughts/suggestions/ideas...
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.