Setting up the database schema

The installation and integration of IBM Cognos® Analytics is available only for technology preview, and is not for production usage.

As part of setting up yourIBM Db2 integration for use with reporting, you need to set up the IBM Db2 reporter schema for issue resolution data. This schema includes tables for alerts, incidents, and the adjoining tables that contain metadata that details changes to incidents and alerts.

Note: For any customization of the IBM Db2 schema, your policies must be adjusted to push data to those fields. For more information, see the section "Customize" within the topic Populate an external database.

Before you begin

You must have IBM Db2 installed (on a virtual machine) before proceeding. For more information, see Installing IBM Db2 and Creating an IBM Db2 integration.

Setting up the reporting schema

Download the database setup scripts to your target IBM Db2 machine. The schemas can be installed from: github.com: aiops-cognos-analytics/schemas.
Create a database for reporting by using the IBM Db2 command line:
```
db2 CONNECT TO <name> USER <user> USING <password>
```

Connect to your newly created database:

 db2 CONNECT TO <name> USER <user> USING <password>

(Optional) To populate the reporter tables with custom fields for alerts or incidents (or both), the scripts that you downloaded need to be augmented. The added fields in the schema need to be populated by a dashboard policy by using the parameter mapping capability:
- For customizing the alerts reporter table:
  1. Open the db/reporter_aiops_alerts.sql file.
  2. Append a column definition inside the CREATE TABLE ALERTS_REPORTER_STATUS block. The definition must define the appropriate name and data type that represents the target incident property, for example:
```
CREATE TABLE ALERTS_REPORTER_STATUS (
    tenantid            VARCHAR(64) NOT NULL,
    ...
    customfield      VARCHAR(255)
    PRIMARY KEY (uuid) )@
```
- For customizing the incidents reporter table:
  1. Open the db/reporter_aiops_incidents.sql file.
  2. Append a column definition inside the CREATE TABLE INCIDENTS_REPORTER_STATUS block. The definition must define the appropriate name and data type that represents the target incident property, for example:
```
CREATE TABLE INCIDENTS_REPORTER_STATUS (
    tenantid            VARCHAR(64) NOT NULL,
    ...
    customfield      VARCHAR(255)
    PRIMARY KEY (uuid) )@
```

Run the SQL scripts to set up the reporting schema with the IBM Db2 command line:

db2 -td@ -vf db2/reporter_aiops_alerts.sql

db2 -td@ -vf db2/reporter_aiops_incidents.sql

db2 -td@ -vf db2/reporter_aiops_noise_reduction.sql

Best practices

When you change the integration schema, ensure that you update the dashboard policy. As table definitions can then differ, the policy needs to be updated with the new tables.
If the number of expected events is very large, set the maximum pool size to 10.
Check the database about any limitation on the maximum number of connections:
```
SELECT count(*) FROM SYSIBMADM.SNAPAPPL;
```

Limitations

If you are using your own script, ensure that the main tables for alerts and incidents are not using autogenerated columns.
The following datatypes are not supporteD: BLOB, CLOB, DBCLOB.
For the throughput, with the provided scripts, you might see a rate of 700/s for inserts and 300-400/s for updates. In case more throughput is needed, remove triggers when no auditing is required.

Upgrading the Cloud Pak for AIOps reporting schema

If the schema setup procedure was completed prior to IBM Cloud Pak for AIOps 4.9.0, the upgrade procedure must be updated.

Download the upgrade script to your IBM Db2 machine. Add the script to the same directory as your IBM Db2 setup scripts. The script can be downloaded from: github.com: aiops-cognos-analytics/schemas/db2.
Connect to your IBM Db2 Cloud Pak for AIOps reporting database:
```
db2 CONNECT TO <name> USER <user> USING <password>
```
Run the upgrade script:
```
db2 -t -vf db2/upgrade.sql
```

Removing the Cloud Pak for AIOps reporting schema

Note: The removal scripts remove all Cloud Pak for AIOps reporting data and schema components from your database.

Connect to your IBM Db2 Cloud Pak for AIOps reporting database:
```
db2 CONNECT TO <name> USER <user> USING <password>
```

Run the removal scripts:

db2 -td@ -vf db2/reporter_aiops_alerts_remove.sql

db2 -td@ -vf db2/reporter_aiops_incidents_remove.sql

db2 -td@ -vf db2/reporter_aiops_noise_reduction_remove.sql