Storing data from uploaded files and data sets in Db2

You can use a Db2 database to store data from uploaded files and data sets. The data is stored as relational tables, and managed by the Data sets server connection in Cognos® Analytics.

The benefits of using Db2 include:

  • Reduced workload on Content Manager, which doesn’t receive, return, or store Parquet files.
  • The query execution is offloaded from the Cognos Analytics application tier to the Db2 server.

    The Compute service is used only when Parquet files are required.

  • The Content Manager size restrictions are not applicable.

    Data is stored in a separate database instance and dependent on the storage space and compute capacity of the Db2 server.

    For more information, see Limiting the size of a request.

Supported Db2 products and versions

The Db2 versions that can be used with the Data sets server connections are the same as the Db2 versions supported by Cognos Analytics for query databases. For more information, see supported software environments (www.ibm.com/support/pages/node/6966712).

Db2 Z, Db2 I, or other products that are based on the Db2 common engine, such as Big SQL, are not supported. Connections that reference an unsupported product or version are blocked when a file is uploaded or data set executed.

The Db2 instance must meet the following requirements:

  • The Db2 instance must be a supported, major version of Db2 LUW or Db2 Warehouse.

    Run-time checks block connections to unsupported products or versions.

  • The Db2 instance must be configured for analytic workloads.

    Tables are created using columnar organization.

  • Db2 server must be installed on Unix or Linux.

    The Windows version does not support the Db2 external table capability.

For more information, see the following Db2 documentation and other publications:

Preparing the Db2 environment

The Db2 administrator must prepare the Db2 environment in the following way:

  • Create a Db2 user and Db2 schema.
  • Grant the Db2 user the following privileges for the schema tables: CREATE, DROP, INSERT, and SELECT.

    For more information, see the Db2 documentation GRANT (schema privileges and authorities) statement (www.ibm.com/docs/en/db2/11.5?topic=statements-grant-schema-privileges-authorities).

  • If you manage your own Db2 servers, versus use a fully managed Db2 Warehouse on Cloud, you can use larger Db2 page sizes. For more information, see the following Db2 documentation:

Creating connections to the data sets server

When you have a Db2 database ready to use for storage of data from uploaded files and data sets, in Cognos Analytics you need to create one or more Data sets store connections to Db2.

  • If only one connection is defined, all data is stored through that connection.
  • Two or more Data sets store connections can be defined that reference different schemas in the same Db2 instance or a different Db2 instance.
    Important: Each Cognos Analytics user can have access to only one Data sets store connection. If two or more Data sets store connections are defined, you must configure, for each user, a single Data sets store connection that they can access. For more information, see Managing multiple connections to the same data source.

A Db2 schema should not be referenced by multiple Data sets store connections or multiple Cognos Analytics installations.

For example, if you plan to use the same Db2 instance for the development and production environments, create separate schemas for the two environments, and ensure that the connection references the appropriate schema. If the same Db2 instance is to be used by two or more Cognos Analytics installations, create separate schemas for each installation.

The Data sets store connection is used to write data into Db2 and query data from Db2. This connection cannot be used for any other purpose than the storage of data from uploaded files and data sets.

To create the connection, follow the steps in the topic Creating a data sets store connection.

Managing the Db2 schema contents

Cognos Analytics manages the lifecycle of tables that are stored in a Db2 schema from creation (CREATE TABLE) to removal (DROP TABLE). If the Db2 schema is empty and no longer required, the Db2 administrator can delete it.

If the Db2 schema is dropped or not accessible, errors that include the Db2 SQLSTATE and SQLCODES codes will occur in Cognos Analytics. As a result, users who could previously upload files or execute data sets stored in the schema might not be able to analyze the data in their dashboards and reports.

For more information, see Cognos Analytics error messages related to data sets store connections.