Loads data into the tables of the database.
You can use the RMU Load command to:
o Perform the initial load of an Oracle Rdb database.
o Reload a table after performing a restructuring operation.
o Load an archival database.
o Move data from one database to another.
o Load security audit records from an OpenVMS security audit
table into the database being audited, or into a different
database than the one being audited.
o Load additional rows into an existing table. (However, note
that it cannot be used to modify existing rows.)
o Import data into a database from an application that generates
RMS files.
You can load data using either of the following two methods:
o A single-process method
This was the only method available prior to Oracle Rdb V7.0.
The single process method uses one process to both read the
input file and load the target table.
o A multiprocess method, also called a parallel load
The parallel load method, which you specify with the Parallel
qualifier, enables Oracle RMU to use your process to read
the input file and use one or more executors (subprocesses
or detached slave process, depending on additional factors)
to load the data into the target table. This results in
concurrent read and write operations, and in many cases,
substantially improves the performance of the load operation.
By default, Oracle RMU sets up a parallel load operation as
follows:
o Your process serves as the load operation execution manager.
o Each storage area (partition) in the table being loaded is
assigned an executor.
o Each executor is assigned four communications buffers.
(You can override this default with the Buffer_Count option to
the Parallel qualifier.)
o Each communications buffer holds the number of rows defined by
the Row_Count qualifier.
Once the executors and communications buffers are set up, the
parallel load operation processes the input file as follows:
1. Your process begins reading the input file and determines the
target storage area for each row in the input file.
2. Your process places each row in the communications buffer for
the executor assigned to the data's target storage area.
3. When an executor's first communications buffer becomes full,
it begins loading the data into the target storage area.
4. If your process has another portion of data ready for a given
executor before that executor has completed loading its first
buffer of data, your process places the next portion of data
in the second communications buffer for that executor.
5. Each executor, concurrent with each of the other executors,
loads the data from its buffers.
6. Your process continues reading, sorting, and assigning data to
each executor (by placing it in that executor's communication
buffer) until all the data from the input file has been
sorted, assigned, and loaded.
The Row_Count qualifier and Parallel qualifier (which provides
the Executor_Count and Buffer_Count options) give you the ability
to fine tune the Parallel load operation.
See the Oracle Rdb Guide to Database Design and Definition for
tips on optimizing the performance of the load operation.
1 – Description
The RMU Load command accepts the following five types of data
files, all of which, except the security audit journal, have the
file extension .unl:
o Text data file
o Delimited text data file
o Binary data file
o Specially structured file
o OpenVMS security audit journal file
With the exception of the specially structured file and the
security audit journal file, you must provide a record definition
file (.rrd) on the RMU Load command line to load these data
files. The record definition file provides Oracle RMU with a
description of (metadata for) the data you are loading.
The following list describes the additional requirements for
loading each of these types of files:
o Text data file
To load a text data file (.unl), you must specify the Record_
Definition qualifier with the Format=Text option.
The following command loads text data (employees.unl) into
the EMPLOYEES table of the mf_personnel database. The
employees.rrd file provides the record definition for the
data in employees.unl
$ RMU/LOAD/RECORD_DEFINITION=(FILE=employees.rrd, FORMAT=TEXT) -
_$ mf_personnel EMPLOYEES employees.unl
You can generate an appropriate .rrd file for the preceding
example by issuing the following command:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=employees.rrd, FORMAT=TEXT) -
_$ mf_personnel EMPLOYEES unload.unl
o Delimited text data files
To load delimited text data files (.unl) you must
specify the Record_Definition qualifier with the with the
Format=Delimited_Text option.
The following command loads delimited text data
(employees.unl) into the EMPLOYEES table of the mf_personnel
database. The employees.rrd file describes the format of
employees.unl
$ RMU/LOAD/RECORD_DEFINITION=(FILE=employees.rrd, -
_$ FORMAT=DELIMITED_TEXT, TERMINATOR="#") -
_$ mf_personnel EMPLOYEES employees.unl
You can generate an appropriate .rrd file for the preceding
example by issuing the following command:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=employees.rrd, -
_$ FORMAT=DELIMITED_TEXT) mf_personnel EMPLOYEES unload.unl
o Binary data files
To load binary data files, you must ensure that the records
you load match the record definition in both size and data
type. The records must all have the same length and the data
in each record must fill the entire record. If the last field
is character data and the information is shorter than the
field length, the remainder of the field must be filled with
spaces. You cannot load a field that contains data stored in
packed decimal format.
The following command loads binary data (employees.unl)
into the EMPLOYEES table of the mf_personnel database. The
employees.rrd file describes the format of employees.unl.
$ RMU/LOAD/RECORD_DEFINITION=(FILE=employees.rrd) mf_personnel -
_$ EMPLOYEES employees.unl
You can generate an appropriate .rrd file for the preceding
example by issuing the following command:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=employees.rrd) mf_personnel -
_$ EMPLOYEES unload.unl
o Specially structured binary files that include both data and
metadata.
To load the specially structured binary files (created by the
RMU Unload command without the Record_Definition qualifier)
you must specify the file (.unl) created by the RMU Unload
command.
The following command loads the binary data contained in
the employees.unl file into the EMPLOYEES table of the mf_
personnel database. The record definition information is
contained within the binary .unl file.
$ RMU/LOAD MF_PERSONNEL EMPLOYEES employees.unl
This specially structured employees.unl file is created with
the following RMU Unload command:
$ RMU/UNLOAD MF_PERSONNEL EMPLOYEES employees.unl
o Security audit journal files
To load the records from a security audit journal file
maintained by the OpenVMS operating system, you must decide
whether to load records into the same database for which
security audit journal records are being recorded or to load
them into a separate database. In either case you do not
need to specify a record definition file; use of the Audit
qualifier indicates to Oracle RMU that the record definition
is that of the security audit journal file.
The following command loads the records from the security
audit journal file (with a logical name of SECURITY_AUDIT) for
the mf_personnel database into the AUDIT_TABLE table of the
mf_personnel database:
$ RMU/LOAD/AUDIT MF_PERSONNEL.RDB AUDIT_TABLE -
_$ SECURITY_AUDIT
This example loads the records from the security audit journal
file (with a logical name of SECURITY_AUDIT) for the mf_
personnel database into the AUDIT_TABLE table of the audit
database:
$ RMU/LOAD/AUDIT=DATABASE_FILE=MF_PERSONNEL.RDB AUDIT.RDB -
_$ AUDIT_TABLE SECURITY_AUDIT
See the Usage Notes for more detailed information on loading
security audit journal records and the file name of the
security audit journal.
In all cases where you specify a record definition file (.rrd),
the record definition file and the database definition of the
table being loaded must match in the number of specified fields
and the data type of each field. If the data you want to load
has more fields than the database table definition specifies,
you can still load the data, but you must use the FILLER keyword
with the field definition in your .rrd file to represent the
additional field. See Example 15 in the Examples help entry under
this command.
By default, the table specified in the RMU Load command is
reserved for PROTECTED WRITE.
Data Type Conversions Performed by Oracle Rdb shows the data type
conversions that can occur while you are performing a load or
unload operation.
Table 11 Data Type Conversions Performed by Oracle Rdb
Original Data
Type New Data Type
TINYINT INTEGER, QUADWORD, SMALLINT, FLOAT, DOUBLE
PRECISION, VARCHAR, CHAR
SMALLINT INTEGER, QUADWORD, FLOAT, DOUBLE PRECISION,
VARCHAR, CHAR
INTEGER SMALLINT, QUADWORD, FLOAT, DOUBLE PRECISION,
VARCHAR, CHAR
QUADWORD SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION,
VARCHAR, CHAR
FLOAT DOUBLE PRECISION, CHAR, and VARCHAR
DOUBLE FLOAT, CHAR, and VARCHAR
PRECISION
DATE CHAR or VARCHAR
TIME CHAR or VARCHAR
TIMESTAMP CHAR or VARCHAR
INTERVAL CHAR or VARCHAR
CHAR FLOAT, DOUBLE PRECISION, DATE, TIME, TIMESTAMP,
INTERVAL, VARCHAR, SMALLINT, INTEGER, or QUADWORD
See the Oracle Rdb SQL Reference Manual for a description of
these data types.
2 – Format
(B)0[m RMU/Load root-file-spec table-name input-file-name
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Audit[=Database_File=db-name] x No audit table loaded
/Buffers=n x See description
/Commit_Every=n x See description
/[No]Constraints[=Deferred] x /Constraints
/Corresponding x See description
/[No]Defer_Index_Updates x /Nodefer_Index_Updates
/[No]Dialect=(dialect-opts) x /Dialect=SQL99
/[No]Execute x /Execute
/Fields=(column-name-list) x See description
/List_Plan=output-file x See description
/[No]Log_Commits x /Nolog_Commits
/[No]Match_Name=table-name x /Nomatch_Name
/Parallel[=(options)] x See description
/[No]Place x /Noplace
/Record_Definition= x See description
({File|Path}=name[,options]) x
(B)0[m /[No]Restricted_Access x /Norestricted_Access
/Row_Count=n x See description
/[No]Skip=n x /Noskip
/Statistics=(stat-opts) x See description
/Transaction_Type=Share-mode x Protected
/[No]Trigger_Relations[=(table_name_list)] x /Trigger_Relations
/[No]Virtual_Fields[=[No]Automatic] x /Novirtual_Fields
3 – Parameters
3.1 – root-file-spec
The file specification for the database root file into which the
table will be loaded. The default file extension is .rdb.
3.2 – table-name
The name of the table to be loaded, or its synonym.
When the Audit qualifier is specified, the table-name parameter
is the name of the table in which you want the security audit
journal records to be loaded. If the table does not exist, the
RMU Load command with the Audit qualifier creates the table and
loads it. If the table does exist, the RMU Load command with the
Audit qualifier loads the table.
3.3 – input-file-name
The name of the file containing the data to be loaded. The
default file extension is .unl.
When the Audit qualifier is specified, the input-file-name
parameter is the name of the journal containing the audit record
data to be loaded. The default file extension is .AUDIT$JOURNAL.
You can determine the name of the security audit journal by using
the DCL SHOW AUDIT/JOURNAL command.
4 – Command Qualifiers
4.1 – Audit
Audit
Audit=Database_File=db-name
Allows you to load a database's security audit records from an
OpenVMS security audit journal into one of the following:
o A table in the database being audited
Specify the Audit qualifier without the Database_File option
to indicate that you want the security audit records to be
loaded into the database specified with the root-file-spec
parameter.
o A table in a different database than the one being audited
Specify the Audit=Database_File=db-name qualifier to indicate
that you want to security audit records for the database
specified with the root-file-spec command parameter to be
loaded into the database specified with the db-name option
parameter.
If you specify the Audit qualifier, you cannot specify the Fields
or Trigger_Relations qualifiers.
In addition you cannot specify the Audit qualifier with a
parallel load operation. If you attempt to do so, Oracle RMU
issues a warning and performs a single-executor load operation.
4.2 – Buffers
Buffers=n
Specifies the number of database buffers used for storing data
during the load operation. If no value is specified, the default
value for the database is used. (The default value for the
database is defined by the logical name RDM$BIND_BUFFERS, or
if the logical is not defined, can be determined by using the
RMU Dump command with the Header qualifier. The RDM$BIND_BUFFERS
logical name, if defined, overrides the value displayed with the
RMU Dump command.) Fewer I/O operations are required if you can
store as much data as possible in memory when many indexes or
constraints are defined on the target table. Therefore, specify
more buffers than allowed by the default value to increase the
speed of the load operation.
See the Oracle Rdb7 Guide to Database Performance and Tuning
for detailed recommendations on setting the number of database
buffers.
4.3 – Commit Every
Commit_Every=n
Specifies the frequency with which Oracle Rdb commits the data
being loaded. For a single-executor load operation, Oracle Rdb
commits the data after every n records that are stored. The
default is to commit only after all records have been stored.
For a parallel load operation, the Commit_Every qualifier
applies separately to each of the executors (processes) used.
For example, if five parallel processes are running, and the
Commit_Every=2 qualifier is specified, Oracle RMU commits data
for each process after it has stored 2 records. This means that
if the Commit_Every=1000 qualifier is specified when you load one
million records with 10 parallel processes, the .ruj files will
store up to 10,000 rows of before-image data.
If you specify the Defer_Index_Updates qualifier and a high value
for the Commit_Every qualifier, memory requirements are high. See
the description of the Defer_Index_Updates qualifier for details.
Commit operations may occur more frequently than you specify
under certain conditions. See the description of the Defer_Index_
Updates qualifier for details.
To determine how frequently you should commit data, decide how
many records you are willing to reload if the original load
operation fails. If you use the Statistics=On_Commit qualifier,
you receive a message indicating the number of records loaded at
each commit operation. Then, if a failure occurs, you know where
to resume loading.
If you specify the Place qualifier and a failure occurs, resume
loading at the point of the previous commit, instead of the
record number of the last successful commit. The Place qualifier
restructures the .unl file prior to loading, so the record number
on which the load operation failed does not correspond to the
same number in the original .unl file.
4.4 – Constraints
Constraints
Constraints=Deferred
Noconstraints
Specifies when or if constraints are evaluated for data
being loaded. If you specify the Constraints qualifier,
constraints are evaluated as each record is loaded. If you
specify the Noconstraints qualifier, constraints are not
evaluated at all during the load operation. If you specify the
Constraints=Deferred qualifier, constraints are evaluated after
all data from the input file has been loaded.
The default is the Constraints qualifier.
Oracle Corporation recommends that you accept the default for
most load operations. The Noconstraints and Constraints=Deferred
qualifiers are useful when load performance is your highest
priority, you fully understand the constraints defined for
your database, and you are familiar enough with the input data
to be fairly certain that loading that data will not violate
constraints; then you might use these qualifiers as follows:
o Constraints=Deferred
This qualifier is particularly useful for improving
performance when you are loading data into a new table.
Oracle Corporation strongly recommends that you issue an
RMU Verify command with the Constraints qualifier when the
load operation has completed. Note, however, that issuing the
RMU Verify command after the load operation has completed
takes about the same amount of time that would have been
spent had you specified the RMU Load command with the
Constraints qualifier. In other words, by specifying the
Constraints=Deferred qualifier, you are only delaying when
the constraint verification will take place.
o Noconstraints
This qualifier is particularly useful when you are performing
a parallel load operation with the Defer_Index_Updates
qualifier. Oracle Corporation strongly recommends that you
issue an RMU Verify command with the Constraints qualifier
when the load operation has completed. Note, however, that
when you issue the RMU Verify command with the Constraints
qualifier, all rows in the table are checked for constraint
violations, not just the rows that are loaded.
Consider the following before issuing an RMU Load command with
the Noconstraints or Constraints=Deferred qualifier:
o If a table is populated with data prior to a load operation,
it is less expensive to check constraints on each record
as it is being loaded, than to verify constraints on the
entire table after the set of new records has been loaded.
For example, assume you load 200 new records into a table that
currently holds 2,000 records and one constraint is defined
on the table. If you verify constraints as the records are
being loaded, constraint validation is performed 200 times.
If you wait and verify constraints after the load operation
completes, constraint verification must be performed for 2,200
records
o If an RMU Verify command reveals that constraint violations
occurred during the load operation, you must track down those
records and either remove them or make other modifications
to the database to restore the data integrity. This can be a
time-consuming process.
Also consider a situation where all of the following are true:
o You perform a parallel load operation
o You specify the Constraints qualifier
o The table into which you are loading data has a constraint
defined on it
o The constraint defined on the table was defined as deferred
o Constraint evaluation fails during the load operation
In a case such as the preceding, you can not easily determine
which rows were loaded and which were not. Therefore Oracle
Corporation recommends that if deferred constraints are defined
on a table, then you should also specify the Constraints=Deferred
qualifier in your parallel load command. When you follow this
recommendation, the records that violate the constraint are
stored in the database. When the load operation completes, you
can remove from the database those records that violate the
constraint.
See Example 6 in Verify for an example of the steps to take if
an RMU Verify command reveals that an RMU Load command has stored
data that violates constraints into your database.
4.5 – Corresponding
Corresponding
Loads fields into a table from the .unl file by matching the
field names in the .rrd file to the column names in the table.
The Corresponding qualifier makes it more convenient to unload,
restructure, and reload a table.
For example, if the columns in the table appear in the order:
EMPLOYEE_ID, LAST_NAME, FIRST_NAME, but the data in your .unl
file appears in the order: EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
and your .rrd file lists the fields in the order: EMPLOYEE_ID,
FIRST_NAME, LAST_NAME, you can use the Corresponding qualifier
to load the data in your .unl file correctly. (You could also use
the Fields qualifier to accomplish the same task, but this can
get tedious if there are numerous fields.)
The .unl file must contain data for each field in the database
into which it is being loaded; if it does not, you should use the
Fields qualifier.
If the Corresponding qualifier is omitted, the RMU Load command
loads the data into database fields by the ordinal position in
which they appear in the .unl, not by the column name described
in the .rrd file.
The Corresponding qualifier cannot be used with either the Fields
or Audit qualifiers.
4.6 – Defer Index Updates
Defer_Index_Updates
Nodefer_Index_Updates
The Defer_Index_Updates qualifier specifies that non-unique
indexes (other than those that define the placement information
for data in a storage area) will not be rebuilt until commit
time.
Use of this qualifier results in less I/O and fewer lock
conflicts than when index builds are not deferred, but results
in a total failure of a load operation if any lock conflicts
are encountered. In such a case, the entire load operation is
rolled back to the previous commit and you must repeat the load
operation. (Record insertion recommences at the beginning of
the input file). For this reason, you should only use the Defer_
Index_Updates qualifier when all of the following are true:
o You specify the Noconstraints qualifier (or you have dropped
constraints, or no constraints are defined on the table).
o You have dropped triggers from the table (or triggers are not
defined for the table).
o No other users are accessing the table being loaded.
Also be aware that required virtual memory can be quite large
when you defer index updates. Required virtual memory is directly
proportional to the following:
o The length of the Ikeys in the indexes being deferred
o The number of indexes being deferred
o The value for n specified with the Commit_Every qualifier
You can estimate the amount of virtual memory required for each
deferred index using the following formula, where:
o n = the value specified with the Commit_Every qualifier
o I = (length of the Ikey + 50)
n * (I * number_defered_ikeys)
The Nodefer_Index_Updates qualifier is the default. When you
specify the Nodefer_Index_Updates qualifier (or accept the
default), both the indexes that define the placement information
for data in a storage area and any other indexes defined on the
table being loaded are rebuilt at verb time.
This can result in a managed deadlock situation when the Parallel
qualifier is specified. The following describes such a scenario:
o Executor_1 locks index node A in exclusive mode
o Executor_2 locks index node B in exclusive mode
o Executor_1 requests a lock on index node B
o Executor_2 requests a lock on index node A
In such a situation, Oracle Rdb resolves the deadlock by
directing one of the executors to commit the data it has already
stored. This resolves the deadlock situation and the load
operation continues.
4.7 – Dialect
Dialect
Nodialect
The Dialect qualifier is used to control whether truncation
of string data during the loading of data is reported or not.
This loss of data might be significant. RMU Load defaults to SQL
dialect SQL99 which implicitly checks for and reports truncations
during INSERT operations.
o /NODIALECT, /DIALECT=SQL89 or /DIALECT=NONE will not report
any truncation errors, which is the "old" behavior of Rdb
(prior to July 2008).
o /DIALECT=SQL99 (the default) will enable reporting of
truncation errors. Note that truncation occurs if non-space
characters are discarded during the insert.
4.8 – Execute
Execute
Noexecute
The Execute and Noexecute qualifiers are used with the List_Plan
qualifier to specify whether or not the generated plan file is
to be executed. The Noexecute qualifier specifies that the plan
file should be created but should not be executed. Regardless of
whether you use the Noexecute or Execute qualifier (or accept the
default), Oracle RMU performs a validity check on the RMU Load
command you specify.
The validity check determines such things as whether the
specified table is in the specified database, the .rrd file (if
specified) matches the table, and that the number of columns
specified with the Fields qualifier matches the number of
columns in the .unl file. The validity check does not determine
such things as whether your process and global page quotas are
sufficient.
By default, the plan file is executed when an RMU Load command
with the List_Plan qualifier is issued.
4.9 – Fields
Fields=(column-name-list)
Specifies the column or columns of the table to be loaded into
the database. If you list multiple columns, separate the column
names with a comma, and enclose the list of column names within
parentheses. Also, this qualifier specifies the order of the
columns to be loaded if that order differs from the order defined
for the table. The number and data type of the columns specified
must agree with the number and data type of the columns in the
.unl file. The default is all columns defined for the table in
the order defined.
If you specify an options file in place of a list of columns, and
the options file is empty, the RMU Load command loads all fields.
4.10 – List Plan
List_Plan[=output-file]
Specifies that Oracle RMU should generate a plan file and write
it to the specified output file. A plan file is a text file that
contains all the qualifiers specified on the RMU Load command
line. In addition, it specifies the executor names (if you are
performing a parallel load operation), the directory for the .ruj
files, the exception files, and the file created by the Place_
Only qualifier (if specified).
Oracle RMU validates the Oracle RMU command prior to generating
the plan file to ensure that an invalid plan file is not created.
(This is true regardless of whether or not you specify the
Noexecute qualifier.) For example, the following command is
invalid and returns an error message because it specifies
conflicting qualifiers (Corresponding and Fields):
$ RMU/LOAD/RECORD_DEF=FILE=NAMES.RRD/CORRESPONDING -
_$ /FIELDS=(LAST_NAME, FIRST_NAME)/LIST_PLAN=my_plan.plan -
_$ MF_PERSONNEL.RDB EMPLOYEES NAMES.UNL
%RMU-F-CONFLSWIT, conflicting options CORRESPONDING and FIELDS...
See the description of the Execute qualifier for a description
of what items are included when Oracle RMU validates the RMU
Load command. See the Examples section for a complete example and
description of a plan file.
You can use the generated plan as a starting point for building a
load operation that is tuned for your particular configuration.
The output file can be customized and then used with subsequent
load operations as the parameter to the RMU Load Plan command.
See Load Plan for details.
If you want to create only a load plan file and do not want
to execute the load plan when the RMU Load command is issued,
specify the Noexecute qualifier. When you specify the Noexecute
qualifier, you must specify a valid Oracle RMU command.
One way to prototype a plan file prior to creating a potentially
very large .unl file is to specify the List_Plan qualifier and
the Noexecute qualifier along with a valid record definition
(.rrd) file and an empty .unl file on the RMU Load command
line. The .rrd file contains the information Oracle RMU needs
to perform the validation of the plan file; however, because data
is not loaded when you specify the Noexecute qualifier, Oracle
RMU does not attempt to load the .unl file. Note, however, that
you cannot specify the Fields qualifier when using this strategy.
(When you specify the Fields qualifier, Oracle RMU checks to make
sure the number of columns specified with the Fields qualifier
match the number of columns specified in the .unl file.)
If you do not specify a file extension, the default file
extension for the plan file is .plan.
4.11 – Log Commits
Log_Commits
Nolog_Commits
Causes a message to be printed after each commit operation. In
the case of a parallel load, a message is printed after each
executor commits.
The default is the Nolog_Commits qualifier, where no message is
printed after individual commit operations. The Nolog_Commits
qualifier does, however, cause a commit operation total to be
printed after the operation completes or generates an error.
4.12 – Match Name
Match_Name=table-name
Nomatch_Name
Specifies the table name to be read. Tables exported by SQL into
an interchange file can be individually loaded into a database.
The default behavior of the RMU Load command is to locate and
load the first set of table data in the unload file. If this is
not the table you want, you can use the Match_Name qualifier to
specify a different table name. If the Match_Name qualifier is
used without a table-name, Oracle RMU assumes the name of the
table being loaded is also the name of the table in the source
data file. The default is the Nomatch_Name qualifier.
4.13 – Parallel
Parallel[=(options)]
Specifies a parallel load operation. A parallel load operation is
especially effective when you have large partitioned tables that
do not contain segmented strings and for which no constraints or
triggers are defined.
If you specify the Parallel qualifier without any options, your
load operation is assigned one executor and four communications
buffers for that executor. A communications buffer is used for
communications between your process and the executors.
If you want to assign additional executors or communications
buffers, or both, use one or both of the following options:
o Buffer_Count=n
Allows you to specify the number of communications buffers
assigned to each executor in a parallel load operation.
Do not confuse this with the Buffers=n qualifier. The
Buffers=n qualifier specifies the number of database buffers
to use during the load operation.
o Executor_Count=n
Allows you to specify the number of worker processes to
be assigned to the load operation. Ideally, the number of
executors should be equal to the number of table partitions.
You should not assign a greater number of executors than
the number of table partitions. If a table is randomly or
vertically partitioned, Oracle RMU creates only one executor,
regardless of the number you specify.
If the user account's MAXDETACH UAF value is greater than 0,
then executors are created as detached processes. If there
is no MAXDETACH value set, then executors are created as
subprocesses. (A MAXDETACH value = 0 equates to unlimited
detached processes.)
At the end of each load operation, Oracle RMU displays summary
statistics for each executor in the load operation and the main
process. Look at the "Idle time" listed in the statistics at the
end of the job to detect data skew and look at "Early commits" to
detect locking contention.
If some executors have a large amount of idle time, you likely
have data that is skewed. Ideally, data loaded with the Parallel
qualifier should appear in random order within the .unl file.
Data that is already in partition order when you attempt to
perform a parallel load operation results in high idle time for
each executor and thus defeats the advantages of a parallel load
operation.
The summary statistics also list the number of records read from
the input file, the number of data records stored, and the number
of data records rejected. In most cases, the number of data
records rejected plus the number of data records stored equals
the number of data read from the input file. However, under the
following circumstances this equation does not hold:
o The parallel load operation aborts due to a duplicate record
that is not allowed.
o You did not specify an exception file.
Similarly if a load operation aborts due to a record in the input
file being improperly delimited for a delimited text load, the
records rejected plus the records stored do not equal the number
of records read from the input file.
You cannot use a parallel load operation to load list data
(segmented string) records or security audit records. If you
specify a parallel load operation and attempt to load list data
or security audit records, Oracle RMU returns a warning and
performs a single-process (non-parallel) load operation.
4.14 – Place
Place
Noplace
Sorts records by target page number before they are stored.
The Place qualifier automatically builds an ordered set of
database keys (dbkeys) when loading data and automatically stores
the records in dbkey order, sequentially, page by page. During
a parallel load operation, each worker executor builds its own
ordered set of dbkeys.
The number of work files used by the RMU Load command is
controlled by the RDMS$BIND_SORT_WORKFILES logical name. The
allowable values are 1 through 10 inclusive, with a default value
of 2. The location of these work files can be specified with
device specifications, using the SORTWORKn logical name (where n
is a number from 0 to 9). See the OpenVMS documentation set for
more information on using SORT/MERGE. See the Oracle Rdb7 Guide
to Database Performance and Tuning for more information on using
these Oracle Rdb logical names.
A significant performance improvement occurs when the records
are stored by means of a hashed index. By using the Commit_
Every qualifier with the Place qualifier, you can specify how
many records to load between COMMIT statements. Performance may
actually decrease when records are stored by means of a sorted
index.
The default is the Noplace qualifier.
4.15 – Record Definition
Record_Definition=(File=name[,options])
Record_Definition=(Path=name[,options])
Specifies the RMS record definition or the data dictionary record
definition to be used when data is loaded into the database. Use
the File=name parameter to specify an RMS record definition file;
use the Path=name parameter to specify that the record definition
be extracted from the data dictionary. (If the record definition
in the data dictionary contains variants, Oracle RMU will not be
able to extract it.)
The default file extension for the File=name parameter is
.rrd. The syntax for the .rrd file is similar to that used by
the Common Dictionary Operator (CDO) interface for the data
dictionary. You must define columns before you can define rows.
You can place only one column on a line. You can create a sample
.rrd file by using the RMU Unload command with the Record_
Definition qualifier. You must ensure that the record definition
in the .rrd file and the actual data are consistent with each
other. Oracle Rdb does not check to see that data types in the
record definition and the data match. See the help entry for
RRD_File_Syntax and the Oracle Rdb Guide to Database Design and
Definition for more information about the format of the .rrd
file.
You must specify either the File=name or Path=name parameter.
The options available are:
o Exception_File=exception-file
Allows you to write unloadable records to a single exception
file for a single-process load operation and into multiple
exception files for a parallel load operation. If you generate
a load plan for a parallel load operation, each executor is
assigned its own exception file. In this case, the exception-
file name you specify is given a different file extension for
each executor.
While Oracle RMU is loading data from an RMS file, if an
exception file is specified, then under certain circumstances
an invalid record in the input file does not cause the
RMU Load command to abort. Instead, Oracle RMU creates the
exception file (or files), writes the unloadable record into
this exception file (or files), and continues loading the
remaining records. This process occurs only if the data is
invalid on the actual insert, due to index, constraint, or
trigger errors. If the record has an invalid format in the RMS
file (for example, a missing delimiter), the exception file is
not used, and the load process aborts.
At the end of the load operation, you can process the
exception file (or files) to correct any problems, and then
reload directly from the exception file or files. The load
operation gives an informational message for each of the
unloadable records and also gives a summary of the number
of records stored and the number of records rejected.
All records that could not be loaded will be written into the
file or files as specified with the argument to the Exception_
File option. The default file extension for the exception
file is .unl for single-process loads; for parallel loads
the default extension is EXC_n, where n corresponds to the
executor number assigned by Oracle RMU. The exception file or
files are created only if there are unloadable records. If the
Exception_File option is not specified, no exception files are
created, and the load operation aborts at the first occurrence
of an exception.
However, note that if the Defer_Index_Updates qualifier is
specified, and a constraint violation or lock conflict occurs,
the load operation aborts when it attempts to commit the
transaction.
If the Defer_Index_Updates qualifier is not specified, records
that cause a constraint violation are written to the exception
file or files and the load operation continues loading the
remaining records.
o Format=Text
If you specify the Format=Text option, Oracle RMU converts all
data to printable text before loading it.
o If you do not specify the Format option, then Oracle RMU
expects to load a fixed-length binary flat file. The data
type of the fields must be specified in the .rrd file.
o Format=(Delimited_Text [,delimiter-options])
If you specify the Format=Delimited_Text option, the .rrd file
contains only text fields and specifies the maximum length of
the columns in the file containing delimited ASCII text. The
column values that are longer than those specified in the .rrd
file are truncated.
Note that DATE VMS types must be specified in the collatable
time format, which is yyyymmddhhmmsscc. For example, March 20,
1993 must be specified as: 1993032000000000.
Unless you specify the Format=Delimited_Text option,
delimiters are regarded as part of the data by Oracle RMU.
Example 13 in the Examples help entry under this command
demonstrates the Format=Delimited_Text option. Delimiter
options (and their default values if you do not specify
delimiter options) are as follows. Note that with the
exception of the Prefix and Suffix delimiter options, the
values specified must be unique. The Prefix and Suffix values
can be the same value as each other, but not the same as other
delimiter options. The Null string must also be unique.
- Prefix=string
Specifies a prefix string that begins any column value in
the ASCII input file. If you omit this option, the column
prefix is assumed to consist of a quotation mark (").
- Separator=string
Specifies a string that separates column values of a row.
If you omit this option, the column separator is assumed to
consist of a single comma (,).
- Suffix=string
Specifies a suffix string that ends any column value in
the ASCII input file. If you omit this option, the column
suffix is assumed to consist of 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 is assumed to be the end of the line.
- Null=string
Specifies a string, which when found in the input record,
is stored as NULL in the database column. This option is
only valid when the Delimited_Text option is specified
also.
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
If provided, the string that represents the null character
must be quoted on the Oracle RMU command line, however, it
must not be quoted in the input file. You cannot specify a
blank space or spaces as the null character.
If the final column or columns of a record are to be set
to NULL, you only have to specify data for the column up to
the last non-null column.
See the Examples section for an example of each of these
methods of storing the NULL value.
NOTE
The values of each of the strings specified in the
delimiter options must be enclosed by 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 the quotation marks as follows:
o The first quotation mark is stripped from the
string.
o The second and third quotation marks 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 delimiter string, you must use two
quotation marks for each quotation mark that you
want to appear in the string. For example, Suffix
**"**.
A delimiter of blank spaces enclosed in quotes is not
valid.
o Place_Only=sorted-placement-file
Allows you to sort the input file and create an output file
sorted in Placement order.
The input file can first be sorted into Placement order by
using the Place_Only option. The resultant file can then be
loaded with the Commit_Every qualifier to gain the required
efficiency. Do not use this option with a parallel load
operation; parallel load operations perform best when the
input file is not sorted.
The Place_Only option cannot be used with either the Commit_
Every qualifier or the Exception_File option (data is not
being stored in the database). However, the Place_Only option
requires the Place qualifier be specified (to sort the data).
The placement-sorted output file has the default file
extension of .unl.
Unless you specify the Null option (with the Format=Delimited_
Text parameter of the Record_Definition qualifier), any null
values stored in the rows of the tables being loaded are not
preserved. Therefore, use the Null option if you want to preserve
null values stored in tables and you are moving data within the
database or between databases.
See the examples in the Examples help entry under the RMU Unload
command for more information.
4.16 – 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.17 – Restricted Access
Restricted_Access
NoRestricted_Access
Allows a single process to load data and enables some
optimizations available only when restricted access is in use.
The default is Norestricted_Access.
If you are loading a table from an RMU Unload file which contains
LIST OF BYTE VARYING data, the Restricted_Access qualifier
reserves the LIST areas for EXCLUSIVE access. This reduces the
virtual memory used by long transactions during a load operation
and also eliminates I/O to the snapshot files for the LIST
storage areas.
The Restricted_Access and Parallel qualifiers are mutually
exclusive and cannot be specified together on the same RMU Load
command line or within a plan file. While RMU Load is running
with the Restricted_Access qualifier specified, no other user can
attach to the database.
4.18 – Row Count
Row_Count=n
Specifies that Oracle Rdb buffer multiple rows between the Oracle
Rdb server and the RMU Load process. The default for n is 500
rows; however, this value should be adjusted based on working
set size and length of loaded data. Increasing the row count may
reduce the CPU cost of the load 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.19 – Skip
Skip=n
Noskip
Ignores the first n data records in the input file. Use this
qualifier in conjunction with the Commit_Every qualifier
when restarting an aborted load operation. An aborted load
operation displays a message indicating how many records have
been committed. Use this value for n. If you specify a negative
number, you receive an error message. If you specify a number
greater than the number of records in the file, you receive an
error message stating that no records have been stored. If you
do not specify a value, you receive an error message stating that
there is a missing keyword value.
Using the Skip qualifier to restart an aborted parallel load
operation is rarely useful. Because records are sorted by the
controller for each executor involved in the parallel load, there
are usually multiple sections of loaded and unloaded records in
the input file. Unless you are very familiar with the data you
are loading and how it is sorted by the controller, you risk
loading some records twice and not loading other records at all,
if you use the Skip qualifier when restarting an aborted parallel
load operation.
The default is the Noskip qualifier.
4.20 – Statistics
Statistics=(stat-opts)
Specifies that statistics are to be displayed at regular
intervals or each time a transaction commits, or both, so that
you can evaluate the progress of the load operation.
The stat-opts are the options you can specify with this
qualifier, namely: Interval=n, On_Commit, or both. If the
Statistics qualifier is specified, you must also specify at least
one option.
When the Statistics=(Interval=n) qualifier is specified, Oracle
RMU prints statistics every n seconds. The minimum value for n is
1.
When the Statistics=(On_Commit) qualifier is specified, Oracle
RMU prints statistics each time a transaction is committed.
If you specify both options, Statistics=(Interval=n, On_Commit),
statistics are displayed every n seconds and each time a
transaction commits.
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 loaded when the last transaction was
committed
o Number of records loaded so far in the current transaction
o If the Record_Definition=Exception_File option is also
specified, the following statistics are displayed also:
- Number of records rejected when the last transaction was
committed
- Number of records rejected so far in the current
transaction
o If the Parallel qualifier is specified also, the following
statistics are displayed also:
- Number of extra commits performed by executors
Extra commits are caused when the Oracle RMU directs your
process or the executors to commit a transaction earlier
than usual to avoid a hung load operation. For example, if
one executor is holding, but no longer needs a lock that
another executor requires, Oracle RMU directs the first
executor to commit its current transaction. By directing an
executor or executors to commit a transaction earlier than
usual, the locks under contention are released and the load
operation can proceed.
- The total number of executors
- The number of executors that are initializing, idle,
terminated, sorting, storing, committing, or executing
At any time during the load operation, you can press Ctrl/T to
display the current statistics.
4.21 – Transaction Type
Transaction_Type=share-mode
Specifies the share mode for the load operation. The following
share modes are available:
Batch_Update
Exclusive
Protected
Shared
You must specify a value if you use the Transaction_Type
qualifier. If you do not specify the Transaction_Type qualifier,
the default share mode is Protected.
If you specify a parallel load operation (with the Parallel
qualifier), and constraints are defined on the table you are
loading, Oracle Corporation recommends that you specify the
Shared share mode, or drop the constraints prior to starting a
parallel load operation, or specify the Noconstraints qualifier.
See the Usage Notes for details.
4.22 – Trigger Relations
Trigger_Relations[=(table-name-list)]
NoTrigger_Relations
You can use the Trigger_Relations qualifier in three ways:
o Trigger_Relations=(table-name-list)
Specifies the tables to be reserved for update. Using this
qualifier, you can explicitly lock tables that are updated
by triggers in store operations. If you list multiple tables,
separate the table names with a comma, and enclose the list of
table names within parentheses.
o Trigger_Relations
If you omit the list of table names, the tables updated by
triggers are locked automatically as required. This is the
default.
o NoTrigger_Relations
Disables triggers on the target table. This option requires
DROP privilege on the table being loaded. You cannot specify a
list of table names with this option.
If you specify a parallel load operation (with the Parallel
qualifier), and triggers are defined on the table you are
loading, Oracle Corporation recommends that you specify the
Shared share mode or drop the triggers prior to starting a
parallel load operation. See the Usage Notes for details.
The Trigger_Relations qualifier can be used with indirect file
references. See the Indirect-Command-Files help entry for more
information.
4.23 – Virtual Fields
Virtual_Fields(=[No]Automatic)
Novirtual_Fields
The Virtual_Fields qualifier is required to reload any AUTOMATIC
(or IDENTITY) fields with real data.
The Novirtual_Fields qualifier is the default, which is
equivalent to the Virtual_Fields=(Noautomatic) qualifier.
If you specify the Virtual_Fields qualifier without a keyword,
all fields are loaded except COMPUTED BY columns and calculated
VIEW columns.
Use this qualifier when restructuring a table and when you do
not wish the AUTOMATIC INSERT AS or IDENTITY column to recompute
new values. Instead, RMU will reload the saved values from a file
created by RMU/UNLOAD/VIRTUAL_FIELDS=AUTOMATIC.
5 – Usage Notes
o To use the RMU Load command for a database, you must have the
RMU$LOAD privilege in the root file access control list (ACL)
for the database or the OpenVMS SYSPRV or BYPASS privilege.
The appropriate Oracle Rdb privileges for accessing the
database tables involved are also required.
o To use the RMU Load command with the Audit qualifier, you must
have both of the following:
- The RMU$SECURITY privilege in the root file ACL for the
database whose security audit records are being loaded
- The RMU$LOAD privilege in the root file ACL for the
database into which these security audit records are being
loaded
If you do not have both of the privileges described in the
preceding list, you must have the OpenVMS SYSPRV or BYPASS
privilege.
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 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 following list provides information on parallel load
operations:
- Specify no more executors (with the Executor_Count option
to the Parallel qualifier) than storage areas defined for
the table you are loading.
- You cannot use a parallel load operation to load list data
(segmented string) records or security audit records. If
you specify a parallel load operation and attempt to load
list data or security audit records, Oracle RMU returns a
warning and performs a single-executor load operation.
- Oracle Corporation recommends that you specify a shared
mode transaction type or specify the Noconstraints
qualifier and drop triggers during a parallel load
operation; otherwise, constraints and triggers defined
on the table you are loading can cause lock conflicts among
the parallel load executors.
- If you are using parallel load and hashed indexes, do not
sort the data prior to loading it. Instead, use the Place
qualifier to the RMU Load command to sort the data as it is
loaded. (The Place qualifier is useful for hashed indexes,
not sorted.)
o The following list provides information on loading security
audit journals:
- Loading security audit journals into a database other than
that which is being audited
When you load the security audit journals recorded for one
database into another database, you specify the database
that is being audited as a parameter to the Audit=Database_
File qualifier, and you specify the database into which
these security audit records should be loaded with the
root-file-spec parameter to the Oracle RMU command.
For instance, the following example loads the security
audit journal records for the mf_personnel database into
the MFP_AUDIT table of the audit_db database. Note that
SECURITY_AUDIT is a logical name that points to the actual
security audit journal file.
$ RMU/LOAD/AUDIT=DATABASE_FILE=MF_PERSONNEL AUDIT_DB -
_$ MFP_AUDIT SECURITY_AUDIT
When you issue the preceding RMU Load command, the audit_
db database must exist. However, the RMU Load command
creates the MFP_AUDIT table in the audit_db database
and appropriately defines the columns for the MFP_AUDIT
database.
In other words, the following SQL statement satisfies the
minimum requirements for the audit_db database to be used
correctly by the preceding RMU Load command:
SQL> CREATE DATABASE FILENAME audit_db.rdb;
Note that there is no field in the audit record loaded by
Oracle RMU to indicate the source database for the records.
Therefore, it is not wise to mix auditing records from
different databases in the same table. Instead, auditing
information for different databases should be loaded into
separate tables.
- Security audit journal file name
The name of the security audit journal file depends on the
version of the operating system software you are running
and on the hardware platform, as follows:
* SYS$MANAGER:SECURITY.AUDIT$JOURNAL for OpenVMS Alpha
V6.1 and later and OpenVMS VAX V6.0 and later
* SYS$MANAGER:SECURITY_AUDIT.AUDIT$JOURNAL for OpenVMS
Alpha prior to V6.1 and OpenVMS VAX V5.5 and earlier.
- Loading security audit journals into the database being
audited
The Oracle Rdb table into which you load the security
audit journal records should be defined with the columns
shown in Columns in a Database Table for Storing Security
Audit Journal Records under the column marked Oracle Rdb
Column Name so that the audit journal records can be loaded
successfully into the table. If the table does not exist,
the RMU Load Audit command creates it with the columns
shown in Columns in a Database Table for Storing Security
Audit Journal Records under the column marked Oracle Rdb
Column Name. You can give the table any valid name.
- Columns in a Database Table for Storing Security Audit
Journal Records lists the column names created by the RMU
Load command with the Audit qualifier.
Table 12 Columns in a Database Table for Storing Security Audit
Journal Records
Oracle Rdb Column
Name SQL Data Type and Length
AUDIT$EVENT CHAR 16
AUDIT$SYSTEM_NAME CHAR 15
AUDIT$SYSTEM_ID CHAR 12
AUDIT$TIME_STAMP CHAR 48
AUDIT$PROCESS_ID CHAR 12
AUDIT$USER_NAME CHAR 12
AUDIT$TSN CHAR 25
AUDIT$OBJECT_NAME CHAR 255
AUDIT$OBJECT_TYPE CHAR 12
AUDIT$OPERATION CHAR 32
AUDIT$DESIRED_ CHAR 16
ACCESS
AUDIT$SUB_STATUS CHAR 32
AUDIT$FINAL_ CHAR 32
STATUS
AUDIT$RDB_PRIV CHAR 16
AUDIT$VMS_PRIV CHAR 16
AUDIT$GRANT_IDENT CHAR 192
AUDIT$NEW_ACE CHAR 192
AUDIT$OLD_ACE CHAR 192
AUDIT$RMU_COMMAND CHAR 512
o Dates stored in ASCII text format can be converted to the VMS
DATE data type format by the RMU Load command. See Example
7 in the Examples help entry under this command, which
demonstrates this conversion.
o To preserve the NULL indicator in a load or unload operation,
specify the Null option when you use the Record_Definition
qualifier. Using the Record_Definition qualifier without the
Null option causes the RMU Load command to replace all NULL
values with zeros. This can cause unexpected results with
computed-by columns.
o When the RMU Load command is issued for a closed database, the
command executes without other users being able to attach to
the database.
o The RMU Load command recognizes character set information.
When you load a table, the RMU Load command recognizes that
the correct size of a column is based on its character set.
For example, the RMU Load command recognizes that a column
defined as CHAR (10) CHARACTER SET KANJI occupies 20 octets.
o By default, the RMU Load command changes any table or column
names that you specify to uppercase. To preserve lowercase
characters, use delimited identifiers; that is, enclose the
names in quotation marks ("").
o If your database uses a character set other than the DEC
Multinational character set (MCS) for table and domain names,
or if you edit a record definition file to use names from such
a character set, the RMU Load command could fail and return
the error shown in the following example:
$ RMU/UNLOAD/RECORD_DEFINITION=FILE=STRINGS MIA -
"TAB_°¡°¢abcd°§ABCD°©°ª" -
STRINGS.UNL
%RMU-I-DATRECUNL, 4 data records unloaded
$ RMU LOAD/RECORD_DEFINITION=FILE=STRINGS MIA -
"TAB_°¡°¢abcd°§ABCD°©°ª" -
STRINGS.UNL
DEFINE FIELD DEC_MCS_CHAR DATATYPE IS TEXT SIZE IS 20.
DEFINE FIELD KANJI_CHAR DATATYPE IS TEXT SIZE IS 10 CHARACTERS -
CHARACTER SET IS KANJI.
DEFINE FIELD HANZI_CHAR DATATYPE IS TEXT SIZE IS 10 CHARACTERS -
CHARACTER SET IS HANZI.
DEFINE FIELD HANYU_CHAR DATATYPE IS TEXT SIZE IS 10 CHARACTERS -
CHARACTER SET IS HANYU.
.
.
.
DEFINE RECORD TAB_°¡°¢abcd°§ABCD°©°ª.
%RMU-F-RECDEFSYN, Syntax error in record definition file
DEFINE RECORD TAB_''°¡°¢ABCD°§ABCD°©°ª.
When this problem occurs, edit the record definition file and
modify the names so that they can be represented with the MCS
character set.
o Oracle RMU does not support the multischema naming convention
and returns an error if you specify one. For example:
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /RECORD_DEFINITION=(FILE=TEXT_NAMES,EXCEPTION_FILE=FILE.UNL) -
_$ corporate_data ADMINISTRATION.PERSONNEL.EMPLOYEES EMP.UNL
%RDB-E-BAD_DPB_CONTENT, invalid database parameters in the database
parameter block (DPB)
%RMU-I-DATRECSTO, 0 data records stored
%RMU-I-DATRECREJ, 0 data records rejected.
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.
SQL> SHOW TABLE ADMINISTRATION.PERSONNEL.EMPLOYEES
Information for table ADMINISTRATION.PERSONNEL.EMPLOYEES
Stored name is EMPLOYEES
.
.
.
Then, to load the table, issue the following RMU Load command:
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /RECORD_DEFINITION=(FILE=TEXT_NAMES,EXCEPTION_FILE=FILE.UNL) -
_$ CORPORATE_DATA EMPLOYEES MY_DATA.UNL
%RMU-I-DATRECSTO, 3 data records stored
%RMU-I-DATRECREJ, 0 data records rejected.
The Fields qualifier can be used with indirect file
references. When you use an indirect file reference in the
field list, the referenced file is written to SYS$OUTPUT if
the DCL SET VERIFY comand has been used. See the Indirect-
Command-Files help entry for more information.
o The Transaction_Type=Batch_Update qualifier cannot be used
with multiple executors (Executor_Count greater than 1).
o The RMU Load procedure supports the loading of tables that
reference system domains.
o If you use a synonym to represent a table or a view, the
RMU Load 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
This command loads the data from the RMS file, names.unl, into
the newly created RETIREES table of the mf_personnel database.
The record structure of RETIREES is in the file names.rrd. The
names.unl and names.rrd files were created by a previous RMU
Unload command. The unload operation unloaded data from a view
derived from a subset of columns in the EMPLOYEES table.
$ RMU/LOAD/RECORD_DEFINITION=FILE=NAMES.RRD -
_$ MF_PERSONNEL RETIREES NAMES.UNL
Example 2
This command restarts an aborted load operation that was loading
the newly created RETIREES table of the mf_personnel database
from the names.unl file. The columns being loaded are EMPLOYEE_
ID, LAST_NAME, and FIRST_NAME. The original load operation
had committed 25 records. Beginning with the 26th record, the
restarted load operation commits the transaction at every record
until it reaches the original point of failure.
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME) -
_$ /COMMIT_EVERY=1/SKIP=25 MF_PERSONNEL RETIREES NAMES.UNL
Example 3
This example loads a new table, PENSIONS, into the mf_personnel
database by using record definitions located in the data
dictionary.
This example assumes that you have first defined a temporary
view, TEMP_PENSIONS, combining appropriate columns of the
EMPLOYEES and SALARY_HISTORY tables. You must also create a
permanent table, PENSIONS, into which you will load the data.
Unload the TEMP_PENSIONS view by using the RMU Unload command
with the Record_Definition=File=name qualifier to create both
an .rrd file containing the column definitions and a data.unl
file containing the data from the TEMP_PENSIONS view. Load the
new record definitions from the pensions.rrd file into the data
dictionary by using the @ command at the CDO prompt. Then you
can load the data into the PENSIONS table of the mf_personnel
database by using the RMU Load command.
$ RMU/UNLOAD/RECORD_DEFINITION=FILE=PENSIONS.RRD MF_PERSONNEL -
_$ TEMP_PENSIONS DATA.UNL
$ DICTIONARY OPERATOR
Welcome to CDO V7.0
The CDD/Repository V7.0 User Interface
Type HELP for help
CDO> @PENSIONS.RRD
CDO> EXIT
$ RMU/LOAD/RECORD_DEFINITION=PATH=PENSIONS MF_PERSONNEL PENSIONS -
_$ DATA.UNL
Example 4
The following command loads the audit records for the mf_
personnel database from the security audit journal file into
the AUDIT_TABLE table in the mf_personnel database. Note that if
the AUDIT_TABLE table does not exist, the RMU Load command with
the Audit qualifier creates it with the columns shown in Columns
in a Database Table for Storing Security Audit Journal Records.
$ RMU/LOAD/AUDIT MF_PERSONNEL AUDIT_TABLE -
_$ SYS$MANAGER:SECURITY.AUDIT$JOURNAL
%RMU-I-DATRECREAD, 12858 data records read from input file.
%RMU-I-DATRECSTO, 27 data records stored.
Example 5
The following command loads the audit records for the mf_
personnel database from the security audit journal file into
the AUDIT_TABLE table into the audit_db database. Note that the
AUDIT_TABLE table is not created when the database is created.
In this case, the RMU Load command with the Audit=Database_
File qualifier creates it with the columns shown in Columns in
a Database Table for Storing Security Audit Journal Records.
$ RMU/LOAD/AUDIT=DATABASE_FILE=MF_PERSONNEL AUDIT_DB AUDIT_TABLE -
_$ SYS$MANAGER:SECURITY.AUDIT$JOURNAL
Example 6
This example loads a new table, COLLEGES, into the mf_personnel
database by using record definitions located in the data
dictionary. A commit operation occurs after every record is
stored. The Log_Commits qualifier prints a message after each
commit operation.
$ RMU/LOAD/RECORD_DEFINITION=FILE=COLLEGES.RRD /COMMIT_EVERY=1 -
_$ /LOG_COMMIT MF_PERSONNEL COLLEGES RMU.UNL
%RMU-I-DATRECSTO, 1 data records stored
%RMU-I-DATRECSTO, 2 data records stored
%RMU-I-DATRECSTO, 3 data records stored
%RMU-I-DATRECSTO, 4 data records stored
%RMU-I-DATRECSTO, 4 data records stored
$
Example 7
The following example shows how a date stored in the .unl file as
16-character collatable text can be converted to VMS DATE format
when loaded into the database by using the RMU Load command.
(The form of the .unl date is yyyymmddhhmmsscc, whereas the form
of the VMS DATE is dd-mmm-yyyy:hh:mm:ss.cc. In both cases, y is
the year, m is the month, d is the day, h is the hour, m is the
minute, s is the second, and c is hundredths of a second. However
in the .unl format, the month is expressed as an integer, whereas
in the VMS DATE format the month is expressed as a 3-character
string.)
The example assumes that the default SYS$LANGUAGE is ENGLISH.
SQL> --
SQL> -- Show the definition of the TEST table, in which the
SQL> -- COL1 column is the VMS DATE data type:
SQL> --
SQL> SHOW TABLE DATETEST;
Columns for table DATETEST:
Column Name Data Type Domain
----------- --------- ------
COL1 DATE VMS
.
.
.
$ !
$ ! Show the .unl file that will be loaded into the TEST table:
$ !
$ TYPE TEST.UNL
$ !
1991060712351212
$ !
$ ! Note that the .rrd file shows a data type of TEXT of 16
$ ! characters. These 16 characters are the number of characters
$ ! specified for the date in the test.unl file:
$ !
$ TYPE TEST.RRD
DEFINE FIELD COL1 DATATYPE IS text size is 16.
DEFINE RECORD TEST.
COL1 .
END TEST RECORD.
$ !
$ ! Load the data in test.unl into the DATETEST table:
$ !
$ RMU/LOAD/RMS=FILE=TEST.RRD TEST.RDB DATETEST TEST.UNL
%RMU-I-DATRECREAD, 1 data records read from input file.
%RMU-I-DATRECSTO, 1 data records stored.
$ !
$ SQL
SQL> ATTACH 'FILENAME TEST';
SQL> SELECT * FROM DATETEST;
COL1
7-JUN-1991 12:35:12.12
1 row selected
Example 8
The following example shows how a date stored in the .unl file
as 22-character collatable text can be converted to TIMESTAMP
format when loaded into the database by using the RMU Load
command. The correct format for the .unl TIMESTAMP value is yyyy-
mm-dd:hh:mm:ss.cc, where y,m,d,h,m,s,and c represent the same
elements of the date and time format as described in Example 7.
This example also shows the use of an exception file to trap data
that cannot be stored.
$ ! Create a column in the mf_personnel database with a
$ ! TIMESTAMP datatype:
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> CREATE TABLE NEWTABLE (COL1 TIMESTAMP);
SQL> SHOW TABLE (COLUMN) NEWTABLE;
Information for table NEWTABLE
Columns for table NEWTABLE:
Column Name Data Type Domain
----------- --------- ------
COL1 TIMESTAMP(2)
SQL> COMMIT;
SQL> EXIT
$ !
$ ! Create a .unl file with the data you want to load. Note that
$ ! the second value is a valid TIMESTAMP specification, the first
$ ! value is not.
$ !
$ CREATE TEST.UNL
06-14-1991:12:14:14.14
1991-06-14:12:14:14.14
$ !
$ ! Create an .rrd file that defines the TIMESTAMP field
$ ! as a TEXT field:
$ !
$ CREATE TEST.RRD
DEFINE FIELD COL1 DATATYPE IS TEXT SIZE 22.
DEFINE RECORD NEWTABLE.
COL1.
END NEWTABLE RECORD.
$ !
$ ! Attempt to load the data in the .unl file. Oracle RMU returns an
$ ! error on the first data record because the date was incorrectly
$ ! specified. The first record is written to the exception file,
$ ! BAD.DAT.
$ !
$ RMU/LOAD/RMS=(FILE=TEST.RRD,EXCEPT=BAD.DAT) MF_PERSONNEL.RDB -
_$ NEWTABLE TEST.UNL
%RMU-I-LOADERR, Error loading row 1.
%RDB-E-CONVERT_ERROR, invalid or unsupported data conversion
-COSI-F-IVTIME, invalid date or time
%RMU-I-DATRECREAD, 2 data records read from input file.
%RMU-I-DATRECSTO, 1 data records stored.
%RMU-I-DATRECREJ, 1 data records rejected.
$ !
$ ! Type BAD.DAT to view the incorrect data record
$ !
$ TYPE BAD.DAT
06-14-1991:12:14:14.14
$ !
$ ! Fetch the data record that stored successfully.
$ !
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SELECT * FROM NEWTABLE;
COL1
1991-06-14:12:14:14.14
1 rows selected
Example 9
Using the RMU Load command, you can load a table in a database by
placing the fields in a different order in the database than they
were in the input file.
The jobs.unl file contains the following:
000001000000000190001Rdb Demonstrator DEMO
The jobs.rrd file contains the following:
DEFINE FIELD J_CODE DATATYPE IS TEXT SIZE IS 4.
DEFINE FIELD WAGE_CL DATATYPE IS TEXT SIZE IS 1.
DEFINE FIELD J_TITLE DATATYPE IS TEXT SIZE IS 20.
DEFINE FIELD MIN_SAL DATATYPE IS TEXT SIZE 10.
DEFINE FIELD MAX_SAL DATATYPE IS TEXT SIZE 10.
DEFINE RECORD JOBS.
MIN_SAL.
MAX_SAL.
WAGE_CL.
J_TITLE.
J_CODE.
END JOBS RECORD.
The JOBS table has the following structure:
Columns for table JOBS:
Column Name Data Type Domain
----------- --------- ------
JOB_CODE CHAR(4) JOB_CODE_DOM
WAGE_CLASS CHAR(1) WAGE_CLASS_DOM
JOB_TITLE CHAR(20) JOB_TITLE_DOM
MINIMUM_SALARY INTEGER(2) SALARY_DOM
MAXIMUM_SALARY INTEGER(2) SALARY_DOM
Notice that:
o The ordering of the columns is different for the JOBS table in
the database and in the input RMS file.
o The names in the .rrd file are also different from the names
in the database.
o The data types of the salary fields are different (Oracle Rdb
will do the conversion).
To load the RMS file correctly, you must use the following
command:
$ RMU/LOAD MF_PERSONNEL JOBS JOBS/RMS=FILE=JOBS -
_$ /FIELDS=(MINIMUM_SALARY,MAXIMUM_SALARY,WAGE_CLASS,JOB_TITLE, -
_$ JOB_CODE)
Notice that the Fields qualifier uses the names of the columns in
the JOBS table (not the field names in the .rrd file), but in the
order of the RMS file.
The names in the .rrd file are immaterial. The purpose of the
Fields qualifier is to load the first field in the RMS file into
the MINIMUM_SALARY column of the JOBS table, load the second
field in the RMS file into the MAXIMUM_SALARY column of the JOBS
table, and so forth.
The results:
SQL> SELECT * FROM JOBS WHERE JOB_CODE = 'DEMO';
JOB_CODE WAGE_CLASS JOB_TITLE MINIMUM_SALARY MAXIMUM_SALARY
DEMO 1 Rdb Demonstrator $10,000.00 $19,000.00
Example 10
The following example shows the sequence of steps used to sort
a file into placement order by using the Place qualifier and the
Place_Only option and then to load the file by using the Commit_
Every qualifier:
$ RMU/LOAD/PLACE -
_$ /RECORD_DEFINITION=(FILE=NAMES.RRD,PLACE_ONLY=PLACED_NAMES) -
_$ MF_PERSONNEL EMPLOYEES UNLOADED_NAMES.UNL
$ RMU/LOAD/RECORD_DEFINITION=(FILE=NAMES.RRD) -
_$ /COMMIT_EVERY=30 MF_PERSONNEL -
_$ EMPLOYEES PLACED_NAMES.UNL
%RMU-I-DATRECREAD, 100 data records read from input file.
%RMU-I-DATRECSTO, 100 data records stored.
Example 11
The following example requests that statistics be displayed
at a regular interval of every minute. It loads the data from
the RMS file, names.unl, into the EMPLOYEES table of the mf_
personnel database. The record structure of EMPLOYEES is in the
file names.rrd. The names.rrd file was created by a previous RMU
Unload command that unloaded data from a subset of columns in the
EMPLOYEES table.
$ RMU/LOAD/STATISTICS=(INTERVAL=60) -
_$ /RECORD_DEFINITION=(FILE=NAMES) -
_$ /FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
Example 12
The following example uses the Exception_File option to the
Record_Definition qualifier to tell Oracle RMU the name of
the file to hold the exception records. Oracle RMU returns
informational messages to alert you to any data records rejected.
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /RECORD_DEFINITION=(FILE=TEXT_NAMES,EXCEPTION_FILE=FILE.UNL) -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-LOADERR, Error loading row 1.
%RDB-E-NO_DUP, index field value already exists; duplicates not
allowed for EMPLOYEES_HASH
%RMU-I-LOADERR, Error loading row 17.
%RDB-E-NO_DUP, index field value already exists; duplicates not
allowed for EMPLOYEES_HASH
%RMU-I-LOADERR, Error loading row 33.
%RDB-E-NO_DUP, index field value already exists; duplicates not
allowed for EMPLOYEES_HASH
%RMU-I-LOADERR, Error loading row 155.
%RDB-E-NO_DUP, index field value already exists; duplicates not
allowed for EMPLOYEES_HASH
%RMU-I-DATRECREAD, 200 data records read from input file.
%RMU-I-DATRECSTO, 196 data records stored.
%RMU-I-DATRECREJ, 4 data records rejected.
Example 13
The following is an example of the format in which you can
provide input data to the RMU Load command when you use the
Format=Delimited_Text option with the Record_Definition
qualifier. This is followed by the RMU Load command you use to
load this data.
"99997","ABUSHAKRA","CAROLINE","S","5 CIRCLE STREET","BOX 506",
"CHELMSFORD", "MA", "02184", "1960061400000000"#
"99996","BRADFORD","LEO","M","4 PLACE STREET","BOX 555",
"NASHUA","NH", "03060", "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= NAMES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#" ) -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECREAD, 2 data records read from input file.
%RMU-I-DATRECSTO, 2 data records stored.
Example 14
The following is an example of the format in which you must
provide input data to the RMU Load command when you specify the
Format=Text option with the Record_Definition qualifier. This is
followed by the RMU Load command you use to load this data.
09166Watts Leora F
09190Margolis David M
09187McDonald Lois F
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, SEX) -
_$ /RECORD_DEFINITION=(FILE=TEXT_NAMES.RRD, FORMAT=TEXT) -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECREAD, 3 data records read from input file.
%RMU-I-DATRECSTO, 3 data records stored.
7 – Examples (Cont.)
Example 15
The following example assumes you want to load a data file
into the JOBS table that contains more fields than the table
definition in the mf_personnel database. The example first
attempts to do this by just excluding the extra field from the
list associated with the Fields qualifier. However, this causes
an error to be returned. The example then uses the FILLER keyword
in the .rrd file to tell Oracle RMU not to attempt to load the
additional field. The command executes successfully.
The table definition for the JOBS table is as follows:
Columns for table JOBS:
Column Name Data Type Domain
----------- --------- ------
JOB_CODE CHAR(4) JOB_CODE_DOM
Primary Key constraint JOBS_PRIMARY_JOB_CODE
WAGE_CLASS CHAR(1) WAGE_CLASS_DOM
JOB_TITLE CHAR(20) JOB_TITLE_DOM
MINIMUM_SALARY INTEGER(2) SALARY_DOM
MAXIMUM_SALARY INTEGER(2) SALARY_DOM
The .rrd file for the data you want to load appears as follows
(note that there is no corresponding field to JOB_STATUS in the
mf_personnel database definition for the JOBS table):
DEFINE FIELD JOB_CODE DATATYPE IS TEXT SIZE IS 4.
DEFINE FIELD WAGE_CLASS DATATYPE IS TEXT SIZE IS 1.
DEFINE FIELD JOB_TITLE DATATYPE IS TEXT SIZE IS 20.
DEFINE FIELD MINIMUM_SALARY DATATYPE IS TEXT SIZE IS 13.
DEFINE FIELD MAXIMUM_SALARY DATATYPE IS TEXT SIZE IS 13.
DEFINE FIELD JOB_STATUS DATATYPE IS TEXT SIZE IS 4.
DEFINE RECORD JOBS.
JOB_CODE .
WAGE_CLASS .
JOB_TITLE .
MINIMUM_SALARY .
MAXIMUM_SALARY .
JOB_STATUS .
END JOBS RECORD.
The data file you want to load, jobs.unl, appears as follows:
DBAD4Corp Db Administratr55000.00 95000.00 Old
You attempt to load the file in the mf_personnel database
by listing only the fields in the RMU Load command that have
corresponding fields defined in the database:
$ RMU/LOAD MF_PERSONNEL/RMS=(FILE=JOBS.RRD, FORMAT=TEXT) -
_$ /FIELDS=(JOB_CODE, WAGE_CLASS, JOB_TITLE, MINIMUM_SALARY, -
_$ MAXIMUM_SALARY) JOBS JOBS.UNL
%RMU-F-FLDMUSMAT, Specified fields must match in number and datatype
with the unloaded data
%RMU-I-DATRECSTO, 0 data records stored
The workaround for the problem of a mismatch between your data
and .rrd file, and database definition for a table is to use the
FILLER keyword in your .rrd file, as follows:
DEFINE FIELD JOB_CODE DATATYPE IS TEXT SIZE IS 4.
DEFINE FIELD WAGE_CLASS DATATYPE IS TEXT SIZE IS 1.
DEFINE FIELD JOB_TITLE DATATYPE IS TEXT SIZE IS 20.
DEFINE FIELD MINIMUM_SALARY DATATYPE IS TEXT SIZE IS 13.
DEFINE FIELD MAXIMUM_SALARY DATATYPE IS TEXT SIZE IS 13.
DEFINE FIELD JOB_STATUS DATATYPE IS TEXT SIZE IS 4 FILLER. <------
DEFINE RECORD JOBS.
JOB_CODE .
WAGE_CLASS .
JOB_TITLE .
MINIMUM_SALARY .
MAXIMUM_SALARY .
JOB_STATUS .
END JOBS RECORD.
Now that the .rrd file has been modified, attempt to load the
record again:
$ RMU/LOAD MF_PERSONNEL/RMS=(FILE=JOBS.RRD, FORMAT=TEXT) -
_$ /FIELDS=(JOB_CODE, WAGE_CLASS, JOB_TITLE, MINIMUM_SALARY, -
_$ MAXIMUM_SALARY) JOBS JOBS.UNL
%RMU-I-DATRECSTO, 1 data records stored.
Example 16
The following example demonstrates the use of the Null="*" option
of the Record_Definition qualifier to signal to Oracle RMU that
any data that appears as an unquoted asterisk in the .unl file
should have the corresponding column in the database be flagged
as NULL.
The example shows the contents of the .unl file, followed by the
RMU Load command used to load this .unl file, and then the output
from an SQL statement to display the data loaded.
"98888","ABUSHAKRA","CAROLINE",*,"5 CIRCLE STREET","BOX 506",
"CHELMSFORD", "MA", "02184", "1960061400000000"#
"98889","BRADFORD","LEO",*,"4 PLACE STREET","BOX 555", "NASHUA","NH",
"03060", "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.
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID > '98000'
cont> AND MIDDLE_INITIAL IS NULL;
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
98888 ABUSHAKRA CAROLINE NULL
5 CIRCLE STREET BOX 506 CHELMSFORD
MA 02184 ? 14-Jun-1960 N
98889 BRADFORD LEO NULL
4 PLACE STREET BOX 555 NASHUA
NH 03060 ? 18-May-1949 N
2 rows selected
Example 17
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 be flagged as NULL.
The example shows the contents of the .unl file, followed by the
RMU Load command used to load this .unl file, and then the output
from an SQL statement to display the data loaded.
"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.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SELECT * FROM EMPLOYEES WHERE ADDRESS_DATA_2 IS NULL;
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
90021 ABUSHAKRA CAROLINE A
5 CIRCLE STREET NULL CHELMSFORD
MA 02184 ? 14-Jun-1960 N
90015 BRADFORD LEO B
4 PLACE STREET NULL NASHUA
NH 03030 ? 18-May-1949 N
2 rows selected
Example 18
The following example is the same as Example 17 except it shows
the use of the default value for 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 be flagged as NULL.
The example shows the contents of the .unl file, followed by the
RMU Load command used to load this .unl file, and then the output
from an SQL statement to display the data loaded.
"90022","ABUSHAKRA","CAROLINE","A","5 CIRCLE STREET",,
"CHELMSFORD", "MA", "02184", "1960061400000000"#
"90014","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.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = '90022' OR
cont> EMPLOYEE_ID ='90014' AND ADDRESS_DATA_2 IS NULL;
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
90014 BRADFORD LEO B
4 PLACE STREET NULL NASHUA
NH 03030 ? 18-May-1949 N
90022 ABUSHAKRA CAROLINE A
5 CIRCLE STREET NULL CHELMSFORD
MA 02184 ? 14-Jun-1960 N
2 rows selected
Example 19
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 be flagged as NULL. In
addition, any column for which there is only data for the first
column or columns has the remaining columns set to NULL.
The example shows the contents of the .unl file, followed by the
RMU Load command used to load this .unl file, and then the output
from an SQL statement to display the data loaded.
"90026","ABUSHAKRA","CAROLINE","A","5 CIRCLE STREET","BOX 783",
"CHELMSFORD","MA", "02184", "1960061400000000"
"90011","BRADFORD","LEO",,,, "NASHUA","NH","03030","1949051800000000"
"90010"
"90009",,,,,,,,,"1966061600000000"
$ 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, -
_$ NULL) -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECREAD, 5 data records read from input file.
%RMU-I-DATRECSTO, 5 data records stored.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL.RDB';
SQL> SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID ='90026' OR
cont> EMPLOYEE_ID BETWEEN '90009' AND '90011';
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
90009 NULL NULL NULL
NULL NULL NULL
NULL NULL ? 16-Jun-1966 N
90010 NULL NULL NULL
NULL NULL NULL
NULL NULL ? NULL N
90011 BRADFORD LEO NULL
NULL NULL NASHUA
NH 03030 ? 18-May-1949 N
90026 ABUSHAKRA CAROLINE A
5 CIRCLE STREET BOX 783 CHELMSFORD
MA NULL ? 14-Jun-1960 N
4 rows selected
Example 20
The following example demonstrates a parallel load operation.
In this example, three executors are specified because there are
three storage areas in the JOB_HISTORY table of the mf_personnel
database. The Defer_Index_Updates qualifier is used because there
are no constraints or triggers defined on the JOB_HISTORY table,
and it is known that no other database activity will occur when
this command is executed.
In addition, a plan file is generated to capture the
specification of this load operation. See the next example for
a description of the plan file.
Note that the pid provided in the output from this command is the
process ID.
$ RMU/LOAD/PARALLEL=(EXEC=3)/DEFER_INDEX_UPDATES mf_personnel.rdb -
_$ /RECORD_DEFINITION=(FILE=JOB_HIST,FORMAT=DELIMITED_TEXT, -
_$ EXCEPTION_FILE=DISK1:[ERRORS]JOB_HIST.EXC) -
_$ /STATISTICS=(INTERVAL=30)/LIST_PLAN=JOB_HISTORY.PLAN -
_$ JOB_HISTORY JOB_HIST.UNL
%RMU-I-EXECUTORMAP, Executor EXECUTOR_1 (pid: 2941941B) will load
storage area EMPIDS_LOW.
%RMU-I-EXECUTORMAP, Executor EXECUTOR_2 (pid: 2941F01D) will load
storage area EMPIDS_MID.
%RMU-I-EXECUTORMAP, Executor EXECUTOR_3 (pid: 2941C81F) will load
storage area EMPIDS_OVER.
--------------------------------------------------------------------------
ELAPSED: 0 00:00:30.05 CPU: 0:00:01.64 BUFIO: 59 DIRIO: 219 FAULTS: 2670
1640 data records read from input file.
1330 records loaded before last commit.
220 records loaded in current transaction.
0 records rejected before last commit.
0 records rejected in current transaction.
26 early commits by executors.
3 executors: 0 Initializing; 0 Idle; 0 Terminated
0 Sorting; 2 Storing; 1 Committing; 0 Executing
--------------------------------------------------------------------------
.
.
.
--------------------------------------------------------------------------
ELAPSED: 0 00:02:30.12 CPU: 0:00:02.94 BUFIO: 103 DIRIO: 227 FAULTS: 267
1
8070 data records read from input file.
7800 records loaded before last commit.
210 records loaded in current transaction.
0 records rejected before last commit.
0 records rejected in current transaction.
139 early commits by executors.
3 executors: 0 Initializing; 0 Idle; 0 Terminated
0 Sorting; 1 Storing; 2 Committing; 0 Executing
---------------------------------------------------------------------------
%RMU-I-EXECSTAT0, Statistics for EXECUTOR_1:
%RMU-I-EXECSTAT1, Elapsed time: 00:02:45.84 CPU time: 12.95
%RMU-I-EXECSTAT2, Storing time: 00:00:45.99 Rows stored: 2440
%RMU-I-EXECSTAT3, Commit time: 00:01:33.17 Direct I/O: 6623
%RMU-I-EXECSTAT4, Idle time: 00:00:22.34 Early commits: 47
%RMU-I-EXECSTAT0, Statistics for EXECUTOR_2:
%RMU-I-EXECSTAT1, Elapsed time: 00:02:48.42 CPU time: 18.10
%RMU-I-EXECSTAT2, Storing time: 00:01:24.98 Rows stored: 4319
%RMU-I-EXECSTAT3, Commit time: 00:01:18.13 Direct I/O: 9621
%RMU-I-EXECSTAT4, Idle time: 00:00:01.03 Early commits: 29
%RMU-I-EXECSTAT0, Statistics for EXECUTOR_3:
%RMU-I-EXECSTAT1, Elapsed time: 00:02:46.50 CPU time: 9.78
%RMU-I-EXECSTAT2, Storing time: 00:00:11.12 Rows stored: 2293
%RMU-I-EXECSTAT3, Commit time: 00:02:26.67 Direct I/O: 3101
%RMU-I-EXECSTAT4, Idle time: 00:00:04.14 Early commits: 77
%RMU-I-EXECSTAT5, Main process idle time: 00:02:41.06
%RMU-I-DATRECREAD, 9052 data records read from input file.
%RMU-I-DATRECSTO, 9052 data records stored.
%RMU-I-DATRECREJ, 0 data records rejected.
Example 21
The following command is the same as in the previous example,
except the Noexecute qualifier is specified. Because this
qualifier is specified, the load operation is not performed.
However, the load plan file is created and verified.
$ RMU/LOAD/PARALLEL=(EXEC=3)/DEFER_INDEX_UPDATES/NOEXECUTE -
_$ mf_personnel.rdb -
_$ /RECORD_DEFINITION=(FILE=JOB_HIST,FORMAT=DELIMITED_TEXT, -
_$ EXCEPTION_FILE=DISK1:[ERRORS]JOB_HIST.EXC) -
_$ /STATISTICS=(INTERVAL=30)/LIST_PLAN=JOB_HISTORY.PLAN -
_$ JOB_HISTORY JOB_HIST.UNL
Example 22
The following display shows the contents of the plan file,
JOB_HISTORY.PLAN, created in the preceding example. The following
callouts are keyed to this display:
1 The Plan Parameters include all the parameters specified on
the RMU Load command line and all possible command qualifiers.
2 Command qualifiers that are not specified on the command line
are sometimes represented as comments in the plan file. This
allows you to edit and adjust the plan file for future use.
3 Command qualifiers that are not specified on the command line
and for which there are defaults are sometimes represented
with their default value in the plan file.
4 Command qualifiers that are explicitly specified on the
command line are represented in the plan file as specified.
5 Executor Parameters are listed for each executor involved
in the load operation. Like the command qualifiers, both the
values you specify on the command line and those that are
allowed but were not specified are included in this list of
parameters.
6 Note that the exception file extension is appended with the
executor number. When you specify such files on the command
line, Oracle RMU generates a separate file for each executor.
If desired, you could edit this plan file to place each
exception file on a different disk or directory.
! Plan created on 20-JUL-1995 by RMU/LOAD.
Plan Name = LOAD_PLAN
Plan Type = LOAD
Plan Parameters:1
Database Root File = MF_PERSONNEL.RDB;
Table Name = JOB_HISTORY
Input File = JOB_HIST.UNL
! Fields = <all> 2
Transaction_Type = PROTECTED
! Buffers = <default>
Row_Count = 50 3
! Skip = <none>
NoLog_Commits
NoCorresponding
Defer_Index_Updates
Constraints
Parallel
NoPlace
Statistics = INTERVAL = 30 4
NoTrigger_Relations
Record_Definition_File = JOB_HIST
Format = Delimited_Text
Prefix = """"
Suffix = """"
NoNull
Separator = ","
End Of Line Terminator
End Plan Parameters
Executor Parameters: 5
Executor Name = EXECUTOR_1
! Place_Only = <none>
Exception_File = DISK1:[DATABASE]JOB_HIST.EXC_1; 6
! RUJ Directory = <default>
Communication Buffers = 4
End Executor Parameters
Executor Parameters:
Executor Name = EXECUTOR_2
! Place_Only = <none>
Exception_File = DISK1:[DATABASE]JOB_HIST.EXC_2;
! RUJ Directory = <default>
Communication Buffers = 4
End Executor Parameters
Executor Parameters:
Executor Name = EXECUTOR_3
! Place_Only = <none>
Exception_File = DISK1:[DATABASE]JOB_HIST.EXC_3;
! RUJ Directory = <default>
Communication Buffers = 4
End Executor Parameters
Example 23
The following example demonstrates the structure of the record
definition file (.rrd) for an RMU Load command for several
different data types. The first part of the example displays the
table definition, the second part shows the RMU Unload command
you could use to get an appropriate .rrd file for these data
types, and the last part shows the .rrd file definitions for
these data types:
SQL> attach 'filename data_types.rdb';
SQL> show table many_types;
Information for table MANY_TYPES
Columns for table MANY_TYPES:
Column Name Data Type Domain
----------- --------- ------
F_ID TINYINT
F_CHAR_3 CHAR(3)
F_TINYINT TINYINT
F_SMALLINT SMALLINT
F_INTEGER INTEGER
F_BIGINT BIGINT
F_NTINYINT TINYINT(1)
F_NSMALLINT SMALLINT(2)
F_NINTEGER INTEGER(7)
F_NBIGINT BIGINT(5)
F_REAL REAL
F_DOUBLE_PREC DOUBLE PRECISION
F_DATE_VMS DATE VMS
F_DATE_ANSI DATE ANSI
F_VARCHAR VARCHAR(20)
F_FLOAT REAL
F_DATE DATE VMS
F_TIME TIME
F_TIMESTAMP TIMESTAMP(2)
F_INTERVAL INTERVAL
DAY (2)
$ RMU/UNLOAD DATA_TYPES.RDB/RECORD_DEF=(FILE=MANY_TYPES.RRD) -
_$ MANY_TYPES MANY_TYPES.UNL
$ TYPE MANY_TYPES.RRD
DEFINE FIELD F_ID DATATYPE IS SIGNED BYTE.
DEFINE FIELD F_CHAR_3 DATATYPE IS TEXT SIZE IS 3.
DEFINE FIELD F_TINYINT DATATYPE IS SIGNED BYTE.
DEFINE FIELD F_SMALLINT DATATYPE IS SIGNED WORD.
DEFINE FIELD F_INTEGER DATATYPE IS SIGNED LONGWORD.
DEFINE FIELD F_BIGINT DATATYPE IS SIGNED QUADWORD.
DEFINE FIELD F_NTINYINT DATATYPE IS SIGNED BYTE SCALE -1.
DEFINE FIELD F_NSMALLINT DATATYPE IS SIGNED WORD SCALE -2.
DEFINE FIELD F_NINTEGER DATATYPE IS SIGNED LONGWORD SCALE -7.
DEFINE FIELD F_NBIGINT DATATYPE IS SIGNED QUADWORD SCALE -5.
DEFINE FIELD F_REAL DATATYPE IS F_FLOATING.
DEFINE FIELD F_DOUBLE_PREC DATATYPE IS G_FLOATING.
DEFINE FIELD F_DATE_VMS DATATYPE IS DATE.
DEFINE FIELD F_DATE_ANSI DATATYPE IS DATE ANSI.
DEFINE FIELD F_VARCHAR DATATYPE IS TEXT SIZE IS 20.
DEFINE FIELD F_FLOAT DATATYPE IS F_FLOATING.
DEFINE FIELD F_DATE DATATYPE IS DATE.
DEFINE FIELD F_TIME DATATYPE IS TIME.
DEFINE FIELD F_TIMESTAMP DATATYPE IS TIMESTAMP SCALE -2.
DEFINE FIELD F_INTERVAL DATATYPE IS INTERVAL DAY SIZE IS 2 DIGITS.
DEFINE RECORD MANY_TYPES.
F_ID .
F_CHAR_1 .
. . .
END MANY_TYPES RECORD.
Example 24
The following example shows part of a script for loading a copy
of the PERSONNEL database using the output from SQL EXPORT.
$! Export the database definition and the data
$ sql$ export database filename personnel into pers.rbr;
$
$! Create an empty database (use RMU Load to add data)
$ sql$ import database from pers.rbr filename copy_pers no data;
$
$! Now use load to add the same table
$ rmu/load copy_pers /match_name=employees employees pers.rbr
%RMU-I-DATRECREAD, 100 data records read from input file.
%RMU-I-DATRECSTO, 100 data records stored.
$
$ rmu/load copy_pers /match_name job_history pers.rbr
%RMU-I-DATRECREAD, 274 data records read from input file.
%RMU-I-DATRECSTO, 274 data records stored.
$
$ rmu/load copy_pers /match_name salary_history pers.rbr
%RMU-I-DATRECREAD, 729 data records read from input file.
%RMU-I-DATRECSTO, 729 data records stored.
$
.
.
.
$ rmu/load copy_pers /match_name work_status pers.rbr
%RMU-I-DATRECREAD, 3 data records read from input file.
%RMU-I-DATRECSTO, 3 data records stored.
Example 25
The following example shows that, by default, truncation errors
during a Load are reported.
$ rmu/load abc f2 f1
%RMU-I-LOADERR, Error loading row 1.
%RDB-E-TRUN_STORE, string truncated during assignment to a column
%RMU-I-DATRECREAD, 1 data records read from input file.
%RMU-I-DATRECSTO, 0 data records stored.
%RMU-F-FTL_LOAD, Fatal error for LOAD operation at 13-FEB-2008 15:39:44.40
$
Example 26
The following example shows the use of the /VIRTUAL_FIELDS
qualifier. The values of the INTEGER field A and the AUTOMATIC
field B are first unloaded into the AA.UNL file from the RMU_
LOAD_AUTOMATIC_4_DB database table AA using the /VIRTUAL_
FIELDS qualifier. Then the values of the INTEGER field A and
the AUTOMATIC field B in the AA.UNL file are loaded into the AA
table in the RMU_LOAD_AUTOMATIC_4_DB2 database.
$ SQL
create database
filename RMU_LOAD_AUTOMATIC_4_DB;
-- create a sequence and a table
create sequence S increment by -1;
create table AA
(a integer
,b automatic as s.nextval);
-- load 10 rows
begin
declare :i integer;
for :i in 1 to 10
do
insert into AA (a) values (:i);
end for;
end;
commit;
disconnect all;
$ exit
$ rmu/unload-
/virtual=(automatic)-
/record=(file=rr,format=delim)-
RMU_LOAD_AUTOMATIC_4_DB aa aa.unl
%RMU-I-DATRECUNL, 10 data records unloaded.
$
$
$! Load using /VIRTUAL
$ rmu/load-
/record=(file=rr,format=delim)-
/virtual-
RMU_LOAD_AUTOMATIC_4_DB2 aa aa.unl
%RMU-I-DATRECREAD, 10 data records read from input file.
%RMU-I-DATRECSTO, 10 data records stored.
$