Growing Cognos (v11.1.7 FP8) Content Store

[BIview]

Hi All,

In one of our Cognos OTAP environments (v11.1.7 FP8) we are dealing with a growing tablespace of the Content Store (Oracle 19c). The actual used space in the database is growing on a daily basis which results in periodically extending datafiles if we don't 'clean up'. We even reached a tablespace of 460GB.
We figured out that many scheduled reports with a pdf output have stored months of daily pdf outputs (Content Store table CDMDATA), which you can't delete manually in Cognos, you get the message 'CM-REQ-4380 You cannot update or delete objects that have not been archived'. They are marked with an 'archivelock'.
The setting 'run history' and 'Report output versions' are both set on 1.
At the database side we see that the claimed space is not release after the 'clean up' update script, as described below.

We don't have any maintenance jobs scheduled,  like 'content removal', 'content archival' or 'consistency check'.
We also don't have an external archive to local filesystem set up.

The work around we have is periodically running an IBM 'clean up' update script : update CMARCHIVESTATUS set ARCHSTAT=0;
After this, to release the claimed space in the database we have to export and import the some tables.

Anyone with experience with this issue or any suggestion where to look for this?

Thanks in advance!

[dougp]

First:  No, I have no experience with this specific problem.  But in case it helps...

My Content Store is on SQL Server.  My environment may be smaller:  about 12,000 reports and 356 datasets.  My Content Store is about 122 GB.  So 460 GB seems reasonable for a large environment.

Have you tried setting "Display of report output versions" to a large number to see if that will allow you to delete the "versions"?  (This value appears to have no limit.  I just set a it to 10 million and Cognos didn't complain.)

Have you restarted the IBM Cognos service?

[cognostechie]

We figured out that many scheduled reports with a pdf output have stored months of daily pdf outputs (Content Store table CDMDATA), which you can't delete manually in Cognos, you get the message 'CM-REQ-4380 You cannot update or delete objects that have not been archived'. They are marked with an 'archivelock'.

Any table from the database can be deleted only after shutting down the Cognos service.

[BIview]

Thank you for your reactions

@dougp
I have set 'run history' and 'Report output versions' on various numbers, but stil I get that CM-REQ-4380 error. The only effect of this setting in the beginning is dat de output version sets which are displayed are or grayed out or blue/active.
By installation default it was set on 5 and 1.
Cognos services are multiple time stopped and restarted and also the servers are multiple time rebooted these past months.


@cognostechie
What do you suggest? deleting a Cognos Content Store table?
We had once in order to shrink the claimed tablespace of table CMDATA, exported the data and truncated it and imported the data back. It just effected the actuel size of the tablespace.

[cognostechie]

Yes, it will reduce the size because when a record is deleted from the table, it will not reduce the size because the space is still consumed but when the table is truncated then the table does not consume space anymore and after that when the records are imported again then only those records get imported which are valid (excluding the deleted ones). Deletion only marks a record to be deleted but it physically remains there. It also depends on the database you have. Different databases behave differently. This is something that the DBAs are supposed to do for periodic maintenance.

[dougp]

It seems like CognosTechie recommends copying the table, truncating the table, then inserting into the original table from the copy.  Is that correct?

Maybe that's a way to do this on Oracle.

On SQL Server, you could probably to a DBCC_SHRINKFILE() to reclaim the unused space in the database.  Oracle probably has a similar function.  That will cause nearly 100% index fragmentation, making your database super slow.  So following that up with defragmenting the indexes is important.

[cognostechie]

Well, before dropping a table or deletion/truncate, indexes are normally dropped and then created again after loading the data.

[BIview]

Hi all,

Shrinking the database,Oracle, in the way above mentioned will be done after finding the root cause of this issue.
I found out that all report outputs  in both public as personal folders are flagged as to be archived, but we don't have any archival maintenance task configured (maybe once in the past). Maybe some custom settings/config file are set to default after some Cognos patch.

The question is does anyone knows where I can find the settings for 'disabling' this archive function?

[bus_pass_man]

Interestingly, if you report to moderator often enough within a time period you can't submit the report.

[MFGF]

Interestingly, if you report to moderator often enough within a time period you can't submit the report.

I didn't see any open moderator reports when I logged in this morning. Are you having trouble reporting spam posts? If so, I'll let the Admins know.

MF.

[bus_pass_man]

Last week there was a Russian spam in this thread allegedly about a movie. There were other, similar, spams in other threads, which I used the report to moderator feature to flag to the moderator. I attempted to report this thread's spam but there apparently is a limit to the number you can do, so I wrote that.