Topic
  • 12 replies
  • Latest Post - ‏2012-07-21T18:13:55Z by SystemAdmin
cab0333
cab0333
3 Posts

Pinned topic Deleting Items in a Worklist through a query

‏2012-05-09T03:04:23Z |
Is there any impact on any other tables if you remove items from the ICMUT00204001 table for a specific worklist? Because this specific worklist has so many items, it does not open, so we would like to delete/remove these items through a query. We are not concerned that there will be nothing written to the history log, but would like to confirm there will be not impact to any of the other tables.
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-09T16:22:10Z  
    Direct SQLs to update/delete from LS tables are NOT supported by IBM. You would need to request the lab service or contact IBM customer support.
  • cab0333
    cab0333
    3 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-09T17:06:31Z  
    Direct SQLs to update/delete from LS tables are NOT supported by IBM. You would need to request the lab service or contact IBM customer support.
    Do you have any recomendations then as to how to remove these documents from the worklist if we cannot open it to to the volume?

    Thanks
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-09T17:11:57Z  
    • cab0333
    • ‏2012-05-09T17:06:31Z
    Do you have any recomendations then as to how to remove these documents from the worklist if we cannot open it to to the volume?

    Thanks
    You could try to force stop your document routing process for those documents and that should remove the work package for the documents.

    Please note that ICMUT00204001 is for work package not work list.
  • cab0333
    cab0333
    3 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-15T04:33:57Z  
    You could try to force stop your document routing process for those documents and that should remove the work package for the documents.

    Please note that ICMUT00204001 is for work package not work list.
    Hi, can you please be more specific on how to do this? Do you know if this will have any impact on any other tables. All I need to do is remove the current items from the worklist. Is the only way to do this through a PMR?

    Thanks!
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-15T16:59:04Z  
    • cab0333
    • ‏2012-05-15T04:33:57Z
    Hi, can you please be more specific on how to do this? Do you know if this will have any impact on any other tables. All I need to do is remove the current items from the worklist. Is the only way to do this through a PMR?

    Thanks!
    To remove items from a process in Windows Client:

    1. Select the item.
    2. Select Actions -> Process -> Remove from. A window opens asking if you want to remove the selected items from their processes.
    3. Click Yes if you want to remove them.
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-21T15:10:07Z  
    There are XPATH query examples in the info center that will return workpackages that reside in a work node. You can control the MAX_RESULT of a query, so if the node contains only packages you want to delete, you can loop through the results. I needed to clear out all packages in all nodes in all of a list of processes, and wrote a nested-loop java program to do it. THere's some logging and timeing code in this example, but you should get the jist:

    for (String processName : processNames) {
    // find the itemId of the DocRouting process.
    String qString = "/'ROUTINGPROCESS'";
    DKResults results = ItemUtilities.search(dsICM, qString);
    // ItemID is unique in the system, so only 1 or zero can be found
    if (results.cardinality() == 1) {
    dkIterator iter = results.createIterator();
    String processItemId = ((DKDDO) iter.next()).getPidObject().getPrimaryId();
    System.out.println(processName);

    // Find all workpackages in the process
    qString = "/'*'/REFERENCEDBY/@REFERENCER =>WORKPACKAGE[@PROCESSITEMID = \""
    + processItemId + "\"]";
    terminatePackages(dsICM, qString);
    }
    }
    //-------------------------------------
    //
    // terminatePackages(DKDatastoreICM dsICM, String qString)
    //
    private static void terminatePackages(DKDatastoreICM dsICM, String qString) {
    DKDocRoutingServiceICM routingService;
    try {
    routingService = new DKDocRoutingServiceICM(dsICM);
    System.out.println(ItemUtilities.searchCount(dsICM, qString) + " Workpackages to delete.");
    dkResultSetCursor cursor = ItemUtilities.search_cursor(dsICM, qString, 0);

    DKDDO wp = null;
    //
    // Read and process the control file
    //
    Integer totalProcess = new Integer(0);
    while ((wp = cursor.fetchNext()) != null) {
    totalProcess++;
    logger.info("WorkPackage " + wp.getPidObject().getPrimaryId());
    try {
    routingService.terminateProcess(wp.getPidObject().pidString());
    logger.info("Terminate workpackage " + wp.getPidObject().getPrimaryId());
    countTerminate++;
    } catch (Exception e) {
    logger.error("Unable to terminate workpackage " + wp.getPidObject().getPrimaryId());
    logger.error(e.getMessage());
    countError++;
    }
    }
    cursor.destroy(); // Close & Destroy Cursor, Ending Implied Transaction.
    System.out.println("Terminated: "+countTerminate);
    System.out.println("Error: "+countError);

    } catch (DKException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
    } catch (Exception e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
    }
    }
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-07-19T15:57:18Z  
    There are XPATH query examples in the info center that will return workpackages that reside in a work node. You can control the MAX_RESULT of a query, so if the node contains only packages you want to delete, you can loop through the results. I needed to clear out all packages in all nodes in all of a list of processes, and wrote a nested-loop java program to do it. THere's some logging and timeing code in this example, but you should get the jist:

    for (String processName : processNames) {
    // find the itemId of the DocRouting process.
    String qString = "/'ROUTINGPROCESS'";
    DKResults results = ItemUtilities.search(dsICM, qString);
    // ItemID is unique in the system, so only 1 or zero can be found
    if (results.cardinality() == 1) {
    dkIterator iter = results.createIterator();
    String processItemId = ((DKDDO) iter.next()).getPidObject().getPrimaryId();
    System.out.println(processName);

    // Find all workpackages in the process
    qString = "/'*'/REFERENCEDBY/@REFERENCER =>WORKPACKAGE[@PROCESSITEMID = \""
    + processItemId + "\"]";
    terminatePackages(dsICM, qString);
    }
    }
    //-------------------------------------
    //
    // terminatePackages(DKDatastoreICM dsICM, String qString)
    //
    private static void terminatePackages(DKDatastoreICM dsICM, String qString) {
    DKDocRoutingServiceICM routingService;
    try {
    routingService = new DKDocRoutingServiceICM(dsICM);
    System.out.println(ItemUtilities.searchCount(dsICM, qString) + " Workpackages to delete.");
    dkResultSetCursor cursor = ItemUtilities.search_cursor(dsICM, qString, 0);

    DKDDO wp = null;
    //
    // Read and process the control file
    //
    Integer totalProcess = new Integer(0);
    while ((wp = cursor.fetchNext()) != null) {
    totalProcess++;
    logger.info("WorkPackage " + wp.getPidObject().getPrimaryId());
    try {
    routingService.terminateProcess(wp.getPidObject().pidString());
    logger.info("Terminate workpackage " + wp.getPidObject().getPrimaryId());
    countTerminate++;
    } catch (Exception e) {
    logger.error("Unable to terminate workpackage " + wp.getPidObject().getPrimaryId());
    logger.error(e.getMessage());
    countError++;
    }
    }
    cursor.destroy(); // Close & Destroy Cursor, Ending Implied Transaction.
    System.out.println("Terminated: "+countTerminate);
    System.out.println("Error: "+countError);

    } catch (DKException e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
    } catch (Exception e1) {
    // TODO Auto-generated catch block
    e1.printStackTrace();
    }
    }
    Hi, I am looking exactly the same to delete more than 9 million items (none needed) from the last but one worknode. It cannot be opened and listworkpackagespidstrings gives java heap errors i.e. program cannot act. So used getnextworkpackage directly on worklist to delete/continue to last node, one by one and it works but it takes approx. 1 hour to delete/continue about 75 items whereas incoming rate is more than few thousands in one hour. May be it has to search for next wp in 9 miliion after each continue ? Not sure how terminateprocess works but that also need individual workpackagepidstring as input which is difficult in this size and causing the issue.

    Any way to resolve this ? Can Xpath query help or that will also have the same slowness issue ? Any help suggestions for program ?

    Will prefer a program to do it but if it cannot be done, the next option will be for direct DB deletions, for every entry in ICMUT00204001 there is also an entry in ICMSTRI001001 table. Is it OK to delete from Workpackages table only or also delete from ICMSTRI001001 which is huge and contain entry for every item in the system ?

    Thank you for your help.

    Vaidehi S. Tripathi
  • JKMorrow
    JKMorrow
    44 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-07-19T16:10:16Z  
    Hi, I am looking exactly the same to delete more than 9 million items (none needed) from the last but one worknode. It cannot be opened and listworkpackagespidstrings gives java heap errors i.e. program cannot act. So used getnextworkpackage directly on worklist to delete/continue to last node, one by one and it works but it takes approx. 1 hour to delete/continue about 75 items whereas incoming rate is more than few thousands in one hour. May be it has to search for next wp in 9 miliion after each continue ? Not sure how terminateprocess works but that also need individual workpackagepidstring as input which is difficult in this size and causing the issue.

    Any way to resolve this ? Can Xpath query help or that will also have the same slowness issue ? Any help suggestions for program ?

    Will prefer a program to do it but if it cannot be done, the next option will be for direct DB deletions, for every entry in ICMUT00204001 there is also an entry in ICMSTRI001001 table. Is it OK to delete from Workpackages table only or also delete from ICMSTRI001001 which is huge and contain entry for every item in the system ?

    Thank you for your help.

    Vaidehi S. Tripathi
    Change your java code to use a CMBResultSetCursor or a dkResultSetCursor. We had this same problem and was saved by dkResultSetCursor. This is a DB result set, and next you iterate through the cursor, as if you had queried the database directly. When I changed our code to use the dkResultSetCursor the job: ran much faster, never failed, and used less cpu, win, win, win.
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-07-19T17:19:54Z  
    • JKMorrow
    • ‏2012-07-19T16:10:16Z
    Change your java code to use a CMBResultSetCursor or a dkResultSetCursor. We had this same problem and was saved by dkResultSetCursor. This is a DB result set, and next you iterate through the cursor, as if you had queried the database directly. When I changed our code to use the dkResultSetCursor the job: ran much faster, never failed, and used less cpu, win, win, win.
    Thanks. Sounds good. It's going to take some time to test and will let you know the result.
    In the mean time, it will be helpful and if it's possible, can you share whatever part of the code you have to do this. Thanks.
  • JKMorrow
    JKMorrow
    44 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-07-19T19:28:03Z  
    Thanks. Sounds good. It's going to take some time to test and will let you know the result.
    In the mean time, it will be helpful and if it's possible, can you share whatever part of the code you have to do this. Thanks.
    Here's an abbreviated version of what we're doing. dsICM is the connection established earlier. I iterate through each of the item types looking for objects to delete. Testing showed that using the DK classes to be significantly faster than the CMB classes, especially when the volume reached the 100K's per night.

    
    dkRetrieveOptions = DKRetrieveOptionsICM.createInstance(dsICM); dkRetrieveOptions.baseAttributes(
    
    true); dkRetrieveOptions.linksInbound(
    
    true); dkRetrieveOptions.linksInboundFolderSources(
    
    true); dkRetrieveOptions.linksOutbound(
    
    false); dkRetrieveOptions.linksDescriptors(
    
    false); dkRetrieveOptions.linksTypeFilter(DKConstantICM.DK_ICM_LINKTYPENAME_DKFOLDER); dkRetrieveOptions.basePropertyCheckedOutDetails(
    
    true);
    

    
    
    
    protected 
    
    void deleteFromItemTypes(String itemTypeName) 
    
    throws Exception 
    { DKDDO ddo; 
    
    int reportedDeletes = 0;   String queryString = 
    "/" + itemTypeName + 
    " [@EXPIRATIONDATE <= \"" + expirationDate + 
    "\" ]";   dkResultSetCursor cursor = dsICM.execute(queryString, DKConstantICM.DK_CM_XQPE_QL_TYPE, dkRetrieveOptions.dkNVPair());   
    
    while ((ddo = cursor.fetchNext()) != 
    
    null) 
    { 
    
    if (isCheckedOutCheckIn(ddo)) 
    { writeData(
    "Pid: " + ddo.getPidObject().getPrimaryId()); ddo.del(); reportedDeletes++; 
    } 
    } cursor.close(); cursor.destroy(); sysOut(
    "Deleted " + reportedDeletes + 
    " items from " + itemTypeName); 
    }
    
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-07-21T18:12:02Z  
    • JKMorrow
    • ‏2012-07-19T19:28:03Z
    Here's an abbreviated version of what we're doing. dsICM is the connection established earlier. I iterate through each of the item types looking for objects to delete. Testing showed that using the DK classes to be significantly faster than the CMB classes, especially when the volume reached the 100K's per night.

    <pre class="jive-pre"> dkRetrieveOptions = DKRetrieveOptionsICM.createInstance(dsICM); dkRetrieveOptions.baseAttributes( true); dkRetrieveOptions.linksInbound( true); dkRetrieveOptions.linksInboundFolderSources( true); dkRetrieveOptions.linksOutbound( false); dkRetrieveOptions.linksDescriptors( false); dkRetrieveOptions.linksTypeFilter(DKConstantICM.DK_ICM_LINKTYPENAME_DKFOLDER); dkRetrieveOptions.basePropertyCheckedOutDetails( true); </pre>
    <pre class="jive-pre"> protected void deleteFromItemTypes(String itemTypeName) throws Exception { DKDDO ddo; int reportedDeletes = 0; String queryString = "/" + itemTypeName + " [@EXPIRATIONDATE <= \"" + expirationDate + "\" ]"; dkResultSetCursor cursor = dsICM.execute(queryString, DKConstantICM.DK_CM_XQPE_QL_TYPE, dkRetrieveOptions.dkNVPair()); while ((ddo = cursor.fetchNext()) != null) { if (isCheckedOutCheckIn(ddo)) { writeData( "Pid: " + ddo.getPidObject().getPrimaryId()); ddo.del(); reportedDeletes++; } } cursor.close(); cursor.destroy(); sysOut( "Deleted " + reportedDeletes + " items from " + itemTypeName); } </pre>
    Thank you for the help. I tested and can process the workpackages at the rate of approx. 80,000 an hour. I guess this will increase once it comes to normal levels.

    Below is the code i wrote for worklist handling in which i used one more variable processMax to control how many to process in one run.

    *********************************************************************************************************
    DKNVPair options[] = new DKNVPair[3];
    options[0] = new DKNVPair(DKConstant.DK_CM_PARM_MAX_RESULTS, "0"); // No Maximum (Default)
    options[1] = new DKNVPair(DKConstant.DK_CM_PARM_RETRIEVE,new Integer(DKConstant.DK_CM_CONTENT_YES));
    options[2] = new DKNVPair(DKConstant.DK_CM_PARM_END,null);

    String query = "/OutgoingDocs/REFERENCEDBY/@REFERENCER => WORKPACKAGE [@PROCESSITEMID = /ROUTINGPROCESS" ;
    System.out.println("Query is : " + query);
    dkResultSetCursor cursor = dsICM.execute(query, DKConstantICM.DK_CM_XQPE_QL_TYPE, options);
    long count = dsICM.executeCount(query, DKConstantICM.DK_CM_XQPE_QL_TYPE, options);

    System.out.println("Query executed. Total workpackages are " + count);
    DKDocRoutingServiceICM routingService = new DKDocRoutingServiceICM(dsICM); // Obtain the core routing service object.

    bufferedWriter.write("Starting to process max of " + processMax + " work packages in worklist " + worklistName);
    bufferedWriter.newLine();
    bufferedWriter.flush();

    while (((ddo = cursor.fetchNext()) != null) & processedDoc < processMax )
    { String wpPidStr = ((DKPid)ddo.getPidObject()).pidString();
    if (wpPidStr != null)
    { String wpPidStrnew = routingService.continueProcess(wpPidStr, routeName, userName);
    processedDoc++;
    bufferedWriter.write("Processed : " + wpPidStr );
    bufferedWriter.newLine();
    bufferedWriter.flush();
    } // end of if pid null check.
    } // end of while loop

    cursor.close();
    cursor.destroy();
    ***************************************************************************************************
  • SystemAdmin
    SystemAdmin
    228 Posts

    Re: Deleting Items in a Worklist through a query

    ‏2012-07-21T18:13:55Z  
    Thank you for the help. I tested and can process the workpackages at the rate of approx. 80,000 an hour. I guess this will increase once it comes to normal levels.

    Below is the code i wrote for worklist handling in which i used one more variable processMax to control how many to process in one run.

    *********************************************************************************************************
    DKNVPair options[] = new DKNVPair[3];
    options[0] = new DKNVPair(DKConstant.DK_CM_PARM_MAX_RESULTS, "0"); // No Maximum (Default)
    options[1] = new DKNVPair(DKConstant.DK_CM_PARM_RETRIEVE,new Integer(DKConstant.DK_CM_CONTENT_YES));
    options[2] = new DKNVPair(DKConstant.DK_CM_PARM_END,null);

    String query = "/OutgoingDocs/REFERENCEDBY/@REFERENCER => WORKPACKAGE [@PROCESSITEMID = /ROUTINGPROCESS" ;
    System.out.println("Query is : " + query);
    dkResultSetCursor cursor = dsICM.execute(query, DKConstantICM.DK_CM_XQPE_QL_TYPE, options);
    long count = dsICM.executeCount(query, DKConstantICM.DK_CM_XQPE_QL_TYPE, options);

    System.out.println("Query executed. Total workpackages are " + count);
    DKDocRoutingServiceICM routingService = new DKDocRoutingServiceICM(dsICM); // Obtain the core routing service object.

    bufferedWriter.write("Starting to process max of " + processMax + " work packages in worklist " + worklistName);
    bufferedWriter.newLine();
    bufferedWriter.flush();

    while (((ddo = cursor.fetchNext()) != null) & processedDoc < processMax )
    { String wpPidStr = ((DKPid)ddo.getPidObject()).pidString();
    if (wpPidStr != null)
    { String wpPidStrnew = routingService.continueProcess(wpPidStr, routeName, userName);
    processedDoc++;
    bufferedWriter.write("Processed : " + wpPidStr );
    bufferedWriter.newLine();
    bufferedWriter.flush();
    } // end of if pid null check.
    } // end of while loop

    cursor.close();
    cursor.destroy();
    ***************************************************************************************************
    It's another issue with my IBM Developerworks id. Both are me : VST and VaidehiTripathi

    Vaidehi S. Tripathi