Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: sql0803n

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: 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 Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In 9.7 you can use dbms_output.put_line to print an application trace, which should let you locate the statement.

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    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..

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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?

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Could I ask for do_unnest_rmt DDL? Just want to get a working example to try it myself.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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!

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  13. #13
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    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.

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •