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.

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

Configuring and building SQL OpenNet for SQL Connection

1. Install Connectivity Series from your distribution and run the command procedure SYS$MANAGER:SYNERGY_STARTUP.COM (see the note above ).
2. Use SET DEF to move to the location of the SQL OpenNet directory. For example:
$ SET DEF CONNECTDIR:
3. 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 one of the following: ORACLE10, SYBASE‑DB, RDB7.

Note

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

4. (optional) To change the default port number (1958), modify the vtxnetd line in NET.COM. For example, to use port 1960 you’d enter
$ VTXNETD -p1960
Note

When a client connects to SQL OpenNet, the client application specifies the port number it will use. If you change the default port in NET.COM, you must also change the port settings in the connect strings on all clients (see SQL Connection: configuring and testing client or stand‑alone access). For details on the vtxnetd command‑line options, see vtxnetd and vtxnet2 programs.

Starting and stopping SQL OpenNet for SQL Connection

Starting SQL OpenNet

Note

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

1. 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.

2. 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 Configuring License Manager for licensing information.

Stopping SQL OpenNet

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, the default port specified in the NET.COM file 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 first build the driver before testing the installation.

1. (Stand‑alone access only) Build the SQL Connection database driver that your application will use following the instructions in step 3 above.
2. Set the SQL_CONNECT environment variable to specify a connection string. The connection string varies depending on the set‑up.

For example:

$ DEFINE SQL_CONNECT net:uid/pwd[/db]@[port:]srv_name!driver_name
$ 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 (required for non‑default port), srv_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.

3. 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.

Note

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

4. 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

Configuring SQL OpenNet for xfODBC

Follow these instructions to change the default 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 and stopping SQL OpenNet for xfODBC.

Note

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.

1. Run the SQL OpenNet start‑up command procedure:
$ @SYS$MANAGER:CONNECT_STARTUP
2. Use SET DEF to move to the location of the SQL OpenNet directory. For example:
$ SET DEF CONNECTDIR:
3. To change the default port (1958), modify the vtxnetd line in the input file NET.COM. For example, to use port 1960 you’d enter
$ VTXNETD -p1960
Note

When a client connects to SQL OpenNet, the client application specifies the port number it will use. If you change the default port in the input file, you must also change the port number settings in the connect strings on all clients to match; see Setting up access with DSNs. For details on the vtxnetd command‑line options, see vtxnetd and vtxnet2 programs.

Starting and stopping SQL OpenNet for xfODBC

Starting SQL OpenNet

1. 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
2. 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

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.