Six Stars

Difference between NB_LINE_INSERTED and NB_LINE global variables?

Hi Everyone,

 

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.

 

Thanks,

Ram

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

Hi Rohit,

The nb_line gives the total number of rows processed in a link. We can use this global variable assigned to a output field in tmap.

Nb_line_updated/inserted global variables can only be used to determine the number of rows inserted into a target dB. But we can only use this if the row counts expected within 10000. If the count exceeds then the counter is reset to 0. So if you are inserting 10002 records into target then this variable will give you a value of 2 which is incorrect. So use some other strategy to get the total number of records inserted or updated into target.

Please let me know if you don't get it.

Thanks,
Ram
8 REPLIES
Seven Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE 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.

 

 

Six Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

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. 

Please comment.

Six Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

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.

Seven Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

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.

Moderator

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

Hello,

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.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

How you doing this.can you  attach screen shot or explain in better way. I am trying todo the same thing.

Six Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

Hi Rohit,

The nb_line gives the total number of rows processed in a link. We can use this global variable assigned to a output field in tmap.

Nb_line_updated/inserted global variables can only be used to determine the number of rows inserted into a target dB. But we can only use this if the row counts expected within 10000. If the count exceeds then the counter is reset to 0. So if you are inserting 10002 records into target then this variable will give you a value of 2 which is incorrect. So use some other strategy to get the total number of records inserted or updated into target.

Please let me know if you don't get it.

Thanks,
Ram
Six Stars

Re: Difference between NB_LINE_INSERTED and NB_LINE global variables?

Thanks , I implemented your first solution. it,s working fine