DB2 Administration › How to Delete Data Immediately

How to Delete Data Immediately

The ReportCenter Expiry Service runs regularly, deleting expired data from your database.

If you must delete data immediately without waiting for it to expire, you can delete it manually. For example, you could have the following reasons for deleting data:

• Your database is too large, and has space or performance problems.
• You have been testing ReportCenter and want to get rid of the data collected during testing.
• You no longer have certain resources and want to get rid of their performance data.

Ensure that only the performance attributes on which you want to report are sent to ReportCenter and stored in your database. Rather than sending everything to ReportCenter, use the Example Reports to see what reports are useful to you.

To delete data immediately, perform the following steps:

1. Run the data warehouse status reports.
2. Decide what to delete.
3. Back up your database.
4. Edit and run the SQL DELETE statements.
5. Run reorganization and statistics utilities.
6. Prevent future unwanted data.

More information:

About Data Retention Rules

Run the Data Warehouse Status Reports

If you do not have a recent run of the following reports, run them.

• Data Warehouse Fact Status

  Note: On a large database, this may take a while to generate.

• Data Warehouse Resource Status

These reports appear under the Reports,Miscellaneous option. We recommend that you schedule them to run weekly.

Decide What to Delete

Look at the Data Warehouse Fact Status Report, and determine what is taking up space. Assess whether the value to your organization of the resulting reports justifies the number of rows taken by particular resources, attributes, and sampling applications.

The aim is not to trim the database of every last unwanted row, but to identify what can most easily reduce the bulk, without impacting the reports you find most useful.

Note the resources, attributes and data sampling applications that you want to delete.

Back Up Your Database

Stop the ReportCenter Java Task, and back up your database.

Edit and Run the SQL DELETE Statements

To edit and run the SQL DELETE statements

1. Locate and copy the dsnpref.NMC1.CC2DSAMP(WRDB2SQE) data set member. This member contains examples of SQL DELETE statements.
2. Edit your copy of WRDB2SQE, as instructed at the top of the member.
3. Confirm that you really want to delete what you have specified, and you have specified what you want to delete.
4. Run the delete statements, using any DB2 batch plan or SQL interface.

   The user who runs them must have adequate database authority to delete fact table rows.

   There must be sufficient DB2 resources to process statements that delete large numbers of rows.

5. A successfully run delete statement ends with a condition code of zero, and a message resembling the following:

   SUCCESSFUL DELETE    OF    31517 ROW(S)
   

Run Reorganization and Statistics Utilities

To run reorganization and statistics utilities

1. Re-organize your database, to reclaim space.
2. Run the RUNSTATS or equivalent utility to ensure optimal access paths and get any response time benefits from a smaller database.
3. Restart the ReportCenter Java Task.
4. Rerun the Data Warehouse Fact Status Report and compare it to the previous one.

Prevent Future Unwanted Data

To avoid collecting unwanted data in the future

1. Review and, if necessary, modify the Expiry Service Data Retention Rules.
2. If you never want to report on a resource or attribute, and you just deleted it, set SEND TO REPORTCENTER = NO so you don't collect it any more.