Running and scheduling stored procedures for eMessage

eMessage reports uses 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 eMessage

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 This stored procedure creates a unique run identifier. The list of the run IDs is stored in the UARE_Runid table.
sp_update_ucc_tables_stats This stored procedure updates statistics for the ucc_tables. You can run this script before the sp_populate_* scripts.
sp_populate_mailing_contacts This stored procedure processes the mailing contact data that is received since the previous run of stored procedures.
sp_populate_mailing_responses This stored procedure 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, this stored procedure 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, this stored procedure processes the SMS response data that is received since the previous run of stored procedures.
sp_get_delta_mailing_contacts This stored procedure is internally called by sp_populate_mailing_contacts procedure, and is responsible to get the mailing contacts that are sent since the previous run of the stored procedures.
sp_generate_mailing_contacts This stored procedure is internally called by the sp_populate_mailing_contacts procedure, and is responsible to get mailing and link level counts on contacted customers for the mailings that are run since the previous run of the stored procedures.
sp_get_delta_mailing_responses This stored procedure is internally called by sp_populate_mailing_responses procedure, and is responsible to get the responses that are received since the previous run of the stored procedures.
sp_generate_mailing_responses This stored procedure is internally called by sp_populate_mailing_responses procedure, and is responsible to get mailing and link level responses since the previous run of the stored procedures.
sp_get_delta_sms_contacts This stored procedure is internally called by sp_populate_sms_contacts procedure, and is responsible to get SMS since the previous run of the stored procedures.
sp_generate_sms_contacts This stored procedure is internally called by sp_populate_sms_contacts procedure, and is responsible to get mailing and link level counts on contacted customers since the previous run of the stored procedures.
sp_get_delta_sms_responses This stored procedure is internally called by sp_populate_sms_responses procedure, and is responsible to get SMS responses since the previous run of the stored procedures.
sp_generate_sms_responses This stored procedure is internally called by sp_populate_sms_responses procedure, and is responsible to get mailing and link level SMS responses since the previous run of the stored procedures.
sp_populate_mobile_responses This stored procedure processes the mobile response data that is received since the previous run of stored procedures.
sp_get_delta_mobile_responses This stored procedure is internally called by sp_populate_mobile_responses procedure, and is responsible to get the responses that are received since the previous run of the stored procedures.
sp_generate_mobile_responses This stored procedure is internally called by sp_populate_mobile_responses procedure, and is responsible to get mobile responses since the previous run of the stored procedures.

Guidelines for running stored procedures for eMessage

Use the following guidelines for running stored procedures:
  • You must schedule sp_populate_mailing_contacts and sp_populate_mailing_responses to run at least 10 minutes after scheduling sp_runid.
  • 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 create the stored procedures for your database by using the scripts that are provided with the installation files.

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

Sample configuration of stored procedures for Oracle

Note the following guidelines when you configure stored procedures for the Oracle database:

The following example illustrates how to create a job and generate a run identifier. The example also illustrates the job ID when the job completes.

Example for creating a run identifier

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014:
declare 
jobno number; 

BEGIN 
DBMS_JOB.submit (job =>:jobno, 
what => 'sp_runid;', 
next_date => to_date('29-Nov-2014 21:00','DD-MON-YYYY HH24:MI' ), 
interval => 'sysdate+1'); 
commit; 
END; 
/

Example for processing email contact data

The following example shows how to schedule a batch job to process contact data. The job runs at 21:10 hours every day:
declare 
jobno number; 

BEGIN 
DBMS_JOB.submit (job =>:jobno, 
what => 'sp_populate_mailing_contacts;', 
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ), 
interval => 'sysdate+1'); 
commit; 
END; 
/

Example for processing email response data

The following example shows how to schedule a batch job to process response data. The job runs at 21:10 hours every day:
declare 
jobno number; 

BEGIN 
DBMS_JOB.submit (job =>:jobno, 
what => 'sp_populate_mailing_responses;', 
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ), 
interval => 'sysdate+1'); 
commit; 
END; 
/

Example for processing SMS contact data

Important: The SMS feature is not a part of the default Reports offering, and you must buy a license for the feature separately. However, the delta placement takes place regardless of whether you have bought the SMS feature.
The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014:
BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_SMS_contacts;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/

Example for processing SMS response data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014:
BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_SMS_responses;',
next_date => to_date('29-Nov-2014 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/

Example for processing mobile response data

The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on November 29, 2014:
BEGIN
DBMS_JOB.submit (job =>:jobno,
what => ’sp_populate_MOBILE_responses;’,
next_date => to_date(’29-Aug-2014 21:10’,’DD-MON-YYYY HH24:MI’ ),
interval => ’sysdate+1’);
commit;
END;
/

Sample configuration of stored procedures for Microsoft™ SQL Server

Note the following guidelines when you configure stored procedures for the Microsoft SQL Server database:
  • Use the SQL Server Agent to create new jobs for each stored procedure.
  • Schedule the jobs to run at least daily. You must schedule sp_runid to run at least 10 minutes before the other scripts.
  • Complete the following steps for each job in the SQL Server Agent interface:
    1. Specify the step type as Transact-SQL script (T-SQL).
    2. Select the Campaign database

Example for creating a run identifier

Define the following command:
DECLARE @return_value int 
EXEC @return_value = [dbo].[SP_RUNID] 
SELECT 'Return Value' = @return_value 
GO

Example for processing email contact data

Define the following command:
DECLARE @return_value int 
EXEC @return_value = [dbo].[SP_POPULATE_MAILING_CONTACTS] 
SELECT 'Return Value' = @return_value 
GO
Schedule the job to run at least 10 minutes after the job that generates the run identifier.

Example for processing email response data

Define the following command:
DECLARE @return_value int 
EXEC @return_value = [dbo].[SP_POPULATE_MAILING_RESPONSES] 
SELECT 'Return Value' = @return_value 
GO
Schedule the job to run at least 10 minutes after the job that generates the run identifier.

Example for processing SMS contact data

Define the following command:
DECLARE @return_value int 
EXEC @return_value = [dbo].[SP_POPULATE_SMS_CONTACTS] 
SELECT 'Return Value' = @return_value 
GO

Example for processing SMS response data

Define the following command:
DECLARE @return_value int 
EXEC @return_value = [dbo].[SP_POPULATE_SMS_RESPONSES] 
SELECT 'Return Value' = @return_value 
GO

Example for processing mobile response data

Define the following command:
DECLARE @return_value int
EXEC @return_value = [dbo].[SP_POPULATE_MOBILE_RESPONSES]
SELECT ’Return Value’ = @return_value
GO

Sample configuration of stored procedures for IBM DB2®

Note the following guidelines when you configure stored procedures for the IBM DB2 database:
  • The database must be DB2 version 9.7.8 or higher.
  • Create new jobs in DB2 Administrative Task Scheduler (ATS)
  • Schedule the jobs to run at least daily. You must schedule sp_runid to run at least 10 minutes before the other scripts.
Complete the following steps before you configure stored procedures for IBM DB2:
  1. Enable the registry.

    Set the DB2_ATS_ENABLE registry variable to YES, TRUE, 1, or ON. Restart the DB2 database after you set the variable.

  2. Create the SYSTOOLSPACE table space.

    Users that belong to the SYSADM or SYSCTRL group can create this space. Use the following query to verify that the space exists:

    SELECT TBSPACE FROM SYSCAT.TABLESPACES WHERE TBSPACE = 'SYSTOOLSPACE'

  3. Grant permissions. In the following examples, substitute the values that are appropriate for your environment:
    • EMESSAGE: Database that contains the eMessage system tables.
    • USER1: Owner of the EMESSAGE database
    • DB2ADMIN: DB2 administrative user
    • Administrator: Super user
  4. Connect to DB2 as an administrative user and run the following grant commands:
    • db2 GRANT DBADM ON DATABASE TO USER DB2ADMIN
    • db2 GRANT DBADM ON DATABASE TO USER USER1
    • db2 grant all on table SYSTOOLS.ADMINTASKS to USER1
    • db2 grant all on table SYSTOOLS.ADMINTASKS to DB2ADMIN
  5. If the SYSPROC.ADMIN_TASK_ADD table exists, run the following grant commands:
    • db2 grant execute on procedure SYSPROC.ADMIN_TASK_ADD to USER1
    • db2 grant execute on procedure SYSPROC.ADMIN_TASK_ADD to DB2ADMIN

Example for creating a run identifier

This example shows how to get a job number every day at 20:50 hours without an end date.
call SYSPROC.ADMIN_TASK_ADD('RunID_Job',null,null,
null,'50 20 * * *','USER1','SP_RUNID',null,null,null)

Example for processing mailing contact data

This example shows how to schedule a batch job to process contact data. In this example, the job runs at 21:00 hours every day.
call SYSPROC.ADMIN_TASK_ADD('Email_Contact_Job',null,null,null,'00 21 * * *',
'USER1','SP_POPULATE_MAILING_CONTACTS',null,null,null)
Schedule the job to run at least 10 minutes after the job that generates the run identifier.

Example for processing mailing response data

This example shows how to schedule a batch job to process response data. In this example, the job runs at 21:00 hours every day.
call SYSPROC.ADMIN_TASK_ADD('Email_Response_Job',null,null,
null,'00 21 * * *','USER1','SP_POPULATE_MAILING_RESPONSES',null,
null,null)
Schedule the job to run at least 10 minutes after the job that generates the run identifier.

Example for processing SMS contact data

This example shows how to schedule a batch job to process contact data. In this example, the job runs at 21:00 hours every day.
call SYSPROC.ADMIN_TASK_ADD(’SMS_Contact_Job’,null,null,null,’00 21 * * *’,
’USER1’,’SP_POPULATE_SMS_CONTACTS’,null,null,null)
Schedule the job to run at least 10 minutes after the job that generates the run identifier.

Example for processing SMS response data

This example shows how to schedule a batch job to process response data. In this example, the job runs at 21:00 hours every day.
call SYSPROC.ADMIN_TASK_ADD(’SMS_Response_Job’,null,null,
null,’00 21 * * *’,’USER1’,’SP_POPULATE_SMS_RESPONSES’,null,
null,null)
This example shows how to schedule a batch job to process contact data. In this example, the job runs at 21:00 hours every day.
call SYSPROC.ADMIN_TASK_ADD(’SMS_Response_Job’,null,null,null,’00 21 * * *’,
’USER1’,’SP_POPULATE_MAILING_RESPONSES’,null,null,null)
Schedule the job to run at least 10 minutes after the job that generates the run identifier.

Example for processing mobile response data

This example shows how to schedule a batch job to process response data. In this example, the job runs at 21:00 hours every day.
call SYSPROC.ADMIN_TASK_ADD(’MOBILE_Response_Job’,null,null,
null,’00 21 * * *’,’USER1’,’SP_POPULATE_MOBILE_RESPONSES’,null,
null,null)