I am creating a job that take data from sequential file, pass it through a transformer and puts it into an Informix database.
The transformer also contains a link to a sequential file that captures all the rejected records.
I need to create a sequential file that gets the job name, start time of job, end time of job, # of total records from the sequential file and # of records inputted into the database successfully. I need to capture the job statistics for about 100 similar jobs. Therefore, I need something generic that I can reuse for all jobs.
Is there a built-in component I can reuse? What is the best strategy to do this?
(Similar question to my last posting)
Thanks!!!
Getting/Creating Job Statistics
Moderators: chulett, rschirm, roy
You need EtlStats.zip. It is posted on my web site or ADN. It is version 7 of DataStage. If you have an older version then it is real simple. It runs dsjob -report project job XML. I am not sure this command will work with older versions of Ds. Anyway it will push the results into Oracle. All the create table scripts are included and the dsx files are included. You could easily switch the the tables to Informix or whatever. I just think these tables should be a part of the target warehouse.
I should be updating these soon because I wrote a few more jobs to get the category of a job and some more useful information. We run our row stats by category because each category represents a target table. In development you can select only your jobs assuming they are all in one category.
The main job GetEtlQaStatDriver get start, end times and row counts one job, one sequence, one category or all jobs. We run this at the end of each sequence so we have a run time history.
This all a part of what my article was about in the newsletter. We all need metadata about our ETL processes for historical reference and to help write better ETL.
I should be updating these soon because I wrote a few more jobs to get the category of a job and some more useful information. We run our row stats by category because each category represents a target table. In development you can select only your jobs assuming they are all in one category.
The main job GetEtlQaStatDriver get start, end times and row counts one job, one sequence, one category or all jobs. We run this at the end of each sequence so we have a run time history.
This all a part of what my article was about in the newsletter. We all need metadata about our ETL processes for historical reference and to help write better ETL.
Mamu Kim
-
Amos.Rosmarin
- Premium Member
- Posts: 385
- Joined: Tue Oct 07, 2003 4:55 am
Hi
There some ways of doing this,
My advise is to use an after job routine that uses the basic job control routines such as DSGetJobInfo
If you are using a specific naming convention it is easy to do it, for example - if you always call the link that goes to the reject file L_REJECT it's easy to get the number of rows passed through this link by using DSGetLinkInfo with DSJ.LINKROWCOUNT.
Try using DSMakeJobReport
Amos
There some ways of doing this,
My advise is to use an after job routine that uses the basic job control routines such as DSGetJobInfo
If you are using a specific naming convention it is easy to do it, for example - if you always call the link that goes to the reject file L_REJECT it's easy to get the number of rows passed through this link by using DSGetLinkInfo with DSJ.LINKROWCOUNT.
Try using DSMakeJobReport
Amos
Oh by the way a separate process is available in DwNav which will download the same information into an Access database to merge with the data in Reporting Assistant. This runs on version 5 and above. You can download DwNav from my web site as well. It is not available on ADN. DwNav includes row history and run times in the html documentation it can produce on any or all the jobs.
Mamu Kim