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.
- To run SQL Connection in a client/server configuration, see SQL Connection and xfODBC: using SQL OpenNet for server set-up and SQL Connection: configuring client or stand-alone access for client set-up.
- To run SQL Connection stand-alone, see SQL Connection: configuring 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 SQL Connection and xfODBC: using SQL OpenNet for server set-up and xfODBC: testing the network connection for client access for client set-up.
- To run xfODBC stand-alone, see xfODBC: stand-alone access.
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:
- Oracle and Synergy databases
- SQL Server databases when accessed with the vtx12_SQLNATIVE driver
- Certain other databases when accessed with the vtx11 (ODBC) driver
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:
- Specify the SQL OpenNet port number.
- Start and test the server.
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.
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: |
- Start the Synergy Configuration Program (from Windows Control Panel, select Synergy Control Panel > Synergy Configuration Program), go to the Connectivity Series tab, and click the Add Service button. (If the Add Service button is disabled, either sqld.exe is not installed or the service is already registered. If the latter, the Start Service button will be enabled.)
- At a command prompt enter
sqld -r
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: |
- In the Synergy Configuration Program, go to the Connectivity Series tab and click the Start Service button.
- Go to Administrative Tools > Component Services, and expand the Services node. Select Synergy/DE OpenNet Server and click the Start button.
- At a command prompt enter
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:
- vtxnetd: By default, existing connections are terminated after a delay of up to 10 seconds, which allows underlying processes time to complete. You can change the delay time by editing the command line in the opennet.srv file.
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.
- vtxnet2: Existing connections are not stopped. Only the third-party applications that are connected to the Synergy data can stop existing processes. The vtxnet2 program does not completely shut down until all child processes have terminated.
To stop and remove SQL OpenNet,
- At a command prompt enter
sqld -x
To stop (but not remove) SQL OpenNet, do one of the following:
- In the Synergy Configuration Program (from Windows Control Panel, select Synergy Control Panel > Synergy Configuration Program), go to the Connectivity Series tab and click the Stop Service button.
- Go to Administrative Tools > Component Services, and expand the Services node. Select Synergy/DE OpenNet Server and click the Stop button.
- At a command prompt enter
sqld -q
- At a command prompt enter
net stop synsql
To remove SQL OpenNet, do one of the following:
- In the Synergy Configuration Program, go to the Connectivity Series tab and click the Remove Service button.
- At a command prompt enter
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.
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:
- Setting the port
- Server-side logging
- Running multiple SQL OpenNet servers
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.
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:
- The connect string (recommended). A port number setting in the connect string overrides settings in net.ini and the services file.
- net.ini. This setting overrides a setting in the services file. See Setting options in net.ini.
- The TCP/IP services file. See below for instructions.
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. |
- On a client configuration, use this 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
- On a stand-alone configuration, use this connect string:
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 release notes, REL_CONN.TXT, 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.