Document Purpose

The bug reported in BISERVER-2735 resulted in various changes in platform components. Imposing row limits and timeouts on queries is sometimes important to avoid causing out of memory errors or processes that consume too many resources on the database server. The purpose of this document is to document the ways to control row limits, timeouts and the read-only setting in the JDBC connection object.

Affected Platform Areas

• Action sequences
  • Certain action sequence (BI) components have been updated to recognize some or all of the new capabilities as action inputs.
• Web-based Adhoc Query And Reporting
  • If appropriate meta-data is provided on the business model, Web-based adhoc will respect the settings and limit the queries accordingly.
• Pentaho Metadata Editor (PME)
  • PME can be used to provide the settings that will be respected by the metadata query components in the platform.

Affected Components

• SQLBaseComponent and subclasses
  • SQLLookupRule
  • MQLRelationalDataComponent
• HQLBaseComponent and subclasses
  • HQLLookupRule
• XQueryBaseComponent and subclasses
  • XQueryLookupRule

New component inputs

The components above now accept up to three new "known" optional inputs.

max_rows

Data Type: Integer
Limit: Must be greater or equal to zero

All of the above components look for, and respect the new input max_rows. For SQLBaseComponent/SQLLookupRule, MQLRelationalDataComponent, and HQLBaseComponent/HQLLookupRule, when max_rows comes in as an input, the value is ultimately passed into the JDBC statement object. For XQueryBaseComponent/XQueryLookupRule, the data limiting is performing during document parse. If the underlying JDBC driver does not support this setting, the action sequence will be terminated.

Care should be used when limiting the number of rows returned by a query because the user will never know that the results are being truncated.

timeout

Data Type: Integer
Limit: Must be greater than zero
Scale: seconds

Of the above components, only the JDBC-based components (SQLBaseComponent/SQLLookupRule, MQLRelationalComponent, HQLBaseComponent/HQLLookupRule) provide support for this setting. The query timeout is passed into the underlying JDBC statement object. Queries that take longer than the specified time (in seconds) will fail and an exception will be thrown to the server.

read_only

Data Type: Boolean
Limit: Must be true or false

Of the above components, only the components (SQLBaseComponent/SQLLookupRule, MQLRelationalComponent) provide support for this setting. The read-only value is passed into the underlying JDBC connection object.

Creating global defaults for these settings

If you want to set a global default for max rows, query timeout, or read-only, you can modify the bean definition for the SQL Connection bean in the file pentaho-solutions/system/pentahoObjects.spring.xml and add the default properties there. Below is an example that shows setting the global defaults to 10,000 rows, 90 seconds for query timeout, and true for read-only.

<bean id="connection-SQL" class="org.pentaho.platform.plugin.services.connections.sql.SQLConnection" scope="prototype">
  <property name="maxRows" value="10000" />
  <property name="queryTimeout" value="90" /> <!-- In Seconds -->
  <property name="readOnly" value="true" />
</bean>

With the defaults above applied, every time a SQLConnection object is created by the Pentaho Object Factory, those properties will be injected into the connection by default. The defaults of course will be overridden by settings in the metadata model, and also at the individual component level in an action sequence.