Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have this job, which works fine in Studio. Records are committed, but when run in the cloud as a scheduled job, records seems not to be committed. I do have a TDBCommit component.
So, why are records committed in Studio and (look like it) not when running as a scheduled job from the TMC ? I use remote engines which run on premise and other jobs which have the same setup commit the records from that job.
What did I miss ?
Jacco
Addition....
The job is called from parent job
second addition .. .
when I run a job from TMC with the job that doesn't seem to commit . . ..... the job commits !! (??)
Mind blown !
@Jacco de Zwart
First, you are using tDBCommit to commit the change, make sure the tDBOutput components use an existing DB connection. tDBCommit component is linked with onsubjobOK, means the component is triggered only when the previous subjob works OK, go to double check if previous subjob works fine when the job is executed on TMC, if the subjob fails, there is no records are commit to DB.
Regards
Shong
Shong,
I have checked your suggestions and to me all the settings you suggest are in place. All the tDBOutput have the appropriate existing DBConnection as well as the tDBCommit component.
The tDBCommit component is linked with onSubJobOK.
I have performed a debug run in the TMC and looked for the place where the rows should be inserted in the table and a commit is performed. That section of the debug log looks like this :
----
1667895495061 2022-11-08 08:18:15.061 DEBUG tREST_1 - Done.
1667895495061 2022-11-08 08:18:15.061 DEBUG tReplicate_1 - Done.
1667895495061 2022-11-08 08:18:15.061 DEBUG tExtractJSONFields_1 - Extracted records count: 128 .
1667895495062 2022-11-08 08:18:15.062 DEBUG tExtractJSONFields_1 - Done.
1667895495062 2022-11-08 08:18:15.062 DEBUG tMap_3 - Written records count in the table 'BookingBase': 128.
1667895495062 2022-11-08 08:18:15.062 DEBUG tMap_3 - Done.
1667895495063 2022-11-08 08:18:15.063 DEBUG tDBOutput_1 - Done.
1667895495063 2022-11-08 08:18:15.063 DEBUG tExtractJSONFields_2 - Extracted records count: 3305 .
1667895495063 2022-11-08 08:18:15.063 DEBUG tExtractJSONFields_2 - Done.
1667895495063 2022-11-08 08:18:15.063 DEBUG tMap_1 - Written records count in the table 'out1': 3297.
1667895495064 2022-11-08 08:18:15.064 DEBUG tMap_1 - Done.
1667895495064 2022-11-08 08:18:15.064 DEBUG tDBOutput_2 - Done.
1667895495064 2022-11-08 08:18:15.064 DEBUG tExtractJSONFields_3 - Extracted records count: 472 .
1667895495065 2022-11-08 08:18:15.065 DEBUG tExtractJSONFields_3 - Done.
1667895495065 2022-11-08 08:18:15.065 DEBUG tMap_4 - Written records count in the table 'out3': 472.
1667895495065 2022-11-08 08:18:15.065 DEBUG tMap_4 - Done.
1667895495065 2022-11-08 08:18:15.065 DEBUG tDBOutput_3 - Done.
1667895495066 2022-11-08 08:18:15.066 DEBUG tDBCommit_1 - Start to work.
1667895495066 2022-11-08 08:18:15.066 DEBUG tDBCommit_1 - Parameters:CONNECTION = tDBConnection_2 | CLOSE = true | UNIFIED_COMPONENTS = tMSSqlCommit |
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' starting to commit.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' commit has succeeded.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Closing the connection 'tDBConnection_2' to the database.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' to the database closed.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Done.
1667895495067 2022-11-08 08:18:15.067 DEBUG update dbo.JOB_INSTANCE_STATUS set JOB_ENDED_AT=?,JOB_RESULT=?,TIME_RANGE_START=?,TIME_RANGE_END=?,COUNT_INPUT=?,COUNT_OUTPUT=?,COUNT_REJECTED=?,COUNT_DELETED=?,RETURN_CODE=?,RETURN_MESSAGE=?,VALUE_RANGE_START=?,VALUE_RANGE_END=?,COUNT_UPDATED=? where JOB_INSTANCE_ID=?
1667895495072 2022-11-08 08:18:15.072 INFO TalendJob: 'StagingLoad_Bookings_JSON' - Finished - status: end returnCode: 0
1667895495076 2022-11-08 08:18:15.076 INFO tRunJob_1 - The child job 'dwh2.stagingload_bookings_json_0_1.StagingLoad_Bookings_JSON' is done.
1667895495076 2022-11-08 08:18:15.076 DEBUG tRunJob_1 - Done.
----
no error, no warning, no message.
But no real commit as well.
Like I said yesterday, running the job individually results with a commit of all the rows retrieve in that job.
Did a debug run on both jobs and looked at the part where the commit should be performed.
Debug of Total Job :
----
1667895495063 2022-11-08 08:18:15.063 DEBUG tMap_1 - Written records count in the table 'out1': 3297.
1667895495064 2022-11-08 08:18:15.064 DEBUG tMap_1 - Done.
1667895495064 2022-11-08 08:18:15.064 DEBUG tDBOutput_2 - Done.
1667895495064 2022-11-08 08:18:15.064 DEBUG tExtractJSONFields_3 - Extracted records count: 472 .
1667895495065 2022-11-08 08:18:15.065 DEBUG tExtractJSONFields_3 - Done.
1667895495065 2022-11-08 08:18:15.065 DEBUG tMap_4 - Written records count in the table 'out3': 472.
1667895495065 2022-11-08 08:18:15.065 DEBUG tMap_4 - Done.
1667895495065 2022-11-08 08:18:15.065 DEBUG tDBOutput_3 - Done.
1667895495066 2022-11-08 08:18:15.066 DEBUG tDBCommit_1 - Start to work.
1667895495066 2022-11-08 08:18:15.066 DEBUG tDBCommit_1 - Parameters:CONNECTION = tDBConnection_2 | CLOSE = true | UNIFIED_COMPONENTS = tMSSqlCommit |
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' starting to commit.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' commit has succeeded.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Closing the connection 'tDBConnection_2' to the database.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' to the database closed.
1667895495067 2022-11-08 08:18:15.067 DEBUG tDBCommit_1 - Done.
1667895495067 2022-11-08 08:18:15.067 DEBUG update dbo.JOB_INSTANCE_STATUS set JOB_ENDED_AT=?,JOB_RESULT=?,TIME_RANGE_START=?,TIME_RANGE_END=?,COUNT_INPUT=?,COUNT_OUTPUT=?,COUNT_REJECTED=?,COUNT_DELETED=?,RETURN_CODE=?,RETURN_MESSAGE=?,VALUE_RANGE_START=?,VALUE_RANGE_END=?,COUNT_UPDATED=? where JOB_INSTANCE_ID=?
1667895495072 2022-11-08 08:18:15.072 INFO TalendJob: 'StagingLoad_Bookings_JSON' - Finished - status: end returnCode: 0
----
Debug of Staging job only :
----
1667898102451 2022-11-08 09:01:42.451 DEBUG tMap_1 - Written records count in the table 'out1': 10792.
1667898102451 2022-11-08 09:01:42.451 DEBUG tMap_1 - Done.
1667898102451 2022-11-08 09:01:42.451 DEBUG tDBOutput_2 - Done.
1667898102455 2022-11-08 09:01:42.455 DEBUG tExtractJSONFields_3 - Extracted records count: 1480 .
1667898102456 2022-11-08 09:01:42.456 DEBUG tExtractJSONFields_3 - Done.
1667898102456 2022-11-08 09:01:42.456 DEBUG tMap_4 - Written records count in the table 'out3': 1480.
1667898102456 2022-11-08 09:01:42.456 DEBUG tMap_4 - Done.
1667898102456 2022-11-08 09:01:42.456 DEBUG tDBOutput_3 - Done.
1667898102456 2022-11-08 09:01:42.456 DEBUG tDBCommit_1 - Start to work.
1667898102456 2022-11-08 09:01:42.456 DEBUG tDBCommit_1 - Parameters:CONNECTION = tDBConnection_2 | CLOSE = true | UNIFIED_COMPONENTS = tMSSqlCommit |
1667898102456 2022-11-08 09:01:42.456 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' starting to commit.
1667898102461 2022-11-08 09:01:42.461 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' commit has succeeded.
1667898102461 2022-11-08 09:01:42.461 DEBUG tDBCommit_1 - Closing the connection 'tDBConnection_2' to the database.
1667898102461 2022-11-08 09:01:42.461 DEBUG tDBCommit_1 - Connection 'tDBConnection_2' to the database closed.
1667898102461 2022-11-08 09:01:42.461 DEBUG tDBCommit_1 - Done.
1667898102464 2022-11-08 09:01:42.464 DEBUG update dbo.JOB_INSTANCE_STATUS set JOB_ENDED_AT=?,JOB_RESULT=?,TIME_RANGE_START=?,TIME_RANGE_END=?,COUNT_INPUT=?,COUNT_OUTPUT=?,COUNT_REJECTED=?,COUNT_DELETED=?,RETURN_CODE=?,RETURN_MESSAGE=?,VALUE_RANGE_START=?,VALUE_RANGE_END=?,COUNT_UPDATED=? where JOB_INSTANCE_ID=?
1667898102478 2022-11-08 09:01:42.478 INFO TalendJob: 'StagingLoad_Bookings_JSON' - Finished - status: end returnCode: 0
1667898102483 2022-11-08 09:01:42.483 INFO TalendJob: 'StagingLoad_Bookings_JSON' - Done.
----
Looks to me that the same steps are taken and executed fine.
But question remains, why does the Total job not commit the records and the stand-alone job (which is part of the total job) does ??
Jacco