Troubleshooting
Problem
An issue was observed where JDBC sessions would hang intermittently when using JBoss and Hibernate to connect to NPS via JDBC. If the session had issued a BEGIN as part of an explicit transaction, the session is observed to remain in “Tx-Idle” state or “Idle” state, but no additional work is processed.
Resolving The Problem
The Netezza database hardware supports one concurrent query running per session. Our drivers monitor the activity and generate appropriate errors when the API user attempts to run multiple queries per session.
The ODBC driver has been observed to hang when concurrent queries run on separate threads.
In this case, JBoss and Hibernate were using c3p0 connection pooling, and the prepared-statement-cache-size was set to 200. The reuse of cached statements appeared to adversely impact the session handling within the JDBC driver, causing the cache not to be cleaned properly.
Although code changes (to better handle cache session closing) would likely have mitigated the issue, setting the prepared-statement-cache-size to 0 avoided the intermittent hangs entirely.
A sample datasource configuration is shown below:
<local-tx-datasource>
<jndi-name>NZDB</jndi-name>
<use-java-context>false</use-java-context>
<connection-url>jdbc:netezza://nps25178:5480/NZDB</connection-url>
<driver-class>org.netezza.Driver</driver-class>
<user-name>etl</user-name>
<password>*******</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<max-idle-timeout>0</max-idle-timeout>
<prepared-statement-cache-size>0</prepared-statement-cache-size>
<blocking-timeout-millis>1800000</blocking-timeout-millis>
<idle-timeout-minutes>10</idle-timeout-minutes>
<track-statements>true</track-statements>
</local-tx-datasource>
Historical Number
NZ595980
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21570281