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.

To run SQL Connection in a client/server configuration, see SQL Connection: 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 xfODBC: using SQL OpenNet for Unix server set-up and xfODBC: testing the network connection for client access for Windows client set-up.

To use Connectivity Series products, you must source the setsde script. This script enables the Synergy/DE environment for the products you have installed. See Environment setup for additional information. The setsde script can be used with any Unix shell except csh.

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

configure SQL OpenNet by setting the port and building a shared library specific to your database. SQL OpenNet includes two prebuilt shared libraries: VTX3.so, which is used for network connections, and VTX4.so, which is used for Synergy database connections.
start SQL OpenNet.

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:

TCP/IP must be installed, configured, and working properly.
You need a C compiler (required by makessqlsrv)—GNU C compiler (GCC) on Linux; IBM XL C/C++ compiler on AIX.
(Oracle only) The Oracle Call Interface (OCI) must be installed for the appropriate bitness (32 or 64).
(Oracle only) You must source the Oracle script to export the correct ORACLE_HOME environment variable for the appropriate bitness (32 or 64). To verify the script ran correctly, run “printenv | grep ORACLE_HOME”.

Install Connectivity Series from your distribution and source the setsde script (see the note above).

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.

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.

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.

Move to the SQL Connection directory (below /synergyde/connect). For example:

cd synsqlx

Run the sqlunixbld program to customize the build process for your 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.

Run makessqlsrv to create a new shared library (VTXn.so) in accordance with the customization you specified when running sqlunixbld:

makessqlsrv

Move the newly-built shared library to the /synergyde/connect directory.

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.

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.

Move to the connect directory. For example:

cd /usr/synergyde/connect

(optional) Edit the set-up script file, startnet, to include SQL Connection environment variables. For instructions, see Setting environment variables.

Set the port number in all three places it appears in the startnet file. See Configuring SQL OpenNet for SQL Connection above.

Source the set-up script file:

. ./startnet

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

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:

To start the server and keep it running after you log out, enter

nohup vtxnetd -p1958 &

To start the server and terminate it when you log out, enter

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

install Connectivity Series and source the setsde script (see Environment setup).
set the port number (client only).
build the shared library for the database (stand-alone only).
test SQL Connection using the example programs.

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.

For information on setting the port number in the connect string, see Network string (opennet_info) syntax. The port in the connect string overrides a port setting in the services file.
To set the port number in the /etc/services file, add this line to the 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:

You need a C compiler (required by makessqlsrv)—GNU C compiler (GCC) on Linux; IBM XL C/C++ compiler on AIX.
(Oracle only) The Oracle Call Interface (OCI) must be installed for the appropriate bitness (32 or 64).
(Oracle only) You must source the Oracle script to export the correct ORACLE_HOME environment variable for the appropriate bitness (32 or 64). To verify the script ran correctly, run “printenv | grep ORACLE_HOME”.

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

Move to the SQL Connection directory. For example:

cd /usr/synergyde/connect/synsqlx

Run the sqlunixbld program to customize the build process for your specific database:

sqlunixbld

Run makessqlsrv to create a new shared library (VTXn.so) in accordance with the customization you specified when running sqlunixbld:

makessqlsrv

Move the newly-built VTXn.so to the /synergyde/connect directory.

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

Verify that the target DBMS server is running. (You may want to test this by using a tool from the database vendor.)

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.

For a Unix client to a Windows, Unix, or OpenVMS server, use a string similar to

SQL_CONNECT=net:uid/pwd[/db]@[port:]srv_name!driver_name ;export SQL_CONNECT

For stand-alone, use a string similar to

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.

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.

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 SDE release notes for more information about using SQL Connection.

xfODBC: using SQL OpenNet

To use SQL OpenNet for xfODBC access, you need to

configure SQL OpenNet for xfODBC.
start SQL OpenNet.

Configuring SQL OpenNet for xfODBC

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

Install Connectivity Series from your distribution and source the setsde script (see Environment setup).

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.

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.

Move to the synodbc directory. For example:

cd /usr/synergyde/connect/synodbc

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

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.

Source the set-up script file:

. ./setodbc

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

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.

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 xfODBC release note entries for the current version.

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

To start the server and keep it running after you log out, enter

nohup vtxnetd -p1958 &

To start the server and terminate it when you log out, enter

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.