Reads and decodes Oracle Rdb metadata and reconstructs equivalent
statements in Relational Database Operator (RDO) or SQL
(structured query language) code for the definition of that
database. These statements can either be displayed or extracted.
You can use these statements to create your database again if you
no longer have the RDO or SQL code that defined your database.
In addition, you can direct the RMU Extract command to produce
output for the following:
o An SQL or RDO IMPORT script (Items=Import)
o An RMU Unload command for each table (Items=Unload)
o An RMU Load command for each table (Items=Load)
o An RMU Set Audit command for the database (Items=Security)
o An RMU Verify command for the database (Items=Verify)
1 – Description
The RMU Extract command decodes information and reconstructs
equivalent commands in the language you select with the Language
qualifier for the definition of that database.
You can extract the definitions to either a file or to
SYS$OUTPUT.
The RMU Extract command extracts the following character set
information:
o For databases:
- The database default character set
- The national character set
o For domains:
- The character set of each character data type domain
- The length in characters of each character data type
domain
o For tables:
- The character set of each character data type column
- The length in characters of each character data type
column
The RMU Extract command may enclose object names in double
quotation marks to preserve uppercase and lowercase characters,
special character combinations, or reserved keywords.
2 – Format
(B)0[mRMU/Extract root-file-spec
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Defaults[=defaults-list] x /Defaults=(quoting_rules=SQL92)
/Items[=item-list] x /Items=All
/Language=lang-name x /Language=SQL
/[No]Log[=log-file] x /Nolog
/Options=options-list x /Option=Normal
/[No]Output[=out-file] x /Output=SYS$OUTPUT
/Transaction_Type[= x See Description
(transation_mode,options...]) x
3 – Parameters
3.1 – root-file-spec
The file specification for the database root file from which you
want to extract definitions. Note that you do not need to specify
the file extension. If the database root file is not found, the
command exits with a "file not found" error.
4 – Command Qualifiers
4.1 – Defaults
Defaults[=defaults-list]
This qualifier is used to change the output of the RMU Extract
command. The following defaults can be modified with the Defaults
qualifier:
o Allocation=integer
Noallocation
When you create a test database using the script generated
by the RMU Extract command, the allocation from the source
database may not be appropriate. You can use the Allocation
keyword to specify an alternate value to be used by all
storage areas, or you can use the Noallocation keyword to
omit the clause from the CREATE STORAGE MAP syntax. The
default behavior, when neither keyword is used, is to use
the allocation recorded in the database for each storage area.
See also the Snapshot_Allocation keyword.
o Date_Format
Nodate_Format
By default, the RMU Extract process assumes that DATE types
are SQL standard-compliant (that is DATE ANSI) and that the
built-in function CURRENT_TIMESTAMP returns TIMESTAMP(2)
values. If your environment uses DATE VMS exclusively, then
you can modify the default by specifying the default DATE_
FORMAT=VMS. The legal values are described in the Oracle Rdb
SQL Reference Manual in the SET DEFAULT DATE FORMAT section.
The default is Date_Format=SQL92.
Use Nodate_Format to omit the setting of this session
attribute from the script.
o Dialect
Nodialect
For some extracted SQL scripts the language dialect must
be specified. You can use the Dialect keyword to supply a
specified dialect for the script. You can find the legal
values for this option in the Oracle Rdb SQL Reference Manual
in the SET DIALECT section. The default is Nodialect.
o Language
Nolanguage
The RMU Extract commmand uses the process language, that is,
the translated value of SYS$LANGUAGE, or ENGLISH, for the
SET LANGUAGE command. However, if the script is used on a
different system then this language might not be appropriate.
You can use the Language keyword to supply a specified
language for the script. Legal language names are defined by
the OpenVMS system logical name table; examine the logical
name SYS$LANGUAGES for a current set. Use the Nolanguage
keyword to omit this command from the script.
o Quoting_Rules
Noquoting_Rules
You can use the Quoting_Rules keyword to supply a specified
setting for the script. You can find the legal values for
this option in the Oracle Rdb SQL Reference Manual in the SET
QUOTING RULES section. The default is Quoting_Rules=SQL92.
The RMU Extract command assumes that SQL keywords and names
containing non-ASCII character set values are enclosed in
quotation marks.
o Snapshot_Allocation=integer
Nosnapshot_Allocation
When you create a test database from the RMU Extract output,
the snapshot file allocation from the source database may not
be appropriate. You can use the Snapshot_Allocation keyword to
specify an alternate value to be used by all snapshot areas,
or you can use the Noallocation keyword to omit the "snapshot
allocation is" clause. The default behavior, when neither
keyword is used, is to use the snapshot allocation stored in
the database for each snapshot area. See also the Allocation
keyword.
4.2 – Items
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.
4.3 – Language
Language=lang-name
Allows you to select one of the following interfaces:
o SQL
When you specify the Language=SQL qualifier, Oracle RMU
generates the Oracle Rdb SQL dialect. The Oracle Rdb SQL
dialect is a superset of SQL92 Entry level, with language
elements from Intermediate and Full SQL92 levels. It also
contains language elements from SQL:1999 and extensions
specific to Oracle Rdb.
o ANSI_SQL
When you specify the Language=ANSI_SQL qualifier and specify
the Option=Normal qualifier, Oracle RMU tries to generate
ANSI SQL statements that conform to the ANSI X3.135-1989 SQL
standard.
When you specify the Language=ANSI_SQL qualifier and the
Option=Full qualifier, Oracle RMU tries to generate SQL
statements that conform to the current ANSI and ISO SQL
database language standards. Refer to the Oracle Rdb SQL
Reference Manual for more information.
Regardless of the Option parameter you specify, any Oracle
Rdb specific features (such as DATATRIEVE support clauses and
storage maps) are omitted.
o RDO
When you specify the RDO language option, Oracle RMU generates
RDO statements.
The default is Language=SQL.
The Language qualifier has no effect on the output generated by
the Items=Load, Items=Unload, and Items=Verify qualifiers. This
is because these qualifiers generate scripts that contain Oracle
RMU commands only.
4.4 – Log
Log[=log-file]
Nolog
Enable or disables log output during execution of the RMU Extract
command. The log includes the current version number of Oracle
Rdb, and the values of the parameter and qualifiers. The default
is Nolog. The default file extension is .log. If you specify Log
without specifying a file name, output is sent to SYS$OUTPUT.
4.5 – Options
Options=options-list
This qualifier is used to change the output of the RMU Extract
command. This qualifier is not applied to output created by the
Items=Unload, Items=Load, Items=Security, or the Items=Verify
qualifier.
The following options can be specified with the Options
qualifier:
o Audit_Comment
Noaudit_Comment
Annotates the extracted objects with the creation and last
altered timestamps as well as the username of the creator. The
date and time values are displayed using the current settings
of SYS$LANGUAGE and LIB$DT_FORMAT. Noaudit_Comment is the
default.
o Cdd_Constraints
Nocdd_Constraints
Specifies that tables extracted by pathname include all
constraints. The Option=Nocdd_Constraints qualifier is
equivalent to the Option=Defer_Constraints qualifier
for tables with a pathname. This option is ignored if
Item=Noconstraints is specified.
When you specify the Cdd_Constraints option and the
Dictionary_References option, the RMU Extract command does
not generate ALTER TABLE statements to add constraints,
but instead assumes they will be inherited from the data
dictionary.
When you use the Nocdd_Constraints option and the Dictionary_
References option, the RMU Extract command generates ALTER
TABLE statements to add FOREIGN KEY and CHECK constraints
after all base tables have been created.
o Cdd_References
Nocdd_References
This option is an alias for Dictionary_References.
o Column_Volume
Nocolumn_Volume
Directs the RMU Extract command to output the table, column,
and column segmented string cardinalities based on sorted
indexes. Note that this qualifier must be used in combination
with the Items=Volume qualifier. If the Items=Volume qualifier
is omitted, cardinalities are not displayed.
RMU Extract generates data of the following type:
Volume for schema MF_PERSONNEL
Default volatility is 5;
Table WORK_STATUS all is 3;
Table EMPLOYEES all is 100;
Column EMPLOYEE_ID all is 100;
Column LAST_NAME all is 83;
.
.
.
Table RESUMES all is 3;
List RESUME
Cardinality IS 3
Number of segments is 3
Average length of segments is 24;
o Debug
Nodebug
Dumps the internal representation for SQL clauses such as
AUTOMATIC AS, VALID IF, COMPUTED BY, MISSING_VALUE, DEFAULT_
VALUE, CONSTRAINTS, SQL DEFAULT, TRIGGERS, VIEWS, and STORAGE
MAPS during processing. The keyword Debug cannot be specified
with the keywords Normal or Full in the same Options qualifier
list.
o Defer_Constraints
Nodefer_Constraints
Forces all constraints to be defined (using an ALTER TABLE
statement) after all tables have been extracted. This option
is ignored if Item=Noconstraints is specified.
If Option=Nodefer_Constraints is specified, all constraints
are generated with the CREATE TABLE statement. If neither
Option=Defer_Constraints nor Option=Nodefer_Constraints is
specified, the default behavior is to generate NOT NULL,
UNIQUE, and PRIMARY KEY constraints with the CREATE TABLE
statement, and generate CHECK and FOREIGN KEY constraints in a
subsequent ALTER TABLE statement.
o Dictionary_References
Nodictionary_References
Directs the RMU Extract command to output 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, this option also displays the data
dictionary path name stored in the database. Refer to Example
8 in the Examples help entry under this command for an example
of using this option.
If neither the Option=Dictionary_References qualifier nor the
Option=Nodictionary_References qualifier is specified, then
Oracle RMU examines the RDB$RELATIONS and RDB$FIELDS system
tables to determine whether or not any domains or tables refer
to the data dictionary. If references are made to the data
dictionary, then the Option=Dictionary_References qualifier is
the default. Otherwise, it is assumed that the data dictionary
is not used, and the default is the Option=Nodictionary_
References qualifier.
The Nodictionary_References keyword causes all references to
the data dictionary to be omitted from the output. This is
desirable if the database definition is to be used on a system
without the data dictionary or in a testing environment.
If the Items=Database and Option=Nodictionary_References
qualifiers are selected, the data dictionary path name stored
in the system table is ignored. For SQL, the NO PATHNAME
clause is generated, and for RDO, the clause DICTIONARY IS
NOT USED is generated.
If the Items qualifier specifies Domain or Table, and the
Option qualifier specifies Nodictionary_References, the
output definition includes all attributes stored in the system
tables.
o Disable_Objects
Nodisable_Objects
Requests that all disabled objects be written to the RMU
Extract output file as disabled (see the description for the
Omit_Disabled option). Disable_Objects is the default.
The Nodisable_Objects option displays the objects but omits
the disabling syntax.
o Domains
Nodomains
The Nodomains option is used to eliminate the domain name
from within metadata objects. The domain name is replaced
by the underlying data type. This option is designed for use
with tools that do not recognize this SQL:1999 SQL language
feature.
Effect on /Language=SQL output:
The default is Option=Domains.
A SQL script generated when Option=Nodomains was specified
does not include the domain name in the CREATE TABLE column
definition, CREATE FUNCTION or CREATE PROCEDURE parameter
definitions, or any value expression which uses the CAST
function to convert an expression to a domain data type
(such as the CREATE VIEW and CREATE TRIGGER statements).
The output generated by the RMU Extract command for
functions and procedures in the CREATE MODULE statement
is not affected by the Option=Nodomains option because it
is based on the original source SQL for the routine body
which is not edited by the RMU Extract command.
Effect on /Language=ANSI_SQL output:
The default is Option=Nodomains when the Option=Normal
qualifier is specified or is the default. The RMU Extract
command does not generate a list of domain definitions even
if the Items=Domains or Items=All qualifier is used. If
you want the generated script to include a list of domain
definitions, use the Options=Domains qualifier:
$RMU/EXTRACT/LANGUAGE=ANSI_SQL/OPTION=DOMAINS databasename
Use the Option=Full qualifier to have the use of domains
included in the syntax generated for SQL:1999.
o Filename_Only
Nofilename_Only
Causes all file specifications extracted from the database to
be truncated to only the file name. The use of this qualifier
allows for easier relocation of the new database when you
execute the created procedure.
o Full
Nofull
Specifies that if metadata that cannot be translated from the
language that defined the database to the equivalent construct
in the language specified with the Language qualifier (for
example, DEFAULT for SQL and the language selected was
RDO) then the metadata is displayed in comments, or Oracle
RMU attempts to create a translation that most closely
approximates the original construct.
Nofull is identical to the Normal option.
o Group_Table
Nogroup_Table
Specifies that indexes and storage maps are to be extracted
and grouped by table. The table is extracted first, than any
PLACEMENT VIA index, then any storage map, and finally all
other indexes.
When the Group_Table qualifier is combined with the
Option=Match qualifier, you can select a table and its related
storage map and indexes.
The default behavior is Nogroup_Table, which means that items
are extracted and grouped by type.
o Header
Noheader
Specifies that the script header and section headers are
included in the extract. This is the default. Because the
header has an included date, specifying Noheader to suppress
the header may allow easier comparison with other database
extractions when you use the OpenVMS DIFFERENCES command.
o Limit_Volume=nn
Nolimit_Volume
Specifies the maximum amount of data to be scanned for
segmented fields. The RMU Extract command stops scanning when
the limit nn is reached. The number of segments and average
length of segments are calculated from the data that was
scanned. Limit_Volume=1000 is the default.
Nolimit_Volume specifies that a full scan for segmented
strings should be done.
o Match:match-string
The Match option allows selection of wildcard object names
from the database. The match string can contain the standard
SQL wildcard characters: the percent sign (%) to match any
number of characters, and the underscore (_) to match a single
character. In addition, the backslash (\) can be used to
prefix these wildcards to prevent them from being used in
matching. If you are matching a literal backslash, use the
backslash twice, as shown in the following example:
Option=Match:"A1\\A2%"
The match string defaults to the percent sign (%) so that all
objects are selected. To select those objects that start with
JOB, use the qualifier Option=Match:"JOB%".
From the mf_personnel database, this command displays the
definitions for the domains JOB_CODE_DOM and JOB_TITLE_DOM,
the tables JOBS and JOB_HISTORY, the index JOB_HISTORY_HASH,
and the storage maps JOBS_MAP and JOB_HISTORY_MAP.
The match string can be quoted as shown if the string contains
spaces or other punctuation characters used by DCL or other
command language interfaces. Most object names are space
filled; therefore, follow the match string with the percent
sign (%) to match all trailing spaces.
The Match option can be used in conjunction with the Item
qualifier to extract specific tables, indexes, and so on,
based on their name and type.
If Group_Table is specified, the match name is assumed
to match a table name; all indexes for that table will be
extracted when the Items=Index qualifier is specified.
o Multischema
Nomultischema
Displays the SQL multischema names of database objects. It is
ignored by the Relational Database Operator (RDO).
The Nomultischema option displays only the SQL single-schema
names of database objects.
o Normal
Nonormal
Includes only the specific source language code used to define
the database. This is the default.
In addition, this option propagates RDO VALID IF clauses as
column CHECK constraints with the attribute NOT DEFERRABLE
when the Language specification is SQL or ANSI_SQL. When an
RDO VALID IF clause is converted, Oracle RMU generates error
messages similar to the following in your log file:
%RMU-W-UNSVALIDIF, VALID IF clause not supported in SQL - ignored
for DEGREE.
%RMU-I-COLVALIDIF, changed VALID IF clause on domain DEGREE to
column check constraint for DEGREES.DEGREE
The first message is a warning that the VALID IF clause could
not be added to the domain definition because the VALID IF
clause is not supported by SQL. The second message is an
informational message that tells you the VALID IF clause was
changed to a column check constraint.
o Omit_Disabled
Noomit_Disabled
Causes all disabled objects to be omitted from the output
of the RMU Extract command. This includes indexes that have
MAINTENANCE IS DISABLED, USERS with ACCOUNT LOCK, and disabled
triggers and constraints.
The Noomit_Disabled option causes all disabled objects to be
included in the output from the RMU Extract command. Noomit_
Disabled is the default.
o Order_By_Name
Noorder_By_Name
Order_by_Name displays the storage area, cache, and journal
names for the items Database, Alter_Database (also known as
Change_Database), and Import in alphabetic order by the ASCII
collating sequence.
Noorder_By_Name displays the storage area, cache, and journal
names for the items Database, Alter_Database, and Import
in approximate definition order. The default ordering is
approximate because a DROP STORAGE AREA, DROP CACHE, or
DROP JOURNAL statement frees a slot that can be reused, thus
changing the order. Noorder_By_Name is the default.
You can use the logical name RDMS$BIND_SORT_WORKFILES to
allocate work files, if needed.
NOTE
If the identifier character set for the database is not
MCS or ASCII, then this option is ignored. Characters
from other character sets do not sort appropriately under
the ASCII collating sequence.
o Synonyms
Nosynonyms
Causes the synonyms to be extracted immediately after the
referenced object, as shown in the following excerpt from an
output file created using the Item=Table qualifier:
create table HISTORICAL_JOB_INFORMATION (
EMPLOYEE_ID
INTEGER,
USER_ID
CHAR (15),
JOB_TITLE TITLE,
START_DATE
DATE,
CURRENT_SALARY MONEY_IN_DOLLARS
default NULL);
create synonym JOBHIST
for table HISTORICAL_JOB_INFORMATION;
Because synonyms can be referenced from almost any database
object, if you keep the definitions close to the target object
you can eliminate occurrences of undefined symbols during
script execution. The default is Option=Synonyms.
Use the Option=Nosynonyms qualifier to disable the display
of CREATE SYNONYM statements. The synonyms referenced in
database objects such as module, procedure, trigger, and table
definitions are still extracted.
o Volume_Scan
Novolume_scan
Directs the RMU Extract command to perform queries to
calculate the cardinality of each table, if both the
Items=Volume and Options=Volume_Scan qualifiers are specified.
The default is Options=Novolume_Scan, in which case the
approximate cardinalities are read from the RDB$RELATIONS
system table. The Options=Volume_Scan option is ignored if the
Items=Volume qualifier is not selected.
o Width=n
Specifies the width of the output files. You can select values
from 60 to 512 characters. The default of 80 characters is
appropriate for most applications.
4.6 – Output
Output=[out-file]
Nooutput
Names the file to which the RMU Extract command writes the data
definition language (DDL) statements. The file extension defaults
to .rdo, if you specify the Language=RDO qualifier; .sql, if
you specify either the Language=SQL or the Language=ANSI_SQL
qualifier. If you specify the Volume option only, the output file
type defaults to .pdl. If you specify Load, Security, Verify, or
Unload only, the output file type defaults to .com. The default
is SYS$OUTPUT. If you disable the output by using the Nooutput
qualifier, command scripts are not written to an output file. The
Log output can be used to determine which features used by the
database cannot be converted to SQL.
Using Qualifiers to Determine Output Selection shows the
effects of the various combinations of the Language and Options
qualifiers.
Table 10 Using Qualifiers to Determine Output Selection
Language Option Effect on Output
RDO Normal Generates RDO syntax.
Full Generates RDO syntax.
Dictionary_ Outputs path name references to the
References repository.
Nodictionary_ Converts path name references to
References the repository to RDO syntax.
Multischema Ignored by RDO.
SQL Normal Generates SQL syntax.
Full Tries to convert RDO specific
features to SQL (for example, the
VALID IF clause).
Dictionary_ Outputs path name references to the
References data dictionary.
Nodictionary_ Converts path name references to
References the data dictionary to SQL syntax.
Multischema Selects SQL multischema naming of
objects.
ANSI_ Normal Generates ANSI/ISO syntax.
SQL
Full Generates ANSI/ISO SQL92 syntax
supported by SQL.
Dictionary_ Ignored for ANSI_SQL.
References
Nodictionary_ Converts path name references to
References the data dictionary to SQL syntax.
This is the default for ANSI_SQL.
Multischema Selects SQL multischema naming of
objects.
Any Audit_Comment Adds a comment before each
definition.
Debug Annotates output where possible.
Domains Replaces domain names for CAST
expression, column and parameter
definitions, and returns clauses
with SQL data type.
Filename_Only Truncates all file specifications
extracted from the database to only
the file name.
Volume_Scan Forces a true count of Tables. Only
valid for Items=Volume.
4.7 – Transaction Type
Transaction_Type[=(transaction_mode,options,...)]
Allows you to specify the transaction mode, isolation level, and
wait behavior for transactions.
Use one of the following keywords to control the transaction
mode:
o Automatic
When Transaction_Type=Automatic is specified, the transaction
type depends on the current database settings for snapshots
(enabled, deferred, or disabled), transaction modes available
to the process, and the standby status of the database.
Automatic mode is the default.
o Read_Only
Starts a READ ONLY transaction.
o Write
Starts a READ WRITE transaction.
Use one of the following options with the keyword Isolation_
Level=[level] to specify the transaction isolation level:
o Read_Committed
o Repeatable_Read
o Serializable. Serializable is the default setting.
Refer to the SET TRANSACTION statement in the Oracle Rdb SQL
Reference Manual for a complete description of the transaction
isolation levels.
Specify the wait setting by using one of the following keywords:
o Wait
Waits indefinitely for a locked resource to become available.
Wait is the default behavior.
o Wait=n
The value you supply for n is the transaction lock timeout
interval. When you supply this value, Oracle Rdb waits n
seconds before aborting the wait and the RMU Extract session.
Specifying a wait timeout interval of zero is equivalent to
specifying Nowait.
o Nowait
Will not wait for a locked resource to become available.
5 – Usage Notes
o To use the RMU Extract command for a database, you must have
the RMU$UNLOAD privilege in the root file access control
list (ACL) for the database or the OpenVMS SYSPRV or BYPASS
privilege.
o For tutorial information on using output from the RMU Extract
command to load or unload a database, refer to the Oracle Rdb
Guide to Database Design and Definition.
o Included in the output from the RMU Extract command is the
SQL SET DEFAULT DATE FORMAT statement. This SQL statement
determines whether columns with the DATE data type or CURRENT_
TIMESTAMP built-in function are interpreted as OpenVMS or
SQL92 format. The RMU Extract command always sets the default
to SQL92. The SQL92 format DATE and CURRENT_TIMESTAMP contain
only the YEAR to DAY fields. The OpenVMS format DATE and
CURRENT_TIMESTAMP contain YEAR to SECOND fields.
If your database was defined with OpenVMS format DATE and
CURRENT_TIMESTAMP, the default SQL SET DEFAULT DATE FORMAT
'SQL92' in the RMU Extract output causes errors to be returned
when you attempt to execute that output. For example, when you
define a trigger:
SQL> CREATE TRIGGER SALARY_HISTORY_CASCADE_UPDATE
cont> AFTER UPDATE OF JOB_CODE ON JOB_HISTORY
cont> (UPDATE SALARY_HISTORY SH
cont> SET SALARY_START = CURRENT_TIMESTAMP
cont> WHERE (SH.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID)
cont> ) for each row;
%SQL-F-UNSDATASS, Unsupported date/time assignment from <Source>
to SALARY_START
You can avoid these errors by editing the output from the RMU
Extract command. Replace the SET DEFAULT DATE FORMAT 'SQL92'
statement with SET DEFAULT DATE FORMAT 'VMS'. If the problem
occurs in trigger definitions, you can use the CAST function
instead. Specify CAST(CURRENT_TIMESTAMP AS DATE VMS) with each
trigger definition that references CURRENT_TIMESTAMP. (You
cannot use the CAST function within the DEFAULT clause of an
SQL CREATE statement).
o The following list contains a description of what the RMU
Extract command generates when it encounters certain RDO
statements:
- RDO and the data dictionary have the concept of validation
clauses at the domain level. The ANSI/ISO SQL92 standard
allows CHECK constraints defined on domains. While the
actions of the ANSI/ISO CHECK constraint do differ from
VALID IF in some respects, the RMU Extract command extracts
the VALID IF clauses as domain CHECK constraints if you
specify the Language=SQL and Option=Full qualifiers.
- RDO multiline descriptions
Because the RDO interface removes blank lines in multiline
descriptions, the description saved in the metadata is not
identical to that entered by the database definition. The
RMU Extract command therefore cannot completely reconstruct
the original description.
- Some RDO trigger definitions
RDO trigger definitions that contain a trigger action
within a join of two or more tables generates invalid SQL
syntax. For example, the following RDO trigger definition
includes a join with an embedded ERASE statement. When the
RMU Extract command encounters this statement, Oracle RMU
generates the invalid SQL trigger definition shown.
DEFINE TRIGGER EXAMPLE
AFTER ERASE
FOR C1 IN EMPLOYEES
EXECUTE
FOR C2 IN JOB_HISTORY
CROSS C3 IN EMPLOYEES
WITH (((C2.EMPLOYEE_ID = C3.EMPLOYEE_ID)
AND (C2.JOB_END MISSING))
AND (C3.EMPLOYEE_ID = C2.EMPLOYEE_ID))
ERASE C2
END_FOR
FOR EACH RECORD.
CREATE TRIGGER EXAMPLE
AFTER DELETE ON EMPLOYEES
(DELETE FROM JOB_HISTORY C2, EMPLOYEES C3
WHERE (((C2.EMPLOYEE_ID = C3.EMPLOYEE_ID)
AND (C2.JOB_END IS NULL))
AND (C3.EMPLOYEE_ID = C2.EMPLOYEE_ID))
) FOR EACH ROW;
Note that in Oracle Rdb Version 4.1 and higher, including
a trigger action within a join of two or more tables
is invalid RDO syntax. For more information on this RDO
restriction, see the ERASE and MODIFY entries in RDO HELP.
o Oracle CDD/Repository Version 5.3 and higher support table
and column constraint definition and maintenance through CDO.
The RMU Extract command, by default, assumes all constraint
maintenance is with SQL and so follows each CREATE TABLE
with an ALTER TABLE FROM pathname to add the constraints.
However, this is no longer necessary if you are using the
later versions of Oracle CDD/Repository. To disable the output
of the SQL ALTER TABLE statements which add constraints use
the Option=Cdd_Constraint qualifier.
o If the Transaction_Type qualifier is omitted from the RMU
Extract command line, a READ ONLY transaction is started
against the database. This behavior is provided for backward
compatibility with prior Oracle Rdb releases. If the
Transaction_Type qualifier is specified without a transaction
mode, the default value Automatic is used.
o If the database has snapshots disabled and the Transaction_
Type qualifier was omitted, the transaction is restarted as
READ WRITE ISOLATION LEVEL READ COMMITTED to reduce the number
of rows locked by operations performed with the Option=Volume_
Scan qualifier enabled.
o When Transaction_Type=Write is specified, the RMU Extract
process does not attempt to write to the database tables.
o In previous versions, Oracle Rdb used derived column names
based on position, for example, F1, F2. With release 7.0.6.4
and later, Oracle Rdb promotes the column names from the base
table into the derived column name list. The result is a more
readable representation of the view or trigger definition.
In the following example the column name EMPLOYEE_ID is
propagated through the derived table. In previous releases
this would be named using a generic label F1.
create view SAMPLE_V
(EMPLOYEE_ID,
COUNTS) as
select
C1.EMPLOYEE_ID,
C1.F2
from
(select C2.EMPLOYEE_ID,
(select count(*) from SALARY_HISTORY C3
where (C3.EMPLOYEE_ID = C2.EMPLOYEE_ID))
from JOB_HISTORY C2) as C1 ( EMPLOYEE_ID, F2 )
order by C1.F2 asc;
o The following list shows the equivalent SQL expressions
matched by the RMU Extract process:
- NULLIF (a, b) is eqivalent to
CASE
WHEN a = b THEN NULL
ELSE a
END
- NVL (a, ..., b) or COALESCE (a, ..., b) is equivalent to
CASE
WHEN a IS NOT NULL THEN a
...
ELSE b
END
- The simple CASE expression
CASE a
WHEN b THEN v1
WHEN NULL THEN v2
...
ELSE v3
END
is equivalent to
CASE
WHEN a = b THEN v1
WHEN a IS NULL THEN v2
...
ELSE v3
END
The RMU Extract procedure tries to decode the internal
representation to as compact a SQL expression as possible.
o The RMU Extract procedure decodes case expressions into ABS
(Absolute) functions:
ABS(a) is equivalent to:
CASE
WHEN a < 0 THEN -a
ELSE a
END
In addition, similar forms of CASE expression are also
converted to ABS:
CASE
WHEN a <= 0 THEN -a
ELSE a
END
CASE
WHEN a > 0 THEN a
ELSE -a
END
CASE
WHEN a >= 0 THEN a
ELSE -a
END
It is possible that the RMU Extract process will change
existing CASE expressions into this more compact syntax, even
if they were not originally coded as an ABS function call.
o If the Group_Table option is used and the Item qualifier omits
one or more of the Table, Index, or Storage_Map keywords, only
the included items are displayed. For example, to extract just
the indexes for the EMPLOYEES table:
$ RMU/EXTRACT/ITEM=INDEX/OPTION=(GROUP_TABLE,MATCH=EMPLOYEES%)
To extract only the storage map and indexes for a table, use
the following command:
$ RMU/EXTRACT/ITEM=(STORAGE_MAP,INDEX)/OPTION=(GROUP_TABLE, -
_$ MATCH=EMPLOYEES%)
o If the name of the LIST storage map is not known, it can be
extracted using the following generic command:
$ RMU/EXTRACT/ITEM=STORAGE_MAP/OPTION=(GROUP_TABLE, -
_$ MATCH=RDB$SEGMENTED_STRING%)
6 – Examples
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;