Data intigrity during multiple RDB-Save
Hi,
I have a situation where I have to insert data into two tables using RDB-Save. The issue is if second insert fails then the first insert should also be reverted. Basically I don't want to insert data in the first table if the second one fails as it happens during two phase commit. There is a Primary- Foreign Key relation in the tables. Is it possible to achieve this using RDB since RDB commits automatically?
Thanks in advance to all the great minds!
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Thanks Ashok_Bommisetty. However how to write multiple insert sql in a single Save tab of a Connect SQL? how should we separate two sqls?
As a best practice, include only one SQL statement on this tab. If your situation requires multiple statements, place one statement each in separate Connect SQL rules.
We have the same requirement. Unfortunately, the external database we are connecting to is a black box and we do not have the ability to create new stored procedures. We only have permissions to execute the original procedures the old system (that we are replacing with Pega) had access to.
I tried calling two stored procedures within one Connect-SQL rule, but I haven't been able to make this work yet. I know it's possible on some database versions, but seemingly not on this one (older DB2).
We REALLY need for Pega to add Transaction management in an Activity around multiple RDB-Save actions.
Try to look at JTA provided by your EJB container. You can write a java step with JTA calls.
This information may be helpful:
https://pdn.pega.com/community/product-support/question/two-phase-commi…;
https://pdn.pega.com/community/pega-academy/question/query-lsa-71-quiz-…
Two or more approaches can be used -
a)Before inserting, check to see if you have a record in the tables with the same key ?
b) convert your operation into a store proc
https://pdn.pega.com/forums/prpc/pegarules-database/rollback-during-rdb…
"Enter an SQL statement to be executed by an activity that includes the RDB-Save method.
As a best practice, include only one SQL statement on this tab. If your situation requires multiple statements, place one statement each in separate Connect SQL rules. Your activity can cause the statements to be executed in the desired sequence. If your situation requires many statements executed in sequence, consider using a stored procedure. (Pega 7 Platform uses prepared statements and submits the SQL code in a single call to database software. Some database software versions may allow multiple statements in one call, but other software versions do not; use of multiple statements on this tab reduces portability and flexibility.)"
https://pdn.pega.com/sites/pdn.pega.com/files/help_v719/procomhelpmain…
c) If second insert failes, remove the insert performed by your first query.
I prefer store proc personally.