VMS Help  —  SQL72  Information Tables
    Information tables display internal information about storage
    areas, after-image journals, row caches, database users, the
    database root, and database character sets. Once the information
    tables are created, you can use the INFO_TABLES.SQL script to
    query the tables with the SQL interface.

    Information tables are special read-only tables that can be
    created in an Oracle Rdb database and used to retrieve database
    attributes that are not stored in the existing relational tables.
    Information tables allow interesting database information, which
    is currently stored in an internal format, to be displayed as a
    relational table.

    The script, INFO_TABLES.SQL, is supplied as a part of the Oracle
    Rdb kit in the SQL$SAMPLE directory.

1  –  All Information Tables

    The following information tables are supported:

    Table Name                  Description

    RDB$STORAGE_AREAS           Displays information about the
                                database storage areas.
    RDB$DATABASE_JOURNAL        Displays information about the
                                default journal information.
    RDB$CACHES                  Displays information about the
                                database row caches.
    RDB$DATABASE_ROOT           Displays information about the
                                database root.
    RDB$JOURNALS                Displays information about the
                                database journal files.
    RDB$DATABASE_USERS          Displays information about the
                                database users.
    RDB$LOGICAL_AREAS           Displays information about the
                                logical areas.
    RDB$CHARACTER_SETS          Displays information about the Oracle
                                Rdb character sets.
    RDB$NLS_CHARACTER_SETS      Displays the mapping of Oracle
                                NLS character sets to Oracle Rdb
                                character sets.

2  –  Example

    The following example shows how to query one of the information
    tables created by the INFO_TABLES.SQL script:

    SQL> SELECT * FROM RDB$LOGICAL_AREAS WHERE RDB$LOGICAL_AREA_NAME='JOBS';
    RDB$LOGICAL_AREA_ID  RDB$AREA_ID  RDB$RECORD_LENGTH  RDB$THRESHOLD1_PERCENT
     RDB$THRESHOLD2_PERCENT  RDB$THRESHOLD3_PERCENT  RDB$ORDERED_HASH_OFFSET
      RDB$RECORD_TYPE  RDB$LOGICAL_AREA_NAME
                     95            7                 41                       0
                          0                       0                        0
                    1  JOBS

    1 row selected

3  –  RDB$STORAGE_AREAS

    Displays information about the database storage areas.

                                Data
    Column Name                 Type      Description

    RDB$LAST_BACKUP             date vms  Date of last backup
    RDB$LAST_INCREMENT_         date vms  Date of last restore
    RESTORE
    RDB$INITIAL_ALLOCATION      integer   Allocation
    RDB$CURRENT_ALLOCATION      integer   Maximum page number
    RDB$AREA_ACCESS_MODE        integer   Access mode
    RDB$THRESHOLD1_BYTES        integer   First threshold value
    RDB$THRESHOLD2_BYTES        integer   Second threshold value
    RDB$THRESHOLD3_BYTES        integer   Third threshold value
    RDB$PAGE_SIZE               integer   Page size
    RDB$AREA_MIN_EXTENT         integer   Minimum page extent
    RDB$AREA_MAX_EXTENT         integer   Maximum page extent
    RDB$AREA_PERCENT_EXTENT     integer   Percent growth
    RDB$EXTEND_COUNT            integer   Number of times area has
                                          been extended
    RDB$SPAM_INTERVAL           integer   Number of pages per SPAM
                                          page
    RDB$JOURNAL_SEQUENCE        integer   AIJ recovery version number
    RDB$MAX_PAGE_FREE_SPACE     integer   Maximum free space on new
                                          page
    RDB$MAX_ROW_LENGTH          integer   Largest segment that can be
                                          stored on a page
    RDB$SEQUENCE                integer   Sequence number
    RDB$AREA_ID                 integer   Area ID
    RDB$LINKED_AREA_ID          integer   For live storage area,
                                          ID of snapshot area. For
                                          snapshot area, ID of live
                                          storage area
    RDB$FLAGS                   integer   Flags
    RDB$THRESHOLD1_PERCENT      integer   First threshold percent
                                          value
    RDB$THRESHOLD2_PERCENT      integer   Second threshold percent
                                          value
    RDB$THRESHOLD3_PERCENT      integer   Third threshold percent
                                          value
    RDB$AREA_FILE               char(255) Storage area file name
    RDB$CACHE_ID                integer   Row cache ID
    RDB$AREA_NAME               char(31)  Storage area name
    RDB$CLUMP_PAGE_COUNT        integer   The number of pages per
                                          logical area clump (UNIFORM
                                          area)
    RDB$PAGE_LENGTH             integer   Page length in bytes
    RDB$PAGES_PER_SPAM_PAGE_    integer   Number of data pages per
    P1                                    SPAM page + 1
    RDB$SPAM_PAGE_VECTOR_       integer   SPAM page vector length in
    LENGTH                                bytes
    RDB$PAGE_PAD_LENGTH         integer   Page padding length in
                                          bytes
    RDB$MAX_SEGMENT_LENGTH      integer   Largest length to which an
                                          existing segment can grow
    RDB$BACKUP_STATISTICS       bigint    Backup-specific statistics
    RDB$SNAPS_ENABLED_TSN       bigint    TSN of oldest snapshots. If
                                          this is a live storage area
                                          that has snapshots allowed,
                                          this field contains the
                                          TSN of the most recent
                                          transaction that enabled
                                          snapshots for or performed
                                          exclusive updates to this
                                          area
    RDB$COMMIT_TSN              bigint    TSN to which this area is
                                          consistent. If the area
                                          is marked inconsistent,
                                          this is the TSN level to
                                          which the area is currently
                                          consistent
    RDB$BACKUP_TSN              bigint    TSN of last full area
                                          backup. If this field
                                          contains zero, this area
                                          has not been backed up
    RDB$INCR_BACKUP_TSN         bigint    TSN of last incremental
                                          area backup. If this field
                                          contains zero, this area
                                          has not been incrementally
                                          backed up
    RDB$LAST_EXTEND             date vms  Date of last area extend

3.1  –  RDB$FLAGS

    Represents flags for RDB$STORAGE_AREAS information table.

    Bit
    Position   Description

    0          Page format is mixed
    1          Checksum calculation is enabled
    2          This is a snapshot area
    3          Area has snapshots
    4          Snapshots are enabled
    5          Area has space management pages
    6          SPAM pages are enabled
    7          Allow multi-volume disk extents
    8          Extent is enabled
    9          Area is corrupt
    10         Area is inconsistent
    11         WORM device is full
    12         Page level locking
    13         Write once
    14         WORM area is corrupt
    15         Row cache is enabled
    16         Area has been restructured. If TRUE, this area has been
               restructured and may only be readied by a restructuring
               process
    17         Roll forward quiet-point is enabled
    18         Area is corrupt due to Hot Standby. If TRUE, this
               storage area may have been corrupted by an aborted
               batch update transaction

4  –  RDB$JOURNALS

    Displays information about the database journal files.

    Column Name                 Data Type      Description

    RDB$LAST_BACKUP             date vms       Date of last AIJ
                                               backup
    RDB$STATUS                  integer        AIJ file is
                                               inaccessible
    RDB$ALLOCATION              integer        Allocation
    RDB$EXTENT                  integer        Extent
    RDB$FLAGS                   integer        Flags
    RDB$JOURNAL_NAME            char(31)       Journal name
    RDB$LAST_BACKUP_SEQUENCE    integer        Last version number
                                               backed up
    RDB$LAST_ACTIVATED          date vms       Date last activated
    RDB$BACKUP_EDIT_STRING      varchar(255)   Backup editname file
    RDB$DEFAULT_FILENAME        char(255)      Default journal name
    RDB$FILENAME                char(255)      Journal name
    RDB$BACKUP_FILENAME         char(255)      Backup file name
    RDB$SEQUENCE_NUMBER         integer        Current AIJ sequence
                                               number
    RDB$STATE                   char(31)       State ("Current" or
                                               "Latent")

4.1  –  RDB$FLAGS

    Represents flags for RDB$JOURNALS information table.

    Bit
    Position   Description

    0          Initialization in progress
    1          Backup in progress
    2          AIJ file has been modified
    3          AIJ restored from existing file
    4          Hard data loss resulted from fail over
    5          Soft data loss resulted from fail over
    6          New version of journal created
    7          Journal has been overwritten
    8          Backup failed prematurely
    9          Journal created due to switch-over suspension
    10         AIJ file block has been assigned
    11         Journal created for recovery synchronization

5  –  RDB$CACHES

    Displays information about the database row caches.

                                Data
    Column Name                 Type      Description

    RDB$ALLOCATION              integer   Allocation
    RDB$EXTENT                  integer   Extent
    RDB$CACHE_ID                integer   Cache ID
    RDB$FLAGS                   integer   Flags
    RDB$ROW_LENGTH              integer   Row length
    RDB$CACHE_SIZE              integer   Cache size
    RDB$WINDOW_COUNT            integer   Window count
    RDB$NUM_RESERVED_ROWS       integer   Number of reserved rows
    RDB$NUM_SWEEP_ROWS          integer   Number of sweep rows
    RDB$CACHE_NAME              char(31)  Cache name
    RDB$LOCATION                char(255) Cache location
    RDB$SNAP_CACHE_SIZE         integer   Number of snapshot record
                                          slots in cache
    RDB$PHYSICAL_MEMORY         bigint    Physical memory in bytes

5.1  –  RDB$FLAGS

    Represents flags for RDB$CACHES information table.

    Bit
    Position   Description

    0          Shared memory is system
    1          Large memory is enabled
    2          Row replacement is enabled
    3          Device/directory is defined
    4          Updated rows to database
    5          Updated rows to backing file
    6          All rows to backing file
    7          Snapshots are enabled

6  –  RDB$DATABASE_ROOT

    Displays information about the database root.

                                    Data
    Column Name                     Type       Description

    RDB$CREATED                     date vms   Date of database
                                               creation
    RDB$LAST_FULL_BACKUP_TIME       date vms   Date of last complete
                                               full backup
    RDB$MAJOR_VERSION               integer    Major software version
    RDB$MINOR_VERSION               integer    Minor software version
    RDB$PRIOR_MAJOR_VERSION         integer    Major software version
                                               for database converted
                                               with /nocommit
    RDB$PRIOR_MINOR_VERSION         integer    Minor software version
                                               for database converted
                                               with /nocommit
    RDB$FLAGS                       integer    Flags
    RDB$MAX_USERS                   integer    Number of users
    RDB$MAX_CLUSTER_NODES           integer    Number of cluster
                                               nodes
    RDB$DEFAULT_NUM_OF_BUFFERS      integer    Number of buffers
    RDB$MAX_RECOVERY_BUFFERS        integer    Number of recover
                                               buffers
    RDB$BUFFER_SIZE                 integer    Buffer size
    RDB$GLOBAL_BUFFER_COUNT         integer    Number of global
                                               buffers
    RDB$ALG_COUNT                   integer    Adjustable lock
                                               granularity count
    RDB$LOCK_TIMEOUT_INTERVAL       integer    Lock timeout interval
                                               in seconds
    RDB$CHECKPOINT_INTERVAL_BLKS    integer    Checkpoint block
                                               interval
    RDB$CHECKPOINT_TIMED_SECONDS    integer    Checkpoint time
                                               interval
    RDB$TRANSACTION_INTERVAL        integer    Commit transaction
                                               interval
    RDB$CLOSE_MODE                  integer    Close mode
    RDB$CLOSE_INTERVAL              integer    Close interval
    RDB$ASYNC_PREFETCH_DEPTH        integer    Async prefetch depth
    RDB$D_ASYNC_PREFETCH_DEPTH      integer    Detected async
                                               prefetch depth
    RDB$D_ASYNC_PREFETCH_THRESHOLD  integer    Detected async
                                               prefetch threshold
    RDB$CLEAN_BUFFER_COUNT          integer    Clean buffer count
    RDB$MAX_BUFFER_COUNT            integer    Maximum buffer count
    RDB$MIN_PAGE_SIZE_BLOCKS        integer    Minimum area page
                                               block count
    RDB$MAX_PAGE_SIZE_BLOCKS        integer    Maximum area page
                                               block count
    RDB$TRANSACTION_MODE_FLAGS      integer    Transaction mode
    RDB$ALG_FACTOR_0                integer    Adjustable locking
                                               granularity factor 0
    RDB$ALG_FACTOR_1                integer    Adjustable locking
                                               granularity factor 1
    RDB$ALG_FACTOR_2                integer    Adjustable locking
                                               granularity factor 2
    RDB$ALG_FACTOR_3                integer    Adjustable locking
                                               granularity factor 3
    RDB$ALG_FACTOR_4                integer    Adjustable locking
                                               granularity factor 4
    RDB$ALG_FACTOR_5                integer    Adjustable locking
                                               granularity factor 5
    RDB$ALG_FACTOR_6                integer    Adjustable locking
                                               granularity factor 6
    RDB$ALG_FACTOR_7                integer    Adjustable locking
                                               granularity factor 7
    RDB$AUDIT_FILENAME              char(255)  Audit journal file
                                               name
    RDB$ROOT_FILENAME               char(255)  Database root file
                                               name
    RDB$RUJ_LOCATION                char(255)  Default recovery-unit
                                               journal file name
    RDB$CACHE_LOCATION              char(255)  Default
                                               device/directory
                                               specification for
                                               record cache files
    RDB$MAX_PAGES_IN_BUFFER         integer    Maximum number of
                                               pages in a buffer
    RDB$RCS_SWEEP_INTERVAL          integer    Row cache server (RCS)
                                               sweep interval (in
                                               seconds)
    RDB$RCS_CKPT_TIME               integer    Time interval to force
                                               row cache server (RCS)
                                               to checkpoint. This
                                               field contains the
                                               number of seconds
                                               that pass before RCS
                                               is forced to perform
                                               another checkpoint
    RDB$LAST_FULL_RESTORE           date vms   Date of last complete
                                               full restore
    RDB$AIJ_ACTIVATION_ID           bigint     AIJ journaling
                                               activation identifier
    RDB$RCVR_ACTIVATION_ID          bigint     RCVR journaling
                                               activation identifier
    RDB$OPER_CLASS                  integer    Operator notification
                                               classes
    RDB$PRESTART_TXN_TIMEOUT        integer    Seconds until
                                               prestarted transaction
                                               is abandoned. Zero
                                               means no abandon timer
    RDB$DB_REPLICATED               integer    AIJ log roll forward
                                               server started

6.1  –  RDB$FLAGS

    Represents flags for RDB$DATABASE_ROOT information table.

    Bit
    Position   Description

    0          Single file database
    1          Open mode
    2          Log server mode
    3          Snapshots are deferred
    4          Global buffers are enabled
    5          Carryover locks are enabled
    6          Statistics collection is enabled
    7          Fast commit is enabled
    8          AIJ commit optimization is enabled
    9          RUJ is corrupt
    10         Database is corrupt
    11         Fast incremental backup is enabled
    12         Async prefetch is enabled
    13         Async batch writes are enabled
    14         Lock partitioning is enabled
    15         Page transfer via memory
    16         Detected async prefetch is enabled
    17         Shared memory is system
    18         Database has been modified (TSN allocated)
    19         Database conversion has been committed
    20         Row cache server (RCS) checkpoints to database, by
               default
    21         RCS checkpoints to backing store files, by default
    22         RCS checkpoints marked and unmarked to RDC
    23         Global buffers should be in VLM
    24         Row cache RUJ global buffers are disabled
    25         LogMiner feature is enabled
    26         Prestarted transactions are enabled
    27         VMS Galaxy shared memory is enabled

7  –  RDB$DATABASE_JOURNAL

    Displays information about the default journal information.

                                Data
    Column Name                 Type      Description

    RDB$CONDITION               integer   AIJ status
    RDB$DEFAULT_ALLOCATION      integer   Default allocation in
                                          blocks
    RDB$DEFAULT_EXTENT          integer   Default extension in blocks
    RDB$CURRENT_BACKUP_         integer   Backup sequence number
    SEQUENCE
    RDB$CURR_RECOVERY_          integer   Recovery sequence number
    SEQUENCE
    RDB$DATABASE_BACKUP_        integer   Database backup sequence
    SEQUENCE                              number
    RDB$ALLOCATION              integer   Number of allocated AIJ
                                          file blocks
    RDB$SHUTDOWN_TIME_MIN       integer   Shutdown time in minutes
    RDB$OPERATOR_CLASSES        integer   Operator class
    RDB$FLAGS                   integer   Flags
    RDB$DEFAULT_BACKUP_         char(255) Default backup file name
    FILENAME
    RDB$CACHE_FILENAME          char(255) Cache file name
    RDB$STANDBY_FILENAME        char(255) Standby database file name
    RDB$SERVER_NAME             char(31)  Server name
    RDB$BACKUP_EDIT_STRING      varchar(25Backup editname file
    RDB$REMOTE_NODE_NAME        char(31)  Remote node name
    RDB$CUR_ACTIVE_AIJ          integer   Current active AIJ journal
                                          index
    RDB$MASTER_FILENAME         char(255) When database replication
                                          is active on the standby
                                          database, this field
                                          contains the file name
                                          of the master database

7.1  –  RDB$FLAGS

    Represents flags for RDB$DATABASE_JOURNAL information table.

    Bit
    Position   Description

    0          Journaling is enabled
    1          Overwrite is enabled
    2          Backup mode
    3          New journal version
    4          ABS uses quiet-point AIJ backup
    5          Replicated as master
    6          Replicated as standby
    7          Master replication database
    8          Database replication online
    9          Hot Standby quiet-point
    10         Hot Standby is enabled
    11         Database changes made when AIJ disabled
    12         One or more journals overwritten
    13         Hard data loss resulted from fail over
    14         Full quiet-point AIJ backup required

8  –  RDB$DATABASE_USERS

    Displays information about the database users.

                                Data
    Column Name                 Type      Description

    RDB$PROCESS_ID              integer   Process ID number
    RDB$STREAM_ID               integer   Stream ID number
    RDB$MONITOR_ID              integer   Monitor ID number
    RDB$ATTACH_ID               integer   Attach ID number
    RDB$FLAGS                   integer   Flags

8.1  –  RDB$FLAGS

    Represents flags for RDB$DATABASE_USERS information table.

    Bit
    Position   Description

    0          Client server process
    1          AIJ log server
    2          Process is being recovered
    3          Database server process
    4          Database utility process
    5          Catch-up server
    6          AIJ roll forward server
    7          Row cache server
    8          Log shipping server
    9          Backup server

9  –  RDB$LOGICAL_AREAS

    Displays information about the logical areas.

                                Data
    Column Name                 Type      Description

    RDB$LOGICAL_AREA_ID         integer   Logical area ID
    RDB$AREA_ID                 integer   Physical area ID
    RDB$FLAGS                   integer   Flags
    RDB$RECORD_LENGTH           integer   Record length
    RDB$THRESHOLD1_PERCENT      integer   First threshold percent
                                          value
    RDB$THRESHOLD2_PERCENT      integer   Second threshold percent
                                          value
    RDB$THRESHOLD3_PERCENT      integer   Third threshold percent
                                          value
    RDB$ORDERED_HASH_OFFSET     integer   Ordered hash offset
    RDB$RECORD_TYPE             integer   AIP record type
    RDB$LOGICAL_AREA_NAME       char(31)  Logical area name

9.1  –  RDB$FLAGS

    Represents flags for RDB$LOGICAL_AREAS information table.

    Bit
    Position   Description

    0          Logical area uses hash ordered index
    1          Logical area modified with unjournaled records
    2          Nologging is enabled

9.2  –  RDB$RECORD_TYPE

    Represents AIP record types for RDB$LOGICAL_AREAS information
    table.

    AIP Record
    Types         Description

    0             Unknown
    1             Table
    2             Sorted index
    3             Hashed index
    4             System record
    5             Segmented string

10  –  RDB$CHARACTER_SETS

    Displays information about the Oracle Rdb character sets.

                              Data
    Column Name               Type       Description

    RDB$LOWCASE_MAPPING       varbyte    Segmented string containing
                              list       a 256 byte table used for
                                         lowercasing characters
    RDB$UPCASE_MAPPING        varbyte    Segmented string containing
                              list       a 256 byte table used for
                                         uppercasing characters
    RDB$ASSOCIATED_           integer    Identifier of the associated
    CHARACTER_SET                        character set
    RDB$CHARACTER_SET_ID      integer    Character set identifier
    RDB$CHARACTER_SET_NAME    char(31)   Character set name
    RDB$CHARACTER_WILDCARD    integer    Character used as wildcard
                                         character
    RDB$FLAGS                 integer    Character set flags
    RDB$FORM_OF_USE           integer    Character set form-of-use
                                         indicator
    RDB$IDENTIFIER_           integer    Character set ID of the
    CHARACTER_SET                        indentifier character set
    RDB$MAXIMUM_OCTETS        integer    Maximum number of octets per
                                         character
    RDB$MINIMUM_OCTETS        integer    Minimum number of octets per
                                         character
    RDB$REPERTOIRE            integer    Character set repertoire
    RDB$SPACE_CHARACTER       integer    Character used as space
    RDB$STRING_WILDCARD       integer    Character used as string
                                         wildcard
    RDB$VERSION               integer    Version number of character
                                         set entry

10.1  –  RDB$REPERTOIRE

    Represents the repertoire values for RDB$CHARACTER_SETS
    information table.

    Value      Name           Description

    0          OTHER          Non-specific repertoire
    1          LATIN          Contains mainly Latin characters
    2          JAPANESE       Contains mainly Japanese characters
    3          SIMPLE_        Contains mainly simplified Chinese
               CHINESE        characters
    4          KOREAN         Contains mainly Korean characters
    5          OLD_CHINESE    Contains mainly traditional Chinese
                              characters
    6          UNIVERSAL      Contains universal characters; for
                              example, UNICODE
    7          INDIAN         Contains mainly Indian characters
    8          ARABIC         Contains mainly Arabic characters
    9          GREEK          Contains mainly Greek characters
    10         CYRILLIC       Contains mainly Cyrillic characters
    11         HEBREW         Contains mainly Hebrew characters

10.2  –  RDB$FORM_OF_USE

    Represents the form-of-use values for RDB$CHARACTER_SETS
    information table.

    Value      Name          Description

    0          FIXED_OCTET   Fixed octet
    1          MIXED_OCTET   Mixed octet with DEC_KANJI style
                             encoding
    2          FIXED_NO_UP   Fixed octet, no uppercasing allowed
    3          MIXED_SS2     As in MIXED_OCTET plus <SS2> as
                             introducer to alternate single octet
                             encoding
    4          FIXED_UP_G1   Fixed octet, uppercasing only 7-bit
                             characters
    5          MIXED_SS3     As in MIXED_SS2 plus <SS3> as introducer
                             to alternate double octet encoding
    6          FIXED_OTHER   Fixed octet, other
    7          MIXED_C2CB    As in MIXED_OCTET plus hex 'C2CB' as
                             introducer to alternate double octet
                             encoding
    9          MIXED_TAG     Mixed octet with leading tag, compound
                             string
    11         MIXED_SHIFT   Mixed octet with coding table shifted;
                             for example, SHIFT_JIS

10.3  –  RDB$FLAGS

    Represents flags for RDB$CHARACTER_SETS information table.

    Bit
    Position   Name                 Description

    0          CONTAINS_ASCII       Character set contains 7-bit
                                    ASCII characters
    1          SPACE_OCTET_REPEATS  All octets of the multi-octet
                                    space character are the same
                                    value

11  –  RDB$NLS_CHARACTER_SETS

    Represents the mapping of Oracle NLS character sets to Oracle Rdb
    character sets.

                          Data
    Column Name           Type      Description

    RDB$CHARACTER_SET_    integer   Character set identifier
    ID
    RDB$NLS_ID            integer   Oracle NLS identifier of
                                    character set
    RDB$NLS_NAME          char(31)  Oracle NLS character set name
Close Help