Cognos-specific connection parameters

This parameter is used to set the JDBC driver fetch size for data source connections in IBM Cognos Analytics.

When the query service in IBM Cognos Analytics executes queries by using JDBC, the fetch size value that is passed to a JDBC driver is calculated dynamically. Support for fetch sizes depends on database vendors. The vendors also decide what the fetch size means, and what the fetch size is when it is used internally in the driver and server. For more details, refer to your vendor’s JDBC documentation.

The query service computes a value for a query by using the following formula: maximum( (bufferSize / ‘row-size’), 10)

The default value for buffer size is 100 kilobytes (KB). The row size is computed from the size of the columns that are projected by the result set in a query. Queries that project columns with large precision or project many columns use a smaller fetch size than those projecting fewer columns or columns with smaller precision.

If the retrieval of a result set can be significantly improved by using a larger buffer size, a Cognos administrator can specify the connection property ibmcognos.fetchBufferSize. The query service automatically adjusts the value if it is lower than 10 kilobytes or greater than 10 megabytes.

If ibmcognos.fetchBufferSize > 1024 * 10240 then bufferSize = 1024 * 10240

If ibmcognos.fetchBufferSize < 10240 then bufferSize = 10240

Larger fetch sizes are not always recommended because they can potentially increase the memory consumption by the JDBC driver and not lead to improved performance. Always review the database vendor documentation and recommended practices before using large values for the ibmcognos.fetchBufferSize property.

When this parameter is specified, the query service is directed to use a decimal float type, DECFLOAT 128, which accurately represents values with precision of up to 34 digits. When a column with large precision is detected, it is internally changed to DECFLOAT and the data type in the model or report is described as DECIMAL(0,0).

To enable this feature, specify the connection parameter ibmcognos.decfloat=true for the database connection that is used by the query service. In existing models, the columns must be remapped to DECIMAL(0,0) instead of double.

For the query service to read the rows that are returned by a query, the JDBC driver must return the column values using a specific Java™ data type. In previous releases, it was possible for a database such as ORACLE to return a numeric column where the precision caused the query service to use the double data type. When the values that were returned by a query had precision greater than 16 digits, the conversion could result in an inaccurate value.

For example, if an ORACLE column was defined as NUMBER (without stating precision), or an aggregate such as SUM was computed that ORACLE returned as a NUMBER, the returned value of 1234567890123456789 might be converted to the value of 1.23456789012345677E18. The two values are not the same.

If the database does not return large values, do not use this parameter and ensure that the models do not include columns with the DECIMAL(0,0) data type. This allows the query service to use a data type that requires less memory than the DECFLOAT type.

This parameter is used to disambiguate metadata when dynamic queries are executed. It assigns a list of one or more qualifiers to data sources that are defined in IBM Cognos Analytics.

The following examples show the syntax to use when specifying the ibmcognos.qualifier_list parameter, and the values that can be assigned for it:

• ibmcognos.qualifier_list=CATALOG1.SCHEMA1, CATALOG2.SCHEMA2
• ibmcognos.qualifier_list=SCHEMA1, SCHEMA2
• ibmcognos.qualifier_list=CATALOG1.SCHEMA1, SCHEMA2
• ibmcognos.qualifier_list=CATALOG1, CATALOG2

A period in the qualifier is used to separate the catalog and schema components. If no period is present and the database supports schemas, the value is treated as a schema. Otherwise, the value is treated as a catalog, if the database supports catalogs.

The query service searches the list in the order specified, and uses the column metadata that it finds for the first qualifier that matches. If no match is found, an ambiguous metadata error is thrown.

The administrator should confirm that the list of qualifiers that are provided for this parameter is identical in order and content to any search list that the user’s database session might have defined. The qualifier list is applied only when the session attempts to disambiguate metadata that is returned by a JDBC driver. Qualified names in dynamic SQL statements reflect the values assigned to catalog or schema properties that the package data source used during query planning.

This parameter is used to configure data source connections when using Kerberos authentication.

For the different data source connection types, specify ibmcognos.authentication=java_krb5, and then add the properties that are required by the JDBC driver for Kerberos authentication, if they are required. The following examples show how to specify this parameter for some data source connections:

• For Teradata connections, specify ibmcognos.authentication=java_krb5;LOGMECH=KRB5;
• For SAP-HANA connections, specify ibmcognos.authentication=java_krb5;
• For Microsoft SQL Server connections, specify ibmcognos.authentication=java_krb5;authenticationScheme=JavaKerberos;

The ibmcognos.maxRowsRetrieved property on a data server connection can be used to set the maximum number of rows that are returned in an SQL query.

This property is applicable for the dynamic query mode (DQM) only, and can be used to prevent users from executing queries which retrieve large numbers of rows from the database server.

Use the following syntax to specify this property, where N represents the maximum number of rows to return:

ibmcognos.maxRowsRetrieved=N

The N value must be an integer greater than 0 and less or equal to 2147483647.

An exception is thrown if an invalid value is detected. By default, no limit is applied to the number of rows that are returned.

Not setting this property, or setting it to 0, means that there is no limit.

The query service can use a larger default VARCHAR precision value than the default value that is supported by the database. This parameter is used to override the database default VARCHAR precision value for the query service.

ibmcognos.maxvarcharsize=N

The SQL standard uses the CLOB data type and the national character large object type (NCLOB) to hold large character values. Different databases support the CLOB data type or their own versions of this type with similar characteristics. The CLOB data type imposes several restrictions on the types of SQL constructs that can be used in queries. Also, database vendors might impose additional restrictions on how CLOB columns must be handled in the client interfaces, such as JDBC. To avoid CLOB-related restrictions, the query service automatically converts CLOB columns into VARCHAR columns by using the CAST function. As a result, the first N characters of the CLOB type are returned as VARCHAR to the query service.

In some cases, a database vendor might support a larger precision if specific database configuration settings, such as page and row size, or server settings, are satisfied. If such preconditions are satisfied, a larger value can be specified on a data server connection. If the preconditions are not satisfied, when you use a value greater than the one that is supported by the database, the SQL statements fail to execute. Before using larger VARCHAR precision values, refer to the database vendor documentation, and verify the value with the database administrator.

The query service uses the following default VARCHAR precision values for the different databases:

If the ibmcognos.maxvarcharsize value is higher than the Java Integer max (2147483647), or not an integer at all, the value is ignored.

If the ibmcognos.maxvarcharsize value is lower than both the default 1024 and the vendor VARCHAR size, the lowest of these 2 values is used instead of the ibmcognos.maxvarcharsize value.

When this property is specified, queries that are based on typed-in SQL are not allowed by the connection. ibmcognos.typeinsqldisabled can be applied to any type of SQL object, for example, a table or a data module.

If you try to create an SQL-based table after this property was specified, the table will not be created. If you specify this property after an SQL-based table was created, the query execution is stopped.