Hello - I have been researching why my content manager database is so large (It's around 30 GB). After much searching and talking with support, I finally found out what is taking up most of the space. Every time you save a report output, Cognos saves a copy of the report spec that was run in CMOBJPROPS7. In our case, we have a few reports with large specs that return a pretty small result set. We run about 300 outputs of these reports per day and need to keep them for 1/2 year. The outputs when output to the file system only total about .8 GB, yet with the extra copies of the spec, it takes up 10.9 GB in the content manager database.
This behavior ends up accounting for more than 60% (17 GB) of our total content manager db, and from what I can tell, they serve no purpose.
Has anyone else seen this behavior? Does anyone know of any purpose for all the duplicate copies of the same spec?
Here's a simple query that will return how much of your content store is copies of report specs associated with outputs (written for sql 2005):
SELECT sum(CAST(DATALENGTH(SPEC)AS NUMERIC(15,5))/1024/1024 ) AS MB FROM CMOBJPROPS7 A with (nolock) INNER JOIN CMOBJNAMES B with (nolock) ON A.CMID = B.CMID INNER JOIN CMOBJECTS C with (nolock) ON A.CMID = C.CMID INNER JOIN CMCLASSES D with (nolock) ON C.CLASSID = D.CLASSID where d.name = 'reportVersion'
Hi Josh,
You have to save the report output in file system instead of Content Store.
You have to enable files sytem access in cognos configuration and specify the location in Cognos Administration.
Please refer the follwoing links
http://www-01.ibm.com/support/docview.wss?uid=swg21344147
http://www-01.ibm.com/support/docview.wss?uid=swg21342666
Hope this helps.
Regards,
Rahul
Yes, I am aware that you can save reports to a file system. Are you suggesting there is a way that once it's in the file system Cognos can still call it up from "View output versions", Cognos security can control access to it, and that Cognos can still control how many days the versions are kept?
Hi Josh,
Once the report output is in file system you cannot access it through "View output versions", I thought you need to maintain the report output for all the runs. If that is not the case you can set the number of report output version to 1 from report properties.
Regards,
Rahul