Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
12 replies Latest Post - ‏2012-07-21T18:13:55Z by SystemAdmin
cab0333
cab0333
3 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-09T16:22:10Z  in response to cab0333
    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
      ACCEPTED ANSWER

      Re: Deleting Items in a Worklist through a query

      ‏2012-05-09T17:06:31Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Deleting Items in a Worklist through a query

        ‏2012-05-09T17:11:57Z  in response to cab0333
        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
          ACCEPTED ANSWER

          Re: Deleting Items in a Worklist through a query

          ‏2012-05-15T04:33:57Z  in response to SystemAdmin
          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
            ACCEPTED ANSWER

            Re: Deleting Items in a Worklist through a query

            ‏2012-05-15T16:59:04Z  in response to cab0333
            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
    ACCEPTED ANSWER

    Re: Deleting Items in a Worklist through a query

    ‏2012-05-21T15:10:07Z  in response to cab0333
    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
      ACCEPTED ANSWER

      Re: Deleting Items in a Worklist through a query

      ‏2012-07-19T15:57:18Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Deleting Items in a Worklist through a query

        ‏2012-07-19T16:10:16Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Deleting Items in a Worklist through a query

          ‏2012-07-19T17:19:54Z  in response to JKMorrow
          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
            ACCEPTED ANSWER

            Re: Deleting Items in a Worklist through a query

            ‏2012-07-19T19:28:03Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

              Re: Deleting Items in a Worklist through a query

              ‏2012-07-21T18:12:02Z  in response to JKMorrow
              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
                ACCEPTED ANSWER

                Re: Deleting Items in a Worklist through a query

                ‏2012-07-21T18:13:55Z  in response to SystemAdmin
                It's another issue with my IBM Developerworks id. Both are me : VST and VaidehiTripathi

                Vaidehi S. Tripathi