cognos queries not terminating and creating locks in redshift database

[src2k19]

Hi Gurus,
We have recently implemented cognos with redshift as a datasource. Since then for some of the queries run at Cognos the threads do not get gracefully terminated at Redshift end leaving behind a lock on the table. I tried to check this issue with AWS but they said that this could be a cognos issue. I could not find a pattern in the queries being stuck until now. Could you please suggest a solution to this issue?

Cognos version- 10.2.2
Datasource connector - JDBC 4.0
Dqm package with FM based and adhoc sql based queries
Queries when run from client tools like Sql workbench runs fine and does not leave behind any trace.

A recommendation from AWS is to try and set AUTOCOMMIT while sending queries to Redshift but since this is third party they cannot garuntee this will resolve the issue. Could you please tell me if an Autocommit setting is possible in Cognos and where can I do this?

Thanks.

[MFGF]

Hi Gurus,
We have recently implemented cognos with redshift as a datasource. Since then for some of the queries run at Cognos the threads do not get gracefully terminated at Redshift end leaving behind a lock on the table. I tried to check this issue with AWS but they said that this could be a cognos issue. I could not find a pattern in the queries being stuck until now. Could you please suggest a solution to this issue?

Cognos version- 10.2.2
Datasource connector - JDBC 4.0
Dqm package with FM based and adhoc sql based queries
Queries when run from client tools like Sql workbench runs fine and does not leave behind any trace.

A recommendation from AWS is to try and set AUTOCOMMIT while sending queries to Redshift but since this is third party they cannot garuntee this will resolve the issue. Could you please tell me if an Autocommit setting is possible in Cognos and where can I do this?

Thanks.

Hi,

I don't have access to a Cognos instance to confirm, but if memory serves you can define a Transaction Isolation level in the data source connection. Setting a lower isolation level (eg read committed) might prevent any locks being implemented.

Just a thought.

MF.