This topic includes the following sections:
- Utilities and features for troubleshooting xfODBC
- ODBC trace logging (Windows)
- Vortex API logging (Windows)
- Vortex host logging
- Synergy DBMS logging
If you encounter errors when using an ODBC-enabled application to access a Synergy database, start by gathering all relevant information, making sure your system is set up correctly, and so forth, as described in the initial steps outlined in Appendix C: xfODBC Troubleshooting Guide. You can then use xfODBC logging 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. See Appendix C: xfODBC Troubleshooting Guide for details.
Utilities and features for troubleshooting xfODBC
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 |
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). |
|
ODBC trace logging |
Records ODBC API calls passed from the ODBC-enabled application to the ODBC Driver Manager. See ODBC trace logging (Windows) below. |
|
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) below. |
|
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 below. |
|
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 below. |
|
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, 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. |
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.
|
ODBC trace logging (Windows)
ODBC trace logging logs ODBC API calls passed from an ODBC-enabled application to the ODBC Driver Manager. We recommend using a stand-alone configuration when using ODBC trace logging. (This is a requirement for SQL Connection; see SQL Connection troubleshooting and error logging.) If you do use ODBC trace logging in a client/server configuration, start it on the client.
To start ODBC trace logging, do the following:
| 1. | Open the ODBC Data Source Administrator, available from Windows Control Panel > Administrative Tools > ODBC Data Sources. There are 32- and 64-bit versions; use the one that corresponds to the bitness of your ODBC-enabled application. |
| 2. | Locate the DSN used by your ODBC-enabled application (on the User, System, or File DSN tab) and click on it to select it. |
| 3. | Go to the Tracing tab and note the log file name and location displayed in the Log File Path field. In Windows Explorer, navigate to that location and clear any existing log files. (Additional data is appended to existing files, which can get quite large.) |
| 4. | Back on the Tracing tab, click the Start Tracing Now button. |
| 5. | Run the application until you receive the error(s). |
| 6. | Exit the application. |
| 7. | Click the Stop Tracing Now button on the Tracing tab and then examine the log file. |
Vortex API logging (Windows)
Vortex API logging records statements (without connect strings) issued to the database by the xfODBC driver in a stand-alone configuration. For client/server configurations (vtxnet2 only), you can use Vortex host logging, but we recommend using Vortex API logging (in a stand-alone configuration).
|
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=d:\temp\vortex VORTEX_API_LOGOPTS=FULL
- Set VORTEX_API_LOGFILE to the path and filename of the logfile you want to produce. Don’t specify an extension for the filename (or version number on OpenVMS). xfODBC automatically appends the process ID (filename_pid) and an extension (.log). If you specify an extension on OpenVMS, no log file will be created.
- Set VORTEX_API_LOGOPTS to one or more of the following. You can set more than one option by separating the options with a plus sign—for example, FULL+TIME.
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.
- Set these environment variables in the environment.
- For client/server configurations, set the environment variables on the client. (For services such as web servers that use the xfODBC driver, you can use the Env. variables field in the xfODBC Setup window to set these environment variables on the client. For information, see Adding a user or system DSN.)
| 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 (without connect strings) 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
- Set VORTEX_HOST_LOGFILE to the path and filename of the log file you want to produce. Don’t specify an extension for the filename (or version number on OpenVMS). xfODBC automatically appends the process ID (filename_pid) and an extension (.log). If you specify an extension on OpenVMS, no log file will be created.
- Set VORTEX_HOST_LOGOPTS to one or more of the following. You can set more than one option by separating the options with a plus sign—for example, FULL+TIME.
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.
- On Windows, set these environment variables in the opennet.srv file before starting vtxnet2. You must use vtxnet2 to enable logging; these environment variables do not work with vtxnetd.
- On Unix, set these environment variables in your environment before starting vtxnetd.
- On OpenVMS, set these environment variables with system-wide logicals before starting the server program.
| 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.
|
|
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_FULL —Set to 1 to record additional ODBC calls to the Synergy database. Use this variable with SDMS_AUDIT or SDMS_AUDIT_SRV.
Setting requirements:
- For stand-alone configurations, set these in the environment.
- On Windows, set these as system-wide environment variables before starting the SQL OpenNet server. This may require stopping and restarting the server. For example:
SDMS_AUDIT_SRV=c:\sdms SDMS_AUDIT_FULL=1 SDMS2_FULL=1
- On Unix, set these prior to starting the SQL OpenNet server. This may require stopping and restarting the server. For example, you can set them in the setodbc file:
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 the 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 SQL OpenNet for xfODBC 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. |
