Hi,
something very strange is happening in one test I am runing. My transaction is rollbacking due to error -289.

Full description of the error message:
"SQL1476N The current transaction was rolled back because of error "-289"."
SQLSTATE=40506

SQL1476N => Stands for rollback
-289 => Stands for Cannot lock table table-name in requested mode.
State40506=> Stands for "The current transaction was rolled back because of an SQL error."

Impressions:
Now, i know that anyone reading this should think: well, obviously someone has locked your table and your transaction eventually rolleback due to too long a wait for the table to become available.

My response is, i find it unlikely:

1st My database systems is only runing my transactions, and I only run one transaction and session at a time;

2nd The transaction that is rollecback is a bulk insert.

3rd Before the bulk insert I create the target output table

4th I lock the table in exclusive mode, like so: "LOCK TABLE TB_TARGET IN EXCLUSIVE MODE"

5th I order the execution of the Insert Into statement

6th Eventually it blows.



Now, obviously, it would seem that my statement at 4 is the root of my problems. It might be so, but if so: it makes litle sense:

1st This optmization was sugested in an IBM post, as means of reducing row locking for large bulk inserts. I took it and it seems to work fine.

2nd This Bulk Insert statement, lets call it B1, is the only one that blows. I've used exactly the same procedure to run other bulk inserts, say B2, to B5, and none of them have given me problems. Finally, even B1 does not give me any problems when the input table contains 25 Million records. The bulk insert of B1 with 125 million input records being processed is the only one that gives me problems.

3rd Before this bulk insert is rolledback because it cannot lock the table in the desired mode, as explained above. However, the Bulk Insert runs for more than one hour before deciding it should roll back. And all the while its transaction is actively performing IO in over the source tablespace, target tablespace and temporay table space.


Does anybody have any Idea why a bulk insert statemnet would incur in this type o conflict?

I am afraid i was mistaken.

DB2 Tablespaces Pages Full Little Pieces

I must have read wrongly the error code, what I am lacking is space in my temporary tablespace, it would seem. According to my statistics i wrote 31 GB there, the limit is 32 Gb or close enough...
Removing the table lock statement did not solve the problem.
Going to retry the bulk insert having extended my temporary raw partition to 38 GB.

Hope this solves it.

Have you thought about using other means besides INSERT to load your data. INSERT is the last resort for that much data. It also helps if you post your statements.

I solved the problem by enlarging the temprary tablespace.


And I do not see why inserting is the last means of performing bulk operations.
Insert as select are designed to support precisely these type of transformations.
Moreover, it is quite common for data to be loaded from text files into databases into staging tables. And from thereafter continue transforming the whole set of data into output tables.


Thank you nevertheless.