I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

Database Connections are pooled and automatically closed after a period of idle activity.

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

Hello, Nigel

Thank you for your response. 

Yes! You are right,

But, some of our queries run complex calculations and sometimes take 7-8 minutes to complete.
5 or 6 of them are enough to crash my server.

Is there a way to manually call this automated cleaning process?

------------------------------
Ali Azimi
------------------------------

Original Message:
Sent: Tue September 27, 2022 02:13 PM
From: NIGEL CAMPBELL
Subject: SQL connection remains open after cancelling the report.

Database Connections are pooled and automatically closed after a period of idle activity.

------------------------------
NIGEL CAMPBELL

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

Hi, 

I think the only way, is to "kill" the sessions on your SQL server, it needs to clean up in temp etc. that´s way it take a short time to close the sessions.

Br

------------------------------
René Kent Nielsen
Brand manager
CogniTech A/S
Herning
------------------------------

Original Message:
Sent: Tue September 27, 2022 02:36 PM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

Hello, Nigel

Thank you for your response. 

Yes! You are right,

But, some of our queries run complex calculations and sometimes take 7-8 minutes to complete.
5 or 6 of them are enough to crash my server.

Is there a way to manually call this automated cleaning process?

------------------------------
Ali Azimi

Original Message:
Sent: Tue September 27, 2022 02:13 PM
From: NIGEL CAMPBELL
Subject: SQL connection remains open after cancelling the report.

Database Connections are pooled and automatically closed after a period of idle activity.

------------------------------
NIGEL CAMPBELL

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

If you are having such issues, would suggest you open a support call if you haven't already.

As applicable, you may need to indicate if your concern is observed server side as one of:

1. a statement in the database server which is not stopping (i.e after an error or attempt to cancel)

2. an active result set which never closes

As noted in my first reply, database connections are pooled and automatically closed after an idle period.

When new queries are executed, an appropriate idle connection in the pool will be used before trying to create another one.

Should you believe that idle connections are not closing, and there is no active statement/resultset on that connection  the DBA has seen for a period of tiem then support can help gather logs to review.

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Tue September 27, 2022 02:36 PM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

Hello, Nigel

Thank you for your response. 

Yes! You are right,

But, some of our queries run complex calculations and sometimes take 7-8 minutes to complete.
5 or 6 of them are enough to crash my server.

Is there a way to manually call this automated cleaning process?

------------------------------
Ali Azimi

Original Message:
Sent: Tue September 27, 2022 02:13 PM
From: NIGEL CAMPBELL
Subject: SQL connection remains open after cancelling the report.

Database Connections are pooled and automatically closed after a period of idle activity.

------------------------------
NIGEL CAMPBELL

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

Hello Nigel,
We have had two incidents where the connections weren't closed automatically, i.e. accumulated in the database, which in turn led to a crash of the database and the entire container it resided in (Oracle). 

In other words, we'd also like to know if there is a way to force the connections to be closed when we see that they are accumulating more than up to a given roof.

/Sigrid

------------------------------
Sigrid Koch
------------------------------

Original Message:
Sent: Tue September 27, 2022 02:13 PM
From: NIGEL CAMPBELL
Subject: SQL connection remains open after cancelling the report.

Database Connections are pooled and automatically closed after a period of idle activity.

------------------------------
NIGEL CAMPBELL

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

What version and fix pack are running of Cognos?
I seem to recall this being an issue in 11.1.7 before the introduction of FP3 I think ( though dont quote me). We had a similar issue and after the FP3 was installed the issue did seem to disappear.......

However, after FP5 was installed we have seen the issue reappear and we are currently in the process of logging a support ticket with IBM Support.

------------------------------
jonathan chesterton
NHS Supply Chain
------------------------------

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

This has been an issue for as long as we've been on Cognos (since DecisionStream 7!).  Especially with those reports/queries that aren't written correctly or just when Cognos decides to generate really terrible SQL (more frequent than you'd imagine), in most cases I have to manually kill the connections on our database server (Sybase) or they will just run till completion - however many hours or days later.

In some cases it's the database server just too overloaded from the bad query (or configuration) to respond, but we've also traced (logged SQL statements on the database server) that Cognos doesn't always actually send a cancel request.  More frustrating that when it times out the connection pools it doesn't properly send a close request to the database server which results in an error in the server errorlog (host process discounnected...).

We are still CQM on 11.1.5 but in testing DQM and 11.1.7.x we still see the same thing happening.

Before our nightly ETL kicks off we have a script that kills ALL connections on the database server.


Wayne

------------------------------
Wayne Westlake
------------------------------

Original Message:
Sent: Wed September 28, 2022 05:03 AM
From: jonathan chesterton
Subject: SQL connection remains open after cancelling the report.

What version and fix pack are running of Cognos?
I seem to recall this being an issue in 11.1.7 before the introduction of FP3 I think ( though dont quote me). We had a similar issue and after the FP3 was installed the issue did seem to disappear.......

However, after FP5 was installed we have seen the issue reappear and we are currently in the process of logging a support ticket with IBM Support.

------------------------------
jonathan chesterton
NHS Supply Chain

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

Add connection close statement under cancel button code

------------------------------
Technology Thrill
------------------------------

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson

How can we add connection close statement under cancel button?

------------------------------
Sukru Haciyanli
------------------------------

Original Message:
Sent: Wed September 28, 2022 05:11 PM
From: Technology Thrill
Subject: SQL connection remains open after cancelling the report.

Add connection close statement under cancel button code

------------------------------
Technology Thrill

Original Message:
Sent: Tue September 27, 2022 11:45 AM
From: Ali Azimi
Subject: SQL connection remains open after cancelling the report.

I encountered a problem while creating and testing new reports in Cognos Analytics.

When report has a heavy query, a popup appears where users can choose a delivery method or cancel it.

Technically, the Cancel button should end the process and close all open connections to the SQL server, but in many cases, it does not, resulting in performance issues. Despite searching many pages, I couldn't find a solution. 

Does anyone have an idea?

------------------------------
Ali Azimi
------------------------------

#CognosAnalyticswithWatson