DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506
Article ID: 229513
Updated On:
Products
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
Seems a locking error due to which unable to trigger the script with Parellel = Yes option in FDM
Environment
Release : 4.9
Component : Fast Data Masker
Resolution
********************************************************************
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). This suggests some database configuration needs to be tuned. Checking the db2diag log leading into the error, the final error was "The escalation of "55" locks on table "BPCP1.LSW_PROCESS_VARIABLE" to lock intent "X" has failed. The SQLCODE is "-911".
In this case, the following database tuning was done:
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.
Doc APAR JR55210 has been opened to include this recommendation.
********************************************************************
Looks like your DBA needs to look at this error and the above IBM suggestion to assist you. It does not appear to be a FDM issue.
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
The error originates from the server side and we log it and cause ...
The error is caused by the DB2 Transaction log for the Data Warehouse becoming full and not able to process the full batch operation.
Resolving The Problem
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.
I suggested the same thing to our DBA last time and they confirmed that this is already the production size(LogFilSiz, LogPrimary, and LogSecond) and that shouldn’t be the case. Do you have any suggestion for the ideal size for LogFilSiz, LogPrimary, and LogSecond as I am been errored on almost every masking job.
It is causing due the deadlocks. Isn’t it due to multiple composite keys on the tables. Not sure.
I would suggest to disable large table split option to reduce concurrency. You can also reduce the number of threads from currently 10.
I didn’t tried this yet, I resolved it by fragmenting the script into number of scripts and it went well somehow. However, this made my work cumbersome.
Will try the suggestion next time I am going to see this error.
Feedback
