OpenVMS SharkOpenVMS Notes: RMS, RDB and Oracle-Rdb

  1. The information presented here is intended for educational use by OpenVMS technologists
  2. The information presented here is provided free of charge, as-is, with no warranty of any kind
Edit: 2018-09-12 (fixed a few typos)

RMS for OpenVMS

  • RMS (Record Management Services a.k.a. Record Management System but not "Richard Mathew Stalman") is file storage technology built-into OpenVMS and VMS
  • Although RMS is not as cool as SQL-friendly "ACID compliant" relational database, it is small, very fast, and free
  • Popular RMS file formats on OpenVMS include:
    • Stream (non record)
      • just a stream of bytes (more like UNIX text files and/or Windows Notepad)
    • Sequential (record)
      • Traditional OpenVMS text files are nothing more than a sequence of variable-length string records, each one representing a line
    • Relative (record)
      • access is by record number
    • Indexed (record)
      • supports up to 255 keys (a.k.a. indexes) although you seldom see applications employing more than 4
      • RMS indexed files are classified as ISAM (Indexed Sequential Access Method) technology and many OpenVMS languages, like VMS-BASIC and VMS-COBOL, provide native support for it. (This technology is long from dead. The default storage engine for MySQL table creation is MyISAM which is a mature and stable ISAM technology)
      • I have encountered a number of computer professionals people who mistakenly think that RMS is only a "Flat File" Technology. While not inherently relational (although external software can make it so), it can be correctly called hierarchical
  • Many OpenVMS utilities are "RMS aware". For example, if you use the $TYPE command on an indexed RMS file, you'll generate an output listing in primary key order (hopefully you stored all the fields in string format). Using the same command on a sequential RMS file will also produce data in sequential order
  • OpenVMS contains many built-in RMS support commands like these:
    • $convert/create
    • $analyze/rms/fdl (fdl = file description language)
    • $edit/fdl               (which contains a menu-driven optimizer)
  • Some third party tools to provide SQL access to RMS:
    • Google this phrase: "Oracle-Rdb Transparent Gateway for RMS"
    • www.CONNX.com
    • www.Attunity.com (I use this product to connect the front end of Microsoft-Access to OpenVMS/RMS via ODBC)

RMS Links

Why Change to Relational?

Question: If RMS-Indexed (ISAM) technology is so fast and cheap, then why consider changing?
Answer: Evolution

  1. The first computers had no operating system software so programmers needed to write their own I/O routines every time they built a new application. Building computer hardware with more memory made it possible to load both "operating system software" and "application software" thus saving programmers valuable time by not having to write their own I/O routines. The burden of writing I/O shifted to hardware manufacturers who also exploited new technologies like "error detection and error correction".

    p.s. The first commercial computer I worked on in 1977 had no operating system (although operating systems had been available for quite some time). It was a 32-bit Interdata Model 70 (an instruction set heavily influenced by the IBM 360) was was used to record long-distance billing information provided by a Northern Electric (number 4) toll-tandem crossbar switch, then wrote that information to a Hewlett-Packard 7970C 9-track tape deck.
     
  2. Operating systems were first based upon tapes (TOS) and then later upon disks (DOS). Early disk operating systems only supported sequential-stream, sequential-block and relative-block operations. If you wanted indexed-access to your relative-block data, you needed to implement your own indexing schemes.

    p.s. OS developers produced standardized data formats so tapes could be read by many other vendors; this did not happen with disks until the advent of ubiquitous removable disk media such as floppy disks and CD-ROMs along with inexpensive IDE hard disk drives.
     
  3. The pressure of COBOL standards forced prudent OS vendors to build in index-block support. This is one reason why Indexed-RMS was chosen to be built directly into the first version of VMS in 1977.
     
  4. In the 1960s and 1970s Edgar F. Codd made huge contributions to the field of relational databases. His employer, IBM, was slow to develop and market Codd's ideas but Larry Ellison of Oracle Corporation was not and so kicked off the age of transactional computing. At this time, Digital Equipment Corporation started work on RDB (relational data base) which first appeared in 1984 for VMS on VAX.
     
  5. This software evolution continues today with modern databases now being able to store everything from searchable electronic documents, images, music, or anything else you can think of. This new technology is known by many names including "Enterprise Content Management", "Content Management System", etc.
Feature Relational RMS
relational tables? built-in maybe 1
SQL? built-in no 2
can add/drop indexes without changes to the application software? yes no
can modify field sizes and data types (within reason) without changes to the application software? yes no
possible to place all desired constraints (rules) into the database rather than the application software?
(thus protecting the database from access via 3rd party software like ODBC etc.)
yes no
possible to trigger software when just accessing the database?
(thus protecting the database from access via 3rd party software like ODBC etc.)
yes no
can be made transaction safe? yes maybe 3 5
can keep a running log of all "before/after" changes? yes maybe 3 4
maintains a limited online-log of all "before" changes? yes 5 difficult 3
possible to do file maintenance (like rebuilding indexes) on the fly to support a true 7x24 operation? yes 6 no

Subscript Notes:

  1. With software anything is possible. However, relational databases allow relational rules to be built-into the database while ISAM technologies require the accessing application software to support/enforce this. But remember that relational databases offer SQL access for ad-hoc queries. Since a human could potentially do anything he wants, the database is now required to protect itself.
     
  2. SQL access to RMS is only possible with third-party tools
     
  3. Almost all corporate financial software of the 1980's used ISAM technology so these features are possible but require a great deal of work. On top of that, all future programmers coming into an existing project need to be as diligent as the original programmers while never making any mistakes. This last statement is possible but only at great expense
     
  4. The "RMS Journaling" option provides cool logging features but requires a license (RMS is free, RMS Journaling is not)
     
  5. IIRC, it was possible to make RMS transaction safe but this required another layered product called ACMS (Application Control Monitoring System)
     
  6. Relational databases are "relational in both time and space". Consider the following example from Oracle 9i
    1. Alice and Bob are bank employees accessing a database consisting of one million accounts.
    2. At 9:00, Alice begins generating a financial report which will take 2 minutes to execute (caveat: must be a read-only select)
    3. At 9:01, Alice's report generator is halfway through the database.
    4. Now, Bob performs a transaction moving $25 from the first account to the last account then commits his changes before Alice's report is finished.
    5. In ISAM technology the $25 would be counted twice in Alice's report. Once on the first record and a second time on the last.
    6. In a relational technology (with transaction support) Alice will not see any of Bob's changes because:
      • all of Bob's before/after information is stored in the rollback segments (UNDO tables) along with the transaction time.
      • When fetching Alice's report data, the engine will use the start time of her transactional query as a key while watching the UNDO tables to ensure she sees the data as it was when she submitted the query at 9:00.
      • It's as if Alice sees a "snapshot" of the whole database taken at 9:00, and "snapshot" is exactly what the file is called in Oracle-Rdb
         
  7. SQL compliant databases first look at your SQL Statement, then look at the current database structure before building the "execution plan" to carry your desires (experts have told me that there are. on average, 20 different ways to get at your data). If you decide to rebuild an index during an online maintenance operation, the database will produce a different execution plan in order to carry out your request. Access to the data might be a little slower (because the index is unavailable) but it will never be blocked as long as the database is still running. (note that clever forms of index maintenance can even prevent this problem; for example, just create the new index before deleting the old one)
     
  8. If none of these features matter to you, then stick with RMS-Indexed (ISAM) databases

Oracle-Rdb for OpenVMS

A really neat product which all VMS developers should try at least once.

  • Oracle-Rdb is a fully relational database product which is a desirable (although expensive) successor to RMS
  • Rdb/VMS was created by DEC (Digital Equipment Corporation) in 1984 and was part of the VMS Information Architecture. Rdb/VMS was intended to be used as a data access method by DATATRIEVE, RALLY, and TEAMDATA as well as applications written in high-level DEC languages like COBOL, FORTRAN, BASIC, Pascal, and C/C++
  • In 1994 DEC sold their Rdb division to Oracle where it was rebranded Oracle-Rdb
  • Contrary to popular belief, Oracle is still enhancing and developing this product. Click here for Rdb Version Info and release notes
    Caveat:
    • searching for the phrases "VMS", "OpenVMS", or "Rdb" on www.oracle.com  will return very little Rdb information
    • searching for these same phrases at "The Oracle Store" web-site returns even less
    • when you discuss "Oracle-Rdb" licensing with Oracle sales reps, they will frequently be quoting facts associated with "Oracle-Database" (e.g. Oracle 9i, Oracle 10g, Oracle 11g) rather than Oracle-Rdb so good luck with that
  • Oracle-Database (eg. Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g) is a different product line than Oracle-Rdb but many GUI tools used with Oracle-Database now also work with Oracle-Rdb
  • Oracle-Rdb currently runs on OpenVMS for VAX, OpenVMS for Alpha, OpenVMS for Itanium
  • There was a version of Oracle-Rdb for Windows-NT called Rdb8 but this seems to have dropped off the face of the Earth and I can't find anyone to tell me what happened or why
  • You can access Oracle-Rdb directly from high level languages, indirectly from ODBC, or interactively via SQL and/or RDO.
  • The Relational Database Operator (RDO) utility is the original interactive interface to Oracle-Rdb. RDO lets you type Oracle-Rdb statements interactively and see the results immediately. (Think "SQL" but with a different command set)
  • SQL is an industry standard interface for accessing relational databases. The SQL interface included with Oracle-Rdb provides full access to Oracle-Rdb databases. With Rdb's SQL interface, users can define, update, and query relational databases. SQL provides the following environments for issuing SQL statements:
    • An interactive SQL utility
    • A precompiler that lets users embed SQL statements in programs written in: Ada, C, COBOL, FORTRAN, Pascal, or PL/I
    • SQL module language modules containing SQL statements that can be called by any language
    • A "Dynamic SQL" interface that process SQL statements generated at program run-time
  • The SQL module language (SQLMOD) and SQL module processor allow procedures that contain SQL statements to be called from any host language, including those not supported by the SQL precompiler. Click here for more info: Oracle Rdb7 Guide to SQL Programming
  • "RDO vs. SQL" command comparison
    Rdb/RDO Commands Rdb/SQL (equivalent command)
    $RDO $SQL
    AT END IF SQLCODE=SQLCODE_EOS
    DECLARE TRANSACTION SET TRANSACTION
    FETCH/GET FETCH INTO
    INVOKE DATABASE DECLARE DATABASE
    ON ERROR IF SQLCODE<SQLCODE_SUCCESS
    MODIFY UPDATE
    REMOVE DELETE
    START STREAM DECLARE/OPEN CURSOR
    STORE INSERT
  • To the best of my knowledge there are no OpenVMS languages with native support for Rdb. However, there are several popular methods for interfacing Rdb to a high level language:
    1. SQLMOD (newer method 1):
      • Note: this works with all so-called DEC languages (including BASIC)
      • A static SQL technique (although variable passing to SQLMOD can make it appear pseudo-dynamic)
      • A supplied SQL MODULE LANGUAGE compiler compiles your SQL statements into an object file of callable routines.
      • You call these routines from your high level language
      • Your high level language is connected to these routines by the OpenVMS linker
        DCL Command Yields Notes
        $SQL$MOD program_bas.sqlmod  program_bas.obj your SQL subroutines are located here
        $basic program.bas  program.obj compile the way you normally would
        $link program.obj, program_bas.obj  program.exe include the new object file to your link command
      • click here for more info: Oracle Rdb7 Guide to SQL Programming
    2. Embedded SQL (newer method 2):
      • Note: Only works with 6 ANSI standard languages (Ada, C, COBOL, FORTRAN, Pascal, and PL/I)
      • A static SQL technique (although variable passing to SQLMOD can make it appear pseudo-dynamic)
      • a supplied SQL pre-compiler can be used to convert special embedded SQL statements found in your source code into the required source code like so:
        DCL Command Yields Notes
        $SQL$PRE/cobol program.sco    program.cob   - Your COBOL source code must be maintained in ".sco" format
        - This new ".cob" file is expendable
        $cobol program.bas  program.obj   compile the way you normally would
        $link program.obj  program.exe   link the way you normally would
      • click here for more info: Oracle Rdb7 Guide to SQL Programming
    3. Dynamic SQL
      • obviously a "dynamic SQL" technique (introduced with SQL-92)
      • SQL text statements are parsed at run-time then passed to Oracle-Rdb via the SQLDA (SQL Descriptor Area)
    4. Embedded RDO (older method):
      • a supplied pre-compiler (see RDML below) can be used to convert special embedded RDO statements (each line is prefixed with &RDB& ) in your source code into the required source code like so:
        DCL Command Yields Notes
        $rdbpre/basic program.rba    program.bas   - Your BASIC source code must be maintained in ".rba" format
        - This new ".bas" file is expendable
        $basic program.bas  program.obj   compile the way you normally would
        $link program.obj  program.exe   link the way you normally would
  • The Relational Data Manipulation Language (RDML) is composed of statements that can be embedded in BASIC, COBOL, and FORTRAN programs. These programs can be processed by the RDML pre-processor, which converts the RDML statements into a series of equivalent DSRI calls to the database. Following a successful pre-compilation, the programmer can submit the resulting source code to the host language compiler.
    Symbol Definition For Various Languages
    Note: make sure you first execute DCL command @sys$library:RDB$SETVER RESET" (see below )
    $ 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
  • Oracle RMU, the Oracle-Rdb management utility, lets database administrators manage Oracle-Rdb databases. Oracle RMU commands are executed at the operating system prompt. Oracle RMU command syntax follows the rules and conventions of the DIGITAL Command Language (DCL).

Oracle Rdb Installation (Quick Use)

Caveat: the following information came from tests on OpenVMS-7.3-2 Alpha. I can only assume it would be the same on OpenVMS-8-4.

  • Install Oracle-Rdb from a terminal emulator and capture all the text to a file. Producing a hardcopy now may be helpful later on.
  • Navigate to directory sys$help and read all of text file RDB071.INSTALL_GUIDE. You might find it helpful to produce a hardcopy but be prepared for 3/4 inch of printed fanfold when printed at a density of 88 lines per page. Alternatively you might wish to download an HTML or PDF version from here (you need to be a registered OTN member but membership is free)
  • On system start up, execute something similar to the following two commands:
    • @sys$startup:RMONSTART71.COM
      • this command will start the process RDMS_MONITOR71 (nothing else will work unless this process is running)
    • @sys$library:RDB$SETVER.COM 7.1 /SYSTEM
      • this command will produce "system logicals" to support Rdb version 7.1 including the associated SQL commands
      • this is the first of two scripts necessary to support DCL command SQL$ (yes, the dollar sign must be after the command)
  • On user login, execute the following command:
    • @sys$library:RDB$SETVER RESET
      • this command will produce DCL symbols to allow access the default installed version of Rdb
        (each process can support different versions of Rdb at the same time but it is not desirable to define these symbols yourself; always use this script)
      • this is the first of two scripts necessary to support DCL command SQL$ (yes, the dollar sign must be after the command)
  • Directories of interest:
    sys$help RDB071.INSTALL_GUIDE
    RDB071A.RELEASE_NOTES ... RDB071D.RELEASE_NOTES
    sys$examples various things
    sys$manager control + shutdown scripts
    sys$startup startup scripts
    sys$library version control scripts
  • Don't forget to add @sys$manager:RMONSTOP71.COM to file sys$manager:SYSHUTDWN.COM
  • Building the demo database
    • if you're the only one on the system playing with Rdb then enter the following commands:
          $set def sys$help
          $set def [.examples.rdb71]
          $@PERSONNEL.COM (to build the test database in either "single file" mode or "multi-file" mode)
    • if you're not the only one on the system playing with Rdb then copy the example files to an [.rdb71] subdirectory under your sys$login directory (you'll need ~ 20k blocks) then execute @PERSONNEL.COM in your subdirectory to create your own personal demo database to hammer anyway you want.
  • Building your own demo database (this is a very simplistic first example with SQL$ statements in bold)
$!============================================================
$! title  : rdb_demo1_create_db.com
$! author : Neil Rieck
$! created: 2004-09-11
$! NSR 051231 tweaked for public display
$!============================================================
$       env_verify = f$environment("verify_procedure")  ! remember verify_state
$       set nover                                       ! now force verify: OFF
$       say :== write sys$output                        !
$       ask :== inquire/nopunct                         !
$       bel[0,8]==7                                     !
$       on warn then goto warning_handler               ! don't take any chances
$       set on                                          !
$       say ""                                          !
$       say "rdb_demo1_create_db.com"                   !
$       say "======================="                   !
$       temp = f$trnlnm("csmis$dat")                    ! does this logical exist?
$       if temp .eqs. ""                                ! nope
$       then                                            !
$               temp2 = f$environment("DEFAULT")        ! get our current default directory
$               set ver                                 !
$               def/sys/log csmis$dat 'temp2'           ! so we'll create the file in current directory
$               setnover                                !
$       endif                                           !
$       temp = f$search("csmis$dat:rdb_demo1_db.rdb")   !
$       if temp .nes. ""                                !
$       then
$               say "-w- warning: database 'csmis$dat:rdb_demo1_db.rdb' already exists"
$               say "    and continuing will create a new database over the old one"
$               ask choice "Continue? (y/N) "           !
$               choice = f$extract(0,1,choice)          !
$               if choice .nes. "Y" then goto sortie1   ! don't take any chances
$       endif
$       set ver		                                ! watch the SQL$ statements
$sql$                   ! this will only work after "@sys$library:RDBVMS_SETVER.COM reset"
!
!       WARNING: if the database already exists, this command will create another new one
!
create database filename csmis$dat:rdb_demo1_db.rdb
        number of users 500             ! number of connections (e.g. 125 users x 4 connections each)
        number of cluster nodes 1;      ! if not in an OpenVMS cluster then set to 1 to improve performance
!
!       domains can be used to provide a uniform column definition between tables
!       eg. alter table customer add column tel3 standard_tel after column tel2;
!
create domain   standard_address        char(25);
create domain   standard_city           char(20);
create domain   standard_name           char(30);
create domain   standard_tel            char(10);
commit;
!
create table customer(
        name    char(30),
        address char(25),
        city    char(20),
        tel1    char(10),
        tel2    char(10));
commit;
!
alter table customer add column postal char(6) after column city;
alter table customer add column province char(15) after column city;
commit;
!
insert into customer values(
        'Neil Rieck',
        '20 Water St N',
        'Kitchener',
        'Ontario',
        'N2H5A5',
        '5195551212',
        '');
insert into customer values(
        'Steve Kennel',
        '20 Water St N',
        'Kitchener',
        'Ontario',
        'N2H5A5',
        '5195551212',
        '');
insert into customer values(
        'Dave McNeil',
        '140 Bayfield St',
        'Barrie',
        'Ontario',
        'L4M3B1',
        '7055551212',
        '');
insert into customer(
        name,address,city,province,postal,tel1,tel2)
        values(
        'Karim Macklai',
        '220 Simcoe St',
        'Toronto',
        'Ontario',
        'M5T1T4',
        '4165551212',
        '');
commit;
!
exit                                    ! exit from SQL$
$sortie1:                               !
$       set nover                       !
$       goto sortie2                    !
$warning_handler:                       !
$error_handler:                         !
$       set noon                        !
$       set nover                       !
$       say "-e- did you execute the script '@sys$library:RDBVMS_SETVER.COM reset' ?"
$sortie2:                               !
$       if env_verify .eqs. "TRUE" then set verify
$       exit                            ! adios

Oracle Rdb Links

  • http://h41379.www4.hpe.com/partners/oracle/
  • Oracle Rdb
  • Over 40 example files including demos of how to access 'Oracle-Rdb' from 'HP-BASIC for OpenVMS BASIC' using SQLMOD
  • Oracle FAQ
  • An unsupported demonstration version of RDB is available for Windows-NT on Intel to customers licensed to use RDB on OpenVMS (because of a BLISS licensing problem this product must never be sold or used on a production platform)
    • Oracle Rdb Workbench for Windows NT/Intel
    • For full demonstration effects you should probably locate an old copy of "Oracle Enterprise Manager v2.0.4" (a.k.a. OEM) which is now only available on an old Oracle-8i (Personal) CD-ROM. If you have lost your old ROMs, you might need to purchase an old Oracle-8i manual but not all of these contained Oracle binaries. Alternatively, I suspect that OEM on the Oracle-9i (Personal) three CD-ROMs may also work provided you download the RDB plug-in for it.

"Oracle Database" Links

Note: Be sure not to confuse "Oracle-Rdb" with "Oracle Database" (a.k.a. Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g, Oracle 12c, ...). They are two different product lines.

Product Note Description
Oracle 8   Just a very cool SQL-compliant database
Oracle 8i i=internet Oracle 8 with added Java support for easy interfacing to the internet (built-in callable Internet package s/w)
Oracle 9i i=internet Oracle 8i with many more features including auto-tuning
Oracle 10g g=grid Oracle 9i with many more features including support for GRID computing
Oracle 11g g=grid Oracle 10g with many more features including automated self-management and testing; XML support; compression
Oracle 12c c=cloud Oracle 11g with extensions for cloud computing

Oracle-Rdb Licensing (as I understand it)

  • you may download "Oracle-Rdb" for free and use it for free provided you are only working on a first prototype (or evaluating Rdb)
  • as soon as the first prototype goes into production...
    • the developer must acquire a developer's license in order to maintain the production software or further develop it.
      • Note: The definition of developer seems blurred when viewed from otn.oracle.com (the Oracle Technology Network). Here they seem to make a distinction between DEVELOP and TEST licenses. I get the feeling that if you are only a developer, then you don't need to buy a license. But this is a gray area which can only be solved by a call to Oracle 
    • the user of the production software must purchase an Oracle-Rdb "Enterprise license" or "named user license"
  • License Types:
    • named user (although these must be purchased in minimum sizes that usually number 25 or 50)
    • per processor
      • as of 2004-09 licensing is still bizarre. "Oracle-Rdb" is only available in the "Enterprise Edition" (while "Oracle Database" is sold in different prices ranges with names like: "Personal Edition", "Standard Edition", and "Enterprise Edition"). The price is based upon "total CPU count" and it doesn't matter if the CPUs are in a single host (SMP); or spread across multiple hosts in an OpenVMS Cluster; or both. This means that an old 8-host "Alpha Server 1000" cluster is charged the same as a new "8-CPU Alpha Server GS1280".
        Since the majority of the Rdb market is for OpenVMS, you would think that Oracle-Rdb Licenses would adopt the three tier naming convention used by the hardware manufacturer. (DS = Departmental. ES=Enterprise. GS=Global)
      • Oracle is a rich company but they don't seem to realize that Microsoft got really rich by selling MS-DOS at $99.00 per copy. IMHO, Oracle should drop the prices of all their products and make up the difference in increased volume. This would certainly slow (if not stop) the migration to open source alternatives like MySQL.
      • Oracle Price Lists

"RMS to Rdb" Migration Tips

Link: Programming examples of how to access 'Oracle Rdb' from 'HP-BASIC for OpenVMS' using SQLMOD

  • Primary Key
    • In RMS indexed files, key#0 is always the "primary key" while others (if they exist) are called alternate keys. A "primary key" may include the "duplicates" qualifier but may never include the "changes" qualifier. Alternate keys may use any qualifier.
    • In SQL, a "primary key" must be unique and is only a constraint (rule) to enforce it. Also, it is not an index as is the case with RMS indexed files. When testing the "primary key" constraint during row insertion, SQL will search the whole table which will slow down the system. One way to get around this is to create an INDEX on the column(s) associated with the constraint so SQL searches the sorted INDEX rather than sequentially searching the whole TABLE.
      • caveat: experiments with MySQL-5.5.25 in 2014 tell me that not all SQL implementations are the same. For example, creating a simple 5-column table with one UNIQUE constraint automatically adds an index to that column. Makes sense to me since insertions would be faster
  • no RFA in Oracle-Rdb
    • In RMS, every record has a unique RFA (Record File Address) which will never change until the file is tuned via $CONVERT/CREATE. Many BASIC applications might search through an RMS index file like so:
      FIND #31, KEY# NXEQ target$            ! set the key of reference
      label_1:
      GET #31,REGARDLESS                     ! read a record but don't apply a lock
      test the data, then...
          goto label_1                       ! read more
              or...
          goto label_2                       ! exit
              or...
          modify the current record like so:
              my_rfa = GETRFA(31)            ! determine the RFA that we've stopped on
              GET #42, RFA my_RFA            ! place a lock on the record using another channel
              change the record data         !
              UPDATE #42                     ! write the change back to disk using another channel
              then...
                  goto lable_1               ! read more
                      or...
                  fall thru to label_2       ! exit
      label_2:
    • In Oracle-Rdb we've got something similar to RFA called ROWID (a.k.a. DBKEY) but this data is not always available to us, especially in cursors. If you occasionally need to use logic similar to the RFA in the RMS example above, create your tables with a "primary key" based upon a SEQUENCE, then use this data as a pseudo RFA.
      • Be sure to create an individual SEQUENCE for every TABLE requiring one.
      • Like an RFA in RMS, be sure to never user this column as a FOREIGN KEY in another TABLE. There are times during maintenance when you might want to reset the SEQUENCE and repopulate the column with new data.
  • CHAR vs. VARCHAR (a.k.a. the temptation to use something new)
    • If you are absolutely certain that a field length will never change length (eg. SEX: M/F), then it is always better to use CHAR(1) rather than VARCHAR(1). CHAR(1) will only require 1 byte of storage while VARCHAR(1) will always require a minimum of 2 bytes when null and 3 bytes when not null. This could have an enormous impact on a table with a 100 million rows.
    • If string data is going to be indexed, a CHAR-based index can be searched much more efficiently than one based upon a VARCHAR. In fact, index-only searches are not always possible with VARCHAR which means that the associated records may need to be inspected in order to satisfy some kinds of SQL queries. Index-only searches will always be more efficient.

(Recommended) RDB Books

  • The Minimum You Need to Know to Be an OpenVMS Application Developer
    • Published 2006 by Logikal Solutions. ISBN 0-9970866-0-7
    • Over 800 pages with a CD-ROM
    • Author: Roland Hughes
    • Covers: DCL, BASIC, FORTRAN, COBOL, C, C++
      Interfacing to: FMS, RMS, CDD, CMS, MMS, Message Files, VMS-Mail, VMS-Phone, MySQL, Oracle-Rdb
      (see the book cover at the URL above for the product matrix)
    • My 2-cents:
      • highly recommended for OpenVMS programmers (especially those new to OpenVMS who need a good bootstrap).
      • One copy of this book should be purchased as an "office resource" for every location where OpenVMS developers work. (this is what I have done in my shop although I must admit that we are only writing OpenVMS code in three locations)
      • The author has pre-printed 1,000 copies of this book but will probably not publish any other OpenVMS books until these are sold. This book is intended to be a prerequisite for future publications.
      • DO NOT begin any new database projects without first reading chapter 13 (MySQL) and chapter 14 (Oracle-Rdb)
    • Chapter Titles:
      1. Fundamentals of OpenVMS
      2. DCL and Utilities We Need
      3. DEC BASIC
      4. FMS (Forms Management System)
      5. CMS (Code Management System)
      6. CDD (Common Data Dictionary)
      7. Object and Text Libraries
      8. MMS (Module Management System)
      9. Message Utility, Mail and Phone
      10. FORTRAN
      11. COBOL
      12. C/C++
      13. MySQL (2014 Note: I recently used information from this chapter to work with Mark Berrymans' MariaDB)
      14. Oracle-Rdb
      15. Ruminations and Observations (invaluable personal observations on the current state of IT)
        1. Overview
        2. What Do You Do?
        3. Keep Your Eye on the Sparrow
        4. Have You Ever Wondered Why Y2K Happened?
        5. Optimal Technology
        6. The Self-Defeating Business Model
        7. Offshore Computing - The Death Knell of IT in the U.S.
        8. Avoiding a Hell-Hole
     
  • The Minimum You Need to Know About Service Oriented Architecture
    • Published 2007 by Logikal Solutions. ISBN:  0-9770866-6-6 (ISBN-13: 978-0-9770866-6-5)
    • Over 370 pages with a CD-ROM
    • Author: Roland Hughes
    • My Notes:
      • I just (2008-07-26) received this book today but it looks like it will help me with a new problem. Our group has just been told the following:
        • You can stay on OpenVMS - Alpha (with eventual migration to Itanium)
        • develop a plan before the end of 2008 to replace "FMS and VT-220 terminal emulation" with web browsers
        • develop a plan before the end of 2009 to replace RMS with something relational (probably Oracle-Rdb)
           
  • "TP Software Development for OpenVMS"
    • Published 1994 by CBM Books (101 Witmer Road, Horsham, PA. 19044)
    • Cover is purple with white/pink lettering
    • Author: John M. Willis
    • this rare gem covers "transaction processing" on OpenVMS. Topics include: ACMS (Application Control Management System), CDD/Repository, DECforms, SQL, Rdb. High level program examples are in COBOL.
    • Chapter Titles:
      1. ACMS Transaction Processing Systems
      2. Preparing for Application Development
      3. Database Design
      4. CDD/Repository - The Data Repository
      5. Rdb/VMS - The Database
      6. VMS Message Files
      7. Application Development Overview
      8. ACMS Task Development
      9. DECforms Forms Development
      10. ACMS Servers and Procedures
      11. SQL Database Programming
      12. ACMS Task Groups
      13. Application Development and Testing
      14. ACMS Applications
      15. ACMS Menus
      16. Preparing the ACMS Run-Time System
      17. Additional ACMS Programming Functionality
      18. Additional DECforms Programming Functionality
      19. Additional SQL Database Programming Functionality
        Appendix-A: DECforms IFDL Source Code for INSERT Task
        Appendix-B: DECforms IFDL Source Code for Complete System
        Appendix-C: SQL Module Source Code for Complete System
        Appendix-D: ADU DUMP of ACMS Task Group Database SPEDX_DELIV_GROUP
        Appendix-E: ADU DUMP of ACMS Application Database SPEDX_APP.ADB
        Appendix-F: ADU DUMP of ACMS Menu Database SPEDX_MENU.MDB
        Index
     
  • "Rdb: A Comprehensive Guide - Third Edition
    • Published 1999 by Digital Press (Butterworth-Heinemann)
    • Cover is orange with with white and black lettering; 465 pages
    • Authors: Lilian Hobbs, Ian Smith, Ken England
    • a must-have book for anyone using or supporting Rdb; very thorough
    • this edition is very SQL oriented (probably a good thing) but...
      contains very little information about RDO (which is only bad if you need to maintain some very old Rdb applications still using RDO). Earlier editions may differ from this statement.
    • only 10 pages devoted to application development (accessing Rdb from a high level language).
      Click the following link to view the official Oracle Rdb7 Guide to SQL Programming manual or this link for Oracle Rdb Documentation
    • Chapter Titles:
      1. Components
      2. Data Definition
      3. Data Manipulation
      4. Storage Structures
      5. Table Access
      6. The Optimizer
      7. Transaction Management
      8. Security
      9. Database Integrity
      10. Database Restructuring
      11. Tuning and Optimization
      12. Distributing Rdb Databases
      13. Interoperability
      14. The Internet and Rdb
      15. Database Tools
      16. Application Programming
      17. Rdb/NT Workbench
        followed by:
        Appendix-A (Banking Database Definition)
        Index
         
  • "Rdb: A Comprehensive Guide - Second Edition
    • Published 1995 by by Digital Press (Butterworth-Heinemann)
    • Cover is turquoise with white and magenta lettering; 463 pages
    • Authors: Lilian Hobbs, Ken England
    • a must-have book for anyone using or supporting Rdb; very thorough
    • this edition is very SQL oriented (probably a good thing) but...
      contains very little information about RDO (which is only bad if you need to maintain some very old Rdb applications still using RDO). Earlier editions may differ from this statement.
    • only 10 pages devoted to application development (accessing Rdb from a high level language).
      Click the following link to view the official Oracle Rdb7 Guide to SQL Programming manual or this link for Oracle Rdb Documentation
    • with a chapter titled "Rdb on OSF/1" they decided to drop the "VMS" reference
    • Chapter Titles:
      1. Components
      2. Data Definition
      3. Data Manipulation
      4. Storage Structures
      5. Table Access
      6. The Optimizer
      7. Transaction Management
      8. Security
      9. Database Integrity
      10. Database Restructuring
      11. Tuning and Optimization
      12. Distributing Rdb Databases
      13. Interoperability
      14. CCD/Repository
      15. Transaction Processing
      16. Database Tools
      17. Application Programming
      18. Multimedia Databases
      19. Rdb on OSF/1 (a.k.a. Digital UNIX 4.x, a.k.a. Tru64 UNIX 5.x)
      20. The Future of Rdb
        followed by:
        Appendix-A (Banking Database Definition)
        Appendix-B (Rdb Logical Names/Environment Variables)
        Glossary
        Index
         
  • "Rdb/VMS: A Comprehensive Guide (First Edition)
    • Published 1991 by Digital Equipment Corporation (Digital Press)
      One Burlington Woods Drive,
      Burlington, MA 01803
    • Cover is black with pink and purple lettering; 352 pages
    • Authors: Lilian Hobbs, Ken England
    • a must-have book for anyone using or supporting Rdb; very thorough
    • this edition covers SQL, RDO, and RDML (new program development should only be in SQL and/or SQLMOD)
    • only 10 pages devoted to application development (accessing Rdb from a high level language).
      Click the following link to view the official Oracle Rdb7 Guide to SQL Programming manual or this link for Oracle Rdb Documentation
    • Looking back, I wished this book could have been my first exposure to relational databases rather than a college course based upon Oracle-6 (they talk about VAX, RA90 disk drives, etc.)
    • Chapter Titles:
      1. Components
      2. Data Definition
      3. Data Manipulation
      4. Storage Structures
      5. Table Access
      6. The Rdb/VMS Optimizer
      7. Transaction Management
      8. Security
      9. Database Integrity
      10. Database Restructuring
      11. Tuning and Optimization
      12. Distributing Rdb/VMS Databases
      13. Interoperability
      14. Data Dictionary
      15. Transaction Processing with Rdb/VMS
      16. Database Tools
      17. Application Programming
      18. The Future of Rdb/VMS
        followed by:
        Appendix-A (Banking Database Definition)
        Appendix-B (Rdb Logical Names)
        Glossary
        Index
         
  • "SQL for Dummies"
    • First Edition (August 1995 Edition, Published By: IDG Books Worldwide, ISBN: 1-56884-336-4)
      • chapter 15: gives an overview of "Embedded SQL" and "SQL modules"
      • chapter 16: gives a good overview of cursors
      • chapter 17: gives an overview of "Dynamic SQL" (including the PREPARE and DESCRIBE statements) as well as a passing mention of SQLDA
      • chapter 18: discusses error handling (including SQLCODE vs. SQLSTATE)
      • this edition seems a little more useful for bootstrapping yourself into an 'Oracle-Rdb' programming project
    • 5th Edition (July 2003)
      • chapter 15: gives an overview of "Embedded SQL" and "SQL modules"
      • chapter 16: ODBC + JDBC
      • chapter 17: SQL:2003 and XML
      • chapter 18: gives a good overview of cursors
         
  • "Introduction to Database Development"
    • AA-JK92A-TE
    • VAX Information Architecture (Orange Cover - Smaller 7" x 11 " Format - 1987)
    • all interactive access is via RDO (no SQL)
    • touches on: CDD, COBOL, DBMS, DATATRIEVE, RALLY, Rdb, RMS, TEAMDATA, VIDA
  • "Introduction to Application Development"
    • AA-JK93A-TE
    • VAX Information Architecture (Orange Cover - Smaller 7" x 11 " Format - 1987)
    • all interactive access is via RDO (no SQL)
    • touches on: ACMS, COBOL, CDD, DATATRIEVE, DBMS, RALLY, Rdb

Back to Home
Neil Rieck
Waterloo, Ontario, Canada.