Finding Schedules in Cognos That Run Too Frequently


Sometimes, a task seems too easy to bother with reading the manual.  We’ve all done it.  The steps appear to be obvious, so why look for guidance?  In our organization, one of those tasks is scheduling Cognos reports.  We provide training documentation and even videos on how to do this, but Cognos does make it reasonably simple.

And yet, we have a handful of users who don’t understand the By Day tab or the Daily Frequency sections of the schedule, so we occasionally end up with reports that are run every hour or even every minute.  Of course this creates an unnecessary load on Cognos and on the reporting databases.

When listing schedules, Cognos does not provide filters for  frequency or daily frequency (recurrence), so finding these wasteful schedules can be tedious.  The solution, as it often does, lies in querying the content store.  The following SQL will list any active schedules that run more than once a day, or that have a recurrence (every X minutes or hours) on the days it runs.  It’s in Oracle SQL, but should be simple enough to convert to your own content store’s SQL.

SELECT
       N2.NAME AS Owner_Name,
       N.NAME AS Schedule_Name,
       O.Created ,
       O.Modified ,
       OP2.Active ,
       OP2.StartDate ,
       OP2.EndDate ,
       DECODE ( OP2.TYPE,
               1, ‘By Month (Day of Month)’,
               2, ‘By Month(Day of Week Instance)’,
               4, ‘By Week’,
               5, ‘By Year(Day of Month)’,
               6, ‘By Year(Day of Week of Month Instance)’,
               7, ‘By Trigger’,
               9, ‘By Day Recurring’,
               10, ‘By Month (Day of Month) Recurring’,
               11, ‘By Week Recurring’,
               12, ‘By Month(Day of Week Instance) Recurring’,
               13, ‘By Year(Day of Month) Recurring’,
               14, ‘By Year(Day of Week of Month Instance) Recurring’
          ) AS Schedule_Type ,
       OP2.EveryNPeriods ,
       CASE
          WHEN OP2.TYPE IN ( 0, 9) AND OP2.DailyPeriod = 0 THEN ‘Every Day’
          WHEN OP2.TYPE IN ( 0, 9) AND OP2.DailyPeriod = 1 THEN ‘Every Hour’
          WHEN OP2.TYPE IN ( 0, 9) AND OP2.DailyPeriod = 2 THEN ‘Every Minute’
          ELSE NULL
       END AS DailySchedule,
       CASE
          WHEN OP2.TYPE IN ( 9,
                            11,
                            12,
                            14)
                THEN OP2. IntraRecurInterval
          ELSE NULL
       END AS Recurrence
  FROM CMOBJECTS O
       INNER JOIN CMOBJECTS PO1 ON O.PCMID = PO1.CMID
       INNER JOIN CMOBJNAMES N ON PO1.CMID = N.CMID
       INNER JOIN CMOBJPROPS2 OP2 ON O.CMID = OP2.CMID
       INNER JOIN CMREFNOORD2 REF ON O.CMID = REF.CMID
       INNER JOIN CMOBJNAMES N2 ON REF.REFCMID = N2.CMID
 WHERE    
       N2.Name LIKE ‘%yournamespace%’  — Something that identifies the organization’s namespace
       AND O.ClassId = 39 — Schedules only
       AND OP2.Active <> — Ignore disabled schedules
       AND NVL ( OP2.EndDate, SYSDATE) >= SYSDATE — Ignore schedules that have expired
       AND
       (  
            (
                    OP2.TYPE = 0  — Daily Schedule
                    AND
                    NVL ( OP2.DailyPeriod , 0) <> 0 — Frequency is not per day (is per hour or minute)
            — Only schedules that run more than once a day
            OR
            (
                    OP2.TYPE IN ( 9,10 ,11, 12,13 ,14) — Any schedule with a recurrence (minute or hour)
            — Or schedules that recur on the day they are run
        );

 

One thing to look out for:  In order to control what time the schedule runs, some of our users set an hourly occurrence, but only within a specific hour of the day.  The technique is effective, and they gave this some thought , so I don’t want to discourage them.

To make this work, I had to determine the possible values for CMOBJPROPS2.TYPE (see the DECODE statement).  I think I found them all but there are gaps in the values (3 & 8).  They may be obsolete, or just values that require a combination of frequency parameters that I couldn’t guess.  If you figure out what those values represent, please let me know.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: