Timeout db2

[nicox1984]

Hi,

I've to insert some data into a DB2 table.

Number of rows is very high, and I need to have a multiple insertion (running more than one instance of the same program).

What I do is essentially the following:

_cnn.setAutoCommit(false); 
for(int i=0; i< numRows; i++){ 
    _stm.executeUpdate(row[i]); 
}  
_cnn.commit();

When I run my programs SOMETIMES I get a TIMEOUT ERROR. In particular:

SQLCODE = -913
SQLERRD = 00C9008E ---> timeout
(It's not a Deadlock...because deadlock SQLERRD should be 00C90088).


So, the solution is to increase timeout. By I wasn't able to do it.

I tryed inserting "locktimeout" when estabilishing connection:

Properties prop = new Properties();
prop.setProperty("locktimeout", "900");

...

_cnn = DriverManager.getConnection(_Url, prop);

Il connects, but DB2 timeout seems do not change...

So I tryed modifing currentlocktimeout in this way:

 
DB2DataSource ds = new DB2DataSource();
....
ds.setCurrentLockTimeout(900);
...
_cnn = ds.getConnection(_User, _Pswd);

but no effect again...



Is there a way to change timeout in a DB2 TABLE????

[quad64bit]

You probably have to change the timeout values server side!

[nicox1984]

Can I do it by a Java Program???

How can I do it...?

[Tolls]

Could you batch the commands and fire a single execute?
You're doing a (potentially) awful lot of toing and froing with the database there by doing single execute calls.

[nicox1984]

Could you batch the commands and fire a single execute?
You're doing a (potentially) awful lot of toing and froing with the database there by doing single execute calls.

I've a TREEMAP, in which I put all rows to insert in DB.

When I complete TREEMAP I execute every single query white a for.

I tryed to use addBatch() and ad the end executeBatch() but after about 20 minutes...It returns an error...


I tryed to autocommit every insert...and in this case NO PROBLEM!

So It's sure a timeout problem. In particular I can see that timeout start after 3 minutes!



Some suggestion??

[Tolls]

It errors?
What error does it give on a batch update then, out of curiosity?

[nicox1984]

It errors?
What error does it give on a batch update then, out of curiosity?

Error -913...Timeout error while executing a batch operation!

[Tolls]

Ah.
I've never had to deal with DB2, so it's quite possible this is something to do with the database itself?

[nicox1984]

Ah.
I've never had to deal with DB2, so it's quite possible this is something to do with the database itself?

I've never had to deal with DB2 too...!!

[Tolls]

Looking up that error it's not a connection timeout but a wait timeout.
That is, the query/update/insert is waiting on another query/update/insert to commit and/or complete.
Now, since you say you are doing this using multiple instances then that is no surprise.
DB2 is likely to lock the table you are inserting to for example, or lock the row you are updating, either of which can result in another instance of your program being forced to hang around since the first instance is not going to commit til it's finished.
This is why, when you commit after each insert/etc it works.

[nicox1984]

Looking up that error it's not a connection timeout but a wait timeout.
That is, the query/update/insert is waiting on another query/update/insert to commit and/or complete.
Now, since you say you are doing this using multiple instances then that is no surprise.
DB2 is likely to lock the table you are inserting to for example, or lock the row you are updating, either of which can result in another instance of your program being forced to hang around since the first instance is not going to commit til it's finished.
This is why, when you commit after each insert/etc it works.

First of all...thanks for your answer.
So, DB2 locks table while inserting/updating rows and release lock when commit is made.
Is there a way to make multiple insert? I can se from ibm specifics that exists 2 db2 parameters, "maxlocks" and "locklist" that can be useful to resolve my problem...
But I haven't find a way to set that in my java program...

[Tolls]

Can't guarantee exactly how it locks, but it's pretty clear you are getting a clash.
The only thing I can suggest is to not parallelise (is that a word?) the bulk changes into a particular table.

[arielb]

I agree with Tolls, is better to use batch for that case.

You can check the code that suggests ibm:
DB2 Universal Database

To see the timeout error, if you have access to as400 WRKACTJOB can use the command and look for the state that corresponds to your user or workstation.

[nicox1984]

I agree with Tolls, is better to use batch for that case.

You can check the code that suggests ibm:
DB2 Universal Database

To see the timeout error, if you have access to as400 WRKACTJOB can use the command and look for the state that corresponds to your user or workstation.

I've tryed that code, but nothing. How I said some post ago, after 20 minutes executeBatch throws an exception and returns me SQLCODE -913 (timeout).

How can I se if I have access to as400 WRKACTJOB?

Can't guarantee exactly how it locks, but it's pretty clear you are getting a clash.
The only thing I can suggest is to not parallelise (is that a word?) the bulk changes into a particular table.

It's sad I can't "parallelise" insertion...!

I'm considering to commit at every insert...

But know there's another problem:
Sometimes program crash for unknown reason...
If some rows was inserted before crashing...It will give me error when restarting execution (It will start inserting from the first row again).

I tried to use istruction like "replace" (in sql it's like insert, but in presence of duplicate, it delete first the row and then insert) or "insert ignore" (only a warning if row is present)...but maybe they aren't legal istruction in DB2..!

desperation

[arielb]

SQLCODE -913 (timeout).

1 - IBM Information Management Software for z/OS Solutions Information Center

2 - IBM Information Management Software for z/OS Solutions Information Center

-913
UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name

[nicox1984]

SQLCODE -913 (timeout).

1 - IBM Information Management Software for z/OS Solutions Information Center

2 - IBM Information Management Software for z/OS Solutions Information Center

I saw. I get SQLERRD[3] = 00C9008E.

So, no deadlock, but timeout.

Suggestion is to commit frequently...

[arielb]

In the as400 you will notice that the process is on hold with the command wkractjob

[Tolls]

Just thinking last night, have you tried batching in small batches rather than the whole lot?
Say 10s of statements, or even 100s, then a commit.
Something that can be executed in a short enough time period that you don't hit the timeout problem.