75 ways to demystify DB2 #48: Techtip :Idle thread timeout after a select query in CLI applications connecting to DB2/zOS

Body

A CLI based application can get  Idle Thread timeout error if that application takes time to process the huge amount of data returned by a select query.

Until a COMMIT or ROLLBACK is being sent by the application after the query, the thread stays idle on the DB2/zOS server.

When the thread stays idle while processing the result sets more than the IDLE THREAD TIMEOUT ( IDTHTOIN) setting in DB2/zOS server,  the timeout error is expected (SQL1224N)

In order to prevent this issue, use commitOnEOF  CLI Keyword. This keyword specifies whether an implicit COMMIT is issued immediately after receiving the last row from a result set.

By using this keyword, you can free resources as soon as the application receives the entire result set from a cursor and prevent hitting IdleThread Timeout error.

This keyword can be specified under [DSN] section in the db2cli.ini or added to the connection string in the application.

Thanks for reading!

- Subbu Prabhu