Reports for row counts using EtlStats.zip

[kduke]

If you downloaded EtlStats.zip then here is query to get the last run's rows per second.

Code: Select all

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 LAST_RUN,
   (
      select 
         PROJECT_NAME,
         JOB_NAME,
         LINK_NAME,
         max(START_TS) as MAX_START_TS
      from 
         ETL_ROW_HIST
      group by
         PROJECT_NAME,
         JOB_NAME,
         LINK_NAME
   ) ROWS_MAX
where
   LAST_RUN.PROJECT_NAME = ROWS_MAX.PROJECT_NAME and
   LAST_RUN.JOB_NAME = ROWS_MAX.JOB_NAME and
   LAST_RUN.LINK_NAME = ROWS_MAX.LINK_NAME and
   LAST_RUN.START_TS = ROWS_MAX.MAX_START_TS
order by
   LAST_RUN.PROJECT_NAME,
   LAST_RUN.JOB_NAME,
   LAST_RUN.LINK_NAME,
   LAST_RUN.START_TS
;

[kduke]

Here is a second query which will get row counts by job category. You need to run the job which populates ETL_JOB.

Code: Select all

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,
         JOB_NAME,
         LINK_NAME,
         max(START_TS) as MAX_START_TS
      from 
         ETL_ROW_HIST
      group by
         PROJECT_NAME,
         JOB_NAME,
         LINK_NAME
   ) ROWS_MAX,
  ETL_JOB
where
   ETL_JOB.JOB_CATEGORY = 'xxx' and
   LAST_RUN.PROJECT_NAME = ETL_JOB.PROJECT_NAME and
   LAST_RUN.JOB_NAME = ETL_JOB.JOB_NAME and
   LAST_RUN.PROJECT_NAME = ROWS_MAX.PROJECT_NAME and
   LAST_RUN.JOB_NAME = ROWS_MAX.JOB_NAME and
   LAST_RUN.LINK_NAME = ROWS_MAX.LINK_NAME and
   LAST_RUN.START_TS = ROWS_MAX.MAX_START_TS
order by
   ETL_JOB.JOB_CATEGORY,
   LAST_RUN.JOB_NAME,
   LAST_RUN.LINK_NAME,
   LAST_RUN.START_TS
;

[kduke]

Here is oen more. If you have populated ETL_JOB then it may have a bug in the KgdIsJobSequence routine. In the last few lines it sets Ans = ErrMsg. Comment this out. Compile the routine and job and rerun.

Here is the SQL for listing how long each sequence ran on its last run.

Code: Select all

select 
   LAST_RUN.JOB_NAME,
   LAST_RUN.START_TS,
   max(LAST_RUN.ELAPSED_TIME) as ELAPSED_TIME
from 
   ETL_JOB_HIST LAST_RUN,
   (
      select 
         PROJECT_NAME,
         JOB_NAME,
         max(START_TS) as MAX_START_TS
      from 
         ETL_JOB_HIST
      group by
         PROJECT_NAME,
         JOB_NAME
   ) MAX_RUN,
  ETL_JOB
where
   ETL_JOB.SEQ_IND = 'Y' and
   LAST_RUN.START_TS > '2004-12-06' and
   LAST_RUN.PROJECT_NAME = ETL_JOB.PROJECT_NAME and
   LAST_RUN.JOB_NAME = ETL_JOB.JOB_NAME and
   LAST_RUN.PROJECT_NAME = MAX_RUN.PROJECT_NAME and
   LAST_RUN.JOB_NAME = MAX_RUN.JOB_NAME and
   LAST_RUN.START_TS = MAX_RUN.MAX_START_TS
group by
   LAST_RUN.JOB_NAME,
   LAST_RUN.START_TS
order by
   LAST_RUN.JOB_NAME,
   LAST_RUN.START_TS
;