OpenVMS Notes:
RMS, RDB and Oracle-Rdb
-
The information presented here is intended
for educational use by OpenVMS technologists
- 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
- 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.
- 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.
- 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.
- 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.
- 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:
- 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.
- SQL access to RMS is only possible with third-party tools
- 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
- The "RMS Journaling" option provides cool logging features but requires
a license (RMS is free, RMS Journaling is not)
- IIRC, it was possible to make RMS transaction safe but this required
another layered product called ACMS (Application Control Monitoring System)
-
Relational databases are "relational in both
time and space". Consider the following example from Oracle
9i
- Alice and Bob are bank employees accessing a database consisting of
one million accounts.
- At 9:00, Alice begins generating a financial report which will take
2 minutes to execute (caveat: must be a read-only select)
- At 9:01, Alice's report generator is halfway through the database.
- 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.
- 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.
- 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
-
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)
- 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:
- 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
- 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
- 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)
- 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:
- Fundamentals of OpenVMS
- DCL and Utilities We Need
- DEC BASIC
- FMS (Forms Management System)
- CMS (Code Management System)
- CDD (Common Data Dictionary)
- Object and Text Libraries
- MMS (Module Management System)
- Message Utility, Mail and Phone
- FORTRAN
- COBOL
- C/C++
- MySQL (2014 Note: I recently
used information from this chapter to work with Mark Berrymans'
MariaDB)
- Oracle-Rdb
- Ruminations and Observations (invaluable personal observations
on the current state of IT)
- Overview
- What Do You Do?
- Keep Your Eye on the Sparrow
- Have You Ever Wondered Why Y2K Happened?
- Optimal Technology
- The Self-Defeating Business Model
- Offshore Computing - The Death Knell of IT in the U.S.
- 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:
-
ACMS Transaction Processing Systems
-
Preparing for Application Development
-
Database Design
-
CDD/Repository - The Data Repository
-
Rdb/VMS - The Database
-
VMS Message Files
-
Application Development Overview
-
ACMS Task Development
-
DECforms Forms Development
-
ACMS Servers and Procedures
-
SQL Database Programming
-
ACMS Task Groups
-
Application Development and Testing
-
ACMS Applications
-
ACMS Menus
-
Preparing the ACMS Run-Time System
-
Additional ACMS Programming Functionality
-
Additional DECforms Programming Functionality
-
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:
- Components
- Data Definition
- Data Manipulation
- Storage Structures
- Table Access
- The Optimizer
- Transaction Management
- Security
- Database Integrity
- Database Restructuring
- Tuning and Optimization
- Distributing Rdb Databases
- Interoperability
- The Internet and Rdb
- Database Tools
- Application Programming
- 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:
-
Components
-
Data Definition
-
Data Manipulation
-
Storage Structures
-
Table Access
-
The Optimizer
-
Transaction Management
-
Security
-
Database Integrity
-
Database Restructuring
-
Tuning and Optimization
-
Distributing Rdb Databases
-
Interoperability
-
CCD/Repository
-
Transaction Processing
-
Database Tools
-
Application Programming
-
Multimedia Databases
-
Rdb on OSF/1 (a.k.a. Digital UNIX 4.x, a.k.a. Tru64 UNIX 5.x)
-
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:
-
Components
-
Data Definition
-
Data Manipulation
-
Storage Structures
-
Table Access
-
The Rdb/VMS Optimizer
-
Transaction Management
-
Security
-
Database Integrity
-
Database Restructuring
-
Tuning and Optimization
-
Distributing Rdb/VMS Databases
-
Interoperability
-
Data Dictionary
-
Transaction Processing with Rdb/VMS
-
Database Tools
-
Application Programming
-
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.