0

We are working on a data warehouse using IBM DB2 and we wanted to load data by partition exchange. That means we prepare a temporary table with the data we want to load into the target table and then use that entire table as a data partition in the target table. If there was previous data we just discard the old partition.

Basically you just do "ALTER TABLE target_table ATTACH PARTITION pname [starting and ending clauses] FROM temp_table".

It works wonderfully, but only for one operation at a time. If we do multiple loads in parallel or try to attach multiple partitions to the same table it's raining deadlock errors from the database.

From what I understand, the problem isn't necessarily with parallel access to the target table itself (locking it changes nothing), but accesses to system catalog tables in the background.

I have combed through the DB2 documentation but the only reference to the topic of concurrent DDL statements I found at all was to avoid doing them. The answer to this question, can't be to simply not attempt it?

Does anyone know a way to deal with this problem?

I tried to have a global, single synchronization table to lock if you want to attach any partitions, but it didn't help either. Either I'm missing something (implicit commits somewhere?) or some of the data catalog updates even happen asynchronously, which makes the whole problem much worse. If that is the case, is there are any chance at all to query if the attach is safe to perform at any given moment?

2

1 Answer 1

1

This is because the ALTER TABLE DDL statement is changing the table definition.

Db2 has to update the catalog tables to record the DDL changes, and it uses a Z-lock (Super Exclusive) when it does that. This means no concurrent ALTER TABLE can run against the same table.

https://www.ibm.com/docs/en/db2/11.5?topic=management-lock-attributes

2
  • Hm yeah, I gathered that it would be locked. The question is how to synchronize it so it doesn't kill itself? The application is inherently concurrent, I won't be able to forbid it from attempting a concurrent partition attach. So I have to synchronize it somehow. I still don't understand why locking a central sync table does not work. DB2 must do several things asynchronously, not as part of the transaction. Aug 27, 2022 at 8:06
  • It's because the db2 catalog is being updated, and that's where the Z-lock is acquired. Doesn't matter what lock you apply to the base table, it's the catalog update which is blocking you. There is no work around, you cannot make concurrent partition changes to the same table. Whatever the application is, it will have to be adapted to single-stream partition changes.
    – Greg
    Sep 8, 2022 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.