DSXchange

Here is the SQL to get sequences. This is not 100% because this relies on the job dependencies in the job properties. SELECT NAME, CATEGORY, EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L', EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5...

Here is a second query which will get row counts by job category. You need to run the job which populates ETL_JOB. select LAST_RUN.JOB_NAME, LAST_RUN.LINK_NAME, LAST_RUN.START_TS, LAST_RUN.ELAPSED_TIME, LAST_RUN.ROW_COUNT, LAST_RUN.ROWS_PER_SEC from ETL_ROW_HIST LAST_RUN, ( select PROJECT_NAME, ...

If you downloaded EtlStats.zip then here is query to get the last run's rows per second. select LAST_RUN.PROJECT_NAME, LAST_RUN.JOB_NAME, LAST_RUN.LINK_NAME, LAST_RUN.START_TS, LAST_RUN.END_TS, LAST_RUN.ELAPSED_TIME, LAST_RUN.ELAPSED_SEC, LAST_RUN.ROW_COUNT, LAST_RUN.ROWS_PER_SEC from ETL_ROW_HIST L...

Please download EtlStats.zip from ADN or my tips page. There are several jobs which you can get rows per sec on one job or all jobs. The results are stored in several tables in the target data warehouse.

Sure it does. LOCATE is smart. If you LOCATE in Rec then uses @FM. LOCATE in Rec<2> will look for @VM as separators. LOCATE in Rec<2,3> will use @SVM as a separator. The learning curve for these little details is steep. Ken, Ray and others have used Universe and other similar databases for years. Mo...

If it does not make a difference then leave them commented out. The SUBR() subroutine sometimes needs these varaibles set. I did it out of habit. Currently this routine selects the whole log file. You need to select the records where @ID is greater than or equal to '//JOB.STARTED.NO'. Add this line:...

After you attach to a job then you need to check the status. Here is the code you need to add. LastRunStatus = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If LastRunStatus = DSJS.RUNFAILED Or LastRunStatus = DSJS.CRASHED Or LastRunStatus = DSJS.STOPPED Then Call DSLogInfo(JobReportName, "Res...

I think it is valid to do some of this in PLSQL if that speeds things up. I think doing it in a BEFORE SQL may make it look like it is all done in DataStage. I think most of us have used temp tables created with SQL to get around some performance issue. Maybe even Ray has used this trick. I do not l...

There are some dictionary items which require @ID, @RECORD to be set. You could probably do this better in SQL. The last run has an id greater or equal to the one stored in '//JOB.STARTED.NO'. So you can modify this routine or here is some SQL. select TIMESTAMP, SEVERITY, FULL.TEXT from RT_LOG111 wh...

You might be able to fix one job and then copy it. After you copy it then change the job name. I think it will remember the parameter settings. Try it and let us know.

I basically do this in my EtlStats driver job. You feed the sequence name to the driver. It looks up the dependent jobs in that sequence. It feeds these to the job that gets row counts for each job in that sequence. The job name is also the instance id. In this scenario it does make sense otherwise ...

You could load this into a hash file and look it up instead of doing this on every row.

Use the UNIX tail command to strip off the trailer record first.

I have seen a strikingly similar routine at this post I checked this out and Peter Lai wrote these routines. If Ascential is giving out routines written by Peter then they should give him credit. I am not sure what you are looking at but the parameter name is stored in STAGECOM.JOB.CONFIG<CONTAINER...

You need to do a search. These have been covered a lot. You cannot use DSJ.ME. You need to use DSAttach() and get a job handle for each job be fore you can get info on it.