SQL Connection and xfODBC on Windows

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

Understanding SQL OpenNet on Windows

On Windows, you can run SQL OpenNet with either the vtxnetd program (which is the default) or the vtxnet2 program. Both are started by the service program sqld. The one you should use depends on the data source you need to connect to. For program syntax, see vtxnetd and vtxnet2 programs.

The vtxnetd program

The vtxnetd program is a multi-threaded server, which listens on the selected port and starts threads to service the connection requests and perform the requested work. The driver name (e.g., vtx12_SQLNATIVE) in the connect string determines the DLL that vtxnetd attaches to its worker thread to service the connection. You can use vtxnetd in the following circumstances:

Using the multi-threaded vtxnetd reduces start-up overhead on initial connections and has a small performance advantage over the multiple program approach of vtxnet2 (see below). If a connection request is made via vtxnetd to a driver that does not support multi-threading, the connection is rejected.

The vtxnetd program offers the ability to specify how existing connections are handled when SQL OpenNet is shut down, which vtxnet2 does not support. See Stopping and removing SQL OpenNet.

The vtxnet2 program

The vtxnet2 program is a listener daemon, which listens on the requested port and creates child processes to service client requests. The driver name portion of the connect string (e.g., !vtx12_SQLNATIVE) determines the server executable and its associated DLL, which are used to create the process to service the connections. To use vtxnet2, you must edit the opennet.srv file; see Customizing the opennet.srv file.

The sqld program

The service program sqld starts either the vtxnetd or the vtxnet2 program, and then polls the program periodically to verify that it is still running. The service name for sqld is SynSQL, and the display name is “Synergy/DE OpenNet Server”. Sqld reads the opennet.srv file, which contains the daemon start-up commands and parameters as well as needed environment variable settings. See sqld program and Customizing the opennet.srv file for more information.

SQL Connection and xfODBC: using SQL OpenNet

To use SQL OpenNet with SQL Connection or xfODBC, you need to do the following on your SQL OpenNet server machine:

Specifying the port number

We recommend setting the port number for SQL OpenNet in the vtxnetd or vtxnet2 command in the opennet.srv file on your server. See vtxnetd and vtxnet2 programs. You can also set it in your system's services file. SQL OpenNet will use the port setting in the services file if the vtxnetd or vtxnet2 command does not specify a port.

To set the port number in the services file (in %windir%\system32\drivers\etc), add this line to the file:

vtxnet nnnn/tcp #Synergy/DE SQL OpenNet

where nnnn is the port number.

Important

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

Make sure the port number that clients use for SQL OpenNet matches the port number used by vtxnetd or vtxnet2. For information on client port settings for SQL Connection, see Configuring SQL Connection (client) (for Windows); SQL Connection: configuring client or stand-alone access (for Unix); or SQL Connection: configuring and testing client or stand-alone access (for OpenVMS). For xfODBC, see Setting up access with DSNs.

Registering, starting, and testing SQL OpenNet

SynSQL must be started by a user with administrator privileges.

1. Before starting SQL OpenNet, you must first register the SynSQL service. Do one of the following: 
sqld -r
Note

You can register and start the SynSQL service in one step with the sqld -rs option. See sqld program for complete sqld syntax.

2. There are several ways to start SynSQL. Do one of the following: 
net start synsql
3. To verify that the server is running, at a command prompt run vtxping: 
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, the port specified in the services file is used. See vtxping utility.

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. You can check the Windows event log for additional information. You may want to start sqld with the -l option for more detailed logging; see sqld program.

SQL OpenNet is now configured. For SQL Connection, you must license your database drivers before you can run the example programs. See Testing SQL Connection (client or stand-alone) for information on the example programs. See Synergy/DE Licensing for licensing information.

Stopping and removing SQL OpenNet

You must stop the service before you can remove (unregister) it. When you stop SQL OpenNet, no new connections can be made. The behavior of existing connections depends on whether you are using vtxnetd or vtxnet2:

To change this behavior such that existing connections are not terminated, remove the -w option from the command line in opennet.srv. This is not recommended, as the vtxnetd program will not completely shut down until all child processes are terminated. See Shutting down vtxnetd on Windows (-w) for details.

To stop and remove SQL OpenNet, 

sqld -x

To stop (but not remove) SQL OpenNet, do one of the following:

sqld -q

net stop synsql

To remove SQL OpenNet, do one of the following:

sqld -x

Customizing the opennet.srv file

The opennet.srv file, located in synergyde\connect, is read by sqld. It contains the command line that is used to start vtxnetd or vtxnet2. By editing that command line, you can change the port, run multiple servers, and so on. You can also define environment variables in the opennet.srv file.

Note

The opennet.srv file is not overwritten when you upgrade Connectivity Series, nor is it removed when you uninstall. We distribute a file named opennet_base.srv (also located in synergyde\connect), which contains default settings and can be used as a reference.

See below for information on using vtxnet2 instead of the default vtxnetd, defining environment variables, caching, and changing the polling interval. For information on the following, see the vtxnetd and vtxnet2 program syntax in vtxnetd and vtxnet2 programs:

Using vtxnet2

The default program, vtxnetd, is a multi-threaded server that generally performs better than vtxnet2. However, you can use vtxnet2 when you want SQL OpenNet to be a listener daemon that creates child processes rather than a multi-threaded server. The start-up lines for both vtxnetd and vtxnet2 are included in the opennet.srv file. To use vtxnet2, just comment out the vtxnetd line and enable the vtxnet2 line. See Understanding SQL OpenNet on Windows for additional information about vtxnetd and vtxnet2.

Defining environment variables

Any environment variables used by SQL OpenNet, such as those that specify the location of your data files, can be defined in the opennet.srv file. Define environment variables towards the beginning of the file, before the vtxnetd or vtxnet2 start-up line, using the following syntax:

env_variable=setting

Caching

You can enable system catalog caching for xfODBC by editing the “syngenload” line in the opennet.srv file. For more information, see Improving performance with system catalog caching.

Changing the polling interval

By default, the sqld program polls (checks) vtxnetd/vtxnet2 every 10 minutes to verify that it is still running. If vtxnetd/vtxnet2 stops unexpectedly, users will be unable to connect, and yet no error will be recorded in the Windows event log (nor will the status of the SynSQL service change in the Component Services dialog box) until the next poll takes place. Consequently, you may want a shorter polling interval, so that should vtxnetd/vtxnet2 stop unexpectedly, sqld will report the event promptly in the Windows event log.

To change the polling interval, in the opennet.srv file, remove the comment (#) at the beginning of the OPENNET_POLL_TIME line and specify the desired polling interval in milliseconds. For example, to set the polling interval to one minute, you’d enter

OPENNET_POLL_TIME=60000   

SQL Connection: configuring client or stand-alone access

This section describes how to configure an SQL Connection client and how to test your client/server or stand-alone configuration.

Configuring SQL Connection (client)

To connect an SQL Connection program to an SQL OpenNet server, you must specify the server name in the connect string, and you must specify the port number for SQL OpenNet in one of the following:

To specify a port number for SQL OpenNet in the TCP/IP services file, add the following line to this file, which is in %windir%\system32\drivers\etc.

vtxnet nnnn/tcp #Synergy/DE SQL OpenNet

where nnnn is the port number.

To test that you can connect to an SQL OpenNet server, run vtxping:

vtxping [-pport] server_name

If the network connection is working properly, 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.

Testing SQL Connection (client or stand-alone)

1. Set the SQL_CONNECT environment variable to specify a connect string. 
net:connect_string@[port:]host!driver_name

where connect_string contains the driver-specific information to be passed to the database driver, port is the port number on which SQL OpenNet is running, host is the server system’s unique name, and driver_name is the driver name (e.g., vtx12_SQLNATIVE).

The syntax for connect_string depends on the driver. For a list of driver names and the connect syntax to use with them, see Building connect strings.

For example, to connect to an SQL Server database on a remote Windows machine, you might enter

set SQL_CONNECT = net:user_name/manager/mydsn@1958:win_serv!vtx12_SQLNATIVE

driver:connect_string

where driver is the name (e.g., vtx12_SQLNATIVE) of the database driver, and connect_string contains the required information to be passed to the database driver. The syntax for connect_string depends on the driver. See Building connect strings for complete information.

For example, to connect to an Oracle 11 database, the connect string consists of the user ID and password. So, you might enter

set SQL_CONNECT = vtx0_11:scott/tiger
2. Go to the synergyde\connect\synsqlx directory to compile, link, and run the example programs. There are several example programs (exam_create_table, exam_fetch, etc.), which are used to test your connection and set-up. Run exam_create_table first: it creates a table, which is then used by the other example programs.

For example:

dbl exam_create_table
dblink exam_create_table
dbr exam_create_table

If this test is unsuccessful, note the error(s) and refer to Error Logging and Messages. For more information about the example programs, see Writing an SQL Connection program.

Your SQL Connection configuration is now complete. See the SQL Connection Reference and the SQL Connection release note entries for the current version for more information about using SQL Connection.

xfODBC: testing the network connection for client access

After configuring SQL OpenNet on the server and installing Connectivity Series or xfODBC Client on the client, you should test the network connection before attempting to access data using xfODBC. To do this, run vtxping on the client machine:

vtxping [-pport] server_name

If the network connection is working properly, 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.

We recommend that you complete the tutorial to ensure that you can connect to a database and to learn more about using xfODBC. See Using the Sample Database As a Tutorial.

xfODBC: stand-alone access

After installing Connectivity Series, we recommend that you complete the tutorial to ensure that you can connect to a database and to learn more about using xfODBC. See Using the Sample Database As a Tutorial.