In the Delete Request Editor, you can create, delete, or modify Delete Requests stored in the Optim™ Directory.
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.
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:
In addition to the standard commands on the File and Edit menus, you can select the following commands from the Tools menu:
The Table Access Strategy dialog allows you to modify the access method processing options for each table in the Delete Request.
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.
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.
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.
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.
Use the Key Lookup Limit for all tables dialog to set the values for all tables in the Delete Request at once.
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. |
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.
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.
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:
New indexes are named using the following syntax.
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.