Running and scheduling stored procedures for eMessage
eMessage Reports in 9.1 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:
Stored procedures for eMessage
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.
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:
*
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
The following example shows how to get a job number every day at 21:00 hours without an end date. The jobs start on August 19, 2013:
declare
jobno number;

BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_runid;',
next_date => to_date('19-Aug-2013 21:00','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/
Example for processing 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('19-Aug-2013 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/
Example for processing 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('19-Aug-2013 21:10','DD-MON-YYYY HH24:MI' ),
interval => 'sysdate+1');
commit;
END;
/
Example for processing SMS contact data
*
The SMS feature is not a part of the default Reports offering, and you must buy a licence 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 August 19, 2013:
BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_SMS_contacts;',
next_date => to_date('19-Aug-2013 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 August 19, 2013:
BEGIN
DBMS_JOB.submit (job =>:jobno,
what => 'sp_populate_SMS_responses;',
next_date => to_date('19-Aug-2013 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 contact data
Define the following command:
DDECLARE @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 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:
DDECLARE @return_value int
EXEC @return_value = [dbo].[SP_POPULATE_SMS_CONTACTS]
SELECT 'Return Value' = @return_value
GO
Example for processing SMS contact data
Define the following command:
DDECLARE @return_value int
EXEC @return_value = [dbo].[SP_POPULATE_SMS_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 configuring 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 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('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 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('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.
Copyright IBM Corporation 2014. All Rights Reserved.