Results 1 to 12 of 12

Thread: SQL911...rc-68

  1. #1
    Join Date
    Jul 2012
    Posts
    9

    Post Unanswered: SQL911...rc-68

    I am facing issue in connection timeout case

    actully my issue is 911 with rc 68 that is connection time out,

    My java application is getting an issue of this. My application is basically doing processing on data in the global temporary session table's and then insert ant update in the main table but during that time i am facing this issue.

    At the same time there is a loading going on on the base table using a procedure and procedure is commiting after insertion of all data.

    we are using isolation level in java application "UR"

    and DB2_SKIPUNCOMMITED , DB2_SKIPDELETED and db2_SKIPINSERTED db2 registery variable is ON.

    Kindly guide me on that.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    You should always post your DB2-version and fixpack and Operating-system name/version when asking for help.


    Some suggestions
    * Your java application could catch the reason-code 68 of the -911 exception and consider a retry of the transaction.

    * your DBA could grant a longer locktimeout value (usually not desirable)

    * your application could try smaller units of work (commit more often)

    * the application that is inserting/updating the main table could commit more often

    * your application could configure a connection-specific locktimeout of 0 (meaning never wait) and react to the exception with some heuristics about when to retry (if it can detect the other application has finished inserting/updating, for example).

  3. #3
    Join Date
    Jul 2012
    Posts
    9
    db2version 9.5 and os : AIX

  4. #4
    Join Date
    Jul 2012
    Posts
    9
    locktimeout value is 2 minute...
    My application cannot commit as its design to insert all or nothing....

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    to insert all or nothing....
    what determines if something can not be inserted?
    Dick Brenholtz, Ami in Deutschland

  6. #6
    Join Date
    Jul 2012
    Posts
    9
    My analysis:
    As i am inserting the base using procedure commiting at the end, so there must be row_lock on the ows inserted by the procedure.
    at the same time from java application i am trying to read the data using "with ur"

    So at the time of updation of uncommited data i must wait till the row lock get removed so the java application can take row lock on that row.
    Might be this is causing the timeout issue.

    ------------------------------------------------------------------------------------

    I tried to replicate the same scenario in the test environment and please find my observation below:

    1) In 1 session i created two tables and inserted few records with commit.
    and inserted few records without commit

    2) in second session i try to read the records using join on both tables using "with ur"
    and the data get populated in the session table which was uncommited in the first session

    so when i tried to update the uncommited data its shows me

    zero rows updated sqlcode 100. but no waiting for commit to happen.
    no 911 error.

    -----------------------------------------------------------------------

  7. #7
    Join Date
    Jul 2012
    Posts
    9
    we have done this to ensure that if some insert fails we can insert the file again in next go as data is very critical so we cannot skip any data ...it must get inserted in one go..

  8. #8
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    well, it is obviously up to you how you want the INSERTs to happen.

    but, how do you expect to UPDATE inserts that have not been committed (they do not exist)
    Dick Brenholtz, Ami in Deutschland

  9. #9
    Join Date
    Jul 2012
    Posts
    9
    Pardon me sir,

    But in our java application we are setting auto commit off
    and in one phase the rows that are not present in base table get inserted and in second phase the row that are already present get updated....

    and we are commiting at the end.

    Kindly check my analysis...kindly guide me on my analysis if i m wrong....

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Consider using the LOCKS_HELD and LOCKWAITS administrative views to identify conflicting applications.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jul 2012
    Posts
    9
    ok...Thanks.

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by sunil_pec1987 View Post
    My analysis:
    As i am inserting the base using procedure commiting at the end, so there must be row_lock on the ows inserted by the procedure.
    at the same time from java application i am trying to read the data using "with ur"

    So at the time of updation of uncommited data i must wait till the row lock get removed so the java application can take row lock on that row.
    Might be this is causing the timeout issue.

    ------------------------------------------------------------------------------------

    I tried to replicate the same scenario in the test environment and please find my observation below:

    1) In 1 session i created two tables and inserted few records with commit.
    and inserted few records without commit

    2) in second session i try to read the records using join on both tables using "with ur"
    and the data get populated in the session table which was uncommited in the first session

    so when i tried to update the uncommited data its shows me

    zero rows updated sqlcode 100. but no waiting for commit to happen.
    no 911 error.

    -----------------------------------------------------------------------
    plz show the screen shots in detailed and the db2pd -appl -trans -locks show locks
    output for each steps。
    and make sure the the register var B2_EVALUNCOMMITTED in your
    test env is not turned on.

Posting Permissions

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