What is the best approach to delete 4.5 million objects from a docbase?

Hi,

I have the following DQL which gives the count of Logical documents in our docbase as 4.5 million objects.

select count( * ) from ecm_inv_mgmt where a_storage_type = 'AppONE'

I need to delete all of them. I know I can use DQL (like DELETE... etc.) or API (like DESTROY....etc) commands. Also I can use either IDQL, IAPI or DA, but my question is what is the best approach to delete 4.5 objects faster without timing out? Please give detailed response, if you can.

Our Environment: Content Server 6.7, Tomcat 6.0.29, Oracle 11.1 on AIX

Please share your knowledge and experience.

Thanks.

Find more posts tagged with

Documentum

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

pettitk

Is this a 1 time thing? If so, I would do the no-code approach and just create a text file with all the r_object_id’s in them and let it run overnight. Test a few hundred lines first, then run the entire thing.

1. Create a text file and put the query in it. Assume its named mydqlquery.txt. Contents would be something like:

select r_object_id from …. Where…

quit

2. Idql32 -Umyuser –Pmypass –Rmyoutput.txt

I don’t have any code snippets handy at the moment. I would just grab something from the snippets online in the developer network, gut it out and put your query in it. There are also snippets and examples in the dfc javadocs (older versions like 6.0 have it, I haven’t seen it in all the newer versions). Run it via composer. Or do the same by grabbing the consistency checker EBS file (docbasic). Gut it down to a few lines and use it as example code. You can run docbasic from a command line, dmbasic.exe. I don’t have that syntax off the top of my head. Its in the manuals though. My 2 cents is 2 just create a quick api script as above unless you need to repeat this often. I use such all of the time to massage or do bulk changes. DQman is a great tool to help with scripting too. Not sure if it can handle 4.5M records well though. Google Documentum dqman. It’s a free and wonder DQL/API tool. (they should pay me to say that!!). J But they don’t. Really it’s a great tool.

All comments

pettitk

Create a small API script w the destroy commands… let it run overnight. J Run it on the server directly.

You can create the script w various tools. I would just use SQL query, or MS Access, etc to generate the destroy commands.

You create a small java or docbasic program to do the same.

Kyle

athati

Thanks Kyle for your quick response.

Do you have any code (DQL/SQL/Java/docbasic) on hand to create API script with DESTROY commands? If you have one, please post it. That would be a great help.

Thanks.

pettitk

1. Create a text file and put the query in it. Assume its named mydqlquery.txt. Contents would be something like:

select r_object_id from …. Where…

quit

2. Idql32 -Umyuser –Pmypass –Rmyoutput.txt

athati

Thanks Kyle for your quick response.

I may use it a couple of times, but it is not regular process.

The problem is creating one big text file with all object IDs using "select r_object_id from....". Can I open it to edit? Otherwise I need to create smaller chunks of text files with many DQLs.

I googled for dqman. I did not get anything like that.

Thanks.

pettitk

Google: dqman Documentum

1st hit

Try notepad++ to do text editing. Its much better then notepad. With stuff this large, you may not be able to use DQman, IDK without trying.

IDK if you can open 4.5M in notepad… You will need to try that one too. I do know the search/replace will be painful w a large file in notepad, so I would suggest notepad++ or any similar advance text editor. MS Word even works, but is slow too.

You may need to use MS Access or do it with an SQL query directly in database (oracle, SQL Server, etc). For that, I use SQL Developer. A free tool similar to Toad. Its by Oracle and free.

trehm_fme

rtry

http://www.dqman.com

pettitk

1. Query the objects to get the r_object_id’s

2. Run destroy,c, on each object in multiple api scripts or come back in a few days.

Ya, you can use tools like dqman to help create the script, or notepad++ too. I woudn’t run all 4.5 million in 1 dqman session. I would break it up into 100’s of scripts and run them all simultaneously.

-Kyle

trehm_fme

Hi,

a script with 4.5 million lines will be hard for every editor.

I would do it this way:

1. Create an object function (etras -> options -> object functions) in dqMan,

called "Delete object", with only one line of code:

destroy,c,{r_object_id}

({r_object_id} references the r_object_id in the query result grid)

and the count processed objects output option.

You should activate the "Allow multiselect" option and you

can activate the "ignore errors" option, so execution will not stop in case of any problems.

2. Query all objects to delete (only column r_object_id, makes it faster)

3. Select all and execute the Delete object function from the context menu.

This will delete all objects with few ressources. Started in the evening it should be done next morning.

athati

Thank you very much for your help Tillmann. It is an excellent suggestion.