ETL Job rowcount and other details

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

ETL Job rowcount and other details

Post by Vrisha »

I have a requirement that once the job got finished, it should populate the
JOB_NAME,
PROCESS_EXECUTION_ID,
PROCESS_START_DATE,
PROCESS_END_DATE,
PROCESS_DURATION,
ROWCOUNT_EXTRACTED,
ROWCOUNT_INSERTED,
ROWCOUNT_UPDATED,
ROWCOUNT_ERROR into a table called D_ETL_AUDIT_JOB_LOG.

Say for example, if I have 10 jobs after each job got finished , there should be an entry in D_ETL_AUDIT_JOB_LOG.

Do I need to create another parallel job to do this or should I use the sequencer to do this. Please let me know.

Thanks.
Suja
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

Why reinvent a redundant wheel? Everything you need is already available in DSODB.

Mike
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks for your reply, Mike. As you said, I went through this website 'https://www-304.ibm.com/support/docview ... wg21509567' and I checked the file 'D:\IBM\InformationServer\Server\DSODB\logs\AppWatcher_Cmd.

AppWatcher_Cmd has the contents like below

2017/08/03 21:10:15: DSODB is currently turned off. AppWatcher will not run.
2017/09/07 15:47:45: AppWatcher start requested.
2017/09/07 15:47:45: DSODB is currently turned off. AppWatcher will not run.
2017/09/07 21:20:10: AppWatcher start requested.
2017/09/07 21:20:10: DSODB is currently turned off. AppWatcher will not run.

Last it ran on 2017/09/07. Am I looking at the correct place? Please let me know. Thanks.
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I haven't been through all of the documentation (thanks Paul) but perhaps something here might help.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks, Craig. I will look into it.
Suja
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

Work with your DataStage admin team to see if the DSODB database is configured for your environment. It's not a mandatory item. But it is super nice to have.
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Hi Craig / Mike/ Paul,

I went through the documentation, it was very useful. But in our environment they didn't build/configured the DSODB database and not ready to do so.

Is there any other way to get the information without DSODB.

Thanks.
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sure but it's messy and painful... which is the whole reason that the DSODB was added to the product.

You can leverage the API for this, either from BASIC routines or from the command line depending on where your skillset lies, equivalent functions exist in both. For BASIC routines, search the documentation for the DSGet* functions. From the command line, leverage dsjob and the equivalent functions there. I would suggest the output be simply a flat file which, once fully populated, could be easily loaded into your table. This rather than each iteration trying to push records directly there. My two cents.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks, Craig. I will try the methods suggested by you and keep you posted.
Suja
Post Reply