Creating a data sets store connection

To use Db2 to store data from uploaded files and data sets, you need to create one or more Data sets store connections to the Db2 instances or schemas. These connections are used to write data into Db2 and query data from Db2.

After the connections are created, data from new uploaded files and data sets is stored in this database.

Before you begin

A copy of the IBM JCC JDBC driver file must exist in the Cognos® Analytics install_location\drivers directory of the server where the Content Manager service is deployed and the servers where dynamic query is deployed.

All Db2 requirements are met, and the Db2 environment is prepared by the Db2 administrator, as documented in the topic Storing data from uploaded files and data sets in Db2.

About this task

Use the following guidelines when creating the connection:

  • The connection must have a sign-on.

    Prompting a user for credentials at run-time, or using other authentication methods that require user input, are not supported.

  • Multiple connections can be defined.

    Each connection must reference a different Db2 schema in the same Db2 instance, or different Db2 instances. If two or more connections are defined for the same schema or database instance, an exception is thrown at execution time. Ambiguous connections are not supported. Apply Cognos Analytics security rules to ensure that Cognos Analytics users can access only one connection. If two or more connections are detected for a user, an ambiguous connection error occurs. For more information, see Managing multiple connections to the same data source.

  • Apply Cognos Analytics security rules for each connection to restrict the run-time access to the connection for the user who uploads files or executes data sets.
  • Optionally, use the ibmcognos.qualifier_list or IBM JCC currentschema name-value pairs to specify the Db2 schema name that will be used to qualify tables in DML and DDL. The value must correspond to an existing Db2 schema for which the Db2 user was granted access privileges.

    If the ibmcognos.qualifier_list property is not defined, Db2 will default the schema based on the Db2 user or the IBM JCC currentschema name-value pair. For more information, see Db2 documentation.

Procedure

  1. Go to the Create data server connection wizard.
    1. Click the Open menu icon Open menu icon.
    2. Click New > Data sets store.
  2. In the Create data sets store connection page, complete the following steps:
    1. Add a name and description (optional) for the connection.
    2. In the Connection type field, select IBM Db2 or IBM Db2 Warehouse.
    3. If you are a system administrator, you can select a tenant.
    4. Click Next.
  3. Enter the connection details.
    1. Specify the JDBC URL. You can view the syntax and example URL under connection details. You might need to ask the administrator for more details, or check the Db2 documentation.
    2. For the Driver class name, use the default that is provided by the application.
    3. Optional: In the Connection properties box, type the ibmcognos.qualifier_list property and specify its value. For example, typing ibmcognos.qualifier_list=DSETUSR1 would tell Cognos Analytics to qualify DDL or DML sent to Db2 with the schema name DSETUSR1.
    4. Select the default isolation level.
    5. Under Authentication method, select Use signon, and click Add signon.

      Define the signon for the connection.

    6. Click Test connection to verify that the data sets store connection works.

      Confirm that the result of the test returns the subtype sql or dashdb. If the subtype is dsn, qsq or BigSQL, correct the URL to refer to a supported Db2 server.

      Tip:

      When you test the connection, the value of the Db2 tableOrganization configuration setting appears. When a table is created in a schema, Db2 will default the organization of the table to the value displayed for tableOrganization. The default value should be Columnar to leverage the capabilities of the Db2 columnar engine. Otherwise, the Db2 row engine is used.

      For more information, see Setting the default table organization on the IBM Db2 Documentation page (https://www.ibm.com/docs/en/db2/11.5?topic=organization-setting-default-table).
    7. Click Save.

Results

The Data sets store connection appears in Manage > Data server connections.

The Data location property for any new uploaded file or data set shows the name of the Data sets store connection.

What to do next

To manage the connection, find it in the Data server connections page, and click it. Then, click the connection context menu Context menu icon.

Data sets server connection context menu

Use the available menu options to edit or delete the connection. For example, to specify security permissions for the connection, click Properties, and on the Permissions tab, add the users, groups, or roles that can use the connection, and grant the required access type for them.

Do not delete or disable the connection while data sets are in the database. Doing so would prevent queries, refresh, append, or deletion of data that was loaded into Db2. Delete the connection only if it is no longer required, for example, after the Db2 administrator removes the applicable schema in Db2.