Need help on how to find the no of rows inserted/updated/deleted.

One Star

Need help on how to find the no of rows inserted/updated/deleted.

Hi Team,
I am using fileInputDelimeted -> tmap -> tOracleOutput, can any one please let me know how to check no of rows inserted/updated/deleted in this way.
Thanks & Regards,
Rajendra Lella.
Moderator

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
Please take a look at a related forum:http://www.talendforge.org/forum/viewtopic.php?id=30187
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.
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi Sabrina,
Thanks for your quick reply, Please could you also help me on below query,
I have 18 tfileInputDelimeted files to be scheduled and how can I consolidate the status of these jobs in a single mail?
Thanks & Regards,
Rajendra Lella.
Moderator

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
Do you want an email notification from tSendmail? What's your expected result?
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.
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi Sabrina,
My query is, instead of scheduling 18 jobs individually, How can I schedule 18 tfileInputDelimeted in one job and once all these jobs are success of failure the e-mail should be sent.
How is it possible? could you help me on these lines..
thanks & regards,
rajendra
Moderator

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
The 18 tfileInputDelimeted files are same column structure? Do you want to extract data from 18 delimited files into one output? Could you please give us more description about current situation?
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.
Four Stars

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi Rajendra,
Better way of doing this is
- Create a subjob for each file
- Create one log table to write the audit information about each file
- Insert all the data for execution of each job in the database table using variables
- Create one master job which calls all these subjobs
- Create one more job to fetch data from log table, arrange it in html table, assign its value to context variable
- Use this value in tsendmail component to send mail with table contents into it..
I hope you got the point
Thanks
Vaibhav
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi Vaibhav,
Thanks for your information,
Please could you pls help me in sharing any link to check as an example .
thanks & regards,
rajendra lella.
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
The 18 tfileInputDelimeted files are same column structure? Do you want to extract data from 18 delimited files into one output? Could you please give us more description about current situation?
Best regards
Sabrina

Hi Sabrina,
the 18 tfileInputDelimeted files need to be transferred to different tables in a database,
ex:
tinputfileA    \                    /    tableA
tinputfileB    _    tmap     ->   tableB
tinputfileC   /                      \  tableC
once the job is done I need the consolidated e-mail to be sent.
Moderator

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
From your description, you can use onSubjobOk connector to trigger tsendmail .
The workflow should be:
tinputfileA    \                    /    tableA
tinputfileB    _    tmap     ->   tableB
tinputfileC   /                      \  tableC
            |       |                  
    OnSubjobOk    OnSubjoberror
         /                      \
      tsendmail                          tsendmail
Here is a related forum: http://www.talendforge.org/forum/viewtopic.php?id=37101
Hope it will be helpful for you. Or do you want to send an email with number of inserted/updated/deleted rows?
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.
Four Stars

Re: Need help on how to find the no of rows inserted/updated/deleted.

Why do you need tMap in between, besides, you can have simple flow like
tinputfileA-->tableA in one talend job and other flows in other jobs
Call all these jobs in single master job and use sendmail component on OnSubjobOk link...
or use
tinputfileA-->tableA 
|
OnSubjobOk 
|
tinputfileB-->tableB 
etc...
It is not a good idea to use tMap if you don't have any lookups..
Vaibhav
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
From your description, you can use onSubjobOk connector to trigger tsendmail .
The workflow should be:
tinputfileA    \                    /    tableA
tinputfileB    _    tmap     ->   tableB
tinputfileC   /                      \  tableC
            |       |                  
    OnSubjobOk    OnSubjoberror
         /                      \
      tsendmail                          tsendmail
Here is a related forum: http://www.talendforge.org/forum/viewtopic.php?id=37101
Hope it will be helpful for you. Or do you want to send an email with number of inserted/updated/deleted rows?
Best regards
Sabrina

Hi Sabrina,
Thanks for your valuable help and support,
Please find the details,
I have 18 jobs to be executed, I learnt that the jobs can be executed one by one by making use of on job ok,
Please find my query,
Job 1 has executed successfully and it has inserted 20 rows in table1
Job 2 has executed successfully and it has inserted 2 rows in table2
Job 3 has executed successfully and it has inserted 10 rows in table3
Now I have to consolidate the details and send an e-mail, which should be as below
Job1,
Inserted 20 rows in table1
Job2,
Inserted 2 rows in table2
Job3,
Inserted 10 rows in table3
How can I track all jobs information and populate it in tSendMail?
Please could you help me on these lines?
Thanks for your valuable help and support.
Regards,
Rajendra Lella,
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Can any one help me ??
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi,
From your description, you can use onSubjobOk connector to trigger tsendmail .
The workflow should be:
tinputfileA    \                    /    tableA
tinputfileB    _    tmap     ->   tableB
tinputfileC   /                      \  tableC
            |       |                  
    OnSubjobOk    OnSubjoberror
         /                      \
      tsendmail                          tsendmail
Here is a related forum: http://www.talendforge.org/forum/viewtopic.php?id=37101
Hope it will be helpful for you. Or do you want to send an email with number of inserted/updated/deleted rows?
Best regards
Sabrina

Hi Sabrina,
Thanks for your valuable help and support,
Please find the details,
I have 18 jobs to be executed, I learnt that the jobs can be executed one by one by making use of on job ok,
Please find my query,
Job 1 has executed successfully and it has inserted 20 rows in table1
Job 2 has executed successfully and it has inserted 2 rows in table2
Job 3 has executed successfully and it has inserted 10 rows in table3
Now I have to consolidate the details and send an e-mail, which should be as below
Job1,
Inserted 20 rows in table1
Job2,
Inserted 2 rows in table2
Job3,
Inserted 10 rows in table3
How can I track all jobs information and populate it in tSendMail?
Please could you help me on these lines?
Thanks for your valuable help and support.
Regards,
Rajendra Lella,
Can any one help me in this regards...?
Four Stars

Re: Need help on how to find the no of rows inserted/updated/deleted.

inside tSendMail component you can use following

"Inserted "+(Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_INSERTED")+ rows in "+table1"+"\n"+
Inserted "+(Integer)globalMap.get("tMSSqlOutput_2_NB_LINE_INSERTED")+ rows in "+"table2"+"\n"+
"Inserted "+(Integer)globalMap.get("tMSSqlOutput_3_NB_LINE_INSERTED")+ rows in "+"table3""\n"+
etc
(Integer)globalMap.get("tMSSqlOutput_3_NB_LINE_INSERTED") - this variable you can get using Ctrl+Space for respective output database component.
Vaibhav
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

Thanks Vaibhav for your help and support.
I will try given scenario and will let you know in case if I find any issues..
One Star

Re: Need help on how to find the no of rows inserted/updated/deleted.

hai XDSHI
Generate 100 records using trowgenerator and load the data into the db
While loading into db there must be two flows one for insertion and other for updating  the existing records in the db.
Output requirement:
Capture the following details for that job in separate tables,
a.       Job start time
b.      Job end time
c.       No of records inserted
d.      No of records updated
e.      No of records deleted
f.        Duration 

Help me to get this output
Moderator

Re: Need help on how to find the no of rows inserted/updated/deleted.

Hi prabhakaran,
We usually use Nb_LINE 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("tDBOutput_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.
 
Here is a tstatCatcher component which is used to gather the Job processing metadata at the Job level and at the component level.
Please take a look at this component reference.
https://help.talend.com/search/all?query=tStatCatcher&content-lang=en

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.