SQL Connection troubleshooting and error logging

The first step in troubleshooting is to make sure Connectivity Series is configured correctly and that your SQL Connection application can successfully connect to the database. (For client/server configurations, this means that you must be able to connect to the SQL OpenNet server.) The following utilities and logging options help you do this:

Utility / logging option

Purpose

dltest

This utility lists needed Connectivity Series DLLs and states whether SQL Connection can find them. To use dltest, run it from the command line. It is in the synergyde\connect directory and has no options or arguments.

vtxping and
synxfpng

These enable you to ping an SQL OpenNet server so you can verify that you can connect in a client/server configuration. Vtxping and synxfpng (when used with the ‑x option) are nearly identical, but synxfpng has a verbose option (‑v) that lists socket calls as they succeed or fail, which can be useful when debugging. For more information, see vtxping utility and synxfpng utility.

vtxnetd / vtxnet2
logging

If you set the log option for either of these, a log file (tcm_pid.log) records connection requests and, if the program can’t start a worker thread or process, logs the reason for the failure. You may be able to use this to determine why a connection fails in a client/server configuration. This log also records ping and kill requests. See vtxnetd and vtxnet2 programs.

SSQLLOG

This environment variable (part of SQL Connection logging; see SQL Connection logging) enables you to see the connect string (with the password masked by asterisks) sent to the database when a connection fails. It works for both client/server and stand-alone configurations.

If Connectivity Series appears to be configured correctly, but you are still unable to connect,

In addition to the logging options listed above, the Connectivity Series installation automatically sets the environment variable VORTEX_HOST_SYSLOG, which instructs the SQL OpenNet server to generate messages for the event log (Windows), syslog (UNIX), or the operator console (OpenVMS) when an attempt to connect to an SQL OpenNet server causes fatal errors. We don’t recommend changing this setting.

Once you can connect…

Once you know that your SQL Connection application can connect to the database, you can use the various types of logging: SQL Connection logging, Vortex API logging, Vortex host logging, and database-specific logging. Figure 1 illustrates where these types of logging apply once the program has connected to the database. (With the exception of the SSQLLOG environment variable mentioned above, these logging methods are useful only when your SQL Connection application has successfully connected to the database.) In general, because networks and OpenNet Server complicate matters, it’s best to start by using logging in a stand-alone configuration. Then, when your program works smoothly in a stand-alone configuration, move to a client/server configuration.

Logging type

Purpose

SQL Connection logging

Records cursor status and SQL Connection API calls. Use this to find SQL Connection API calls in your code that behave differently than expected. For more information, see SQL Connection logging.

Vortex API and Vortex host logging

Record SQL commands and SQL Connection internal information. You can use these to see how an SQL statement is broken down into commands, and you can use these for performance tuning. For more information, see Vortex API logging and Vortex host logging.

Database-specific logging

Use database-specific logging to examine database-specific errors. For information on database-specific logging for Synergy databases, see Synergy DBMS logging (Synergy database driver only).

We also recommend the following:

If you can’t solve a problem by examining the log files, save the log files and call Synergy/DE Developer Support. Support will also need a description of the problem and the version numbers of all relevant software and hardware—especially the Synergy/DE version, operating system, database, and database version.

1. Errors and logging for a program that has successfully connected.

Errors and logging for a program that has successfully connected

SQL Connection logging

SQL Connection logging enables you to track SQL Connection API operations, see the connection string used when a connection fails, list open cursors, and create a detailed log for use by Synergy/DE Developer Support.

To use SQL Connection logging, set one of these environment variables in synergy.ini (Windows only) or in the environment. For client/server configurations, set them on the client.

Set…

To…

Explanation

SSQLLOG

1

Creates the SSQLX.LOG log file in your working directory, which lists

  • SQL Connection API operations in the order they were sent to the SQL Connection API interface.
  • %SSC_xxx function calls as well as errors and warnings (in certain circumstances).
  • open cursors if one of the following errors occurs: $ERR_CURSERR (“ID not select error” or “ID must be a non select cursor”) or $ERR_NOMORECURS (“No more available open cursors”).
  • the connect string (with the password masked by asterisks) that is sent to the database when a connection fails.

This log also indicates whether an %SSC_OPEN call reused a cursor, closed a cursor and then reopened it, reopened a closed cursor, or created a new cursor.

SQLJUSTINTIME

1

Records the cursor status on an error condition, which can help you determine why an operation failed. SQLJUSTINTIME creates the log file ssqlerr.log, which contains an open cursor listing. This file is created in your working directory or (on Windows) in the temp directory for your user account. If ssqlerr.log already exists, new logging is appended to the current file, potentially creating a very large file.

SQLJUSTINTIME cannot be used if SSQLLOG is set.

Vortex API logging

Vortex API logging enables you to see the exact SQL commands passed to the SQL OpenNet client (in a client/server configuration) or to the database driver (in a stand-alone configuration). You can use this information to debug SQL statements, and you can use it to verify optimization. (See Using Vortex API logging to verify optimization below.)

To use Vortex API logging, set one or more of the following environment variables in the environment. Set them on the client in a client/server configuration.

Set…

To…

Explanation

VORTEX_API_LOGFILE

Filespec

Logs the exact SQL commands passed to the SQL OpenNet client (if client/server) or database driver (if stand-alone). If you set this without setting VORTEX_API_LOGOPTS, a list of operations with a total count for each operation is recorded.

Note: Don’t specify an extension for the filename (or a version number on OpenVMS). SQL Connection automatically appends the process ID (filename_pid) and an extension (.log). If you specify an extension on OpenVMS, no log file will be created.

VORTEX_API_LOGOPTS

Options

Must be used with VORTEX_API_LOGFILE, and must be set to one or more of the following. To set more than one option, separate options with the plus sign—for example, FULL+TIME.

APPEND—Appends logging information to existing file.

ERROR—Logs only statements with errors.

FULL—Specifies full logging. Note: If your program opens multiple database channels concurrently, you must also set MULTI (or you’ll get an error).

MULTI—Creates a separate file for each channel when using multiple database channels.

PLAY—Enables Synergy/DE Developer Support to playback an operation.

RECORD—Logs data for Synergy/DE Developer Support.

SQL—Creates a file that contains SQL commands. Specify the filename (minus extension) and path (optional) with VORTEX_API_LOGFILE. The extension is .sql.

TIME—Logs execution time for statements.

Using Vortex API logging to verify optimization

You can use Vortex API logging to find out how well you’ve optimized cursor usage. (We suggest you use VORTEX_API_LOGOPTS=TIME.) The final page of the log lists counts of Vortex API calls and indicates which operations reuse cursors.

In the following example, the EXEC2 count indicates that 100 EXEC statements reused cursors, and the OPENFETCH count indicates that 200 open statements reused cursors.

EXEC                5
EXEC2               100
OPEN                20
OPENFETCH           200

For best performance, each statement should have more operations that reuse cursors than operations that don’t.

Note that you can also use SSQLLOG (SQL Connection logging) to see if cursors are being opened and closed for a series of identical SELECT statements, where a single cursor with %SSC_SCLOSE (or no close at all) should be used.

Vortex host logging

Like Vortex API logging, Vortex host logging records SQL commands. But Vortex host logging logs these commands as they are passed from the SQL OpenNet server to the database driver. You can use this information to debug SQL statements and to verify optimization. (See Using Vortex API logging to verify optimization above for information.)

To use Vortex host logging, set one or more of the environment variables listed in the following table. Set these on the server.

Set…

To…

Explanation

VORTEX_HOST_LOGFILE

Filespec

Logs SQL commands as they are passed from SQL OpenNet to the database driver. If you set this without setting VORTEX_HOST_LOGOPTS, a list of operations along with a total count for each operation is recorded.

Note: Don’t specify an extension for the filename (or a version number on OpenVMS). SQL Connection automatically appends the process ID (filename_pid) and an extension (.log). If you specify an extension on OpenVMS, no log file will be created.

VORTEX_HOST_LOGOPTS

Options

Must be used with VORTEX_HOST_LOGFILE, and must be set to one or more of the following. To set more than one option, separate options with the plus sign—for example, FULL+TIME.

APPEND—Appends logging information to existing file.

ERROR—logs error statements only.

FULL—Specifies full logging. If your program opens multiple database channels concurrently, MULTI must also be set (or you’ll get an error).

MULTI—Creates a separate file for each channel when using multiple database channels. Set automatically when running vtxnetd on Windows.

PLAY—Enables Synergy/DE Developer Support to playback an operation.

RECORD—logs data for Synergy/DE Developer Support.

SQL—Creates a file that contains SQL commands. Specify the filename (minus extension) and path (optional) with VORTEX_HOST_LOGFILE. The extension is .sql.

TIME—logs execution time for statements.

Synergy DBMS logging (Synergy database driver only)

If you’re connecting to a Synergy database driver, Synergy DBMS logging enables you to create a log of Synergy database driver operations. You can also create a detailed log for Synergy/DE Developer Support.

For information on Synergy DBMS logging, see Data Access Errors and Error Logging.