Timeout and locking problems: DB2 database load utility
Use the following suggestions to help troubleshoot timeout and locking problems that might occur when you use a DB2® database load utility with HCL® Campaign.
Multiple flowcharts are running concurrently and the flowcharts write to the same table. A flowchart run fails with the following errors:
- HCL Campaign UI: "Loader command exited with an error status 4" and
- Loader logs: "SQL0911N The current transaction has been rolled back because of a deadlock or timeout".
For example, you are using multiple flowcharts to insert records to the UA_ContactHistory table using the Mail List process box.
The load utility does not support loading data at the hierarchy level. When you concurrently run multiple flowcharts that load data into the same table, each individual load process locks the table. Each load process has to wait until the previous load finishes. If a process takes a longer time to finish, the next load process in the queue times out and produces the errors listed above.
Table locking during load operations: In most cases, the load utility uses table level locking to restrict access to tables. The level of locking depends on the stage of the load operation and whether it was specified to allow read access.
A load operation in ALLOW NO ACCESS mode uses a super exclusive lock (Z-lock) on the table for the duration of the load. Before a load operation in ALLOW READ ACCESS mode begins, the load utility waits for all applications that began before the load operation to release their locks on the target table. At the beginning of the load operation, the load utility acquires an update lock (U-lock) on the table. It holds this lock until the data is being committed. When the load utility acquires the U-lock on the table, it waits for all applications that hold locks on the table prior to the start of the load operation to release them, even if they have compatible locks. This is achieved by temporarily upgrading the U-lock to a Z-lock, which does not conflict with new table lock requests on the target table as long as the requested locks are compatible with the load operation's U-lock. When data is being committed, the load utility upgrades the lock to a Z-lock, so there can be some delay in commit time while the load utility waits for applications with conflicting locks to finish.
MinReqForLoaderCommandandMinReqForLoaderCommandForAppendis not applicable for contact history table, detailed contact history table, and response history table.- The load operation can time out while it waits for the applications to release their locks on the table prior to loading. However, the load operation does not time out while waiting for the Z-lock needed to commit the data.