Extracting Siebel ETL timings

For a long time, I relied on my siebel admins to get Siebel ETL timings and often struggled to get certain information. Last week, I realized why bother when all the info are available on DB2 tables. This sql gives you the timing which can be imported into an excel spreadsheet to make pretty graphs and trend analysis :

with temp1 (etl_date,start_time,end_time,success_rows,failed_rows) as
(
select date(start_ts)as “ETL_DATE” ,min(start_ts) as “START_TIME” ,max(end_ts) as “END_TIME”,
sum(sucess_rows) as “ROWS_SUCCEFULLY_PROCESSED”,sum(failed_rows) as “ROWS_FAILED”
from siebdac.W_ETL_RUN_STEP
group by date(start_ts),run_wid order by min(start_ts))

 
select etl_date,start_time,end_time,success_rows,failed_rows,timestampdiff(4,char(timestamp(end_time) – timestamp(start_time)))as “Duration_in_minutes” ,
((success_rows + failed_rows)/timestampdiff(2,char(timestamp(end_time) – timestamp(start_time)))) as “Rows_processed_per_second”
from temp1;