Which one would give better performance ?? DQL or API

Tej

Hi All,

I have a query ,

I need to execute a delete operation for about 10 milion objects ,

What would be the most optimized solution

A) Run a DQL using DFC code.

Run a API using DFC code.

c) Run DQL using DocBasic script.

d) Run API Using Doc basic scrpit

Is there any better option other that the above ones , if yes could you please share it.

Thanks in advance,

Regards,

Tej.

Find more posts tagged with

Comments

apriam_i

Hi Tej!

If you have single DQL query for all set of those 10 million objects, it would be better to run query in Documentum Administrator or use idql tool.

In late versions of Documentum Docbasic is depricated. AFAIK it is always translated into java while running.

Tej

Thanks for the Reply,

I am using documentum 6.5 version ,

if we run a DQL Query then it would get timmed out ...

So how to handle the time out or session time as thjis is not a one time effort it would be repeated in a period of time.

Thanks for the help in Advance,

Reagrds,

Tej.

kkoneru

Tej-

If you are going to delete 10 million object you can split the DQL query into multiple one'e and then run the DQL Query. Are you deleting any FTI or Audit trail entries?

Please also note that you need to alter the table in the DB side also.

Aringen

Tej,

The best option is to use DFC.

Get the following piece of code and try getting the r_object_id s of the dm_sysobjects that you want to delete by using a DQL query and rest this code will take care of.

public static void main(String[] args) {

queryString = <your query string> ;

IDfSession session = null;

try {

loginManager = new LoginManager(m_user,m_password,m_docbase); //LoginManager is my implementation

session = loginManager.getSession();

collection = commonMethod.execReadQuery(queryString, session);

while (collection.next()) {

if(collection.getId(“r_object_id”).isObjectId()){

sysObject = (IDfSysObject)session.getObject(collection.getId(“r_object_id”));

sysObject.destroy();

}

}*/

} catch (Exception e) {

e.printStackTrace();

}finally{

try {

if(collection != null)

collection.close();

if(loginManager.isConnected())

loginManager.releaseSession();

} catch (Exception e) {

e.printStackTrace();

}

Best Regards,

Arindam

kkoneru

Tej-

Here is the code if you want to do it usng DFC:

import java.io.*;
import java.util.*;

import com.documentum.com.*;
import com.documentum.fc.common.*;
import com.documentum.fc.client.*;

public class DeleteQueueItems {

public static void main(String args[]) {

if(args.length<5) {
System.err.println("Usage: docbase user pass true|false dqlWHERE");
System.exit(1);
}

try {
DeleteQueueItems tests = new DeleteQueueItems(args[0],args[1],args[2],args[3],args[4]);
}catch(Exception exc) {
System.err.println("EXCEPTION caught in main!");
exc.printStackTrace();
}

} // main

public DeleteQueueItems(String docbase, String user, String pass,String deleteStr,String dqlWhere) throws Exception {

IDfClientX clientx = null;
IDfSessionManager sessionManager = null;
IDfSession session = null;

        clientx = new DfClientX();
        sessionManager = createSessionManager(docbase, user, pass);
        session = sessionManager.getSession(docbase);
        System.err.println("established DCTM session!");

boolean delete = "true".equalsIgnoreCase(deleteStr);

int count = 0;

ArrayList list = new ArrayList(1000);
IDfCollection col = null;
        IDfQuery q = clientx.getQuery();
String dql = "select r_object_id from dmi_queue_item where " + dqlWhere;
        q.setDQL(dql);
System.err.println(dql);
        col = q.execute(session, IDfQuery.DF_READ_QUERY);
while(col.next()) {
list.add(col.getId("r_object_id"));
}
col.close();
System.out.println("Total Items that matched criteria: " + list.size());

for(int i=0;i<list.size();i++) {
IDfId id = (IDfId) list.get(i);

if(delete) {
   IDfPersistentObject item = session.getObject(id);
   if(item!=null) {
    try {
     System.err.println("deleting: " + id.getId());
     item.destroy();

     count++;
     if(count%100==0) {
      System.out.println("Deleted " + count);
     }

    }catch(DfException exc2) {
     System.err.println("!!!! COULD NOT DELETE " + id.getId());
    }
   }else {
    System.err.println("COULD NOT FIND " + id.getId());
   }
}else {
   System.err.println("Would have deleted: " + id.getId());
}

}

sessionManager.release(session);

} // constructor

IDfSessionManager createSessionManager(String docbase, String user, String pass)
throws Exception {
    //create Client objects
    IDfClientX clientx = new DfClientX();
    IDfClient client = clientx.getLocalClient();

    //create a Session Manager object
    IDfSessionManager sMgr = client.newSessionManager();

    //create an IDfLoginInfo object named loginInfoObj
    IDfLoginInfo loginInfoObj = clientx.getLoginInfo();
    loginInfoObj.setUser(user);
    loginInfoObj.setPassword(pass);
    loginInfoObj.setDomain(null);

    //bind the Session Manager to the login info
    sMgr.setIdentity(docbase, loginInfoObj);
    //could also set identity for more than one Docbases:
    // sMgr.setIdentity( strDocbase2, loginInfoObj );
    //use the following to use the same loginInfObj for all Docbases (DFC 5.2 or later)
    // sMgr.setIdentity( * , loginInfoObj );
    return sMgr;
}

} // class

DCTM_Guru

We had similar issue when we were trying to delete millions of audit trail objects. The problem with using DQL (which is really SQL on the backend) is that the database will try to execute the entire statement as single transaction. We were using Oracle, so while it was trying to delete rows, it was also creating rows in TEMP space in case we wanted to rollback the delete transaction. When we initially ran the DQL (without breaking into batches as previous poster suggested), it hung Oracle server. Not only did it hang, when we tried restarting Oracle, the server was trying to revert the delete stored in the TEMP space. Net-net, avoid running large deletes/updates via DQL.

Querying for large set is fine and then use DFC delete for each object worked for us.

apriam_i

Tej, btw, try to delete (either DQL or DFC) object in superuser's session.

In this case security permission checks will be bypassed.