Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: BLOB Update/Delete Problem

    I am facing a strange problem with DB2
    Hope to find some solution here.
    I am connecting to DB2 db using com.ibm.db2.jcc.DB2Driver in my java program. In the program, I have to delete some records from a table having a blob column. Now the peculiar problem arises, executeupdate() on PreparedStatement doesnot return for the last record to be deleted although the record is deleted in the table. Hence, the application hangs and I have to forcefully terminate it. The same program works fine for table without any blob column.

    Any clues or suggestions???I am really stuck with it.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2007
    Posts
    3
    DB2 Version that we are using is DB2 v8.1.1.89 FP 9.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you verify that you don't run into some sort of lock timeout? You may want to set the timeout to 10 or 30 seconds or so and try again. If you get a lock timeout after the indicated time, you can try to figure out what is blocking the application. If you do not get a timeout, it may be a good idea to open a PMR with IBM support because DB2 shouldn't hang in such situations.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2007
    Posts
    3

    Problem resolved

    We managed to resolve the problem. We were selecting records from A table, inserting into B table and after that we were deleting the record from A table. I don't know whether it's mandatory to put 'with ur' with every select statement in DB2 as I am not very familiar with DB2 but after putting 'with ur' clause in the select query our problem is resolved.

    Thanks for the suggestions and help.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    WITH UR tells DB2 that you are willing to read uncommitted and potentially invalid data. DB2 does that by not requesting a read-lock (shared lock) on table A for the SELECT.

    While the symptom goes away, I have my doubts that this is really a "solution" for your problem.

    The introduction of WITH UR strongly hints at a lock-wait issue. I guess that another application also accesses tables A and/or B and does not commit, causing your application to wait for locks. Therefore, I believe that a real solution would be to check the applications that they commit frequently enough and also to configure your DB2 to have a defined lock-timeout and not the default of -1 (which means to wait forever).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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