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

Note

You must rebuild the SQL Connection shared library (VTX14.so or VTX0.so) whenever you upgrade Synergy/DE, Oracle, or MySQL.

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 of SQL OpenNet. We recommend setting this in the vtxnetd command in the startnet file on the server (see vtxnetd and vtxnet2 programs), as well as the other two places it appears in startnet (in the vtxkill and vtxping commands). You can also set it in your system's /etc/services file. If the vtxnetd command does not specify a port, SQL OpenNet will use the port setting in the services file.

To set the port number in the services file, 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 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 setting the client port number, 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.

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 Synergy/DE Licensing for licensing information.

Starting SQL OpenNet for SQL Connection

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 Setting environment variables.
3. Set the port number in all three places it appears in the startnet file. See Configuring SQL OpenNet for SQL Connection above.
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 -p1958 &

vtxnetd -p1958 &

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 for SQL Connection

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, a port setting 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 specifying the port in the connect string (recommended) or adding a line to the /etc/services file.

vtxnet nnnn/tcp #Synergy/DE SQL OpenNet

where nnnn is the port number.

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:

Note

You must rebuild the SQL Connection shared library (VTX14.so or VTX0.so) whenever you upgrade Synergy/DE, Oracle, or MySQL.
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 Synergy/DE Licensing 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, 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 SQL Connection sample programs.

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. Set the port number for SQL OpenNet. We recommend setting the port number in the vtxnetd command in the startnet file on the server (see vtxnetd and vtxnet2 programs), as well as the other two places it appears in startnet (in the vtxkill and vtxping commands). Optionally, you can specify the port number in your system's /etc/services file. If the vtxnetd command does not specify a port, SQL OpenNet will use the port setting in the services file.

To set the port number in the services file, add this line to the file:

vtxnet nnnn/tcp #Synergy/DE SQL OpenNet

where nnnn is the port number for SQL OpenNet.

Important

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. See Setting up access with DSNs for information on client settings for xfODBC.

Starting 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 in a batch file, shell script, or DCL command file.
3. Set 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.) See Configuring SQL OpenNet for xfODBC above.
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 -p1958 &

vtxnetd -p1958 &

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

Stopping SQL OpenNet for xfODBC

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 port specified in the /etc/services file is used.