Retrieve the number of rows inserted or update in a Table

[Umbix0562]

Hi

I have a Job Parallel (datastage 8.1) writing in a table (Oracle 10g) by an upsert. I would to create e routine to capture some information as, for example, the number of inserted or updated rows, as they appears in the Job's Log. May anybody suggest me a solution? How can I retrieve these information?

Thank you

Umberto

[chulett]

In general, you can retrieve the number of insert and update operations from link counts in the logs, which is what is noted there but which for anything other than inserts does not necessarily equate one-to-one with records. Check the various "DSGet" functions available listed in the Designer's Help index.

Your particular quest is going to be complicated by the fact that you are using "upsert" logic which in the end means you'll have no clue how many operations were actually inserts versus updates. Is there any way you can determine that from the data itself, query the target after the job runs to identify records from the most recent run and which are inserts and which were update?

[ray.wurlod]

Further, the link row count is only the number of rows sent - no information exists in the link row count about how many of these rows were successfully inserted/updated. The only guaranteed method is to compare before and after images of the table.

[Sreenivasulu]

Unfortunately sometimes reading the joblog also does not help.
For example In 8.1 using an oracle enterprise stage there is no option of 'user defined insert sql' . It needs to be put in 'user defined update'.
Here the 'records inserted' come as 'records updated'. So you need to read 'records updated as records inserted' if you have used 'user defined insert sql'.

Hence you cannot capture the records inserted even by reading the 'joblog' in a straightforward 'resusable way' by 'reading the job log'

Regards
Sreeni

[arunpramanik]

Hi

I have a Job Parallel (datastage 8.1) writing in a table (Oracle 10g) by an upsert. I would to create e routine to capture some information as, for example, the number of inserted or updated rows, as they appears in the Job's Log. May anybody suggest me a solution? How can I retrieve these information?

Thank you

Umberto

Alternative solution will be, to have some Audit columns , like (process batch no, creation date, last update date) in the target table. Retrieve the count from this column based on process batch number after the job is completed.