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

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 com.ibm.db2.jcc.DB2Binder -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.

Any clue what's happening friends?
I need urgent help!

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?

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!

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.

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.

HELP PLEASE!

i need some help please!