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.
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
- 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
- IBM® recommends using Oracle Automatic Memory Management (AMM). For more information, go to http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm
- Create stored procedures by using a database utility, such as SQL Plus.
- Schedule the sp_runid procedure to run at least 10 minutes before the other scripts.
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
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
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
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
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
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
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
- 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:
- Specify the step type as Transact-SQL script (T-SQL).
- Select the Campaign database
Example for creating a run identifier
DECLARE @return_value int
EXEC @return_value = [dbo].[SP_RUNID]
SELECT 'Return Value' = @return_value
GO
Example for processing email contact data
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
DECLARE @return_value int
EXEC @return_value = [dbo].[SP_POPULATE_MAILING_RESPONSES]
SELECT 'Return Value' = @return_value
GO
Example for processing SMS contact data
DECLARE @return_value int
EXEC @return_value = [dbo].[SP_POPULATE_SMS_CONTACTS]
SELECT 'Return Value' = @return_value
GO
Example for processing SMS response data
DECLARE @return_value int
EXEC @return_value = [dbo].[SP_POPULATE_SMS_RESPONSES]
SELECT 'Return Value' = @return_value
GO
Example for processing mobile response data
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®
- 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.
- 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.
- 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'
- 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
- 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
- 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
call SYSPROC.ADMIN_TASK_ADD('RunID_Job',null,null,
null,'50 20 * * *','USER1','SP_RUNID',null,null,null)
Example for processing mailing contact data
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
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
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
call SYSPROC.ADMIN_TASK_ADD(’SMS_Response_Job’,null,null,
null,’00 21 * * *’,’USER1’,’SP_POPULATE_SMS_RESPONSES’,null,
null,null)
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
call SYSPROC.ADMIN_TASK_ADD(’MOBILE_Response_Job’,null,null,
null,’00 21 * * *’,’USER1’,’SP_POPULATE_MOBILE_RESPONSES’,null,
null,null)