After upgrading from TDM Portal 4.9.x to TDM Portal 4.11.x our Publishing jobs are failing

Products

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

Issue/Introduction

Recently, after the upgrade of TDM Portal, we are unable to publish the data with a repeat count (Iteration count) more than 8. 

We are able to publish the data as long as we  do not go beyond 8 iterations. Otherwise, we see the following error:

ERROR: Publish failed for job <nnnnn>, Insert into <Table_Name> failed: DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO "<SCHEMA>"."<TABLE>" ("COLUMN1","COLUMN2","COLUMN3","COLUMN4","COLUMN5","COLUMN6","COLUMN7","COLUMN8","COLUMN9","COLUMN10","COLUMN11","COLUMN12","COLUMN13","COLUMN14","COLUMN15","COLUMN16","COLUMN17","COLUMN18","COLUMN19","COLUMN20","COLUMN21","COLUMN22","COLUMN23","COLUMN24","COLUMN25","COLUMN26","COLUMN27","COLUMN28","COLUMN29","COLUMN30","COLUMN31","COLUMN32","COLUMN33","COLUMN34","COLUMN35","COLUMN36","COLUMN37","COLUMN38","COLUMN39","COLUMN40","COLUMN41","COLUMN42","COLUMN43","COLUMN44","COLUMN45","COLUMN46","COLUMN47","COLUMN48","COLUMN49","COLUMN50") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ORA-02291: integrity constraint (SCHEMA.TABLE_1FK) violated - parent key not found

Cause

There was a new feature added in TDM Portal 4.10 patch that allows for batch commits for publishing, Ordinarily when running a publishing to a database table, TDM would insert the data one row at a time. This was to preserve the data integrity, and not violate the constraints on the tables, as long as the table publish order was set properly so that the rows in the parent table were inserted before the child tables.

When the batch commit is enabled, TDM holds the inserts and publish them in batches. Because of the batch commits, we don't have a way to ensure the data integrity, and that constraint violations will not happen. Normally, when enabling the batch option, we recommend using a Pre- and Post-script that removes the table constraints, and re-enables them after the publishing has completed.

Resolution

To resolves this issue, we had to make the following two changes to the %CATDMWEB_HOME%\conf\application.properties file:

1. Modify line 193 to disable the batch commit by changing the value from true to false.
   This is disabled by default, but had been enabled in for the customer's Portal server.
   
   # If this flag is set to true, records will be inserted in batches in the target database.
   # The default is false i.e. records will be inserted individually to the target database.
   # This setting can be overridden in the parameters of an individual publish job.
   # Please note that this parameter is effective only if the target database supports batch inserts
   # and actionOnDuplicate for the publish job is set to the default value of "exit".
   tdmweb.publish.batchCommit=false
   
   
2. Modify line 197 to disable the global repeat count based commit by changing the value from true to false. 
   By default, this parameter is enabled.
   
   #If this flag is set to true along with tdmweb.publish.batchCommit=true then records will be inserted in batches.
   # Table repetitions would be segregated to batches of size equivalent to tdmweb.publish.iterationsBeforeCommit
   tdmweb.publish.globalRepeatCountBasedCommit=false

After saving the changes, you must stop and restart the CA Test Data Manager Portal service for the changes to take effect.