Enabling the stored procedures for the Interact Event Pattern report

The Interact Event Pattern report uses the delta refresh process for data aggregation process so that reports can render faster.

Procedure

Apart from the steps followed to enable reports, you must complete the following steps to enable the Interact Event Pattern report:

Browse to the <Interact_ReportPack_Installer_Home>\Cognos10\interact-ddl\<DB Type>\ folder.
For DB2®, set the following parameters:
- db2set DB2_COMPATIBILITY_VECTOR=ORA
- db2set DB2_ATS_ENABLE=YES
You can verify the execution by using the following command, if the database is active: db2 list active databases.
You can use the following solution to activate the DB2, explicitly.
1. Stop the application running on this instance by running command: db2 force application all.
2. Stop the DB2 by running the command: db2stop force.
3. Start the database by running the command: db2start.
4. Issue below command to activate the database explicitly : db2 activate db <dbname>.
  You should see this message: DB20000I The ACTIVATE DATABASE command completed successfully.
5. Verify that the database is activated by issuing command: db2 list active databases.
  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/
```
This procedure must be followed whenever the instance is restarted.
ATS depends on the table space to store historical data and configuration information. To check whether the table space is defined in the database or to create the table space, see http://www.ibm.com/developerworks/data/library/techarticle/dm-0809see/.
The ADMIN_TASK_STATUS is an administrative view that is created when the ADMIN_TASK_ADD procedure is called first time. These views must exist in the database. If the views are missing, create the views with the help of database administrator.
You must have access privilege on the ADMIN_TASK_STATUS administrative view.
Run the following scripts in a sequence on the ETL database:
1. acir_tables_<DB Type>.sql
2. acir_scripts_<DB Type>.sql
Note: You must run the acir_tables_<DB Type>.sql scripts if it has not been 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 For Interact Event Pattern report, changing the degree of parallel execution.
Note: Make sure that the SQL Server Agent service must be running.
Before the ETL process starts, run the following command on the ETL database to create parallel batch degree records in the UARI_PATTERN_LOCK table:
- For Oracle, run execute SP_POPULATE_PATTERN_LOCK(2), where degree is 2.
- For DB2, run call SP_POPULATE_PATTERN_LOCK(2), where degree is 2.
- For SQL Server, run EXEC [dbo].[SP_POPULATE_PATTERN_LOCK] @p_parallel_degree = 2, where degree is 2.
After you run the script, the UARI_PATTERN_LOCK table is populated with the stored procedures with the degree value. This 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 higher value, hardware resource requirements also increase proportionally. The number of procedures that are run for data aggregation depend on the degree value.
At the first run, ETL does not insert any values against the respective PatternID in the UARI_DELTA_PATTERNS table, as all the patterns are new or delta. The reports aggregation process collects all the PatternIDs from the ETL tables inserts them in the UARI_DELTA_PATTERNS table.
The ETL process calls the SP_AGGR_RUN_STATUS procedure. The SP_AGGR_RUN_STATUS procedure checks the UARI_PATTERN_LOCK table for running JobIDs. The JobID contains the flag 'Y' either in case of running or failed scenarios. If there are any failed jobs, the procedure sets the flag to 'N'. For running jobs, the value remains 'Y'. The ETL process always checks the status of the reports aggregation by checking status of the submitted jobs. If the ETL finds reports aggregation running, the ETL does not start its execution. The ETL starts after again as per the schedule.
The ETL process checks the UARI_PATTERN_LOCK table for the number of JobIDs with value Y. The ETL process starts only if the number of JobIDs with the value 'Y' is 0. If the number of JobIDs with value 'Y' is greater than 0, then the ETL process is skipped and it runs at the next scheduled interval. For more information about the ETL process, see the HCL® Interact Administrator's Guide.
From the second run onwards, the ETL process updates the UARI_DELTA_PATTERNS table with the update flag for the updated PatternID, for updated data, the Pattern ID is marked with 'U'. For deleted data, the PatternID is marked with 'D'.Newly added Patterns are identified by reports aggregation code and then inserted into UARI_DELTA_PATTERNS with the flag 'P'. The ETL always updates the flags in UARI_DELTA_PATTERNS table for the existing PatternID's and newly added patternID's are processed and inserted into UARI_DELTA_PATTERNS table by reports aggregation process.
The aggregation process is done for only the PatternIDs marked with the 'U' or 'D' flag.

Results

When The ETL finishes running 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.

What to do next

Note: It is possible that the first report aggregation takes longer time than subsequent aggregation. It is because the system is trying to aggregates all data for the first time.

Note: At any time if you want to turn off the aggregation process, while the ETL feature is, you can disable the trigger so that reports aggregation is not called.

Run following command in the respective database to disable Trigger.

For DB2 refer to http://www.ibm.com/developerworks/data/library/techarticle/0211swart/0211swart.html .

For Oracle: alter trigger TR_AGGREGATE_DELTA_PATTERNS disable;

For SQL Server: Disable Trigger TR_AGGREGATE_DELTA_PATTERNS on uaci_etlpatternstaterun

To turn on the reports aggregation: alter trigger TR_AGGREGATE_DELTA_PATTERNS enable;

For SQL Server: Enable Trigger TR_AGGREGATE_DELTA_PATTERNS on uaci_etlpatternstaterun