1 RDBPRE72 RDBPRE is a preprocessor for BASIC, COBOL, and FORTRAN programs that contain embedded RDO data manipulation statements. Each RDO statement is flagged by the special &RDB& flag. Because the RDBPRE preprocessor submits your source program to the appropriate host language compiler and creates an object file, you should never submit the output from the RDBPRE preprocessor to a host language compiler. When you create the source BASIC, COBOL, or FORTRAN program files, use the RDBPRE preprocessor default input file types. See the subtopic "Default_file_types" for a table that shows the RDBPRE default input file types and the output file types. 2 Default_file_types RDBPRE Preprocessor Default Input Output List Language File Type Source File Type File Type -------- ------------- ---------------- --------- BASIC .RBA .BAS .LIS and .RDBERR.LOG COBOL .RCO .COB .LIS and .RDBERR.LOG FORTRAN .RFO .FOR .LIS and .RDBERR.LOG The .LIS file is generated by the host language compiler, not by the RDBPRE preprocessor. 2 Precompiling Before you invoke the RDBPRE preprocessor, define the following symbol: $ RDBPRE :== $RDBPRE Invoke the preprocessor and specify a file name and the language. The following example shows how to preprocess the file COB_ SAMPLE.RCO: $ RDBPRE INPUT FILE> COB_SAMPLE.RCO/COB You can use RDBPRE and host language compile qualifiers when you precompile a program. 3 /DISTRIBUTED_TRANSACTIONS Starts distributed transactions for those transactions that involve more than one attachment to a database. This qualifier starts the distributed transactions by calling DECdtm system services implicitly. 3 /[NO]INITIALIZE_HANDLES Instructs RDBPRE to generate code that automatically initializes the declared database and request handles generated by RDBPRE. This qualifier has no effect on whether or when handles are cleared in the generated code. It only controls initialization of handles in declarations. The /INITIALIZE_HANDLES qualifier is the default. The /NOINITIALIZE_HANDLES qualifier lets the shareable image and the program that calls it access the database. When you use the /NOINITIALIZE_HANDLES qualifier, any handle you specify in your application program must also be specified in the shareable image. 3 /MESSAGE_MAP This qualifier is for BASIC programs only. The /MESSAGE_MAP qualifier causes the precompiler to generate MAP (RDB$module- name_MAP) declarations instead of DECLARE declarations. If the application will be linked SHAREABLE, the PSECTS that the MAP declarations create will have to be made non-shareable before using this qualifier. 2 Linking You link the object files for RDBPRE RDO programs just as you would link any program object file. Invoke the OpenVMS Linker and specify one or more object file specifications and any link switches you want to use. The following example shows how to link all the modules of the program COB_SAMPLE.RCO: $ LINK COB_SAMPLE, COB_CALL_OTHER, COB_CALLABLE_ERROR_HANDLER 2 Query_governor Using the Oracle Rdb query governor feature, you can specify the following for RDBPRE queries: o The maximum elapsed time that the query optimizer can spend compiling a query o The maximum number of rows delivered during query processing There is no RDBPRE interface for the query governor feature, but this feature can be enabled for RDBPRE queries by defining the RDMS$BIND_QC_REC_LIMIT and RDMS$BIND_QC_TIMEOUT logical names. See the top-level topic "Logical_Names" in the RDO interface help utility for information on how to define and set values for the RDMS$BIND_QC_REC_LIMIT and RDMS$BIND_QC_TIMEOUT logical names. 1 RDO72 The Relational Database Operator (RDO) utility is an interactive interface to Oracle Rdb, a relational database management system. RDO lets you type Oracle Rdb statements interactively and see the results immediately. You can use RDO to: o Define and maintain your database o Learn about Oracle Rdb o Test and prototype Oracle Rdb applications o Perform small-scale data manipulation operations To run RDO, define a symbol and use that symbol to enter the utility. For example: $ RDO :== $RDO $ RDO RDO provides complete online HELP. 2 Release_Notes To see the current Release Notes for Oracle Rdb, type or print the following file: SYS$HELP:RDBvvu[n].RELEASE_NOTES where vv = version u = update n = subsequent update (optional) For example: SYS$HELP:RDB072.RELEASE_NOTES 2 Sample_Databases To help you learn and test Oracle Rdb features, online files are provided 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 provided 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 or RDO. Specifies SQL or RDO as the data definition language. SQL is the default. There are slight differences between the SQL and the RDO 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 the 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 (corporate_ date) 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 multifile version using RDO data definitions and store the data definitions in the repository, enter the following command: $ @RDM$DEMO:PERSONNEL RDO 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. Note that you can use either the SQL or RDO interface to work with the resulting database, regardless of whether the database was created using SQL or RDO command files. 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. Although you can use RDO to work with the database, RDO does not allow you to take advantage of the multischema feature. NOTE When you create the personnel or mf_personnel database, a 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, a 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. 2 Sample_Programs During installation, a number of sample programs are installed in a variety of programming 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. 1 ORACLE_RDB72 Oracle Rdb is a relational database management system for OpenVMS systems. Oracle Rdb databases can be accessed by high-level language programs and many Oracle products. Oracle Expert for Rdb and Oracle Trace for Rdb for OpenVMS can be used to improve the performance of Oracle Rdb databases. You can use the SQL or RDO interface for interactive data definition, learning, and prototyping. 2 Release_Notes To see the current Release Notes for Oracle Rdb, type or print the following file: SYS$HELP:RDBvvu[n].RELEASE_NOTES where vv = version u = update n = subsequent update (optional) For example: SYS$HELP:RDB072.RELEASE_NOTES 2 Sample_Databases To help you learn and test Oracle Rdb features, online files are provided 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 provided 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 or RDO. Specifies SQL or RDO as the data definition language. SQL is the default. There are slight differences between the the SQL and the RDO 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 the 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 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. Note that you can use either the SQL or RDO interface to work with the resulting database, regardless of whether the database was created using SQL or RDO command files. 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. Although you can use RDO to work with the database, RDO does not allow you to take advantage of the multischema feature. NOTE When you create the personnel or mf_personnel database, a 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, a 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. 2 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. 2 How_to_use_help_files Oracle Rdb provides DCL help for the following topics: o SQL-Provides overview information on Oracle's implementation of the SQL interface for Oracle Rdb. o SQLMOD-Provides reference information on SQL Module Language. o SQLPRE-Provides reference information on SQL Precompiler. o RMU-Provides information and syntax on the Oracle RMU utility. o SQL_SERVICES-Provides reference and user information on Oracle SQL/Services. o RDBPRE-Describes the RDBPRE preprocessor. o RDML-Provides general information and syntax for the RDML preprocessor. o RDO-Provides overview information on the RDO utility. For OpenVMS I64, Oracle offers just a multiversion kit. For releases prior to 7.1 on OpenVMS Alpha systems, Oracle offers an Oracle Rdb standard kit and multiversion kit. The names of the DCL help topics are slightly different for the two types of Oracle Rdb kits. Oracle Rdb gives you the ability to read all the DCL help topics for each version of Oracle Rdb that is installed on your system. How you access these DCL help topics depends on whether you want to read the help topic for a standard kit or a multiversion kit. If you are not sure what Oracle Rdb kits are installed on your system, ask your database administrator or system manager. For information on reading DCL topics for standard kits, see the subtopic "Standard_kit." For information on reading DCL topics for multiversion kits, see the subtopic "Multiversion_Kit". 3 Standard_kit The standard kit is available only for OpenVMS Alpha systems that are running Oracle Rdb releases prior to 7.1. To read the DCL help topics that were written for the standard kit for a particular version of Oracle Rdb, you must: 1. Set your default environment to the Oracle Rdb standard kit. For example: $ @SYS$LIBRARY:RDB$SETVER S This step is optional if you are already in the default environment for the standard kit. 2. Type HELP and the name of the topic you want help on, as follows: $ HELP SQL $ HELP RDB $ HELP RMU $ HELP SQL_SERVICES $ HELP RDBPRE $ HELP RDML $ HELP RDO 3 Multiversion_Kit When one or more Oracle Rdb multiversion kits are installed on your system, you can read the DCL help topics that were written for each multiversion kit. Suffixes are added to DCL help topics when multiversion kits for one or more versions of Oracle Rdb are installed on a system. These suffixes are numbers that identify the topic as being written for a particular version of Oracle Rdb. To read the DCL help topics that were written for the multiversion kit for a particular version of Oracle Rdb, you must: 1. Set your default environment to the multiversion kit for the version of Oracle Rdb that you want help on. For example: $ @SYS$LIBRARY:RDB$SETVER 7.1 This step is optional if you are already in the default environment for the multiversion kit for the version of Oracle Rdb for which you want help. 2. Type HELP and the name of the topic you want help on. For example, to read the DCL help topics for the Oracle Rdb V7.1 multiversion kit, type the following: $ HELP SQL72 $ HELP RMU72 $ HELP RDBPRE72 $ HELP RDO72 $ HELP RDML72 2 Oracle_SQL_Services Provides the runtime and development tools for creating Oracle SQL/Services applications. See Oracle SQL/Services help for additional information. 3 Oracle_SQL_Services_Manager The Oracle SQL/Services Manager server management graphical user interface (GUI) lets you manage an Oracle SQL/Services server on an OpenVMS system from any Windows client. The Oracle SQL/Services Manager server runs on Windows NT Intel, Windows 98, and Windows 95. You can use the Oracle SQL/Services Manager server to manage a server configuration on line by establishing a system management connection to a running server and then performing system management functions that operate on the running server or the configuration file or both. You cannot use the Oracle SQL/Services Manager server to perform the off line server management functions of creating a new server configuration or starting a server, nor can you shut down a running server. However, you can modify and restart a running server using the Oracle SQL/Services Manager server. You cannot execute scripts using the Oracle SQL/Services Manager server. The Oracle SQL/Services Manager server requires Oracle SQL/Services V7.0 or higher to run. 2 Oracle_ODBC_Driver The Oracle ODBC [32-bit] Driver for Rdb provides support for Open Database Connectivity (ODBC) connections from Windows XP and Windows 2003 systems to Oracle Rdb databases. See the ODBC help for additional information. 2 RMU For information on Oracle RMU, the Oracle Rdb management utility, type: $ HELP RMU72 If you are working in a multiversion environment, see the HELP topic on How_to_use_help_files for information on using HELP in the multiversion environment. 2 RdbALTER The RdbALTER utility provides a low-level patch capability that allows you to repair corruption on Oracle Rdb database pages. In addition, it allows you to relocate database root, storage area, and snapshot files to other disks or directories. NOTE Oracle Rdb recommends that the RdbALTER utility be used only as a last resort to provide a temporary patch to a corrupt database. Use the RdbALTER utility only after you fully understand the internal data structure, know the information the database should contain, and know the full effects of the command. Because of the power of the RdbALTER utility and the cascading effects it can have, Oracle Rdb recommends that you experiment on a copy of the damaged database before applying the RdbALTER utility to a production database. The help provided with the RdbALTER utility provides complete information on using this utility. To invoke RdbALTER from the command level, enter the following command: $ RMU/ALTER From the RdbALTER> prompt, type HELP. 2 SQL For information on the SQL interface to Oracle Rdb, type: $ HELP SQL72 If you are working in a multiversion environment, see the HELP topic on How_to_use_help_files for information on using HELP in the multiversion environment. 2 Terminology The following table identifies the differences in terminology among the Oracle Rdb standard interface products: DIFFERENCES IN RELATIONAL TERMINOLOGY ___________________________________________________________ ANSI/ISO SQL SQL______________Standard_________RDO, RDML________________ Alias N/A[1] Database handle Authorization Authorization OpenVMS user name identifier identifier Cartesian Cartesian Cross product product product Catalog Catalog N/A[1] (SQL:2003)[2] Column Column Field Column select Column select Record selection expression expression expression Connection Connection N/A[1] (SQL:2003)[2] Isolation level Isolation level Concurrency READ COMMITTED READ COMMITTED (SQL:2003)[2] Isolation level Isolation level N/A[1] REPEATABLE READ REPEATABLE READ (SQL:2003)[2] Isolation level Isolation level Consistency SERIALIZABLE SERIALIZABLE (SQL:2003)[2] Context files N/A[1] N/A[1] Correlation Correlation Context variable name name Database Database[3] Database Domain Domain Global field (SQL:2003)[2] Environment Environment N/A[1] List N/A[1] Segmented string Parameter Parameter Host language variable Predicate Predicate Conditional expression Result table Result table Record stream Row Row Record Schema Schema N/A[1] Session Session N/A[1] (SQL:2003)[2] Storage area N/A[1] Storage area Storage map N/A[1] Storage map Table Table Relation ___________________________________________________________ Footnotes: [1] N/A means that the term is not applicable or not used with the listed product, standard, or system. [2] SQL:2003 is the industry standard. [3] The ANSI/ISO SQL:2003 Standard defines this term in a slightly different way than does the SQL interface of Oracle Rdb. 2 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 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. 4 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. 4 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. 4 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. 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 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 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 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 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 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 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. 4 RDB$FLAGS Represents flags for RDB$CONSTRAINTS system table. Bit Position Description 0 Currently disabled. 1 Currently enabled without validation. 3 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. 4 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 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 4 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 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. 4 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 RDB$PARAMETER_SUB_TYPE For details, see the Help topic RDB$FIELD_SUB_TYPE. 3 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 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. 4 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 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 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 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. 4 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 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. 4 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 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 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. 4 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 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. 4 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 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. 4 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 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. 4 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 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. 4 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 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. 4 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. 4 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 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 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 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. 4 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 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. 4 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. 4 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 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. 4 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 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. 4 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 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. 4 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 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 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 4 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. 4 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 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 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. 2 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. 3 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. 3 Example The following example shows how to query one of the information tables created by the INFO_TABLES.SQL script: SQL> SELECT * FROM RDB$LOGICAL_AREAS WHERE RDB$LOGICAL_AREA_NAME='JOBS'; RDB$LOGICAL_AREA_ID RDB$AREA_ID RDB$RECORD_LENGTH RDB$THRESHOLD1_PERCENT RDB$THRESHOLD2_PERCENT RDB$THRESHOLD3_PERCENT RDB$ORDERED_HASH_OFFSET RDB$RECORD_TYPE RDB$LOGICAL_AREA_NAME 95 7 41 0 0 0 0 1 JOBS 1 row selected 3 RDB$STORAGE_AREAS Displays information about the database storage areas. Data Column Name Type Description RDB$LAST_BACKUP date vms Date of last backup RDB$LAST_INCREMENT_ date vms Date of last restore RESTORE RDB$INITIAL_ALLOCATION integer Allocation RDB$CURRENT_ALLOCATION integer Maximum page number RDB$AREA_ACCESS_MODE integer Access mode RDB$THRESHOLD1_BYTES integer First threshold value RDB$THRESHOLD2_BYTES integer Second threshold value RDB$THRESHOLD3_BYTES integer Third threshold value RDB$PAGE_SIZE integer Page size RDB$AREA_MIN_EXTENT integer Minimum page extent RDB$AREA_MAX_EXTENT integer Maximum page extent RDB$AREA_PERCENT_EXTENT integer Percent growth RDB$EXTEND_COUNT integer Number of times area has been extended RDB$SPAM_INTERVAL integer Number of pages per SPAM page RDB$JOURNAL_SEQUENCE integer AIJ recovery version number RDB$MAX_PAGE_FREE_SPACE integer Maximum free space on new page RDB$MAX_ROW_LENGTH integer Largest segment that can be stored on a page RDB$SEQUENCE integer Sequence number RDB$AREA_ID integer Area ID RDB$LINKED_AREA_ID integer For live storage area, ID of snapshot area. For snapshot area, ID of live storage area RDB$FLAGS integer Flags RDB$THRESHOLD1_PERCENT integer First threshold percent value RDB$THRESHOLD2_PERCENT integer Second threshold percent value RDB$THRESHOLD3_PERCENT integer Third threshold percent value RDB$AREA_FILE char(255) Storage area file name RDB$CACHE_ID integer Row cache ID RDB$AREA_NAME char(31) Storage area name RDB$CLUMP_PAGE_COUNT integer The number of pages per logical area clump (UNIFORM area) RDB$PAGE_LENGTH integer Page length in bytes RDB$PAGES_PER_SPAM_PAGE_ integer Number of data pages per P1 SPAM page + 1 RDB$SPAM_PAGE_VECTOR_ integer SPAM page vector length in LENGTH bytes RDB$PAGE_PAD_LENGTH integer Page padding length in bytes RDB$MAX_SEGMENT_LENGTH integer Largest length to which an existing segment can grow RDB$BACKUP_STATISTICS bigint Backup-specific statistics RDB$SNAPS_ENABLED_TSN bigint TSN of oldest snapshots. If this is a live storage area that has snapshots allowed, this field contains the TSN of the most recent transaction that enabled snapshots for or performed exclusive updates to this area RDB$COMMIT_TSN bigint TSN to which this area is consistent. If the area is marked inconsistent, this is the TSN level to which the area is currently consistent RDB$BACKUP_TSN bigint TSN of last full area backup. If this field contains zero, this area has not been backed up RDB$INCR_BACKUP_TSN bigint TSN of last incremental area backup. If this field contains zero, this area has not been incrementally backed up RDB$LAST_EXTEND date vms Date of last area extend 4 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 3 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 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 3 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 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 3 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 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 3 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 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 3 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 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 3 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 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 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 3 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 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 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 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 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 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 3 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 2 Preprocessors The following preprocessors are available with Oracle Rdb: o Use SQL$PRE (the SQL precompiler) to preprocess programs written in Ada, C, COBOL, FORTRAN, Pascal, and PL/I. o Use SQLMOD (module language) for languages that are not supported by the SQL precompiler. o Use RDBPRE to preprocess programs written in BASIC, COBOL, and FORTRAN. o Use the RDML preprocessor with C and PASCAL programs containing RDML statements. You can define symbols to invoke the preprocessor for your host language. For example: $ SADA :== $SQL$PRE/ADA $ SCC :== $SQL$PRE/CC $ SCOB :== $SQL$PRE/COBOL $ SFOR :== $SQL$PRE/FORTRAN $ SPLI :== $SQL$PRE/PLI $ SPAS :== $SQL$PRE/PASCAL $ RBAS :== $RDBPRE/BASIC $ RCOB :== $RDBPRE/COBOL $ RFOR :== $RDBPRE/FORTRAN $ RDMLC :== $RDML/C $ RDMLPAS :== $RDML/PASCAL 2 RDML For information on the RDML preprocessor, type: $ HELP RDML72 If you are working in a multiversion environment, see the HELP topic on How_to_use_help_files for information on using HELP in the multiversion environment. 2 Errors Explanations for SQL, COSI, RDB, RDO, RDML, RDMS, and Oracle RMU facility messages are provided in online files: o SQL messages: SYS$HELP:SQL$MSG.DOC o Common Operating System Interface (COSI) messages: SYS$HELP:COSI_MSG.DOC o Common Oracle Rdb messages: SYS$HELP:RDB_MSG.DOC o RDO messages: SYS$HELP:RDO_MSG.DOC o RDML messages: SYS$HELP:RDML_MSG.DOC o Oracle Rdb specific (RDMS facility) messages: SYS$HELP:RDMS_MSG.DOC o Oracle RMU messages: SYS$HELP:RMU_MSG.DOC o NSDS messages: SYS$HELP:NSDS$MESSAGES.DOC o RMS messages: RMS error messages can be found in the OpenVMS documentation. There is no online documentation for RMS error messages. o Repository messages: SYS$HELP:CDDPLUS_MSG.DOC o Replication Option for Rdb messages: SYS$HELP:DDAL$MSG.DOC The SQL error message explanations are also included in the interactive SQL Help file under the "Errors" topic. The COSI error message explanations are also included in the Oracle RMU and RDO Help files under the "COSI_ERRORS" topic and in the SQL Help file under the "Errors" topic. The RDO and RDMS error message explanations are also included in the RDO Help file under the "Errors" topic. The Oracle RMU error message explanations are also included under the "RMU_ERRORS" topic in the Oracle RMU help file. The Replication Option for Rdb error message explanations are also included in the interactive SQL Help file under the "Errors" topic of the "REPLICATION_OPTION" topic. The RDB facility messages are no longer included in the RDO Help file because a subsequent installation of Gateway software or Rdb/ELN upgrades the RDB_MSG.DOC file whenever the RDB message image, RDBMSGS.EXE, includes new or revised error messages. Thus, the source for the latest, most up-to-date RDB facility messages is always the RDB_MSG.DOC file in SYS$HELP. The message documentation for all the facilities follows the same format, with messages alphabetized by message name. After the message name and text, the documentation includes an explanation and suggested user action. You can print the online message documentation files for reference. In addition, you can use a search command to see only the message information you need. The following example shows a user receiving the RDMS error message RELEXI, then obtaining an explanation of the RELEXI message from the RDMS online message documentation file: RDO> DELETE FIELD ID_DOM. %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-F-RELEXI, field ID_DOM is used in relation EMPLOYEES -RDMS-F-FLDNOTDEL, field ID_DOM has not been deleted RDO> ROLLBACK RDO> FINISH RDO> EXIT $ $ SEARCH/WINDOW=(0,10) SYS$COMMON:[SYSHLP]RDMS_MSG.DOC RELEXI RELEXI, field is used in relation Explanation: The specified field you attempted to delete is used in a relation. User Action: You should first change the relation to exclude this field, and then delete the field. . . . 2 Hot_Standby The Hot Standby option is a discrete, separately-purchasable Oracle product that provides high-performance database replication. The Hot Standby option prevents your Oracle Rdb database or Oracle CODASYL DBMS database from becoming a single point of failure by physically duplicating the database at a geographically remote-standby site. In the event of a node or cluster failure, the replicated "hot standby" database automatically becomes the master database and takes over application processing. The Hot Standby option automatically performs coordinated database synchronization with high performance and minimal impact on system resources. You need to intervene manually only to start the replication services, and to fail over applications to the hot standby database if a failure occurs. The Hot Standby option does not require specific hardware to operate, and you do not need to make any changes to application coding. You start the replication operation by entering a Replicate command using either the Oracle RMU or DBO utility, as appropriate. These utilities provide syntax and semantics for the following Replicate commands: o Replicate After_Journal Configure-automatically sets up replication attributes for the master and standby databases o Replicate After_Journal Reopen_Output-allows you to view a replication output file o Replicate After_Journal Start-initiates replication operations on the master node or the standby node o Replicate After_Journal Stop-terminates replication operations on the node where you enter the command Once started, the replication operation automates the AIJ backup and rollforward operations. The standby database is created from a backup copy of the master database. As database modifications are applied to the after-image journal for the master database, the Hot Standby option automatically applies the modification over a high-speed network connection to the after- image journal for the standby database. Although, the primary purpose of database replication is to provide a higher degree of availability for mission-critical systems, the standby database can also serve as a read-only database for offline generation of reports. The Hot Standby option is completely compatible with the standard Oracle Rdb and CODASYL DBMS database components. The master and standby databases can be implemented on systems running OpenVMS Alpha or OpenVMS I64, or any combination of these operating systems. For example, you can use the Hot Standby option to replicate a master database running on OpenVMS Alpha to a standby database running on OpenVMS I64. The only requirement is that the network connection is common to both operating system platforms. For additional information, see the Hot_Standby Help topic in the command line help for RMU. Refer to the Oracle Rdb7 and Oracle CODASYL DBMS: Guide to Hot Standby Databases for information about the Hot Standby option.