DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Error for batch element #1: DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-911

2021-11-09 12:53:21.900 Thread:4 - [jcc][103][10843][3.69.78] Non-recoverable chain-breaking exception occurred during batch processing.  The batch is terminated non-atomically. ERRORCODE=-4225, SQLSTATE=null 

This appears to be a locking error - unable to trigger the script with Parellel = Yes option in FDM

Environment

Fast Data Masker all supported releases

Cause

The error "DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-911, DRIVER=4.18.60" indicates a deadlock or timeout occurred while executing the batch updated. (sqlerrmc=-911).

Checking the db2diag log leading into the error, the final error was
"The escalation of "55" locks on table "<SCHEMA>.<TABLE>" to lock intent "X" has failed. The SQLCODE is "-911".

Recommended database configuration tuning by IBM:

• db2 update db cfg for bpmdb using MAXLOCKS AUTOMATIC;
• db2 update db cfg for bpmdb using LOCKLIST AUTOMATIC;
• If the DB2 self tuning memory feature is enabled (which is the default for DB2), it is recommended to set the LOCKLIST and MAXLOCKS to AUTOMATIC.

Have your DB2 DBA review the following IBM documentation to confirm and assist you:
https://www.ibm.com/support/pages/db2-error-during-server-rename-batch-failure%C2%A0-batch-was-submitted-least-one-exception-occurred-individual-member-batch
To access the IBM Page, you must have an active IBM account.

Resolution

The error is caused by the DB2 Transaction log for the Data Warehouse becoming full and not able to process the full batch operation.

Steps taken to resolve this error:

• Increase the LogFilSiz, LogPrimary, and LogSecond for the Data Warehouse.
• Stop the IBM Collaborative Lifecycle Management (CLM) server and re-start it. This should start the processing of the batch process.
• Changing the LogFilSiz, LogPrimary, and LogSecond variable is illustrated under the topic of Tuning transaction log characteristics in the IBM DB2 Information Center.

Workarounds that can be taken in FDM to help reduce the threat of deadlocks:

• Disable LARGETABLESPLIT option to reduce concurrency.
• If Using the PARALLEL option, reduce the number of threads to 4.
• Break the FDM script into multiple scripts to reduce the size of the job.