Close menu

SQL/R Troubleshooting

Introduction

For troubleshooting purposes, SQL/R provides both client and server side logging.

A client log file may be helpful in situations where either a connection cannot be established or a certain ODBC function call does not behave as expected.

A server log file may be used to analyze any kind of backend problems, for example if a running connection suddenly aborts without obvious reason or a SQL query delivers unexpected results.

Logging is configured by specifying the destination where the log messages are written as well as which functional areas are logged and how detailed the log output should be.

The log destination may be a log file in the file system or a system-defined location such as the syslog on HP-UX and Linux or the Windows event log.

The log flags (log mode) define what will be logged. The functional area is specified with a capital letter A...Z (see below for details). The verbosity, i.e., how much will be logged, is specified with the digits 0...3.

Verbosity:

By default, only error messages are logged (verbosity 0).

The asterisk '*' is used to set all functional areas to a specific verbosity. It makes sense first to specify a common verbosity using '*' and then optionally specify which functional areas should be logged differently, as shown in the examples below.

Examples:


Creating an ODBC driver log file

On Windows, a client-side ODBC driver log file is created by editing the LogMode and LogFile parameters in the sqlrodbc.ini file.

To open the sqlrodbc.ini file, use:
Start Menu -> Programs -> SQLR -> ODBC Driver Configuration

The default sqlrodbc.ini file contains commented example settings for LogMode and LogFile. To activate them, the leading ';' semicolon characters must be removed.

With LogMode, the following functional areas may be specified:

For a detailed log except the network transactions, set LogMode = *2N0. For a full log, set LogMode = *3.

LogFile must specify a file in the file system that the invoking process (the ODBC program using the SQL/R ODBC driver) is allowed to write.

For example: LogFile = c:/sqlrodbc.log

On platforms other than Windows, there is no sqlrodbc.ini file. Instead, the SQLR_LOG_FLAGS and SQLR_LOG_FILE environment variables may be set and exported before the invoking process is started.

SQLR_LOG_FLAGS is the equivalent of LogMode and SQLR_LOG_FILE is the equivalent of LogFile.

For example: 

 SQLR_LOG_FLAGS="*2N0"
 SQLR_LOG_FILE="/tmp/sqlrodbc.log"
 export SQLR_LOG_FLAGS SQLR_LOG_FILE

Please note:


Creating a SQL/R server log file

SQL/R server logging is configured in the odbc.cfg configuration file.

On HP-UX and Linux, this is: /etc/opt/sqlr2/odbc.cfg

On Windows, to open the odbc.cfg file, use:
Start Menu -> Programs -> SQLR -> Server Configuration

The original odbc.cfg configuration file contains the commented default settings for the LogFile and LogFlags parameters. To activate them, the leading '#' hash characters must be removed.

The LogFile parameter specifies the log destination. This may either be a file in the file system or set to "syslog" to write any log messages to the HP-UX or Linux syslog or the Windows event log.

With LogFlags, the following functional areas may be specified:

For a full diagnostic log, set LogFlags = *3D2 (The 'D' flag should not be set to 3 because this would generate periodic messages that are typically not of interest).

To find out details about specific SQL statements, the LogFlags = *1Y2O2 setting is often helpful.

During normal operation, it is recommended to set LogFlags = *1. This will log any connect/disconnect activities. Alternatively, setting LogFlags = *1E2 enables logging of additional audit information per connection.

To disable logging exept for error messages, set LogFlags = *0.

Please note: SQL/R server logging with LogFlags different from *0 or *1 or *1E2 should not be active for longer than necessary to avoid a performance impact on the SQL/R server.