Results 1 to 18 of 18

Thread: Timeout db2

  1. #1
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Timeout db2

    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:

    Java Code:
    _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:

    Java Code:
    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:

    Java Code:
     
    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????

  2. #2
    quad64bit's Avatar
    quad64bit is offline Moderator
    Join Date
    Jul 2009
    Location
    VA
    Posts
    1,323
    Rep Power
    7

    Default Re: Timeout db2

    You probably have to change the timeout values server side!

  3. #3
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2

    Can I do it by a Java Program???

    How can I do it...?

  4. #4
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,859
    Rep Power
    19

    Default Re: Timeout db2

    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.

  5. #5
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2

    Quote Originally Posted by Tolls View Post
    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??

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,859
    Rep Power
    19

    Default Re: Timeout db2

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

  7. #7
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2

    Quote Originally Posted by Tolls View Post
    It errors?
    What error does it give on a batch update then, out of curiosity?
    Error -913...Timeout error while executing a batch operation!

  8. #8
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,859
    Rep Power
    19

    Default Re: Timeout db2

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

  9. #9
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2

    Quote Originally Posted by Tolls View Post
    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...!!

  10. #10
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,859
    Rep Power
    19

    Default Re: Timeout db2

    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.

  11. #11
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2

    Quote Originally Posted by Tolls View Post
    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...

  12. #12
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,859
    Rep Power
    19

    Default Re: Timeout db2

    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.

  13. #13
    arielb is offline Member
    Join Date
    Jan 2012
    Location
    Panamá
    Posts
    41
    Rep Power
    0

    Default Re: Timeout db2

    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.

  14. #14
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2

    Quote Originally Posted by arielb View Post
    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?



    Quote Originally Posted by Tolls View Post
    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

  15. #15
    arielb is offline Member
    Join Date
    Jan 2012
    Location
    Panamá
    Posts
    41
    Rep Power
    0

    Default Re: Timeout db2

    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

  16. #16
    nicox1984 is offline Member
    Join Date
    Dec 2011
    Posts
    10
    Rep Power
    0

    Default Re: Timeout db2


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

    So, no deadlock, but timeout.

    Suggestion is to commit frequently...

  17. #17
    arielb is offline Member
    Join Date
    Jan 2012
    Location
    Panamá
    Posts
    41
    Rep Power
    0

    Default Re: Timeout db2

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

  18. #18
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,859
    Rep Power
    19

    Default Re: Timeout db2

    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.

Similar Threads

  1. JAR server timeout
    By Axephilic in forum Advanced Java
    Replies: 0
    Last Post: 10-24-2011, 07:22 PM
  2. getHostName(). is there any way to set timeout??
    By nikmanutd in forum Networking
    Replies: 1
    Last Post: 10-05-2011, 06:09 PM
  3. Implement a timeout
    By pjmorce in forum Advanced Java
    Replies: 5
    Last Post: 04-27-2010, 02:36 AM
  4. Java RMI timeout
    By sky in forum Advanced Java
    Replies: 0
    Last Post: 03-17-2010, 12:44 AM
  5. Socket Timeout?
    By Moncleared in forum New To Java
    Replies: 1
    Last Post: 09-12-2009, 04:12 AM

Posting Permissions

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