DSM___________________________________________
DEC DB Integrator Gateway for DSM User's Guide
Part Number: AA-Q0T4B-TE
March 1994
This document describes how to install, set up, and
use the
DEC DB Integrator Gateway for DSM software. It is
intended for Digital Standard M (DSM) environment
managers and programmers.
Revision/Update Information: This is a revised
document.
Operating System and Version: OpenVMS VAX Version
5.5 and higher
OpenVMS AXP Version
1.5 and higher
Software Version: DEC DB Integrator Gateway for DSM
Version 1.0A
Digital Equipment Corporation
Maynard, Massachusetts
__________________________________________________________
First Printing, January 1994
Revised, March 1994
Possession, use, or copying of the software described in
this publication is authorized only pursuant to a valid
written license from Digital or an authorized sublicensor.
Digital Equipment Corporation makes no representations
that the use of its products in the manner described in
this publication will not infringe on existing or future
patent rights, nor do the descriptions contained in this
publication imply the granting of licenses to make, use,
or sell equipment or software in accordance with the
description.
© Digital Equipment Corporation 1994. All rights
reserved.
The Reader's Comments form at the end of the hardcopy
version of this document requests your critical evaluation
to assist in preparing future documentation.
The following are trademarks of Digital Equipment
Corporation: Alpha AXP, AXP, Bookreader, CI, DASL, DDCMP,
DEC, DEC C, DECdtm, DECnet, DECserver, DECstation, DECUS,
DECUSCOPE, DECwindows, DELUA, Digital, DSM, DSM-11,
OpenVMS, PATHWORKS, PDP-11, RA, Rdb/VMS, TEAMDATA, TK,
ULTRIX, VAX, VAX CDD, VAX DOCUMENT, VAX DSM, VAX DSM DDP,
VAX RALLY, VAX RMS, VAX SQL, VMS, VT100, VT300, and the
DIGITAL logo.
The following are third-party trademarks: IBM and OS/2 are
registered trademarks of International Business Machines
Corporation and DB2 is a trademark of International
Business Machines Corporation. ISMS/R is a registered
trademark of Cullinet P.C. Software, Inc. Macintosh is a
registered trademark of Apple Computer, Inc. Microsoft,
MS, MS-DOS, and Visual Basic are registered trademarks
of Microsoft Corporation. Motif is a registered trademark
of Open Software Foundation, Inc., licensed by Digital.
MUMPS is a registered trademark of Massachusetts General
Hospital. OSF, OSF/1, and OSF/Motif are registered
trademarks of Open Software Foundation, Inc. ORACLE is
a registered trademark of Oracle Corporation. NFS and
SPARCstation are trademarks of Sun Microsystems, Inc. Sun
is a registered trademark of Sun Microsystems, Inc. UNIX
is a registered trademark of UNIX System Laboratories,
Inc., a wholly owned subsidiary of Novell, Inc.
All other trademarks and registered trademarks are the
property of their respective holders.
This document is available on CD-ROM.
This document was prepared using VAX DOCUMENT Version 2.1.
_____________________________________________________________
Contents
.................................................. vii
Overview
The Non-SQL Data Server (NSDS).............. 1-3
Installing the DEC DB Integrator Gateway for DSM
Software
Required Operating System Components......... 2-2
Installation Overview........................ 2-2
Registering Required Licenses................ 2-3
DEC DB Integrator Gateway for DSM
Installation Procedure....................... 2-3
Installing the DSM Software.............. 2-4
Common Components........................ 2-4
Disk Space Required.................... 2-4
VMS Privileges Required................ 2-6
Unlocking Process Account Password..... 2-7
Installing Common Components........... 2-7
Installing Common Component Images as
Shared................................. 2-9
Installing SQL Images for Privileged
Applications........................... 2-14
Using Different Versions of the Common
Components............................. 2-15
Deleting Versions of the Common
Components............................. 2-17
Installing SQL Services.................. 2-19
Installing NSDS.......................... 2-20
Notes for Installing PC Access and ODBC Rdb
Driver....................................... 2-23
iii
3 Setting Up the DEC DB Integrator Gateway for DSM
Software
Setting Up Your
DEC DB Integrator Gateway for DSM
System....................................... 3-2
Defining Databases....................... 3-2
Enabling DECdtm Services................. 3-3
Setting Up SQL Services.................. 3-4
Modifying OpenVMS Process Quotas......... 3-6
Attaching to Databases....................... 3-6
Precompiled Subqueries....................... 3-6
Security and Access Control for PC Clients... 3-7
Using DSM Transaction Processing............. 3-8
4 Using DASL with the DEC DB Integrator Gateway for DSM
Software
DASL Setup for Queries....................... 4-2
DASL Table Access........................ 4-2
DASL Data Types.......................... 4-2
DASL Access Paths........................ 4-3
Skipping Records......................... 4-5
DASL Setup for Database Updates.............. 4-5
Table Hierarchy.......................... 4-6
Pointer Fields........................... 4-6
Output Transforms........................ 4-7
Indexes.................................. 4-7
Counter Fields........................... 4-7
5 Using FileMan with the
DEC DB Integrator Gateway for DSM Software
Overview of the FileMan Interface............ 5-2
Setting Up the FileMan Interface............. 5-2
Installing the SQL Files..................... 5-2
Editing the SQL Files.................... 5-3
Using the SQL PROFILE File............... 5-3
Using the SQL PRESENTATION File.......... 5-3
Creating Initialization Routines......... 5-4
iv
6 User Defined Databases
Writing Your Own Routines.................... 6-2
Writing Metadata and Data Access Routines.... 6-3
Debug Flags.................................. 6-15
Index
Examples
2-1 How to Install SQL Services.............. 2-19
2-2 How to Install NSDS...................... 2-20
Tables
2-1 Global Pages and Sections Required for
Common Component VAX Images.............. 2-11
2-2 Global Pagelets and Sections Required for
DEC Rdb for OpenVMS AXP Images .......... 2-12
4-1 DASL Data Types.......................... 4-2
6-1 Supported External Data Types............ 6-3
6-2 Metadata Access Routines................. 6-4
6-3 Data Access Routines..................... 6-5
6-4 Data Update Routines..................... 6-13
v
________________________________________________________________
Preface
Intended Audience
This manual addresses the DSM[TM] environment manager who
is responsible for installing the DSM system software and
installing and setting up the DEC DB Integrator Gateway for DSM
(gateway for DSM) software. It also contains information
needed by MUMPS[R] programmers who will be using the
gateway for DSM software.
All readers of this manual should be familiar with the
topics covered in the manuals described in the section
"Related Manuals."
Manual Objectives
This manual describes how to install and set up the
gateway for DSM software. It also describes how to use
the DSM Application Software Library (DASL[TM]) interface,
the VA FileMan (FileMan) interface, and the user-defined
interface.
This manual is structured as follows:
o Chapter 1 describes Non-SQL Data Server (NSDS) concepts
and gives an overview of the gateway for DSM software.
o Chapter 2 contains preinstallation and installation
procedures for installing the gateway for DSM software
and the other required software components.
o Chapter 3 explains how to set up and manage gateway for DSM
systems.
o Chapter 4 describes how to use the DASL software with
the gateway for DSM software.
vii
o Chapter 5 describes how to use FileMan software with
the
gateway for DSM software.
o Chapter 6 contains information needed by programmers
to create an interface to databases other than DASL or
FileMan databases.
Related Manuals
The DSM documentation set consists of the following
manuals:
o Introduction to DSM
This manual describes the syntax and language elements
of DSM. It is an introduction to MUMPS programming and
is presented tutorially.
o Callable Routines Reference Manual
This manual describes the callable interface, which
allows routines written in software languages that run
layered on the OpenVMS[TM] operating system to access
the DSM database and interpreter.
o Database Operations Guide
This manual describes how to maintain the integrity and
reliability of the DSM database. It also describes
transaction processing and describes the internal
structure of the DSM database.
o External Routine Programming Guide
This manual describes how to call external routines.
o Installation and Management Guide
This manual describes how to install and manage DSM
systems.
o Language Pocket Reference
This manual summarizes DSM language elements, the DSM
command syntax, and I/O options.
o Language Reference Manual
This manual describes the syntax and elements of the
DSM language.
viii
o Master Index
This manual contains index entries for manuals in the
DSM documentation set (excluding the DASL manuals).
o Programmer's Guide
This manual describes how to use the programming
capabilities of DSM.
o Release Notes
This manual contains features and compatibility
information for the current version of DSM.
ix
o Windowing Application Programming Interface Guide for
DSM
This manual describes the M Windowing Application
Programming Interface (WAPI) for DSM.
o X and Motif Reference and Programming Guide for DSM
This manual describes the X, Motif [R], and ZXMUMPS
routines used by programmers to create windows
applications.
The following DASL manuals are part of the DSM documenta-
tion set. For more information about these manuals, see
the DASL documentation set or the Introduction to DSM.
o DASL Handbook
o DASL Management Guide
o DASL Pocket Reference
o DASL Programmer's Guide
o DASL Reference Manual
o DASL Master Index
The DSM for OpenVMS VAX[TM] documentation set is available
on the OpenVMS[TM] Online Documentation Library Compact
Disc. The DSM for OpenVMS AXP documentation set is
available on the OpenVMS AXP Layered Products Software
Online Documentation disc. These documentation files can
be used with the Bookreader[TM] for OpenVMS.
For More Information
The DEC RdbAccess for VAX RMS on VMS Non-SQL Data Server
User's Guide manual, which is provided with the DEC DB
Integrator Gateway for DSM User's Guide, describes how the
Non-SQL Data Server works with metadata and data drivers
to provide SQL access to nonrelational data.
For more information about topics covered in this guide,
refer to:
o DEC Database Access and Integration Product Family
User's Guide
o DEC DB Integrator Handbook
o DEC DB Integrator Installation and Configuration Guide
x
o DEC Rdb Guide to SQL Programming
o DEC Rdb Guide to Using the SQL/Services Client APIs
o DEC Rdb Guide to Using the SQL/Services Server
o DEC Rdb Introduction to SQL
o DEC Rdb SQL Reference Manual
o DEC Rdb SQL/Services Installation and Configuration
Guide
o OpenVMS System Manager's Manual: Tuning, Monitoring,
and Complex Systems
Conventions Used in This Manual
This manual uses the following documentation conventions
and symbols.
__________________________________________________________
Convention____Meaning_____________________________________
boldface Emphasizes an important concept.
type Indicates user input.
italic type Introduces new terms.
Indicates the title of a manual.
" " Indicates a section title.
Ctrl/x Indicates that you press the Ctrl key on
the terminal keyboard while simultaneously
pressing some other key (represented here by
x).
<Return> Indicates that you press the Return key on
the terminal keyboard.
<SP> Indicates that a space must separate
components of a command or command line.
<Tab> Indicates that you press the Tab key.
[item] Encloses optional characters or portions of
routine lines that need not be entered by
the user or that are not displayed by the
system.
xi
__________________________________________________________
Convention____Meaning_____________________________________
{item} Indicates that the enclosed item is
optional.
. . . Indicates that additional command parameters
can be added to the command line.
. Indicates a break between two illustrated
. lines of user input and that all user input
. is not shown.
OSF/1 Indicates the DEC OSF/1 AXP operating
______________system._____________________________________
xii
Your Comments Are Invited
Comments regarding the usability of our documentation,
including feedback on clarity, organization, and accuracy
are welcome. Comments are also welcome regarding figures,
examples, and the index. Please send your comments about
the content of the documentation to Digital Equipment
Corporation through any of the following methods:
o FAX: 508-467-1605
Attention: DSM Product Group
o Internet address
doc_comment@nurse.enet.dec.com
o Postal Service
Digital Equipment Corporation
DSM Product Group MR02-3/5E
2 Results Way
Marlboro, MA 01752-9840
U.S.A.
Please include the product name and version, and the name
of the manual and section on which you are commenting. For
your convenience, a Reader's Comments page is located at
the back of this manual.
________________________Note ________________________
The communication methods listed in this section
should be used only for comments on documentation.
Other types of information - such as Software
Performance Reports or software enhancement requests
- cannot be handled through these methods.
_____________________________________________________
xiii
1
________________________________________________________________
Overview
The DEC DB Integrator Gateway for DSM (gateway for DSM)
product is part of a family of Digital Equipment
Corporation's products that provide relational access
to databases that are not necessarily relational in
structure.
The gateway for DSM allows you to use popular PC
applications such as Microsoft[R] Access to access DSM
databases in a relational way. Also, the gateway for DSM
allows full database integration of DSM databases with
other relational databases through Digital's Accessworks
product set.
The strategy for this family of products is to provide
a Digital Standard Relational Interface (DSRI) so that
any access methods that work with Digital's Relational
Database (Rdb) also work with the databases of these
products.
Access methods that can be used include the following:
o DSRI
Direct DSRI access is used by a number of Digital
products (for example, VAX RALLY[TM] and TEAMDATA[TM])
and products developed by third-party vendors (for
example, Focus by IBI).
o VAX Structured Query Language (SQL)
VAX SQL[TM] is a OpenVMS facility that supports
Interactive SQL, Embedded SQL, Dynamic SQL, and SQL
Module Language.
o Rdb Remote
Overview 1-1
Rdb Remote is a facility that allows DSRI access from
remote OpenVMS systems using DECnet[TM] software.
1-2 Overview
o SQL Services
SQL Services supplies server support and client
application programming interfaces (APIs) for multiple
platforms including the following:
- OpenVMS
- ULTRIX[TM]
- MS-DOS[R]
- Microsoft Windows
- Macintosh[R]
- Sun[R] SPARCstation[TM]
- IBM[R] OS/2[R]
A number of PC and workstation software products use
the SQL Services API (for example, Microsoft's Q+E).
SQL Services uses PATHWORKS[TM] for communicating
between clients and servers.
o Open Database Connectivity (ODBC)
ODBC is a Microsoft-defined standard for SQL access
to databases in a client/server environment. It is
supported in the Microsoft Windows environment by the
ODBC Driver for Rdb.
The Non-SQL Data Server (NSDS)
The gateway for DSM and a number of the other family
members (for example, DEC DB Integrator Gateway for RMS
and DEC DB Integrator Gateway for DBMS) use the Non-SQL
Data Server (NSDS) engine to provide a relational server
interface. DBMS has its own data dictionary as a source
of metadata; RMS uses the Common Data Dictionary (CDD) to
store its metadata.
Figure 1-1 shows the software configuration for accessing
NSDS.
An M programming system has no inherent source of
metadata, but in many M programming environments data
dictionaries have been developed. To provide general
utility, the DSM gateway provides a call interface
definition so that M programs can be written to support
Overview 1-3
different data structures and metadata repositories. M
programs that are supplied support an interface to the
DASL databases and to VA FileMan databases. A user-defined
specification is supplied so you can create an interface
to other database systems.
For more information about the DASL interface, see
Chapter 4. For more information about the VA FileMan
interface, see Chapter 5. For more information about the
user-defined interface, see Chapter 6.
1-4 Overview
2
________________________________________________________________
Installing the DEC DB Integrator Gateway for DSM Software
This chapter provides information about the following:
o Required Operating System Components
o Installation Overview
o Registering Required Licenses
o DEC DB Integrator Gateway for DSM Installation
Procedure
For a description of how to set up the gateway for DSM
software, see Chapter 3.
Installing the DEC DB Integrator Gateway for DSM Software 2-1
Required Operating System Components
The following products are required to install and run the
gateway for DSM software:
o DSM for OpenVMS VAX or DSM for OpenVMS AXP (Version 6.3
and higher)
o Rdb Common Components software
o SQL Services software
o Non-SQL Data Server (NSDS) software
o DECdtm[TM] services (optional and supplied with the
OpenVMS operating system)
If the gateway for DSM is being used to access multiple
databases within one transaction, you need to enable
and start up DECdtm services. For more information
about DECdtm services, see the section "Enabling DECdtm
Services" in Chapter 3.
If you intend to use PC client applications for SQL access
to DSM, you may need to install the following:
o ODBC Driver for Rdb Version 1.0
o PATHWORKS
The Open Database Connectivity (ODBC) Driver for Rdb
must be installed on one or more PCs that are running
Microsoft Windows if PC software that uses ODBC (for
example, Microsoft Access and Microsoft's Visual Basic[R])
is going to be used.
Installation Overview
The following sections describe how to install, initial-
ize, and start up the gateway for DSM software. Before
you install the gateway for DSM software, you must install
the DSM distribution kit, which is not included with the
gateway for DSM kit.
After you install DSM, install the following products
(Digital recommends that you install them in the listed
order):
1. Install Rdb Common Components
2. Install SQL Services
2-2 Installing the DEC DB Integrator Gateway for DSM Software
3. Install NSDS
4. Enable DECdtm services (optional) as described in
Chapter 3
Registering Required Licenses
To run the DSM for OpenVMS VAX or DSM for OpenVMS AXP
software, you must register your DSM software license
by using the OpenVMS License Management Facility (LMF).
This procedure is described in the DSM Installation and
Management Guide.
Similarly, to run the gateway for DSM software, you must
register and load your DEC DB Integrator Gateway for DSM
license by using LMF.
To register a license, log in to the system manager's
account. Do one of the following to register the
gateway for DSM license:
o Invoke the SYS$UPDATE:VMSLICENSE.COM procedure. When it
prompts you for information, enter the data from your
Product Authorization Key (PAK).
o Use the DCL command LICENSE REGISTER with the
appropriate qualifiers that correspond to information
on the PAK.
After you register the license, you must load the license
to activate it. Enter the following to load the license:
$ LICENSE LOAD GATEWAY-DSM-USER<Return>
See the OpenVMS License Management Utility Manual for
additional information about using LMF, or type HELP
LICENSE at the DCL prompt.
DEC DB Integrator Gateway for DSM Installation Procedure
The following sections provide detailed instructions for
installing the gateway for DSM software and other required
software.
Installing the DEC DB Integrator Gateway for DSM Software 2-3
Installing the DSM Software
When you install the DSM distribution kit, you can include
support for the gateway for DSM interface by answering yes
to the following question:
Include support for gateway for DSM [Y OR N] ? <N> Y<Return>
For complete information about installing and managing
your DSM system, see the DSM Installation and Management
Guide.
Common Components
The Common Components kit contains SQL and Rdb/Dispatch,
the software components required by all the members of the
DEC DB Integrator family of products.
If you do not have DEC Rdb on your system, and do not plan
to install it, you must install the Common Components kit
before you can use the DEC DB Integrator Gateway products
or DEC DB Integrator.
If you have an existing DEC Rdb kit on your system, you
must install the Common Components kit or upgrade to DEC
Rdb Version 6.0 before you can use the DEC DB Integrator
Gateway products or DEC DB Integrator. If you choose not
to upgrade to DEC Rdb Version 6.0, Digital recommends that
you install the multiversion Common Components kit.
Disk Space Required
The following lists summarize the storage requirements for
installing the common components. Note that the values in
the lists are approximate.
The disk space requirements for OpenVMS AXP are:
__________________________________________________________
Used After
Kit______________Needed_for_Installation_Installation_____
Full Development 62,000 62,000
Run-Time_________62,000__________________39,000<>_________
2-4 Installing the DEC DB Integrator Gateway for DSM Software
The disk space requirements for OpenVMS VAX are:
__________________________________________________________
Used After
Kit______________Needed_for_Installation_Installation_____
Full Development 40,000 40,000
Run-Time_________40,000__________________28,000<>_________
Installing the DEC DB Integrator Gateway for DSM Software 2-5
VMS Privileges Required
VMSINSTAL is located in SYS$UPDATE, which is a restricted
directory. To install the Common Components, you must use
an account that has the SETPRV privilege authorized. As
one of its first actions, the VMSINSTAL command procedure
grants all privileges except BYPASS to the process that
invokes it. The VMSINSTAL command succeeds only if the
account has SETPRV privilege.
To check the default privileges of the installing account,
log in and enter this DCL command:
$ SHOW PROCESS/PRIVILEGES
If the installing account lacks the SETPRV privilege,
you cannot install the Common Components. You have two
options:
o Ask your system manager to use the VMS Authorize
Utility (AUTHORIZE) to modify the default privileges of
the account to include the SETPRV privilege.
o Run AUTHORIZE and make the changes yourself, if the
installing account has the SYSPRV privilege:
$ SET DEFAULT SYS$SYSTEM
$ RUN AUTHORIZE
UAF> MODIFY <account-name>/PRIVILEGES=(SETPRV)
UAF> EXIT
To activate the change in privileges, you must log out
and log in again.
________________________Note ________________________
When installing the Common Components on systems
with DECnet/OSI, the installation account must also
have the
NET$MANAGE identifier.
_____________________________________________________
2-6 Installing the DEC DB Integrator Gateway for DSM Software
Unlocking Process Account Password
The installation account cannot have a locked password. If
this is the initial installation of the Common Components,
the procedure creates an account called RDB$REMOTE, or
RDB$REMOTEnn for a multiversion installation. If the
installing account has a locked password, the installation
procedure will be unable to automatically generate a
password for this account, and will abort with the
following message.
*************************************************************
Error generating password for remote account.
**************************************************************
To modify an account with a locked password:
$ MCR AUTHORIZE
UAF> MODIFY <account-name>/FLAGS=NOLOCKPWD
Installing Common Components
To install Common Components, follow these steps:
1. Invoke the VMSINSTAL command procedure from a
privileged account, using the following syntax:
@SYS$UPDATE:VMSINSTAL variant-name device-name OPTIONS
N
For example, to install the Common Components
multiversion kit on device ddcu, type the following:
$ @SYS$UPDATE:VMSINSTAL decrdbmv060 ddcu: OPTIONS N
To install the Common Components standard kit on device
ddcu, type the following:
$ @SYS$UPDATE:VMSINSTAL decrdb060 ddcu: OPTIONS N
Digital recommends that you install the multiversion
kit. This avoids possible problems in overwriting
existing files and images for a different version of
the Common Components or DEC Rdb.
Installing the DEC DB Integrator Gateway for DSM Software 2-7
2. Type CC (for COMMON COMPONENTS) when prompted for
the kit type to install. The menu and response should
appear as follows:
***********************************************************************
From the following menu, please select the type of DEC Rdb kit to
install. Choice CC (COMMON COMPONENTS) is included in all 3 DEC Rdb
installations and should only be selected when installing a
product, other than DEC Rdb, that requires the common components.
***********************************************************************
Please select the type of DEC Rdb kit you wish to install:
Enter DEV for DEC Rdb DEVELOPMENT (the default)
Enter INT for DEC Rdb INTERACTIVE
Enter RTO for DEC Rdb RUNTIME-ONLY
Enter CC for COMMON COMPONENTS
* Enter the kit type to install [DEV]: CC
When you enter CC, only the Common Components saveset
(B) will be restored. The following submenu will be
displayed:
***********************************************************************
The COMMON COMPONENTS option should be selected only when installing
a product other than Rdb. It is not intended for use with any Rdb
installation.
***********************************************************************
Please select the type of COMMON COMPONENTS kit you wish to install:
Enter DEV for DEVELOPMENT
Enter RTO for RUNTIME-ONLY (the default)
* Enter the type of COMMON COMPONENTS to install [RTO]:
3. Press Return to select the default option, RUNTIME-
ONLY. Do not select the DEVELOPMENT option unless
you have purchased one of the following development
licenses:
o SQL development license
o Rdb development license
o DBI development license
2-8 Installing the DEC DB Integrator Gateway for DSM Software
Selecting the DEVELOPMENT option provides the addi-
tional SQL files needed for development; SQL$PREnn.EXE,
SQL$MODnn.EXE, and a small number of related files, as
well as all the Rdb/Dispatch and SQL files installed
with the RTO default.
Installing Common Component Images as Shared
________________________Note ________________________
Unless stated otherwise by the client specific
installation procedure, each client using the
Common Components will call SQL$STARTUP.COM and
SQL$SHUTDOWN.COM from their own startup and shutdown
procedures. These procedures should only be executed
standalone when client specific startup and shutdown
procedures are not provided.
_____________________________________________________
If you expect Common Component images to be used
extensively on your system, you can reduce the system
overhead and memory requirements by installing images as
shared. When images are not installed as shared, multiple
users who access the images at the same time must each
have their own copy of those images in memory. When the
images are installed as shared, everyone uses the same
copy of the image, eliminating duplicate copies of the
same image, and thus improving performance.
The Common Component installation procedure does not
install any images as shared. The following separate
startup and shutdown procedures are provided to install
and remove the SQL and Rdb/Dispatch images:
o SYS$STARTUP:SQL$STARTUP.COM
o SYS$MANAGER:SQL$SHUTDOWN.COM
Each client using the Common Components will call these
procedures from their own startup and shutdown procedures.
The following Rdb/Dispatch images are automatically in-
stalled by
SQL$STARTUP.COM:
o SYS$LIBRARY:RDBSHR.EXE
o SYS$LIBRARY:RDB$SHARE<version>.EXE
Installing the DEC DB Integrator Gateway for DSM Software 2-9
o SYS$MESSAGE:RDBMSGS.EXE
o SYS$MESSAGE:COSIMSG.EXE
SQL files are not currently installed by SQL$STARTUP.COM.
However, a subroutine to install these images is included
in SQL$STARTUP.COM. To activate the installation and/or
removal of SQL images, remove the comment character
(!) from the following lines in SQL$STARTUP.COM and
SQL$SHUTDOWN.COM:
$ ! GOSUB INSTALL_SQL_IMAGES
$ ! GOSUB REMOVE_SQL_IMAGES
If you invoke this subroutine in SQL$STARTUP.COM, the
subroutine installs the following SQL images:
o SYS$LIBRARY:SQL$INT.EXE
o SYS$LIBRARY:SQL$SHR.EXE
o SYS$MESSAGE:SQL$MSG.EXE
The subroutine also includes code to install SQL$UTL_
SHARE60.EXE and the development images, SQL$.EXE,
SQL$MOD.EXE, and SQL$PRE.EXE. To execute the instal-
lations, you must uncomment the code. SQL$STARTUP.COM
provides an explanation of why these images are not
included in the default installations.
Since SQL$UTL_SHAREnn.EXE is a varianted image for both
standard and multiversion Common Components, you must add
additional lines for each Common Components version you
install as shared. Development images are not typically
installed as shared images.
Table 2-1 and Table 2-2 show the remaining Common
Component images not installed as shared on your system.
SQL images not installed as shared during the installation
are also included in these tables to show the global page
and global section requirements.
If you install some or all of these images as shared, you
must verify that you have enough global pages and global
sections to accommodate the shared images.
Table 2-1 lists the approximate number of global pages
and global sections required for each Common Components
OpenVMS VAX image that you install as shared. <>
2-10 Installing the DEC DB Integrator Gateway for DSM Software
Table 2-2 lists the approximate number of global
pagelets and global sections required for each Common
Components OpenVMS AXP image that you install as shared.
On OpenVMS AXP systems, the size of a page can differ on
different CPUs. Pagelets are 512 bytes and correspond to
OpenVMS VAX pages; they are not CPU-specific. <>
Table 2-1 Global Pages and Sections Required for Common
___________Component_VAX_Images_________________________________
Global Global
Component_____Image_Name[1]___________________Pages[2]Sections[2]
SQL images SYS$LIBRARY:SQL$INT.EXE[3] 3 2
* SYS$LIBRARY:SQL$SHR.EXE 16 2
SYS$LIBRARY:SQL$UTL_ 142 3
SHARE60.EXE
* SYS$MESSAGE:SQL$MSG.EXE 329 1
* SYS$SYSTEM:SQL$.EXE 1635 2
* SYS$SYSTEM:SQL$PRE.EXE 4911 3
* SYS$SYSTEM:SQL$MOD.EXE 4768 3
[1]If_you_have_installed_the_multiversion_variant_of_the_Common_
Components, some images have the version number added to the end
of the image name. Those images are marked with an asterisk next
to the image name.
[2]Values supplied are approximate. If you have installed
the multiversion variant of the Common Components, multiply
the supplied values by the number of versions of the Common
Components running on your system.
[3]SQL$INT calls either SQL$SHR, or a version-specific
variant (such as SQL$SHR60, depending on what version you have
specified).
________________________________________________________________
Installing the DEC DB Integrator Gateway for DSM Software 2-11
Table 2-2 Global Pagelets and Sections Required for DEC Rdb for
___________OpenVMS_AXP_Images___________________________________
Global Global
Component_____Image_Name[1]___________________PageletsSections[2]
SQL images SYS$LIBRARY:SQL$INT.EXE[3] 65 1
* SYS$LIBRARY:SQL$SHR.EXE 2394 2
SYS$LIBRARY:SQL$UTL_ 431 2
SHARE60.EXE
* SYS$MESSAGE:SQL$MSG.EXE 329 1
* SYS$SYSTEM:SQL$.EXE 3237 1
* SYS$SYSTEM:SQL$PRE.EXE 8461 2
* SYS$SYSTEM:SQL$MOD.EXE 8166 2
[1]If_you_have_installed_the_multiversion_variant_of_the_Common_
Components, some images have the version number added to the end
of the image name. Those images are marked with an asterisk next
to the image name.
[2]Values supplied are approximate. If you have installed
the multiversion variant of the Common Components, multiply
the supplied values by the number of versions of the Common
Components running on your system.
[3]SQL$INT calls either SQL$SHR, or a version-specific
variant (such as SQL$SHR60, depending on what version you have
specified).
________________________________________________________________
The SQL$SHUTDOWN.COM procedure removes Common Component
images that have been installed.
By default, SQL$SHUTDOWN.COM removes only Rdb/Dispatch
installed images. However, a subroutine is included that
removes SQL images. To activate this subroutine, you must
remove the comment character (!) from the following line
in SQL$SHUTDOWN where this subroutine is invoked:
$ ! GOSUB REMOVE_SQL_IMAGES ! In SQL$SHUTDOWN.COM
SQL$SHUTDOWN.COM only removes images when no other client
requiring the Common Components is installed on the
system. Consequently, it requires a parameter indicating
the calling client to determine if any others are still
present on the system.
2-12 Installing the DEC DB Integrator Gateway for DSM Software
The syntax for using SQL$SHUTDOWN is as follows:
$ @SYS$LIBRARY:SQL$SETVER <sql version>
$ @SYS$MANAGER:SQL$SHUTDOWN <calling client>
If you do not execute SQL$SETVER.COM, and try to execute
SQL$SHUTDOWN.COM, you receive an error message indicating
that the logical names were not set.
In the preceding example, <sql version> is the Common
Component version shipped with the client, and <calling
client> is one of the following:
o DBI - use for DBI and the relational gateway products
o NSDS - use for the nonrelational gateway products
o RDBB - use for Rdb/ELN
o RDM - use for DEC Rdb
o VIDA - use for VIDA for IDMS/R
o VIDA2 - use for RdbAccess for DB2 Version 1.0 or
Version 2.0
o VIDAO - use for RdbAccess for Oracle Version 1.0 or
Version 2.0
The relational gateway products are DEC DB Integrator
Gateway for SYBASE, DEC DB Integrator Gateway for ORACLE,
DEC DB Integrator Gateway for DB2. The nonrelational
gateway products are DEC DB Integrator Gateway for
Custom Drivers, DEC DB Integrator Gateway for DBMS,
DEC DB Integrator Gateway for DSM, and DEC DB Integrator
Gateway for RMS.
Example:
$ @SYS$LIBRARY:SQL$SETVER 6.0
$ @SYS$MANAGER:SQL$SHUTDOWN NSDS
By default, the removal of images from INSTALL is
triggered by the logical names SQL$VERSION_VARIANT
and RDB$DISPATCH_VERSION_VARIANT, which are defined by
SYS$LIBRARY:SQL$SETVER.COM.
Installing the DEC DB Integrator Gateway for DSM Software 2-13
________________________Note ________________________
It is possible to set these logical names to
different versions for process, job, group, and
system tables, if necessary. However, you should be
very careful to avoid creating incompatibilities if
you do so.
_____________________________________________________
You can override these logical names by passing a valid
calling client name as parameter P1, and the Common
Component version to deinstall as P2.
To override the use of the logicals to determine which
Common Component images to remove, pass the version as P2.
The following command removes Version 6.0 images:
$ @SYS$MANAGER:SQL$SHUTDOWN DBI 6.0
If the DEC Rdb multiversion Version 6.0 software is
installed, these images will be removed; otherwise the
standard images, if they are Version 6.0 images, will be
removed.
Installing SQL Images for Privileged Applications
Unless you modify SQL$STARTUP.COM to do so, none of the
SQL images supplied as part of the Common Components are
installed. Applications that use SQL and are installed
with privileges require that you install the following
images using the Install utility:
o SYS$LIBRARY:SQL$INT.EXE
o SYS$LIBRARY:SQL$SHR.EXE (or SYS$LIBRARY:SQL$SHR60.EXE)
To install the images, use the following commands:
$ INSTALL ADD SYS$LIBRARY:SQL$INT.EXE
$ INSTALL ADD SYS$LIBRARY:SQL$SHR.EXE
Unless you install these images, applications installed
with privileges will encounter the error %SQL-F-NOENTRYPT,
Older SQL version not compatible with compiled SQL.
It is not necessary to install the SQL images as shared
or with privileges to avoid this error (installing in any
manner will avoid the error).
2-14 Installing the DEC DB Integrator Gateway for DSM Software
Using Different Versions of the Common Components
Certain environments may require a specific version of
the common components, or may need to set each component
to a different version. SYS$LIBRARY:SQL$SETVER.COM and
SYS$LIBRARY:SQL$SHOVER.COM are procedures provided with
the Common Components installation to set up the logical
names and symbols necessary for using a particular version
of SQL and/or Rdb/Dispatch.
________________________Note ________________________
To use the DEC DB Integrator Gateway products or DEC
DB Integrator, the Common Component's environment
must be set to 6.0. This can be done at the system,
group, or process level, as described in the
following section.
_____________________________________________________
SQL$SETVER.COM will accept the following three parameters:
o P1 = (REQUIRED) version (that is, S for STANDARD, 6.0
for MULTIVERSION) or RESET to reset SQL symbols or
REMOVE to remove SQL and DISPATCH logicals.
o P2 = (OPTIONAL) Logical name table switch (/PROCESS,
/GROUP, /JOB, or /SYSTEM). Default is /PROCESS
o P3 = (OPTIONAL) Common Component logicals to set (SQL,
DISPATCH, or ALL). Default is ALL. This can go in P2 or
P3 as the last parameter.
The following example sets both SQL and Rdb/Dispatch to
multiversion Version 6.0. If multiversion Version 6.0
is not installed and a standard version is found, the
standard version will be set.
$ @SYS$LIBRARY:SQL$SETVER 6.0
Current PROCESS SQL environment is version T6.0-01 (MULTIVERSION)
Current PROCESS Rdb/Dispatch environment is version T6.0-01 (MULTIVERSION)
Installing the DEC DB Integrator Gateway for DSM Software 2-15
The following examples show other possible parameters.
! To set only Rdb/Dispatch
$ @SYS$LIBRARY:SQL$SETVER 6.0 DISPATCH
! To set only SQL
$ @SYS$LIBRARY:SQL$SETVER 6.0 SQL
! To set only SQL in group table
$ @SYS$LIBRARY:SQL$SETVER 6.0 /GROUP SQL
! To set both SQL and Rdb/Dispatch to standard version
$ @SYS$LIBRARY:SQL$SETVER S
! To set only Rdb/Dispatch to standard version
$ @SYS$LIBRARY:SQL$SETVER S DISPATCH
! To set only SQL to standard version
$ @SYS$LIBRARY:SQL$SETVER S SQL
The SQL$SHOVER.COM procedure has the following two
functions:
o To show the current settings for each logical name
table
$ @SYS$LIBRARY:SQL$SHOVER
Current PROCESS SQL environment is version T6.0-01 (MULTIVERSION)
Current PROCESS DISPATCH environment is version T6.0-01 (MULTIVERSION)
o To show all the versions installed on the system. This
can be used to determine what the highest version of
Rdb/Dispatch or SQL is installed on the system.
$ @SYS$LIBRARY:SQL$SHOVER VERSIONS
"SQL$INSTALLED_VERSIONS" = "*T6.0-01" (LNM$PROCESS_TABLE)
"RDB$DISPATCH_INSTALLED_VERSIONS" = "V4.0-5" (LNM$PROCESS_TABLE)
"*T6.0-01"
The * in the preceding output indicates a multiversion
kit.
The following examples show other possible parameters:
! To show only Rdb/Dispatch
$ @SYS$LIBRARY:SQL$SHOVER VERSIONS DISPATCH
! To show only SQL
$ @SYS$LIBRARY:SQL$SHOVER VERSIONS SQL
! To show only 6.0 Rdb/Dispatch
$ @SYS$LIBRARY:SQL$SHOVER VERSIONS DISPATCH 6.0
! To show only 6.0 SQL and suppress output
$ @SYS$LIBRARY:SQL$SHOVER VERSIONS SQL 6.0 NOSHOW
2-16 Installing the DEC DB Integrator Gateway for DSM Software
Deleting Versions of the Common Components
For your convenience, the Common Components kit provides a
command procedure, SYS$MANAGER:SQL$DEINSTALL_DELETE.COM,
to delete current or previous versions of the Common
Components. You must run this command file from an account
that has SETPRV privileges, or from an account that has
SYSPRV, CMKRNL, SYSNAM, and WORLD privileges.
You can use this command file to delete a previous version
of the Common Components when you have installed a newer
version and determine that prior versions are no longer
needed.
________________________Note ________________________
SQL$DEINSTALL_DELETE will not execute if multiple
products requiring Common Components are found on
the system or DEC Rdb of the same version is found
on the system.
_____________________________________________________
When you run the command file, you can optionally pass
a single parameter that indicates the output location
for all messages generated while the command file
processes. This parameter can either be the name of a file
(for example, SQL$DEINSTALL_DELETE.LOG) or the logical
SYS$OUTPUT (which displays messages on your screen).
To run the SQL$DEINSTALL_DELETE command file and have
messages sent to a file named SQL$DEINSTALL_DELETE.LOG,
enter the following command:
$ @SYS$MANAGER:SQL$DEINSTALL_DELETE.COM SQL$DEINSTALL_DELETE.LOG
The command procedure checks for the existence of the
different versions of the Common Components on your
system, and then displays a menu listing each version
found (Standard first, and then the oldest to the most
current Multiversion):
Installing the DEC DB Integrator Gateway for DSM Software 2-17
SQL versions currently installed on your system
* Version 5.1 (Multiversion)
1 Version 6.0 (Multiversion)
0 Quit
(* This version cannot be deinstalled by this procedure)
Enter Choice to deinstall (0...1) : 0
If the command procedure displays an asterisk (*) next to
a version
entry on the menu, it means that version cannot be deleted
by
SQL$DEINSTALL_DELETE.COM because it is pre-Version 6.0.
Enter the menu number for the version you want to delete.
For example, to delete Version 6.0 Multiversion, enter the
following:
Enter Choice to deinstall (0...1) : 1
The command procedure displays the following message:
You are about to deinstall SQL 6.0 (Multiversion)
If your system is a cluster member, this procedure creates
SYS$SCRATCH:SQL$CLUSTER_DEINSTALL60.COM. Use this command
procedure to deinstall the Common Components Version 6.0
(Multiversion) from other nodes in the cluster. You must
either run this command procedure on each node that has
Common Components Version 6.0 (Multiversion) installed, or
use SYSMAN to run it clusterwide.
Next, the command procedure asks you to confirm that you
want to continue with the deinstallation (whether or not
your system is part of a cluster):
Enter Y(ES) to continue to deinstall SQL 6.0 (Multiversion): YES
The final prompt asks you whether or not you want to
delete the RDB$REMOTE account for the version you
specified (keep this account if, for example, you plan
to use it as a template to build other accounts):
Do you want to delete RDB$REMOTE60? [N]: YES
2-18 Installing the DEC DB Integrator Gateway for DSM Software
When the command procedure completes the deletion of the
appropriate files, it displays the following message:
%SQL-I-END Deinstallation of SQL 6.0 (Multiversion) now complete
Installing SQL Services
If multiplatform support such as PC access is required,
then you must install SQL Services.
Example 2-1 shows how to start the SQL Services instal-
lation. The installation procedure asks if you would
like to print out the DEC Rdb SQL/Services Installation
and Configuration Guide. Answer YES to this question
and refer to the DEC Rdb SQL/Services Installation
and Configuration Guide for the complete SQL services
installation procedure.
Example 2-1 How to Install SQL Services
.
.
.
Enter the products to be processed from the first distribution volume set.
* Products: SQLSRV<Return>
* Enter installation options you wish to use (none): <Return>
The following products will be processed:
SQLSRV V6.0
Beginning installation of SQLSRV V6.0 at 14:39
%VMSINSTAL-I-RESTORE, Restoring product save set A ...
%VMSINSTAL-I-RELMOVED, Product's release notes have been moved to SYS$HELP.
The SQL/Services installation guide has been provided in SYS$HELP.
* Would you like to print the installation guide ? [NO]: YES<Return>
.
.
.
Installing the DEC DB Integrator Gateway for DSM Software 2-19
Installing NSDS
The RdbAccess installation procedure provides two kit
installation options, the NSDS FULL kit and the NSDS BASE
kit. Install the NSDS BASE kit. After the installation,
add the following line to your system startup file:
$ @SYS$STARTUP:NSDS$STARTUP.COM NSDS_BASE
Example 2-2 shows how to install RdbAccess Version 2.2
(NSDS BASE kit option).
Example 2-2 How to Install NSDS
.
.
.
Enter the products to be processed from the next distribution volumeset.
* Products: NSDS<Return>
* Enter installation options you wish to use (none): <Return>
The following products will be processed:
NSDS V2.2
Beginning installation of NSDS V2.2 at 07:41
%VMSINSTAL-I-RESTORE, Restoring product save set A ...
%VMSINSTAL-I-RELMOVED, Product's release notes have been moved to SYS$HELP.
*****************************************************************
DEC DB Gateway for RMS installation procedure (2.2-1)for:
NSDS 2.2-1
NSDS CDD METADATA DRIVER 2.2-1
NSDS RMS DATA DRIVER 2.2-1
*****************************************************************
Checking system requirements ...
CDD Major Version Minor Version 5
%NSDS-E-BADCDDPLUSVERSION, CDD/Plus V4.2 or later required to run full NSDS
The NSDS FULL kit (RDBACCESS-RMS) includes
the NSDS Server plus RMS and CDD Drivers
(continued on next page)
2-20 Installing the DEC DB Integrator Gateway for DSM Software
Example 2-2 (Cont.) How to Install NSDS
The NSDS BASE kit (RDBACCESS-BASE) includes:
the NSDS Server without RMS and CDD Drivers
* Would you like to install the NSDS FULL kit [YES]? NO<Return>
* Would you like to install the NSDS BASE kit [NO]? YES<Return>
* Would you like to install the sample drivers [NO]?<Return>
Product: RDBACCESS-BASE
Producer: DEC
Version: 2.2-1
Release Date: 21-FEB-1994
* Does this product have an authorization key registered and loaded? NO<Return>
*****************************************************************
No license(s) is available for the product. You may continue
the installation, but the product will not run and only a
a minimal IVP can be run until the appropriate license(s) is
installed.
*****************************************************************
* Do you wish to continue the installation [NO]? YES<Return>
* Do you want to purge files replaced by this installation [YES]?<Return>
*****************************************************************
The full IVP cannot be run. A minimal IVP may be run instead.
*****************************************************************
* Do you want to run the IVP after the installation [YES]? <Return>
*****************************************************************
There are no more questions. The installation takes ap-
proximately 15 minutes on a stand-alone VAX 8200.
*****************************************************************
Beginning installation ...
*****************************************************************
Installing under VMS V1.5 - 28-SEP-1992 07:43
(continued on next page)
Installing the DEC DB Integrator Gateway for DSM Software 2-21
Example 2-2 (Cont.) How to Install NSDS
*****************************************************************
%VMSINSTAL-I-SYSDIR, This product creates system disk directory
VMI$ROOT:[SYSTEST.NSDS$IVP].
%VMSINSTAL-I-SYSDIR, This product creates system disk directory
VMI$ROOT:[SYSHLP.EXAMPLES.NSDS].
******************************************************************
The NSDS$STARTUP.COM and NSDS$SHUTDOWN.COM files have been
placed in SYS$STARTUP. The following line should be placed
in your system startup file.
$ @SYS$STARTUP:NSDS$STARTUP.COM NSDS_BASE
******************************************************************
******************************************************************
The IVP has been placed in SYS$COMMON:[SYSTEST.NSDS$IVP].
You may run this procedure separately any time you wish. To
execute it, type :
$ SET DEFAULT SYS$COMMON:[SYSTEST.NSDS$IVP]
$ @NSDS$IVP
******************************************************************
******************************************************************
Adding DEC DB Gateway for RMS help to the DCL help library
******************************************************************
%VMSINSTAL-I-ACCOUNT, This installation updates an ACCOUNT named RDB$REMOTE.
%UAF-I-MDFYMSG, user record(s) updated
*************************************************************
The qualifier LGICMD for the RDB$REMOTE account in SYSUAF
is modified by this installation.
*************************************************************
%VMSINSTAL-I-MOVEFILES, Files will now be moved to their target
directories...
(continued on next page)
2-22 Installing the DEC DB Integrator Gateway for DSM Software
Example 2-2 (Cont.) How to Install NSDS
*****************************************************************
The DEC DB Gateway for RMS IVP verifies that all files are
properly in place and that the sample RMS data files are defined
in the CDD/Plus dictionary.
*****************************************************************
*****************************************************************
Performing DEC DB Gateway for RMS IVP for:
NSDS V2.2-1
*****************************************************************
*****************************************************************
Checking the existence of DEC DB Gateway for RMS.
*****************************************************************
*****************************************************************
DEC DB Gateway for RMS IVP completed successfully
*****************************************************************
Installation of NSDS V2.2 completed at 07:43
Adding history entry in VMI$ROOT:[SYSUPD]VMSINSTAL.HISTORY
Creating installation data file: VMI$ROOT:[SYSUPD]NSDS022.VMI_DATA
VMSINSTAL procedure done at 07:44
Notes for Installing PC Access and ODBC Rdb Driver
Distributed SQL access to DSM from PC applications is
supported using DEC SQL Services and Microsoft ODBC APIs.
Consequently, PC software, such as Microsoft Access,
Microsoft's Visual Basic, or Powersoft's PowerBuilder,
which use these APIs can be configured to access DSM
databases.
Installing the DEC DB Integrator Gateway for DSM Software 2-23
Support for DEC SQL Services and ODBC connections to the
OpenVMS system is supplied through PATHWORKS and a set
of Rdb drivers loaded on the PC. PATHWORKS needs to be
installed on your PC for SQL Services to work.
Files for the ODBC Rdb driver are included with the SQL
Services software. You must install this software on your
PC to use ODBC. For directions about how to install the
driver, see the DEC Rdb SQL/Services Installation and
Configuration Guide.
2-24 Installing the DEC DB Integrator Gateway for DSM Software
3
________________________________________________________________
Setting Up the DEC DB Integrator Gateway for DSM Software
This chapter provides information about the following:
o Setting Up Your DEC DB Integrator Gateway for DSM
System
o Attaching to Databases
o Precompiled Subqueries
o Security and Access Control for PC Clients
o Using DSM Transaction Processing
Setting Up the DEC DB Integrator Gateway for DSM Software 3-1
Setting Up Your DEC DB Integrator Gateway for DSM System
The following sections describe how to set up your
gateway for DSM system. Setup tasks include the following:
o Defining databases
o Enabling DECdtm services
o Setting up SQL Services (SQLSRV$CONFIG.DAT)
o Modifying OpenVMS process quotas (AUTHORIZE)
Defining Databases
Define databases by assigning system logical names in the
SYS$STARTUP:DSM$INSTALL_SITE.COM command procedure, as
follows:
$ DEFINE logical "/TYPE=NSDS/PATH=path/DICTIONARY=DSM$SQL_MDI_DRIVER"
where:
path = ENV:UCI:VOL:ROUTINE:INIT
where:
ENV is a valid DSM environment name
UCI is a valid DSM UCI name
VOL is a valid DSM volume set name
ROUTINE is a valid driver support routine name prefix
INIT is a DSM initialization routine that is called
when the database is attached
All of the previous parameters are optional except
ROUTINE, which has no default. For the DASL version, the
routine name prefix is %DANSDS. For the FileMan version,
the routine name prefix is %SQLSFM. For more information
about user-defined routine name prefixes, see Chapter 6.
3-2 Setting Up the DEC DB Integrator Gateway for DSM Software
Sample logicals for a DASL database and a FileMan database
are already defined in SYS$EXAMPLES:DSM$SQL_DATABASE_
LOGICALS.COM. If you want to use either or both of these
interfaces, you can copy either or both of the following
lines into DSM$INSTALL_SITE.COM (be sure to remove the
comment character (!)):
!$ DEFINE/SYSTEM DSMDB "/TYPE=NSDS/PATH=:::%DANSDS:/DICT=DSM$SQL_MDI_DRIVER"
!$ DEFINE/SYSTEM FMDB "/TYPE=NSDS/PATH=:::%SQLSFM:/DICT=DSM$SQL_MDI_DRIVER"
Note that the database name must be in the SYSTEM name
table if you want to use a database with SQL Services.
Enabling DECdtm Services
DECdtm services is required if you want to access multiple
databases within the same transaction. DECdtm services
is supplied with the OpenVMS operating system and starts
up automatically when you boot your system. To actually
turn on DECdtm services, you must create a transaction log
file, as follows:
1. Edit SYS$STARTUP:DSM$SQL_LOGICALS.COM and add a comment
character (!) in front of the following line:
$ DEFINE/SYSTEM SQL$DISABLE_CONTEXT "true"
This allows you to perform distributed transactions and
take advantage of functionality such as:
o Using interactive SQL to copy a table from DSM to an
Rdb database
o Using the DEC Data Distributor software, which
replicates DEC Rdb relational databases
o Using the DEC DB Integrator product
2. Use the Log Manager Control Program (LMCP) to create a
transaction log file. Enter the following:
$ RUN SYS$SYSTEM:LMCP<Return>
LMCP> CREATE LOGFILE filespec [qualifier...]<Return>
For more information about DECdtm services and about how
to create transaction log files, see the OpenVMS System
Manager's Manual: Tuning, Monitoring, and Complex Systems
document.
Setting Up the DEC DB Integrator Gateway for DSM Software 3-3
Setting Up SQL Services
Access from remote platforms that are not running the VMS
operating system is normally provided by SQL Services.
SQL Services maintains a set of serially reusable server
processes for accessing databases. There are two types of
server processes:
o Generic
Generic server processes serve an access stream by
attaching to a database, performing a series of SQL
actions, and detaching from the database.
o Database
Database servers attach to a particular database when
they start up and stay attached to that database while
access streams come and go.
NSDS gateways cache all of a database's metadata at attach
time. This allows database servers to be more responsive
especially if a database has a large volume of metadata.
Client software must provide a valid username and password
to access a database using SQL Services. In the case of
generic servers, the user-identifier code (UIC), rights,
and privileges of the client user are inherited by the
server process, but the process quotas come from the
SQLSRV$SRV account. In the case of database servers, a
particular account is used to access the database. The
client user must have a particular right from the rights
database to log in as that user. Some client software,
ODBC for example, knows how to use database servers. Other
client software does not.
SQL Services servers are set up by editing the file
SQLSRV$CONFIG.DAT located in SYS$STARTUP. The following is
a sample generic server setup where one server process is
started when SQL Services start up. As many as 10 servers
can be running at one time. After 1800 seconds, unused
servers are run down.
3-4 Setting Up the DEC DB Integrator Gateway for DSM Software
-- -----------------------------------------------------------------
-- Default GENERIC Class Definition
-- ----------------------------------------------------------
TYPE "GENERIC"
STARTUP "SQLSRV$SRV"
MIN 1
MAX 10
IDLE 1800
-- ----------------------------------------------------------
With generic servers, the client user must have access
to the DSM database. With database servers, the user
specified in SQLSRV$CONFIG.DAT must have access to the
DSM database.
The following is a sample setup for a database server.
The account TRNMGR1 is used to access the database. The
EXECUTE string is a valid SQL attach statement for the
database. Users that access this server must be granted
the rights identifier in the IDENT clause (for example,
DSM$MANAGER_TRNMGR1).
-- ------------------------------------------------------------
-- Sample DATABASE Class Definition
-- ----------------------------------------------------------
TYPE "CHCSDB"
STARTUP "SQLSRV$SRV"
USER "TRNMGR1"
EXECUTE "ATTACH 'FILENAME CHCSDB'"
IDENT "DSM$MANAGER_TRNMGR1"
MIN 3
MAX 6
IDLE 1800
-- ----------------------------------------------------------
Server processes can run out of virtual memory unless
the PGFLQUO process quota is large enough. The virtual
memory required depends on the quantity of metadata in the
database (the number of tables and the number of columns
per table). A PGFLQUO of 40000 can accommodate a large
amount of metadata. WSEXTENT can be set to PGFLQUO - 10000
to prevent excessive page faults. The accounts that need
to be modified are the SQLSRV$SRV account and each of the
database-specific accounts.
Setting Up the DEC DB Integrator Gateway for DSM Software 3-5
SQL Services servers create log files in the SYS$SYSDEVICE:[SQLSRV$SERVER]
directory, which can be used for diagnostic purposes. The
log file name is constructed as follows:
node_SS_type_number.LOG
For example:
MUMPS_SS_CHCSDB_0000.LOG
MUMPS_SS_GENERIC_0006.LOG
Modifying OpenVMS Process Quotas
If Rdb Remote is being used, PGFLQUO must be at least
30000 and WSEXTENT must be at least 20000 for the
RDB$REMOTE account.
Attaching to Databases
When you define a database using the ODBC Administrator,
class servers for SQL Services and generic class servers
attach strings must be defined.
For Rdb Version 4.0, the syntax for attaching to databases
is as follows:
DECLARE SCHEMA FILENAME <logical name>
For versions of Rdb later than Version 4.0, the syntax for
attaching to databases is as follows:
ATTACH 'FILENAME <logical name>'
If you are writing programs that use embedded SQL, use the
following syntax with Rdb Version 4.0:
DECLARE SCHEMA FILENAME <logical name> WITH DSRI OPTIONS (NO PARAM3)
Precompiled Subqueries
The DASL and FileMan data driver support generates
precompiled subqueries that improve query performance.
The precompiled routines are MUMPS routines that are
stored in the default volume set in the DASL and FileMan
environments.
You need to have at least 2 megabytes of space available
in the default volume set. Precompiled routines that are
not used for a week are purged.
3-6 Setting Up the DEC DB Integrator Gateway for DSM Software
If a table definition is changed in FileMan, enter the
following to eliminate stored subqueries for the modified
table:
>D KTABLE^%SQLSFMP(table_name)<Return>
If a table definition or an underlying data dictionary
definition is changed in DASL, enter the following to
eliminate stored subqueries for the modified table:
>D KTABLE^%DANSDSP(table_name)<Return>
Security and Access Control for PC Clients
There are different ways to control access to a database.
PC Client software must provide a valid VMS user name and
password, which is the basis of all access control.
The following is a list of the different levels where
rights and privileges can be tested based on the VMS user
name:
o If an SQL Services database server is serving the
PC client, then the user logging in must be granted
the right specified in the SQLSRV$CONFIG.DAT file to
access the database. In this case, the user account
specified in SQLSRV$CONFIG.DAT must have the rights and
privileges discussed in the following list items.
For a description of SQL Services and SQLSRV$CONFIG.DAT,
see the section "Setting Up SQL Services."
o If a DEC DB Integrator Gateway database is defined by
a logical name, the logical name can be in a logical
name table protected by an ACL. This does not provide
protection from a user that for some reason already
knows what the logical name translates to.
o DSM security can be set up to require an ACL for access
to a given DSM environment.
o DSM security can provide read, write, or read and write
access to specific globals for SYSTEM, WORLD, GROUP,
and UCI.
Setting Up the DEC DB Integrator Gateway for DSM Software 3-7
o With DASL, specific databases can be defined that allow
access only to tables in a particular DASL group. The
DASL table definition allows you to specify whether
tables can be inserted, updated, or deleted.
o With FileMan, specific databases can be defined
that allow access only to tables in a particular SQL
PROFILE, and the profile definition specifies whether
tables can be inserted, updated, or deleted.
Using DSM Transaction Processing
To use DSM transaction processing with the gateway for DSM,
you need to create a configuration that contains multiple
database sets because the default routine directory must
not be located on a transaction database set.
Also, you must edit the data driver routine (%DANSDSD for
DASL, %SQLSFMD for FileMan) as follows:
For entry point TRSTART add ZUSTART "SQL"
For entry point COMMIT add ZUCOMMIT
For entry point ABORT add ZUABORT
The global that maps the precompiled subqueries is
^sqlprog. This global should be translated to a non-
transaction database set.
3-8 Setting Up the DEC DB Integrator Gateway for DSM Software
4
________________________________________________________________
Using DASL with the DEC DB Integrator Gateway for DSM Software
This chapter includes information about the following:
o DASL Setup for Queries
o DASL Table Access
o DASL Data Types
o DASL Access Paths
o DASL Setup for Database Updates
For more information about installing and using the DASL
software, see the DASL documentation set.
DASL with the DEC DB Integrator Gateway for DSM Software 4-1
DASL Setup for Queries
DASL support for the gateway for DSM software uses Table
Definitions and Data Dictionary definitions to map DASL
data as relational.
Run the %DACVRT routine to set up all tables as readable
but not updateable. Cross references of type NUMERIC,
NAME, and STRING are made visible to the interface by
defining them as access paths in the column definition.
For more information about access paths, see the section
"DASL Access Paths" in this chapter.
Another way to control table visibility is to use
DASL groups. If the database definition specifies an
initialization routine, and that routine sets the local
variable GROUP to a valid DASL group name, then only the
tables belonging to that group are made visible.
DASL Table Access
The gateway for DSM allows SQL access to any or all DASL
tables. For each table that you want to access, select
the DASL Define Tables option and enter Y after the Read?
prompt in the SQL Access section as shown in Figure 4-1.
DASL Data Types
DASL data types are mapped to VMS data types that are
acceptable to NSDS and SQL. The valid DASL data types are
described in Table 4-1.
Table_4-1__DASL_Data_Types________________________________
Data_Type___Description___________________________________
STRING Mapped to SQL CHAR(n), where n is the DASL
field length.
NAME Mapped to SQL CHAR(n), where n is the DASL
field length.
DATE Mapped to (and converted to) the VMS date and
time stamp.
(continued on next page)
4-2 Using DASL with the DEC DB Integrator Gateway for DSM Software
Table_4-1_(Cont.)__DASL_Data_Types________________________
Data_Type___Description___________________________________
TIME Mapped to (and converted to ASCII string) SQL
CHAR(11).
NUMERIC The DASL field length includes a sign.
Therefore, a field length of 10 digits is
interpreted as 9 digits. If the number of
digits is greater than 9 or a scale factor is
entered into the field length for the table
column, NUMERIC is mapped to (and converted
to) G_FLOAT; otherwise NUMERIC is mapped to
____________(and_converted_to)_a_32-bit_integer.__________
Note the following:
o Table field lengths and types override data dictionary
field lengths and types.
o When fields are accessed, output transforms are
executed. The output transforms should provide data
that is consistent with field types and lengths.
DASL Access Paths
When presented with queries like the following, NSDS can
only fetch all the rows in the table unless access paths
(NSDS terminology) have been defined.
SELECT * FROM COMPANY WHERE COMPANY_NAME STARTING WITH 'Dig';
SELECT NAME,CITY FROM NAMES WHERE NAME='Smith,John';
The first query in the previous example can be performed
more efficiently if an access path is defined for the
COMPANY_NAME column. The second query is more efficient
if an access path is defined for the NAME column. To take
advantage of DASL cross-references and other indexes that
may have been defined, answer Y to the Access Path? prompt
and enter an Access Path Global as shown in Figure 4-2.
Note the following:
o You can define an access path for any column of the
table unless the data type maps to G_FLOAT, which NSDS
DASL with the DEC DB Integrator Gateway for DSM Software 4-3
does not support for access paths, or the column is a
pointer field.
o The initialization routine and order routine are used
to specify routines to process global indexes of an
unknown structure.
o Known structures are DASL NAME cross-references, DASL
STRING cross-references, and DEFAULT. DASL NUMERIC
cross-references have DEFAULT structure.
o The DEFAULT global index structure for access paths is
as follows:
^GLO(constant subscript 1, constant subscript 2, ...,
subscript ranging over column values,
DASL primary key 1 value, key 2 value, ...key n value)
= "" or something.
o Every table in which the first DASL primary key is one
of the columns automatically has an access path for
that column.
o Numeric access paths are expected to present the data
sorted numerically. STRING access paths are expected
to present the data sorted by ASCII character. Primary
key access paths can be of type STRING but have canonic
numbers up front: special coding handles this case.
DASL NAME and DASL STRING cross-references appear as
uppercase characters and therefore are not sorted by
ASCII character (special coding handles this case).
DASL NUMERIC cross-references can have non-numeric
data. No special coding handles this case, therefore,
unexpected results can occur.
o Keep the following in mind when implementing your own
access path routines:
- To avoid local variable name conflicts, begin
variable names with a percent character (%) or a
lowercase character.
- If you want a local variable to be undisturbed
between calls, use the predefined variable RHAND
as a subscript. For example:
"%MYVAR(RHAND)"
4-4 Using DASL with the DEC DB Integrator Gateway for DSM Software
- Your initialization routine is called once before
multiple calls to your order routine. The variable
RHAND is defined.
- Your order routine acts like a virtual order
function:
input variables:
data dictionary name of column assigned a value
data dictionary names of each of the primary keys assigned a value
QUIT = 0
output variables:
the same with new values
The routine is expected to return the next set of
values for the input variables. Set QUIT to 1 when
there are no more input variables. The succeeding
values of the variable representing the access path
column must be ASCII sorted for STRING and NAME data
types, otherwise the values are sorted numerically.
Skipping Records
DASL tables can be set up so that records are skipped at
run time. Use the Data Dictionary Definition Option to
add an output transform for one of the data dictionary
elements that corresponds to one of the table columns.
If the variable NSDSSKIP is set to true by this output
transform, the record in process is skipped.
DASL Setup for Database Updates
Because the DASL database is not actually a relational
database, the desired behavior for an insert, update, or
delete is ambiguous in the case of certain constructs.
A default behavior is executed for each of the following
constructs:
o Table hierarchy
o Pointer fields
o Output transforms
o Indexes
o Counter fields
DASL with the DEC DB Integrator Gateway for DSM Software 4-5
You can override the default behavior for the previous
constructs by writing preaction or postaction MUMPS
routines that you specify in the Define Tables screen (as
shown in Figure 4-1). In many cases, the default behavior
of the original table is unacceptable. You can define new
tables so that the default behavior produces the desired
result.
Entries in the preaction and postaction fields must be in
the following format:
TAG^ROUTINE
Preaction routines are called at run time before any
database updates are performed. Postaction routines are
called after all updates implied by the default behavior
have been performed.
Table Hierarchy
DASL tables can have multiple primary keys and can have
data stored at multiple subscript levels. The default
behavior of the gateway for DSM is to only update or
insert data stored at the last primary key subscript level
and below. The default behavior for deletes is to only
kill at the lowest primary key subscript level.
However, you can write preaction or postaction routines
to override this behavior. Another way to control which
subscript levels are updated is to define separate tables
for the data at each subscript level and only update these
tables, while still using the original table for queries.
Pointer Fields
The default behavior for pointer fields is to ignore
the values provided for these fields in inserts and
updates. However, you can override this behavior by
writing preupdate or postupdate action routines. The
DASL Demo database has a preaction routine that searches
the global that the pointer field is pointing to for
the best match on the relevant field (not desirable in
a production situation). If a preaction routine defines
the variable PTR(I) (where I is the column number of the
pointer field), the field is updated with the value of
PTR(I).
4-6 Using DASL with the DEC DB Integrator Gateway for DSM Software
Another way to update pointer fields is to define a
separate table for update that does not describe the
pointer field as a pointer field. For example, in the
demo database, the CONTACTS table has a COMPANY_NAME field
pointing at the COMPANY table. A CONTACTSU table can be
defined with COMPANY_NAME defined as a nonpointer NUMERIC
field. The COMPANY_NAME of CONTACTSU could be updated with
a COMPANY_ID value (perhaps derived from a COMPANY_NAME
query of the COMPANY table).
Output Transforms
Fields that have output transforms are not updated by
default, generally because these transforms do not have
inverses. You can override this behavior by writing a
preaction routine, or you can define a separate table to
update the raw value.
Indexes
DASL cross-references of type NAME, STRING, and NUMERIC
and others of DEFAULT structure (see the section "DASL
Access Paths") are maintained by the update software
as long as they have been entered in the table's column
definition. You may need to maintain other indexes by
writing preaction or postaction routines.
Counter Fields
To enforce a counter constraint on an insert, create a
preinsert routine that incrementally locks the counter
node and sets a local variable (variable name = data
dictionary name of the field) to the next value, and then
updates the counter node.
DASL with the DEC DB Integrator Gateway for DSM Software 4-7
5
________________________________________________________________
Using FileMan with the DEC DB Integrator Gateway for DSM
Software
This chapter includes information about the following:
o Overview of the FileMan Interface
o Setting Up the FileMan Interface
o Installing the SQL Files
FileMan with the DEC DB Integrator Gateway for DSM Software 5-1
Overview of the FileMan Interface
The data model implied by the FileMan data dictionary
is hierarchical. Files can have subfiles which in turn
can have subfiles, recursively. The supplied interface
routines normalize this data model by presenting each
subfile as a separate table. Internal entry numbers are
presented as table attributes so that subfile tables can
be joined with their parent tables.
Two FileMan files are included with the gateway for DSM:
o SQL PROFILE file
o SQL PRESENTATION file
The SQL PROFILE file is used to define a subset of the
whole database that is used for the SQL database and
to determine for each file whether INSERTs, DELETEs, or
UPDATEs are allowed.
The SQL PRESENTATION file is used to specify the data
types and lengths of certain fields, and to override the
default presentation of pointer fields.
Setting Up the FileMan Interface
To set up FileMan for gateway for DSM access, do the
following:
1. Install the SQL files.
2. Edit the SQL files.
3. Define one or more profiles in initialization routines.
4. Define one or more databases in DSM$INSTALL_SITE.COM.
Installing the SQL Files
To install the SQL files, enter the following:
>D ^%SQLFMS<Return>
The following prompts appear:
SQL PROFILE file number: nnnn \\
SQL PRESENTATION file number: nnnn \\
5-2 Using FileMan with the DEC DB Integrator Gateway for DSM Software
where:
nnnn is the proposed file number
If you press Return, the default file number is used or
you can enter a different number.
Editing the SQL Files
To edit the SQL files choose the following FileMan option
from the FileMan main menu:
1. ENTER OR EDIT FILE ENTRIES
Using the SQL PROFILE File
The SQL PROFILE file does the following:
o Minimizes the time for database attaches
o Provides access control
The SQL PROFILE file allows you to define a named profile.
For each profile, a list of files can be added. For each
file on the list, you can determine whether INSERTs,
UPDATEs, and DELETEs are allowed.
Because all metadata is loaded at database attach time,
loading only a subset is faster. Limiting access to a
subset of all the files for a particular user class is
often desirable. Each separate DEC DB Integrator Gateway
database logical name can be protected by a different
Access Control List (ACL). Pointer fields in files that
are in a particular profile can still access data in files
that are not in the profile.
Using the SQL PRESENTATION File
You can enter a list of fields for each file in the
FileMan database in the SQL PRESENTATION file. You can
do the following for each field:
o Override the default data type
o Override the default length
o Override the default presentation for pointer fields
FileMan with the DEC DB Integrator Gateway for DSM Software 5-3
The main reason to override the default data type is for
computed fields where the data type is not specified. The
default data type for such fields is numeric if the field
length is less than 9; otherwise the default data type is
free text.
The main reason to override the default length is for free
text fields with validation routines where it is difficult
to determine the length from the data dictionary. The
default length of such fields is 255.
The default presentation for pointer fields is to use
(perhaps recursively) the .01 field of the file pointed
to. Specifying the pointer representation as NAKED in the
SQL PRESENTATION file causes the field to be represented
as an integer and the actual pointer value is retrieved
in queries. This value can be used to join a table with a
pointer field to the table that is pointed to.
Creating Initialization Routines
You must create a DSM initialization routine for each
profile that is treated as a separate DEC DB Integrator
Gateway database, and a database logical name must be
defined in DSM$SQL_LOGICALS.COM that uses the name of the
routine. The routine sets the local variable PROFILE to
the name of a profile defined in the SQL PROFILE file. If
the local variable PROFILE is not defined, a PROFILE named
TEST is used.
5-4 Using FileMan with the DEC DB Integrator Gateway for DSM Software
6
________________________________________________________________
User Defined Databases
This chapter includes information about the following:
o Writing Your Own Routines
o Writing Metadata and Data Access Routines
o Debug Flags
User Defined Databases 6-1
Writing Your Own Routines
DSM supplies routines that support access to DASL and
FileMan databases. The metadata and data drivers use
the DSM call interface to call into DSM to get their
information. The actual routine names that are called
are defined in the logical name that defines the database.
You can write DSM routines that allow access to other
metadata repositories and data structures. To do so,
you must implement the routines as described in the
section "Writing Metadata and Data Access Routines" in
this chapter.
The routine name prefix used for the calls is the routine
name prefix defined with the /PATH qualifier in the
database logical name. For the DASL interface, this
routine name is %DANSDS, which is a routine supplied
with DASL. For the FileMan interface, this routine name
is %SQLSFM, which is supplied with the gateway for DSM
software. You can use these supplied routines as examples
of how to write your own routines.
Routine names have the following naming conventions:
o An M is appended to the routine name to form the
routine name that is used for metadata routines.
o A D is appended to the routine name for data access
routines.
o A U is appended for data update routines.
Therefore, %DANSDSM and %SQLSFMM are the routine names for
DASL and FileMan metadata routines, %DANSDSD and %SQLSFMD
are the routine names for DASL and FileMan data access
routines, and %DANSDSU and %SQLSFMU are the routine names
for data update routines.
The drivers call fixed entry points in these routines to
get their information, so any set of routines that provide
the same entry points can be used for an SQL database.
A typical driver function sets some local variables,
executes an entry point, and reads some local variables.
The external data types currently supported in this call
interface are described in Table 6-1.
6-2 User Defined Databases
Table_6-1__Supported_External_Data_Types__________________
Data_Type___Description___________________________________
STRING Uninterpreted - up to string maximum
INT Numeric data (no decimal point allowed) (is
converted to a 32-bit integer)
GFLOAT Numeric with decimal point allowed (is
converted to G_FLOAT)
NUMERIC Leading separate numeric format (is converted
to packed decimal (presented to SQL as scaled
QUADWORD))
DATE ASCII string suitable for $BINTIM system
____________service_______________________________________
For each table, a DBKEY of a fixed length must be
definable and must uniquely determine a row (tuple) for at
least the length of a session (the span of time between
database attach and detach).
For a number of the routines, an optional output parameter
ERRSTR is allowed. If present, the value of ERRSTR is
interpreted as an error message and the routine is assumed
to have failed. For some of the other routines, two
optional output parameters are allowed: ERRSTR and ERRLEV.
For these routines, ERRSTR is interpreted as an error
message and ERRLEV as an error level. An ERRLEV value of 0
is considered as a warning. ERRLEV values greater than 0
are interpreted as a failure.
Writing Metadata and Data Access Routines
The tables in this section describe the routine entry
points that you must supply for the metadata and data
drivers.
Table 6-2 describes the entry points for the metadata
driver.
User Defined Databases 6-3
Table_6-2__Metadata_Access_Routines_______________________
Entry_______ParametersDescription_________________________
INIT Input:
INITIAL DSM routine name
INIT is called at the time the client attaches
to the database. The routine in INITIAL is a
user-definable initialization routine.
FF Output:
DNAM Field name
FDES Field description
FTYP Field type (STRING,DATE,NUMERIC,GFLOAT,INT)
FLEN Field length (for the external data
SCALE type)
Scale factor to use for NUMERIC type
FF (fetch fields) is called repeatedly until
DNAM = NULL. It is used to define global data
fields presented as DOMAINS in SQL. None are
required.
FDR Output:
TABLE Table name
TDESC Table description
DBKLEN Length in bytes of DBKEY
IDXSEG Number of unique index segments
IDXSEG(i) (optional)
Field name of index segment
(optional)
FDR (fetch dictionary relations) is called
repeatedly until TABLE = NULL. It is used to
find out about all relations (tables) in the
database. The subscript of IDXSEG(i) varies
from 1 to the number specified in IDXSEG in
the order that the field appears in the key.
FRF Input:
TABLE Table name
(continued on next page)
6-4 User Defined Databases
Table_6-2_(Cont.)__Metadata_Access_Routines_______________
Entry_______ParametersDescription_________________________
Output:
FNAM Field name
DD Domain name from FF call or null
FTYP ("")
FLEN Field type (STRING,DATE,NUMERIC,GFLOAT,INT)
FDES
SCALE Field length (for the external data
type)
Field description
Scale factor to use for type NUMERIC
type
FRF (find relation fields) is called for each
table defined by FDR calls. It is called
repeatedly for a given table until FNAM =
NULL. This defines the columns for the table
____________and_the_order_in_which_they_appear_in_SQL.____
Table 6-3 describes the entry points for the data driver.
Table_6-3__Data_Access_Routines___________________________
Entry_______Parameters____Description_____________________
INIT Input:
INITIAL DSM routine name
INIT is called at the time the client attaches
to the database. The routine in INITIAL is a
user-definable initialization routine.
QUERYR Input:
TABLE Table name
(continued on next page)
User Defined Databases 6-5
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
Output:
RCARD Number of tuples in relation
RDLEN Record length
RSIZE Number of disk blocks
KNAM Name of primary key
KTYPE Data type of primary key
KLEN Length of primary key (external
KOFF data type)
KHASH Offset of primary key in record
KDUP Hash key (1 = yes, 0 = no)
DBKLEN Duplicate keys allowed (1 = yes,
NAUX 0 = no)
INSERT Length of DBKEY
UPDATE Number of auxiliary keys
DELETE 1 = insertable, 0 = not
KIOEFF insertable
1 = updatable, 0 = not updatable
KSUEFF 1 = deletable, 0 = not deletable
1 if primary key allows
efficient access, 0 otherwise
1 if primary key access path has
quick setup, 0 otherwise
(continued on next page)
6-6 User Defined Databases
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
QUERYR (query relation) is called at the
beginning of a query to gather optimization
information for a table. The RCARD and RSIZE
parameters need not be exact. They are used
to compare tables in the case of joins. The
RDLEN parameter should be exact based on field
length of external type. The KNAM, KTYPE,
KLEN, KOFF, and KHASH parameters are for
whatever you choose to call a primary key.
The NAUX parameter returns the number of other
keys you can use to implement access paths.
If KHASH is true, only access paths with the
predicate = are used for the key. The key is
used for indexed merge joins only if KIOEFF
and KSUEFF are true.
AUXKEY Input:
TABLE Table name
Output:
KNAM Name of the auxiliary key
KTYPE Data type of the auxiliary key
KLEN Length of auxiliary key
KOFF (external data type)
KHASH Offset of auxiliary key in
KDUP record
KIOEFF Hash key (1 = yes, 0 = no)
Duplicate keys allowed (1 = yes,
KSUEFF 0 = no)
1 if auxiliary key allows
efficient access, 0 otherwise
1 if auxiliary key access path
has quick setup, 0 otherwise
(continued on next page)
User Defined Databases 6-7
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
This routine is called NAUX times immediately
after a QUERYR call to gather information
about additional keys. There is no reason to
specify a key of external type GFLOAT, because
NSDS does not support access paths for that
type. You can use all other supported data
types.
BITMAP Input:
RHAND Relation handle (access stream
BITMAP(RHAND) identifier)
Fields required for this access
stream
This routine provides a binary bitmap
indicating which fields (possibly a subset
of all the columns in the table) are required
for a specific access stream. The OUT array
needs to be filled in for these fields only.
Example:
If BITMAP(RHAND)=$C(5)_$C(2), then fields 1,
3, and 10 are required.
DBKLEN Input:
TABLE Table name
Output:
DBKLEN Length of DBKEY
OPEN Input:
TABLE Table name
This routine is called for the first access
stream to the table for a query. Multiple
access streams can exist for the same table
within a query. Separate stream contexts are
maintained for subsequent data access calls by
unique relation handles and DBKEY values.
CLOSE Input:
TABLE Table name
(continued on next page)
6-8 User Defined Databases
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
This routine is called when the last access
stream for a table within a query is released.
SAP Input:
TABLE Table name
RHAND Relation handle
STATE One of RESET, PATH, or KEYS
KOFF Key offset
PATHNUM Path number
KSIZ Key size (in bytes)
KSENUM Sequence number
LB Lower bound
LBOP Lower bound operator (GTR, GTE,
EQL, or STW)
LBSIZ Lower bound size (in bytes)
UB Upper bound
UBOP Upper bound operator (LSS, LSE,
EQL, or STW)
UBSIZ Upper bound size
(continued on next page)
User Defined Databases 6-9
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
SAP (set access path) communicates access
path information for a particular relation
handle within a query. This routine is
called multiple times to convey access path
information for a given table and relation
handle.
It is first called with STATE = RESET. Within
a query the relation handle is unique, but not
necessarily within a session. This call allows
the cleanup of any stale information based on
a relation handle. The only parameters set for
this state are STATE, RHAND, and TABLE.
It is then called with STATE = PATH. It is
called only one time. The parameters for this
call are STATE, KSIZ, and KOFF - identifying
the key to use for the access path.
It is then called multiple (possibly) times
with STATE = KEYS, a separate sequence number
(KSENUM) for each call and upper and lower
bounds for that sequence number. The data
that should be provided by future GETNXT calls
should be a logical or of the data specified
by the separate sequences. The bounds in
each sequence follow the bounds of previous
sequences in the order implied by the key
specified.
Upper bound and lower bound data of type INT
have been converted to ASCII numeric strings.
Numeric (packed decimal) strings need to
be converted (see UNPACK^%DANSDSD (supplied
with DASL) or UNPACK^%SQLSFMD (supplied with
gateway for DSM)).
(continued on next page)
6-10 User Defined Databases
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
DBKGET Input:
TABLE Table name
DBKEY DBKEY value
Output:
OUT(i) Output array
ERRSTR Optional error message string
DBKGET (get tuple by DBKEY) returns a single
table row. This call should not interfere with
any contexts preserved across calls by GETNXT.
GETNXT Input:
TABLE Table name
DBKEY DBKEY value
RHAND Relation handle
Output:
OUT(i) Output array
DBKEY New DBKEY value
ERRSTR Optional error message string
GETNXT (get next tuple) is used to fetch
multiple tuples. It is called repeatedly until
DBKEY is undefined. For the first call, DBKEY
is equal to NULL. For subsequent calls, the
DBKEY value is equal to the last one returned
for this relation handle. If there are no
access paths defined for the relation handle,
this routine typically is called until all
tuples have been delivered.
If access paths have been defined for this
relation handle, only the tuples satisfying
the access path should be returned.
(continued on next page)
User Defined Databases 6-11
Table_6-3_(Cont.)__Data_Access_Routines___________________
Entry_______Parameters____Description_____________________
The OUT(i) array should have a subscript
varying between 1 and the number of columns
in the table, each entry specifying a value
for that column. Each entry's format depends
on the external data type. Only entries that
are specified by the BITMAP call need to be
defined.
TRSTART No parameters
TRSTART is called at the start of a transac-
tion.
COMMIT Output:
ERRSTR Optional error message string
COMMIT is called to commit a transaction.
The presence of the ERRSTR parameter is
interpreted as an indication that the commit
has failed.
ABORT Output:
ERRSTR Optional error message string
ABORT is called to abort a transaction.
The presence of the ERRSTR parameter is
interpreted as an indication that the commit
____________has_failed.___________________________________
Table 6-4 describes entry points that must be implemented
if you declare tables to be writeable. These entry points
must reside in the U routine.
6-12 User Defined Databases
Table_6-4__Data_Update_Routines___________________________
Entry_______ParametersDescription_________________________
INSERT Input:
TABLE Table name
RHAND Relation handle
IN(i) Input array
Output:
ERRSTR Optional error message string
ERRLEV Optional error level
INSERT is called to insert a new row in a
table. The values for the columns are stored
in IN(i), where i is the field number. The
values in IN(i) are ASCII strings with the
exception of fields of type DATE. For these
fields, the IN(i) value is a VMS date-time
stamp. Date and time fields can be converted
to $H format using the DSM %CVMSHOR external
routine.
UPDATE Input:
TABLE Table name
RHAND Relation handle
DBKEY DBKEY value for row to update
IN(i) Input array
Output:
ERRSTR Optional error message string
ERRLEV Optional error level
UPDATE is called to modify an existing row in
a table. The values for the columns are stored
in IN(i) where i is the field number. Date
and time fields can be converted to $H format
using the %CVMSHOR external routine.
DELETE Input:
TABLE Table name
RHAND Relation handle
DBKEY DBKEY value for row to delete
(continued on next page)
User Defined Databases 6-13
Table_6-4_(Cont.)__Data_Update_Routines___________________
Entry_______ParametersDescription_________________________
Output:
ERRSTR Optional error message string
ERRLEV Optional error level
____________DELETE_is_called_to_delete_a_row_from_a_table.
6-14 User Defined Databases
Debug Flags
To assist in debugging access routines, the generic NSDS
debug flags described in the Accessing Nonrelational Data
with SQL manual can be used. Also, there are two DSM
specific debug flags that can be turned on by defining
the logical name NSDS$DSM_DEBUG_FLAGS.
The following example causes a trace of metadata driver
calls to be displayed on SYS$OUTPUT:
$ DEFINE NSDS$DSM_DEBUG_FLAGS C<Return>
The following example causes a trace of data driver calls
to be displayed on SYS$OUTPUT:
$ DEFINE NSDS$DSM_DEBUG_FLAGS F<Return>
The two flags can also be combined:
$ DEFINE NSDS$DSM_DEBUG_FLAGS CF<Return>
User Defined Databases 6-15
_____________________________________________________________
Index
DASL database (cont'd)
________________________ sample logicals for
control defining, 3-3
of, 3-7 setup considerations, 4-5
Control List DASL interface
counter fields, 4-7 ACL
description of, 1-3
indexes, 4-7 access
environment, 3-7 output transforms, 4-7
pointer fields, 4-6 FileMan
database logical name, precompiled subqueries,
5-3 3-6
subqueries, eliminating, logical
table, 3-7 3-7
support for Utility,
gateway for DSM, 4-2
DASL tables________________________
access to, 4-2
default behavior, 4-6 xiii
skipping records, 4-5 Components
Data driver version,
access routines, 6-5
2-17 routine entry points, 6-3
version, 2-17 Data types
fields DASL supported, 4-2
behavior, 4-7 supported, 6-2
Data update routines, 6-13
Database________________________
attaching to, 3-6 access
controlling access, 3-7 4-3
defining for database
gateway for DSM, 3-2 access
3-8
Index-1
Debug flags FileMan interface (cont'd)
description of, 6-15 protecting database
DEC DB Integrator Gateway for DSM logical name, 5-3
description of, 1-1 setting up, 5-2
installing, 2-2 subqueries, eliminating,
setup, 3-2 3-7
using transaction
processing with, 3-8 G
DECdtm services ___________________________
enabling, 3-3 Global protection, 3-7
DECnet/OSI
NET$MANAGE identifier, I__________________________
2-6 Indexes
Documentation default behavior, 4-7
submitting comments, xiii Initialization routines
DSM Application Software creating, 5-4
Library Installation
See DASL interface installing DSM, 2-4
DSM$INSTALL_SITE.COM installing NSDS, 2-20
defining databases, 3-2 installing SQL Services,
defining FileMan database 2-19
, 5-2 overview, 2-2
DSM$SQL_DATABASE_ registering DSM license,
LOGICALS.COM 2-3
defining DASL database, required operating system
3-3 components, 2-2
defining FileMan database
, 3-3 L__________________________
License Management Facility
F__________________________ See LMF
FileMan database LMF
controlling access to, registering DSM license,
3-8 2-3
sample logicals for registering
defining, 3-3 gateway for DSM license
FileMan interface , 2-3
creating initialization Logical name table
routines, 5-4 protecting, 5-3
description of, 1-3 Logical names
installing SQL files, 5-2 defining databases, 3-2
overview of, 5-2 NSDS$DSM_DEBUG_FLAGS,
precompiled subqueries, 6-15
3-6
Index-2
Privileges
M__________________________ required VMS, 2-6
Metadata driver Process quotas
access routines, 6-4 modifying, 3-6
routine entry points, 6-3 Product Authorization Key
See PAK
N__________________________
NET$MANAGE identifier R__________________________
DECnet/OSI, 2-6 RDB$REMOTE account, 2-18
Non-SQL Data Server Routine name
See NSDS conventions, 6-2
NSDS Routines
description of, 1-3 writing your own, 6-2
installing, 2-20
software configuration, S__________________________
1-4 Security
NSDS$DSM_DEBUG_FLAGS See Access control
logical name Servers
creating, 6-15 description of, 3-4
NSDSSKIP variable, 4-5 setting up virtual memory
for, 3-5
O__________________________ SETPRV privilege, 2-6
ODBC Rdb driver SQL files, 5-3
installation and installing for FileMan,
requirements, 2-23 5-2
Output transforms SQL PRESENTATION file
default behavior, 4-7 description of, 5-2
entering fields, 5-3
P__________________________ SQL PROFILE file
PAK, 2-3 creating initialization
PATHWORKS routines, 5-4
SQL Services and ODBC, defining a named profile,
2-24 5-3
Pointer fields description of, 5-2, 5-3
default behavior, 4-6 SQL Services
Postaction routine installing, 2-19
description of, 4-6 PATHWORKS requirements,
Preaction routine 2-24
description of, 4-6 setting up, 3-4
Precompiled subqueries SQL$CLUSTER_DEINSTALL60
description of, 3-6 command procedure, 2-18
Index-3
SQL$DEINSTALL_DELETE
command procedure, 2-17 U__________________________
SQLSRV$CONFIG.DAT file User-defined interface
controlling access to description of, 1-3, 6-2
database, 3-7
setting up servers, 3-4 V__________________________
System setup VA FileMan interface
gateway for DSM software,
3-2 See FileMan interface
Virtual memory
T__________________________ setting quotas for, 3-5
Transaction processing VMS Authorize Utility, 2-6
using DEC DB Integrator Gateway forVDSMprivileges
required, 2-6
with, 3-8 VMSINSTAL command procedure
, 2-6
VMSLICENSE.COM, 2-3
Index-4