How To: Gather ODBC Logs for troubleshooting

[SophiaF]

How To: Gather ODBC Logs

Gathering logs from the driver can be useful to troubleshoot errors that appear in Alteryx when attempting to connect to a database. There are two types of logs that can be gathered:

1. Data Source Name (DSN) trace logs (set up via an individual DSN)

 

- This log uses the driver settings of the individual DSN, meaning the logs are more specific to that datasource .

- This is the preferred log to gather.

2. Generic ODBC tracing

 

- This produces a standard trace file using Microsoft's ODBC trace library. This will gather logs for all connections made through any driver/DSN, and is therefore less verbose and more generic.

- Only gather this if your driver does not offer DSN trace logs (Procedure A).

Prerequisites

• Alteryx Designer
• Alteryx Server
• Any supported database driver

Procedure A - DSN trace log

1. Open the ODBC Data Source Administrator window
   1. Please note that there are separate windows for 64 and 32-bit data sources. Please choose the appropriate window based on the bitness of the driver that you are utilizing for the connection
2. On the User DSN or System DSN tab, select the DSN you are using to connect in Alteryx
3. Click the Configure button:
   
   
   
   
    
4. Each driver has different configuration options for enabling logging. Generally, you will find the logging options under the Advanced tab or settings, or under Logging Options.
5. You will also be required to set:
   1. The level of logging - always select LOG_TRACE or the highest level, if on a scale

LOG_TRACE is the preferred level	Highest level on a scale of 0-6

 

2. The path to the log file
   
   
    

Here are some examples of how to set the logging for different drivers:

Spoiler (Highlight to read)

Amazon Redshift




Simba Hive ODBC




Snowflake


 

8. Return to Alteryx and replicate the error.

9. Gather the log from the location you specified

Procedure B - Generic ODBC Trace

1. Open the ODBC Data Source Administrator window
   1. Please note that there are separate windows for 64 and 32-bit data sources. Please choose the appropriate window based on the bitness of the driver that you are utilizing for the connection
2. Select the Tracing tab
3. Under the Log File Path select the Browse button and set the file to an easy-to-reach location (i.e. your Desktop)
   
   
   
   
    
4. Click Start Tracing Now
   
   
   
    
5. Return to Alteryx and replicate the error
6. Gather the log from the location you specified

Common Issues

Spoiler (Highlight to read)

I am unable to find the logging options for the driver I am using.

Generally, the drivers have great help documentation for how to enable logging. A Google search of "my driver name enable logging DSN" should give you detailed steps specific to that driver. Some drivers do not have logging options available,

I configured the driver logging, but no log file is created.

1. If you have run your workflow but no log appears after the run completes, this generally means you do not have the appropriate permissions level to log. Please contact your DBA to confirm and investigate.
2. If you are using Snowflake, you cannot specify a log location. The default location isC:\Users\your_username
3. If you are using PostgreSQL:
1. In the Input Tool, add the following to the end of your connection string
 

;commlog=1;debug=1

2. By default, it writes the log to your user directory: C:\Users\your_username
 

I am unable to find the logging options for the driver I am using.Generally, the drivers have great help documentation for how to enable logging. A Google search of "my driver name enable logging DSN" should give you detailed steps specific to that driver. Some drivers do not have logging options available,I configured the driver logging, but no log file is created.1. If you have run your workflow but no log appears after the run completes, this generally means you do not have the appropriate permissions level to log. Please contact your DBA to confirm and investigate.2. If you are using Snowflake, you cannot specify a log location. The default location isC:\Users\your_username3. If you are using PostgreSQL: 1. In the Input Tool, add the following to the end of your connection string;commlog=1;debug=1 2. By default, it writes the log to your user directory: C:\Users\your_username

Additional Resources

 

• Troubleshooting Database Connections