SQLRowsetSize

Added in v2.0.3 Specifies the maximum number of rows to retrieve per ODBC request.

This parameter can be used to improve execution time for long-running processes that are caused by slow Microsoft SQL queries. It can benefit any users that use an ODBC driver that does not have a fetch setting, such as MS SQL and Db2®. It does not affect Oracle database users.

This parameter is not applicable to Planning Analytics Engine.

Parameter type: optional, dynamic

Note: This parameter applies to all data sources even if you are pulling data from multiple different data sources.

To set this parameter, add the following line to the Planning Analytics database configuration:

SQLRowsetSize=nn

Where nn is any positive integer that represents the maximum number of ODBC requests to make.

The minimum value is 50 (default).

For cloud only customers, the default value is 500. The entry SQLRowsetSize=500 is added to the Planning Analytics database configuration for any new provision in IBM® Planning Analytics on Cloud. If the setting exists in the Planning Analytics database configuration, it is not changed.

The recommended value depends on the long-running process that you are trying to improve processing for. You can double the values (100, 200, and so on) to test for the best results. Increasing the value of SQLRowsetSize fetches more results per ODBC request and incurs network latency fewer times.

A larger value means that more memory is used per fetch. You need to look at memory consumption to see whether the increase in memory usage is acceptable (it might be noticeable if you have lots of data per row).