Copies the data from a specified table or view of the database
into one of the following:
o A specially structured file that contains both the data and
the metadata (.unl).
o An RMS file that contains data only (.unl). This file is
created when you specify the Record_Definition qualifier.
(The Record_Definition qualifier also creates a second file,
with file extension .rrd, that contains the metadata.)
Data from the specially structured file can be reloaded by using
the RMU Load command only. Data from the RMS file can be reloaded
using the RMU Load command or by using an alternative utility
such as is offered by DATATRIEVE.
1 – Description
The RMU Unload command copies data from a specified table or view
and places it in a specially structured file or in an RMS file.
Be aware that the RMU Unload command does not remove data from
the specified table; it merely makes a copy of the data.
The RMU Unload command can be used to do the following:
o Extract data for an application that cannot access the Oracle
Rdb database directly.
o Create an archival copy of data.
o Perform restructuring operations.
o Sort data by defining a view with a sorted-by clause, then
unloading that view.
The specially structured files created by the RMU Unload command
contain metadata for the table that was unloaded. The RMS files
created by the RMU Unload command contain only data; the metadata
can be found either in the data dictionary or in the .rrd file
created using the Record_Definition qualifier. Specify the
Record_Definition qualifier to exchange data with an application
that uses RMS files.
The LIST OF BYTE VARYING (segmented string) data type cannot be
unloaded into an RMS file; however, it can be unloaded into the
specially structured file type.
Data type conversions are valid only if Oracle Rdb supports the
conversion.
The RMU Unload command executes a read-only transaction to gather
the metadata and user data to be unloaded. It is compatible with
all operations that do not require exclusive access.
2 – Format
(B)0[mRMU/Unload root-file-spec table-name output-file-name
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Allocation=n x /Allocation=2048
/Buffers=n x See description
/Commit_Every=n x None
/[No]Compression[=options] x /Nocompression
/Debug_Options={options} x See description
/Delete_Rows x None
/[No]Error_Delete x See description
/Extend_Quantity=number-blocks x /Extend_Quantity=2048
/Fields=(column-name-list) x See description
/Flush={Buffer_End|On_Commit} x See description
/[No]Limit_To=n x /Nolimit_To
/Optimize={options} x None
/Record_Definition={([No]File|Path)=name,options} x See description
/Reopen_Count=n x None
/Row_Count=n x See description
/Statistics_Interval=seconds x See description
/Transaction_Type[=(transaction_mode,options...)] x See description
/[No]Virtual_Fields[=[No]Automatic,[No]Computed_By] x /Novirtual_Fields
3 – Parameters
3.1 – root-file-spec
The root file specification of the database from which tables or
views will be unloaded. The default file extension is .rdb.
3.2 – table-name
The name of the table or view to be unloaded, or its synonym.
3.3 – output-file-name
The destination file name. The default file extension is .unl.
4 – Command Qualifiers
4.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.
4.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.
4.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.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.
4.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.
4.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).
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
4.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.
5 – Usage Notes
o To use the RMU Unload command for a database, you must have
the RMU$UNLOAD privilege in the root file access control
list (ACL) for the database or the OpenVMS SYSPRV or BYPASS
privilege. You must also have the SQL SELECT privilege to the
table or view being unloaded.
o For tutorial information on the RMU Unload command, refer to
the Oracle Rdb Guide to Database Design and Definition.
o Detected asynchronous prefetch should be enabled to achieve
the best performance of this command. Beginning with Oracle
Rdb V7.0, by default, detected asynchronous prefetch is
enabled. You can determine the setting for your database by
issuing the RMU Dump command with the Header qualifier.
If detected asynchronous prefetch is disabled, and you do not
want to enable it for the database, you can enable it for your
Oracle RMU operations by defining the following logicals at
the process level:
$ DEFINE RDM$BIND_DAPF_ENABLED 1
$ DEFINE RDM$BIND_DAPF_DEPTH_BUF_CNT P1
P1 is a value between 10 and 20 percent of the user buffer
count.
o You can unload a table from a database structured under
one version of Oracle Rdb and load it into the same table
of a database structured under another version of Oracle
Rdb. For example, if you unload the EMPLOYEES table from
a mf_personnel database created under Oracle Rdb V6.0, you
can load the generated .unl file into an Oracle Rdb V7.0
database. Likewise, if you unload the EMPLOYEES table from
a mf_personnel database created under Oracle Rdb V7.0, you
can load the generated .unl file into an Oracle Rdb V6.1
database. This is true even for specially formatted binary
files (created with the RMU Unload command without the Record_
Definition qualifier). The earliest version into which you can
load a .unl file from another version is Oracle Rdb V6.0.
o The Fields qualifier can be used with indirect file
references. When you use the Fields qualifier with an indirect
file reference in the field list, the referenced file is
written to SYS$OUTPUT if you have used the DCL SET VERIFY
command. See the Indirect-Command-Files help entry for more
information.
o To view the contents of the specially structured .unl file
created by the RMU Unload command, use the RMU Dump Export
command.
o To preserve the null indicator in a load or unload operation,
use the Null option with the Record_Definition qualifier.
Using the Record_Definition qualifier without the Null option
replaces all null values with zeros; this can cause unexpected
results with computed-by columns.
o Oracle RMU does not allow you to unload a system table.
o The RMU Unload command recognizes character set information.
When you unload a table, RMU Unload transfers information
about the character set to the record definition file.
o When it creates the record definition file, the RMU Unload
command preserves any lowercase characters in table and column
names by allowing delimited identifiers. Delimited identifiers
are user-supplied names enclosed within quotation marks ("").
By default, RMU Unload changes any table or column (field)
names that you specify to uppercase. To preserve lowercase
characters, use delimited identifiers. That is, enclose the
names within quotation marks. In the following example, RMU
Unload preserves the uppercase and lowercase characters in
"Last_Name" and "Employees":
$ RMU/UNLOAD/FIELDS=("Last_name",FIRST_NAME) TEST "Employees" -
_$ TEST.UNL
NOTE
The data dictionary does not preserve the distinction
between uppercase and lowercase identifiers. If you use
delimited identifiers, you must be careful to ensure that
the record definition does not include objects with names
that are duplicates except for the case. For example,
the data dictionary considers the delimited identifiers
"Employee_ID" and "EMPLOYEE_ID" to be the same name.
o Oracle RMU does not support the multischema naming convention
and returns an error if you specify one. For example:
$ RMU/UNLOAD CORPORATE_DATA ADMINISTRATION.PERSONNEL.EMPLOYEES -
_$ OUTPUT.UNL
%RMU-E-OUTFILDEL, Fatal error, output file deleted
-RMU-F-RELNOTFND, Relation (ADMINISTRATION.PERSONNEL.EMPLOYEES) not found
When using a multischema database, you must specify the SQL
stored name for the database object.
For example, to find the stored name that corresponds to the
ADMINISTRATION.PERSONNEL.EMPLOYEES table in the corporate_data
database, issue an SQL SHOW TABLE command, as follows:
SQL> SHOW TABLE ADMINISTRATION.PERSONNEL.EMPLOYEES
Information for table ADMINISTRATION.PERSONNEL.EMPLOYEES
Stored name is EMPLOYEES
.
.
.
Then to unload the table, issue the following RMU Unload
command:
$ RMU/UNLOAD CORPORATE_DATA EMPLOYEES OUTPUT.UNL
o If the Transaction_Type qualifier is omitted, a Read_Only
transaction is started against the database. This behavior is
provided for backward compatibility with prior Rdb releases.
If the Transaction_Type qualifier is specified without a
transaction mode, the default value Automatic is used.
o If the database has snapshots disabled, Oracle Rdb defaults to
a READ WRITE ISOLATION LEVEL SERIALIZABLE transaction. Locking
may be reduced by specifying Transaction_Type=(Automatic), or
Transaction_Type=(Shared,Isolation_Level=Read_Committed).
o If you use a synonym to represent a table or a view, the RMU
Unload command translates the synonym to the base object
and processes the data as though the base table or view had
been named. This implies that the unload interchange files
(.UNL) or record definition files (.RRD) that contain the
table metadata will name the base table or view and not use
the synonym name. If the metadata is used against a different
database, you may need to use the Match_Name qualifier to
override this name during the RMU load process.
6 – Examples
Example 1
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database. The data is stored in names.unl.
$ RMU/UNLOAD -
_$ /FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
Example 2
The following command unloads the EMPLOYEES table from the
mf_personnel database and places the data in the RMS file,
names.unl. The names.rrd file contains the record structure
definitions for the data in names.unl.
$ RMU/UNLOAD/RECORD_DEFINITION=FILE=NAMES.RRD MF_PERSONNEL -
_$ EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
Example 3
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database and accepts the default values for delimiters, as shown
by viewing the names.unl file:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
-$ /RECORD_DEFINITION=(FILE=NAMES, FORMAT=DELIMITED_TEXT) -
-$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
$ !
$ ! TYPE the names.unl file to see the effect of the RMU Unload
$ ! command.
$ !
$ TYPE NAMES.UNL
"00164","Toliver "
"00165","Smith "
"00166","Dietrich "
"00167","Kilpatrick "
"00168","Nash "
.
.
.
Example 4
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database and specifies the asterisk (*) character as the string
to mark the beginning and end of each column (the prefix and
suffix string):
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /RECORD_DEFINITION=(FILE=NAMES, -
_$ FORMAT=DELIMITED_TEXT, SUFFIX="*", -
_$ PREFIX="*") -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
$ !
$ ! TYPE the names.unl file to see the effect of the RMU Unload
$ ! command.
$ !
$ TYPE NAMES.UNL
*00164*,*Toliver *
*00165*,*Smith *
*00166*,*Dietrich *
*00167*,*Kilpatrick *
*00168*,*Nash *
*00169*,*Gray *
*00170*,*Wood *
*00171*,*D'Amico *
.
.
.
Example 5
The following command unloads all column values from the
EMPLOYEES table of the mf_personnel database, and specifies the
Format=Text option of the Record_Definition qualifier. Oracle RMU
will convert all the data to printable text, as can be seen by
viewing the text_output.unl file:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=TEXT_RECORD,FORMAT=TEXT) -
_$ MF_PERSONNEL EMPLOYEES TEXT_OUTPUT
%RMU-I-DATRECUNL, 100 data records unloaded.
$ !
$ ! TYPE the text_output.unl file to see the effect of the RMU Unload
$ ! command.
$ !
$ TYPE TEXT_OUTPUT.UNL
00164Toliver Alvin A146 Parnell Place
Chocorua NH03817M19470328000000001
00165Smith Terry D120 Tenby Dr.
Chocorua NH03817M19540515000000002
00166Dietrich Rick 19 Union Square
Boscawen NH03301M19540320000000001
.
.
.
Example 6
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database and requests that statistics be displayed on the
terminal at 2-second intervals:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /STATISTICS_INTERVAL=2 -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
Example 7
The following example unloads a subset of data from the EMPLOYEES
table, using the following steps:
1. Create a temporary view on the EMPLOYEES table that includes
only employees who live in Massachusetts.
2. Use an RMU Unload command to unload the data from this view.
3. Delete the temporary view.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> CREATE VIEW MA_EMPLOYEES
cont> (EMPLOYEE_ID,
cont> LAST_NAME,
cont> FIRST_NAME,
cont> MIDDLE_INITIAL,
cont> STATE,
cont> STATUS_CODE)
cont> AS SELECT
cont> E.EMPLOYEE_ID,
cont> E.LAST_NAME,
cont> E.FIRST_NAME,
cont> E.MIDDLE_INITIAL,
cont> E.STATE,
cont> E.STATUS_CODE
cont> FROM EMPLOYEES E
cont> WHERE E.STATE='MA';
SQL> COMMIT;
SQL> EXIT;
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=MA_EMPLOYEES,FORMAT=DELIMITED_TEXT) -
_$ MF_PERSONNEL MA_EMPLOYEES MA_EMPLOYEES.UNL
%RMU-I-DATRECUNL, 9 data records unloaded.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> DROP VIEW MA_EMPLOYEES;
SQL> COMMIT;
Example 8
The following example shows that null values in blank columns
are not preserved unless the Null option is specified with the
Delimited_Text option of the Record_Definition qualifier:
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Create the NULL_DATE table:
SQL> CREATE TABLE NULL_DATE
cont> (COL1 VARCHAR(5),
cont> DATE1 DATE,
cont> COL2 VARCHAR(5));
SQL> --
SQL> -- Store a row that does not include a value for the DATE1
SQL> -- column of the NULL_DATE table:
SQL> INSERT INTO NULL_DATE
cont> (COL1, COL2)
cont> VALUES ('first','last');
1 row inserted
SQL> --
SQL> COMMIT;
SQL> --
SQL> -- The previous SQL INSERT statement causes a null value to
SQL> -- be stored in NULL_DATE:
SQL> SELECT * FROM NULL_DATE;
COL1 DATE1 COL2
first NULL last
1 row selected
SQL> --
SQL> DISCONNECT DEFAULT;
SQL> EXIT;
$ !
$ ! In the following RMU Unload command, the Record_Definition
$ ! qualifier is used to unload the row with the NULL value, but
$ ! the Null option is not specified:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=NULL_DATE,FORMAT=DELIMITED_TEXT) -
_$ MF_PERSONNEL NULL_DATE NULL_DATE
%RMU-I-DATRECUNL, 1 data records unloaded.
$ !
$ ! The null_date.unl file created by the previous unload
$ ! operation does not preserve the NULL value in the DATE1 column.
$ ! Instead, the Oracle Rdb default date value is used:
$ TYPE NULL_DATE.UNL
"first","1858111700000000","last"
$ !
$ ! This time, unload the row in NULL_DATE with the Null option to
$ ! the Record_Definition qualifier:
$ RMU/UNLOAD MF_PERSONNEL NULL_DATE NULL_DATE -
_$ /RECORD_DEFINITION=(FILE=NULL_DATE.RRD, FORMAT=DELIMITED_TEXT, NULL="*")
%RMU-I-DATRECUNL, 1 data records unloaded.
$ !
$ TYPE NULL_DATE.UNL
"first",*,"last "
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Delete the existing row from NULL_DATE:
SQL> DELETE FROM NULL_DATE;
1 row deleted
SQL> --
SQL> COMMIT;
SQL> EXIT;
$ !
$ ! Load the row that was unloaded back into the table,
$ ! using the null_date.unl file created by the
$ ! previous RMU Unload command:
$ RMU/LOAD MF_PERSONNEL /RECORD_DEFINITION=(FILE=NULL_DATE.RRD, -
_$ FORMAT=DELIMITED_TEXT, NULL="*") NULL_DATE NULL_DATE
%RMU-I-DATRECREAD, 1 data records read from input file.
%RMU-I-DATRECSTO, 1 data records stored.
$ !
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Display the row stored in NULL_DATE.
SQL> -- The NULL value stored in the data row
SQL> -- was preserved by the load and unload operations:
SQL> SELECT * FROM NULL_DATE;
COL1 DATE1 COL2
first NULL last
1 row selected
Example 9
The following example demonstrates the use of the Null="" option
of the Record_Definition qualifier to signal to Oracle RMU that
any data that is an empty string in the .unl file (as represented
by two commas with no space separating them) should have the
corresponding column in the database flagged as NULL.
The first part of this example shows the contents of the .unl
file and the RMU Load command used to load the .unl file. The
terminator for each record in the .unl file is the number sign
(#). The second part of this example unloads unloads the data
and specifies that any columns that are flagged as NULL should be
represented in the output file with an asterisk.
"90021","ABUSHAKRA","CAROLINE","A","5 CIRCLE STREET",,
"CHELMSFORD", "MA", "02184", "1960061400000000"#
"90015","BRADFORD","LEO","B","4 PLACE STREET",, "NASHUA","NH",
"03030", "1949051800000000"#
$ !
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, -
_$ MIDDLE_INITIAL, ADDRESS_DATA_1, ADDRESS_DATA_2, -
_$ CITY, STATE, POSTAL_CODE, BIRTHDAY) -
_$ /RECORD_DEFINITION=(FILE= EMPLOYEES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#", -
_$ NULL="") -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECREAD, 2 data records read from input file.
%RMU-I-DATRECSTO, 2 data records stored.
$ !
$ ! Unload this data first without specifying the Null option:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, -
_$ MIDDLE_INITIAL, ADDRESS_DATA_1, ADDRESS_DATA_2, -
_$ CITY, STATE, POSTAL_CODE, BIRTHDAY) -
_$ /RECORD_DEFINITION=(FILE= EMPLOYEES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#") -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECUNL, 102 data records unloaded.
$ !
$ ! The ADDRESS_DATA_2 field appears as a quoted string:
$ TYPE EMPLOYEES.UNL
.
.
.
"90021","ABUSHAKRA ","CAROLINE ","A","5 CIRCLE STREET ","
","CHELMSFORD ","MA","02184","1960061400000000"#
$ !
$ ! Now unload the data with the Null option specified:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, -
_$ MIDDLE_INITIAL, ADDRESS_DATA_1, ADDRESS_DATA_2, -
_$ CITY, STATE, POSTAL_CODE, BIRTHDAY) -
_$ /RECORD_DEFINITION=(FILE= EMPLOYEES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#", -
_$ NULL="*") -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECUNL, 102 data records unloaded.
$ !
$ ! The value for ADDRESS_DATA_2 appears as an asterisk:
$ !
$ TYPE EMPLOYEES.UNL
.
.
.
"90021","ABUSHAKRA ","CAROLINE ","A","5 CIRCLE STREET ",*,
"CHELMSFORD ","MA","02184","1960061400000000"#
Example 10
The following example specifies a transaction for the RMU Unload
command equivalent to the SQL command SET TRANSACTION READ WRITE
WAIT 36 RESERVING table1 FOR SHARED READ;
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(SHARED,ISOLATION=REPEAT,WAIT=36)-
SAMPLE.RDB-
TABLE1-
TABLE.DAT
Example 11
The following example specifies the options that were the default
transaction style in prior releases.
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(READ_ONLY,ISOLATION_LEVEL=SERIALIZABLE)-
SAMPLE.RDB-
TABLE1-
TABLE1.DAT
Example 12
If the database currently has snapshots deferred, it may be more
efficient to start a read-write transaction with isolation level
read committed. This allows the transaction to start immediately
(a read-only transaction may stall), and the selected isolation
level keeps row locking to a minimum.
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(SHARED_READ,ISOLATION=READ_COMMITTED)-
SAMPLE.RDB-
TABLE1-
TABLE1.DAT
Using a transaction type of automatic adapts to different
database settings.
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(AUTOMATIC)-
SAMPLE.RDB-
TABLE1-
TABLE1.DAT
Example 13
The following example shows the output from the flags STRATEGY
and ITEM_LIST which indicates that the Optimize qualifier
specified that sequential access be used, and also that Total_
Time is used as the default optimizer preference.
$ DEFINE RDMS$SET_FLAGS "STRATEGY,ITEM_LIST"
$ RMU/UNLOAD/OPTIMIZE=SEQUENTIAL_ACCESS PERSONNEL EMPLOYEES E.DAT
.
.
.
~H Request Information Item List: (len=11)
0000 (00000) RDB$K_SET_REQ_OPT_PREF "0"
0005 (00005) RDB$K_SET_REQ_OPT_SEQ "1"
000A (00010) RDB$K_INFO_END
Get Retrieval sequentially of relation EMPLOYEES
%RMU-I-DATRECUNL, 100 data records unloaded.
Example 14
AUTOMATIC columns are evaluated during INSERT and UPDATE
operations for a table; for instance, they may record the
timestamp for the last operation. If the table is being
reorganized, it may be necessary to unload the data and reload it
after the storage map and indexes for the table are re-created,
yet the old auditing data must remain the same.
Normally, the RMU Unload command does not unload columns marked
as AUTOMATIC; you must use the Virtual_Fields qualifier with the
keyword Automatic to request this action.
$ rmu/unload/virtual_fields=(automatic) payroll_db people people.unl
Following the restructure of the database, the data can be
reloaded. If the target columns are also defined as AUTOMATIC,
then the RMU Load process will not write to those columns. You
must use the Virtual_Fields qualifier with the keyword Automatic
to request this action.
$ rmu/load/virtual_fields=(automatic) payroll_db people people.unl
Example 15
This example shows the action of the Delete_Rows qualifier.
First, SQL is used to display the count of the rows in the table.
The file PEOPLE.COLUMNS is verified (written to SYS$OUTPUT) by
the RMU Unload command.
$ define sql$database db$:scratch
$ sql$ select count (*) from people;
100
1 row selected
$ rmu/unload/fields="@people.columns" -
sql$database -
/record_definition=(file:people,format:delimited) -
/delete_rows -
people -
people2.dat
EMPLOYEE_ID
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
SEX
BIRTHDAY
%RMU-I-DATRECERA, 100 data records erased.
%RMU-I-DATRECUNL, 100 data records unloaded.
A subsequent query shows that the rows have been deleted.
$ sql$ select count (*) from people;
0
1 row selected
Example 16
The following example shows the output from the RMU Unload
command options for XML support. The two files shown in the
example are created by this RMU Unload command:
$ rmu/unload -
/record_def=(format=xml,file=work_status) -
mf_personnel -
work_status -
work_status.xml
Output WORK_STATUS.DTD file
<?xml version="1.0"?>
<!-- RMU Unload for Oracle Rdb V7.1-00 -->
<!-- Generated: 16-MAR-2001 22:26:47.30 -->
<!ELEMENT WORK_STATUS (RMU_ROW*)>
<!ELEMENT RMU_ROW (
STATUS_CODE,
STATUS_NAME,
STATUS_TYPE
)>
<!ELEMENT STATUS_CODE (#PCDATA)>
<!ELEMENT STATUS_NAME (#PCDATA)>
<!ELEMENT STATUS_TYPE (#PCDATA)>
<!ELEMENT NULL (EMPTY)>
Output WORK_STATUS.XML file
<?xml version="1.0"?>
<!-- RMU Unload for Oracle Rdb V7.1-00 -->
<!-- Generated: 16-MAR-2001 22:26:47.85 -->
<!DOCTYPE WORK_STATUS SYSTEM "work_status.dtd">
<WORK_STATUS>
<RMU_ROW>
<STATUS_CODE>0</STATUS_CODE>
<STATUS_NAME>INACTIVE</STATUS_NAME>
<STATUS_TYPE>RECORD EXPIRED</STATUS_TYPE>
</RMU_ROW>
<RMU_ROW>
<STATUS_CODE>1</STATUS_CODE>
<STATUS_NAME>ACTIVE </STATUS_NAME>
<STATUS_TYPE>FULL TIME </STATUS_TYPE>
</RMU_ROW>
<RMU_ROW>
<STATUS_CODE>2</STATUS_CODE>
<STATUS_NAME>ACTIVE </STATUS_NAME>
<STATUS_TYPE>PART TIME </STATUS_TYPE>
</RMU_ROW>
</WORK_STATUS>
<!-- 3 rows unloaded -->
Example 17
The following example shows that if the Flush=On_Commit qualifier
is specified, the value for the Commit_Every qualifier must be
equal to or a multiple of the Row_Count value so the commits
of unload transactions occur after the internal RMS buffers are
flushed to the unload file. This prevents loss of data if an
error occurs.
$RMU/UNLOAD/ROW_COUNT=5/COMMIT_EVERY=2/FLUSH=ON_COMMIT MF_PERSONNEL -
_$ EMPLOYEES EMPLOYEES
%RMU-F-DELROWCOM, For DELETE_ROWS or FLUSH=ON_COMMIT the COMMIT_EVERY value must
equal or be a multiple of the ROW_COUNT value.
The COMMIT_EVERY value of 2 is not equal to or a multiple of the ROW_COUNT value
of 5.
%RMU-F-FTL_UNL, Fatal error for UNLOAD operation at 27-Oct-2005 08:55:14.06
Example 18
The following examples show that the unload file and record
definition files are not deleted on error if the Noerror_Delete
qualifier is specified and that these files are deleted on error
if the Error_Delete qualifier is specified. If the unload file is
empty when the error occurs, it will be deleted.
$RMU/UNLOAD/NOERROR_DELETE/ROW_ACOUNT=50/COMMIT_EVERY=50 MF_PERSONNEL -
_$ EMPLOYEES EMPLOYEES.UNL
%RMU-E-OUTFILNOTDEL, Fatal error, the output file is not deleted but may not
be useable,
50 records have been unloaded.
-COSI-F-WRITERR, write error
-RMS-F-FUL, device full (insufficient space for allocation)
$RMU/UNLOAD/ERROR_DELETE/ROW_COUNT=50/COMMIT_EVERY=50 MF_PERSONNEL -
_$ EMPLOYEES EMPLOYEES.UNL
%RMU-E-OUTFILDEL, Fatal error, output file deleted
-COSI-F-WRITERR, write error
-RMS-F-FUL, device full (insufficient space for allocation)
Example 19
The following example shows the FORMAT=CONTROL option. This
command creates a file EMP.CTL (the SQL*Loader control file)
and EMPLOYEES.DAT in a portable format to be loaded.
$ RMU/UNLOAD/RECORD_DEFINITION=(FORMAT=CONTROL,FILE=EMP) -
SQL$DATABASE -
EMPLOYEES -
EMPLOYEES
Example 20
The following shows an example of using the COMPRESSION qualifier
with the RMU Unload command.
$ RMU/UNLOAD/COMPRESS=LZW/DEBUG=TRACE COMPLETE_WORKS COMPLETE_WORKS
COMPLETE_WORKS
Debug = TRACE
Compression = LZW
* Synonyms are not enabled
Unloading Blob columns.
Row_Count = 500
Message buffer: Len: 54524
Message buffer: Sze: 109, Cnt: 500, Use: 31 Flg: 00000000
** compress data: input 2700 output 981 deflate 64%
** compress TEXT_VERSION : input 4454499 output 1892097 deflate 58%
** compress PDF_VERSION : input 274975 output 317560 deflate -15%
%RMU-I-DATRECUNL, 30 data records unloaded.
Example 21
The following shows an example of using the COMPRESSION qualifier
with RMU Unload and using the EXCLUDE_LIST option to avoid
attempting to compress data that does not compress.
$ RMU/UNLAOD/COMPRESS=(LZW,EXCLUDE_LIST:PDF_VERSION)/DEBUG=TRACE COMPLETE_WORKS
COMPLETE_WORKS COMPLETE_WORKS
Debug = TRACE
Compression = LZW
Exclude_List:
Exclude column PDF_VERSION
* Synonyms are not enabled
Unloading Blob columns.
Row_Count = 500
Message buffer: Len: 54524
Message buffer: Sze: 109, Cnt: 500, Use: 31 Flg: 00000000
** compress data: input 2700 output 981 deflate 64%
** compress TEXT_VERSION : input 4454499 output 1892097 deflate 58%
%RMU-I-DATRECUNL, 30 data records unloaded.