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:
- The Interact ETL process transforms the audience blob data into ETL database tables.
- 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 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_COUNTER procedure.
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:
|
SP_POPULATE_PATTERN_LOCK
|
Updates the UARI_PATTERN_LOCK table with the
degree of parallel execution
configured.
For MARIA DB, the jobs are not supported and the stored procedures are run sequentially
|
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_LOCK table. |
SP_REFRESH_PATTERNINFO |
For Oracle and DB2 only Refreshes the 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
|
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.
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: This is applicable from version 12.1.0.3 onwards. create database trigger TR_AGGREGATE_DELTA_PATTERNS along with store procedure AGGREGATE_DELTA_PATTERNS. |
For OneDB only, create stored
procedure AGGREGATE_DELTA_PATTERNS This is applicable from version 12.1.0.3 onwards. |
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.
- Browse the
<Interact_Home>/reports/ddl/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:
- 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/
- 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 theacir_tables_<DB Type>.sql
script if it was not run earlier.Note: If an exception is thrown after you run theacir_scripts_db2.sql
script on the target database, delete the trigger and create it with the appropriate database user.For SQL server, run theacir_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. - 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);
- 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;
;
- 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 commandPROCEDURE AGGREGATE_DELTA_PATTERNS
.
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);
- Set the value of @job_name to JOB_MA_3.
- Set the value of @p_parallel_degree to 3.
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:- Set the value of @job_name to JOB_C_3.
- Set the value of @p_parallel_degree to 3.
- Set the value of @job_name to JOB_WC_3.
- 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
<patterntype> patterns delta
refresh started for parallel degree <degree value>
<patterntype> patterns delta refresh completed for parallel degree <degree
value>
<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:
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
.