The VMS SharkOpenVMS Notes: RMS / Oracle-RDB / MySQL / MariaDB

  1. The information and software presented on this web site are intended for educational use only for OpenVMS application developers and OpenVMS system attendants.
  2. The information and software presented on this web site are provided free of charge.
  3. The information and software presented on this web site are presented to you as-is. I will not be held responsible in any way if the information and software presented on this web site damages your computer system, business or organization (sounds like the legal warning from a Microsoft shrink-wrap seal, eh?)
  4. The information presented on this page can be really dangerous if you don't know what you are doing. So develop your skills by practicing on development systems. On top of this, always make sure you have good backup copies of everything before you begin.
  5. Is this text too small? You have two options:
    1. hold down the CTRL key while rolling the mouse wheel (zoom-in, zoom-out)
    2. use your keyboard like so:
      • hit: CTRL with "-" key to zoom smaller
      • hit: CTRL with "+" key to zoom larger
      • hit: CTRL with zero key to reset zoom
 

Quick Index


Edit: 2014-01-31 (changed some MariaDB stuff)

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. By building computer hardware with more memory, it was now 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. Also, it would now be the responsibility of hardware manufacturers to write new I/O drivers to exploit 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 16-bit Interdata Model 70 (a clone of an IBM minicomputer) that collected long-distance billing information from a Northern Electric 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 index schemes.

    p.s. OS developers developed 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 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
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 amount of work. On top of that, all future programmers coming into an existing project need to be as diligent as the original programmers and must never make any mistakes. This last statement is possible but only at a great expense.
  4. The "RMS Journaling" option provides cool logging features but requires a license (RMS is free, RMS Journaling is not)

  5. Relational databases are "relational in both time and space". Consider the following example from Oracle 9i

    1. At 9:00, Betty starts generating a large report which will take 5 minutes to execute
    2. At 9:01, Joe starts adding/deleting/modifying records which are of interest to Betty, then commits his changes before Betty's report is finished.
    3. Unlike what would happen with ISAM technology, Betty will not see any of Joe's changes because:
      an internal time-stamp associated with the SELECT statement used in Betty's report will be used to search through the rollback segments (UNDO tables) generated by the Bill's changes. Betty will only see things as they were at 9:00 even if Bill deleted everything.  It's as if Betty sees a "snapshot" of the whole database taken at 9:00, and "snapshot" is exactly what the associated file is called in Oracle-Rdb

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

  7. 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-Rdb Web Agent Links

Note that "Rdb Web Agent" is not recommended for new code development. In 2004-11-xx I sent a note to the "Oracle-Rdb team" asking them why the "Rdb Web Agent" links were buried in their web site. Here is their response:

The Rdb Web Agent software works well but it is very much out of date.  We are working hard to help you deliver great web based applications by enhancing our support for Java and JDBC based applications and by working on integration of Oracle-Rdb with Oracle Application Server.  We don't want customers to make a mistake by starting new development work with the Rdb Web Agent.  That's why we don't have links to these pages. 

"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, etc.). 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-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

(Recommended) RDB Books

MySQL - MariaDB for OpenVMS (a low budget alternative)

Relational Database Caveat:

MySQL is a fully SQL compliant "open source" (free to acquire and use) relational database.

History (a 10,000-foot/2-km view)

MariaDB is a fully SQL compliant "open source" relational database. This is an alternate fork of MySQL

Software for OpenVMS

MySQL/Maria Tips

Other (low budget) Alternatives

Other Links

Final Thoughts

Links: Click to here to download a quick hack demo program that shows how to extract information from a web page submitted to Apache for OpenVMS (CSWS) using CGI (Common Gateway Interface). This program can run in four different modes: CRT (green screen), WEB=GET, WEB=POST and WEB=GET+POST. It shows how to submit forms. It contains a small amount of RMS code to show how to interface RMS to the WEB. It also shows how to do a JAVASCRIPT pop-up window. Click OpenVMS Notes: CSWS-Tips if you need to modify your Apache environment.

Business over the web (an OpenVMS future without VT-100 terminals)

Back to OpenVMS
Back to Home
Neil Rieck
Kitchener - Waterloo - Cambridge, Ontario, Canada.