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). Indicates that you press the Return key on the terminal keyboard. Indicates that a space must separate components of a command or command line. 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 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] ? Y 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 /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 /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.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 $ @SYS$MANAGER:SQL$SHUTDOWN 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, is the Common Component version shipped with the client, and 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 * Enter installation options you wish to use (none): 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 . . . 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 * Enter installation options you wish to use (none): 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 * Would you like to install the NSDS BASE kit [NO]? YES * Would you like to install the sample drivers [NO]? 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 ***************************************************************** 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 * Do you want to purge files replaced by this installation [YES]? ***************************************************************** The full IVP cannot be run. A minimal IVP may be run instead. ***************************************************************** * Do you want to run the IVP after the installation [YES]? ***************************************************************** 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 LMCP> CREATE LOGFILE filespec [qualifier...] 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 For versions of Rdb later than Version 4.0, the syntax for attaching to databases is as follows: ATTACH 'FILENAME ' If you are writing programs that use embedded SQL, use the following syntax with Rdb Version 4.0: DECLARE SCHEMA FILENAME 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) 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) 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 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 The following example causes a trace of data driver calls to be displayed on SYS$OUTPUT: $ DEFINE NSDS$DSM_DEBUG_FLAGS F The two flags can also be combined: $ DEFINE NSDS$DSM_DEBUG_FLAGS CF 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