Purging operational data

For operational data purging for SQL server systems, schedule the Itron-supplied stored procedure TASK_PURGE to run daily. TASK_PURGE is used to clean up all the task-related tables. When purging large amounts of data, you can run multiple task purge procedures concurrently.

TASK_PURGE uses four parameters: DAYSTOKEEP, BATCHSIZE, TIMETORUN, and HISTORIC_ONLY.

  • DAYSTOKEEP determines the number of days of task data you want to keep in IEE starting from today and moving backward. The default value is 32767.

    Note: DAYSTOKEEP is determined using the SubmittedDate rather than the ProcessingCompleteDate; ensuring that tasks in a Canceled, Error, or Failed state (thus having a NULL value for the ProcessingCompleteDate) will be purged.

  • BATCHSIZE determines the number of tasks, in chunks, that are deleted per pass of the purge task, when running in Batch mode. The default value is 1000. With each pass of the purge task, one chunk is deleted and the procedure verifies that TIMETORUN has not expired. The task purge procedure continues this loop, until the time defined by TIMETORUN expires.

  • TIMETORUN determines the maximum running time (in minutes) that the task purge procedure instance continues to purge tasks in BATCHSIZE chunks. The default value is 180. When the TIMETORUN setting expires, the task first completes processing its current batch chunk, and then stops.

    Note: When you assign values to the BATCHSIZE and TIMETORUN parameters, take into consideration the amount of memory and the number of processors you have available.

    Note: The BATCHSIZE and TIMETORUN parameters have a direct relationship to one another. The larger the value assigned to BATCHSIZE, the longer amount of time it takes the purge procedure to process each batch. If you set BATCHSIZE to a large value, make sure that you set TIMETORUN to a value that allows enough time for the task procedure to realistically complete the number of passes required to purge the amount of data you want to purge.

  • (Optional) HISTORIC_ONLY determines if historical tasks only are purged. Set this value to TRUE to purge only the historical tasks prior to DAYSTOKEEP. The default value is FALSE, which purges all data, current and historical, prior to DAYSTOKEEP. When left blank, the default value, FALSE, is used.

    If TASK_PURGE has not yet been run, Itron recommends the following methodology for putting it in place:

  1. Determine how many days of task data are currently in IEE. SELECT MIN (processingcompletedate) FROM TASK. For this example, suppose there are 180 days of data in the task table and you desire to keep no more than 90 days.

  2. Run TASK_PURGE several times starting with 178 as DAYSTOKEEP and decrementing DAYSTOKEEP by 2 each day until you are down to 90 days of data online.

  3. If you want to run multiple task purges concurrently, then allow the first new version of the purge procedure to run to completion before starting multiple task purge procedures. Doing so allows the queue table to fill, which can be time consuming. Failure to do so doesn't cause harm; all other purge procedures wait for the queue table fill before they complete, which could exceed the TIMETORUN value.

  4. Now schedule (using DBMS_SCHEDULER) TASK_PURGE to run each day with DAYSTOKEEP = 90.