Error logging for xfODBC

If you encounter errors when using an ODBC‑enabled application to access a Synergy database, start by making sure your system is set up correctly (see Troubleshooting data access). You can then use xfODBC logging (see list below) to diagnose the problem. Generally, it’s best to use logging in a stand‑alone configuration first. Then test your network connection. Finally, when you’ve got the stand‑alone configuration and the network layer working correctly, re‑create your client/server configuration and use logging to diagnose any remaining problems.

If you can’t solve a problem by examining the log files, save the log files, collect all pertinent information about the problem, and contact Synergy/DE Developer Support. In addition to the log files, Support will need a description of the problem and the version numbers of all relevant software and hardware—especially the Synergy/DE version, operating system, and third‑party ODBC‑compliant application. For client/server configurations, supply this information for both the client and the server.

The following are the utilities and logging facilities you can use to debug problems and, in some cases, verify optimization with xfODBC. The diagram in figure 1 illustrates where the different types of logging fit in the data access process.

Utilities and Facilities Useful in Debugging

Utility/Facility

Description

dltest

Lists needed Connectivity Series DLLs and states whether they can be found by xfODBC. Dlltest is located in the synergyde\connect directory. Run this utility from the command line; it has no options.

ODBC trace logging

Records ODBC API calls passed from the ODBC‑enabled application to the ODBC Driver Manager. See ODBC trace logging (Windows).

Vortex API logging

Records API calls made by the xfODBC driver on a Windows client. This enables you to see the exact SQL statement issued to the database, debug SQL statement errors, and verify optimization. See Vortex API logging (Windows).

Vortex host logging

Records API calls made from the SQL OpenNet server to the Synergy database driver. We recommend Vortex API logging instead of Vortex host logging. These two types of logging generally record identical information, but Vortex API logging is easier to use. On Windows, Vortex host logging can be used only with vtxnet2 (not vtxnetd). See Vortex host logging.

SET OPTION logging

Records information about indexes used to optimize a query (as well as internal information for use by Synergy/DE Developer Support). See SET OPTION and Creating a file for query processing options.

Synergy driver logging

Enables you to determine if a system catalog is cached. See Using logging to determine if a system catalog is cached.

Synergy DBMS logging

Records ISAM calls made from the Synergy database driver to your Synergy database. This enables you to debug open file errors, licensing errors, and connection failures. See Synergy DBMS logging.

vtxping and synxfpng

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 identical, except that 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, 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 information to determine why a connection is failing in a client/server configuration. For more information, see vtxnetd and vtxnet2 programs.

Generally problems are caused by an ODBC‑enabled application, such as Microsoft Query, sending unsupported SQL statements to the xfODBC driver. (See Appendix B: SQL Support for information on the SQL statements supported by xfODBC.) To test the validity of an SQL statement, you can use Microsoft ODBC Test (Odbcte32.exe). (You can also use this utility to test your setup. If you’re able to connect with ODBC Test, your setup is working. See Using ODBC Test to test a query for an example.)

In addition to the logging options listed above, we automatically set the environment variable VORTEX_HOST_SYSLOG, which instructs the SQL OpenNet sever 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.

1. xfODBC error logging and messages.

xfODBC error logging and messages

Using the log files

ODBC trace logging (Windows)

ODBC trace logging records ODBC API calls passed from the ODBC‑enabled application to the ODBC Driver Manager. We recommend that you use log files to debug in a stand‑alone configuration. If you need to use ODBC trace logging in a client/server configuration, do this on the client.

1. Exit your ODBC‑enabled application.
2. Open the ODBC Data Source Administrator available from Windows Control Panel.
3. Go to the Tracing tab and click the Start Tracing Now button.
4. Make a note of the log file name displayed on the Tracing tab so that you can find it later.
5. Re‑run the application until you receive the error(s), and then examine the log file.
6. Once you have successfully logged the error, turn tracing off.

Vortex API logging (Windows)

Vortex API logging records statements (connect strings are omitted) issued to the database by the xfODBC driver. We recommend that you use log files to debug in a stand‑alone configuration. If you need to use Vortex API logging in a client/server configuration, set the environment variables on the client.

Note

Vortex API logging is not supported for multi‑threaded applications.

1. Exit your ODBC‑enabled application.
2. Use the VORTEX_API_LOGFILE and VORTEX_API_LOGOPTS environment variables to specify a name for the log and turn logging on. For example:
VORTEX_API_LOGFILE=c:\vortex
VORTEX_API_LOGOPTS=FULL

APPEND—Appends logging information to existing log file

ERROR—Logs only statements with errors

FULL—Specifies full logging

PLAY—Sets an option that enables Synergy/DE Developer Support to play back an operation

RECORD—Logs data for Synergy/DE Developer Support

SQL—Creates a file that contains diagnostic information. The filename (minus extension) is specified with VORTEX_HOST_LOGFILE. The extension is .sql. (Client only)

TIME—Logs execution time for statements

If you set VORTEX_API_LOGFILE without setting VORTEX_API_LOGOPTS, the log file will include a list of all operations along with a total count for each operation.

3. Re‑run the application until you receive the error(s), and then exit the ODBC application.
4. Examine the log file.
5. Once you have successfully logged the error, turn logging off by unsetting the environment variables (and reboot if necessary). Logging slows performance, and the log files can quickly fill a disk.

Vortex host logging

Vortex host logging records statements (connect strings are omitted) passed to SQL OpenNet from the xfODBC driver. Vortex host logging applies only to client/server configurations. Note that we recommend Vortex API logging instead of Vortex host logging. Vortex API logging and Vortex host logging generally record identical information, but Vortex API logging is easier to use. (In special cases, however, Synergy/DE Developer Support may instruct you to use Vortex host logging.)

Vortex host logging is not supported for multi‑threaded applications, so use this only with vtxnet2, not vtxnetd.

1. Exit your ODBC‑enabled application on the client.
2. Set the VORTEX_HOST_LOGFILE and VORTEX_HOST_LOGOPTS environment variables on the server to specify a name for the log and to turn logging on. For example:
VORTEX_HOST_LOGFILE=c:\vortex
VORTEX_HOST_LOGOPTS=FULL

APPEND—Appends logging information to existing log file

ERROR—Logs only statements with errors

FULL—Specifies full logging

PLAY—Sets an option that enables Synergy/DE Developer Support to play back an operation

RECORD—Logs data for Synergy/DE Developer Support

SQL—Creates a file that contains diagnostic information. The filename (minus extension) is specified with VORTEX_HOST_LOGFILE. The extension is .sql. (Client only)

TIME—Logs execution time for statements

If you set VORTEX_HOST_LOGFILE without setting VORTEX_HOST_LOGOPTS, the log file will include a list of all operations along with a total count for each operation.

3. Go to the client, re‑run the application until you receive the error(s). Then exit the application.
4. Go to the server and examine the log file to determine the problem. The log file will be named filename_pid.log, where filename is the name you specified with the VORTEX_HOST_LOGFILE variable and pid is the process ID.
5. Once you have successfully logged the error, turn logging off by unsetting the environment variables on the server. Logging slows performance, and the log files can quickly fill a disk.

Synergy DBMS logging

Synergy DBMS logging records ISAM calls made to your Synergy database from the xfODBC driver. We recommend that you use log files to debug in a stand‑alone configuration. If you need to use Synergy DBMS logging in a client/server configuration, set the environment variables on the server. For information on using Synergy DBMS to resolve performance issues, see Tracking performance.

Note

Synergy DBMS logging can significantly reduce performance. Use it for diagnostic purposes only; then turn it off.

Synergy DBMS logging on Windows and UNIX

1. Exit your ODBC‑enabled application.
2. Set one or more of the following environment variables.

SDMS_AUDIT —Set to the path and filename for the Synergy DBMS audit log file for non‑server operations.On Windows, use SDMS_AUDIT_SRV instead of SDMS_AUDIT (even in non‑server situations) to audit threaded programs.

SDMS_AUDIT_FULL —Set to any value to extend the logging output by adding the first 50 bytes of each record to the log file.

SDMS_AUDIT_MODE —Set to any value to specify that I/O control modes are logged for each file operation.

SDMS_AUDIT_SRV —Set to the path and filename for Synergy DBMS audit logs for operations on a server or threaded Windows program. The thread ID and current time are appended to the extension for each log filename.

SDMS2_FULL1 —Set to 1 to record additional ODBC calls to the Synergy database. Use this variable with SDMS_AUDIT or SDMS_AUDIT_SRV.

Setting requirements:

SDMS_AUDIT_SRV=c:\sdms
SDMS_AUDIT_FULL=1
SDMS2_FULL=1
SDMS_AUDIT=/usr2/sdms.log ;export SDMS_AUDIT
SDMS_AUDIT_FULL=1         ;export SDMS_AUDIT_FULL
SDMS2_FULL=1              ;export SDMS2_FULL
3. Run the ODBC‑enabled application until you receive the error(s), and then exit the application.
4. Examine the log file (on the server in a client/server configuration) to determine the problem.
5. Turn logging off by unsetting the environment variables. Logging slows performance, and the log files can quickly fill a disk.

Synergy DBMS logging on OpenVMS

1. Set the following environment variables to specify a location for the log file and turn logging on.

SDMS2_LOG—Set to the path and filename for te SDMS2 audit log file.

SDMS2_FULL—Set to 1 to record additional ODBC calls to the Synergy database.

These should be set with system‑wide logicals. For example:

$ DEF/SYS/EXE SDMS2_LOG DEVICE:[DIRECTORY]FILE
$ DEF/SYS/EXE SDMS2_FULL 1
2. Stop and restart the SQL OpenNet server. For example:
$ SET DEF CONNECTDIR:
$ VTXKILL TIGER
$ @STARTNET

For more information on stopping and starting the SQL OpenNet server, see Starting and stopping SQL OpenNet for xfODBC.

3. Go to the client system and run your application until you receive the error. Then completely exit the ODBC application on the client.
4. Go back to the server and use vtxkill to stop the OpenNet server. For example:
$ VTXKILL TIGER
5. Examine the log file. It should be in the location you specified with the SDMS2_LOG logical.
6. Turn logging off by unsetting the logicals. Logging slows performance, and the log files can quickly fill a disk.