Count of Inserted rows

[Anonymous]

Hi All,
I have a job which reads more than 5000 records daily. I want to create a log table which captures job status ( OK/FAILED) , No of Rows Inserted , No of Rows Updated , No of Rows Deleted.
So I can be able to identify the no of rows inserted daily.
Kindly assist how to do this.

Thanks,
Saty.

[Anonymous]

Hi,
When inserted item is chosen, there will be a global variale such as ((Integer)globalMap.get("tOracleRow_1_NB_LINE_INSERTED")) will be avaible, which counts the total number of records have been inserted into database.
Press ctrl+space to access all the global variables
See my screenshot
Best regards
Sabrina

[Anonymous]

Thanks Sabrina,

I tried to use that parameter in my job. But I'm getting null in my output. Kindly go through my screenshots.

Kindly assist.

Thanks,
Saty.

[janhess]

You need to make sure the log process is running after the other processes - use an on subjob ok.

[Anonymous]

You need to make sure the log process is running after the other processes - use an on subjob ok.

Hi Janhess,
Once the main job execution is done and Job status is captured by tAssert components. And then Log Process works.
So it is the last process to run in the job. No need to use on Subjob Ok for Log Process I guess.
Thanks,
Saty.

[janhess]

Shouldn't you be using tMySQLOutput1 for the stats?

[Anonymous]

Shouldn't you be using tMySQLOutput1 for the stats?

Hi Janhess,
I'm not clear. Are u asking me whether I'm using these functions using tMySQLOutput component.? If so, Yes. I'm using it to call these functions in tMap ( in Log Process ) component.

Thanks,
Saty.

[janhess]

No the tMySQLOutput you reference in your rule is 2. Looking at your picture I thought it should be 1.

[Anonymous]

No the tMySQLOutput you reference in your rule is 2. Looking at your picture I thought it should be 1.

Thanks Janhess. I did not observe that. I've changed to 1 and now I got it. I've got values under
' TOTAL_NO_OF_ROWS ' and ' NO_OF_ROWS_INSERTED '. Because I'm only inserting the data.
I've changed the job as in Pic 1. And I have used two queries in tMySqlRow component. One Update and one Delete query. U can see the properties of tMySqlRow component in Pic 2.
Now I want to capture deleted count and updated count.
I hope I've explained clearly. Kindly assist.
Thanks,
Saty.

[Anonymous]

Hi Saty
There is no global variable like (Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED") available if you use tMysqlRow to execute the statements, if you want to count the number of rows are deleted or updated, you have to use tMysqlOutput to do it. To do:
1. Query the records from table with tMysqlInput, and link it to a tMap.
2. On tMap, define two output table, one for deleting, and another one for updating, set the filter expression of deleting output as:
row1.id<2000
set the fitler expression of updating output as:
row1.id>5000
3. Link each output to a tMysqlOutput component and select corresponding action 'delete'/'update'.
The job design looks like:
tMysqlInput--main--tMap--out1--tMysqlOutput_1(for deleting)
--out2--tMysqlOutput_2(for updating)
Then, you can use the global variables on other subjob.
(Integer)globalMap.get("tMysqlOutput_1_NB_LINE_DELETED")
(Integer)globalMap.get("tMysqlOutput_2_NB_LINE_UPDATED")
Shong