SQL is an industry standard interface for accessing relational
databases. The SQL interface included with Oracle Rdb provides
full access to Oracle Rdb databases.
With Rdb's SQL interface, users can define, update, and query
relational databases. SQL provides the following environments for
issuing SQL statements:
o An interactive SQL utility
o A precompiler that lets users embed SQL statements in Ada, C,
COBOL, FORTRAN, Pascal, or PL/I programs
o SQL module language modules containing SQL statements that any
language can call
o A dynamic SQL interface that process SQL statements generated
when the program runs
1 – More Information
Develop applications using Oracle Rdb's SQL interface because
it complies with the ANSI/ISO standard for SQL languages. This
syntax is widely accepted as a standard for access to relational
databases.
2 – Release Notes
Release note information for Oracle Rdb's SQL interface is
included in the file for Oracle Rdb.
To see the current release notes, type or print the following
file:
SYS$HELP:RDBvvu.RELEASE_NOTES
where vv = version
u = update
For example:
SYS$HELP:RDB072.RELEASE_NOTES
3 – System Tables
Oracle Rdb stores information about the database as a set of
tables called system tables. The system tables are the definitive
source of Oracle Rdb metadata. Metadata defines the structure
of the database; for example, metadata defines the fields that
comprise a particular table and the fields that can index that
table.
The definitions of most system tables are standard and are likely
to remain constant in future versions of Oracle Rdb.
Under each Help topic for a particular system table, BLR refers
to binary language representation. This is low-level syntax used
internally to represent Oracle Rdb data manipulation operations.
3.1 – Special Notes
The following Help topics describe the usage of system tables
with respect to particular versions of Oracle Rdb or in table to
other database constructs, operations, or products.
3.1.1 – Using Data Dictionary
Although you can store your data definitions in the data
dictionary, the database system refers only to the system
tables in the database file itself for these definitions. In a
sense, the system tables are an internal data dictionary for the
database. This method improves performance as Oracle Rdb does not
have to access the data dictionary at run time.
3.1.2 – Modifying
When you create a database, Oracle Rdb defines, populates,
and manipulates the system tables. As the user performs data
definition operations on the database, Oracle Rdb reads and
modifies the system tables to reflect those operations. You
should not modify any of the Oracle Rdb system tables using data
manipulation language, nor should you define any domains based on
system table fields. However, you can use regular Oracle Rdb data
manipulation statements to retrieve the contents of the system
tables. This means that your program can determine the structure
and characteristics of the database by retrieving the fields of
the system tables. See the Example Help subtopic under System_
Tables in this Help library for an example of querying system
tables.
3.1.3 – Updating Metadata
When you use the SQL SET TRANSACTION . . . RESERVING statement
to lock a set of tables for an Oracle Rdb operation, you normally
exclude from the transaction all the tables not listed in the
RESERVING clause. However, Oracle Rdb accesses and updates system
tables as necessary, no matter which tables you have locked with
the SQL SET TRANSACTION statement.
When your transaction updates database metadata, Oracle Rdb
reserves the system tables involved in the update in the
EXCLUSIVE share mode. Other users are unable to perform data
definition operations on these tables until you complete your
transaction. For example:
o When you refer to a domain (global field) in an update
transaction that changes data definitions, Oracle Rdb locks
an index for the system table, RDB$RELATION_FIELDS. No other
users can refer to the same domain until you commit your
transaction.
o When you change a system table or domain definition, Oracle
Rdb locks an index in the system table, RDB$FIELD_VERSIONS. No
other users can change table or global field definitions until
you commit your transaction.
o When you change a table definition, Oracle Rdb locks an index
in the system table, RDB$RELATION_FIELDS. No other users can
change tables in the same index node until you commit your
transaction.
3.1.4 – LIST OF BYTE VARYING Metadata
Oracle Rdb has supported multiple segment LIST OF BYTE VARYING
data types for user-defined data. However in previous versions,
Oracle Rdb maintained its own LIST OF BYTE VARYING metadata
columns as single segments. This restricted the length to
approximately 65530 bytes. An SQL CREATE TRIGGER or CREATE MODULE
statement could fail due to this restriction.
This limit was lifted by changing the way Oracle Rdb stores its
own metadata.
o For columns containing binary data, such as the binary
representation of query, routine, constraint, trigger action,
computed by column, or query outline, Oracle Rdb breaks the
data into pieces that best fit the system storage area page
size. Thus, the segments are all the same size with a possible
small trailing segment.
The LIST OF BYTE VARYING column value is no longer fragmented,
improving performance when reading system metadata.
o For columns containing text data such as the SQL source
(for elements such as triggers and views) and user-supplied
comment strings, Oracle Rdb breaks the text at line boundaries
(indicated by ASCII carriage returns and line feeds) and
stores the text without the line separator. Thus, the segments
are of varying size with a possible zero length for blank
lines.
An application can now easily display the LIST OF BYTE VARYING
column value and the application no longer needs to break up
the single text segment for printing.
No change is made to the LIST OF BYTE VARYING column values
when a database is converted (using the RMU Convert command, RMU
Restore command, or SQL EXPORT/IMPORT statements) from a previous
version.
Applications that read the Oracle Rdb system LIST OF BYTE VARYING
column values must be changed to understand multiple segments.
Applications that do not read these system column values should
see no change to previous behavior. Tools such as the RMU Extract
command and the SQL SHOW and EXPORT statements handle both the
old and new formats of the system metadata.
3.2 – Read Only Access
The following is a list of fields of various system tables that
are set to read-only access.
o RDB$ACCESS_CONTROL
o RDB$FLAGS
o RDB$MODULE_OWNER
o RDB$ROUTINE_OWNER
3.3 – Example
The following BASIC program uses an SQL Module to query system
tables
PROGRAM SYSTEM_RELATION
! This BASIC program interactively prompts a user to enter a name
! of a system table (table). Next, the program calls an SQL
! Module which uses a cursor to read the system table that the
! user entered. Upon reading the fields (domains) of the system
! table, the program displays a message to the user as to whether
! the fields in a system table can be updated.
OPTION TYPE = EXPLICIT, SIZE = INTEGER LONG
ON ERROR GOTO ERR_ROUTINE
!
! Declare variables and constants
!
DECLARE STRING Column_name, Table_name
DECLARE INTEGER Update_yes, sqlcode
DECLARE INTEGER CONSTANT TRIM_BLANKS = 128, UPPER_CASE = 32
EXTERNAL SUB SET_TRANSACTION (LONG)
EXTERNAL SUB OPEN_CURSOR(LONG,STRING)
EXTERNAL SUB FETCH_COLUMN(LONG,STRING,INTEGER)
EXTERNAL SUB CLOSE_CURSOR(LONG)
EXTERNAL SUB COMMIT_TRANS (LONG)
!
! Prompt for table name
!
INPUT 'Name of Table'; Table_name
Table_name = EDIT$(Table_name, UPPER_CASE)
PRINT 'Starting query'
PRINT 'In '; Table_name; ' Table, columns:'
!
! Call the SQL module to start the transaction.
!
CALL SET_TRANSACTION(Sqlcode)
!
! Open the cursor.
!
CALL OPEN_CURSOR(Sqlcode, Table_name)
GET_LOOP:
WHILE (Sqlcode = 0)
!
! Fetch each column
!
CALL FETCH_COLUMN(Sqlcode, Column_name, Update_yes)
IF (Sqlcode = 0)
THEN
!
! Display returned column
!
PRINT ' '; EDIT$(Column_name, TRIM_BLANKS);
IF (update_yes = 1)
THEN
PRINT ' can be updated'
ELSE
PRINT ' cannot be updated'
END IF
END IF
NEXT
ERR_ROUTINE:
IF Sqlcode = 100
THEN
PRINT "No more rows."
RESUME PROG_END
ELSE
PRINT "Unexpected error: ", Sqlcode, Err
RESUME PROG_END
END IF
PROG_END:
!
! Close the cursor, commit work and exit
!
CALL CLOSE_CURSOR(Sqlcode)
CALL COMMIT_TRANS(Sqlcode)
END PROGRAM
The following module provides the SQL procedures that are called
by the preceding BASIC program.
-- This SQL module provides the SQL procedures that are called by the
-- preceding BASIC program, System Table
-----------------------------------------------------------------
-- Header Information Section
-----------------------------------------------------------------
MODULE SQL_SYSTEM_REL_BAS -- Module name
LANGUAGE BASIC -- Language of calling program
AUTHORIZATION SQL_SAMPLE -- Authorization ID
--------------------------------------------------------------------
-- DECLARE Statements Section
--------------------------------------------------------------------
DECLARE ALIAS FILENAME 'MF_PERSONNEL' -- Declaration of the database.
DECLARE SELECT_UPDATE CURSOR FOR
SELECT RDB$FIELD_NAME, RDB$UPDATE_FLAG
FROM RDB$RELATION_FIELDS
WHERE RDB$RELATION_NAME = table_name
ORDER BY RDB$FIELD_POSITION
--------------------------------------------------------------------
-- Procedure Section
--------------------------------------------------------------------
-- Start a transaction.
PROCEDURE SET_TRANSACTION
SQLCODE;
SET TRANSACTION READ WRITE;
-- Open the cursor.
PROCEDURE OPEN_CURSOR
SQLCODE
table_name RDB$RELATION_NAME;
OPEN SELECT_UPDATE;
-- Fetch a row.
PROCEDURE FETCH_COLUMN
SQLCODE
field_name RDB$FIELD_NAME
update_flag RDB$UPDATE_FLAG;
FETCH SELECT_UPDATE INTO :field_name, :update_flag;
-- Close the cursor.
PROCEDURE CLOSE_CURSOR
SQLCODE;
CLOSE SELECT_UPDATE;
-- Commit the transaction.
PROCEDURE COMMIT_TRANS
SQLCODE;
COMMIT;
3.4 – All System Tables
The Oracle Rdb system tables are as follows:
RDB$CATALOG_SCHEMA Contains the name and definition of
each SQL catalog and schema. This
table is present only in databases
with the SQL multischema feature
enabled.
RDB$COLLATIONS The collating sequences used by this
database.
RDB$CONSTRAINTS Name and definition of each
constraint.
RDB$CONSTRAINT_RELATIONS Name of each table that participates
in a given constraint.
RDB$DATABASE Database-specific information.
RDB$FIELD_VERSIONS One row for each version of each
column definition in the database.
RDB$FIELDS Characteristics of each domain in the
database.
RDB$GRANTED_PROFILES Description of roles and profiles
granted to users and other roles.
RDB$INDEX_SEGMENTS Columns that make up an index.
RDB$INDICES Characteristics of the indexes for
each table.
RDB$INTERRELATIONS Interdependencies of entities used in
the database.
RDB$MODULES Module definition as defined by
a user, including the header and
declaration section.
RDB$OBJECT_SYNONYMS When synonyms are enabled, this
system table is created to describe
the synonym name, type, and target.
RDB$PARAMETERS Interface definition for each
routine stored in RDB$ROUTINES. Each
parameter to a routine (procedure or
function) is described by a row in
RDB$PARAMETERS.
RDB$PRIVILEGES Protection for the database objects.
RDB$PROFILES Description of any profiles, roles or
users in the database.
RDB$QUERY_OUTLINES Query outline definitions used by
the optimizer to retrieve known query
outlines prior to optimization.
RDB$RELATION_CONSTRAINTS Lists all table-specific constraints.
RDB$RELATION_CONSTRAINT_ Lists the columns that participate
FLDS in unique, primary, or foreign key
declarations for table-specific
constraints.
RDB$RELATION_FIELDS Columns defined for each table.
RDB$RELATIONS Tables and views in the database.
RDB$ROUTINES Description of each function and
procedure in the database. The
routine may be standalone or part
of a module.
RDB$SEQUENCES Characteristics of any sequences
defined for the database.
RDB$STORAGE_MAPS Characteristics of each storage map.
RDB$STORAGE_MAP_AREAS Characteristics of each partition of
a storage map.
RDB$SYNONYMS Connects an object's user-specified
name to its internal database name.
This table is only present in
databases with the SQL multischema
feature enabled.
RDB$TRIGGERS Definition of a trigger.
RDB$VIEW_RELATIONS Interdependencies of tables used in
views.
RDB$WORKLOAD Collects workload information.
3.5 – RDB$CATALOG_SCHEMA
The RDB$CATALOG_SCHEMA system table contains the name and
definition of each SQL catalog and schema. This table is present
only in databases that have the SQL multischema feature enabled.
The following table provides information on the columns of the
RDB$CATALOG_SCHEMA system table.
Data
Column Name Type Summary Description
RDB$PARENT_ID integer For a schema, this is the
RDB$CATALOG_SCHEMA_ID
of the catalog to which
this schema belongs. For
a catalog, this column is
always 0.
RDB$CATALOG_SCHEMA_NAME char(31) The name of the catalog or
schema.
RDB$CATALOG_SCHEMA_ID integer A unique identifier
indicating whether this
is a catalog or a schema.
Schema objects have
positive identifiers
starting at 1 and
increasing. Catalog
objects have negative
identifiers starting at
-1 and decreasing. 0 is
reserved.
RDB$DESCRIPTION list A user-supplied description
of byte of the catalog or schema.
varying
RDB$SCHEMA_AUTH_ID char(31) The authorization
identifier of the creator
of the schema.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the schema or
catalog is created.
RDB$LAST_ALTERED date vms Set when SQL ALTER CATALOG
or ALTER SCHEMA statement
is used (future).
RDB$CATALOG_SCHEMA_ char(31) Creator of this schema or
CREATOR catalog.
3.6 – RDB$COLLATIONS
The RDB$COLLATIONS system table describes the collating sequence
to be used in the database. The following table provides
information on the columns of the RDB$COLLATIONS system table.
Data
Column Name Type Summary Description
RDB$COLLATION_NAME char(31) Supplies the name by which
the database's collating
sequences are known within
the database.
RDB$COLLATION_SEQUENCE list Internal representation of
of byte the collating sequence.
varying
RDB$DESCRIPTION list A user-supplied description
of byte of the collating sequence.
varying
RDB$FLAGS integer A bit mask where the
following bits are set:
o Bit 0
If an ASCII collating
sequence.
o Bit 1
If a DEC_MCS collating
sequence.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the collating
sequence is created.
RDB$LAST_ALTERED date vms Reserved for future use.
RDB$COLLATION_CREATOR char(31) Creator of this collating
sequence.
3.7 – RDB$CONSTRAINTS
The RDB$CONSTRAINTS system table contains the name and definition
of each constraint. The following table provides information on
the columns of the RDB$CONSTRAINTS system table.
Data
Column Name Type Summary Description
RDB$CONSTRAINT_NAME char(31) The system-wide unique name
of the constraint.
RDB$CONSTRAINT_BLR list The BLR that defines the
of byte constraint.
varying
RDB$CONSTRAINT_SOURCE list The user's source for the
of byte constraint.
varying
RDB$DESCRIPTION list A user-supplied description
of byte of this constraint.
varying
RDB$EVALUATION_TIME integer A value that represents
when a constraint is
evaluated, as follows:
o 0
At commit time (deferred
initially deferred).
o 1
At verb time (deferrable
initially immediate).
o 2
At verb time (not
deferrable).
RDB$EXTENSION_PARAMETERS list Reserved for future use.
of byte
varying
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the constraint is
created.
RDB$LAST_ALTERED date vms Reserved for future use.
RDB$CONSTRAINT_CREATOR char(31) Creator of this constraint.
RDB$FLAGS integer Flags.
3.7.1 – RDB$FLAGS
Represents flags for RDB$CONSTRAINTS system table.
Bit
Position Description
0 Currently disabled.
1 Currently enabled without validation.
3.8 – RDB$CONSTRAINT_RELATIONS
The RDB$CONSTRAINT_RELATIONS system table lists all tables that
participate in a given constraint. The following table provides
information on the columns of the RDB$CONSTRAINT_RELATIONS system
table.
Data
Column Name Type Summary Description
RDB$CONSTRAINT_NAME char(31) The system-wide unique name
of the constraint.
RDB$RELATION_NAME char(31) The name of a table
involved in the constraint.
RDB$FLAGS integer Flags.
RDB$CONSTRAINT_CONTEXT integer The context variable of
the table involved in the
constraint.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.8.1 – RDB$FLAGS
Represents flags for RDB$CONSTRAINT_RELATIONS system table.
Bit
Position Description
0 Reserved for future use.
1 Reserved for future use.
2 If the constraint is on the specified table.
3 If the constraint evaluates with optimization by dbkey
lookup.
4 If the constraint checks for existence.
5 If the constraint checks for uniqueness.
6 If the constraint needs to evaluate on store of
specified table row.
7 If the constraint need not evaluate on store of
specified table row.
8 If the constraint needs to evaluate on erase of
specified table row.
9 If the constraint need not evaluate on erase of
specified table row.
3.9 – RDB$DATABASE
The RDB$DATABASE system table contains information that pertains
to the overall database. This table can contain only one row.
The following table provides information on the columns of the
RDB$DATABASE system table.
Data
Column Name Type Summary Description
RDB$CDD_PATH char 256 The dictionary path name
for the database.
RDB$FILE_NAME char 255 Oracle Rdb returns the
file specification of the
database root file. [1]
RDB$MAJ_VER integer Derived from the database
major version.
RDB$MIN_VER integer Derived from the database
minor version.
RDB$MAX_RELATION_ID integer The largest table
identifier assigned. Oracle
Rdb assigns the next table
an ID of MAX_RELATION_ID +
1.
RDB$RELATION_ID integer The unique identifier of
the
RDB$RELATIONS table. If
you drop a table, that
identifier is not assigned
to any other table.
RDB$RELATION_ID_ROOT_DBK char(8) A pointer (database key or
dbkey) to the base of the
RDB$REL_REL_ID_NDX index on
column RDB$RELATION_ID.
RDB$RELATION_NAME_ROOT_ char(8) A pointer (dbkey) to the
DBK base of the RDB$REL_REL_
NAME_NDX index on column
RDB$RELATION_NAME.
RDB$FIELD_ID integer The identifier of the
RDB$FIELD_VERSIONS table.
RDB$FIELD_REL_FLD_ROOT_ char(8) A pointer (dbkey) to the
DBK base of the RDB$VER_REL_
ID_VER_NDX index on columns
RDB$RELATION_ID and
RDB$VERSION.
RDB$INDEX_ID integer The identifier of the
RDB$INDICES table.
RDB$INDEX_NDX_ROOT_DBK char(8) A pointer (dbkey) to the
base of the RDB$NDX_NDX_
NAME_NDX index on column
RDB$INDEX_NAME.
RDB$INDEX_REL_ROOT_DBK char(8) A pointer (dbkey) to the
base of the RDB$NDX_REL_
NAM_NDX index on column
RDB$RELATION_ID.
RDB$INDEX_SEG_ID integer The identifier of the
RDB$INDEX_SEGMENTS table.
RDB$INDEX_SEG_FLD_ROOT_ char(8) A pointer (dbkey) to the
DBK base of the RDB$NDX_SEG_
NAM_FLD_POS_NDX index on
columns RDB$INDEX_NAME and
RDB$FIELD_POSITION.
RDB$SEGMENTED_STRING_ID integer The logical area ID that
contains the segmented
strings.
RDB$ACCESS_CONTROL list The access control policy
of byte for the database.
varying
RDB$DESCRIPTION list A user-supplied description
of byte of the database.
varying
RDB$DATABASE_PARAMETERS list Reserved for future use.
of byte
varying
RDB$EXTENSION_PARAMETERS list Reserved for future use.
of byte
varying
RDB$FLAGS integer Flags.
RDBVMS$MAX_VIEW_ID integer The largest view
identifier assigned to
the RDB$RELATION_ID column
in the RDB$RELATIONS system
table. Oracle Rdb assigns
the next view an ID of MAX_
VIEW_ID + 1.
RDBVMS$SECURITY_AUDIT integer A bit mask that indicates
the privileges that will be
audited for the database,
as specified in the RMU Set
Audit command.
RDBVMS$SECURITY_ALARM integer A bit mask that indicates
the privileges that will
produce alarms for the
database, as specified in
the RMU Set Audit command.
RDBVMS$SECURITY_USERS list An access control list
of byte that identifies users
varying who will be audited or
who will produce alarms
for DAC (discretionary
access control) events
when DACCESS (discretionary
access) auditing is enabled
for specific database
objects.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDBVMS$SECURITY_AUDIT2 integer Reserved for future use.
RDBVMS$SECURITY_ALARM2 integer Reserved for future use.
RDBVMS$CHARACTER_SET_ID integer Value is the character set
ID used for the identifier
character set.
RDBVMS$CHARACTER_SET_ integer Value is the character set
NATIONAL ID used for all NCHAR (also
called NATIONAL CHAR or
NATIONAL CHARACTER) data
types and literals.
RDBVMS$CHARACTER_SET_ integer Value is the character set
DEFAULT ID used for the default
character set.
RDB$MAX_ROUTINE_ID integer Maintains a count of the
modules and routines added
to the database. Value is 0
if no routines or modules
have been added to the
database.
RDB$CREATED date vms Set when the database is
created.
RDB$LAST_ALTERED date vms Set when SQL ALTER DATABASE
statement is used.
RDB$DATABASE_CREATOR char(31) Creator of this database.
RDB$DEFAULT_STORAGE_AREA_ integer Default storage area used
ID for unmapped, persistent
tables and indices.
RDB$DEFAULT_TEMPLATE_ integer Reserved for future use.
AREA_ID
Footnote:
[1] The root file specification is not stored on disk (an RMU
Dump command with the Areas qualifier shows that this field
is blank) and is only returned to queries at runtime.
Therefore, the root file specification remains correct
after you use the RMU Move_Area, RMU Copy_Database, and RMU
Backup commands, and the SQL EXPORT and IMPORT statements.
The following ALTER DATABASE clauses modify the RDB$LAST_ALTERED
column in the RDB$DATABASE system table:
o CARDINALITY COLLECTION IS {ENABLED | DISABLED}
o DICTIONARY IS [NOT] REQUIRED
o DICTIONARY IS NOT USED
o METADATA CHANGES ARE {ENABLED | DISABLED}
o MULTISCHEMA IS {ON | OFF}
o SECURITY CHECKING IS EXTERNAL (PERSONA SUPPORT IS {ENABLED |
DISABLED})
o SECURITY CHECKING IS INTERNAL (ACCOUNT CHECK IS {ENABLED |
DISABLED})
o SYNONYMS ARE ENABLED
o WORKLOAD COLLECTION IS {ENABLED | DISABLED}
The following SQL statements modify the RDB$LAST_ALTERED column
in the RDB$DATABASE system table:
o GRANT statement
o REVOKE statement
o COMMENT ON DATABASE statement
3.9.1 – RDB$FLAGS
Represents flags for RDB$DATABASE system table.
Bit
Position Description
0 If dictionary required.
1 If ANSI protection used.
2 If database file is a CDD$DATABASE database.
3 Reserved for future use.
4 Reserved for future use.
5 Reserved for future use.
6 Multischema is enabled.
7 Reserved for future use.
8 System indexes use run length compression.
9 The optimizer saves workload in RDB$WORKLOAD system
table.
10 The optimizer is not updating table and index
cardinalities.
11 All metadata changes are disabled.
12 Oracle Rdb uses database for user and role names.
13 If security is internal, validate the UIC. If security
is external then this indicates that persona support is
enabled.
14 Synonyms are supported.
15 Prefix cardinalities are not collected for system
indexes.
16 If collecting, use full algorithm for system indexes.
17 Use sorted ranked index for system indexes.
3.10 – RDB$FIELD_VERSIONS
The RDB$FIELD_VERSIONS system table is an Oracle Rdb extension.
This table contains one row for each version of each column
definition in the database. The following table provides
information on the columns of the RDB$FIELD_VERSIONS system
table.
Data
Column Name Type Summary Description
RDB$RELATION_ID integer The identifier for a table
within the database.
RDB$FIELD_ID integer An identifier used
internally to name the
column represented by this
row.
RDB$FIELD_NAME char(31) The name of the column.
RDB$VERSION integer The version number for the
table definition to which
this column belongs.
RDB$FIELD_TYPE integer The data type of the column
represented by this row.
This data type must be
interpreted according to
the rules for interpreting
the DSC$B_DTYPE field of
class S descriptors (as
defined in the OpenVMS
Calling Standard).
Segmented strings require
a unique field type
identifier. This identifier
is currently 261.
RDB$FIELD_LENGTH integer The length of the column
represented by this
row. This length must be
interpreted according to
the rules for interpreting
the DSC$W_LENGTH field
within class S and SD
descriptors (as defined
in the OpenVMS Calling
Standard).
RDB$OFFSET integer The byte offset of the
column from the beginning
of the row.
RDB$FIELD_SCALE integer For numeric data types,
the scale factor to be
applied when interpreting
the contents of the column
represented by this row.
This scale factor must be
interpreted according to
the rules for interpreting
the DSC$B_SCALE field of
class SD descriptors (as
defined in the OpenVMS
Calling Standard).
For date-time data
types, RDB$FIELD_SCALE
is fractional seconds
precision. For other non-
numeric data types,
RDB$FIELD_SCALE is 0.
RDB$FLAGS integer Flags.
RDB$VALIDATION_BLR list The BLR that represents the
of byte SQL check constraint clause
varying defined in this version of
the column.
RDB$COMPUTED_BLR list The BLR that represents the
of byte SQL clause, COMPUTED BY,
varying defined in this version of
the column.
RDB$MISSING_VALUE list The BLR that represents the
of byte SQL clause, MISSING_VALUE,
varying defined in this version of
the column.
RDB$SEGMENT_LENGTH integer The length of a segmented
string segment. For date-
time interval fields, the
interval leading field
precision.
RDBVMS$COLLATION_NAME char(31) The name of the collating
sequence for the column.
RDB$ACCESS_CONTROL list The access control list for
of byte the column.
varying
RDB$DEFAULT_VALUE2 list The SQL default value.
of byte
varying
RDBVMS$SECURITY_AUDIT integer A bit mask that indicates
the privileges that will be
audited for the database,
as specified in the RMU Set
Audit command.
RDBVMS$SECURITY_ALARM integer A bit mask that indicates
the privileges that will
produce alarms for the
database, as specified in
the RMU Set Audit command.
RDB$FIELD_SUB_TYPE integer A value that describes the
data subtype of RDB$FIELD_
TYPE as shown in Help topic
RDB$FIELD_SUB_TYPE.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.10.1 – RDB$FLAGS
Represents flags for RDB$FIELD_VERSIONS system table.
Bit
Position Description
0 Not used.
1 Not used.
2 Not used.
3 Used by Oracle Rdb internally.
4 Set if column references a local temporary table
(usually a COMPUTED BY column).
5 Use SQL semantics for check constraint processing.
6 AUTOMATIC set on insert.
7 AUTOMATIC set on update.
8 If check constraint fails, use name in message.
9 Column is computed by an IDENTITY sequence.
10 View column is based on a read-only, or dbkey column.
3.11 – RDB$PARAMETER_SUB_TYPE
For details, see the Help topic RDB$FIELD_SUB_TYPE.
3.12 – RDB$FIELD_SUB_TYPE
The following table lists the values for the RDB$FIELD_SUB_TYPE
and the RDB$PARAMETER_SUB_TYPE columns.
RDB$FIELD_TYPE = DSC$K_DTYPE_ADT
RDB$FIELD_SUB_TYPE [1] Summary Description
Less than 0 Reserved for future use.
Equal to 0 Traditional OpenVMS timestamp, which
includes year, month, day, hour,
minute, second.
7 DATE ANSI, which includes year,
month, day.
56 TIME, which includes hour, minute,
second.
63 TIMESTAMP, which includes year,
month, day, hour, minute, second.
513 INTERVAL YEAR.
514 INTERVAL MONTH.
515 INTERVAL YEAR TO MONTH.
516 INTERVAL DAY.
520 INTERVAL HOUR.
524 INTERVAL DAY TO HOUR.
528 INTERVAL MINUTE.
536 INTERVAL HOUR TO MINUTE.
540 INTERVAL DAY TO MINUTE.
544 INTERVAL SECOND.
560 INTERVAL MINUTE TO SECOND.
568 INTERVAL HOUR TO SECOND.
572 INTERVAL DAY TO SECOND.
RDB$FIELD_TYPE = DSC$K_DTYPE_T or DSC$K_DTYPE_VT
RDB$FIELD_SUB_TYPE Summary Description
Equal to 0 ASCII or DEC_MCS character set.
Greater than 0 Character set other than ASCII or
DEC_MCS.
Less than 0 Special use of character data.
RDB$FIELD_TYPE = DSC$K_DTYPE_BLOB [2]
RDB$FIELD_SUB_TYPE Summary Description
Less than 0 User-specified.
Equal to 0 Default.
Equal to 1 BLR (query) type.
Equal to 2 Character type.
Equal to 3 MBLR (definition) type.
Equal to 4 Binary type.
Equal to 5 OBLR (outline) type.
Greater than 5 Reserved for future use.
Footnotes:
[1] When RDB$FIELD_SUB_TYPE is not equal to 0, then
RDB$SEGMENT_LENGTH can hold the interval leading field
precision for intervals, and RDB$FIELD_SCALE can hold
the fractional seconds precision for interval, time, or
timestamp.
[2] RDB$SEGMENT_LENGTH is the suggested size for a single
binary large object (BLOB) segment.
3.13 – RDB$FIELDS
The RDB$FIELDS system table describes the global (generic)
characteristics of each domain in the database. There is one
row for each domain in the database. The following table provides
information on the columns of the RDB$FIELDS system table.
Data
Column Name Type Summary Description
RDB$FIELD_NAME char(31) The name of the domain
represented by this row.
Each row within
RDB$FIELDS must have a
unique
RDB$FIELD_NAME value.
RDB$FIELD_TYPE integer The data type of the domain
represented by this row.
This data type must be
interpreted according to
the rules for interpreting
the DSC$B_DTYPE field of
class S descriptors (as
defined in the OpenVMS
Calling Standard).
Segmented strings require
a unique field type
identifier. This identifier
is 261.
RDB$FIELD_LENGTH integer The length of the field
represented by this
row. This length must be
interpreted according to
the rules for interpreting
the DSC$W_LENGTH field
within class S and SD
descriptors (as defined in
OpenVMS Calling Standard).
For strings, this field
contains the length in
octets (8-bit bytes), not
in characters.
RDB$FIELD_SCALE integer For numeric data types,
the scale factor to be
applied when interpreting
the contents of the field
represented by this row.
This scale factor must be
interpreted according to
the rules for interpreting
the DSC$B_SCALE field of
class SD descriptors (as
defined in the OpenVMS
Calling Standard). For
date-time data types,
RDB$FIELD_SCALE is
fractional seconds
precision. For other
non-numeric data types,
RDB$FIELD_SCALE is 0.
RDB$SYSTEM_FLAG integer A bit mask where the
following bits are set:
o If Bit 0 is clear,
this is a user-defined
domain.
o If Bit 0 is set, this is
a system domain.
RDB$VALIDATION_BLR list The BLR that represents the
of byte validation expression to be
varying checked each time a column
based on this domain is
updated.
RDB$COMPUTED_BLR list The BLR that represents
of byte the expression used to
varying calculate a value for
the column based on this
domain.
RDB$EDIT_STRING varchar(25The edit string used
by interactive SQL when
printing the column based
on this domain. RDB$EDIT_
STRING can be null.
RDB$MISSING_VALUE list The value used when the
of byte missing value of the column
varying based on this domain is
retrieved or displayed.
RDB$MISSING_VALUE does
not store any value in
a column; instead, it
flags the column value
as missing.
RDB$FIELD_SUB_TYPE integer A value that describes the
data subtype of RDB$FIELD_
TYPE as shown in the
RDB$FIELD_SUB_TYPE Help
topic.
RDB$DESCRIPTION list A user-supplied description
of byte of this domain.
varying
RDB$VALIDATION_SOURCE list The user's source text for
of byte the validation criteria.
varying
RDB$COMPUTED_SOURCE list The user's source used
of byte to calculate a value at
varying execution time.
RDB$QUERY_NAME char(31) The query name of this
domain. Column attributes
in RDB$RELATION_FIELDS take
precedence over attributes
in
RDB$FIELDS.
If the attribute value is
missing in
RDB$RELATION_FIELDS, the
value from RDB$FIELDS is
used.
RDB$QUERY_NAME can be null.
RDB$QUERY_HEADER list The query header of
of byte the domain is used by
varying interactive SQL. Column
attributes in
RDB$RELATION_FIELDS take
precedence over attributes
in RDB$FIELDS.
If the attribute value is
missing in
RDB$RELATION_FIELDS, the
value from RDB$FIELDS is
used.
RDB$DEFAULT_VALUE list The default value used by
of byte non-SQL interfaces when
varying no value is specified for
a column during a STORE
clause. It differs from
RDB$MISSING_VALUE in that
it holds an actual column
value.
Column attributes in
RDB$RELATION_FIELDS take
precedence over attributes
in RDB$FIELDS.
If the attribute value is
missing in
RDB$RELATION_FIELDS, the
value from RDB$FIELDS is
used.
RDB$SEGMENT_LENGTH integer The length of a segmented
string segment. For date-
time interval fields, the
interval leading field
precision.
RDB$EXTENSION_PARAMETERS list Reserved for future use.
of byte
varying
RDB$CDD_NAME list The fully qualified name of
of byte the dictionary entity upon
varying which the domain definition
is based, as specified
in the SQL clause, FROM
PATHNAME.
RDBVMS$COLLATION_NAME char(31) The name of the collating
sequence for the domain.
RDB$DEFAULT_VALUE2 list The BLR for the SQL default
of byte value. This value is used
varying when no value is provided
in an SQL INSERT statement.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$FLAGS integer Flags.
RDB$CREATED date vms Set when the domain is
created.
RDB$LAST_ALTERED date vms Set when SQL ALTER DOMAIN
statement used.
RDB$FIELD_CREATOR char(31) Creator of this domain.
3.13.1 – RDB$FLAGS
Represents flags for RDB$FIELDS system table.
Bit
Position Description
0 A SQL CHECK constraint is defined on this domain.
1 AUTOMATIC set on insert.
2 AUTOMATIC set on update.
3 If check constraint fails, use name in message.
4 Column is computed an IDENTITY sequence.
5 View column is based on a read-only, or dbkey column.
3.14 – RDB$GRANTED_PROFILES
The RDB$GRANTED_PROFILES system table contains information
about each profile, and role granted to other roles and users.
The following table provides information on the columns of
the RDB$GRANTED_PROFILES system table. See also the related
RDB$PROFILES system table.
Data
Column Name Type Summary Description
RDB$GRANTEE_PROFILE_ID integer This is a unique identifier
generated for the parent
RDB$PROFILES row.
RDB$PROFILE_TYPE integer Class of profile
information: role (1),
default role (2), profile
(0).
RDB$PROFILE_ID integer Identification of the
profile or role granted
to this user.
3.15 – RDB$INDEX_SEGMENTS
The RDB$INDEX_SEGMENTS system table describes the columns that
make up an index's key. Each index must have at least one column
within the key. The following table provides information on the
columns of the RDB$INDEX_SEGMENTS system table.
Data
Column Name Type Summary Description
RDB$INDEX_NAME char(31) The name of the index
of which this row is a
segment.
RDB$FIELD_NAME char(31) The name of a column that
participates in the index
key. This column name
matches the name in the
RDB$FIELD_NAME column of
the RDB$RELATION_FIELDS
table.
RDB$FIELD_POSITION integer The ordinal position of
this key segment within
the total index key. No
two segments in the key may
have the same RDB$FIELD_
POSITION.
RDB$FLAGS integer A bit mask where Bit 0
is set for descending
segments, otherwise the
segments are ascending.
RDB$FIELD_LENGTH integer Shortened length of text
for compressed indexes.
RDBVMS$FIELD_MAPPING_LOW integer Shows the lower limit of
the mapping range.
RDBVMS$FIELD_MAPPING_HIGH integer Shows the higher limit of
the mapping range.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CARDINALITY bigint Prefix cardinality for this
and all prior key segments
(assumes sorting by ordinal
position).
3.16 – RDB$INDICES
The RDB$INDICES system table contains information about indexes
in the database. The following table provides information on the
columns of the RDB$INDICES system table.
Data
Column Name Type Summary Description
RDB$INDEX_NAME char(31) A unique index name.
RDB$RELATION_NAME char(31) The name of the table in
which the index is used.
RDB$UNIQUE_FLAG integer A value that indicates
whether duplicate values
are allowed in indexes, as
follows:
o 0
If duplicate values are
allowed.
o 1
If no duplicate values
are allowed.
RDB$ROOT_DBK char(8) A pointer to the base of
the index.
RDB$INDEX_ID integer The identifier of the
index.
RDB$FLAGS integer Flags.
RDB$SEGMENT_COUNT integer The number of segments in
the key.
RDB$DESCRIPTION list A user-supplied description
of byte of this index.
varying
RDB$EXTENSION_PARAMETERS list Stores NODE SIZE value,
of byte PERCENT FILL value,
varying compression algorithm, and
compression run length for
this index. Also reserved
for other future use.
RDB$CARDINALITY bigint The number of unique
entries for a non-unique
index. For a unique index,
the number is 0.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the index is
created.
RDB$LAST_ALTERED date vms Set when SQL ALTER INDEX
statement is used.
RDB$INDEX_CREATOR char(31) Creator of this index.
RDB$KEY_CLUSTER_FACTOR bigint(7) Sorted Index: The ratio
of the number of clump
changes that occur when
you traverse level-
1 index nodes and the
duplicate node chains
to the number of keys in
the index. This statistic
is based on entire index
traversal. This means last
duplicate node of current
key is compared with first
duplicate node of next key
for clump change.
Hash Index: The average
number of clump changes
that occur when you go
from system record to hash
bucket to overflow hash
bucket (if fragmented), and
traverse the duplicate node
chain for each key. This
statistic is based on per
key traversal.
RDB$DATA_CLUSTER_FACTOR bigint(7) Sorted Index: The ratio of
the number of clump changes
that occur between adjacent
dbkeys in duplicate chains
of all keys to the number
of keys in the index. For
unique index, the dbkeys of
adjacent keys are compared
for clump change. This
statistic is based on
entire index traversal.
This means last dbkey of
current key is compared
with first dbkey of next
key for clump change.
Hashed Index: The average
number of clump changes
that occur between adjacent
dbkeys in a duplicate chain
for each key. For a unique
index, this value will be
always 1. This statistic is
based on per key traversal.
RDB$INDEX_DEPTH integer Sorted Index: The depth of
the B-tree.
Hashed Index: This column
is not used for hashed
indices and is left as 0.
3.16.1 – RDB$FLAGS
Represents flags for RDB$INDICES system table.
Bit
Position Description
0 Hashed index.
1 Index segments are numeric with mapping values
compression.
2 Hashed ordered index. (If bit is clear, hashed
scattered.)
3 Reserved for future use.
4 Run-length compression.
5 Index is disabled or enabled deferred.
6 Build pending (enabled deferred).
7 Reserved for future use.
8 Reserved for future use.
9 Reserved for future use.
10 Reserved for future use.
11 If on, duplicates are compressed.
12 Sorted ranked index.
13 Prefix cardinalities disabled.
14 Use the full collection algorithm for prefix
cardinality.
15 Index generated for a constraint when SET FLAGS 'AUTO_
INDEX' was enabled.
3.17 – RDB$INTERRELATIONS
The RDB$INTERRELATIONS system table contains information that
indicates the interdependencies of objects in the database. The
RDB$INTERRELATIONS table can be used to determine if an object
can be dropped or if some other object depends upon its existence
in the database. The following table provides information on the
columns of the RDB$INTERRELATIONS system table.
Data
Column Name Type Summary Description
RDB$OBJECT_NAME char(31) The name of the object
that cannot be dropped or
altered because it is used
by some other entity in the
database.
RDB$SUBOBJECT_NAME char(31) The name of the associated
sub-object that cannot be
dropped or altered because
it is used by another
entity in the database.
RDB$ENTITY_NAME1 char(31) The name of the entity that
depends on the existence
of the object identified
by the RDB$OBJECT_NAME and
RDB$SUBOBJECT_NAME.
RDB$ENTITY_NAME2 char(31) If used, the name of the
entity, together with
RDB$ENTITY_NAME1, that
depends on the existence
of the object specified
in RDB$OBJECT_NAME and
RDB$SUBOBJECT_NAME.
RDB$USAGE char(31) The relationship among
RDB$OBJECT_NAME,
RDB$SUBOBJECT_NAME,
RDB$ENTITY_NAME1, and
RDB$ENTITY_NAME2.
RDB$USAGE contains a short
description.
RDB$FLAGS integer Flags.
RDB$CONSTRAINT_NAME char(31) This column is the name
of a constraint that is
referred to from another
system table. The value in
this column equates to a
value for the same column
in the RDB$CONSTRAINTS
system table.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.17.1 – RDB$FLAGS
Represents flags for RDB$INTERRELATIONS system table.
Bit
Position Description
0 Entity is a module.
1 Object is a module.
2 Entity is a routine.
3 Object is a routine.
4 Entity is a trigger.
5 Object is a trigger.
6 Entity is a constraint.
7 Object is a constraint.
8 Reserved.
9 Reserved.
10 Reserved.
11 Reserved.
12 Reserved.
13 Reserved.
14 Entity is a sequence.
15 Object is a sequence.
3.18 – RDB$MODULES
The RDB$MODULES system table describes a module as defined by
a user. A module can contain a stored procedure or an external
function. Each module has a header, a declaration section, and
a series of routines. The header and declaration section are
defined in RDB$MODULES. (Each routine is defined by an entry in
RDB$ROUTINES.) A row is stored in the RDB$MODULES table for each
module that is defined by a user. The following table provides
information on the columns of the RDB$MODULES system table.
Data
Column Name Type Summary Description
RDB$MODULE_NAME char(31) Name of the module.
RDB$MODULE_OWNER char(31) Owner of the module. If
the module is an invoker
rights module, this column
is set to NULL. Otherwise,
definers username from this
column is used for definers
rights checking.
RDB$MODULE_ID integer Unique identifier assigned
to this module by Oracle
Rdb.
RDB$MODULE_VERSION char(16) Module version and
checksum. Allows runtime
validation of the module
with respect to the
database.
RDB$EXTENSION_PARAMETERS list Encoded information for
of byte module level declarations.
varying
RDB$MODULE_HDR_SOURCE list Source of the module header
of byte as provided by the definer.
varying
RDB$DESCRIPTION list Description of the module.
of byte
varying
RDB$ACCESS_CONTROL list Access Control List (ACL)
of byte to control access to the
varying module. This value can be
NULL.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the module is
created.
RDB$LAST_ALTERED date vms Set when module is altered
by the ALTER, RENAME,
DROP, GRANT and REVOKE
statements.
RDB$MODULE_CREATOR char(31) Creator of this module.
Differentiates between
OWNER and AUTHORIZATION.
RDB$VARIABLE_COUNT integer Number of global variables.
3.19 – RDB$OBJECT_SYNONYMS
The RDB$OBJECT_SYNONYMS system table is created with synonyms
are enabled to record the synonym name, type, and target. The
following table provides information on the columns of the
RDB$OBJECT_SYNONYMS system table.
Data
Column Name Type Summary Description
RDB$CREATED date vms Time and date when synonym
entry was created.
RDB$LAST_ALTERED date vms Time and date when synonym
entry was last altered.
RDB$DESCRIPTION list A user-supplied description
of byte of the synonym.
varying
RDB$EXTENSION_PARAMETERS list Reserved for future use.
of byte
varying
RDB$FLAGS integer Flags.
RDB$OBJECT_TYPE integer The type of synonym.
RDB$SYNONYM_NAME char(31) The synonym to be used
by queries. This name
is unique within the
RDB$OBJECT_SYNONYMS system
table.
RDB$SYNONYM_VALUE char(31) name of the object for
which the synonym is
defined.
RDB$SYNONYM_CREATOR char(31) Creator of the synonym
entry.
3.19.1 – RDB$FLAGS
Represents flags for RDB$OBJECT_SYNONYMS system table.
Bit
Position Description
0 When set, this bit indicates that this synonym
references another synonym.
1 Reserved for future use.
2 Indicates that the synonym was created by RENAME
statement.
3.20 – RDB$PARAMETERS
The RDB$PARAMETERS system table defines the routine interface for
each routine stored in RDB$ROUTINES. Each parameter to a routine
(procedure or function) is described by a row in RDB$PARAMETERS.
The following table provides information on the columns of the
RDB$PARAMETERS system table.
Data
Column Name Type Summary Description
RDB$PARAMETER_NAME char(31) Name of the parameter.
RDB$PARAMETER_SOURCE char(31) Source (domain or table) to
the routine containing the
parameter.
RDB$ROUTINE_ID integer Unique identifier assigned
to the routine containing
this parameter by Oracle
Rdb.
RDB$ORDINAL_POSITION integer Position in parameter
list. Position 0
indicates function result
description.
RDB$PARAMETER_TYPE integer Data type of the parameter.
RDB$PARAMETER_SUB_TYPE integer A value that describes
the data subtype of
RDB$PARAMETER_TYPE as shown
in RDB$FIELD_SUB_TYPE Help
topic.
RDB$PARAMETER_LENGTH integer Length of the parameter.
RDB$PARAMETER_SCALE integer Scale of the data type.
RDB$PARAMETER_SEG_LENGTH integer The length of the segmented
string segment. For date-
time interval fields, the
interval leading field
precision.
RDB$DEFAULT_VALUE2 list Parameter default.
of byte
varying
RDB$FLAGS integer Flags.
RDB$DESCRIPTION list Description of the
of byte parameter.
varying
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.20.1 – RDB$FLAGS
Represents flags for RDB$PARAMETERS system table.
Bit
Position Description
0 IN (read) INOUT (modify).
1 OUT (write) INOUT (modify).
2 Reserved for future use.
3 BY DESCRIPTOR (default is BY REFERENCE).
4 BY VALUE (Bit number 3 is ignored).
5 Reserved for future use.
6 Set if parameter mode is undefined.
If Bits 0 and 1 are both clear, then the parameter is
the RETURN TYPE of a function.
3.21 – RDB$PRIVILEGES
The RDB$PRIVILEGES system table describes the protection for
the database objects. There is one row per grantor, grantee, and
privileges combination per entity in the database.
A row is stored in the RDB$PRIVILEGES table for each user who
grants another user privileges for a database object.
If the privilege for a database object was granted without the
SQL GRANT option, the row of the grantor and grantee is modified.
The privilege change takes effect at commit time of the command.
NOTE
The RDB$PRIVILEGES system table is used only in ANSI
databases.
The following table provides information on the columns of the
RDB$PRIVILEGES system table.
Data
Column Name Type Summary Description
RDB$SUBOBJECT_ID integer The id of the column
or routine for which
protection is defined.
If protection is on a
database, module, table,
or view, this field is
NULL. The value stored in
this column must be unique
within the database.
RDB$OBJECT_ID integer The id of the module,
table, sequence, or view
for which protection is
defined. The column is
NULL if the protection is
defined for the database.
The value stored in this
column must be unique
within the database.
RDB$GRANTOR integer The binary format UIC of
the person who defined or
changed the privileges.
This is usually the UIC of
the person who executed the
protection command.
For an SQL IMPORT
statement, the UIC is
that of the person who
originally defined the
protection for the user;
not necessarily the person
who performed the SQL
IMPORT statement.
RDB$GRANTEE list The binary format of the
of byte UICs of the persons who
varying hold privileges on the
database object.
RDB$PRIV_GRANT integer Specifies the access mask
of privileges that the
grantee has that he can
grant to other users.
RDB$PRIV_NOGRANT integer Specifies the access mask
of privileges that the
grantee has that he can use
himself but cannot give to
other users.
RDB$FLAGS integer Flags.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.21.1 – RDB$FLAGS
Represents flags for RDB$PRIVILEGES system table.
Bit
Position Description
0 Privilege is defined for a module and procedure.
1 The data is related to sequences.
3.22 – RDB$PROFILES
The RDB$PROFILES system table contains information about each
profile, user and role defined for the database. The following
table provides information on the columns of the RDB$PROFILES
system table. See also the related RDB$GRANTED_PROFILES system
table.
Data
Column Name Type Summary Description
RDB$CREATED date vms time and date when profile
entry was created.
RDB$LAST_ALTERED date vms time and date when profile
entry was last altered.
RDB$DESCRIPTION list Comment for this entry.
of byte
varying
RDB$EXTENSION_ list Extra definitions such as
PARAMETERS of byte default transaction.
varying
RDB$SYSTEM_FLAG integer Set to TRUE (1) if this
is a system define role
or user, otherwise it is
set to FALSE (0). When
the RDB$SYSTEM_FLAG is
set these entries may
not be deleted by a DROP
statement.
RDB$FLAGS integer Flags.
RDB$DEFINE_ACCESS integer Reserved for future use.
RDB$CHANGE_ACCESS integer Reserved for future use.
RDB$DELETE_ACCESS integer Reserved for future use.
RDB$PROFILE_ID integer This is a unique identifier
generated for each USER,
PROFILE and ROLE added to
the database.
RDB$PROFILE_TYPE integer Class of profile
information: role (1),
user (3), profile (0).
RDB$PROFILE_NAME char(31) Name of the user, profile
or role. This name
is unique within the
RDB$PROFILES table.
RDB$PROFILE_CREATOR char(31) Creator of entry.
3.22.1 – RDB$FLAGS
Represents flags for RDB$PROFILES system table.
Bit
Position Description
0 The user entry is disabled (ACCOUNT LOCK).
1 Means that the user/role is identified externally.
2 Reserved for future use.
3 This is a system role.
4 Means the user is assigned a profile.
3.23 – RDB$QUERY_OUTLINES
The RDB$QUERY_OUTLINES system table contains query outline
definitions that are used by the optimizer to retrieve known
query outlines prior to optimization. The following table
provides information on the columns of the RDB$QUERY_OUTLINES
system table.
Data
Column Name Type Summary Description
RDB$OUTLINE_NAME char(31) The query outline name.
RDB$BLR_ID char 16 The BLR hashed identifier.
This identifier is
generated by the optimizer
whenever a query outline is
created.
RDB$MODE integer The query mode (MANDATORY
or OPTIONAL).
RDB$FLAGS integer Flags.
RDB$DESCRIPTION list A user-supplied description
of byte of this outline.
varying
RDB$OUTLINE_BLR list The compiled query outline.
of byte
varying
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the outline is
created.
RDB$LAST_ALTERED date vms Reserved for future use.
RDB$OUTLINE_CREATOR char(31) Creator of this outline.
3.23.1 – RDB$FLAGS
Represents flags for RDB$QUERY_OUTLINES system table.
Bit
Position Description
0 This outline has been invalidated by some action, such
as dropping a required table or index.
3.24 – RDB$RELATION_CONSTRAINTS
The RDB$RELATION_CONSTRAINTS system table lists all table-
specific constraints. The following table provides information
on the columns of the RDB$RELATION_CONSTRAINTS system table.
Data
Column Name Type Summary Description
RDB$CONSTRAINT_MATCH_TYPE integer The match type associated
with a referential
integrity table-specific
constraint. This column is
reserved for future use.
The value is always 0.
RDB$CONSTRAINT_NAME char(31) The name of the constraint
defined by the table
specified by RDB$RELATION_
NAME.
The value in this column
equates to a value for the
same column in the
RDB$CONSTRAINTS system
table.
RDB$CONSTRAINT_SOURCE list This text string contains
of byte the source of the
varying constraint from the table
definition.
RDB$CONSTRAINT_TYPE integer The type of table-specific
constraint defined. The
values are shown in the
RDB$CONSTRAINT_TYPE Help
topic.
RDB$ERASE_ACTION integer The type of referential
integrity erase action
specified. This column is
reserved for future use.
The value is always 0.
RDB$FIELD_NAME char(31) The name of the column
for which a column-level,
table-specific constraint
is defined. The column is
blank for a table-level
constraint.
RDB$FLAGS integer Flags.
RDB$MODIFY_ACTION integer The type of referential
integrity modify action
specified. This column is
reserved for future use.
The value is always 0.
RDB$REFD_CONSTRAINT_NAME char(31) The name of the unique or
primary key constraint
referred to by a
referential integrity
foreign key constraint.
If the constraint is
not a referential
integrity constraint or
no referential integrity
constraint was specified,
this column will be
null. Otherwise, the
value in this column
will equate to a value
for the same columns in
the RDB$CONSTRAINTS and
RDB$RELATION_CONSTRAINT_
FLDS system tables.
This column is used to
determine the foreign key
referenced table name and
referenced column names.
RDB$RELATION_NAME char(31) The name of the table
on which the specified
constraint is defined.
The value in this column
equates to a value for
the same column in the
RDB$RELATIONS system table.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.24.1 – RDB$FLAGS
Represents flags for RDB$RELATION_CONSTRAINTS system table.
Bit
Position Description
0 This is SQL standard UNIQUE constraint which allows
unique values and ignores NULL.
3.24.2 – RDB$CONSTRAINT_TYPE
The following table lists the values for the RDB$CONSTRAINT_TYPE
column.
Value Symbol Meaning
1 RDB$K_CON_CONDITION Requires conditional expression
constraint.
2 RDB$K_CON_PRIMARY_KEY Primary key constraint.
3 RDB$K_CON_REFERENTIAL Referential (foreign key)
constraint.
4 RDB$K_CON_UNIQUE Unique constraint.
5 Reserved for future use.
6 RDB$K_CON_NOT_NULL Not null (missing) constraint.
3.25 – RDB$RELATION_CONSTRAINT_FLDS
The RDB$RELATION_CONSTRAINT_FLDS system table lists the columns
that participate in unique, primary, or foreign key declarations
for table-specific constraints.
There is one row for each column that represents all or part of a
unique, primary, or foreign key constraint.
The following table provides information on the columns of the
RDB$RELATION_CONSTRAINT_FLDS system table.
Data
Column Name Type Summary Description
RDB$CONSTRAINT_NAME char(31) The name of a constraint
for which the specified
column participates.
RDB$FIELD_NAME char(31) The name of the column
that is all or part of the
specified constraint. The
value in this column is the
same as that stored in the
RDB$RELATION_FIELDS system
table.
RDB$FIELD_POSITION integer The ordinal position of the
specified column within the
column list that declares
the unique, primary or
foreign key constraint.
For column-level
constraints, there will
always be only one column
in the list. The first
column in the list has
position value 1, the
second has position value
2, and so on.
RDB$FLAGS integer Reserved for future use.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.26 – RDB$RELATION_FIELDS
The RDB$RELATION_FIELDS system table contains one row for each
column in each table. The following table provides information on
the columns of the RDB$RELATION_FIELDS system table.
Data
Column Name Type Summary Description
RDB$RELATION_NAME char(31) The name of the table
that contains the column
represented by this row.
RDB$FIELD_NAME char(31) The name of the column
represented by this row
within the table. Each
RDB$RELATION_FIELDS
row that has the same
RDB$RELATION_NAME must
have a unique RDB$FIELD_
NAME.
RDB$FIELD_SOURCE char(31) The name of the domain
(from the RDB$FIELD_NAME
column within the
RDB$FIELDS table) that
supplies the definition for
this column.
RDB$FIELD_ID integer An identifier that can
be used within the BLR to
name the column represented
by this row. Oracle Rdb
assigns each column an id
that is permanent for as
long as the column exists
within the table.
RDB$FIELD_POSITION integer The ordinal position of the
column represented by this
row, relative to the other
columns in the same table.
RDB$QUERY_NAME char(31) The query name of this
column. RDB$QUERY_NAME can
be null.
RDB$UPDATE_FLAG integer A value that indicates
whether a column can be
updated:
o 0
If column cannot be
updated.
o 1
If column can be
updated.
RDB$QUERY_HEADER list The query header of this
of byte column for use by SQL.
varying Column attributes in
RDB$RELATION_FIELDS take
precedence over RDB$FIELDS.
If the attribute value is
missing in
RDB$RELATION_FIELDS,
SQL uses the value from
RDB$FIELDS.
RDB$DESCRIPTION list A user-supplied description
of byte of the contents of this
varying row.
RDB$VIEW_CONTEXT integer For view tables, this
column identifies the
context variable used to
qualify the view column.
This context variable must
be defined within the row
selection expression that
defines the view. The
context variable appears
in the BLR represented by
the column RDB$VIEW_BLR in
RDB$RELATIONS.
RDB$BASE_FIELD char(31) The local name of the
column used as a component
of a view. The name is
qualified by the context
variable identified in
RDB$VIEW_CONTEXT.
RDB$DEFAULT_VALUE list The default value used by
of byte non-SQL interfaces when
varying no value is specified for
a column during a STORE
clause.
It differs from
RDB$MISSING_VALUE in that
it holds an actual column
value. Column attributes in
RDB$RELATION_FIELDS take
precedence over attributes
in RDB$FIELDS.
If the attribute value is
missing in
RDB$RELATION_FIELDS, the
value from RDB$FIELDS is
used.
RDB$EDIT_STRING varchar(25The edit string to be
used by interactive SQL
when printing the column.
RDB$EDIT_STRING can be
null.
RDB$EXTENSION_PARAMETERS list Reserved for future use.
of byte
varying
RDB$ACCESS_CONTROL list The access control list for
of byte the column.
varying
RDB$DEFAULT_VALUE2 list The BLR for SQL default
of byte value. This value is used
varying when no value is provided
in an SQL INSERT statement.
RDBVMS$SECURITY_AUDIT integer A bit mask that indicates
the privileges that will be
audited for the database,
as specified in the RMU Set
Audit command.
RDBVMS$SECURITY_ALARM integer A bit mask that indicates
the privileges that will
produce alarms for the
database, as specified in
the RMU Set Audit command.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.27 – RDB$RELATIONS
The RDB$RELATIONS system table names all the tables and views
within the database. There is one row for each table or view.
The following table provides information on the columns of the
RDB$RELATIONS system table.
Data
Column Name Type Summary Description
RDB$RELATION_NAME char(31) The name of a table within
the database. Each row
within RBB$RELATIONS must
have a unique RDB$RELATION_
NAME.
RDB$RELATION_ID integer An identification number
used within the BLR to
identify a table.
RDB$STORAGE_ID integer A pointer to the database
logical area where the data
for this table is stored.
RDB$SYSTEM_FLAG integer A value that indicates
whether a table is a system
table or a user-defined
table:
o 0
If a user table.
o 1
If a system table.
RDB$DBKEY_LENGTH integer The length in bytes of the
database key. A database
key for a row in a table
is 8 bytes, and "n times
8 " for a view row, where
"n" is the number of tables
referred to in the view.
If the view does not
contain a dbkey, RDB$DBKEY_
LENGTH is 0. This occurs
when the view uses GROUP
BY, UNION, or returns a
statistical value.
RDB$MAX_VERSION integer The number of the current
version of the table
definition.
This value is matched with
the
RDB$VERSION column in
RDB$FIELD_VERSIONS to
determine the current row
format for the table.
RDB$CARDINALITY bigint The number of rows in the
table (cardinality).
RDB$FLAGS integer Flags.
RDB$VIEW_BLR list The BLR that describes the
of byte row selection expression
varying used to select the rows for
the view. If the table is
not a view, RDB$VIEW_BLR is
missing.
RDB$DESCRIPTION list A user-supplied description
of byte of this table or view.
varying
RDB$VIEW_SOURCE list The user's source text for
of byte the view definition.
varying
RDB$ACCESS_CONTROL list The access control policy
of byte for the table.
varying
RDB$EXTENSION_PARAMETERS list Reserved for future use.
of byte
varying
RDB$CDD_NAME list The fully qualified name of
of byte the dictionary entity upon
varying which the table definition
is based, as specified
in the SQL clause, FROM
PATHNAME.
RDBVMS$SECURITY_AUDIT integer A bit mask that indicates
the privileges that will
be audited for the table,
as specified in the RMU Set
Audit command.
RDBVMS$SECURITY_ALARM integer A bit mask that indicates
the privileges that produce
alarms for the table, as
specified in the RMU Set
Audit command.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDBVMS$SECURITY_AUDIT2 integer Reserved for future use.
RDBVMS$SECURITY_ALARM2 integer Reserved for future use.
RDB$CREATED date vms Set when the table or view
is created (for system
tables it will be the same
as the database creation
timestamp).
RDB$LAST_ALTERED date vms Set when SQL ALTER TABLE,
CREATE/ALTER STORAGE MAP,
ALTER DOMAIN, GRANT, or
REVOKE statements cause
changes to this system
table.
RDB$RELATION_CREATOR char(31) Creator of this system
table.
RDB$ROW_CLUSTER_FACTOR bigint(7) The ratio of the number of
clump changes that occur
when you sequentially read
the rows to the number of
rows in a table. If a row
is fragmented and part of
its fragment is located in
a clump different than the
current one or immediate
next one then it should be
counted as a clump change.
RDB$TYPE_ID integer Reserved for future use.
3.27.1 – RDB$FLAGS
Represents flags for RDB$RELATIONS system table.
Bit
Position Description
0 This table is a view.
1 This table is not compressed.
2 The SQL clause, WITH CHECK OPTION, is used in this view
definition.
3 Indicates a special internal system table.
4 This view is not an ANSI updatable view.
5 Reserved for future use.
6 Reserved for future use.
7 Reserved for future use.
8 Ignore Bit 1 and use RDB$STORAGE_MAPS for compression
information.
9 Set for temporary table.
10 Set for global temporary table; clear for local
temporary table.
11 Set for delete data on commit; clear for preserve data
on commit.
12 Reserved for future use.
13 Set if view or table references a local temporary
table.
14 Special read-only information table.
15 System table has storage map.
16 View references only temporary table.
3.28 – RDB$ROUTINES
The RDB$ROUTINES system table describes each routine that is part
of a stored module or a standalone external routine. An external
routine can either be part of a module or standalone (outside the
context of a module). The following table provides information on
the columns of the RDB$ROUTINES system table.
Data
Column Name Type Summary Description
RDB$ROUTINE_NAME char(31) Name of the routine.
RDB$GENERIC_ROUTINE_NAME char(31) Reserved for future use.
RDB$MODULE_ID integer The identifier of the
module that contains this
routine. If routine is
standalone, value is 0.
RDB$ROUTINE_ID integer Unique identifier assigned
to this routine.
RDB$ROUTINE_VERSION char(16) Routine version and
checksum. Allows runtime
validation of the routine
with respect to the
database.
RDB$PARAMETER_COUNT integer The number of parameters
for this routine.
RDB$MIN_PARAMETER_COUNT integer Minimum number of
parameters for this
routine.
RDB$ROUTINE_BLR list The BLR for this routine.
of byte If the routine is external,
varying this column is set to NULL.
RDB$ROUTINE_SOURCE list Source of the routine as
of byte provided by the definer.
varying
RDB$FLAGS integer Flags.
RDB$SOURCE_LANGUAGE integer The RDB$SOURCE_LANGUAGE
section lists the values
for this column.
RDB$DESCRIPTION list Description of the routine.
of byte
varying
RDB$ACCESS_CONTROL list The access control list
of byte (ACL) to control access to
varying the routine. This value can
be NULL.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$EXTENSION_PARAMETERS list Stores interface
of byte information about the
varying routine. This includes
parameter mappings, the
shareable image name, and
entry point name.
RDB$TYPE_ID integer Reserved for future use.
RDB$ROUTINE_OWNER char(31) Owner of the routine. This
column is only used when
the routine is standalone
(when RDB$MODULE_ID is
0) otherwise the value is
NULL.
RDB$CREATED date vms Set when the routine is
created (the same as the
parent module's creation
timestamp).
RDB$LAST_ALTERED date vms Set when the routine is
modified by the ALTER,
RENAME, GRANT, and REVOKE
statements.
RDB$ROUTINE_CREATOR char(31) Creator of this routine.
Differentiates between
AUTHORIZATION and OWNER.
3.28.1 – RDB$FLAGS
Represents flags for RDB$ROUTINES system table.
Bit
Position Description
0 Routine is a function. (Call returns a result.)
1 Routine is not valid. (Invalidated by a metadata
change.)
2 The function is not deterministic (that is, the routine
is variant). A subsequent invocation of the routine
with identical parameters may return different results.
3 Routine can change the transaction state.
4 Routine is in a secured shareable image.
5 Reserved for future use.
6 Routine is not valid. (Invalidated by a metadata change
to the object upon which this routine depends. This
dependency is a language semantics dependency.)
7 Reserved for future use.
8 External function returns NULL when called with any
NULL parameter.
9 Routine has been analyzed (used for trigger dependency
tracking).
10 Routine inserts rows.
11 Routine modifies rows.
12 Routine deletes rows.
13 Routine selects rows.
14 Routine calls other routines.
15 Reserved for future use.
16 Routine created with USAGE IS LOCAL clause.
17 Reserved for future use.
18 Reserved for future use.
19 Routine is a SYSTEM routine.
20 Routine generated by Oracle Rdb.
Other bits are reserved for future use.
3.28.2 – RDB$SOURCE_LANGUAGE
The following table lists the values for the RDB$SOURCE_LANGUAGE
column.
Value Language
0 Language undefined
1 Ada
2 C
3 COBOL
4 FORTRAN
5 Pascal
6 Reserved for future use.
7 BASIC
8 GENERAL
9 PL/I
10 SQL - default for stored functions and stored procedures
3.29 – RDB$SEQUENCES
The RDB$SEQUENCES system table contains information about each
sequence. The following table provides information on the columns
of the RDB$SEQUENCES system table.
Data
Column Name Type Summary Description
RDB$CREATED date vms Time sequence was created.
RDB$LAST_ALTERED date vms Last time sequence was
altered.
RDB$ACCESS_CONTROL list Access control list for
of byte this sequence.
varying
RDB$DESCRIPTION list Description provided for
of byte this sequence.
varying
RDB$START_VALUE bigint Starting value for the
sequence.
RDB$MINIMUM_SEQUENCE bigint Minimum value for the
sequence.
RDB$MAXIMUM_SEQUENCE bigint Maximum value for the
sequence.
RDB$NEXT_SEQUENCE_ bigint Next value available for
VALUE use for the sequence.
This column is a read
only COMPUTED BY column.
When the sequence is first
defined this column returns
NULL.
RDB$INCREMENT_VALUE integer Increment value for the
sequence. A positive
value indicates an
ascending sequence, and
a negative value indicates
a descending sequence.
RDB$CACHE_SIZE integer Number of sequence numbers
to allocate and hold in
memory. If one (1), then
NOCACHE was specified
and the values will be
allocated one at a time.
RDB$FLAGS integer Flags.
RDB$SEQUENCE_ID integer Unique number assigned to
this sequence object. This
value is for internal use
only.
RDB$SEQUENCE_NAME char(31) Unique name of the
sequence.
RDB$SEQUENCE_CREATOR char(31) Creator of this sequence.
3.29.1 – RDB$FLAGS
Represents flags for RDB$SEQUENCES system table.
Bit
Position Description
0 Sequence will cycle.
1 Sequence is ordered.
2 Sequence is random.
3 Indicates that this is a system sequence and may not be
dropped.
4 Indicates that there was no minimum value specified.
5 Indicates that there was no maximum value specified.
6 Indicates that this is a column IDENTITY sequence.
7 Indicates that this sequence will wait for locks.
8 Indicates that this sequence will not wait for locks.
3.30 – RDB$STORAGE_MAPS
The RDB$STORAGE_MAPS system table contains information about
each storage map. The following table provides information on the
columns of the RDB$STORAGE_MAPS system table.
Data
Column Name Type Summary Description
RDB$MAP_NAME char(31) The name of the storage
map.
RDB$RELATION_NAME char(31) The name of the table
to which the storage map
refers.
RDB$INDEX_NAME char(31) The name of the index
specified in the SQL
clause, PLACEMENT VIA
INDEX, of the storage map.
RDB$FLAGS integer Flags.
RDB$MAP_SOURCE list The user's source text for
of byte the storage map definition.
varying
RDB$DESCRIPTION list A user-supplied description
of byte of the storage map.
varying
RDB$EXTENSION_PARAMETERS list Lists the column names
of byte for vertical record
varying partitioning.
RDB$VERTICAL_PARTITION_ integer A counter that indicates
INDEX the number of vertical
record partitions.
If vertical record
partitioning is used, there
is one RDB$STORAGE_MAPS for
each vertical partition.
RDB$VERTICAL_PARTITION_ char(31) Name of the vertical record
NAME partition.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.30.1 – RDB$FLAGS
Represents flags for RDB$STORAGE_MAPS system table.
Bit
Position Description
0 If map is for a mixed format area.
1 If map enables compression.
2 Partition key cannot be updated.
3 Reserved for future use.
4 User named this partition.
5 Override used for strict partitioning - NO REORGANIZE.
3.31 – RDB$STORAGE_MAP_AREAS
The RDB$STORAGE_MAP_AREAS system table contains information about
each storage area to which a storage map refers. The following
table provides information on the columns of the RDB$STORAGE_MAP_
AREAS system table.
Data
Column Name Type Summary Description
RDB$MAP_NAME char(31) The name of the storage
map.
RDB$AREA_NAME char(31) The name of the storage
area referred to by the
storage map.
RDB$ROOT_DBK char(8) A pointer to the root of
the SORTED index, if it is
a SORTED index.
RDB$ORDINAL_POSITION integer The order of the storage
area represented by this
row in the map.
RDB$STORAGE_ID integer For a table, a pointer
to the database logical
area. For a hashed index,
a pointer to the system
record.
RDB$INDEX_ID integer A pointer to the index
logical area.
RDB$STORAGE_BLR list The BLR that represents
of byte the SQL clause, WITH LIMIT
varying OF, in the storage map
definition.
RDB$DESCRIPTION list Description of this
of byte partition.
varying
RDB$EXTENSION_PARAMETERS list Lists table names and
of byte column names that are
varying referenced by segmented
string storage maps.
RDB$VERTICAL_PARTITION_ integer For LIST storage maps,
INDEX the value indicates the
relationship between areas
of a LIST storage map area
set.
RDB$FLAGS integer Flags.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$PARTITION_NAME char(31) Name of the index or
storage map partition.
3.31.1 – RDB$FLAGS
Represents flags for RDB$STORAGE_MAP_AREAS system table.
Bit
Position Description
0 If Bit 0 is clear, the LIST storage area set is filled
randomly.
If Bit 0 is set, the LIST storage area set is filled
sequentially.
1 User named this partition.
2 BUILD PARTITION is required.
3 Deferred build using NOLOGGING.
3.32 – RDB$SYNONYMS
The RDB$SYNONYMS system table connects the user-visible name of
an object to the stored name of an object. The user-visible name
of an object might be replicated in multiple schemas, whereas
the stored name of an object is unique across all schemas and
catalogs. This table is present only in databases that have the
SQL multischema feature enabled.
Unlike rows in other system tables, the rows in the RDB$SYNONYMS
system table are compressed. The following table provides
information on the columns of the RDB$SYNONYMS system table.
Data
Column Name Type Summary Description
RDB$SCHEMA_ID integer The RDB$CATALOG_SCHEMA_ID
of the schema to which this
object belongs.
RDB$USER_VISIBLE_NAME char(31) The name of an object as it
appears to the user.
RDB$OBJECT_TYPE integer A value that represents
the type of an object, as
follows:
o 8
A constraint.
o 19
A domain (global field).
o 26
An index.
o 31
A system table.
o 36
A view.
o 60
A sequence.
o 67
A storage map.
o 81
A trigger.
o 117
A collating sequence.
o 180
An outline.
o 192
A type.
RDB$STORED_NAME char(31) The name of an object as
is actually stored in the
database.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.33 – RDB$TRIGGERS
The RDB$TRIGGERS system table describes the definition of a
trigger. The following table provides information on the columns
of the RDB$TRIGGERS system table.
Data
Column Name Type Summary Description
RDB$DESCRIPTION list A user-supplied text string
of byte describing the trigger.
varying
RDB$FLAGS integer Flags.
RDB$RELATION_NAME char(31) The name of the table for
which this trigger is
defined. The trigger may
be selected on an update to
the named table (qualified
by the columns described in
the RDB$TRIGGER_FIELD_NAME_
LIST).
This table is used as a
subject table for all
contexts that refer to
it.
RDB$TRIGGER_ACTIONS list A text string containing
of byte all the sets of triggered
varying actions defined for this
trigger. The string
consists of one or more
sets of clumplets, one set
for each triggered action.
RDB$TRIGGER_CONTEXTS integer The context number used
within the triggered action
BLR to map the triggered
action BLR to the current
context of the triggering
update statement.
RDB$TRIGGER_FIELD_NAME_ list A text string composed of
LIST of byte a count field and one or
varying more counted strings. The
count is an unsigned word
that represents the number
of strings in the list.
The counted strings
are ASCIC names that
represent column names.
If the trigger is of event
type UPDATE, it will be
evaluated if one or more of
the specified columns has
been modified.
RDB$TRIGGER_NAME char(31) The name of a trigger.
This name must be a unique
trigger name within the
database.
RDB$TRIGGER_NEW_CONTEXT integer A context number used
within the triggered
action's BLR to refer to
the new row values for the
subject table for an UPDATE
event.
RDB$TRIGGER_OLD_CONTEXT integer A context number used
within the triggered
action's BLR to refer to
the old row values of the
subject table that existed
before an UPDATE event.
RDB$TRIGGER_SOURCE list An optional text string for
of byte the trigger definition. The
varying string is not used by the
database system.
It should reflect the full
definition of the trigger.
This column is used by the
interfaces to display the
trigger definition.
RDB$TRIGGER_TYPE integer The type of trigger, as
defined by the combination
of the trigger action time
and the trigger event.
Action times are BEFORE
and AFTER, and events are
INSERT, DELETE, and UPDATE.
The values that represent
the type of trigger are
shown in the TRIGGER_TYPE_
VAL Help topic.
RDB$SECURITY_CLASS char(20) Reserved for future use.
RDB$CREATED date vms Set when the trigger is
created.
RDB$LAST_ALTERED date vms Set when SQL ALTER TRIGGER
statement is used.
RDB$TRIGGER_CREATOR char(31) Creator of this trigger.
RDB$EXTENSION_PARAMETERS list Extension parameters.
of byte
varying
3.33.1 – RDB$FLAGS
Represents flags for RDB$TRIGGERS system table.
Bit
Position Description
0 Trigger is currently disabled.
1 Invalid due to changed schema.
2 Referenced table was altered.
3.33.2 – TRIGGER_TYPE_VAL
The following table lists the values for the RDB$TRIGGER_TYPE
column of the RDB$TRIGGERS system table and the different types
of triggers they represent.
Numeric
Value Symbolic Value Description
1 RDB$K_BEFORE_STORE Trigger is evaluated before
an INSERT.
2 RDB$K_BEFORE_ERASE Trigger is evaluated before
a DELETE.
3 RDB$K_BEFORE_MODIFY Trigger is evaluated before
an UPDATE.
4 RDB$K_AFTER_STORE Trigger is evaluated after
an INSERT.
5 RDB$K_AFTER_ERASE Trigger is evaluated after
a DELETE.
6 RDB$K_AFTER_MODIFY Trigger is evaluated after
an UPDATE.
3.34 – RDB$VIEW_RELATIONS
The RDB$VIEW_RELATIONS system table lists all the tables that
participate in a given view. There is one row for each table
or view in a view definition. The following table provides
information on the columns of the RDB$VIEW_RELATIONS system
table.
Data
Column Name Type Summary Description
RDB$VIEW_NAME char(31) Names a view or table that
uses another table. The
value of RDB$VIEW_NAME is
normally a view name, but
might also be the name of
a table that includes a
column computed using a
statistical expression.
RDB$RELATION_NAME char(31) The name of a table used to
form the view.
RDB$VIEW_CONTEXT integer An identifier for the
context variable used
to identify a table in
the view. The context
variable appears in the
BLR represented by the
column RDB$VIEW_BLR in
RDB$RELATIONS.
RDB$SECURITY_CLASS char(20) Reserved for future use.
3.35 – RDB$WORKLOAD
The RDB$WORKLOAD system table is an optional system table
(similar to RDB$SYNONYMS and RDB$CATALOG_SCHEMA). It is created
when the database attribute WORKLOAD COLLECTION IS ENABLED is
specified on an SQL CREATE or ALTER DATABASE statement. Once
created, this system table can never be dropped.
The following table provides information on the columns of the
RDB$WORKLOAD system table.
Data
Column Name Type Summary Description
RDB$CREATED date vms Time workload entry was
created.
RDB$LAST_ALTERED date vms Last time statistics were
updated.
RDB$DUPLICITY_FACTOR bigint(7) Value ranges from 1.0 to
table cardinality. Number
of duplicate values for an
interesting column group
(RDB$FIELD_GROUP).
RDB$NULL_FACTOR integer(7) Value ranges from 0.0 to
1.0. This is the proportion
of table rows that have
NULL in one or more columns
of an interesting column
group.
RDB$RELATION_ID integer Base table identifier.
RDB$FLAGS integer Reserved for future use.
RDB$FIELD_GROUP char(31) Contains up to 15 sorted
column identifiers.
RDB$SECURITY_CLASS char(20) Reserved for future use.
4 – 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.
4.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.
4.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
4.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
4.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.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.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
4.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
5 – Sample Databases
To help you learn and test Oracle Rdb features, Oracle Rdb
provides online files that you can execute to create the
following sample databases:
o personnel
The personnel database is a single-file database.
o mf_personnel
The mf_personnel database is a multifile database.
o corporate_data
The corporate_data database is a single-file database that
demonstrates the use of the multischema feature of SQL.
The personnel and mf_personnel databases contain the same
domains, tables, and columns. The corporate_data database
contains slightly different domains, tables, and columns.
You use a single command procedure by Oracle Rdb to create the
various forms of the sample databases.
By default, the command procedure builds the single-file
personnel database using SQL data definitions. Use the following
command to build this database:
$ @RDM$DEMO:PERSONNEL
You can specify parameters when you invoke the command procedure
to create alternative versions of the sample databases. The
following shows the format of the command you enter to create
a sample database:
$ @RDM$DEMO:PERSONNEL interface-lang database-form reposit multischema dir
The parameters specify the following choices:
1. Interface-lang: Enter SQL
Specifies SQL as the data definition language. SQL is the
default.
There are slight differences between the SQL definitions of
PERSONNEL and MF_PERSONNEL.
2. Database-form: Enter S or M.
Specifies the creation of either a single-file (S) or
multifile (M) database. A single-file database is the default.
3. Reposit: Enter CDD or NOCDD.
Specifies whether or not to store data definitions in the
repository. The default is not to store data definitions in
the repository.
4. Multischema: Enter MSDB or omit parameter.
Specifies the creation of a multischema database, CORPORATE_
DATA. Note that for the first three parameters you must
specify the SQL interface, the single-file database form,
and that data definitions not be stored in the repository. The
following example demonstrates how to create the multischema
database:
$ @RDM$DEMO:PERSONNEL SQL S NOCDD MSDB
5. Dir: Enter a directory specification where you want the
database created. If you do not specify this parameter, this
procedure will prompt you for a directory specification. If
you do not provide a directory specification at the prompt,
your default directory will be used.
If you run this procedure in batch and this parameter is
empty, your default directory will be used.
To specify the second, third, fourth, or fifth parameters, you
must include any previous parameters. You can use uppercase or
lowercase to specify the parameters.
For example, to build the the multifile version using SQL data
definitions and storing the data definitions in the repository,
enter the following command:
$ @RDM$DEMO:PERSONNEL SQL M CDD
When you build the single-file personnel database, the command
procedure builds a database called PERSONNEL.RDB, regardless
of the interface language used. When you build the multifile
mf_personnel database, the command procedure builds a database
called MF_PERSONNEL.RDB (plus related storage area files),
regardless of the interface language used.
When you build the multischema corporate_data database, the
command procedure builds a database called CORPORATE_DATA.RDB.
You can use only SQL to build the database.
NOTE
When you create the personnel or mf_personnel database, the
log of the database definition statements used in creating
the database is placed in a file called PERSONNEL.LOG in the
same directory as the database.
When you create the corporate_data database, the log of the
database definition statements used in creating the database
is placed in a file called CORPORATE_DATA.LOG in the same
directory as the database.
The personnel command procedure consists of several smaller
command files, executable files, and data files. These files are
located in the directory RDM$DEMO. You might want to use these
files as models in creating your databases.
For additional information about creating the sample databases,
read the online file ABOUT_SAMPLE_DATABASES.TXT. This file is
located in the Samples directory.
6 – Sample Programs
During installation, SQL installs a number of sample programs in
a variety of languages in a Samples directory.
On OpenVMS, these programs are located in the following Samples
directory:
SQL$SAMPLE
A brief description of the sample programs can be found in the
file about_sql_examples.txt in the Samples directory.
7 – Interactive SQL
Interactive SQL lets you type SQL statements interactively and
see the results immediately. You can use interactive SQL for:
o Defining and maintaining your database
o Learning about SQL
o Testing and SQL applications and developing prototypes
o Performing small-scale data manipulation operations
To run interactive SQL, define a symbol as follows:
$ SQL :== $SQL$
$ SQL
Interactive SQL provides complete online help.
8 – Precompiler
Type HELP SQLPRE72 at the DCL prompt for information on SQL
Precompiler.
9 – Module language
Type HELP SQLMOD72 at the DCL prompt for information on SQL Module
Language.
10 – Linking Programs
When users link programs, they must somehow specify the SQL
interface user library SQL$USER.OLB. If you define the logical
name LNK$LIBRARY as the user library, you save users from having
to explicitly specify that library each time they link their
embedded SQL programs.
To define LNK$LIBRARY, issue this command:
$ DEFINE/SYSTEM/EXECUTIVE/NOLOG LNK$LIBRARY SQL$USER
To make sure LNK$LIBRARY is defined each time the system starts
up, add the previous command to the SYS$STARTUP:RMONSTART.COM
command file.
You must also check to see that the system logical name
LNK$LIBRARY is not already being used. Your site or other
products may have already defined the LNK$LIBRARY logical
name. If so, you should add a numeric suffix to the LNK$LIBRARY
definition you create and to the definition in RMONSTART.COM.
See the Oracle Rdb Installation and Configuration Guide for more
information about adding a suffix.
If you do not define LNK$LIBRARY to specify the SQL user library,
users must explicitly name it when they link programs with
embedded SQL statements. For example:
$ LINK my_prog, SQL$USER/LIBRARY
See the OpenVMS documentation set for more information about the
LINK command.
11 – Logical Names
The following table lists the logical names that SQL recognizes
for special purposes.
Table 1 Summary of SQL Logical Names
Logical Name Function
RDB$CHARACTER_SET Specifies the database default and
national character sets in addition
to the session default, identifier,
literal, and national character sets.
The logical name is used by the
EXPORT and IMPORT statements and
by the SQL precompiler and SQL module
language to allow compatibility of
most recent versions with earlier
versions of Oracle Rdb. This logical
name sets the attributes for the
default connection.
This logical name is also deprecated
and will not be supported in a future
release.
RDB$LIBRARY Specifies a protected library that
you can use to store external routine
images, such as external functions.
Oracle Rdb recommends that you manage
public or sensitive external routine
images using a protected library
that is referenced by the logical
name RDB$LIBRARY. You should define
RDB$LIBRARY as an executive mode
logical name in the system logical
name table. If the external routine
image is located in the protected
area, you can ensure that the desired
image is used by specifying the
RDB$LIBRARY logical name with an
explicit file name in the LOCATION
clause plus the WITH SYSTEM LOGICAL_
NAME TRANSLATION clause in a CREATE
FUNCTION statement.
RDB$RDBSHR_EVENT_FLAGS Can be used to override the four
event flag numbers that are assigned
to RDB$SHARE at startup time by the
LIB$GET_EF system service.
RDB$REMOTE_BUFFER_SIZE Changes the default buffer size,
up to your system quota limits, of
network transfers. This can reduce
the number of network I/O operations
used during large transfers.
Restrictions apply, depending on
which version of Oracle Rdb you are
using.
RDB$REMOTE_MULTIPLEX_OFF Disables the multiplex feature.
RDB$ROUTINES Specifies the location of an external
routine image. If you do not specify
a location clause in a CREATE
FUNCTION, CREATE PROCEDURE, or CREATE
MODULE statement, or if you specify
the DEFAULT LOCATION clause, SQL uses
the RDB$ROUTINES logical name as the
default image location.
RDMS$BIND_OUTLINE_MODE When multiple outlines exist for a
query, this logical name is defined
to select which outline to use.
RDMS$BIND_QG_CPU_TIMEOUT Specifies the amount of CPU time used
to optimize a query for execution.
RDMS$BIND_QG_REC_LIMIT Specifies the number of rows that
SQL fetches before the query governor
stops output.
RDMS$BIND_QG_TIMEOUT Specifies the number of seconds that
SQL spends compiling a query before
the query governor aborts that query.
RDMS$BIND_SEGMENTED_ Allows you to reduce the overhead of
STRING_BUFFER I/O operations at run time when you
are manipulating a segmented string.
RDMS$DEBUG_FLAGS Allows you to examine database access
strategies and the estimated cost of
those strategies when your program
runs.
RDMS$SET_FLAGS Allows you to examine database access
strategies and the estimated cost of
those strategies when your program
runs. See the SET FLAGS statement for
a list of valid keywords that can be
used with this logical name.
RDMS$DIAG_FLAGS When defined to 'L', prevents the
opening of a scrollable list cursor
when the online format of lists is
chained.
RDMS$RTX_SHRMEM_PAGE_CNT Specifies the size of the shared
memory area used to manipulate
server site-bound, external routine
parameter data and control data.
RDMS$USE_ Allows applications to use the
OLD_CONCURRENCY isolation-level behavior that was
in effect for V4.1.
RDMS$USE_OLD_SEGMENTED_ When defined to YES, the default
STRING online format for lists (segmented
strings) is chained.
RDMS$VALIDATE_ROUTINE Controls the validation of routines.
SQL$DATABASE Specifies the database that SQL
declares if you do not explicitly
declare a database.
SQL$DISABLE_CONTEXT Disables the two-phase commit
protocol. Useful for turning off
distributed transactions when
you want to run batch-update
transactions.
SQL$EDIT Specifies the editor that SQL invokes
when you issue the EDIT statement in
interactive SQL.
SQLINI Specifies the command file that SQL
executes when you invoke interactive
SQL.
SYS$CURRENCY Specifies the character that SQL
substitutes for the dollar sign ($)
symbol in an EDIT STRING clause of
a column or domain definition, or
the EDIT USING clause of a SELECT
statement.
SYS$DIGIT_SEP Specifies the character that SQL
substitutes for the comma symbol (,)
in an EDIT STRING clause of a column
or domain definition, or the EDIT
USING clause of a SELECT statement.
SYS$LANGUAGE Specifies the language that SQL uses
for date and time input and displays,
or the EDIT USING clause of a SELECT
statement.
SYS$RADIX_POINT Specifies the character that SQL
substitutes for the decimal point
symbol (.) in an EDIT STRING clause
of a column or domain definition,
or the EDIT USING clause of a SELECT
statement.
The following table shows the valid equivalence names for the
logical name RDB$CHARACTER_SET.
Table 2 Valid Equivalence Names for RDB$CHARACTER_SET Logical
Name
Name of Character
Character Set Set Equivalence Name
MCS DEC_MCS Undefined
Korean and ASCII DEC_KOREAN DEC_HANGUL
Hanyu and ASCII DEC_HANYU DEC_HANYU
Hanzi and ASCII DEC_HANZI DEC_HANZI
Kanji and ASCII DEC_KANJI DEC_KANJI
For more information on these and other logical names, see the
Oracle Rdb7 Guide to Database Performance and Tuning.