Purge Configuration and Scheduling

It’s important to have all of Millennium’s eligible purges configured and running. You can use the front-end tool dmpurgejobmgr.exe to do the initial configuration of the purges. You then use the OpsView Scheduler application to schedule the purge job to run one or more times a day.

To see a spreadsheet of the purge templates available to you, go into CCL or SQLPlus. From CCL, run:

rdb SELECT * FROM dm_purge_template where active_ind = ‘1’ go

From SQLPlus, the command is nearly the same. Simply omit rdb at the beginning and go at the end.

This command should generate a table listing all the purges available for your Millennium version. If you want to see how successful the purges are, run the following command from CCL (from SQLPlus, simply omit rdb at the beginning and go at the end):

rdb SELECT T.Name, T.Program_str,J.Max_Rows,JL.Start_DT_TM, JL.End_DT_TM, JL.Parent_Table, JL.Parent_Rows, JL.Child_Rows, JL.Err_Msg, JL.Err_Code, JL.Purge_Flag FROM DM_PURGE_TEMPLATE T INNER JOIN DM_PURGE_JOB J ON T.Template_Nbr = J.Template_Nbr INNER JOIN DM_PURGE_JOB_LOG JL ON J.Job_ID = JL.Job_ID ORDER BY JL.Start_DT_TM DESC go

Below is an abbreviated example of the table the command will generate:

Here’s what you want to note:

1. When the Max Rows column is more than 50 percent of the Parent Rows, increase the Max Rows. If you don’t, you will have the problem that appears in the first row of data. Here the Error Message indicates that when Max Rows was reached for MIC IC Orders, the data that should have been purged was not.
2. To understand the full impact of the purges on your system, you want to look at both the Parent Rows and Child Rows columns. In the table’s third row of data, the Parent Rows purge was 261,752, but the Child Rows was 1,880,177. This means this purge in one run deleted 2,141,929 rows. This is a lot of data, which took nearly 53 minutes to purge (subtract the End Date and Time from the Start Date and Time). My first row’s purge took just under 6 minutes to delete 50,000 rows. One of the side effects of purges is you may need to increase your undo tablespace since some of these purges take a long time and delete millions of rows of data from the database.

To return to “Purges Help Minimize Database Growth” on the Millennium Medic homepage, click here.