Number of record affected by SQL operations in the job

[jseclen]

Hi Forum,

I'm migrating DTS processes to DataStage jobs, but, in this DTS the customer use code to insert record in a log table, this log table contains all sql operations including the number of records affected, for example, after an insert, delete or update sentence. In sql server, this is possible but in DataStage??

Can i captured this results and use it ???

Thanks,

[ray.wurlod]

Not directly. DataStage records the number of rows sent, and the SQL can be captured through stage tracing. Sending this information to a log could also be done, but you would need to design this yourself (just as you would have to script it in DTS). Exactly how would depend on the format of your log - in particular whether it is a text file or a database table. You specified "log table" so possibly the easiest method for you is a DataStage server job that collects and writes the information.

Much of this already exists in downloadable form in the ETLStats package, from
Kim Duke's website

[baglasumit21]

Hi Forum,

I'm migrating DTS processes to DataStage jobs, but, in this DTS the customer use code to insert record in a log table, this log table contains all sql operations including the number of records affected, for example, after an insert, delete or update sentence. In sql server, this is possible but in DataStage??

Can i captured this results and use it ???

Thanks,

Hi Jseclen,
It is not possible the achieve this directly in Datastage. However you can use a hashed file and transformer to get the number of rows for each operation(insert, delete or update ) and then insert rocord in log table for each of them. You will have to do a little bit of programming in datastage for it.