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

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

• http://h41379.www4.hpe.com/doc/731FINAL/DOCUMENTATION/PDF/OVMS_731_RMS.PDF
• http://h41379.www4.hpe.com/openvms/journal/v2/articles/rms.pdf
• IsamCentral - Byte Designs :: C-ISAM, CIASM, D-ISAM and DIASM solutions for Windows, UNIX and Linux)

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.

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.

• www.oracle.com/database/index.html
• asktom.oracle.com

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