Example 1
The following command extracts these database items:
COLLATING_SEQUENCES, DOMAINS, TABLES, INDEXES, STORAGE_MAPS,
VIEWS, SEQUENCES, and TRIGGERS.
The All option is the default. The All or Noall option can be
used in conjunction with other items to select specific output.
For example, the Items=(All,Nodatabase) qualifier selects all
metadata items except the physical database characteristics.
$ RMU/EXTRACT/ITEM=(ALL, NODATABASE) MF_PERSONNEL
Example 2
The following command generates a DCL command procedure
containing an RMU Load command for each table in the database:
$ RMU/EXTRACT/ITEMS=LOAD MF_PERSONNEL
Example 3
The following command displays the protection access control list
(ACL) definitions in the mf_personnel.rdb database:
$ RMU/EXTRACT/ITEMS=PROTECTIONS MF_PERSONNEL.RDB
Example 4
The following command generates a DCL command procedure
containing an RMU Unload command for each table in the database:
$ RMU/EXTRACT/ITEMS=UNLOAD MF_PERSONNEL.RDB
Example 5
The following example displays index definitions:
$ RMU/EXTRACT/ITEMS=INDEXES MF_PERSONNEL
Example 6
The following example displays domain and table definitions. Note
that the Noall option could have been omitted.
$ RMU/EXTRACT/ITEMS=(NOALL,DOMAINS,TABLES) MF_PERSONNEL
Example 7
The following example displays definitions for domains (fields)
and tables (relations) that reference data dictionary path names
rather than using the information contained in the Oracle Rdb
system tables. In addition to the database statements, it also
references the data dictionary path name stored in the database,
as shown in the following example:
$ RMU/EXTRACT/LANG=SQL/ITEM=ALL/OPTION=DIC/OUTPUT=CDD_MODEL.LOG/LOG= -
_$ CDD_EXTRACT.LOG CDD_SQL_DB
Example 8
The following example creates a command procedure containing
a script of partial RMU Verify commands or verify command
partitions for the mf_personnel database. This command procedure
was created with the following RMU Extract command:
$ RMU/EXTRACT/ITEM=VERIFY MF_PERSONNEL
Example 9
The following command displays a query outline definition that
was previously added to the mf_personnel database:
$ RMU/EXTRACT/ITEMS=(OUTLINES) MF_PERSONNEL
Example 10
The following command displays the after-image journal (.aij)
file configuration for mf_personnel:
$ RMU/EXTRACT/ITEMS=(ALTER_DATABASE) MF_PERSONNEL
Example 11
The following command displays the function definitions in mf_
personnel for functions previously created using SQL:
$ RMU/EXTRACT/ITEM=FUNCTION MF_PERSONNEL
Example 12
The following command displays the table and column cardinalities
based on sorted indexes:
$ RMU/EXTRACT/OPTION=COLUMN_VOLUME/ITEM=VOLUME MF_PERSONNEL
Example 13
The following example:
o Executes an SQL EXPORT statement to create an interchange
file.
o Executes an RMU Extract command with the Item=Import
qualifier to generate an Import script. In addition, the
Option=Filename_Only qualifier is specified to prevent full
file specifications from appearing in the SQL IMPORT script.
(If full file specifications are used, you cannot test the
script without replacing the database that was exported.)
o Defines a logical to define the interchange file name used in
the Import script file.
o Executes the Import script file.
SQL> -- Create interchange file, SAVED_PERS.RBR.
SQL> --
SQL> EXPORT DATABASE FILENAME MF_PERSONNEL.RDB INTO SAVED_PERS.RBR;
SQL> EXIT;
$ !
$ RMU/EXTRACT/ITEM=IMPORT/OPTION=FILENAME_ONLY/OUTPUT=IMPORT_PERS.SQL -
_$ MF_PERSONNEL
$ DEFINE/USER RMUEXTRACT_RBR SAVED_PERS.RBR
$ !
$ SQL$
SQL> @IMPORT_PERS.SQL
SQL> set language ENGLISH;
SQL> set default date format 'SQL92';
SQL> set quoting rules 'SQL92';
SQL> set date format DATE 001, TIME 001;
SQL>
SQL> -- RMU/EXTRACT for Oracle Rdb V7.2-00 2-JAN-2006 15:34:38.63
SQL> --
SQL> -- Physical Database Definition
SQL> --
SQL> -----------------------------------------------------------------
SQL> import database from rmuextract_rbr
cont> filename 'MF_PERSONNEL'
.
.
.
Example 14
The following example shows an extract from the generated script
when the SYS$LANGUAGE and LIB$DT_FORMAT symbols are defined.
The language and format will default to ENGLISH and the standard
OpenVMS format if these logical names are not defined.
$ DEFINE LIB$DT_FORMAT LIB$DATE_FORMAT_002,LIB$TIME_FORMAT_001
$ DEFINE SYS$LANGUAGE french
$ RMU/EXTRACT/OUT=SYS$OUTPUT/ITEM=DOMAIN MF_PERSONNEL/OPT=AUDIT_COMMENT
.
.
.
-- Created on 8 janvier 2006 13:01:31.20
-- Never altered
-- Created by RDB_EXECUTE
--
SQL> CREATE DOMAIN ADDRESS_DATA_1
cont> CHAR (25)
cont> comment on domain ADDRESS_DATA_1 is
cont> ' Street name';
.
.
.
Example 15
If a database has snapshots set to ENABLED DEFERRED, it may
be preferable to start a read/write transaction. In this
environment, using the Transaction_type=(Read_only) qualifier
causes a switch to a temporary snapshots ENABLED IMMEDIATE state.
This transition forces the READ ONLY transaction to wait while
all READ WRITE transactions complete, and then all new READ WRITE
transactions performing updates will start writing rows to the
snapshot files for use by possible read only transactions. To
avoid this problem use an RMU Extract command specifying a READ
WRITE ISOLATION LEVEL READ COMMITTED transaction.
$ RMU/EXTRACT/ITEM=TABLE/OUT=TABLES.SQL-
/TRANSACTION_TYPE=(WRITE,ISOLATION=READ)-
SAMPLE.RDB
Example 16
This example specifies the options which were the default
transaction style in prior releases.
$ RMU/EXTRACT/ITEM=TABLE/OUT=TABLES.SQL-
/TRANSACTION_TYPE=(READ_ONLY)-
SAMPLE.RDB
Example 17
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. This could be explicitly
stated by using the following command:
$ RMU/EXTRACT-
/TRANSACTION_TYPE=(WRITE,ISOLATION=READ_COMMITTED)-
SAMPLE.RDB
Using a transaction type of automatic adapts to different
database settings:
$ RMU/EXTRACT-
/TRANSACTION_TYPE=(AUTOMATIC)-
SAMPLE.RDB
Example 18
This example shows the use of the Item=Workload qualifier to
create a DCL command language script.
$ RMU/EXTRACT/ITEM=WORKLOAD -
SCRATCH/LOG/OPTION=(FILENAME,AUDIT)
$! RMU/EXTRACT for Oracle Rdb V7.2-00 7-JAN-2006 22:00:42.72
$!
$! WORKLOAD Procedure
$!
$!---------------------------------------------------------------------
$ SET VERIFY
$ SET NOON
$
$! Created on 7-JAN-2006 10:12:26.36
$! Last collected on 7-JAN-2006 22:00:34.47
$!
$ RMU/INSERT OPTIMIZER_STATISTICS -
SCRATCH -
/TABLE=(CUSTOMERS) -
/COLUMN_GROUP=(CUSTOMER_NAME) -
/DUPLICITY_FACTOR=(4.0000000) -
/NULL_FACTOR=(0.0000000) /LOG
$
$! Created on 7-JAN-2006 10:12:26.36
$! Last collected on 7-JAN-2006 22:00:34.58
$!
$ RMU/INSERT OPTIMIZER_STATISTICS -
SCRATCH -
/TABLE=(RDB$FIELDS) -
/COLUMN_GROUP=(RDB$FIELD_NAME) -
/DUPLICITY_FACTOR=(1.7794118) -
/NULL_FACTOR=(0.0000000) /LOG
$
.
.
.
$ SET NOVERIFY
$ EXIT
Example 19
The following example shows the use of the Match option to select
a subset of the workload entries based on the wildcard file name.
$ RMU/EXTRACT/ITEM=WORKLOAD -
SCRATCH/LOG/OPTION=(FILENAME,AUDIT,MATCH:RDB$FIELDS%)
$! RMU/EXTRACT for Oracle Rdb V7.2-00 8-JAN-2006 10:53
$!
$! WORKLOAD Procedure
$!
$!------------------------------------------------------------------------
$ SET VERIFY
$ SET NOON
$
! Created on 7-JAN-2006 15:18:02.30
$ SET NOON
$
$! Created on 7-JAN-2006 15:18:02.30
$! Last collected on 7-JAN-2006 18:25:04.27
$!
$ RMU/INSERT OPTIMIZER_STATISTICS -
SCRATCH -
/TABLE=(RDB$FIELDS) -
/COLUMN_GROUP=(RDB$FIELD_NAME) -
/DUPLICITY_FACTOR=(1.0000000) -
/NULL_FACTOR=(0.0000000) /LOG
$ SET NOVERIFY
$ EXIT
Example 20
The following example shows the use of Item options Defer_
Constraints, Constraints, and Match to extract a table and its
constraints.
$ RMU/EXTRACT/ITEM=(TABLE,CONSTRAINT)-
_$ /OPTION=(FILENAME_ONLY,NOHEADER,-
_$ DEFER_CONSTRAINT,MATCH:EMPLOYEES%) -
_$ MF_PERSONNEL
set verify;
set language ENGLISH;
set default date format 'SQL92';
set quoting rules 'SQL92';
set date format DATE 001, TIME 001;
attach 'filename MF_PERSONNEL';
create table EMPLOYEES (
EMPLOYEE_ID ID_DOM,
LAST_NAME LAST_NAME_DOM,
FIRST_NAME FIRST_NAME_DOM,
MIDDLE_INITIAL MIDDLE_INITIAL_DOM,
ADDRESS_DATA_1 ADDRESS_DATA_1_DOM,
ADDRESS_DATA_2 ADDRESS_DATA_2_DOM,
CITY CITY_DOM,
STATE STATE_DOM,
POSTAL_CODE POSTAL_CODE_DOM,
SEX SEX_DOM,
BIRTHDAY DATE_DOM,
STATUS_CODE STATUS_CODE_DOM);
comment on table EMPLOYEES is
'personal information about each employee';
alter table EMPLOYEES
add constraint EMP_SEX_VALUES
check(EMPLOYEES.SEX in ('M', 'F', '?'))
deferrable
add constraint EMP_STATUS_CODE_VALUES
check(EMPLOYEES.STATUS_CODE in ('0', '1', '2', 'N'))
deferrable
alter column EMPLOYEE_ID
constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
primary key
deferrable;
commit work;
Example 21
The following example shows the use of the option Group_Table to
extract a table and its indexes:
$ rmu/extract/item=(table,index)-
_$ /option=(group_table,match=employees%,-
_$ filename_only,noheader) db$:mf_personnel
set verify;
set language ENGLISH;
set default date format 'SQL92';
set quoting rules 'SQL92';
set date format DATE 001, TIME 001;
attach 'filename MF_PERSONNEL';
create table EMPLOYEES (
EMPLOYEE_ID ID_DOM
constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
primary key
deferrable,
LAST_NAME LAST_NAME_DOM,
FIRST_NAME FIRST_NAME_DOM,
MIDDLE_INITIAL MIDDLE_INITIAL_DOM,
ADDRESS_DATA_1 ADDRESS_DATA_1_DOM,
ADDRESS_DATA_2 ADDRESS_DATA_2_DOM,
CITY CITY_DOM,
STATE STATE_DOM,
POSTAL_CODE POSTAL_CODE_DOM,
SEX SEX_DOM,
BIRTHDAY DATE_DOM,
STATUS_CODE STATUS_CODE_DOM);
comment on table EMPLOYEES is
'personal information about each employee';
create unique index EMPLOYEES_HASH
on EMPLOYEES (
EMPLOYEE_ID)
type is HASHED SCATTERED
store
using (EMPLOYEE_ID)
in EMPIDS_LOW
with limit of ('00200')
in EMPIDS_MID
with limit of ('00400')
otherwise in EMPIDS_OVER;
create unique index EMP_EMPLOYEE_ID
on EMPLOYEES (
EMPLOYEE_ID
asc)
type is SORTED
node size 430
disable compression;
create index EMP_LAST_NAME
on EMPLOYEES (
LAST_NAME
asc)
type is SORTED;
commit work;
alter table EMPLOYEES
add constraint EMP_SEX_VALUES
check(EMPLOYEES.SEX in ('M', 'F', '?'))
deferrable
add constraint EMP_STATUS_CODE_VALUES
check(EMPLOYEES.STATUS_CODE in ('0', '1', '2', 'N'))
deferrable;
commit work;
Example 22
The following example shows the output when you use the
Item=Revoke_Entry qualifier:
$ RMU/EXTRACT/ITEM=REVOKE_ENTRY ACCOUNTING_DB
...
-- Protection Deletions
--
--------------------------------------------------------------------------------
revoke entry
on database alias RDB$DBHANDLE
from [RDB,JAIN];
revoke entry
on database alias RDB$DBHANDLE
from [RDB,JONES];
revoke entry
on database alias RDB$DBHANDLE
from PUBLIC;
revoke entry
on table ACCOUNT
from [RDB,JONES];
revoke entry
on table ACCOUNT
from PUBLIC;
revoke entry
on table ACCOUNT_BATCH_PROCESSING
from [RDB,JONES];
revoke entry
on table ACCOUNT_BATCH_PROCESSING
from PUBLIC;
revoke entry
on table BILL
from [RDB,JONES];
revoke entry
on table BILL
from PUBLIC;
...
Example 23
The following example shows sample output for the WORK_STATUS
table of MF_PERSONNEL. The uppercase DCL commands are generated
by RMU Extract.
$ RMU/EXTRACT/ITEM=UNLOAD-
_$ /OPTION=(NOHEADER,FULL,MATCH:WORK_STATUS%) sql$database
$ CREATE WORK_STATUS.COLUMNS
! Columns list for table WORK_STATUS
! in DISK1:[DATABASES]MF_PERSONNEL.RDB
! Created by RMU Extract for Oracle Rdb V7.2-00 on 1-JAN-2006 20:50:25.33
STATUS_CODE
STATUS_NAME
STATUS_TYPE
$ RMU/UNLOAD -
DISK1:[DATABASES]MF_PERSONNEL.RDB -
/FIELDS="@WORK_STATUS.COLUMNS" -
WORK_STATUS -
WORK_STATUS.UNL
$
$ EXIT
$ RMU/EXTRACT/ITEM=LOAD-
_$ /OPTION=(NOHEADER,FULL,MATCH:WORK_STATUS%) sql$database
$ RMU/LOAD -
/TRANSACTION_TYPE=EXCLUSIVE -
/FIELDS="@WORK_STATUS.COLUMNS" -
DISK1:[DATABASES]MF_PERSONNEL.RDB -
WORK_STATUS -
WORK_STATUS.UNL
$
$ EXIT
Example 24
The following example shows how to extract all constraints as an
ALTER TABLE statement.
$ rmu/extract/item=(notab,constr) db$:sql_personnel/opt=(nohead,mat=empl%,defer)
set verify;
set language ENGLISH;
set default date format 'SQL92';
set quoting rules 'SQL92';
set date format DATE 001, TIME 001;
attach 'filename $DISK1:[JONES]SQL_PERSONNEL.RDB';
alter table EMPLOYEES
add constraint EMP_SEX_VALUES
check((EMPLOYEES.SEX in ('M', 'F')
or (EMPLOYEES.SEX is null)))
initially deferred deferrable
add constraint EMP_STATUS_CODE_VALUES
check((EMPLOYEES.STATUS_CODE in ('0', '1', '2')
or (EMPLOYEES.STATUS_CODE is null)))
initially deferred deferrable
alter column EMPLOYEE_ID
constraint EMP_EMPLOYEE_ID_NOT_NULL
not null
initially deferred deferrable;