I have a question about the Delete Documents selection box in Supervisor.
When you select from the Supervisor menu, Tools > Delete Document, a Delete Documents selection box pops up. One of the columns is labelled Type and has a description of the document type. For example:
Document (user) or Document (repository).
I have been trying to find what table in the Repository database the description is stored?
I have searched every table there and couldnt find it.
I have attached a screen shot of the Delete Documents selection box so you know what I am talking about.
Would anyone be able to help?
Does anyone have a good SQL script that can search all tables in a database for a piece of text and return the table it is stored in?
Reason I asked is, it seems that when documents are scheduled, when you look in supervisor for the scheduled documents, this ‘Type description’ appears next to them.
We want to just delete all those documents from the OBJ_M_DOCUMENTS table, where ‘Type’ = ‘Document (user)’. But no description field exists in that table.
Even if I knew the correct Type Code, that would help.
100s of reports are being scheduled, and the OBJ_M_DOCUMENTS table keeps filling up and running out of tablespace. The DBAs say they have monitoring tools for the database but they forget to tell us about any warnings on the table space for this table. The DBA’s won’t do anything about this until we give them the right sql for deletion of records. (They will automate this process when we do).
I have a repository based universe which I use to help make decisions on which documents to clear out. I have attached a copy, along with a report which lists documents in the repository. The report shows all documents along with info on their size (at least when exported), which document domain they are in, whether they are corporate or not, whether they are scheduled or not, and whose inboxes they are in.
The last tab is useful because it lists all documents that are older than a certain number of days (a filter set to 60 days currently), but excludes corporate and current scheduled documents - this list is exactly the same as the list you get in Supervisor if you ‘Purge Inbox Documents’ older than 60 days - but it gives so much more information, so you can be selective about purging.
One thing to note, if a document is listed in more than one inbox it does not mean that there is more than one copy in the repository, it just means that more than one user has a link to the same document.
Obviously if you set the filter in the report to less days then you can identify more documents to delete - the ones to watch out for are reports that are only in the BCA agents inbox (the BCA agents appear as users). As long as these do not have pending jobs (use the In Queue? flag to make sure), and they are not in any other users inbox then it should be safe to delete.
Please remember in Supervisor when you use the Delete Documents window you have all documents on view - including corporate documents and pending BCA documents, so if you are not careful you can run the risk of creating havoc! Make sure you carefully pick the report based on the Document Id, not the name. Finally I would suggest deleting in small batches from Supervisor, otherwise the window hangs for ages - and you are less likely to do something you regret!
I would do the above in conjunction with regular repository backups, and scan, repair and compact operations.
There is a “sticky” at the top of this forum that links to the repository structure documentation available from BusinessObjects. What you need, I believe, is in the OBJ_M_DOCUMENTS table, which is detailed here:
Dave, I had a look at that table yesterday, OBJ_M_DOCUMENTS and the field M_DOC_N_TYPE.
I would have thought that scheduled documents would have the M_DOC_N_TYPE of ‘4’ which is BODT_DOCUMENTBATCH. But no, the scheduled documents also have the M_DOC_N_TYPE = 1, ‘BODT_DOCUMENT’, which is the same type as documents stored in Corporate Docs.
Beckster, I forgot to mention here that the version running here is 5.1.6 of Business Objects, so I won’t be able to run the report you have created in this version.
However, as I am a Consultant, it will definitely come into good use at other client sites, so thanks so much for posting it on BOB.
Do you possibly have some screen shots of what the report looks like and then I can possibly recreate a 5.1.6 version of the reports.
No problem, I’ll try and get all my reports together. Maybe some help text too.
DaMouse, have you come across the ‘fiddle’ for opening v6 reports in v5? Open the report in a text editor, scroll all the way to the bottom and find the line that says 6.0.0.0. Change that to 5.1.7.0 or whatever version suits. Save the file. Then try opening in v5 - it should work.
I can’t remember if my universe may need adjusting for v5 - in some versions I have derived tables in (but only for the BCA queue part). You will also need to define the connection to your repository obviously.
Otherwise at some point I’ll try and get a v5 version available
I have dropped a copy of the universe and some useful reports to BOBs Uploads.
If it is considered suitable then I’d be happy to think it might help some more folks out.
I’d also appreciate any feedback on it - there is always room for improvement - I have always intended to try and incorporate some of the resource inheritance permissions (such as Cindy has tackled with managero in the past) e.g. which groups have permissions to which documents etc., maybe one day I’ll find the time!
(BOB member since 2004-07-23)
(BOB member since 2002-06-06)
(BOB member since 2003-02-18)
(BOB member since 2007-01-03)