Version 2021

Connection Management

No PostgreSQL specific customizations were made for this feature.

A warehouse database connection is initiated any time a user executes an un-cached report or browses un-cached elements. The Database Connection Monitor enables you to view the number of busy and cached connections to the data warehouse. You can also view the name of the database instance, the user who is using the connection, and the database login being used to connect to the database.

If a database connection is cached, the ODBC connection from Intelligence server to the data warehouse remains open. However, if the data warehouse connection surpasses the connection time-out or lifetime governors (set in the Database Connections dialog box, on the Advanced tab), the ODBC connection closes, and it no longer displays in the Database Connection Monitor.

View the Current Database Connections

  1. In Developer, log in to a project source. You must log in as a user with the Monitor Database Connections privilege.

  2. Go to Administration > System Monitors > Database Connections. The database connection information displays on the right-hand side.

    Database connection threads can also be managed via Developer. The settings which control database connections are located on Database Instance level:

Maximum Cancel Attempt Time

When a user runs a report that executes for a long time on the data warehouse, the user can cancel the job execution. This may be due to an error in the report’s design, especially if it is in a project in a development environment, or the user may simply not want to wait any longer. If the cancel is not successful after 30 seconds, Intelligence server deletes that job’s database connection thread. The Maximum cancel attempt time (sec) controls how long you want Intelligence server to wait in addition to the 30 seconds before deleting the thread.

Maximum Query Execution Time

This is the maximum amount of time that a single pass of SQL can execute on the data warehouse. When the SQL statement or fetch operation begins, a timer starts counting. If the Maximum query execution time (sec) limit is reached before the SQL operation is concluded, Intelligence server cancels the operation.

This setting is very similar to the SQL time out (per pass) VLDB setting (see Limiting a Report's SQL Per Pass). That VLDB setting overrides Maximum query execution time (sec). This setting is made on the database connection and can be used to govern the maximum query execution time across all projects that use that connection. The VLDB setting overrides this setting for a specific report.

Maximum Connection Attempt Time

This is the maximum amount of time that Intelligence server waits while attempting to connect to the data warehouse. When the connection is initiated, a timer starts counting. If the Maximum connection attempt time (sec) limit is reached before the connection is successful, the connection is canceled, and an error message appears.

Connection Lifetime

This is the amount of time that an ODBC connection is kept alive. The entire duration of the connection cannot be longer than this limit. A value of 0 indicates the connection is not cached and is immediately deleted after the completion of execution on the database. Even though the lifetime of the connection is 0, a new database connection is spawned when required and after the completion of the job, the connection is immediately deleted. A value of -1 indicates that there is no limit on the lifetime of the connection. Depending on the status of the connection at the time the limit is reached, several things can happen:

  1. If the database connection has a status of Cached (it is idle, but available) when the limit is reached, the connection is deleted.
  2. If the database connection has a status of Busy (it is executing a job) when the limit is reached, the job completes and then the connection is deleted and does not go into a Cached state.

Connection Idle Timeout

This is the amount of time an inactive connection thread remains cached in Intelligence server until it is terminated. When a database connection finishes a job and there is no job waiting to use it, the connection is cached, and a timer starts counting. If the time reaches the connection idle timeout limit, the database connection thread is deleted. This is used to prevent connections from tying up data warehouse and Intelligence server resources if they are not needed. A value of 0 indicates that a connection is not be cached, for example, as soon as an active connection becomes idle, that connection is deleted. A value of -1 indicates that there is no limit on the time that a connection can remain idle. If the connection lifetime limit is smaller than the idle timeout limit, the connection is deleted once the connection lifetime limit is reached.

Administrators can, based on priority, specify the number of warehouse connections that are required for efficient job processing. There are three possible priorities for a job: high, medium and low.

Administrators are not required to set medium and high connections, but must set at least one low connection, because low priority is the default job priority.

The optimal number of connections is dependent on several factors, however the main criterion to consider when setting the number of connections is the number of concurrent queries the warehouse database can support.