Dear BFI Admins/ @ssakunala
The data imports for our BFI environment are failing and i see the below error in the import log :
ImportStep:CatalogMerger) Moving IBM Catalog data to SAM tables (import time ‘2021-05-13 00:30:00.190’).
2021-05-13 00:33:10 (+0:03:01.913) WARN: (ImportStep:CatalogMerger) com.ibm.license.mgmt.core.tools.merger.dao.CatalogDAO::runCatalogUpdate::Exception while running query: ’ UPDATE sam.definition_sources_persistent SET updated_at = ‘2021-05-13 00:30:00.190’ WHERE id in ( SELECT trg_per.id FROM sam.definition_sources_persistent trg_per JOIN sam.definition_sources_scd trg_scd ON trg_scd.id = trg_per.id AND trg_scd.valid_from < ‘2021-05-13 00:30:00.190’ AND trg_scd.valid_to = ‘9999-12-31 23:59:59.997’ JOIN ibm.definition_sources src ON trg_per.ibm_id = src.id AND src.valid_to = ‘9999-12-31 23:59:59.997’ WHERE trg_per.ibm_id > 0 and ( src.name <> trg_scd.name OR src.contact <> trg_scd.contact OR src.organization <> trg_scd.organization OR src.description <> trg_scd.description ) ) with ur '.
2021-05-13 00:33:10 (+0:00:00.002) ERROR: (ImportStep:CatalogMerger) SQLException -> Message : mainException
-> SQL Message : The current transaction has been rolled back because of a deadlock or timeout. Reason code “68”… SQLCODE=-911, SQLSTATE=40001, DRIVER=3.72.24
-> SQL ErrorCode : -911
-> SQL State : 40001
com.ibm.db2.jcc.am.SqlTransactionRollbackException: The current transaction has been rolled back because of a deadlock or timeout. Reason code “68”… SQLCODE=-911, SQLSTATE=40001, DRIVER=3.72.24
BFI version : 9.2.16
Catalog is 10.0.3.1
Does anyone know or see this issue before, please share some thoughts and help me to remediate the same.
Thank You
Mayank
A SQL Deadlock message usually indicates there is another SQL job running on the database server that conflicts with the import…check for Database Backup jobs, DBCC Consistency Checks, or Index Reorganization jobs on the database server that may have been running at the same time as the import, and then modify your schedules so the jobs do not overlap the Import.
1 Like
Depending on how long the daily import runs and other process controls, you can try a manual import at a different time and see if it completes successfully. This will be an indication of some conflict during the scheduled import time.
1 Like
Hello Jason,
Thanks for responding, however my bad i missed mentioning that i did a manual import since i got the same gist that may be another job or some other queries are running in parallel during scheduled import time but no luck, the manual import that i initiated was about 6 hours later and that doesn’t work too.
Anything else i can check, since the import error says exception occurs during importstep:catalogmerger does it have anything to do with catalog customizations?
Thank You
Mayank
Hello @ssakunala
Thanks for responding, however my bad i missed mentioning that i did a manual import since i got the same gist that may be another job or some other queries are running in parallel during scheduled import time but no luck, the manual import that i initiated was about 6 hours later and that doesn’t work too.
Anything else i can check, since the import error says exception occurs during importstep:catalogmerger does it have anything to do with catalog customizations?
Thank You
Mayank
From your message looks like you have some catalog customizations. Your BFI version is kind of old and it’s possible this could be causing the issue.
Do you have only custom signatures or any custom products added to the catalog?
Open a case with HCL BFI support and the L2/L3 team can provide some SQL Queries to extract and analyze the data that’s causing the timeout.
1 Like