Stored procedures for the Interact Event Pattern report

The Interact Event Pattern report uses the data that is contained in staging tables, which are populated by stored procedures. The stored procedures perform a delta refresh operation.

Interact Event Pattern report data is processed in two steps:

  1. The Interact ETL process transforms the audience blob data into ETL database tables.
  2. The reports aggregator aggregates the data incrementally for each pattern type in preconfigured parallel execution. This is specific Interact reports pack.

Both processes are integrated with the database trigger on the UACI_ETLPATTERNSTATERUN table. This trigger is fired on successful ETL execution and submits database jobs to aggregate the reports data.

The following tables provide information about the stored procedures and the tasks that they complete.

Stored procedures for the Interact Event Pattern report
Stored procedure Task
SP_GENERATE_PATTERN_MATCHALL Called internally by the SP_POPULATE_PATTERN_MATCHALL procedure. Responsible for retrieving the data for Match All patterns that were executed since the previous run of the stored procedures.
SP_GENERATE_PATTERN_COUNTER Called internally by the SP_POPULATE_PATTERN_COUNTERprocedure. Responsible for retrieving the data for Counter patterns that were executed since the previous run of the stored procedures.
SP_GENERATE_PATTERN_WC Called internally by the SP_POPULATE_PATTERN_WC procedure. Responsible for retrieving the data for Weighted Counter patterns that were executed since the previous run of the stored procedures.
SP_POPULATE_PATTERN_MATCHALL Processes the Match All Pattern type data that was received since the previous run of stored procedures.
SP_POPULATE_PATTERN_COUNTER Processes the Counter Pattern type data that was received since the previous run of stored procedures.
SP_POPULATE_PATTERN_WC Processes the Weighted Counter Pattern type data that was received since the previous run of stored procedures.
Stored procedure Task
SP_UPDATE_UACI_TABLES_STATS

Called by the trigger to update the database statistics and the database jobs are submitted for reports data aggregation.

Updates the statistics for the following ETL tables:
  • UACI_ETLPATTERNSTATE
  • UACI_ETLPATTERNSTATEITEM
  • UACI_ETLPATTERNEVENTINFO
SP_POPULATE_PATTERN_LOCK

(p_parallel_degree)

Updates the UARI_PATTERN_LOCKtable with the degree of parallel execution configured.

p_parallel_degree is the degree at which the aggregation processes run in parallel.

For MARIA DB, the jobs are not supported and the stored procedures are run sequentially

p_parallel_degree is always 1 for MARIA DB

SP_AGGR_RUN_STATUS Called by the Interact ETL process before the start of the aggregation process to check the lock status of the running stored procedures. Run against the UARI_PATTERN_LOCKtable.
SP_REFRESH_PATTERNINFO

For Oracle and DB2 only

Refreshes the UARI_PATTERNSTATE_INFO table to get the state and audience level information for the ICs and Categories.

The call to this procedure is given by a trigger before the aggregation procedures start.

As Mviews are not supported for SQL Server, this procedure is not applicable for SQL Server.

SP_UARI_REBIND_PACKAGES

For DB2 only

Rebinds the packages that are associated with the aggregation trigger and procedures. Called from the trigger after the SP_UPDATE_UACI_TABLES_STATS procedure call.

Stored procedure Task
SQ_UARI_RUN Creates a unique run identifier. The list of the run IDs is stored in the UARI_RUNS table.

For SQL Server, RunID is generated by using the IDENTITY property on the RunId column, which generates new IDs on each run.

Database trigger
Stored procedure Task
TR_AGGREGATE_DELTA_PATTERNS After the UACI_ETLPATTERNSTATERUN table is updated with the value 3, the trigger is invoked by submitting the jobs that call the stored procedures for data aggregation.

For OneDB:

create database trigger TR_AGGREGATE_DELTA_PATTERNS along with store procedure AGGREGATE_DELTA_PATTERNS.

For OneDB only, create stored procedure

AGGREGATE_DELTA_PATTERNS

It updates UARI_PROCESSED_PATTERNS table, and create three jobs to invoke the following sub store procedures:

SP_POPULATE_PATTERN_MATCHALL,

SP_POPULATE_PATTERN_COUNTER,

EXECUTE PROCEDURE SP_POPULATE_PATTERN_WC.

Two ph_task are used for each invoke store procedure call, in order to block and tracking the job states. One ph_task before invoke call and one ph_task after completing the invoke call.

The ETL process

On the first run, ETL does not insert any values against the respective PatternID in the UARI_DELTA_PATTERNS table because all patterns are new or delta. The reports aggregation process collects all PatternID from the ETL tables and inserts them into 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 jobs based on the JobID.

JobID value Reason
Y The job is running. Scenarios are running or failed.
N Failed job.

The ETL process always checks the status of the reports aggregation by checking the status of the submitted jobs. If the ETL finds reports aggregation running, the ETL does not start its run. The ETL starts again according to 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 no JobIDs have the value Y. If any JobIDs have the value Y, then the ETL process is skipped and runs at the next scheduled interval. For more information about the ETL process, see the Unica Interact Administrator 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 PatternID is marked with U.
  • For deleted data, the PatternID is marked with D.
  • For newly added data, the PatternID is identified by the reports aggregation code and is marked with P.

The aggregation process is run for only the PatternIDs that are marked with the U or D flag.

Enabling stored procedures for the Interact Event Pattern report

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

Administrative Task Scheduler (ATS) depends on table space to store historical data and configuration information. To verify if the table space is defined in the database or to create the table space.

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.

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

  1. Browse the <Interact_Home>/reports/ddl/interact-ddl/<DB Type>/ folder.
  2. For DB2, set the following parameters:
    • db2set DB2_COMPATIBILITY_VECTOR=ORA
    • db2set DB2_ATS_ENABLE=YES
  3. When the instance is restarted, you must activate DB2 by running the following commands in the order listed:
    • db2 force application all Stop the application on this instance.
    • db2stop force Stop DB2.
    • db2start Start the database.
    • db2 activate db <dbname> Explicitly activate the database. You should see this message:DB20000I The ACTIVATE DATABASE command completed successfully.
    • db2 list active databases Verify that the database is activated. You must see a similar output.
      Active Databases
      Database name = <dbname> Applications connected currently = 0
      Database path   = /data04/<DB instance
              owner>/NODE0000/SQL00001/
  4. On the ETL database, run the following scripts in the order listed:
    • acir_tables_<DB Type>.sql
    • 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: Ensure that the SQL Server Agent service is running.
  5. 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.
    • For MariaDB:

      CALL SP_POPULATE_PATTERN_LOCK(1);

      The parallel degree execution is not configurable for MariaDB and its value is always 1.

    • For OneDB: CALL SP_POPULATE_PATTERN_LOCK(2);
  6. 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: You can contact IBM support.
    • For Oracle: alter trigger TR_AGGREGATE_DELTA_PATTERNS disable;
    • For SQL Server: Disable Trigger TR_AGGREGATE_DELTA_PATTERNS on uaci_etlpatternstaterun
    • For MariaDB: By default, the trigger is enabled. It must be dropped using the following command

      DROP TRIGGER IF EXISTS TR_AGGREGATE_DELTA_PATTERNS

    • For OneDB: By default, the trigger is enabled. It must be dropped using the following command.

      DROP TRIGGER if exists TR_AGGREGATE_DELTA_PATTERNS;;

  7. 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: You can contact IBM support.
    • For Oracle: alter trigger TR_AGGREGATE_DELTA_PATTERNS enable;
    • For SQL Server: Enable Trigger TR_AGGREGATE_DELTA_PATTERNS on uaci_etlpatternstaterun;
    • For MariaDB: By default, the trigger is enabled.

      If it is dropped, then it can be enabled by the trigger creation command.

      See the acir_scripts_mariadb.sql for the trigger creation command.

    • For OneDB: By default, the trigger is enabled.

      If it is dropped, then it can be enabled by the trigger creation command.

      See the acir_scripts_onedb.sql along with the create command

      PROCEDURE AGGREGATE_DELTA_PATTERNS.
Note: 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. When the system aggregates all data for the first time, the report aggregation process may take a longer time than subsequent aggregations.

Changing the degree of parallel execution for the Interact Event Pattern report

The degree of parallel execution 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 also increase proportionally

Configure the degree at which the aggregation process runs so that the Interact Event Pattern report can render faster.

To configure database jobs for a degree value of 3, complete one of the following steps, depending on your database:
  • For Oracle: Run the execute SP_POPULATE_PATTERN_LOCK(3) command against the Interact ETL database.
  • For IBM DB2: Run the call SP_POPULATE_PATTERN_LOCK(3) command against the Interact ETL database.
  • For SQL Server: Run the default acir_jobs_sqlserver.sql script to create database jobs for degree value 1 and 2. The patterns with degree values 1 and 2 are aggregated in the UARI_PROCESSED_PATTERNS table.
  • For MariaDB: Run the CALL SP_POPULATE_PATTERN_LOCK(1);

    The parallel degree execution is not configurable for MariaDB and its value is always 1.

  • For OneDB: Run the CALL SP_POPULATE_PATTERN_LOCK(2);
To modify the degree to 3 for the Match All Pattern, copy the sample code for degree 1 and complete the following steps:
  1. Set the value of @job_name to JOB_MA_3.
  2. Set the value of @p_parallel_degree to 3.
Run the following command against the Interact ETL database.
DECLARE
@jobId BINARY(16),
@status int,
@schedule_name varchar(16), @dbname varchar(100)
set @dbname= (SELECT DB_NAME());

EXEC msdb.dbo.sp_add_job @job_name=N’JOB_MA_3’, @job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’first’, @command=N’EXEC [dbo].[SP_POPULATE_PATTERN_MATCHALL]
@p_parallel_degree = 3’, @database_name=@dbname;

EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, @server_name=N’(local)’;
GO

You can create the degree for the Counter Pattern and the Weighted Counter Pattern and run the commands against the ETL database.

To modify the degree to 3 for the Counter Pattern, copy the sample code for degree 1 and complete the following steps:
  1. Set the value of @job_name to JOB_C_3.
  2. Set the value of @p_parallel_degree to 3.
To modify the degree to 3 for the Weighted Counter Pattern, copy the sample code for degree 1 and complete the following steps:
  1. Set the value of @job_name to JOB_WC_3.
  2. Set the value of @p_parallel_degree to 3

Log messages in the UARI_DELTA_REFRESH_LOG table for the Interact Event Pattern report

The UARI_DELTA_REFRESH_LOG table contains logging information for all procedures.

Aggregation process status

To verify the status of the aggregation process, look for the following text: MESSAGE_LINE:
<patterntype> patterns delta
        refresh started for parallel degree <degree value>
<patterntype> patterns delta refresh completed for parallel degree <degree
    value>
where:
  • <patterntype> is Match All, Counter, or Weighted Counter.
  • <degree value> is the value with which patterns are processed in parallel. For example, when the degree value is 2, the following messages are logged:
MatchAll patterns delta refresh started for parallel degree 
MatchAll patterns delta refresh completed for parallel degree
MatchAll patterns delta refresh started for parallel degree 
MatchAll patterns delta refresh completed for parallel degree 2

UARI_PATTERNSTATE_INFO table

To verify if the UARI_PATTERNSTATE_INFO table is refreshed, look for the following text:

MESSAGE_LINE:

Pattern State information refresh procedure started
--The procedure to refresh the data in UARI_PATTERNSTATE_INFO is running.

MESSAGE_LINE:

Pattern State information refresh procedure completed
--The procedure to refresh the data in UARI_PATTERNSTATE_INFO is completed.

Lock flags reset by the SP_AGGR_RUN_STATUS procedure

To verify if the lock flags are reset by the SP_AGGR_RUN_STATUS procedure, look for the following text:

MESSAGE_LINE:

patterns lock has been reset for parallel degree <degree value>

The OBJECT column of the UARI_DELTA_REFRESH_LOG table contains the procedure name for which the lock is reset.

where: <degree value> is the value with which patterns are processed in parallel. For example, when the degree value is 1, the following message is logged:

patterns lock has been reset for parallel degree 1

For DB2 only: rebinding of packages

For DB2 only: To verify that rebinding of the packages completed, look for the following text:

MESSAGE_LINE:

Rebind of packages started
--Rebinding of the packages started

MESSAGE_LINE:

Rebinding of packages completed successfully on <datetime>
--Rebinding of the packages completed successfully on the given date.

Statistics updated on ETL tables

To verify if the statistics were updated on the ETL tables, look for the following text:

MESSAGE_LINE:

Table statistics update
        started
--Update statistics on the ETL
        tables is in process

MESSAGE_LINE:

Statistics on Tables
        UACI_ETLPATTERNSTATE UACI_ETLPATTERNSTATEITEM
UACI_ETLPATTERNEVENTINFO and
        indexes have been updated successfully on <datetime>
--Statistics are updated on the
        mentioned ETL tables on the given date.

Degree of parallel execution

To verify the degree of parallel execution, look for the following text:

MESSAGE_LINE:
Pattern aggregation processing Parallel degree is set to <degree value>
--Parallel degree with which report aggregation will run is set to <degree value>.

For example, when the degree value is 2, the following message is logged:

Pattern aggregation processing Parallel degree is set to 2.