Items[=item-list]
Allows you to extract and display selected definitions. Note that
each of the item names can be combined to provide shorter command
lines such as the following:
$ RMU/EXTRACT/NOLOG/ITEMS=(ALL,NODATABASE) MF_PERSONNEL
If you omit the Items qualifier from the command line or specify
it without any options, the action defaults to Items=All.
The following options can be specified with the Items qualifier:
o All
Indicates that all database items are to be extracted. This
is the default and includes all items except Alter_Database,
Forward_References, Import, Load, Protections, Revoke_Entry,
Security, Synonyms, Unload, Verify, Volume, and Workload
options. You can use either All or Noall in combination with
other items to select specific output.
In the following example, the Items=All option causes all the
definitions except for Triggers to be extracted and displayed:
$ RMU/EXTRACT/ITEMS=(ALL,NOTRIGGERS) MF_PERSONNEL
The following example displays domain and table definitions.
Note that the Noall option could have been omitted:
$ RMU/EXTRACT/ITEMS=(NOALL, DOMAIN, TABLE) MF_PERSONNEL
o Alter_Database (or Change_Database)
Noalter_Database
Displays the physical database after-image journal object
definition.
o Catalog
Nocatalog
Displays all contents of the catalog created for an SQL
multischema database. This item is ignored if the interface
is RDO.
o Collating_Sequences
Nocollating_Sequences
Displays all the collating sequences defined for the database
that you select. Note that Oracle Rdb does not save the name
of the source OpenVMS National Character Set (NCS) library and
the name becomes the defined logical, NCS$LIBRARY, by default.
o Constraints
Noconstraints
By default, table and column constraints are output by the
Items=Table qualifier. If you specify Item=Noconstraints,
constraint information is not extracted for any table. If you
specify the Language=SQL qualifier, the default is to have
Item=Constraints enabled when tables are extracted.
To extract all constraints as an ALTER TABLE statement, use
the Item=Constraint and Option=Defer_Constraints qualifiers.
To force all constraints to be defined after tables are
defined, use the Item=Tables and Option=Defer_Constraints
qualifiers.
o Database
Nodatabase
Displays the database attributes and characteristics. This
includes information such as the database root file name, the
number of buffers, the number of users, the repository path
name, and the characteristics for each storage area.
If you specify RMU Extract with the Option=Nodictionary_
References qualifier, the data dictionary path name is
ignored.
o Domains (or Fields)
Nodomains
Displays the domain definitions. If the domain was originally
defined using the data dictionary path name, the output
definition shows this. If the Option=Nodictionary_References
qualifier is specified, the data dictionary path name is
ignored and the column attributes are extracted from the
system tables.
o Forward_References
Noforward_References
Queries the dependency information in the database
(RDB$INTERRELATIONS) and extracts DECLARE FUNCTION and
DECLARE PROCEDURE statements for only those routines that
are referenced by other database objects. The default is
Noforward_Reference.
The Forward_References item is used in conjunction with other
Item keywords, for example, /Item=(All,Forward).
o Functions
Nofunctions
Displays external function definitions.
o Import
Noimport
Generates an RDO or SQL IMPORT script that defines every
storage area and row cache. The Language qualifier determines
whether Oracle RMU generates an RDO or SQL IMPORT script
(If you specify the Language=SQL or the Language=ANSI_SQL
qualifier, the same SQL IMPORT script is generated.) Because
the RDO interface does not accept many of the database options
added to recent versions of Oracle Rdb, Oracle Corporation
recommends that you specify the Language=SQL qualifier (or
accept the default).
The Items=Import qualifier is useful when you want to re-
create a database that is the same or similar to an existing
database. Editing the file generated by Oracle RMU to change
allocation parameters or add storage areas and so on is easier
than writing your own IMPORT script from scratch.
When Oracle RMU generates the IMPORT script, it uses an
interchange file name of rmuextract_rbr in the script.
Therefore, you must either edit the IMPORT script generated
by Oracle RMU to specify the interchange file that you want
to import, or assign the logical name RMUEXTRACT_RBR to your
interchange file name. (An interchange file is created by an
SQL or RDO EXPORT statement.) See Example 14 in the Examples
help entry under this command.
o Indexes (or Indices)
Noindexes
Displays index definitions, including storage map information.
o Load
Unload
Generates a DCL command procedure containing an RMU Load or
RMU Unload command for each table in the database. This item
must be specified explicitly, and is not included by default
when you use the Items=All qualifier.
Oracle RMU generates the Fields qualifier for the Load and
Unload scripts when you specify the Option=Full qualifier. If
you do not specify the Option=Full qualifier, the scripts are
generated without the Fields qualifier.
If you specify the RMU Extract command with the Item=Unload
qualifier, DCL commands are added to the script to create a
file with type .COLUMNS. This file defines all the unloaded
columns. The file name of the .COLUMNS file is derived from
the name of the extracted table. You can reference the file by
using the "@" operator within the Fields qualifer for the RMU
Load and RMU Unload commands.
Virtual columns, AUTOMATIC or COMPUTED BY table columns,
and VIEW calculated columns appear in the .COLUMNS file as
comments.
o Module
Nomodule
Displays procedure and function definitions. This item is
valid only when the Language specification is SQL; it is
ignored if the Language specification is RDO or ANSI_SQL.
o Outlines
Nooutlines
Displays query outline definitions. This item is valid only
when the Language specification is SQL; it is ignored if the
Language specification is RDO or ANSI_SQL.
o Procedures
Noprocedures
Extracts external procedures.
o Profiles
Noprofiles
Displays profiles as defined by the CREATE PROFILE statement.
o Protections
Noprotections
Displays the protection access control list (ACL) definitions.
If the protections are defined using SQL ANSI semantics, they
cannot be represented in RDO. In this case, the diagnostic
message warns you that the protections must be extracted using
the Language=SQL qualifier. If you specify Language=ANSI_SQL,
a diagnostic message warns you that the ACL-style protections
cannot be extracted in ANSI format. You must explicitly
specify the Protections option. It is not included by default
when you use the Items=All qualifier.
o Revoke_Entry
Norevoke_Entry
Extracts a SQL or RDO script that deletes the protections from
all access control lists in the database: database, table,
sequences, column, module, function, and procedure.
The output script contains a series of SQL REVOKE ENTRY
statements (or DELETE PROTECTION statements if the language
selected is RDO) that remove the access control entry for the
user and all objects.
o Role
Norole
Displays role definitions as defined by the SQL CREATE ROLE
statement. In addition, any roles that have been granted
are displayed as a GRANT statement. By default, roles are
not extracted, nor are they included when you specify the
Items=All qualifier.
o Schema
Noschema
Displays the schema definitions for an SQL multischema
database. This option is ignored if the interface is RDO.
o Sequence
Nosequence
Displays the sequence definitions in the database that were
originally defined with the SQL CREATE SEQUENCE statement.
o Security
Nosecurity
Displays RMU Audit commands based on information in the
database. This item must be specified explicitly, and is not
included by default when you use the Items=All qualifier.
o Storage_Maps
Nostorage_Maps
Displays storage map definitions, including the list
(segmented string) storage map.
o Synonyms
Nosynonyms
Generates a report of all the synonyms defined for the
database. All synonyms of a database object, including
synonyms of those synonyms, are grouped together. The output
is ordered by creation as recorded by the RDB$CREATED column.
This report is useful for viewing all synonyms or moving them
to other databases. However, since synonyms refer to many
different database objects, a single set of definitions is
usually not adequate when defining a new database. Oracle
Corporation recommends that you use the Option=Synonym
qualifier in most cases.
o Tables (or Relations)
Notables
Displays table definitions in the same order in which they
were created in the database.
If the table was originally defined using the data dictionary
path name, that path name is used for the definition.
If you specify the Option=Nodictionary_References qualifier,
the data dictionary path name is ignored and the table
attributes are extracted from the system tables.
If Item=Noconstraints is specified, constraint information is
not extracted for any table.
The Items=Tables qualifier handles domains in the following
ways:
- The output for this item reflects the original definitions.
If a column is based on a domain of a different name, the
BASED ON clause is used in RDO, and the domain name is
referenced by SQL.
- Any columns that are based on fields in a system table are
processed but generate warning messages.
- Certain domains created using RDO in a relation definition
cannot be extracted for RDO because it is not possible to
distinguish columns defined using a shorthand method as
shown in the example that follows. In this case, the column
FIELD_1 becomes or is defined as a domain.
DEFINE RELATION REL1.
FIELD_1 DATATYPE IS TEXT SIZE 10.
END.
However, this type of definition in SQL causes special
domains to be created with names starting with SQL$. In
this case, the SQL domain is translated into the following
data type:
CREATE TABLE TAB1
(COLUMN_1 CHAR(10));
The output for this item also includes the table-level
constraints that can be applied: PRIMARY KEY, FOREIGN KEY, NOT
NULL, UNIQUE, and CHECK. In the case of the CHECK constraint,
the expression might not be translated to or from RDO and SQL
due to interface differences.
o Triggers
Notriggers
Displays trigger definitions.
o User
Nouser
Displays user definitions as defined by the SQL CREATE USER
statement. In addition, if you also specify Role with the
Item qualifier, then any roles that have been granted to a
user are displayed as GRANT statements. By default, Users are
not displayed, nor are they displayed when you specify the
Items=All qualifier.
o Verify
Noverify
Causes the generation of an optimal DCL command procedure
containing multiple RMU Verify commands. Using this command
procedure is equivalent to performing a full verification
(RMU Verify with the All qualifier) for the database. This
command procedure can be broken down further into partial
command scripts to perform partial verify operations. These
partial command scripts can then be submitted to different
batch queues to do a full verify operation in parallel, or
they can be used to spread out a full verify operation over
several days by verifying a piece of the database at a time.
A partitioning algorithm is a procedure to determine what
portions of the database should be verified in the same
command script. For example, areas with interrelations
should be verified with the same partial command script. A
partitioning algorithm considers the following when creating a
partial command script from the equivalent RMU Verify command
with the All qualifier:
1. Each storage area is assigned to a partition.
2. For each table in the database, if the table is not
partitioned, the table is put in the partial command script
corresponding to that storage area; otherwise, if the table
is partitioned across several storage areas, the partitions
corresponding to all of the storage areas are merged into
one partial command script and the table is added to this
partial command script.
3. For each index in the database, the process shown in step 2
is followed.
4. For an index on a table, the index and table are merged
into one partial command script.
The scripts of partial RMU Verify commands are written in
the form of a command procedure. Each partial command script
is preceded by a label of the form STREAM_n: where n is an
integer greater than 1. For example, to execute the command
at label STREAM_3:, invoke the command procedure by using the
following syntax:
$ @<command-procedure-name> STREAM_3
The resultant command procedure is set up to accept up to four
parameters, P1, P2, P3, and P4, as shown in Parameters for
Generated Command File.
Table 9 Parameters for Generated Command File
Parameter Option Description
P1 Stream_n Specifies the command stream to be
executed. The variable n is the "number"
of the RMU Verify command stream to
be executed. If omitted, all command
streams are executed.
P2 [No]Log Specifies whether to use the Log
qualifier in the RMU Verify command
line. If omitted, the DCL verify switch
value is used.
P3 Read_Only | Provides the RMU Verify
Protected | Transaction_Type value. If omitted,
Exclusive Transaction_Type = Protected is used.
P4 Specifies the name of the output file
for the RMU Verify Output qualifier. If
omitted, Output = SYS$OUTPUT is used.
o Views
Noviews
Displays view definitions. If the database was defined using
SQL, it is possible that the view cannot be represented
in RDO. In this case, the diagnostic message warns that
the view definition is being ignored, and the user should
use LANGUAGE=SQL to extract the view. Note the following
transformations the RMU Extract command makes when it cannot
precisely replicate the SQL source code:
- The RMU Extract command cannot precisely replicate derived
table column names or correlation names for any select
expression.
The RMU Extract command generates new names for correlation
names (C followed by a number) and derived table column
names (F followed by a number).
For example, suppose you create a view, as follows:
SQL> ATTACH 'FILENAME mf_personnel';
SQL> CREATE VIEW DERIVED_1
cont> (F1) AS
cont> SELECT CAST(AVG(JOB_COUNT) AS INTEGER(2))
cont> FROM (SELECT EMPLOYEE_ID, COUNT (*)
cont> FROM JOB_HISTORY
cont> GROUP BY EMPLOYEE_ID) AS EMP_JOBS (EMPLOYEE_ID, JOB_COUNT);
SQL> COMMIT;
If you issue the following RMU Extract command, you receive
the output shown:
$ rmu/extract/item=view/option=(match:DERIVED_1%,noheader,filename_only) -
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 view DERIVED_1
(F1) as
(select
CAST(avg(C2.F2) AS INTEGER(2))
from
(select C4.EMPLOYEE_ID, count(*)
from JOB_HISTORY C4
group by C4.EMPLOYEE_ID)
as C2 (F1, F2));
commit work;
- The RMU Extract command cannot generate the original SQL
source code for the user-supplied names of AS clauses. This
is particularly apparent when the renamed select expression
is referenced in an ORDER BY clause. In such a case, the
RMU Extract command generates correlation names in the form
RMU$EXT_n where n is a number.
For example, suppose you create a view, as follows:
SQL> SET QUOTING RULES 'SQL92';
SQL> CREATE DATA FILE xyz;
SQL> CREATE TABLE DOCUMENT
cont> (REPORT CHAR(10));
SQL> CREATE TABLE REPORTING
cont> (NAME CHAR(5));
SQL> CREATE TABLE "TABLES"
cont> (CODTAB CHAR(5));
SQL> CREATE VIEW VIEW_TEST
cont> (CREDIT,
cont> CODTAB,
cont> CODMON) AS
cont> SELECT
cont> C1.NAME,
cont> C2.CODTAB,
cont> (SELECT C7.REPORT FROM DOCUMENT C7) AS COM
cont> FROM REPORTING C1, "TABLES" C2
cont> ORDER BY C1.NAME ASC, C2.CODTAB ASC, COM ASC;
SQL>
If you issue the following RMU Extract command, you receive
the output shown:
$ RMU/EXTRACT/ITEM=VIEW MF_PERSONNEL.RDB
.
.
.
create view VIEW_TEST
(CREDIT,
CODTAB,
CODMON) as
select
C1.NAME,
C2.CODTAB,
(select DOCUMENT.REPORT from DOCUMENT) AS RMU$EXT_1
from REPORTING C1, "TABLES" C2
order by C1."NAME" asc, C2.CODTAB asc, RMU$EXT_1 asc;
o Volume
Novolume
Displays cardinality information in a PDL-formatted file for
use by Oracle Expert for Rdb. This item must be specified
explicitly, and is not included by default when the Items=All
qualifier is used.
o Workload
Noworkload
Generates a DCL command language script. The script is used
with the RMU Insert Optimizer_Statistics command to extract
the work load and statistics stored in the RDB$WORKLOAD table.
The unloaded information can be applied after a new database
is created using the SQL EXPORT and IMPORT statements, or
it can be applied to a similar database for use by the RMU
Collect Optimizer_Statistics/Statistic=Workload command.
This item must be specified explicitly, and is not included by
default when the Items=All qualifier is used. The default is
Noworkload.
You can modify the output of the Item=Workload qualifier by
specifying the following keywords with the Option qualifier:
o Audit_Comment
Each RMU Insert Optimizer_Statistics statement is preceded
by the created and altered date for the workload entry. The
default is Noaudit_comment.
o Filename_Only
The database file specification output for the RMU Insert
Optimizer_Statistics statement is abbreviated to just the
filename.
o Match
A subset of the workload entries based on the wildcard file
name is selected.