General Tab, Delete Request Editor

Use the General tab to specify the Source File and Control File used by the Delete Request, and to specify the processing options.

Delete Request Editor with General as the active tab. The elements of the General tab are described as follows.

Source File Type

Select Extract or Archive, to indicate the type of source file used.

Source File

Enter the name of the Extract or Archive File that contains the data to delete. Extract Files have a .xf extension by default. Archive Files have a .af extension by default. If you do not specify a path for either of these files, the process uses the default Data Directory defined in Personal Options.

Control File

Enter the name of a file is used during the process to track the success or failure of each row in the source file. Control Files have a .cf extension by default.

If you specify a file that already exists, a confirmation dialog prompts you to confirm that you want to overwrite the file when you run the Delete Request. Use a Personal Option to disable this feature.

Note: You can browse the contents of an Extract, Archive, or Control File by clicking Utilities > Browse from the menu or by right‑clicking and clicking Browse from the shortcut menu. For details, refer to the Common Elements Manual.

Process Options

Use the following options to manage the Delete Process:

Commit Frequency

Enter the number of rows to process before committing the changes to the database. Clear this box to use the Maximum Commit Frequency specified in Product Options. (Refer to the Installation and Configuration Guide .) Frequent commits keep locking to a minimum, but might slow the process.

If the process ends abnormally, click Utilities > Restart/Retry from the menu to resume processing from the last commit point. Refer to the Common Elements Manual.

Lock Tables

Select this check box to lock each database table until all rows for that table are processed. Lock tables to ensure that other database activity does not interfere with the process and to prevent other users from accessing tables involved in the process. Permission to lock tables is not available if denied in Product Options.

Review Archive Delete List

Select this check box to display the Delete After Archive Specifications dialog during processing. Use this dialog to review delete options for tables in an Archive File and select tables from which rows are deleted. (Refer to Delete Data.)

Generate Statistical Report

Statistical information may indicate whether you can improve performance by overriding the default method (scan or key lookup) for accessing a table. (For details, see the Common Elements Manual. )This check box is selected by default to include statistical information in the Delete Process Report. (For details, see Statistical Information.)

Discard Row Limit

Enter the maximum number of rows that can be discarded, up to 999999999. The process stops after the specified number is reached and all rows in the array are processed. To allow an unlimited number of rows to be discarded, specify zero (0) or leave blank.

Note: Discard Row Limit is an approximate value due to array processing for the Delete Process. For example, assume the discard limit is set to 50 and the array supports 200 rows for a given table. If the first 50 rows fail, the DBMS continues to process the other 150 rows in the array, before evaluating the discard limit. (The number of rows that can be deleted via one call to the DBMS depends on the size of the row.)
Compare Row Contents

Select this check box to compare rows of data in the source file with rows in the database and delete only rows from the database that exactly match rows in the source file. Rows that do not match are discarded and noted in the Control File. If you clear this check box for a table with a unique primary key, the row comparison is not performed.

For a table with a non-unique primary key, clearing this check box has no effect. This is the default. Its purpose is to eliminate the risk of losing updates to the data in the database since the Archive or Extract was performed. You can override this default using Tools > Edit Strategy to display the Table Access Strategy panel. (For details see Using the Delete Request Editor..)

Include LOB columns in row comparison

This option is available only when Compare Row Contents is selected. Select this check box to include LOB columns in the row comparison. Clear this check box to exclude LOB columns from the row comparison. (This check box is selected by default.)

Clearing these check boxes might improve performance significantly. However, you risk losing any updates to the data in the database since the Archive or Extract was performed. Also, you will not receive any warnings (for example, a warning of a “not found” condition if a row in the source file does not exist in the DBMS).
Verify Table Structure in Database

Select this check box to verify that, for each table, all columns in the source file match the columns in the database before deleting a row. (If columns do not match, processing is canceled, and an error message is written to the Delete Process Report.) When the source file is an Archive File, this check box is unavailable, and selected by default. Columns match when:

  • The number of columns is the same for each table.
  • The names of the columns are the same.
  • The column attributes are compatible.
Database Connections

Increase the number of concurrent database connections for the Delete Process. Increasing database connections improves performance when processing large quantities of data (1000 or more rows) by allowing multiple threads to delete rows concurrently.

Only tables with unique indexes are processed in parallel and only if Lock Tables is not selected. Also, tables from an Oracle or Sybase ASE database not processed in parallel when the Access Method is User Forced Scan and some tables may not be processed in parallel due to database RI constraints.

To increase the maximum number of connections, select an even number from 2 to the maximum specified in Personal Options.