SQL Connection troubleshooting and error logging

This topic includes the following sections:

Making sure your application can connect to the database
Once you can connect…
SQL Connection logging
Vortex API logging
Vortex host logging

Making sure your application can connect to the database

When you encounter an SQL Connection issue, you’ll need take some preliminary steps to get a complete picture of the setup, the circumstances surrounding the issue, and the information available on the issue (i.e., the preliminary steps outlined in Appendix B: SQL Connection Troubleshooting Guide). You’ll then need to start troubleshooting by making sure your Connectivity Series configuration is correct and that your SQL Connection application can successfully connect to the database. (For client/server configurations, this means 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	On Windows and Unix, the dltest utility lists DLLs or shared libraries used by Connectivity Series and states whether Connectivity Series can locate them. On Unix, it also lists the name of the library path environment variable (e.g., LIBPATH on AIX). If you have both 32- and 64-bit components installed, running both the 32- and 64-bit versions of dltest can help you troubleshoot problems.
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 or log2 option for either of these programs, log files (tcm_pid.log) record connection requests. And if the program can’t start a worker thread or process, this logging records the reason for the failure. You may be able to use this to determine why a connection is failing in a client/server configuration. See vtxnetd and vtxnet2 programs for more information, and be sure to clean up unneeded log files and turn off this logging when it is no longer needed.
SSQLLOG	This environment variable 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. See SQL Connection logging below.
SQLJUSTINTIME	This environment variable records the cursor status on an error condition, which can help you determine why an operation failed. See SQL Connection logging below.

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

check encryption settings on the client (in net.ini) and on the server (in the vtxnetd or vtxnet2 command line). Make sure these match (or try connecting after removing these settings from both locations), and make sure net.ini is in the directory specified by VORTEX_HOME. (Note that if you have both 32-bit and 64-bit Connectivity Series on a 64-bit Windows machine, VORTEX_HOME is set by the last version installed.) See Setting options in net.ini for more information. Mismatched encryption settings or the inability to access net.ini encryption settings, can cause a variety of errors when connecting, including invalid username, null password, invalid connect string syntax, and data source name errors.
for SQL Server, make sure you are using SQL Server authentication (not just Windows authentication, which SQL OpenNet can’t use). For example, for SQL Server 2008, make sure the “SQL Server and Windows Authentication mode” option is selected in the Security Section of Server Properties.
verify you have the correct 32- and 64-bit Connectivity Series components installed. For example, if your application uses IIS, the bitness of the IIS process determines the required bitness of the Connectivity components. You can run both the 32- and 64-bit versions of dltest to verify whether the correct DLLs are installed.

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 below.
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 below.
Database-specific logging	Use database-specific logging to examine database-specific errors.
ODBC trace logging	If you are using an ODBC-based driver (VTX11 or VTX12_SQLNATIVE), you can use ODBC trace logging to record ODBC API calls passed from the application to the ODBC Driver Manager. To use ODBC trace logging with an SQL Connection program, you must use a local connection (not SQL OpenNet). See ODBC trace logging (Windows) for more information.

We also recommend the following:

Check the return value of all SQL Connection function calls, use %SSC_GETEMSG to retrieve error messages, and process all error codes as necessary.
Use bounds checking (-qcheck or /CHECK_BOUNDS), which instructs the runtime to report errors if your program subscripts outside the bounds of a field. Additionally, for SQL Connection, bounds checking makes sure that stack records are not used for %SSC_ function operations, which would lead to memory corruption.

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.

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 it on the client.

Set…	To…	Explanation
SSQLLOG	1	Creates the SSQLX.LOG log file (in the current working directory of the process), 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 some cases. 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. SSQLLOG cannot be used if SQLJUSTINTIME is set.
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.

Set…

To…

Explanation

SSQLLOG

Creates the SSQLX.LOG log file (in the current working directory of the process), 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 some cases.
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.

SSQLLOG cannot be used if SQLJUSTINTIME is set.

SQLJUSTINTIME

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.

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.

OPEN statements that reuse cursors are listed as OPENFETCH statements.
FETCH statements that reuse cursors are listed as FETCHNEXT statements.
The names of all other statements that reuse cursors end in 2.

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.

On Windows, set them in the opennet.srv file before starting vtxnetd or vtxnet2.
On Unix, set them in the environment before starting vtxnetd.
On OpenVMS, set them as system-wide logicals before starting 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.

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.

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.