ReportServer - ReportServer Configuration Guide 4.6.1

4.1. Datasources

In this section we will cover:

how to define the default datasource,
how to define datasource bundles (only available in ReportServer Enterprise),
how to configure the connection pool,
how to configure the internal db

4.1.1. Defining the Default Datasource

ReportServer allows to configure a single default datasource. The default datasource can then be accessed with only a single mouse click when working with reports and parameters. The default datasource is set in the file /fileserver/etc/datasources/datasources.cf.

You can use the key of the datasource or use the ID of the datasource for the assignment. Use one of the two variants for the configuration:

	<defaultDatasourceKey>MY_DATASOURCE<defaultDatasourceKey>
	<defaultDatasourceId>12</defaultDatasourceId>

Remark. Keep in mind that the datasource key is case sensitive.

4.1.2. Configuring the Connection Pool

By default, ReportServer will pool connections to relational database systems. This increases the stability of the system, since you can define an upper limit of simultaneously open connections and, furthermore, it improves the performance at the same time since database connections are kept open and ready for use.

The connection pools can be configured both globally and per datasource. The configuration is done in the file /fileserver/etc/datasources/pool.cf.

To not use connection pools, change the attribute "disable" to "true": <pool disable="false">.

ReportServer uses the library C3P0 (http://www.mchange.com/projects/c3p0/) to perform connection pooling. To globally set a property, set the property within the <defaultconfig> tags. For data-source-specific settings, use the tag <pool16>, where 16 is the ID of the data source.

	<?xml version="1.0" encoding="UTF-8"?>
	<configuration>
		<pool>
			<defaultconfig>
				<maxPoolSize>40</maxPoolSize>
				<initialPoolSize>10</initialPoolSize>
				<acquireRetryAttempts>10</acquireRetryAttempts>
				<acquireRetryDelay>500</acquireRetryDelay>
				<checkoutTimeout>60000</checkoutTimeout>
				<maxConnectionAge>7200</maxConnectionAge>
				<maxIdleTime>3600</maxIdleTime>
			</defaultconfig>
			<pool16>
				<acquireRetryAttempts>20</acquireRetryAttempts>
			</pool16>
		</pool>
	</configuration>

The possible configuration settings of C3P0 can be found at http://www.mchange.com/projects/c3p0/#configuration_properties. ReportServer simply passes on any set property to C3P0.

Note that as of ReportServer 4.0.0, the current state of the connections (including total max pool size, busy connections and number of connections) is visualized in the "Connection Pool" System Console.

4.1.3. Internal database

ReportServer uses a database to buffer data coming from non-database datasources such as, for example, CSV datasources. This buffer database is called the internal database. Per default ReportServer uses the its own database for this and creates tables with the prefix rs_tmptbl_. You can change the database to be used as well as configure certain aspects of the internal database via the configuration file datasources/internaldb.cf. The default configuration file is

<configuration>
   <internaldb>
      <droponstartup>true</droponstartup>
      <datasource>REPORTSERVER_DATASOURCE</datasource>
   </internaldb>
</configuration>

The droponstartup tells ReportServer to remove any temporary tables on startup. This should only be turned off for debugging purposes as having old temporary tables still available after startup will cause errors when using the internal DB. Via the datasource tag you can define the datasource (via its key) to be used as the internal database.

4.1.4. SQL limits and parameter options

The datasource parameter (a parameter that can be used to allow the user to select values from a predefined set) can return a single value or a list of values. If a user selects multiple values (or uses many filters in a dynamic list) this might lead to problems with certain database systems. This is due to the fact that the number of values that can be used in IN clauses is limited for most database systems.

You can specify the maximum number of values that are to be used in an IN clause in the file /fileserver/etc/datasources/sql.cf. ReportServer will then distribute the selected values over multiple IN-clauses.

For this set the following parameter

<incondition>
  <maxsize>1000</maxsize>
</incondition>

in accordance with the prerequisites of the used database system.

4.1.5. The datasource parameter

Queries that run for a long time have an impact on the performance of ReportServer. You can specify how long ReportServer should wait for the results of a parameter query. A parameter query is executed when a report is opened.

You can specify a timeout for long running parameter queries in the configuration file /fileserver/etc/datasources/parameter.cf.

Set the parameter <querytimeout>60</querytimeout> to stop queries after 60 seconds.

If you want to use the "post-processing" feature of the datasource parameter enable it using the following lines:

	<postprocessing>
		<enable>true</enable>
	</postprocessing>

In general it is good practice to have parameter queries optimized such that they run very fast such as to provide a good user experience.

The post-processing feature, for example, allows to switch parameter values or perform complex string operations. Learn more about datasource parameter post-processing in the parameter chapter in the administrator's guide.