Update a Large Number of Objects Using DQL

I also posted in the support forum: https://community.emc.com/thread/145047

Hello ECM community. This is my first post. I've looked/searched around and I havn't been able to find anything yet that does exactly what I want to be able to do.

I want to be able to update the attributes of a large number of object, like 100,000, with update dql statements, like the following:

UPDATE custom_object OBJECTS

SET acl_name = 'custom_object_acl'

WHERE FOLDER('/Custom/Directory', DESCEND)

If the query updates too many objects (most of the time over 2000 objects, although once I sucessfully updated 32,000 objects), then the server hangs and the issued dql statement has no effect. I've been using DA's dql editor to perform these changes.

I was wondering if there was something that would:

1. Break the dql command automatically and update objects in batches

~or~

2. Execute the dql command successfully without hanging up the server and possibly give an estimated time to completion.

Basically, I want to be able to input this dql command and have it perform without having to worry about how many objects are being updated. It's fine that the command runs for three days, I just need to know that it is still executing.

Thanks for the help.

Find more posts tagged with

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

PanfilovAB

Seems the problem is that since D6 dfc executes TBO methods for any update operation, e.g. content server returns list of ids of updated objects and dfc should execute save() method for every object returned. You can avoid this behaviour by executing DQL-update in the following way (note, that it is an API call, not DQL):

apply,c,,EXEC,QUERY,S,'UPDATE custom_object OBJECTS SET acl_name = ''custom_object_acl'' WHERE FOLDER(''/Custom/Directory'',DESCEND)',FOR_UPDATE,B,T,BOF_DQL,B,F

All comments

DCTM_Guru

The surest of way of avoid any sort of hanging is instead of doing the execution in batches and worry about database tempspace (where the SQL update is writing in case of transaction rollback) is to query for all the object ID and then use DFC methods to update each individual object. This minimizes the transaction size to single object.

Julien.Fontaine

Yes, cut your job in various Batches.

For example :

Select * from xxxxx where r_object_id like '*0'

It is a good way to create 16 batches !

lastnitescurry

UPDATE custom_object OBJECTS SET acl_name = 'custom_object_acl'WHERE FOLDER('/Custom/Directory', DESCEND)

Suggest you also update acl_domain or you run the risk of breaking an object security.

PanfilovAB

apply,c,,EXEC,QUERY,S,'UPDATE custom_object OBJECTS SET acl_name = ''custom_object_acl'' WHERE FOLDER(''/Custom/Directory'',DESCEND)',FOR_UPDATE,B,T,BOF_DQL,B,F

ArunSelvaraj

Hi,

You can use creation date as where clause and update objects in batches

Hope it helps

--Arun

lastnitescurry

PanfilovAB wrote:Seems the problem is that since D6 dfc executes TBO methods for any update operation, e.g. content server returns list of ids of updated objects and dfc should execute save() method for every object returned.

Maybe but the database transation rollback problem as Johnny mention has been a pain in the Admins buttocks long before TBOs were a twikle in Documentum engineers eyes

btw... nice method args, must have a play with that sometime

PanfilovAB

Brian Dinneen wrote: the database transation rollback problem as Johnny mention has been a pain in the Admins buttocks long before TBOs were a twikle in Documentum engineers eyes

Not sure about MSSQL, but on my test VM with oracle database Zach's update generates about 640Mb redo and 200Mb undo (for each of 100k records following changes applied: updated owner_permit, group_permit, world_permit (note that acl changed) and acl_name, acl_domain, i_vstamp columns in dm_sysobject_s table, one record created in dmi_queue_item_s for fulltext indexing, and one record created in dm_audittrail_s and dmi_object_type). Such numbers (640Mb redo and 200Mb undo) were noticeable 10 years ago, but for modern hardware they do not make sense.