Some reports are capable of firing quite resource consuming queries on the data source. This can cause a bottleneck on the database and hence a problem for other users and jobs. This recipe will show you how to automatically get the report query killed if it takes longer than a certain time limit.
Create a simple list report based on GO Data Warehouse (Query) package. Pull Product line, Product type, and Product name as columns.
- Open the report in Cognos Report Studio.
- Go to Query Explorer and select Query1.
- From Properties pane, open Maximum Execution Time property.
- Set it to a low number. For testing, we will set it to 1.
That means we are setting the maximum execution time for the query to one second.
- Run the report to test it. In most cases, this will cross the threshold of one second and you will receive an error message like the one shown as follows:
- Now go back to Cognos Report Studio and increase the Maximum Execution Time for query to 30 sec and run the report. It will run fine.
In this recipe, we experimented with the Maximum Execution Time property of the query. As you can see, this property allows us to terminate the report execution automatically if the query is taking a long time.
It is a useful property for reports where users can accidentally or purposely put some highly resource consuming selection parameters. For example, if a report is supposed to be run for a small date range, some users might mistakenly run it for months or years, hence hammering the database. This can take up a lot of database time and might also affect other jobs running on the server.
By putting a time limit on it, we can ensure that report is automatically terminated if it is going on for a certain length of time.
However, this time limit is not for the total time taken by the query on database. It is for the time lapsed from query submission to the first result returned back. In HTML output, often a page full of data is returned quickly and hence the report might not show an error. Whereas, when same report is run in PDF or Excel, it might reach the threshold and error.
Also, there is no easy way to customize the error message. As you can see, it is an ugly message, but we have to live with it.
The maximum time limit can also be set at package level in Framework Manager using the Governors. Please refer to Framework Manager Documentation for the same.
The administrator can also define an environment-wide query execution time limit from Connection Portal by configuring the report service. For that, please refer to the Administration and Security Guide.