For Unica Deliver only: How to schedule and run stored procedures

Unica Deliver reports use the data that is contained in staging tables, which are populated by stored procedures. The stored procedures perform a delta refresh operation. Run the stored procedures at least once per day. If you run the procedures more frequently, the delta refresh method prevents multiple concurrent runs.

The following table provides information about the stored procedures and the tasks that they complete:
Table 1. Stored procedures for Deliver

This two-columned table lists the stored procedures in the first column and explains the tasks that the procedures complete in the second column.
Stored procedure Task
sp_runid Creates a unique run identifier. The list of the run IDs is stored in the UARE_RUNS table.
sp_update_ucc_tables_stats Updates statistics for the ucc_* tables. You can run this script before the sp_populate_* scripts.
sp_populate_mailing_contacts Processes the mailing contact data that is received since the previous run of stored procedures.
sp_populate_mailing_responses Processes the mailing response data that is received since the previous run of stored procedures.
sp_populate_sms_contacts If the SMS feature is enabled: Processes the SMS contact data that is received since the previous run of stored procedures.
sp_populate_sms_responses If the SMS feature is enabled: Processes the SMS response data that is received since the previous run of stored procedures.
sp_populate_WhtsApp_contacts If the WhatsApp feature is enabled: Processes the WhatsApp contact data that is received since the previous run of stored procedures.
sp_populate_WhtsApp_responses If the WhatsApp feature is enabled: Processes the WhatsApp response data that is received since the previous run of stored procedures.
sp_get_delta_mailing_contacts Called internally by sp_populate_mailing_contacts procedure. Responsible for retrieving the mailing contacts that were sent since the previous run of the stored procedures.
sp_generate_mailing_contacts Called internally by the sp_populate_mailing_contacts procedure. Responsible for retrieving the mailing and link level counts on contacted customers for the mailings that were run since the previous run of the stored procedures.
sp_get_delta_mailing_responses Called internally by sp_populate_mailing_responses procedure. Responsible for retrieving the responses that were received since the previous run of the stored procedures.
sp_generate_mailing_responses Called internally by sp_populate_mailing_responses procedure. Responsible for retrieving mailing and link level responses since the previous run of the stored procedures.
sp_get_delta_sms_contacts Called internally by sp_populate_sms_contacts procedure. Responsible for retrieving SMS since the previous run of the stored procedures.
sp_generate_sms_contacts Called internally by sp_populate_sms_contacts procedure. Responsible for retrieving the mailing and link level counts on contacted customers since the previous run of the stored procedures.
sp_get_delta_sms_responses Called internally by sp_populate_sms_responses procedure. Responsible for retrieving SMS responses since the previous run of the stored procedures.
sp_generate_sms_responses Called internally by sp_populate_sms_responses procedure. Responsible for retrieving the mailing and link level SMS responses since the previous run of the stored procedures.
sp_get_delta_WhtsApp_contacts Called internally by sp_populate_WhtsApp_contacts procedure. Responsible for retrieving WhatsApp messages since the previous run of the stored procedures.
sp_generate_WhtsApp_contacts Called internally by sp_populate_WhtsApp_contacts procedure. Responsible for retrieving the mailing and link level counts on contacted customers since the previous run of the stored procedures.
sp_get_delta_WhtsApp_responses Called internally by sp_populate_WhtsApp_responses procedure. Responsible for retrieving WhatsApp responses since the previous run of the stored procedures.
sp_generate_WhtsApp_responses Called internally by sp_populate_WhtsApp_responses procedure. Responsible for retrieving the mailing and link level WhatsApp responses since the previous run of the stored procedures.
sp_populate_mobile_responses Processes the mobile response data that was received since the previous run of stored procedures.
sp_get_delta_mobile_responses Called internally by sp_populate_mobile_responses procedure. Responsible for retrieving the responses that were received since the previous run of the stored procedures.
sp_generate_mobile_responses Called internally by sp_populate_mobile_responses procedure. Responsible for retrieving mobile responses since the previous run of the stored procedures.

Guidelines for running stored procedures

Use the following guidelines when you run the stored procedures:
  • You must create the stored procedures for your database by using the scripts that are provided with the installation files.
  • Consider the size of the tables and indexes in your installation. Larger tables require more time to update. Allow sufficient time to process the contact and response data. The initial runs are likely to require more time to complete than subsequent runs.
  • Because the stored procedures can run for an extended amount of time, consider running the procedures at times of reduced system activity, such as overnight.
  • You can reduce the amount of the time that is required to refresh the reports data by limiting the scope of the reports data processed.
  • You must schedule the following procedures to run at least 10 minutes after scheduling sp_runid:
    • sp_populate_mailing_contacts
    • sp_populate_mailing_responses
    • sp_populate_sms_contacts
    • sp_populate_sms_responses
    • sp_populate_WhtsApp_contacts
    • sp_populate_WhtsApp_responses
    • sp_populate_mobile_responses

When the scripts have run successfully, they display a final return code of 0.