Script to delete objects from the CS

[oscarca]

Hello Cognoise community,

I have a bunch of reports that are using a package that is no longer in use. Instead of having to manually remove all the reports that uses this package, I wish to simplify the process by creating a deletion script.
Has anyone created a script to delete all reports in the content store related to a specific package ?

Best regards,
Oscar

[dougp]

DANGER!  Just deleting the records from CMOBJECTS will leave orphaned records throughout the Content Store.  You should track down all of the related entries, like shortcuts, report views, etc.

It may be better to write a query against the Content Store to identify all of the reports, then go into the portal and manually delete them.  The attached query should do that for a Content Store on MS SQL Server.

[CognosPaul]

This sounds like something Motio is planning on doing. Do you have CI?

[oscarca]

Thank you for your valuable input dougp. I was just thinking since MOTIO can accomplish this which means that there must be an underlaying script that can find dependencies, delete them and make sure no orphaned records/objects exisits.
I will look into the attachment you sent me.

CognosPaul, I only have the free version of MOTIO PI not CI unfortunately.

[MFGF]

...since MOTIO can accomplish this which means that there must be an underlaying script that can find dependencies, delete them and make sure no orphaned records/objects exisits...

It's not necessarily the case that it is a script. There are both SDK and REST APIs into a Cognos instance, and my guess would be this would be something Motio coded via the SDK.

Just to echo what Doug said - messing in the content store like this can be very dangerous. Use extreme caution if you decide to proceed.

Good luck!

MF.

[Andrei I]

Has anyone created a script to delete all reports in the content store related to a specific package ?

Do you need to find all reports which using this package  (get a list with reports paths)?
Or you need a script to actually delete reports ?

[CognosPaul]

It will be difficult to find every reference, just consider all of the different places you might have a report reference.


Jobs
Schedules
Report Views
Shortcuts
Layout Component References
Drill throughs (there's a table that is used to update specs as reports are moved, and in the reports themselves)
Package level drill throughs
Style references


I'm sure there are some other ways of referencing reports, I just can't think of any off the top of my head.

[dougp]

Correct.  The SQL script I provided looks for reports that use a certain package.  There are more pieces to this puzzle.

I think Cognos will gracefully update a job that uses a report that is deleted via the portal (you'll want to test that) but if you hack away at the database and miss one of these, you may have problems with that job.  Similarly, deleting a report will delete its schedule (if done via the portal).

Cognos does not update report views and shortcuts that point to a report you delete.  You'll want to write a query to identify which report views and shortcuts point to the reports that use the package you want to delete.

To identify layout component references that refer to a report that uses the package you want to delete, you'll need to inspect the report spec (CMOBJPROPS7) of every report in your system.  Build a report with a layout component reference and review the report spec (XML) to learn what to search for.  Report specs can contain a lot of text.  If you do this, you'll want to involve CMOBJPROPS7 and your search (where spec like '%something to search for%') separately from the main part of the query or your query will run all week.

I think your best course of action is to write SQL to learn what you need to delete -- then manually delete the correct things using the UI.

[Andrei I]

I think your best course of action is to write SQL to learn what you need to delete -- then manually delete the correct things using the UI.

It would be safer to simply use Cognos SDK with very well defined API...

[oscarca]

Has anyone of you guys written your own Cognos SDK programs ?

[Andrei I]

I did :-). There is a special forum for SDK:
https://www.cognoise.com/index.php/board,136.0.html

[oscarca]

Thank you so much Andre, I will look into it :)