Specifying Archiving Criteria for an Oracle Archive Subclient

This task applies to the subclient on the production database.

There may be times when you want to archive a subset of the table based on the value of one or more columns in the table (for example, orders that are older than a certain date). Create valid SQL queries against one or more of the table columns.

You can choose to delete the rows from the database to save memory on the production database and to improve lookup performance. The database is re-indexed after the rows are deleted.

The software deletes the rows (known as pruning) immediately after the archive. You can delay the pruning to off-peak hours. For information on how to delete the pruning, see Delaying Pruning on the Production Database.

Procedure

From the CommCell Browser, expand Client Computers > client > Oracle >instance.
Right-click the archive_subclient and click Properties.

The Subclient Properties dialog box appears.

On the Content tab, right-click the table to build the query against, and then select Specify Archive Criteria.

The Specify Archive Criteria For dialog box appears.

The Specify Archive Criteria dialog box appears.

Option	Configuration
Type the query	In the SELECT ...FROM box, type the WHERE clause of the SQL query. Optional: To delete the rows from the production database after the query, select the Delete Rows check box. Click OK.
Create the query in the user interface.	In the Columns box, double-click the table column to query against. The Select ...FROM area displays the column selection. In the Operators box,double-click the operator to use for the query. The Select ...FROM area displays the operator after the column selection. In the Select ...FROM box, type the value that you want to use in the query. When you have to enter 2 values (for example, using "BETWEEN AND"), separate each value with a space. Optional: To delete the rows from the production database after the query, select the Delete Rows check box. Click OK.

Option

Configuration

Type the query

In the SELECT ...FROM box, type the WHERE clause of the SQL query.
Optional: To delete the rows from the production database after the query, select the Delete Rows check box.
Click OK.

Create the query in the user interface.

In the Columns box, double-click the table column to query against.

The Select ...FROM area displays the column selection.
In the Operators box,double-click the operator to use for the query.

The Select ...FROM area displays the operator after the column selection.
In the Select ...FROM box, type the value that you want to use in the query. When you have to enter 2 values (for example, using "BETWEEN AND"), separate each value with a space.
Optional: To delete the rows from the production database after the query, select the Delete Rows check box.
Click OK.

Example

One of the most common archive criteria is using the date to determine what gets archived.

If you use the following order table:

Column	Type
ORDER_ID	NUMBER(12,0)
ORDER_DATE	DATE
ORDER_MODE	VARCHAR2(8,BYTE)
CUSTOMER_ID	NUMBER(6,0)
ORDER_STATUS	NUMBER(2,0)
ORDER_TOTAL	NUMBER(8,2)
SALES_REP_ID	NUMBER(6,0)
PROMOTION_ID	NUMBER(6,0)

To archive the records with an ORDER_DATE older than 1 year, type the following query in the Select ...FROM box.

WHERE CV_ARCH_SYSDATE – ORDER_DATE > 365

Note: CV_ARCH_SYSDATE is a Commvault software-defined macro that places the archive job start time in the appropriate format for specifying the archive criteria.