IBM Netezza Warehouse - Open Connections

[ajohnso2]

Hi All,

We use Netezza as our source DB (Using the native IBM Netezza connector). Im experiencing some issues whereby if a dataset refresh is cancelled or a query is cancelled (while working on desktop) or it just flat out fails for one reason or another, it is not closing the connection and query on netezza. This leads to queries stacking on netezza, this is even more apparent when using a model with Direct Query and slicer values are changed in quick succession.

Has anyone else who uses this combination experienced this or does anyone have any advice on anything we could check/rectify to ensure queries are cancelled correctly at the database? 

As you can imagine with queries stacking this causes (and could cause) a major issue with concurrency. 

I'd also appreciate the viewpoint from anyone who uses a microsoft datawarehouse cloud or on prem... please tell me you do not experience this!!

[v-xiandat-msft · Accepted solution]

Hi @ajohnso2 ,

To address this issue, let's start by focusing on a few areas:

1. Query Cancellation Handling: Ensure that the client application or tool you're using to interact with Netezza properly supports query cancellation. This includes sending the appropriate signals to Netezza to terminate the query and close the connection. It might be beneficial to review the documentation or support resources for the specific client tools you're using to ensure they handle query cancellations correctly.

2. Connection Pool Management: If you're using a connection pool (either through the application or middleware), verify the pool's configuration regarding idle connections and connection timeouts. Proper configuration can help ensure that connections are closed or returned to the pool for reuse, rather than remaining open indefinitely.

3. Monitoring and Diagnostics: Utilize Netezza's system views and logs to monitor active sessions and queries. This can help identify long-running or orphaned sessions that may need to be manually terminated. 

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.