1 – Allocation
Allocation=n
Enables you to preallocate the generated output file. The
default allocation is 2048 blocks; when the file is closed it
is truncated to the actual length used.
If the value specified for the Allocation qualifier is less
than 65535, it becomes the new maximum for the Extend_Quantity
qualifier.
2 – Buffers
Buffers=n
Specifies the number of database buffers used for the unload
operation. If no value is specified, the default value for
the database is used. Although this qualifier might affect
the performance of the unload operation, the default number of
buffers for the database usually allows adequate performance.
3 – Commit Every
Commit_Every=n
Turns the selection query into a WITH HOLD cursor so that the
data stream is not closed by a commit. Refer to the Oracle Rdb7
SQL Reference Manual for more information about the WITH HOLD
clause.
4 – Compression
Compression[=options]
NoCompression
Data compression is applied to the user data unloaded to the
internal (interchange) format file. Table rows, null byte vector
and LIST OF BYTE VARYING data are compressed using either the LZW
(Lempel-Ziv-Welch) technique or the ZLIB algorithm developed by
Jean-loup Gailly and Mark Adler. Table metadata (column names and
attributes) are never compressed and the resulting file remains
a structured interchange file. Allowing compression allows the
result data file to be more compact, using less disk space and
permitting faster transmission over communication lines. This
file can also be processed using the RMU Dump Export command.
The default value is Nocompression.
This qualifier accepts the following optional keywords (ZLIB is
the default if no compression algorithm is specified):
o LZW
Selects the LZW compression technique.
o ZLIB
Selects the ZLIB compression technique. This can be modified
using the LEVEL option.
o LEVEL=number
ZLIB allows further tuning with the LEVEL option that accepts
a numeric level between 1 and 9. The default of 6 is usually
a good trade off between result file size and the CPU cost of
the compression.
o EXCLUDE_LIST[=(column-name,...)]
It is possible that data in LIST OF BYTE VARYING columns is
already in a compressed format (for instance images as JPG
data) and therefore need not be compressed by RMU Unload.
In fact, compression in such cases might actually cause
the output to grow. The EXCLUDE_LIST option will disable
compression for LIST OF BYTE VARYING columns. Specific column
names can be listed, or if omitted, all LIST OF BYTE VARYING
columns will be excluded from compression.
Only the user data is compressed. Therefore, additional
compression may be applied using various third party compression
tools, such as ZIP. It is not the goal of RMU to replace such
tools.
The qualifier RECORD_DEFINITION (or RMS_RECORD_DEF) is not
compatible /COMPRESSION. Note that the TRIM option for DELIMITED
format output can be used to trim trailing spaces from VARCHAR
data.
5 – Debug Options
Debug_Options={options}
The Debug_Options qualifier allows you to turn on certain debug
functions. The Debug_Options qualifier accepts the following
options:
o [NO]TRACE
Traces the qualifier and parameter processing performed by
RMU Unload. In addition, the query executed to read the table
data is annotated with the TRACE statement at each Commit
(controlled by Commit_Every qualifier). When the logical name
RDMS$SET_FLAGS is defined as "TRACE", then a line similar to
the following is output after each commit is performed.
~Xt: 2009-04-23 15:16:16.95: Commit executed.
The default is NOTRACE.
$RMU/UNLOAD/REC=(FILE=WS,FORMAT=CONTROL) SQL$DATABASE WORK_STATUS WS/DEBUG=TRACE
Debug = TRACE
* Synonyms are not enabled
Row_Count = 500
Message buffer: Len: 13524
Message buffer: Sze: 27, Cnt: 500, Use: 4 Flg: 00000000
%RMU-I-DATRECUNL, 3 data records unloaded.
o [NO]FILENAME_ONLY
When the qualifier Record_Definition=Format:CONTROL is used,
the name of the created unload file is written to the control
file (.CTL). When the keyword FILENAME_ONLY is specified, RMU
Unload will prune the output file specification to show only
the file name and type. The default is NOFILENAME_ONLY.
$RMU/UNLOAD/REC=(FILE=TT:,FORMAT=CONTROL) SQL$DATABASE WORK_STATUS WS/DEBUG=
FILENAME
--
-- SQL*Loader Control File
-- Generated by: RMU/UNLOAD
-- Version: Oracle Rdb X7.2-00
-- On: 23-APR-2009 11:12:46.29
--
LOAD DATA
INFILE 'WS.UNL'
APPEND
INTO TABLE "WORK_STATUS"
(
STATUS_CODE POSITION(1:1) CHAR NULLIF (RDB$UL_NB1 = '1')
,STATUS_NAME POSITION(2:9) CHAR NULLIF (RDB$UL_NB2 = '1')
,STATUS_TYPE POSITION(10:23) CHAR NULLIF (RDB$UL_NB3 = '1')
-- NULL indicators
,RDB$UL_NB1 FILLER POSITION(24:24) CHAR -- indicator for
STATUS_CODE
,RDB$UL_NB2 FILLER POSITION(25:25) CHAR -- indicator for
STATUS_NAME
,RDB$UL_NB3 FILLER POSITION(26:26) CHAR -- indicator for
STATUS_TYPE
)
%RMU-I-DATRECUNL, 3 data records unloaded.
o [NO]HEADER
This keyword controls the output of the header in the control
file. To suppress the header use NOHEADER. The default is
HEADER.
o APPEND, INSERT, REPLACE, TRUNCATE
These keywords control the text that is output prior to the
INTO TABLE clause in the control file. The default is APPEND,
and only one of these options can be specified.
6 – Delete Rows
Specifies that Oracle Rdb delete rows after they have been
unloaded from the database. You can use this qualifier with the
Commit_Every qualifier to process small batches of rows.
If constraints, triggers, or table protection prevent the
deletion of rows, the RMU Unload operation will fail. The Delete_
Rows qualifier cannot be used with non-updatable views, those
containing joins, or aggregates (union or group by).
7 – Error Delete
Noerror_Delete
Specifies whether the unload and record definition files should
be deleted on error. By default, the RMU Unload command deletes
the unload and record definition files if an unrecoverable error
occurs that causes an abnormal termination of the unload command
execution. Use the Noerror_Delete qualifier to retain the files.
If the Delete_Rows qualifier is specified, the default for this
qualifier is Noerror_Delete. This default is necessary to allow
you to use the unload and record definition files to reload the
data if an unrecoverable error has occurred after the delete of
some of the unloaded rows has been committed. Even if the unload
file is retained, it may not be able to reload the data using the
RMU Load command if the error is severe enough to prevent the RMU
error handler from continuing to access the unload file once the
error is detected.
If the Delete_Rows qualifier is not specified, the default is
Error_Delete.
8 – Extend Quantity
Extend_Quantity=number-blocks
Sets the size, in blocks, by which the unload file (.unl) can
be extended. The minimum value for the number-blocks parameter
is 1; the maximum value is 65535. If you provide a value for the
Allocation qualifier that is less than 65535, that value becomes
the maximum you can specify.
If you do not specify the Extend_Quantity qualifier, the default
block size by which .unl files can be extended is 2048 blocks.
9 – Fields
Fields=(column-name-list)
Specifies the column or columns of the table or view to be
unloaded from the database. If you list multiple columns,
separate the column names with a comma, and enclose the list
of column names within parentheses. This qualifier also specifies
the order in which the columns should be unloaded if that order
differs from what is defined for the table or view. Changing the
structure of the table or view could be useful when restructuring
a database or when migrating data between two databases with
different metadata definitions. The default is all the columns
defined for the table or view in the order defined.
10 – Flush
Flush=Buffer_End
Flush=On_Commit
Controls when internal RMS buffers are flushed to the unload
file. By default, the RMU Unload command flushes any data left
in the internal RMS file buffers only when the unload file is
closed. The Flush qualifier changes that behavior. You must use
one of the following options with the Flush qualifier:
o Buffer_End
The Buffer_End option specifies that the internal RMS buffers
be flushed to the unload file after each unload buffer has
been written to the unload file.
o On_Commit
The On_Commit option specifies that the internal RMS buffers
be flushed to the unload file just before the current unload
transaction is committed.
If the Delete_Rows qualifier is specified, the default for this
qualifier is Flush=On_Commit. This default is necessary to allow
you to use the unload and record definition files to reload the
data if an unrecoverable error has occurred after the delete of
some of the unloaded rows has been committed.
If the Delete_Rows qualifier is not specified, the default is to
flush the record definition buffers only when the unload files
are closed.
More frequent flushing of the internal RMS buffers will avoid the
possible loss of some unload file data if an error occurs and the
Noerror_Delete qualifer has been specified. Additional flushing
of the RMS internal buffers to the unload file can cause the RMU
Unload command to take longer to complete.
11 – Limit To
Limit_To=n
Nolimit_To
Limits the number of rows unloaded from a table or view. The
primary use of the Limit_To qualifier is to unload a data sample
for loading into test databases. The default is the Nolimit_To
qualifier.
12 – Optimize
Optimize={options}
Controls the query optimization of the RMU Unload command. You
must use one or more of the following options with the Optimize
qualifier:
o Conformance={Optional|Mandatory}
This option accepts two keywords, Optional or Mandatory, which
can be used to override the settings in the specified query
outline.
If the matching query outline is invalid, the
Conformance=Mandatory option causes the query compile, and
hence the RMU Unload operation, to stop. The query outline
will be one which either matches the string provided by
the Using_Outline or Name_As option or matches the query
identification.
The default behavior is to use the setting within the query
outline. If no query outline is found, or query outline usage
is disabled, then this option is ignored.
o Fast_First
This option asks the query optimizer to favor strategies that
return the first rows quickly, possibly at the expense of
longer overall retrieval time. This option does not override
the setting if any query outline is used.
This option cannot be specified at the same time as the Total_
Time option.
NOTE
Oracle Corporation does not recommend this optimization
option for the RMU Unload process. It is provided only
for backward compatibility with prior Rdb releases when
it was the default behavior.
o Name_As=query_name
This option supplies the name of the query. It is used to
annotate output from the Rdb debug flags (enabled using the
logical RDMS$SET_FLAGS) and is also logged by Oracle TRACE.
If the Using_Outline option is not used, this name is also
used as the query outline name.
o Selectivity=selectivity-value
This option allows you to influence the Oracle Rdb query
optimizer to use different selectivity values.
The Selectivity option accepts the following keywords:
- Aggressive - assumes a smaller number of rows is selected
compared to the default Oracle Rdb selectivity
- Sampled - uses literals in the query to perform preliminary
estimation on indices
- Default - uses default selectivity rules
The following example shows a use of the Selectivity option:
$RMU/UNLOAD/OPTIMIZE=(TOTAL_TIME,SELECTIVITY=SAMPLED) -
_$ SALES_DB CUSTOMER_TOP10 TOP10.UNL
This option is most useful when the RMU Unlaod command
references a view definition with a complex predicate.
o Sequential_Access
This option requests that index access be disabled for this
query. This is particularly useful for RMU Unload from views
against strictly partitioned tables. Strict partitioning is
enabled by the PARTITIONING IS NOT UPDATABLE clause on the
CREATE or ALTER STORAGE MAP statements. Retrieval queries
only use this type of partition optimization during sequential
table access.
This option cannot be specified at the same time as the Using_
Outline option.
o Total_Time
This option requests that total time optimization be applied
to the unload query. It does not override the setting if any
query outline is used.
In some cases, total time optimization may improve performance
of the RMU Unload command when the query optimizer favors
overall performance instead of faster retrieval of the first
row. Since the RMU Unload process is unloading the entire set,
there is no need to require fast delivery of the first few
rows.
This option may not be specified at the same time as the Fast_
First option. The Optimize=Total_Time behavior is the default
behavior for the RMU Unload command if the Optimize qualifier
is not specified.
o Using_Outline=outline_name
This option supplies the name of the query outline to be
used by the RMU Unload command. If the query outline does
not exist, the name is ignored.
This option may not be specified at the same time as the
Sequential_Access option.
13 – Record Definition
Record_Definition=[File=name,options]
Record_Definition=[Path=name,options]
Record_Definition=Nofile
Creates an RMS file containing the record structure definition
for the output file. The record description uses the CDO record
and field definition format. The default file extension is .rrd.
If you omit the File=name or Path=name option you must specify an
option.
The date-time syntax in .rrd files generated by this qualifier
changed in Oracle Rdb V6.0 to make the .rrd file compatible with
the date-time syntax support for Oracle CDD/Repository V6.1. The
RMU Unload command accepts both the date-time syntax generated
by the Record_Definition qualifier in previous versions of Oracle
Rdb and the syntax generated in Oracle Rdb V6.0 and later.
See the help entry for RRD_File_Syntax for more information on
.rrd files and details on the date-time syntax generated by this
qualifier.
The options are:
o Format=(Text)
If you specify the Format=(Text) option, Oracle RMU converts
all data to printable text before unloading it.
o Format=Control
The Format=Control option provides support for SQL*Loader
control files and portable data files. The output file
defaults to type .CTL.
FORMAT=CONTROL implicitly uses a portable data format as TEXT
rather than binary values. The unloaded data files are similar
to that generated by FORMAT=TEXT but includes a NULL vector to
represent NULL values ('1') and non-NULL values ('0').
The SQL*Loader control file uses this NULL vector to set NULL
for the data upon loading.
When FORMAT=CONTROL is used, the output control file and
associated data file are intended to be used with the Oracle
RDBMS SQL*Loader (sqlldr) command to load the data into an
Oracle RDBMS database table. LIST OF BYTE VARYING (SEGMENTED
STRING) columns are not unloaded.
The keywords NULL, PREFIX, SEPARATOR, SUFFIX, and TERMINATOR
only apply to DELIMITED_TEXT format and may not be used in
conjunction with the CONTROL keyword.
DATE VMS data is unloaded including the fractional seconds
precision. However, when mapped to Oracle DATE type in the
control file, the fractional seconds value is ignored. It
is possible to modify the generated control file to use the
TIMESTAMP type and add FF to the date edit mask.
NOTE
The RMU Load command does not support loading data using
FORMAT=Control.
o Format=XML
The Format=XML option causes the output Record_Definition file
type to default to .DTD (Document Type Definition). The output
file defaults to type .XML. The contents of the data file is
in XML format suitable for processing with a Web browser or
XML application.
If you use the Nofile option or do not specify the File or
Path keyword, the DTD is included in the XML output file
(internal DTD). If you specify a name with the File or Path
keyword to identify an output file, the file is referenced as
an external DTD from within the XML file.
The XML file contains a single table that has the name of the
database and multiple rows named <RMU_ROW>. Each row contains
the values for each column in printable text. If a value is
NULL, then the tag <NULL/> is displayed. Example 16 shows this
behavior.
NOTE
The RMU Load command does not support loading data using
FORMAT=XML.
o Format=(Delimited_Text [,delimiter-options])
If you specify the Format=Delimited_Text option, Oracle RMU
applies delimiters to all data before unloading it.
Note that DATE VMS dates are output in the collatable time
format, which is yyyymmddhhmmsscc. For example, March 20, 1993
is output as: 1993032000000000.
If the Format option is not used, Oracle RMU outputs data to
a fixed-length binary flat file. If the Format=Delimited_Text
options is not used, VARCHAR(n) strings are padded with blanks
when the specified string has fewer characters than n so that
the resulting string is n characters long.
Delimiter options (and their default values if you do not
specify delimiter options) are:
- Prefix=string
Specifies a prefix string that begins any column value in
the ASCII output file. If you omit this option, the column
prefix will be a quotation mark (").
- Separator=string
Specifies a string that separates column values of a row.
If you omit this option, the column separator will be a
single comma (,).
- Suffix=string
Specifies a suffix string that ends any column value in
the ASCII output file. If you omit this option, the column
suffix will be a quotation mark (").
- Terminator=string
Specifies the row terminator that completes all the column
values corresponding to a row. If you omit this option, the
row terminator will be the end of the line.
- Null=string
Specifies a string, which when found in the database
column, is unloaded as NULL in the output file.
The Null option can be specified on the command line as any
one of the following:
* A quoted string
* An empty set of double quotes ("")
* No string
The string that represents the null character must be
quoted on the Oracle RMU command line. You cannot specify a
blank space or spaces as the null character. You cannot use
the same character for the Null value and other Delimited_
Text options.
NOTE
The values of each of the strings specified in the
delimiter options must be enclosed within quotation
marks. Oracle RMU strips these quotation marks while
interpreting the values. If you want to specify a
quotation mark (") as a delimiter, specify a string
of four quotation marks. Oracle RMU interprets four
quotation marks as your request to use one quotation
mark as a delimiter. For example, Suffix = """".
Oracle RMU reads these quotation marks as follows:
o The first quotation mark is stripped from the string.
o The second and third quotation mark are interpreted
as your request for one quotation mark (") as a
delimiter.
o The fourth quotation mark is stripped.
This results in one quotation mark being used as a
delimiter.
Furthermore, if you want to specify a quotation mark as
part of the delimited string, you must use two quotation
marks for each quotation mark that you want to appear in
the string. For example, Suffix = "**""**" causes Oracle
RMU to use a delimiter of **"**.
o Trim=option
If you specify the Trim=option keyword, leading and/or
trailing spaces area removed from each output field. Option
supports three keywords:
o TRAILING - trailing spaces will be trimmed from CHARACTER
and CHARACTER VARYING (VARCHAR) data that is unloaded.
This is the default setting if only the TRIM option is
specified.
o LEADING - leading spaces will be trimmed from CHARACTER and
CHARACTER VARYING (VARCHAR) data that is unloaded.
o BOTH - both leading and trailing spaces will be trimmed.
When the Record_Definition qualifier is used with load or unload
operations, and the Null option to the Delimited_Text option
is not specified, any null values stored in the rows of the
tables being loaded or unloaded are not preserved. Therefore,
if you want to preserve null values stored in tables and you are
moving data within the database or between databases, specify the
Null option with Delimited_Text option of the Record_Definition
qualifier.
14 – Reopen Count
Reopen_Count=n
The Reopen_Count=n qualifier allows you to specify how many
records are written to an output file. The output file will
be re-created (that is, a new version of the file will be
created) when the record count reaches the specified value.
The Reopen_Count=n qualifier is only valid when used with the
Record_Definition or Rms_Record_Def qualifiers.
15 – Rms Record Def
Rms_Record_Def=(File=name[,options])
Rms_Record_Def=(Path=name[,options])
Synonymous with the Record_Definition qualifier. See the
description of the Record_Definition qualifier.
16 – Row Count
Row_Count=n
Specifies that Oracle Rdb buffer multiple rows between the Oracle
Rdb server and the RMU Unload process. The default value for n
is 500 rows; however, this value should be adjusted based on
working set size and length of unloaded data. Increasing the row
count may reduce the CPU cost of the unload operation. For remote
databases, this may significantly reduce network traffic for
large volumes of data because the buffered data can be packaged
into larger network packets.
The minimum value you can specify for n is 1. The default row
size is the value specified for the Commit_Every qualifier or
500, whichever is smaller.
17 – Statistics Interval
Statistics_Interval=seconds
Specifies that statistics are to be displayed at regular
intervals so that you can evaluate the progress of the unload
operation.
The displayed statistics include:
o Elapsed time
o CPU time
o Buffered I/O
o Direct I/O
o Page faults
o Number of records unloaded since the last transaction was
committed
o Number of records unloaded so far in the current transaction
If the Statistics_Interval qualifier is specified, the seconds
parameter is required. The minimum value is 1. If the unload
operation completes successfully before the first time interval
has passed, you receive only an informational message on the
number of files unloaded. If the unload operation is unsuccessful
before the first time interval has passed, you receive error
messages and statistics on the number of records unloaded.
At any time during the unload operation, you can press Ctrl/T to
display the current statistics.
18 – Transaction Type
Transaction_Type[=(transaction_mode,options,...)]
Allows you to specify the transaction mode, isolation level, and
wait behavior for transactions.
Use one of the following keywords to control the transaction
mode:
o Automatic
When Transaction_Type=Automatic is specified, the transaction
type depends on the current database settings for snapshots
(enabled, deferred, or disabled), transaction modes available
to this user, and the standby status of the database.
Automatic mode is the default.
o Read_Only
Starts a Read_Only transaction.
o Exclusive
Starts a Read_Write transaction and reserves the table for
Exclusive_Read.
o Protected
Starts a Read_Write transaction and reserves the table for
Protected_Read.
o Shared
Starts a Read_Write transaction and reserves the table for
Shared_Read.
Use one of the following options with the keyword Isolation_
Level=[option] to specify the transaction isolation level:
o Read_Committed
o Repeatable_Read
o Serializable. Serializable is the default setting.
Refer to the SET TRANSACTION statement in the Oracle Rdb SQL
Reference Manual for a complete description of the transaction
isolation levels.
Specify the wait setting by using one of the following keywords:
o Wait
Waits indefinitely for a locked resource to become available.
Wait is the default behavior.
o Wait=n
The value you supply for n is the transaction lock timeout
interval. When you supply this value, Oracle Rdb waits n
seconds before aborting the wait and the RMU Unload session.
Specifying a wait timeout interval of zero is equivalent to
specifying Nowait.
o Nowait
Does not wait for a locked resource to become available.
19 – Virtual Fields
Virtual_Fields(=[No]Automatic,[No]Computed_By)
Novirtual_Fields
The Virtual_Fields qualifier unloads any AUTOMATIC or COMPUTED
BY fields as real data. This qualifier permits the transfer of
computed values to another application. It also permits unloading
through a view that is a union of tables or that is comprised
of columns from multiple tables. For example, if there are two
tables, EMPLOYEES and RETIRED_EMPLOYEES, the view ALL_EMPLOYEES
(a union of EMPLOYEES and RETIRED_EMPLOYEES tables) can be
unloaded.
The Novirtual_Fields qualifier is the default, which is
equivalent to the Virtual_Fields=[Noautomatic,Nocomputed_By)
qualifier.
If you specify the Virtual_Fields qualifier without a keyword,
all fields are unloaded, including COMPUTED BY and AUTOMATIC
table columns, and calculated VIEW columns.
If you specify the Virtual_Fields=(Automatic,Nocomputed_By)
qualifier or the Virtual_Fields=Nocomputed_By qualifier, data
is only unloaded from Automatic fields. If you specify the
Virtual_Fields=(Noautomatic,Computed_By) qualifier or the
Virtual_Fields=Noautomatic qualifier, data is only unloaded from
Computed_By fields.