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.

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

db2version 9.5 and os : AIX

locktimeout value is 2 minute...
My application cannot commit as its design to insert all or nothing....

what determines if something can not be inserted?

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.

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

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

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)

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

Consider using the LOCKS_HELD and LOCKWAITS administrative views to identify conflicting applications.

ok...Thanks.

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.