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%)