Best Auditing Approach in version 8.5 onwards
Moderators: chulett, rschirm, roy
Best Auditing Approach in version 8.5 onwards
Hi,
We have a requirement to capture datastage job level stats for each and every job in a application. The rough audit table structure we have is
DSJobName
Source_Rec_Count
Reject_Rec_Count - will be combining all rejects in various transformations using funnel in single output
Target_Rec_Count
Job_start_time
job_end_time
Job_status - finished, aborted, finished with warnings
Batch_id- Unique identifier for particular run
Which is the best way to get this kind of information. Two options I can see are
1. Link counts by routines and calling this routine after each and every job
2. Operations DB - which is available on latest versions of DS
As I am not much aware of operations DB, any one can throw more light on this and whether all this required information above is available in it??
Thanks
Sachin
We have a requirement to capture datastage job level stats for each and every job in a application. The rough audit table structure we have is
DSJobName
Source_Rec_Count
Reject_Rec_Count - will be combining all rejects in various transformations using funnel in single output
Target_Rec_Count
Job_start_time
job_end_time
Job_status - finished, aborted, finished with warnings
Batch_id- Unique identifier for particular run
Which is the best way to get this kind of information. Two options I can see are
1. Link counts by routines and calling this routine after each and every job
2. Operations DB - which is available on latest versions of DS
As I am not much aware of operations DB, any one can throw more light on this and whether all this required information above is available in it??
Thanks
Sachin
Sachin C
There are many ways one can approach this. At my current site, we wrote our own logging mechanism which is triggered by the "Before-Job" subroutine call in each job in the project.
Another, simpler, way would be to upgrade to 9.x for the DataStage logging mechanism.
Alternatively, look into Kim Duke's "etlstats" using the search mechanism here.
Another, simpler, way would be to upgrade to 9.x for the DataStage logging mechanism.
Alternatively, look into Kim Duke's "etlstats" using the search mechanism here.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
You can do it in after job sub routine only. I think mentioning Before job subroutine was mistake unless before job subroutine triggers some monitoring scripts or so and then proceeds with next step. EtlStats is a good approach and you do not need to reinvent the wheels. And I think 9.1 will make this much easier.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.![Wink :wink:](./images/smilies/icon_wink.gif)
Genius may have its limitations, but stupidity is not thus handicapped.
![Wink :wink:](./images/smilies/icon_wink.gif)
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 94
- Joined: Wed May 08, 2002 8:44 am
- Location: Germany
- Contact:
Re: Best Auditing Approach in version 8.5 onwards
Hi Sachin,
we do this running DSJobReport (report type 2/XML) for every job as an after job subroutine. It delivers everything you mentioned exept a Batch_id, which is your own thing. Later we process these XMLs to be stored into a DB table for auditing.
While this works pretty fine you have to be aware that there are some differences in the logging of EE and server jobs. DS_JOBS in the Universe repository lets you trace the type of job.
Another thing you have to ba aware of is that inserted/rejected rows to databases are not reported properly, depending on the settings of your data access stage. DS mainly counts the link flow records. It will report the full amound of records send to a DB link - even if these rows get rejected...
I haven't seen the new features of version 9 regarding reporting so far. Will be interesting to see what IBM has implemented meanwhile...![Wink ;-)](./images/smilies/icon_wink.gif)
Best regards
Klaus
we do this running DSJobReport (report type 2/XML) for every job as an after job subroutine. It delivers everything you mentioned exept a Batch_id, which is your own thing. Later we process these XMLs to be stored into a DB table for auditing.
While this works pretty fine you have to be aware that there are some differences in the logging of EE and server jobs. DS_JOBS in the Universe repository lets you trace the type of job.
Another thing you have to ba aware of is that inserted/rejected rows to databases are not reported properly, depending on the settings of your data access stage. DS mainly counts the link flow records. It will report the full amound of records send to a DB link - even if these rows get rejected...
I haven't seen the new features of version 9 regarding reporting so far. Will be interesting to see what IBM has implemented meanwhile...
![Wink ;-)](./images/smilies/icon_wink.gif)
Best regards
Klaus