Sample configuration of stored procedures for OneDB

Use the following guidelines when you configure stored procedures for the OneDB database.

Guidelines for configuring stored procedures

Use the OneDB tasks to create new jobs for each stored procedure. These tasks are created using sysadmin database.

  • Run following command before initializing the Deliver database.

    Set environment the DB_LOCALE and GL_USEGLS = 1

  • Schedule the jobs to run atleast daily. You must schedule sp_runid to run atleast 10 minutes before the other scripts.
  • Create tasks in Sysadmin database.

Example for creating a run identifier

The following example shows how to create a task to generate a run identifier.

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'SP_RUNS',
'This task is to invoke procedure to generate runids for the deliver delta refresh runs.',
'TASK',
'EXECUTE PROCEDURE < Deliver_Database name>@<DB Server Instance>:SP_RUNID()',
'20:30:00','1 0:00:00', 0
);

Example for processing email contact data

The following example shows how to process email contact data. Schedule the job to run at least 10 minutes after the job that generates the run identifier.

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'MAILING_CONTACTS',
'This task is to invoke procedure to populate mailing contacts.',
'TASK',
'EXECUTE PROCEDURE < Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_MAILING_CONTACTS()',
'20:40:00','1 0:00:00', 0
);

Example for email response data

The following example shows how to process email response data. Schedule the job to run atleast 10 minutes after the job that generates the run identifier. 

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'MAILING_RESPONSES',
'This task is to invoke procedure to populate mailing responses.',
'TASK',
'EXECUTE PROCEDURE < Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_MAILING_RESPONSES()',
'20:40:00','1 0:00:00', 0
);

Example for processing SMS contact data

The following example shows how to process SMS contact data. Schedule the job to run at least 10 minutes after the job that generates the run identifier.

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'SMS_CONTACTS',
'This task is to invoke procedure to populate SMS contacts.',
'TASK',
'EXECUTE PROCEDURE < Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_SMS_CONTACTS()',
'20:40:00','1 0:00:00', 0
);

Example for processing SMS response data

The following example shows how to process SMS response data. Schedule the job to run at least 10 minutes after the job that generates the run identifier. 

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'SMS_RESPONSES',
'This task is to invoke procedure to populate SMS responses.',
'TASK',
'EXECUTE PROCEDURE <Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_SMS_RESPONSES()',
'20:40:00','1 0:00:00', 0
);

To check scheduled tasks creation in sysadmin database, run the following query.

SELECT * from ph_task;

To check run status, check in sysadmin database and run the following query.

select * from ph_run;

You can also check in each channel process data in Deliver database using following query.

Select * from uare_delta_refresh_log order by runid desc;

Example for processing WhatsApp contact data

The following example shows how to process WhatsApp contact data. Schedule the job to run at least 10 minutes after the job that generates the run identifier.

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'WHTSAPP_CONTACTS',
'This task is to invoke procedure to populate WhatsApp contacts.',
'TASK',
'EXECUTE PROCEDURE < Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_WHTSAPP_CONTACTS()',
'20:40:00','1 0:00:00', 0
);

Example for processing WhatsApp response data

The following example shows how to process WhatsApp response data. Schedule the job to run at least 10 minutes after the job that generates the run identifier. 

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'WHTSAPP_RESPONSES',
'This task is to invoke procedure to populate WhatsApp responses.',
'TASK',
'EXECUTE PROCEDURE <Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_WHTSAPP_RESPONSES()',
'20:40:00','1 0:00:00', 0
);

To check scheduled tasks creation in sysadmin database, run the following query.

SELECT * from ph_task;

To check run status, check in sysadmin database and run the following query.

select * from ph_run;

You can also check in each channel process data in Deliver database using following query.

Select * from uare_delta_refresh_log order by runid desc;

Example for processing Mobile response data

The following example shows how to process Mobile response data. Schedule the job to run at least 10 minutes after the job that generates the run identifier. 

INSERT INTO ph_task
(
tk_name,tk_description,tk_type,tk_execute,tk_start_time,tk_frequency, tk_attributes
)
VALUES
(
'MOBILE_RESPONSES',
'This task is to invoke procedure to populate Mobile responses.',
'TASK',
'EXECUTE PROCEDURE <Deliver_Database_name>@<DBServer_Instance>:SP_POPULATE_MOBILE_RESPONSES()',
'20:40:00','1 0:00:00', 0
);

To check scheduled tasks creation in sysadmin database, run the following query.

SELECT * from ph_task;

To check run status, check in sysadmin database and run the following query.

select * from ph_run;

You can also check in each channel process data in Deliver database using following query.

Select * from uare_delta_refresh_log order by runid desc;