OpenVMS 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
  • MySQL and MariaDB information has been moved here
Edit: 2023-13-31 (fixed a few typos)

RMS for OpenVMS

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 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.

$!============================================================
$! 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

"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)

"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.