WCS Scheduler Queries

-- IBM documentation on WCS Scheduler data model
-- http://pic.dhe.ibm.com/infocenter/wchelp/v7r0m0/topic/com.ibm.commerce.data.doc/refs/rdmjobs.htm


-- Validate if a job is in active status, replace sccpathinfo with the job name
select * from schconfig where
sccpathinfo like '%DynaCacheInvalidation%'
and sccactive='A';

-- Get the status of a scheduler job, replace sccpathinfo with the job name
select * from schstatus where
scsjobnbr in
(select scsjobnbr from schconfig where
sccpathinfo like '%DynaCacheInvalidation%'
and sccactive='A')
order by scsprefstart desc;

-- Get the status of a scheduler job within a time range, replace sccpathinfo with the job name
select * from schstatus where
scsjobnbr in
(select scsjobnbr from schconfig where
sccpathinfo like '%DynaCacheInvalidation%'
and sccactive='A')
and scsprefstart between to_timestamp('02.01.14 08:00:00.000000','MM.DD.YY HH24:MI:SS:FF')
and to_timestamp('02.01.14 09:00:00.000000','MM.DD.YY HH24:MI:SS:FF')
order by scsprefstart desc;

-- Get the status of WCS broadcast within a time range, replace sccpathinfo with the job name
select * from schbrdcst where
jobrefnum in
(select sccjobrefnum from schconfig where
sccpathinfo like '%SaveMarketingStatistics%'
and sccactive='A')
-- comment out this if you don't want to query by date range
and endtime between to_timestamp('02.01.14 08:00:00.000000','MM.DD.YY HH24:MI:SS:FF')
and to_timestamp('02.01.14 09:00:00.000000','MM.DD.YY HH24:MI:SS:FF')
-- Query only successful jobs, to query failed jobs change result='F'
and result='S'
order by endtime desc;