VMS Help
RMU72, Extract, Examples

 *Conan The Librarian

    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;
  Close     Help