SQL Connection and xfODBC on OpenVMS

This topic explains how to configure SQL Connection and xfODBC for remote use with SQL OpenNet and how to configure SQL Connection for local (stand-alone) use.

To run SQL Connection in a client/server configuration, see SQL Connection: using SQL OpenNet for server set-up and SQL Connection: configuring and testing client or stand-alone access for client set-up.
To run SQL Connection stand-alone, see SQL Connection: configuring and testing client or stand-alone access. (Stand-alone is defined as any connection that doesn’t use SQL OpenNet.)
To run xfODBC in a client/server configuration, see xfODBC: using SQL OpenNet for OpenVMS server set-up and xfODBC: testing the network connection for client access for Windows client set-up.

For all configurations, see Quotas for SQL OpenNet. You may need to change these options in STARTNET.COM.

To use Connectivity Series products, you must execute the command file SYS$MANAGER:SYNERGY_STARTUP.COM, which executes SYS$MANAGER:CONNECT_STARTUP.COM to set up the environment for Connectivity Series. SYS$MANAGER:SYNERGY_STARTUP.COM should be added to your OpenVMS system start-up procedure; refer to the installation instructions that came with your distribution (they are also available on the Synergex website) for more information.

Understanding SQL OpenNet on OpenVMS

SQL OpenNet runs as the vtxnetd detached process, which listens for requests and starts child processes to service requests from remote clients. Each database driver consists of VTXn.EXE and an associated shared image, VTXn_SO.EXE. The driver name (e.g., VTX0) in the connect string determines the executable file and its associated shared image that the service manager uses to perform the actual servicing of requests. It uses TCP/IP for communication.

See vtxnetd and vtxnet2 programs for more information.

SQL Connection: using SQL OpenNet

To use SQL OpenNet for SQL Connection access, you need to

configure and build SQL OpenNet for SQL Connection. SQL OpenNet includes two prebuilt database drivers: VTX3_SO.EXE, which is used for network connections, and VTX4_SO.EXE, which is used for Synergy database (RMS) connections.
start SQL OpenNet.

Configuring and building SQL OpenNet for SQL Connection

Install Connectivity Series from your distribution and run the command procedure SYS$MANAGER:SYNERGY_STARTUP.COM (see the note above).

Use SET DEF to move to the location of the SQL OpenNet directory. For example:

$ SET DEF CONNECTDIR:

Build the SQL Connection database driver corresponding to the program that your application will specify in the connect string. For example, for Oracle you would build the driver VTX0_SO.EXE. If your client application will be connecting to a Synergy database, you do not need to build a driver. Instead, use the prebuilt database driver VTX4_SO.EXE.

$ @BUILD_SSQL_DB rdbms

where rdbms is RDB7 or ORACLEnn (e.g., ORACLE12).

If you are building an SQL Connection database driver for an Oracle driver, the Oracle Call Interface (OCI) must be installed.

To set the port number, modify the vtxnetd line in NET.COM. For example, the following specifies port 1960:

$ VTXNETD -p1960

If you don't specify a port in the vtxnetd command, SQL OpenNet will use port 1958. For details on the vtxnetd command line options, see vtxnetd and vtxnet2 programs.

Make sure the port you specify for SQL OpenNet isn't used by anything else on the system.

Make sure the port number that clients use for SQL OpenNet matches the port number used by vtxnetd. For information on client port settings, see SQL Connection: configuring and testing client or stand-alone access.

Starting SQL OpenNet for SQL Connection

If you use database logicals, be sure to define them in your system start-up command procedure before starting SQL OpenNet.

Use SET DEF to move to the location of the SQL OpenNet directory, and then execute the SQL OpenNet start-up command file. For example:

$ SET DEF CONNECTDIR:
$ @STARTNET

We recommend starting SQL OpenNet from your OpenVMS system start-up file.

Verify that the server is running:

$ VTXPING [-pport] server_name

where port is the port number that the server is running on and server_name is the host name of the SQL OpenNet machine. If no port is specified, 1958 is used.

If the server starts successfully, you’ll receive a “vtxnetd is alive and kicking” message. If there’s a problem, you’ll see an error message. See vtxping utility for more information. For SQL Connection, you must license your database drivers before you can run the example programs. See Synergy/DE Licensing for licensing information.

Stopping SQL OpenNet for SQL Connection

To stop SQL OpenNet, use vtxkill. Running vtxkill disables new connections, but does not terminate existing processes. Vtxnetd stops only when all children have terminated. The syntax is

$ VTXKILL [-pport] server_name

where port is the port number that the server is running on, and server_name is the host name of the SQL OpenNet machine. If no port is specified, 1958 is used.

SQL Connection: configuring and testing client or stand-alone access

After you install Connectivity Series and run SYS$MANAGER:SYNERGY_STARTUP.COM, no additional configuration is necessary for client access to a remote database; start with step 2 below to test your installation. For stand-alone access, you will need to build the driver before testing the installation.

(Stand-alone access only) Build the SQL Connection database driver that your application will use following the instructions in step 3 above.

Set the SQL_CONNECT environment variable to specify a connection string. The connection string varies depending on the set-up.

For example:

For an OpenVMS client to a Windows, UNIX, or OpenVMS server, use a string similar to

$ DEFINE SQL_CONNECT net:uid/pwd[/db]@[port:]server_name!driver_name

For stand-alone, use a string similar to

$ DEFINE SQL_CONNECT driver_keyword:uid/pwd

where uid is an RDBMS log-in user ID, pwd is an RDBMS log-in password, db is the name of the database (for SQL Server), port is the port number on which SQL OpenNet is running, server_name is your server machine’s unique name, driver_name is the driver name (e.g., VTX0), and driver_keyword is the keyword for the driver (e.g., oracle). For more information, see Building connect strings.

Compile, link, and run the example programs. There are several example programs (EXAM_CREATE_TABLE, EXAM_FETCH, etc.) located in the directory DEVICE:[SYNERGYDE.CONNECT.SYNSQLX]. These programs are for use with MySQL, Oracle, SQL Server, and Synergy databases. Run EXAM_CREATE_TABLE first: it creates a table, which is then used by the other example programs.

For example:

$ DBL EXAM_CREATE_TABLE
$ LINK EXAM_CREATE_TABLE, sys$share:ssqlrtl/opt
$ RUN EXAM_CREATE_TABLE

For more information about the example programs, see Writing an SQL Connection program.

Use the link options file ssqlrtl.opt (instead of synrtl.opt) to link an SQL Connection application.

Test the SQL Connection runtime image by running your SQL Connection application and trying to access data from the target database. If this test is unsuccessful, note the error(s) returned and refer to Error Logging and Messages.

See the SQL Connection Reference and the release notes, REL_CONN.TXT, for more information about using SQL Connection.

xfODBC: using SQL OpenNet

To use SQL OpenNet for xfODBC access, you need to

configure SQL OpenNet for xfODBC (optional).
start SQL OpenNet.

Configuring SQL OpenNet for xfODBC

Follow these instructions to set the port number that SQL OpenNet uses for RMS file access. If you want to use the default port (1958), you do not need to read this section; go directly to Starting SQL OpenNet for xfODBC.

If you have not logged out between installing Connectivity Series and configuring SQL OpenNet, the SQL OpenNet start-up command procedure is already running, and you can skip step 1.

Run the SQL OpenNet start-up command procedure:

$ @SYS$MANAGER:CONNECT_STARTUP

Use SET DEF to move to the location of the SQL OpenNet directory. For example:

$ SET DEF CONNECTDIR:

To set the port, modify the vtxnetd line in the input file NET.COM. For example, the following specifies port 1960:

$ VTXNETD -p1960

If you don't specify a port in the vtxnetd command, SQL OpenNet will use port 1958. For details on the vtxnetd command-line options, see vtxnetd and vtxnet2 programs.

Make sure the port you specify for SQL OpenNet isn't used by another program on the system.

Make sure the port number that clients use for SQL OpenNet matches the port number used by vtxnetd. For information on client port settings, see Setting up access with DSNs.

Starting SQL OpenNet for xfODBC

Use SET DEF to move to the location of the SQL OpenNet directory, and then execute the SQL OpenNet start-up command file. For example:

$ SET DEF CONNECTDIR:
$ @STARTNET

Verify that the server is running:

$ VTXPING [-pport] server_name

where port is the port number that the server is running on, and server_name is the host name of the SQL OpenNet machine. If no port is specified, 1958 is used.

If the server starts successfully, you’ll receive a “vtxnetd is alive and kicking” message. If there’s a problem, you’ll see an error message. See vtxping utility for more information.

Stopping SQL OpenNet for xfODBC

To stop SQL OpenNet, use vtxkill. Running vtxkill disables new connections, but does not terminate existing processes. Vtxnetd stops only when all children have terminated. The syntax is

$ VTXKILL [-pport] server_name

where port is the port number that the server is running on, and server_name is the host name of the SQL OpenNet machine. If no port is specified, 1958 is used.

Quotas for SQL OpenNet

The following options are the same as those used by the OpenVMS RUN command. (The corresponding OpenVMS names are shown in parentheses below.) Because vtxnetd is a detached process, the defaults for these options would normally come from the PQL_D* SYSGEN parameters; however, we override those defaults during installation. The PQL_M* SYSGEN parameters, if larger, supersede any that we set. Any options not specifically mentioned below still use the PQL_D* SYSGEN parameters.

The installation sets the default values for these options (except for /PAGE_FILE and /PRIORITY) in STARTNET.COM file. If you specify a value lower than the default, the default will be used instead. Note that if you upgrade from a version with different (likely lower) values, the installation will copy those values to the new STARTNET.COM instead of setting the current defaults. But it will still enforce the defaults as the minimum values. (/PAGE_FILE and /PRIORITY do not get copied to the new file on an upgrade.)

To see these quotas and the current amount free, run the command SHOW PROC/CONT/ID=vtxnetd_process_id and press Q. For more information on quotas, see Synergex KnowledgeBase article 1477.

/BUFFER_LIMIT=nnn

(BYTLM) Specify the maximum amount of memory in bytes that an SQL OpenNet process tree can use for buffered I/O operations. The default is 256000.

/ENQUEUE_LIMIT=nnn

(ENQLM) Specify the maximum number of locks that an SQL OpenNet process tree can have outstanding at any one time. The default is 16000.

/EXTENT=nnn

(WSEXTENT) Specify the maximum to which an SQL OpenNet process tree may increase its physical memory. The default is 14366.

/FILE_LIMIT=nnn

(FILLM) Specify the maximum number of files that an SQL OpenNet process tree can have open at any one time. The default is 512. The CHANNELCNT SYSGEN parameter may need to be increased. We recommend using MIN_CHANNELCNT in MODPARAMS.DAT in conjunction with AUTOGEN to set this.

/MAXIMUM_WORKING_SET=nnn

(WSQUOTA) Specify the maximum to which an SQL OpenNet process tree may increase its working set size. The default is 8192.

/PAGE_FILE=nnn

(PGFLQUOTA) Specify the maximum number of pages of virtual memory that an SQL OpenNet process tree can allocate. The default is 164593.

/PRIORITY=nnn

Specify the priority level at which an SQL OpenNet process tree runs. The default is 4.