get row count

bobbysridhar · Post by **bobbysridhar** » Tue Jul 17, 2012 10:12 am

Job start time=2012-07-16 10:47:28
Job end time=2012-07-16 10:47:34
Job elapsed time=00:00:06
Job status=2 (Finished with warnings)
Stage: abc_OCI, rows input
Stage start time=2012-07-16 10:47:31, end time=2012-07-16 10:47:33, elapsed=00:00:02
Link: DSLink267, 14 rows
Stage: Transformer_269, 14 rows input
Stage start time=2012-07-16 10:47:31, end time=2012-07-16 10:47:33, elapsed=00:00:02
Link: DSLink267, 14 rows
Link: DSLink271, 14 rows
Stage: Sequential_File_264, 14 rows input
Stage start time=2012-07-16 10:47:31, end time=2012-07-16 10:47:33, elapsed=00:00:02
Link: DSLink271, 14 rows

chulett · Post by **chulett** » Tue Jul 17, 2012 10:17 am

OK... getting some of the values will be pretty simple, like start time and end time for example. However, with no naming conventions that I can see for the stages and especially for the links, how will you recognize which are which? Until you start naming things consistently (like all reject links start with "rej_" as one small example) you'll never be able to consistently get the information you need from these reports.

bobbysridhar · Post by **bobbysridhar** » Tue Jul 17, 2012 12:05 pm

naming i will do it which is not a problem.
how can i get the desired results after naming convention.

RAJARP · Post by **RAJARP** » Tue Jul 17, 2012 12:59 pm

Hi Sreedhar,

the after job routine in the below link will serve your purpose(a post by marpadga18)

viewtopic.php?t=138651

Regards,
Raja R P

chulett · Post by **chulett** » Tue Jul 17, 2012 10:37 pm

Yikes. That routine looks... overly complex and requires a ton of specific parameters be added to each job.

Sridhar, I'm concerned you've chosen a route that you say you are not proficient in, that being UNIX shell scripting. That's what you would need to if you wanted to break a "Job Report" down from the command line. Do you work with anyone that could help you with it? At a high level, you would need to establish variables to hold each audit table column value and then extract / grep that information from the file. For example, you could execute a grep command looking for the "Job start time" string and it would return "Job start time=2012-07-16 10:47:28" which you could then cut to get the second field in the equal sign delimited string so that you end up with "2012-07-16 10:47:28". Off the top of my head...

Code: Select all

JobStartTime=`grep 'Job start time' YourFilename | cut -f 2 -d "="`

Lather, rinse, repeat for all of the other elements you need and then echo them to your desired output filename concatenated together with your choice of field delimiter. Once you are done a simple job could source from the resulting flat file and load your Audit table.

I would imagine you could also do something similar in a DataStage job or even a BASIC routine. Stage variables could hold each component, you could read each line of the Job Report as one big string and then use functions to do the same steps as noted above... Index() could determine what element a record held and the Field() function to cut out just the part you want.

Multiple ways to skin this cat, chose whatever route you and yours are most comfortable with maintaining / supporting.

kduke · Post by **kduke** » Fri Jul 20, 2012 8:39 pm

I totally agree with Craig and Vincent. Never do this within a job. Always do it after a job finishes. Why build something new when I give you what you want. You can easily modify my job to write to any audit table. My jobs will get row counts for every job in a project as long as you don't reset the job or compile it. There even is a job to loop through the log files and retrieve old row counts. There are about 40 jobs to help extract all kinds of simple metadata like job names, folder names, stage names and link names. These jobs are just examples. Take them and make them your own. If you improve on them then share your version. The jobs have been discusses lots of times. They are real old versions. Maybe I need to post a scaled down set of new jobs. They are all server jobs which could easily be rewritten into PX jobs. They are just to get you started. Hopefully I can post cleaned up versions soon. I haven't updated my web site in years. Sorry.