Enabling stored procedures for the Unica Interact Event Pattern report

In addition to the steps that you followed to enable reports, you must enable the Unica Interact Event Pattern report. The Unica Interact Event Pattern report uses the delta refresh process for data aggregation so that reports can render faster.

Before you begin

Administrative Task Scheduler (ATS) depends on table space to store historical data and configuration information.

To execute the scheduled job from Task Scheduler, the database must be active.

The ADMIN_TASK_STATUS is an administrative view that is created when the ADMIN_TASK_ADD procedure is called for the first time. These views must exist in the database. If the views are missing, create the views with the help of your database administrator. You must have access privilege on the ADMIN_TASK_STATUS administrative view. .

About this task

To enable stored procedures for the Unica Interact Event Pattern report, complete the following steps.

Procedure

Browse to the <Interact_ReportPack_Installer_Home>\cognos11\interact-ddl\<DB Type>\ folder.
For DB2®, set the following parameters:
- db2set DB2_COMPATIBILITY_VECTOR=ORA
- db2set DB2_ATS_ENABLE=YES
When the instance is restarted, you must activate DB2® by running the following commands in the order listed:
1. db2 force application all Stop the application on this instance.
2. db2stop force Stop DB2®.
3. db2start Start the database.
4. db2 activate db <dbname> Explicitly activate the database.
  You should see this message:
```
DB20000I The ACTIVATE DATABASE command completed successfully.
```
5. db2 list active databases Verify that the database is activated.
  You should see output similar to the following output.
```
Active Databases
Database name     = <dbname>
Applications connected currently     = 0
Database path    = /data04/<DB instance owner>/NODE0000/SQL00001/
```
On the ETL database, run the following scripts in the order listed:
1. acir_tables_<DB Type>.sql
2. acir_scripts_<DB Type>.sql
Note: You must run the acir_tables_<DB Type>.sql script if it was not run earlier.

Note: If an exception is thrown after you run the acir_scripts_db2.sql script on the target database, delete the trigger and create it with the appropriate database user.

For SQL server, run the acir_jobs_sqlserver.sql script. The script creates database jobs for degree 2. To change the degree, see Changing the degree of parallel execution for the Interact Event Pattern report.

Note: Make sure that the SQL Server Agent service is running.
Before the ETL process starts, you must create parallel batch degree records in the UARI_PATTERN_LOCK table. Run one of the following commands on the ETL database to create these records:
- For Oracle: execute SP_POPULATE_PATTERN_LOCK(2)
- For DB2®: call SP_POPULATE_PATTERN_LOCK(2)
- For SQL Server: EXEC [dbo].[SP_POPULATE_PATTERN_LOCK] @p_parallel_degree = 2
In this example, 2 is the degree at which the aggregation processes run in parallel.
The UARI_PATTERN_LOCK table is populated with the stored procedures with the degree value. The degree value is configurable. Increase the degree of parallel execution for the Interact Event Pattern report aggregation process to reduce the elapse time. If the degree is set to a higher value, hardware resource requirements increase proportionally. The number of procedures that are run for data aggregation depend on the degree value.
Optional: While the ETL feature is running, you can disable the trigger so that reports aggregation is not called. To disable the trigger and turn off the reports aggregation process, run one of the following commands depending on your database type:
- For DB2® refer to the documentation
- For Oracle: alter trigger TR_AGGREGATE_DELTA_PATTERNS disable;
- For SQL Server: Disable Trigger TR_AGGREGATE_DELTA_PATTERNS on uaci_etlpatternstaterun
Optional: To enable the trigger and turn on the reports aggregation process, run one of the following commands depending on your database type:
- For DB2® refer to the documentation.
- For Oracle: alter trigger TR_AGGREGATE_DELTA_PATTERNS enable;
- For SQL Server: Enable Trigger TR_AGGREGATE_DELTA_PATTERNS on uaci_etlpatternstaterun

Results

When ETL completes successfully, the status in the UACI_ETLPATTERNSTATERUN table is updated as 3, and the trigger TR_AGGREGATE_DELTA_PATTERNS is called. The trigger calls the stored procedure for the set parallel degree.

Note: When the system aggregates all data for the first time, the report aggregation process may take a longer time than subsequent aggregations.