SQL Connection and xfODBC on UNIX

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 UNIX

SQL OpenNet consists of a daemon process (vtxnetd), which listens for requests and starts child processes to service requests from remote clients. The driver name (e.g., VTX0) specified in the connect string determines the executable file 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 SQL OpenNet for SQL Connection

You should ensure that the following requirements are met before configuring SQL OpenNet:

1. Install Connectivity Series from your distribution and source the setsde script (see the note above ).
2. Set the port number by adding the following line to your system’s /etc/services file:
vtxnet nnnn/tcp #Synergy/DE SQL OpenNet server

where nnnn is the port number for SQL OpenNet. The default port number is 1958, but you can specify any unused port.

Important

If you use a non‑default port, you must also do the following:

You can override the port number in the services file by specifying the port on the command line when running vtxnetd. See vtxnetd and vtxnet2 programs for details on the vtxnetd command‑line options.

3. Build the server shared library corresponding to the program that your client application will specify in the connect string. For example, for Oracle, you would build the shared library VTX0.so, which corresponds to the VTX0 database driver. The shared library files (other than VTX3.so and VTX4.so) must be built at install time for the specific version of your database. They should always be built on the target machine; do not build them on one machine and then transfer them to another machine.
cd synsqlx
sqlunixbld

The sqlunixbld program will prompt you to select the database and version you’re using. It uses this information to build the lib/ssql_libs file, which is used by makessqlsrv to create the shared library.

makessqlsrv
4. Move the newly‑built shared library to the /synergyde/connect directory.
5. SQL OpenNet is now configured. You must license your database drivers before you can run the example programs. See Configuring License Manager for licensing information.

Starting and stopping SQL OpenNet for SQL Connection

Starting SQL OpenNet

Follow these instructions to configure and source the startnet script, which kills and restarts SQL OpenNet, and can also be used to set SQL Connection environment variables.

Note

If you will be connecting to an Oracle database, you must source the Oracle script oraenv before starting SQL OpenNet—even if you’ve already sourced it to configure SQL OpenNet.

1. Move to the connect directory. For example:
cd /usr/synergyde/connect
2. (optional) Edit the set‑up script file, startnet, to include SQL Connection environment variables. For instructions, see Using network initialization files to set network defaults.
3. If you are using a port number other than the default (1958), change the port number in all three places it appears in the startnet file.
4. Source the set‑up script file:
. ./startnet

If the server starts successfully, you’ll receive a “vtxnetd is alive and kicking” message.

5. To run SQL OpenNet at system start‑up, add the setsde and startnet scripts to your start‑up file, for example /etc/rc. Make sure these scripts are sourced after License Manager is started.

If you do not want to use startnet, you can start SQL OpenNet by typing the vtxnetd command on the command line:

nohup vtxnetd &
vtxnetd &

See vtxnetd and vtxnet2 programs for details on the vtxnetd command‑line options. After executing the vtxnetd command, we recommend that you use vtxping to verify that the server is running. See vtxping utility for more information.

Stopping SQL OpenNet

To stop SQL OpenNet, use vtxkill. Running vtxkill kills the daemon process so that no new connections can be made, but it does not terminate existing connections. 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 etc/services file is used.

SQL Connection: configuring client or stand‑alone access

To use SQL Connection for client access to a remote database or for stand‑alone access to a local database, you need to

Setting the port number (for client access)

Set the port number by either adding a line to the /etc/services file or specifying the port in the connect string.

vtxnet nnnn/tcp #Synergy/DE SQL Connection

where nnnn is the port number. The default port is 1958. If you configured SQL OpenNet to run on a different port, specify that port number here.

Building a shared library (for stand‑alone access)

For stand‑alone access, you’ll need to build an SQL Connection shared library. The shared library should be built on the target machine; do not build it on one machine and then transfer it to another machine. Review the following requirements before building a shared library:

1. Move to the SQL Connection directory. For example:
cd /usr/synergyde/connect/synsqlx
2. Run the sqlunixbld program to customize the build process for your specific database:
sqlunixbld

The sqlunixbld program will prompt you to select the database and version you’re using. It uses this information to build the lib/ssql_libs file, which is used by makessqlsrv to create the shared library.

3. Run makessqlsrv to create a new shared library (VTXn.so) in accordance with the customization you specified when running sqlunixbld:
makessqlsrv
4. Move the newly‑built VTXn.so to the /synergyde/connect directory.
5. SQL Connection is now configured. You must license your database drivers before you can run the sample programs. See Configuring License Manager for licensing information.

Testing your connection with the sample programs

1. Verify that the target DBMS server is running. (You may want to test this by using a tool from the database vendor.)
2. Set the SQL_CONNECT environment variable to specify a connection string. The connection string varies depending on the set‑up. The examples below use the Bourne shell.
SQL_CONNECT=net:uid/pwd[/db]@[port:]srv_name!driver_name ;export SQL_CONNECT
SQL_CONNECT=driver_keyword:uid/pwd ;export SQL_CONNECT

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 database driver (e.g., VTX0), and driver_keyword is the keyword for the database driver (e.g., oracle). For more information, see Building connect strings.

3. Compile and link the example programs. There are several example programs (exam_create_table, exam_fetch, etc.) located in the synergyde/connect/synsqlx directory. 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
dblink exam_create_table
dbr exam_create_table

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

4. Run the example programs to test your connection and set‑up. If this test is unsuccessful, note the error(s) and refer to Error Logging and Messages.

Your Synergy/DE 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: using SQL OpenNet

To use SQL OpenNet for xfODBC access, you need to

Configuring SQL OpenNet for xfODBC

Before configuring SQL OpenNet, you must have TCP/IP installed, configured, and working properly.

1. Install Connectivity Series from your distribution and source the setsde script (see Environment setup).
2. Add the SQL OpenNet port number to the TCP/IP services file. Open your system’s /etc/services file and add this line:
vtxnet nnnn/tcp #Synergy/DE SQL OpenNet server

where nnnn is the port number for SQL OpenNet. The default port number is 1958, but you can specify any unused port.

Important

If you use a non‑default port, you must also specify it on the client side in the ODBC Data Source Administrator dialog box. See Setting up access with DSNs.

You can override the port number in the services file by specifying the port on the command line when running vtxnetd. See vtxnetd and vtxnet2 programs for details on the vtxnetd command‑line options.

Starting and stopping SQL OpenNet for xfODBC

Follow these instructions to configure and source the setodbc script, which kills and restarts SQL OpenNet, and can also be used to set xfODBC environment variables.

1. Move to the synodbc directory. For example:
cd /usr/synergyde/connect/synodbc
2. (optional) Edit the set‑up script file, setodbc, to include xfODBC environment variables. See Setting environment variables for xfODBC.
3. If you are using a port number other than the default (1958), change the port number in all three places it appears in the startnet script file. (This file is located in the synergyde/connect directory; it is executed by setodbc.)
4. Source the set‑up script file:
. ./setodbc

If the server starts successfully, you’ll receive a “vtxnetd is alive and kicking” message.

Note

We do not recommend using the DBLCASE environment variable with xfODBC. However, if you do use it, and you see this error when the setodbc script runs

Cannot open /usr/synergyde/connect/synodbc/GENESIS.ISM

it is likely that DBLCASE was not set when setodbc and dbcreate were initially run. On this initial run, there was no problem. However, when the system was rebooted, it is likely that DBLCASE was set to u:l, and then when setodbc subsequently ran, there was a case mismatch, resulting in an error.

To correct this situation, set DBLCASE to blank and source setodbc. To avoid seeing this error in the future, DBLCASE must be set to blank whenever dbcreate is run and SQL OpenNet or a local ODBC application is started.

5. To run SQL OpenNet at system start‑up, add the setsde and setodbc scripts to your start‑up file, for example /etc/rc. Make sure the setsde and setodbc scripts are sourced after License Manager is started.

Your xfODBC server component is now ready to use. For information about using xfODBC, see the xfODBC User’s Guide and the online release notes file, REL_CONN.TXT.

If you do not want to use setodbc, you can start SQL OpenNet by typing the vtxnetd command on the command line:

nohup vtxnetd &
vtxnetd &

See vtxnetd and vtxnet2 programs for details on the vtxnetd command‑line options. After executing the vtxnetd command, we recommend that you use vtxping to verify that the server is running. See vtxping utility for more information.  

Stopping SQL OpenNet

To stop SQL OpenNet, use vtxkill. Running vtxkill kills the daemon process so that no new connections can be made, but it does not terminate existing connections. 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 etc/services file is used.