Use the MOVE_DELETE Batch utility statement to delete data
that has been placed in an Extract File from the DB2® database.
This statement uses DB2 SQL statements to perform the delete. You
can use keywords to provide parameters similar to those for the online
Delete Process. For most processes, only a few keywords are needed:
those to control processing and others to allocate an output file.
Keywords to control processing are shown in the statement syntax.
For keywords to allocate an output file, see the Batch Utilities
Guide, File Allocation Parameters section.
MOVE_DELETE
EXTRACT_FILE dsname
CONTROL_FILE ( File Allocation Parameters )
[ DELETE_LOCK { YES | NO } ]
[ DELETE_COMMIT_ROWS n ]
[ DELETE_COMMIT_MINUTES n ]
[ DELETE_DISCARD n ]
[ COMPARE_ROW { YES | NO } ]
[ RESTART { YES | NO } ]
[ DEFAULT_KEY_LIMIT n ]
[ ACCESS_METHOD ( cid.tablename, { K | S | E }, key limit ) ]
[ REPORT_LEVEL { DETAIL | SUMMARY } ]
- EXTRACT_FILE
- The name of the Extract File containing the rows to be deleted.
EXTRACT_NAME must be included in the MOVE_DELETE statement.
- dsname
- The fully qualified name of the Extract File.
- CONTROL_FILE
- Name of the Control File and its allocation parameters. This keyword
is required to execute the Delete Process. To name and allocate the
Control File, use the keywords shown in the Batch Utilities
Guide, File Allocation Parameters section.
Processing Keywords Use the following keywords
to provide processing options for the MOVE_Delete Process.
- DELETE_LOCK
- Indicate whether to lock tables during the Delete Process. Locking
tables ensures other database activity does not interfere with the
Delete Process. However, it will prevent other users from accessing
the table.
- YES
- Lock tables. (This setting causes an error if site options prevent
a user from locking tables.)
- NO
- Do not lock tables (default).
- DELETE_COMMIT_ROWS
- The frequency of commits in rows for the Delete Process. The commit
points affect the starting point in case of a Restart. Frequent commits
keep page locks to a minimum. If you omit both DELETE_COMMIT_ROWS
and DELETE_COMMIT_MINUTES, the site limit is used.
- n
- Commit each n number of rows where n is
a value from 1 through the Site Options Commit Frequency Rate, inclusive.
Note: If DELETE_LOCK is set to YES,
DELETE_COMMIT_ROWS and DELETE_COMMIT_MINUTES are ignored. A commit
is performed as processing for each table is completed.
- DELETE_COMMIT_MINUTES
- The frequency of commits in minutes for the Delete Process. If
you omit DELETE_COMMIT_MINUTES, DELETE_COMMIT_ROWS determines the
commit frequency.
- n
- Commit every n minutes, where n is
1 through 1440.
- DELETE_DISCARD
- The maximum number of rows that can be discarded when deleting
rows, before terminating the process. If the limit is met, the process
is terminated. You can use Restart to begin the Delete Process again
at the termination point.
- n
- Specify a value in the range 1 to 4,294,967,295.
- blank
- No limit on discarded rows.
- COMPARE_ROW
- Indicate whether the Delete Process compares rows of data in the
Extract File with rows in the database prior to deletion.
- YES
- Rows are deleted from the database only if they exactly match
rows in the Extract File. Rows that do not exactly match are discarded
and noted in the Control File. (This setting causes an error if the Compare
Row Contents site option is not set to USER.)
- NO
- Row comparison is not performed (default). This may improve performance
significantly; however, you risk losing any updates to the data in
the database since the Extract Process was performed.
- RESTART
- Indicate whether to restart or retry the delete portion of a Move Process
if a processing failure occurs. Optim automatically
determines whether to perform a restart or a retry.
- YES
- Restart or retry the delete and, optionally, respecify other MOVE_DELETE
keywords.
- NO
- Do not restart or retry the delete (default).
Note: You can also restart or retry
the process by adding the RESTART operand to the PARM field on the
batch EXEC statement. Use a blank to separate the RESTART operand
from the previous operand in the PARM field.
- DEFAULT_KEY_LIMIT
- The maximum number of keys to be used at one time when using key
lookup to process a table. Applies only if a limit was not specified
in the Access Definition.
- n
- Specify a value in the range 1 - 100. (Default = 1)
The following conditions must be true to process multiple
keys at one time when deleting rows: - An index on the primary key is defined for the
table.
- COMPARE_ROW keyword is NO.
- Row-level Archive Actions are not defined for the
Delete Process (e.g., Before Delete of Row).
- The table is not a parent in a DBMS relationship.
- ACCESS_METHOD
- Indicate how to access the rows in a DB2 table.
You may specify this keyword once for each table to be processed.
If you omit it for a table, Optim™ determines
how to access the rows in that table. You must specify the parameters
within parentheses, separated by commas, and in the following order:
- cid.tablename
- The table name
- K
- Use key lookup
- S
- Use table scan
- E
- Optim determines the access
method.
- key limit
- Maximum number of keys to be specified in an SQL statement when
the access method is K. Specify a number between 1 and 100. You may
omit this parameter if the access method is S.
- REPORT_LEVEL
- The level of detail in the process report.
- DETAIL
- Produce a detailed report (default).
- SUMMARY
- Produce a summary report.
Example
Use
the following statement to delete data from the DB2 database that had been placed in the Extract
File, DEPT14.EXTRACT.TRADES.
MOVE_DELETE
EXTRACT_NAME DEPT14.EXTRACT.TRADES
CONTROL_FILE (DSNAME DEPT14.CTRLFILE MODE REP)
DELETE_COMMIT_ROWS 5000
DELETE_DISCARD 100