EtlStats question

Peter · Post by **Peter** » Tue Dec 19, 2006 5:46 am

Hi all,

I have downloaded the EtlStats package from Kduke and I have the following questions:

- What is the easiest way to use DSJobReportDbDriver to onyl insert statistic information into the DB (without generating the HTML report)

- How can I achive to create statisticts for multiple instance sequences / jobs.

Thanks for any help.

Regards
Peter

kduke · Post by **kduke** » Tue Dec 19, 2006 7:53 am

You could modify DSJobReportDbDriver and strip out what you do not want. I would rename it so you can upgrade to the next version of EtlStats. You do not have to call DSJobReportDbDriver at all if you want. You can run DSJobReportDb directly. DSJobReportDbDriver was created so at the end of a sequence you could call DSJobReportDbDriver and it would run DSJobReportDb for every job in that sequence.

The only way to accurately get the instance ids is to process the log of the sequence. There is a job included with EtlStats called EtlGetRowCountsFromLog which could help. You really need to get the instance ids from the sequence. This is not what EtlGetRowCountsFromLog does but it should help anyway.

Peter · Post by **Peter** » Tue Dec 19, 2006 10:50 am

Thx Kduke for the quick reply.

The job 'EtlGetRowCountsFromLog' has the following info in the annotation:

Targets: (hash files)
ETL_JOB_HIST
ETL_ROW_HIST
ETL_PARAM_HIST

How should I create this files? Just with CREATE.FILE <name> <type>? Currently the job fails with the message:
Error: Unable to open ETL_JOB_HIST

Thanks for your help.

Peter

Peter · Post by **Peter** » Tue Dec 19, 2006 10:54 am

>You can run DSJobReportDb directly.

I have done that now and as jobname I've used the <jobname>.<invocationID> as paramter and it works

ray.wurlod · Post by **ray.wurlod** » Tue Dec 19, 2006 2:13 pm

Can you please mark the thread as resolved?

kduke · Post by **kduke** » Tue Dec 19, 2006 8:05 pm

ETL_JOB_HIST
ETL_ROW_HIST
ETL_PARAM_HIST

Are jobs updated by the job you ran. The DDL to create all the tables updated in EtlStats are in the zip file in a folder called DdlScripts.

Peter · Post by **Peter** » Wed Dec 20, 2006 2:51 am

ray.wurlod wrote:Can you please mark the thread as resolved?

Not every question is answered yet, do you meen I should open a new thread?

kduke wrote:ETL_JOB_HIST
ETL_ROW_HIST
ETL_PARAM_HIST

Are jobs updated by the job you ran. The DDL to create all the tables updated in EtlStats are in the zip file in a folder called DdlScripts.

The ddls are for Oracle and SqlServer (and these tables are created and it works with DsJobReportDB).
The job EtlGetRowCountsFromLog seems not to use these tables (but expects the same names as hashed files?).

The job EtlGetRowCountsFromLog has the following code job control:

Code: Select all

      open 'DS_JOBS' to DsJobs else
         ErrMsg = "Error: Unable to open DS_JOBS"
         gosub ErrRtn
         goto TheEnd
      end

Because this job has no job paramters for SID, UID, PW I have assumed that the 'open' command is related to a hashed file!?

kduke · Post by **kduke** » Wed Dec 20, 2006 7:10 am

I am not sure why Ray thought you were done asking questions.

Yes the open is for hashed files. I will have to look at the job in question to see what it does.

kduke · Post by **kduke** » Wed Dec 20, 2006 7:20 am

You got it correct. You need to write a job which moves the tables above to load them into hashed files of the same name. You need to create these hashed files somehow before running this job. If you want you can write a job to pass these from the hashed files into the real tables if you need them there.

These jobs are examples on how to do things. You need move this data to whatever you want. The format of the hashed files is in the BASIC code in this job.

Peter · Post by **Peter** » Wed Dec 20, 2006 8:50 am

Thx Kduke, now the topic is resolved for me.

prasannak · Post by **prasannak** » Tue Apr 22, 2008 9:12 am

Hi,

Suppose I do not want to install the whole package of etlstats to my project, would i be still able to leverage just the dsjobreportDB features of inserting stats into the table...?
I am interested in the stats that are in etl_job_hist (start,end times, status etc)...

I guess what i am trying to get at is:
I want to use dsjobreportdb that inserts individual job/activity stats into the target metadata table etl_job_hist.
Do I need any other supporting jobs,routines from etlstats to make the dsjobreportDB to compile and execute successfully?

I stripped the etl_row_hist and etl_param_hist portion of the design from this job as i do not require them for my implementation...

prasannak · Post by **prasannak** » Tue Apr 22, 2008 9:17 am

I took the usage_analysis for dsjobreportDB and found no dependancies with other etlstats jobs/sequences...
So, i am presuming this is an independant job...

But, what i am a bit confused is the reasoning behind having this as a before job subroutine...
I am interested in getting the start,end time and status stats at each activity of a sequence...
Do i need to use dsjobreportDbDriver for this?

kduke · Post by **kduke** » Tue Apr 22, 2008 10:01 pm

The driver can figure out all the jobs in a sequence and feed them into job to get row counts one at a time. There is a sequence included as an example on calling the driver job. It also emails row counts as a report.

prasannak · Post by **prasannak** » Wed Apr 23, 2008 8:58 am

Suppose I do not want to install the whole package of etlstats to my project, would i be still able to leverage just the dsjobreportDB features of inserting stats into the table...?
I am interested in the stats that are in etl_job_hist (start,end times, status etc)...

I guess what i am trying to get at is:
I want to use dsjobreportdb that inserts individual job/activity stats into the target metadata table etl_job_hist.
Do I need any other supporting jobs,routines from etlstats to make the dsjobreportDB to compile and execute successfully?

I stripped the etl_row_hist and etl_param_hist portion of the design from this job as i do not require them for my implementation...

prasannak · Post by **prasannak** » Wed Apr 23, 2008 3:40 pm

Hi ,

I had a some questions on etlstats:
I had imported the dsx file.
1) The install.txt specifies creating files in TCL prompt. Are these directories created under unix etl server?
Our environment is usually constrained to allow creating additional folders since a project is allocated at a minimum a staging and log directory in the server and
no other directories are permitted. So, is it ok to re-use the existing directories as mandated so that I do not have to create these at all?
I am hoping this will be a limitation on etlstats.
2) What is the difference between DSJobReportDbDriver and DSJobReportDbDriverNoWait?
It has been mentioned in install.txt that sequences SeqJobStatus or SeqPrepareEtlStats are examples of how to use DSJobReportDbDriver...But the sequences themselves
refer to a job called DSJobReportDbDriverNoWait...Please clarify...
3) What is the job that should be used to track individual job activities of a sequence ? DSJobReportDbDriver or DSJobReportDb ?
4) I am also interested to track the overall sequence start and end times as well in addition to the individual job activities of a sequence which etl_job_hist accounts for.
How do I achieve this?
5) Suppose I am not interested in reporting off tables like ETL_ROW_HIST(my requirements do not call for it at the moment), do I still need to use all the jobs under ETL_ROW_COUNTS category?
What other categories are must haves for me to achieve the population of etl_job_hist ? To begin with, we would be maintaining etl_job as a static data.

Would highly appreciate if answers can be provided for all the questions as I hit a road block on some of these while trying to play with etlstats...