System 1032/ODBC Interface Under the Hood By Tym Stegner
In several previous CCAPRINT articles I addressed the usefulness and uses of the System 1032/ODBC Driver. In this article I describe how the System 1032 ODBC interface works to address some of the questions that have come in from customers. The discussion traces all operations of an ODBC connection, including references to System 1032 and S1032ODBC logical names, resources, and files.
Introducing ODBC ODBC, the acronym for Open DataBase Connectivity, is a standard adapted by Microsoft to facilitate communication between PC applications and server databases. ODBC itself is an API that enables an application to connect to a database and transfer data back and forth.
Examining ODBC Components The System 1032/ODBC Driver has components for the client and the server. On the client side, it consists of the ODBC driver and ancillary files. This driver is installed on the PC, making the ODBC driver available to the Microsoft ODBC Data Administrator control panel application under the Windows operating system.
Using the ODBC Data Administrator, you can create an object called a data source (DSN) to act as a pointer to data on a server. The significant contents of the DSN object are a local name, a resource name, a server name, and a port identifier on that server.
On the server side, the components consist of command procedures and the executable ODBC server software. Some of the command procedures initialize the proper environment for running the ODBC server application and others drive the execution process.
As part of installation, the installer creates a version-specific subdirectory that contains three sub-directories:
Directory SYS$SYSDEVICE: [S1032.ODBC.V041] AXP.DIR !or VAX.DIR DEMO.DIR LIB.DIR
The AXP (or VAX) directory contains the hardware-specific images necessary to operate the System 1032/ODBC Driver:
Directory SYS$SYSDEVICE:[S1032.ODBC.V041.AXP]
ODBC_DICTIONARY.EXE !dictionary program S1032ODBC3.EXE !server executable S1032ODBCSHR3_V61.EXE !context switcher pre-VMS V71 S1032ODBCSHR3_V71.EXE !context switcher post-VMS V71
A demonstration directory contains sample datasets and examples to run ODBC stored procedures.
Directory SYS$SYSDEVICE:[S1032.ODBC.V041.DEMO]
Lastly, a library area contains the configuration and control command files:
Directory SYS$SYSDEVICE:[S1032.ODBC.V041.LIB]
CONFIG_NET_?.COM !TCP/IP-stack-specific config file CREATE_IVP_FILES.COM !re-creates demonstration files ODBC.TEMPLATE !source for ODBC.INI S1032ODBC.COM !execution command file S1032ODBC.HLB !dictionary utility help file S1032_ODBC_V041_?.COM !Server environment config file
A specially authorized user account is also required for ODBC server operations--not for installation, but to run the ODBC image and to authenticate the login by the user of the connection. The ODBC installation process does not automatically create this account. CCA recommends that you do not use the SYSTEM account for this account, but instead create a new account for ODBC server operations.
Once the authentication account is created, according to the directions in the System 1032/ODBC Driver Installation and Maintenance Guide, and the ODBC software is installed, the ODBC driver configuration command procedures are run either automatically by the installation process or manually by you.
The first file run is the System 1032 ODBC configuration file: S1032_ODBC_V041_<node>. (The file is created during installation, and the file name reflects the installed version of ODBC, as well as the SCSNODE name of the CPU installed upon.) This configuration command file defines the necessary System 1032/ODBC logical names into the system logical name table:
S1032ODBC_LIB !ODBC command files area S1032ODBC_DEMO !demonstration files area S1032ODBC_IMAGE !image area S1032ODBCSHR3 !context switch image designator S1032ODBC_DBGFILES !debugging switch; initially FALSE S1032ODBC_INIPATH !authentication account default directory S1032ODBC_ERR !ODBC server error log file
TCP/IP Connectivity After the required ODBC logical names are defined, the next configuration step sets up the TCP/IP port where the ODBC server receives connection requests. This step uses the CONFIG_NET_?.COM file--the TCP/IP-specific command file also created during ODBC installation based on the current TCP/IP stack on the installed node. This command file takes care of all necessary tasks to define a TCP/IP service to receive connection requests.
Any VMS system running a TCP/IP stack has a high-level listener program that monitors all TCP/IP ports on the system. When a connection request is detected upon a particular port, this listener program runs the defined service for that port.
In the case of System 1032/ODBC Driver, the S1032SRVMGR service is defined to create a process under the authentication account, and run the S1032/ODBC execution command file.
Configuring a Data Source Before you can make connections using the ODBC Driver, you must complete two tasks. First, create one or more dictionaries, using the ODBC_DICTIONARY.EXE program found in the S1032ODBC_IMAGE directory. This utility is the dictionary manager that is used to add and delete tables and procedures that can be accessed via the client application.
Second, and ancillary to creating the dictionary, you must update the ODBC.INI file in the S1032ODBC_INIPATH directory. The ODBC.INI file is the server directory relating the PC-based DSNs to the dictionaries on that server.
Each entry in the ODBC.INI file begins with a dictionary resource name, followed by a directory path and file specification for one dictionary. Other options for the entry include definitions for scratch areas and optional debugging switches.
If the S1032ODBC_DBGFILES logical is TRUE, SYS$OUTPUT is defined to S1032ODBC.OUT, SYS$ERROR is defined to S1032ODBC.ERR, and S1032_ODBC_MESSAGE is defined to DEBUG.
Note: each distinct ODBC connection request creates a new OpenVMS process, each running its own copy of the System 1032/ODBC Driver. Summary This article has traced the execution steps required every time a user makes a connection using System 1032/ODBC Driver. Once the product has been installed and configured, there still remains a lot of processing that must be accomplished to fulfill the connection request and subsequent data transfer operations. While most of the actual ODBC processing is itself invisible to the user, it is still helpful to understand the infrastructure of how the System 1032/ODBC Driver components interact to serve ODBC requests.
Insight 204
Announcing Insight 204 Symposium for 2005 By Marie Kelly, Director of Marketing
We are pleased to announce the dates of the next Insight 204 Symposium. The event will be held June 5-8, 2005 in Boston at the Hyatt Harborside Hotel. Mark your calendars now so that you won't miss this unique opportunity to learn all about the new release of Model 204 due out next spring. As always, registration for the Insight 204 Symposium is FREE!
We will be providing details over the next couple of months including registration information, room reservation guidelines, and session topics. If you have any questions that need to be answered immediately, please submit them using a CCA Feedback Form.
Thank you for your continued interest in maximizing your use of Model 204! We look forward to seeing you in Boston next year!
Suspending Users with STOPUSER By James Damon
Suppose you, as system manager or system administrator, are handed urgent work that needs a dedicated Online for the next 15 to 30 minutes. Do you issue a BROADCAST URGENT command requesting users to refrain from all activity? Do you issue an EOD ON command, to prevent new users from logging in? Do you set everyone’s priority to low and give the urgent work high priority using the PRIORITY command? Or, do you resort to the draconian measure of bumping some users or bumping all users? Is there a better alternative?
There are several situations where you, in attempting to monitor and control the workload in an Online system, require slightly more finesse and flexibility than the BUMP or BUMP ALL command. Suppose you noticed what you suspect to be a looping user. You’re not sure, but the MONITOR SL command shows the user consuming large amounts of CPU although performing very little database I/O. Or, maybe the request is consuming large amounts of CPU and performing a large number of database I/Os. Other users are complaining that response time is terrible and they cannot complete their important tasks. The suspect user has been running for 30 minutes. Do you bump the suspect user with the resulting loss of 30 minutes of potentially useful work? Or, is there an alternative?
The STOPUSER (also, STOPU) command lets you suspend a particular user for an indefinite period of time and then, at a time of your choosing, you can release that user to resume processing with no loss of work. You can suspend multiple users with multiple STOPU commands, one user per command.
How Suspension Works The STOPU username ON command schedules the user for suspension and displays the following message to the system manager:
STOPU USER12 ON*** M204.3274: USER SCHEDULED FOR STOP
The command takes affect only at certain points in a User Language request, such as:
When the command does take affect, the user is suspended and enters a bumpable, swappable wait. No work is lost, no transactions are backed out, all record sets and record locks are maintained. The user remains logged in but suspended until the system manager issues the STOPU username OFF command. When the STOPU command takes effect, the following message is sent to the suspended user:
*** M204.2375: SYSTEM MANAGER HAS STOPPED YOU; ANSWERING “YES” TO THE FOLLOWING MESSAGE WILL HAVE NO EFFECT UNTIL THE SYSTEM MANAGER TURNS STOP OFF*** M204.1076: DO YOU REALLY WANT TO CONTINUE?
If the user answers N to this message, the user’s request ends. Any active transactions are backed out. The user returns to command level.
Reactivating the Suspended User When you determine that the suspended user may resume evaluation of the request, you issue a STOPU username OFF command. The following message is issued to the system manager:
STOPU USER 12 OFF*** M204.2420: USER SCHEDULED FOR START
However, this does not automatically cause the request to resume evaluation. Only when the user responds to the M204.1076 message with Y, does evaluation of the request resume.
Note: Due to the nature of the scheduler algorithm, the STOPU command as well as the BUMP command, do not always take effect immediately. The same is true regarding the M204.1076: DO YOU REALLY WANT TO CONTINUE prompt for limit settings such as MCPU, MDKWR, and MDKRD. Those limits may be exceeded to some degree before the prompt is issued. Generally, if there are no other users requiring service, each of these actions may be delayed since there is, in some sense, no urgent reason to act immediately. No other users need resources so the target user runs a little longer. In these cases the delay will be slight and virtually unnoticeable.
Requirements for Using the STOPU CommandRequirements for Using the STOPU Command No CCAIN parameters are required to enable the STOPU command. It is available to all system managers and system administrators.
