Supported SQL data types

The IBM Cognos Analytics query service supports the standard relational data types that represent numeric, character, or temporal values.

When data modules and models are built, and queries are planned and executed, the data source is required to describe the column metadata, such as the data type, precision, scale, and nullability, to the query service. This includes columns in tables or views that are returned by a query or passed as parameters to procedures, functions, or query parameters. The query service maps the source column data types to the types that it supports. If the source data type is not supported by the query service, the query service treats it as an unknown type.

The following list shows the data types that are supported by the query service:

Precise and imprecise numeric types

The following precise numeric types are supported: smallint, integer, bigint, decimal, and decfloat.

The following imprecise numeric types are supported: float (real treated as float), and double precision.

When database vendors support numeric data types that are equivalent to the types that the query service supports, the query service easily maps the source data types to the types that it supports.

When database vendors use a general "number" data type, where the range of values that a column or parameter can hold is determined by the column precision and scale, the query service must determine which of its built-in data types to use for the mapping. The query service assigns the data type based on the precision and scale of the metadata. For example, a column in ORACLE that is described as NUMBER(3) is mapped to the smallint type. Columns with higher precision are mapped to larger precise (integer, bigint, or decimal) or imprecise (double precision) data types. For very large numeric values, the query service can use the decfloat data type.

For more information, see ibmcognos.decfloat.

Character types

The following types are supported: char, varchar, clob, national char, national varchar, and national clob.

Character large objects (clob) can hold large strings and impose restrictions on how they can be used in a query. For more information, see the ibmcognos.maxvarcharsize parameter in Cognos-specific connection parameters.

The maximum length of a character string supported by dynamic query is 64 KB.

Datetime types

The following types are supported: date, time, time with time zone, timestamp, and timestamp with time zone.

Interval types

The following types are supported: interval year to month, and interval year to second.

Logical types

The supported type is Boolean.

The query service does not return the Boolean type to reports or dashboards.

Unknown types

The query service might not support a data type that is an equivalent of the source data type. A Framework Manager model or a data module that include columns with such data types show the type as an unknown data type. The query service can't perform any local query processing on values with the unknown data type, and the values can't be displayed in reports and dashboards.

A column of an unknown type can be referenced in expressions (calculations or filters) that are processed by the underlying data source. For example, a table includes a spatial column. A report or model might include a detail filter that the data source uses to evaluate if a customer is located within a distance from the specified spatial value. The data source must evaluate the expression in the filter.

If a table includes a bit string column, the report or model that uses the column can include an expression to convert the bit string to a type, such as integer, that is supported by the query engine. The expression must be supported by the data source.

Some data sources are supported through a vendor JDBC driver. In such cases, it might be possible to automatically convert the type and values of a built-in data type into a type that is supported by the query service. The query service would not be aware of the original data type. For more information about mapping the vendors built-in data types to JDBC data types, see the applicable SQL reference or programming guides from the vendors.