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.
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