I am trying to pull the row counts of a job in the sub job as a variable in tStatcatcher. The count of inserted records in a variable(tMap) using "((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_INSERTED")) " it work fine and gives the result when the count of rows processed is under 9999. When it reaches 10000 this returns 0. Also if I use ((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE")) it gives me correct result for the rows>=10000 as well. Is this a known issue? I am using both the variable only in a sub job to get the row count from the previous job.
Kindly share your thoughts on these global variables.
My guess is that tMSSqlOutput_1_NB_LINE_INSERTED resets for every batch inserted.
You can go to the advanced settings of the output component and change the "commit every" option to test this.
For example, if your batch size is 1000, this variable will rest to 0 once it reaches 1000.
Whereas tMSSqlOutput_1_NB_LINE gives you the total number of rows inserted into the database.
Thanks Arvin for your reply.
It seems logical which you said, I tried increasing the commit changes to '20000' but still, the row count comes as 0. Maybe the maximum value for committing is 10000 only.
Sorry, missed changing the Batch size, now it works as you said.
But if this is the case how can we get the number of rows updated at the end of the job?
If the case is to get the count of rows updated and the updated rows are in lakhs/millions then use of this variable will not give us the correct result.
Is this not an enhancement which can be done in order to fetch all the count in one run of the job instead of getting the count in batch?
Talend Team - Please comment.
Have you tried using this global variable - tMSSqlOutput_1_NB_LINE_UPDATED ?
Assuming you have set the keys in the schema and selected the update option in the component, I think this variable should work just fine.
Usually, we use Nb_LINE is used to count the total number of records have been proceed.
When inserted item is choosen, there will be a global variale such as ((Integer)globalMap.get("t<DB>Output_1_NB_LINE_INSERTED")) will be avaible, which counts the total number of records have been inserted into database. The same to updated, deleted items.