Remove Rows Process

The File Maintenance Facility Remove Rows process selectively removes rows from a registered Archive File, on the basis of selection criteria in a command file.

The Remove Rows process generates the following files when an Archive File is processed:

Command File Syntax

The command file contains selection criteria that identify the rows to be removed from processed Archive Files.

A command may appear several times in a command file. A single command can span multiple lines for ease of maintenance (except where noted otherwise). A group of commands ending with a RUN command represents a “process block.” Except for the RUN command, all commands in a process block can appear in any order. See Example for a sample command file.

A command file may contain one or more process blocks in the following format:
[// comment ]
[ OPTIMDIR optimdirectory ]
AF { arcservername | (local) } archivefilename1[,archivefilenamen]
[ STARTTABLE dbalias.cid.tablename ]

[ SELCRIT dbalias.cid.tablename sqlwhereclause ]
...
[ SELCRIT dbalias.cid.tablename sqlwhereclause ]

[ ONERROR { CONTINUE | QUITPROCESS | QUITALL }]
[ RETAINORIGINAL { YES | NO }]
RUN REMOVEROWS
//
At the beginning of a line, indicates that the text following the slashes is a comment. Enter comment text on one line, anywhere in the command file.
OPTIMDIR
Optim™ Directory to register and/or update Archive Files. The Optim Directory provides complete Archive File names in case a wild card name is specified. (The default is the current Optim Directory.)
AF
The Archive Files to be processed. (Required)
filenames
Server name followed by a comma-separated list of Archive File names or patterns with wildcards (% for any number of characters). If a file name contains spaces, use quotes around the name. The file(s) must exist in the Archive Directory. You can use multiple occurrences of this command to process Archive Files on multiple servers.
STARTTABLE
Sets the Start Table for the data processed in the process block. The Start Table determines which rows are selected from the Archive File(s) in the block.
tablename
A three-part table name. The default is the Start Table in the Access Definition used to create the Archive File.
SELCRIT
Identifies rows to be removed from processed Archive Files. (Required)

A process block can include multiple SELCRIT commands. Multiple SELCRIT commands can include the same table name. In this case, the process combines the WHERE clauses with an OR.

The Subset Extract process handles selection criteria as an “intersection” of the data. Suppose the Archive File contains the tables CUSTOMERS (parent) and ORDERS (child), and the selection criteria are:

CUSTOMERS: CUST_ID = ‘00486' 
ORDERS: ORDER_ID = 111

The CUSTOMERS row with CUST_ID = ‘00486' has two orders: 111 and 1164. Based on the selection criteria, only the ORDERS row with Order ID 111 would be included.

The process maintains relational integrity. Relationship Options 1 and 2 are both disabled.

criteria
A three-part table name (including the Start Table) followed by a WHERE clause. (Command, table name, and WHERE clause must all be on the same line.)
ONERROR
Indicates how Optim will react to an error.
CONTINUE
Processing continues with the next file, if possible.
QUITPROCESS
Processing ends with the current process and proceeds to the next.
QUITALL
Processing ends immediately. (Default)
RETAINORIGINAL
Indicates if Optim should keep the original Archive File and create a new Archive File with a unique sequence number appended to the file name and a new GUID/AFID.

If an Archive File has an index file, the index file is processed after the Archive File is processed successfully. If the Archive File is overwritten, then the index file is overwritten. If a new Archive File is created, then the original index file is retained and a new index file is created.

YES
(Default) Optim creates a new Archive File with a new GUID/AFID and an index file. It registers the new Archive File. The process places the generated files in the same directory as the original files and the new file names are derived from the original file names, as follows.
Note: A number is embedded in the filename to ensure uniqueness in case a user runs the Remove Rows process more than once for an Archive File.
New Archive File
<orig. archive file name>fmf_remove<number>.af
New Index File
<orig. index file name>fmf_remove<number>.afx
NO
Optim creates a temporary Archive File and, at the end of the process, deletes the original Archive File and changes the temporary Archive File name to the original file name. If the Archive File is overwritten, its associated retention information (from the Storage Profile) is updated. The original GUID/AFID is kept.
RUN
Identifies operation to run. The RUN command and its value indicate the end of a process block. Any commands following the RUN command are part of the next process block. (Required.)
REMOVEROWS
Removes rows specified in selection criteria.

Example

This example illustrates how you can remove rows from Archive Files using a command line process.

The following command line removes rows from Archive Files specified in command.txt, the command file. The process report is written to filemaint.txt.
PR0CMND /FMF
OUTPUT=C:\RTDATA\filemaint.txt
@command.txt

The following illustrates the contents of a command file used by the Remove Rows process.

//This is the first block.
OPTIMDIR PSTDJHDIR
AF OPTIMSVR1 C:/OPTIM/DATA/PAYROLL%.AF,C:/OPTIM/DATA/GENERAL_LEDGER.AF
SELCRIT DBMS.OPTUSER.CUSTOMERS LASTNAME=“henderson” and FIRSTNAME=“dave”
SELCRIT DBMS.OPTUSER.CUSTOMERS LASTNAME=“skippy”
SELCRIT DBMS.OPTUSER.CUSTOMERS LASTNAME=“VanDorn”
RUN REMOVEROWS
//This begins the second block.
OPTIMDIR PSTDJHDIR2
AF (LOCAL) %ACCOUNTS_PAYABLE.AF,%ACCOUNTS_RECEIVABLE.AF
SELCRIT DBMS.OPTUSER.CUSTOMERS COLNAME1=‘XXXE'
RUN REMOVEROWS