Multiple Stored Procedures in a campaign updating Oracle Table

[DarrylLawrence]

We have a campaign with multiple communication nodes, ie Email, SMS, iVoice.

Then we have a stored procedure node attached to each communication that updates contact dates (per product offered) in an Oracle table (Customer Contact Summary Table).

The same customer can be in more than one communication in the campaign. When the campaign is executed (or run in a schedule) some of the updates on the Oracle table fails as multiple processes cannot update the same record on the Customer Contact Summary Oracle Table.

I need to know how we can resolve this as we must be able to date stamp the contact fields after each campaign is executed, otherwise we would incorrectly contact a customer who was already contacted with a marketing offer.

See attached document for screenshots of the layout of the campaign and the Contact Summary Oracle Table.

This is the code that updates the Oracle Table.

&UpdateContactField. This is a macro variable created from the metadata selected by the user in the communication definition.

For example, it could resolve to this variable name : LAST_SMS_DT

/* update mart table */
proc sql;

update CI_MART.MA_Customer_Contact_Summary
set LAST_TELEMARKETING_DT = datetime(),
&UpdateContactField. = datetime()
where Customer_RK in (select customer_rk from CI_MART.MA_ACCOUNT
where account_rk in (select account_rk from &InTable1))
;
quit;

[shill]

What if you connected all your comm nodes to a single process node? That should prevent the process node from running until all the comm nodes are done, and your process node should have access to all the details you need to process each of those nodes' list of customers (or export files, whichever method you're using today) and update the table. You'll probably need to use some of the fields in MATableForMacro (or possibly the "neighbors" variable/table?) to figure out the channel.

That's just theory, though, to point you in the right direction - I'm fairly confident the answer lies in the combination of those pieces but I don't have a specific example of what it would look like to make the magic happen 🙂

A more "brute force" way of handling it would be to have each process node create a trigger file that indicates "I'm running" and have each one check for the existence of that file before you start, but there are other complications with that (like resetting it if an error occurs, possible race conditions with two starting at the same moment before either one writes the file, etc.).

[JamesAnderson]

Hi,
You could also do some testing using the locking options available to you via the SAS/Access to Oracle engine, namely: READ_LOCK_TYPE, READ_ISOLATION_LEVEL, UPDATE_LOCK_TYPE.
Additionally I would specifically set a small size for DBCOMMIT, so that the duration of row locks created during Update statements are reduced, making those rows available again for other update statements. This will slow down the update statement processing overall, but should help with your problem of row lock contention.
A couple of additional suggestions/comments that may help:

1. Your query uses DateTime - do you actually need Datetime for your campaign logic (i.e. do you calculate based on hours or minutes of the day ? or do you only use the Date portion ie your contact policy works in number of whole days)? If not then perhaps you could also include a where clause that filtered customers who had already been updated today: WHERE LAST_TELEMARKETING_DT < TODAY(), because the logic only cares about the date rather than the hour or minute.
2. What is the purpose of the WHERE Clause on Customer_RK ? Without knowing the details it seems redundant given that your campaign is at account level, the intable has account_rks. Removing this should speed up the query, and therefore also reduce lock contention
Cheers
James