PX Tables Clean Up Procedure

Contents

To avoid operational problems for P6, PX table entries are not automatically deleted when you delete data from P6 tables. You can use the following clean up procedure to clear the obsolete entries in PX tables.

1. Run the CLEAN_PX_DELETE stored procedure to purge obsolete entries in the PX tables:

   For Oracle:

   BEGIN

   CLEAN_PX_DELETE();

   END;

   For a Microsoft SQL Server database:

   USE [ddname]

   GO

   DECLARE @return_value int

   EXEC @return_value = [dbo].[CLEAN_PX_DELETE]

   SELECT 'Return Value' = @return_value

   GO

   where ddname is the is your admin user name

   Note: You can run this procedure any time you want or you can run it as a database background job and schedule a specific time for it to run.

2. To delete batch size and max delete from each table in a single run, run the following queries:

   Note: You can specify the delete batch size and max delete from each table in a single run. Delete batch size is the number of rows deleted before a commit. Max delete is the max number of rows that will be deleted from a table in a single run. They have default values if you do not provide your own. They're used to avoid resource problems with the P6 operations.

   • insert into settings (namespace, setting_name, setting_value) values ('PxService.Cleanup','DeleteMaxSize','100000');
   • insert into settings (namespace, setting_name, setting_value) values ('PxService.Cleanup','DeleteBatchSize','5000');