Capturing Log Information to a table

vskr72 · Post by **vskr72** » Wed Nov 23, 2011 10:39 pm

Hello,

I need to copy the following stats of each ETL process into a DB table:

No. of  records read from Src
No. of  records failed to read from Src
No. of records to tgt
Records rejected by Tgt
Start_Time
End_Time

THanks,

Kumar

ray.wurlod · Post by **ray.wurlod** » Wed Nov 23, 2011 11:19 pm

Download ETLstats for free from Kim Duke's web site and avoid re-inventing the wheel.

chulett · Post by **chulett** » Thu Nov 24, 2011 8:54 am

It's not a small wheel.

Even if you don't use it directly, ETLStats will give you ideas on how you can 'roll your own' and the APIs and techniques you can use to satisfy your requirement. As with most things, there are multiple ways to approach this and it's not really a subject that fits well into a post on a web board.

A couple of things to think about...

* When do you want to do this collection - individually after each job runs? Collectively after an entire 'batch' runs?
* What approach are you most comfortable with developing and supporting - via the APIs and job control (BASIC) code? From the command line using dsjob in shell scripts?
* Where will you get this information from - the job's log? From the job itself?

Biggest point in my mind is to lay the whole thing out, plan out the exact how of this from beginning to end before you jump in and start coding away.

Also don't think you need to use PX for any of this, Server jobs are perfect for something like this. IMHO

vskr72 · Post by **vskr72** » Thu Nov 24, 2011 9:13 am

I understand that that ETLStats is not free. There is a trial version. Not sure if my company would be willing to buy it.

We were thinking that we will do it as a part of the each load. Not sure if I can achieve this using datastage PX. When we were brainstorming, some one suggested that I use some shell scripts to read the log files. Need to explore that option. It it really possible to get these details from the job itself?

chulett · Post by **chulett** » Thu Nov 24, 2011 9:34 am

vskr72 wrote:I understand that that ETLStats is not free.

Your understanding is wrong. Completely free, thanks to the generosity of Kim Duke.

vskr72 also wrote:Is it really possible to get these details from the job itself?

Yes, use the DSGet* functions such as DSGetJobInfo() and DSGetLinkInfo() for that. There are dsjob equivalents for all of those functions as well, so you could do this either from your own job control code or shell scripts.