IBM Support

IBM Cognos BI Schedules Take a Long Time to Complete

Troubleshooting


Problem

IBM Cognos BI Schedules become progressively slower to complete and the slow down cannot be associated with slow database runtimes or running at or near capacity.

Symptom

IBM Cognos BI Schedules that used to take a certain time to complete, now take three or four times as much time to complete.

Cause

At a high level, the IBM Cognos BI content store uses certain NC tables within the content store as a working queue when executing schedules. If schedules fail or require a rerun, these queue tables fill up with a number of rows. As these the number of rows increase, internal schedule tasks such as updating the history and schedule status take longer and longer to complete. This results in a significant increase in the amount of time that is required to complete the schedule.

Diagnosing The Problem

It is important to differentiate this issue from other environmental factors that may affect an IBM Cognos BI system. The easiest way to determine if a large NC queue is the cause of the IBM Cognos BI Schedule slow down, the DBA could check the record count on the following tables within the content store database.

NC_TASK_ANCESTOR_STOREIDS

NC_TASK_HISTORY_DETAIL

NC_TASK_QUEUE

NC_TSE_STATE_MAP

NC_TASK_PROPERTY

If the record count comes back at more than 1000 records, then the Resolution section of this technote may apply.

Resolving The Problem

Ensure a valid and current backup has been taken of the IBM Cognos content store using a trusted and proven method. Once a valid backup is in place the following SQL scripts can be executed by a DBA against an active IBM Cognos notification store database. There is no requirement to stop the IBM Cognos BI services.


Microsoft SQL Server Script:

begin transaction;
create table #tempnc (TASK_ID CHAR(45) PRIMARY KEY);
insert into #tempnc (TASK_ID) SELECT TOP <NUMBER_RECORDS_TOBE_REMOVED> t1.TASK_ID from <dbo>.NC_TSE_STATE_MAP as t1, <dbo>.NC_TASK_QUEUE as t2 where t1.TASK_ID = t2.TASK_ID and t1.STATUS >= 20 and ((t2.DATE_ENTERED + 86400000) <= (SELECT MAX(DATE_ENTERED) FROM <dbo>.NC_TASK_QUEUE));

delete from <dbo>.NC_TASK_ANCESTOR_STOREIDS where TASK_ID IN (select TASK_ID from #tempnc);
delete from <dbo>.NC_TASK_HISTORY_DETAIL where TASK_ID IN (select TASK_ID from #tempnc);
delete from <dbo>.NC_TASK_QUEUE where TASK_ID IN (select TASK_ID from #tempnc);
delete from <dbo>.NC_TSE_STATE_MAP where TASK_ID IN (select TASK_ID from #tempnc);
delete from <dbo>.NC_TASK_PROPERTY where TASK_ID IN (select TASK_ID from #tempnc);

drop table #tempnc;
commit transaction;

IBM DB2 Script:
create global temporary table tempnc (TASK_ID CHAR(45) NOT NULL);
insert into tempnc (TASK_ID) SELECT t1.TASK_ID from <schema>.NC_TSE_STATE_MAP as t1, <schema>.NC_TASK_QUEUE as t2 where t1.TASK_ID = t2.TASK_ID and t1.STATUS >= 20 and ((t2.DATE_ENTERED + 86400000) <= (SELECT MAX(DATE_ENTERED) FROM <schema>.NC_TASK_QUEUE)) FETCH FIRST <NUMBER_RECORDS_TOBE_REMOVED> ROWS ONLY;

delete from <schema>.NC_TASK_ANCESTOR_STOREIDS where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TASK_HISTORY_DETAIL where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TASK_QUEUE where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TSE_STATE_MAP where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TASK_PROPERTY where TASK_ID IN (select TASK_ID from tempnc);

drop table tempnc;
commit;

Oracle Script:

create global temporary table tempnc (TASK_ID CHAR(45) NOT NULL);
insert into tempnc (TASK_ID) select t1.TASK_ID from <schema>.NC_TSE_STATE_MAP t1, <schema>.NC_TASK_QUEUE t2 where t1.TASK_ID = t2.TASK_ID and t1.STATUS >= 20 and ((t2.DATE_ENTERED + 86400000) <= (select MAX(DATE_ENTERED) from <schema>.NC_TASK_QUEUE)) and ROWNUM < <NUMBER_RECORDS_TOBE_REMOVED>;

delete from <schema>.NC_TASK_ANCESTOR_STOREIDS where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TASK_HISTORY_DETAIL where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TASK_QUEUE where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TSE_STATE_MAP where TASK_ID IN (select TASK_ID from tempnc);
delete from <schema>.NC_TASK_PROPERTY where TASK_ID IN (select TASK_ID from tempnc);

drop table tempnc purge;
commit;


<dbo>=Microsoft SQL Server Table owner
<NUMBER_RECORDS_TOBE_REMOVED> = Place holder for the number of records to be removed. Initial recommended value is 1000.
<schema>=Placeholder for IBM DB2 or Oracle schema

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2;10.1.1;10.1;8.4.1","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21637944