Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004

    Unanswered: Bind Package problem

    We are facing a very unique problem while trying to run a batch insert application into our database. Our databse is a DB2 UDB of version

    The program tries to insert around 10,000 records into the database.

    Initially we were failing after an insert of around 450 records.
    The SQLcode was -805 and the associated package that it was looking for was SYSLH203.

    The problem, it seemed, was with the bind of the packages.

    We used the command -

    java -url jdbc:db2://LOCALHOST:50000/database -user XXXXXX -password XXXXX -size 20

    This created the package SYSLH203 along with a number of other packages.
    We provided the necessary users with BIND, EXECUTE and CONTROL authority on the new packages added.

    Now, when we ran the program it inserted about 1500 rows but failed again. It was looking for yet another package.

    We now found that the size parameter in the above command was guided by the formula

    no. of isolation levels * holdability per level * integer + 1.

    where no. of isolation levels = 4
    holdability = 2
    and the default value for integer was 3.

    So we now ran the command with the size of 25 ( 4*2*3 +1)

    Now when the program was run after the bind we found that we are able to insert 3500 rows.

    We were now extremely confused!

    What should be the optimum value for size?

    We tried with higher value for size. At 66 the bind process was hanging and users were being timed out. Infact the same occurred when I tried with size 50.

    The highest number of row insertions that we could reach was 9780.

    We do not know the limit and optimum value for size that we should use.

    Request your help in this regard.

  2. #2
    Join Date
    Sep 2004
    Any clue what's happening friends?
    I need urgent help!

  3. #3
    Join Date
    Oct 2003
    I think you are probably "barking up the wrong tree." I suspect that the bottom-line on this situation has nothing to do with packages, or the size-parameter or much of anything else. The computer is probably, simply, choking, and twiddling parameters isn't addressing the underlying issue. Perhaps the transaction isolation level is much too high, or there are lots of index-updates that are piling-up in a transaction that is lasting much too long. Bulk insert operations need to happen in small chunks. (It puzzles me, though, why any such trouble appears after only 450 records.) Does the server log say anything useful?
    ChimneySweep(R): fast, automatic
    table repair at a click of the

  4. #4
    Join Date
    Sep 2004
    I checked the server log.
    It says that ' The escalation of "5528" locks on table "schema.tablename" to lock intent "X" was successful on some occasions.

    It also says -

    ADM1822W The active log is being held by dirty pages. This is not an error,
    but database performance may be impacted. If possible, reduce the database
    work load. If this problem persists, either decrease the SOFTMAX and/or
    increase the NUM_IOCLEANERS DB configuration parameters.

    I am not sure what the problem is!

  5. #5
    Join Date
    May 2003
    There is not a problem with those messages, they are just information messages about what DB2 is doing and some performance considerations. For a large insert, I would increase the NUM_IOCLEANERS , increase the LOGBUFSZ (you must increase DBHEAP by the same amount), and leave the SOFTMAX alone.

    If you are having lock contention among applications (-911 return codes), then you should increase the LOCKLIST by a factor of 10, otherwise don't worry about the lock escalation from row to table level since it improves performance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2004
    I increased the num_iocleaners from 1 to 2.
    locklist is at 100.
    logbufsize was increaesd from 131 to 200.

    I tried to increase maxlocks.
    However, everytime I am trying to do that and restart the instance, the MAXLOCKS is reinitiallised to 0.

    I did a bind after this with size 30.

    The number of records being inserted now has come down to 3471.


  7. #7
    Join Date
    Sep 2004
    i need some help please!

Posting Permissions

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