Running Data Purge

About this task

Before you run the purge operation, shut down all Optimize processes. The purge can take several hours to execute. You can track the progress of a running purge operation in the OPERATION_LOG table. See Viewing Purge Operation Events.

Oracle

Edit online

About this task

Important: To run massive_data_purge in Oracle, you must be logged in as the schema owner, not as SYSTEM or SYS.

Optimize purges data from database components in Oracle by calling a stored procedure from an Oracle database client. The procedure purges data by re-creating the database component and re-inserting the data you want to keep. With this approach, the purge operation always takes about the same length of time, regardless of the length of time between purges, so you can purge data infrequently (for example, about every three months). However, while frequent database purging will not speed up the time duration of the purge, you can improve the performance of the data purge operation by keeping fewer days of data.

To run Data Purge in Oracle

Procedure

The stored procedure call is:
```
execute massive_data_purge;
```

Avoid aborting a data purge procedure because doing so can affect foreign key constraints. However, if the massive_data_purge procedure for Oracle fails or is aborted, call this stored procedure to restore all Foreign Key constraints:

declare l_execution_count integer;

begin

metadatapkg.execute_pending_sql (l_execution_count,

                   'MASSIVE_DATA_PURGE',

                   'Add Foreign Keys',

                   metadatapkg.cyes

);

end;

Results

Important: If the Analysis and Process Tracker database components are in separate schemas, the Data Purge must be executed in each schema separately.

SQL Server

Edit online

About this task

Optimize purges data from databases in SQL Server by calling a stored procedure from an MSSQL database client. This procedure purges data by issuing DELETE statements.

To run Data Purge in SQL Server

Procedure

The stored procedure call is:
```
exec data_mgmt_purge_data
```
Avoid aborting a data purge procedure because doing so can affect foreign key constraints. Data purge in SQL Server disables constraints one at a time. If the data_mgmt_purge_data procedure for SQL Server fails or is aborted, call this stored procedure to restore all Foreign Key constraints:
```
EXEC db_mgmt_modify_dependant_fk_constraints_all 'CHECK'
```

Results

Important: If the Analysis and Process Tracker database components are in separate schemas, the Data Purge must be executed in each schema separately.

DB2

Edit online

About this task

Execute the following procedure from the command line where DB2 has been installed:

To run Data Purge in DB2

Procedure

Windows Only: db2cmd (skip this step if running on UNIX)
db2 connect to [dbname] user [username] using [password]
where:
- [dbname] is the DB2 database containing the Analysis database component and/or the Process Tracker database component.
- [username] [password] are the credentials for accessing the DB2 database listed above and access to the schemas containing the Analysis and Process Tracker database components.
- [analysis or process tracker schema name] is the schema containing the Analysis and/or Process Tracker tables.
db2 set path [Analysis Schema or Process Tracker Schema], SYSTEM

where [analysis or process tracker schema name] is the schema containing the Analysis and/or Process Tracker tables.
db2 set current schema [Analysis Schema or Process Tracker Schema]

where [analysis or process tracker schema name] is the schema containing the Analysis and/or Process Tracker tables.
Initiate the purge:
- Windows:
```
db2 call data_mgmt_purge_data()
```
- UNIX:
```
db2 "call data_mgmt_purge_data()"
```
Important: If the Analysis and Process Tracker database components are in separate schemas, the Data Purge must be executed in each schema separately.

Resetting Referential Constraints if Data Purge Fails on DB2

Edit online

About this task

If the Data Purge fails or is canceled by the user, there is a chance the referential constraints were not re-enabled. Use the following command sequence to enable them.

To reset referential constraints

Procedure

(Windows Only:) db2cmd (Skip this step if running on UNIX.)
db2 connect to[dbname] user [username] using [password]
db2 set path[Analysis Schema or Process Tracker Schema], SYSTEM
db2 set current schema [Analysis Schema or Process Tracker Schema]

Initiate the purge:

Windows:

db2 call db_mgmt_modify_dependant_fk_constraints_all ('ENFORCED')

UNIX:

db2 "call db_mgmt_modify_dependant_fk_constraints_all ('ENFORCED')"