Using the Delete Request Editor

In the Delete Request Editor, you can create, delete, or modify Delete Requests stored in the Optim™ Directory.

Delete Request Editor. The elements of the Delete Request Editor are described as follows.
Description
Enter text to describe the purpose of the Delete Request (up to 40 characters).
Server Name

If the optional Optim Server component is installed, you can delegate resource-intensive Delete Request processing (for example, when the source file contains a large number of tables or rows) to a machine hosting Optim Server.

Click the down arrow to select a machine hosting Optim Server, or select (Local) to process the request on the local workstation.

Note: If the Optim Server machine option is not enabled at your site, Server Name is unavailable.
Tabs

The Delete Request Editor displays tabs that allow you to specify parameters and select options for running a Delete Request. Each tab in the editor serves a unique purpose:

General
Specify parameters for the Delete Process, including the Source File Type, Source File name, Control File name, and optional Process Options. Each time you open the editor, the General tab is shown first.
Notify
Specify options for automatic email notification of the success or failure of the process.

Tools Menu Commands

In addition to the standard commands on the File and Edit menus, you can select the following commands from the Tools menu:

Edit Strategy
Open the Table Access Strategy dialog to select these processing options for each table:
  • override the default method (scan or key lookup) of accessing the table
  • specify the maximum number of lookups for the table
  • select whether or not the table's row contents are compared.
Edit ACL
Open the Access Control List Editor to secure the Delete Request with an Access Control List. Available when Object Security is enabled.
Delete ACL
Delete the Access Control List securing the Delete Request. Available for secured Delete Requests only.

Table Access Strategy

The Table Access Strategy dialog allows you to modify the access method processing options for each table in the Delete Request.

Table Access Strategy dialog
Access Method

Allows you to override the default method (scan or key lookup) of accessing each table in the Delete Request. A scan reads all rows in a table at one time; whereas, a key lookup locates rows using a WHERE clause to search for primary or foreign key values.

Note: You should only override the default method if the statistical information in the process report indicates the default method is inefficient.

Specify:

Default
By default, Archive determines whether to use a scan or key lookup.
Note: In general, a key lookup is used when a DBMS index is available, and a scan is used when an index is not available.
Force Scan
Force Archive to use a scan.
Force Key Lookup
Force Archive to use a key lookup.
Note: Before selecting, you should verify that a DBMS index exists. Right-click a table name in the grid and select Analyze Primary Key Index for this table to open the Primary Key Index Analysis dialog. For more information, see Primary Key Index Analysis.

To set all values in the parent or child column at once, you can right‑click the Table Access grid column and click either Set All Default, Set All Force Key Lookup, or Set All Force Scan from the shortcut menu.

Key Lookup Limit

Specify the maximum number of key lookups performed at one time for a table. Valid values are 1 through 100. By default, Archive looks at one key at a time.

Note that increasing the Key Lookup Limit can significantly improve performance. For example, if you specify 5 for Key Lookup Limit and the key spans a single column, 5 key values are searched in a single request to the DBMS.

Note: The following conditions must be true to edit the Key Lookup Limit column for a table:
  • An index on the primary key is defined for the table.
  • Compare Row Contents is not selected for the Delete Request.
  • Row-level Archive Actions are not defined for the Delete Process (for example, Before Delete of Row).
  • The DBMS for the table is Sybase ASE, Informix®, or SQL Server (if SQL Server is not using Array Deletes).
  • The table does not have any child tables.

You can right-click the Key Lookup Limit grid column and select the Set All command from the shortcut menu to display the Key Lookup Limit for all tables dialog.

Key Lookup Limit for all tables dialog

Use the Key Lookup Limit for all tables dialog to set the values for all tables in the Delete Request at once.

Compare Row Contents
Specify whether to compare the row contents before deleting. For a table in the Delete Request, click the down arrow to select one of these values:
  • Default - If the table has a non-unique Primary Key, the default is to compare row contents, regardless of the setting for Compare Row Contents in the Delete Request. To override the default, use Compare Never. For a table with a unique Primary Key, the default is to use the setting for Compare Row Contents in the Delete Request.
  • Compare Always - If the table has a non-unique Primary Key, specifying Compare Always has no effect since these rows are always compared, regardless of the setting for Compare Row Contents in the Delete Request. For a table with a unique Primary Key, use Compare Always to override or augment the setting for Compare Row Contents in the Delete Request.
  • Compare Never - Use Compare Never if you do not want row contents to be compared. This overrides the setting for Compare Row Contents in the Delete Request. This might improve performance significantly. However, you risk losing any updates to the data in the database since the Archive or Extract was performed. Optim displays a warning message which you must acknowledge before you can proceed with the Delete.
Refer to the table below for information on the delete options and their potential results.
Table 1. Database Types, Processing Options and Expected Results
Database Primary Key Index (non-unique or no index) Compare Never option set for table? Archive Actions or Delete File attached? Key Lookup Limit setting Delete processing when Access Method is default Expected result Risks, Warnings, and Comments
Oracle or DB2® non-unique index Yes No N/A Array delete used All rows deleted Additional rows with same Primary Key may be deleted.
Oracle or DB2 non-unique index Yes Yes N/A Cursor controlled individual Primary Key delete All rows deleted Wrong rows with same Primary Key may be deleted.
Database is NOT Oracle or DB2 non-unique index Yes No >1 Multiple key delete All rows deleted Additional rows with same Primary Key may be deleted.
Database is NOT Oracle or DB2 non-unique index Yes No =1 Individual Primary Key delete – NOT cursor controlled Only first row in the duplicate Primary Key table is marked as deleted; other rows with the same Primary Key will be marked as "Not Found". Additional rows with same Primary Key may be deleted.
Database is NOT Oracle or DB2 non-unique index Yes Yes N/A Cursor controlled individual Primary Key delete All rows deleted Wrong rows with the same Primary Key may be deleted.
All databases no index Yes Yes N/A Cursor controlled delete using table scans All rows deleted Wrong rows with the same Primary Key may be deleted.
All databases no index Yes No N/A Individual Primary Key delete–NOT cursor controlled Only first row in the Duplicate Primary Key set is marked as deleted–other rows with the same Primary Key will be marked as "Not Found" Additional rows with the same Primary Key may be deleted.

Primary Key Index Analysis

Right-click a table name in the Table Access Strategy dialog and select Analyze Primary Key Index for this table or Analyze Primary Key Index for all tables to open the Primary Key Index Analysis dialog.

Primary Key Index Analysis dialog

This dialog provides information to help you determine which tables have an index on the primary key. Providing this information allows you to determine which access method is most effective for each table in the Delete Request, as Key Lookup requires an index on the primary key.

Additionally, you can create an index for tables in the Delete Request, if none exist.

Table
The name of the table in the Source File referenced by the Delete Request.
Status
The status of the index for the listed table(s). Possible status values are:
DBPK
An index on the database primary key is defined for the table and is used to access the table.
Unique
A unique index on an Optim primary key is defined for the table. The primary key columns may be indexed in any order.
Full
An index on an Optim primary key is defined for the table. The index includes all primary key columns at the beginning of the index, in any order, but can include additional columns.
Partial
An index on an Optim primary key is defined for the table. The index includes at least one primary key column at the beginning of the index, but can include additional columns.
None
No index exists with the necessary columns.
Indeterminate
Archive attempted to create DBMS indexes. Click Refresh to analyze the new data.
No PK
No primary key is defined for the table.
Table Not Found
The DB Alias referencing the database tables does not exist in the current Optim Directory.

Shortcut Menu

Using the shortcut menu, you can create needed DBMS indexes. Right-click the grid on the Primary Key Index Analysis dialog to display a shortcut menu.

If the status of an index is shown as Partial, or None, select from the following shortcut menu options to create necessary indexes:

Create Index
Select to display the Review SQL dialog. The display generates SQL statements for creating the DBMS index.
Create All Indexes
From the submenu, select the DB Alias for the table to be indexed, displaying the Review SQL dialog. The display generates SQL statements for creating the indexes.

Review Index SQL

Review Index SQL dialog

New indexes are named using the following syntax.

I_tablenam nnnnnnnn

I_
Prefix for index name.
tablenam
First eight letters of the table name.
nnnnnnnn
Eight-digit, sequential number.

Modify the name of the index, or other parts of the statements as necessary, then click Proceed to create the indexes.

The Browse Output dialog displays the results, after the SQL statements are executed.