Example 1
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database. The data is stored in names.unl.
$ RMU/UNLOAD -
_$ /FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
Example 2
The following command unloads the EMPLOYEES table from the
mf_personnel database and places the data in the RMS file,
names.unl. The names.rrd file contains the record structure
definitions for the data in names.unl.
$ RMU/UNLOAD/RECORD_DEFINITION=FILE=NAMES.RRD MF_PERSONNEL -
_$ EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
Example 3
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database and accepts the default values for delimiters, as shown
by viewing the names.unl file:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
-$ /RECORD_DEFINITION=(FILE=NAMES, FORMAT=DELIMITED_TEXT) -
-$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
$ !
$ ! TYPE the names.unl file to see the effect of the RMU Unload
$ ! command.
$ !
$ TYPE NAMES.UNL
"00164","Toliver "
"00165","Smith "
"00166","Dietrich "
"00167","Kilpatrick "
"00168","Nash "
.
.
.
Example 4
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database and specifies the asterisk (*) character as the string
to mark the beginning and end of each column (the prefix and
suffix string):
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /RECORD_DEFINITION=(FILE=NAMES, -
_$ FORMAT=DELIMITED_TEXT, SUFFIX="*", -
_$ PREFIX="*") -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
%RMU-I-DATRECUNL, 100 data records unloaded.
$ !
$ ! TYPE the names.unl file to see the effect of the RMU Unload
$ ! command.
$ !
$ TYPE NAMES.UNL
*00164*,*Toliver *
*00165*,*Smith *
*00166*,*Dietrich *
*00167*,*Kilpatrick *
*00168*,*Nash *
*00169*,*Gray *
*00170*,*Wood *
*00171*,*D'Amico *
.
.
.
Example 5
The following command unloads all column values from the
EMPLOYEES table of the mf_personnel database, and specifies the
Format=Text option of the Record_Definition qualifier. Oracle RMU
will convert all the data to printable text, as can be seen by
viewing the text_output.unl file:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=TEXT_RECORD,FORMAT=TEXT) -
_$ MF_PERSONNEL EMPLOYEES TEXT_OUTPUT
%RMU-I-DATRECUNL, 100 data records unloaded.
$ !
$ ! TYPE the text_output.unl file to see the effect of the RMU Unload
$ ! command.
$ !
$ TYPE TEXT_OUTPUT.UNL
00164Toliver Alvin A146 Parnell Place
Chocorua NH03817M19470328000000001
00165Smith Terry D120 Tenby Dr.
Chocorua NH03817M19540515000000002
00166Dietrich Rick 19 Union Square
Boscawen NH03301M19540320000000001
.
.
.
Example 6
The following command unloads the EMPLOYEE_ID and LAST_NAME
column values from the EMPLOYEES table of the mf_personnel
database and requests that statistics be displayed on the
terminal at 2-second intervals:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME) -
_$ /STATISTICS_INTERVAL=2 -
_$ MF_PERSONNEL EMPLOYEES NAMES.UNL
Example 7
The following example unloads a subset of data from the EMPLOYEES
table, using the following steps:
1. Create a temporary view on the EMPLOYEES table that includes
only employees who live in Massachusetts.
2. Use an RMU Unload command to unload the data from this view.
3. Delete the temporary view.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> CREATE VIEW MA_EMPLOYEES
cont> (EMPLOYEE_ID,
cont> LAST_NAME,
cont> FIRST_NAME,
cont> MIDDLE_INITIAL,
cont> STATE,
cont> STATUS_CODE)
cont> AS SELECT
cont> E.EMPLOYEE_ID,
cont> E.LAST_NAME,
cont> E.FIRST_NAME,
cont> E.MIDDLE_INITIAL,
cont> E.STATE,
cont> E.STATUS_CODE
cont> FROM EMPLOYEES E
cont> WHERE E.STATE='MA';
SQL> COMMIT;
SQL> EXIT;
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=MA_EMPLOYEES,FORMAT=DELIMITED_TEXT) -
_$ MF_PERSONNEL MA_EMPLOYEES MA_EMPLOYEES.UNL
%RMU-I-DATRECUNL, 9 data records unloaded.
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> DROP VIEW MA_EMPLOYEES;
SQL> COMMIT;
Example 8
The following example shows that null values in blank columns
are not preserved unless the Null option is specified with the
Delimited_Text option of the Record_Definition qualifier:
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Create the NULL_DATE table:
SQL> CREATE TABLE NULL_DATE
cont> (COL1 VARCHAR(5),
cont> DATE1 DATE,
cont> COL2 VARCHAR(5));
SQL> --
SQL> -- Store a row that does not include a value for the DATE1
SQL> -- column of the NULL_DATE table:
SQL> INSERT INTO NULL_DATE
cont> (COL1, COL2)
cont> VALUES ('first','last');
1 row inserted
SQL> --
SQL> COMMIT;
SQL> --
SQL> -- The previous SQL INSERT statement causes a null value to
SQL> -- be stored in NULL_DATE:
SQL> SELECT * FROM NULL_DATE;
COL1 DATE1 COL2
first NULL last
1 row selected
SQL> --
SQL> DISCONNECT DEFAULT;
SQL> EXIT;
$ !
$ ! In the following RMU Unload command, the Record_Definition
$ ! qualifier is used to unload the row with the NULL value, but
$ ! the Null option is not specified:
$ RMU/UNLOAD/RECORD_DEFINITION=(FILE=NULL_DATE,FORMAT=DELIMITED_TEXT) -
_$ MF_PERSONNEL NULL_DATE NULL_DATE
%RMU-I-DATRECUNL, 1 data records unloaded.
$ !
$ ! The null_date.unl file created by the previous unload
$ ! operation does not preserve the NULL value in the DATE1 column.
$ ! Instead, the Oracle Rdb default date value is used:
$ TYPE NULL_DATE.UNL
"first","1858111700000000","last"
$ !
$ ! This time, unload the row in NULL_DATE with the Null option to
$ ! the Record_Definition qualifier:
$ RMU/UNLOAD MF_PERSONNEL NULL_DATE NULL_DATE -
_$ /RECORD_DEFINITION=(FILE=NULL_DATE.RRD, FORMAT=DELIMITED_TEXT, NULL="*")
%RMU-I-DATRECUNL, 1 data records unloaded.
$ !
$ TYPE NULL_DATE.UNL
"first",*,"last "
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Delete the existing row from NULL_DATE:
SQL> DELETE FROM NULL_DATE;
1 row deleted
SQL> --
SQL> COMMIT;
SQL> EXIT;
$ !
$ ! Load the row that was unloaded back into the table,
$ ! using the null_date.unl file created by the
$ ! previous RMU Unload command:
$ RMU/LOAD MF_PERSONNEL /RECORD_DEFINITION=(FILE=NULL_DATE.RRD, -
_$ FORMAT=DELIMITED_TEXT, NULL="*") NULL_DATE NULL_DATE
%RMU-I-DATRECREAD, 1 data records read from input file.
%RMU-I-DATRECSTO, 1 data records stored.
$ !
$ SQL
SQL> ATTACH 'FILENAME MF_PERSONNEL';
SQL> --
SQL> -- Display the row stored in NULL_DATE.
SQL> -- The NULL value stored in the data row
SQL> -- was preserved by the load and unload operations:
SQL> SELECT * FROM NULL_DATE;
COL1 DATE1 COL2
first NULL last
1 row selected
Example 9
The following example demonstrates the use of the Null="" option
of the Record_Definition qualifier to signal to Oracle RMU that
any data that is an empty string in the .unl file (as represented
by two commas with no space separating them) should have the
corresponding column in the database flagged as NULL.
The first part of this example shows the contents of the .unl
file and the RMU Load command used to load the .unl file. The
terminator for each record in the .unl file is the number sign
(#). The second part of this example unloads unloads the data
and specifies that any columns that are flagged as NULL should be
represented in the output file with an asterisk.
"90021","ABUSHAKRA","CAROLINE","A","5 CIRCLE STREET",,
"CHELMSFORD", "MA", "02184", "1960061400000000"#
"90015","BRADFORD","LEO","B","4 PLACE STREET",, "NASHUA","NH",
"03030", "1949051800000000"#
$ !
$ RMU/LOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, -
_$ MIDDLE_INITIAL, ADDRESS_DATA_1, ADDRESS_DATA_2, -
_$ CITY, STATE, POSTAL_CODE, BIRTHDAY) -
_$ /RECORD_DEFINITION=(FILE= EMPLOYEES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#", -
_$ NULL="") -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECREAD, 2 data records read from input file.
%RMU-I-DATRECSTO, 2 data records stored.
$ !
$ ! Unload this data first without specifying the Null option:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, -
_$ MIDDLE_INITIAL, ADDRESS_DATA_1, ADDRESS_DATA_2, -
_$ CITY, STATE, POSTAL_CODE, BIRTHDAY) -
_$ /RECORD_DEFINITION=(FILE= EMPLOYEES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#") -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECUNL, 102 data records unloaded.
$ !
$ ! The ADDRESS_DATA_2 field appears as a quoted string:
$ TYPE EMPLOYEES.UNL
.
.
.
"90021","ABUSHAKRA ","CAROLINE ","A","5 CIRCLE STREET ","
","CHELMSFORD ","MA","02184","1960061400000000"#
$ !
$ ! Now unload the data with the Null option specified:
$ RMU/UNLOAD/FIELDS=(EMPLOYEE_ID, LAST_NAME, FIRST_NAME, -
_$ MIDDLE_INITIAL, ADDRESS_DATA_1, ADDRESS_DATA_2, -
_$ CITY, STATE, POSTAL_CODE, BIRTHDAY) -
_$ /RECORD_DEFINITION=(FILE= EMPLOYEES.RRD, -
_$ FORMAT=DELIMITED_TEXT, -
_$ TERMINATOR="#", -
_$ NULL="*") -
_$ MF_PERSONNEL EMPLOYEES EMPLOYEES.UNL
%RMU-I-DATRECUNL, 102 data records unloaded.
$ !
$ ! The value for ADDRESS_DATA_2 appears as an asterisk:
$ !
$ TYPE EMPLOYEES.UNL
.
.
.
"90021","ABUSHAKRA ","CAROLINE ","A","5 CIRCLE STREET ",*,
"CHELMSFORD ","MA","02184","1960061400000000"#
Example 10
The following example specifies a transaction for the RMU Unload
command equivalent to the SQL command SET TRANSACTION READ WRITE
WAIT 36 RESERVING table1 FOR SHARED READ;
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(SHARED,ISOLATION=REPEAT,WAIT=36)-
SAMPLE.RDB-
TABLE1-
TABLE.DAT
Example 11
The following example specifies the options that were the default
transaction style in prior releases.
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(READ_ONLY,ISOLATION_LEVEL=SERIALIZABLE)-
SAMPLE.RDB-
TABLE1-
TABLE1.DAT
Example 12
If the database currently has snapshots deferred, it may be more
efficient to start a read-write transaction with isolation level
read committed. This allows the transaction to start immediately
(a read-only transaction may stall), and the selected isolation
level keeps row locking to a minimum.
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(SHARED_READ,ISOLATION=READ_COMMITTED)-
SAMPLE.RDB-
TABLE1-
TABLE1.DAT
Using a transaction type of automatic adapts to different
database settings.
$ RMU/UNLOAD-
/TRANSACTION_TYPE=(AUTOMATIC)-
SAMPLE.RDB-
TABLE1-
TABLE1.DAT
Example 13
The following example shows the output from the flags STRATEGY
and ITEM_LIST which indicates that the Optimize qualifier
specified that sequential access be used, and also that Total_
Time is used as the default optimizer preference.
$ DEFINE RDMS$SET_FLAGS "STRATEGY,ITEM_LIST"
$ RMU/UNLOAD/OPTIMIZE=SEQUENTIAL_ACCESS PERSONNEL EMPLOYEES E.DAT
.
.
.
~H Request Information Item List: (len=11)
0000 (00000) RDB$K_SET_REQ_OPT_PREF "0"
0005 (00005) RDB$K_SET_REQ_OPT_SEQ "1"
000A (00010) RDB$K_INFO_END
Get Retrieval sequentially of relation EMPLOYEES
%RMU-I-DATRECUNL, 100 data records unloaded.
Example 14
AUTOMATIC columns are evaluated during INSERT and UPDATE
operations for a table; for instance, they may record the
timestamp for the last operation. If the table is being
reorganized, it may be necessary to unload the data and reload it
after the storage map and indexes for the table are re-created,
yet the old auditing data must remain the same.
Normally, the RMU Unload command does not unload columns marked
as AUTOMATIC; you must use the Virtual_Fields qualifier with the
keyword Automatic to request this action.
$ rmu/unload/virtual_fields=(automatic) payroll_db people people.unl
Following the restructure of the database, the data can be
reloaded. If the target columns are also defined as AUTOMATIC,
then the RMU Load process will not write to those columns. You
must use the Virtual_Fields qualifier with the keyword Automatic
to request this action.
$ rmu/load/virtual_fields=(automatic) payroll_db people people.unl
Example 15
This example shows the action of the Delete_Rows qualifier.
First, SQL is used to display the count of the rows in the table.
The file PEOPLE.COLUMNS is verified (written to SYS$OUTPUT) by
the RMU Unload command.
$ define sql$database db$:scratch
$ sql$ select count (*) from people;
100
1 row selected
$ rmu/unload/fields="@people.columns" -
sql$database -
/record_definition=(file:people,format:delimited) -
/delete_rows -
people -
people2.dat
EMPLOYEE_ID
LAST_NAME
FIRST_NAME
MIDDLE_INITIAL
SEX
BIRTHDAY
%RMU-I-DATRECERA, 100 data records erased.
%RMU-I-DATRECUNL, 100 data records unloaded.
A subsequent query shows that the rows have been deleted.
$ sql$ select count (*) from people;
0
1 row selected
Example 16
The following example shows the output from the RMU Unload
command options for XML support. The two files shown in the
example are created by this RMU Unload command:
$ rmu/unload -
/record_def=(format=xml,file=work_status) -
mf_personnel -
work_status -
work_status.xml
Output WORK_STATUS.DTD file
<?xml version="1.0"?>
<!-- RMU Unload for Oracle Rdb V7.1-00 -->
<!-- Generated: 16-MAR-2001 22:26:47.30 -->
<!ELEMENT WORK_STATUS (RMU_ROW*)>
<!ELEMENT RMU_ROW (
STATUS_CODE,
STATUS_NAME,
STATUS_TYPE
)>
<!ELEMENT STATUS_CODE (#PCDATA)>
<!ELEMENT STATUS_NAME (#PCDATA)>
<!ELEMENT STATUS_TYPE (#PCDATA)>
<!ELEMENT NULL (EMPTY)>
Output WORK_STATUS.XML file
<?xml version="1.0"?>
<!-- RMU Unload for Oracle Rdb V7.1-00 -->
<!-- Generated: 16-MAR-2001 22:26:47.85 -->
<!DOCTYPE WORK_STATUS SYSTEM "work_status.dtd">
<WORK_STATUS>
<RMU_ROW>
<STATUS_CODE>0</STATUS_CODE>
<STATUS_NAME>INACTIVE</STATUS_NAME>
<STATUS_TYPE>RECORD EXPIRED</STATUS_TYPE>
</RMU_ROW>
<RMU_ROW>
<STATUS_CODE>1</STATUS_CODE>
<STATUS_NAME>ACTIVE </STATUS_NAME>
<STATUS_TYPE>FULL TIME </STATUS_TYPE>
</RMU_ROW>
<RMU_ROW>
<STATUS_CODE>2</STATUS_CODE>
<STATUS_NAME>ACTIVE </STATUS_NAME>
<STATUS_TYPE>PART TIME </STATUS_TYPE>
</RMU_ROW>
</WORK_STATUS>
<!-- 3 rows unloaded -->
Example 17
The following example shows that if the Flush=On_Commit qualifier
is specified, the value for the Commit_Every qualifier must be
equal to or a multiple of the Row_Count value so the commits
of unload transactions occur after the internal RMS buffers are
flushed to the unload file. This prevents loss of data if an
error occurs.
$RMU/UNLOAD/ROW_COUNT=5/COMMIT_EVERY=2/FLUSH=ON_COMMIT MF_PERSONNEL -
_$ EMPLOYEES EMPLOYEES
%RMU-F-DELROWCOM, For DELETE_ROWS or FLUSH=ON_COMMIT the COMMIT_EVERY value must
equal or be a multiple of the ROW_COUNT value.
The COMMIT_EVERY value of 2 is not equal to or a multiple of the ROW_COUNT value
of 5.
%RMU-F-FTL_UNL, Fatal error for UNLOAD operation at 27-Oct-2005 08:55:14.06
Example 18
The following examples show that the unload file and record
definition files are not deleted on error if the Noerror_Delete
qualifier is specified and that these files are deleted on error
if the Error_Delete qualifier is specified. If the unload file is
empty when the error occurs, it will be deleted.
$RMU/UNLOAD/NOERROR_DELETE/ROW_ACOUNT=50/COMMIT_EVERY=50 MF_PERSONNEL -
_$ EMPLOYEES EMPLOYEES.UNL
%RMU-E-OUTFILNOTDEL, Fatal error, the output file is not deleted but may not
be useable,
50 records have been unloaded.
-COSI-F-WRITERR, write error
-RMS-F-FUL, device full (insufficient space for allocation)
$RMU/UNLOAD/ERROR_DELETE/ROW_COUNT=50/COMMIT_EVERY=50 MF_PERSONNEL -
_$ EMPLOYEES EMPLOYEES.UNL
%RMU-E-OUTFILDEL, Fatal error, output file deleted
-COSI-F-WRITERR, write error
-RMS-F-FUL, device full (insufficient space for allocation)
Example 19
The following example shows the FORMAT=CONTROL option. This
command creates a file EMP.CTL (the SQL*Loader control file)
and EMPLOYEES.DAT in a portable format to be loaded.
$ RMU/UNLOAD/RECORD_DEFINITION=(FORMAT=CONTROL,FILE=EMP) -
SQL$DATABASE -
EMPLOYEES -
EMPLOYEES
Example 20
The following shows an example of using the COMPRESSION qualifier
with the RMU Unload command.
$ RMU/UNLOAD/COMPRESS=LZW/DEBUG=TRACE COMPLETE_WORKS COMPLETE_WORKS
COMPLETE_WORKS
Debug = TRACE
Compression = LZW
* Synonyms are not enabled
Unloading Blob columns.
Row_Count = 500
Message buffer: Len: 54524
Message buffer: Sze: 109, Cnt: 500, Use: 31 Flg: 00000000
** compress data: input 2700 output 981 deflate 64%
** compress TEXT_VERSION : input 4454499 output 1892097 deflate 58%
** compress PDF_VERSION : input 274975 output 317560 deflate -15%
%RMU-I-DATRECUNL, 30 data records unloaded.
Example 21
The following shows an example of using the COMPRESSION qualifier
with RMU Unload and using the EXCLUDE_LIST option to avoid
attempting to compress data that does not compress.
$ RMU/UNLAOD/COMPRESS=(LZW,EXCLUDE_LIST:PDF_VERSION)/DEBUG=TRACE COMPLETE_WORKS
COMPLETE_WORKS COMPLETE_WORKS
Debug = TRACE
Compression = LZW
Exclude_List:
Exclude column PDF_VERSION
* Synonyms are not enabled
Unloading Blob columns.
Row_Count = 500
Message buffer: Len: 54524
Message buffer: Sze: 109, Cnt: 500, Use: 31 Flg: 00000000
** compress data: input 2700 output 981 deflate 64%
** compress TEXT_VERSION : input 4454499 output 1892097 deflate 58%
%RMU-I-DATRECUNL, 30 data records unloaded.