Merge query is triggered during Obj-Save
SummaryWhat happens when the Obj-Save method invokes a SQL MERGE query?
Steps to Reproduce
1. Log in to a Pega 7.1.9 system.2. Create an activity that calls the Obj-Save method.
3. Run the activity and observe query formed.
ResolutionHere’s the explanation for the reported behavior:
Starting with Pega 7.1.7, Pega uses the SQL MERGE verb and JDBC batch.
The MERGE verb allows Pega to update a database record whether or not it exists using a single database call.
JDBC batch allows for potentially improved performance in cases where many instances of the same class are stored in the same transaction.When saving an instance to the database using one of the standard Obj- methods, Pega uses MERGE for a particular database if all of the following conditions are true:
- The Pega Platform is not running on Tomcat 6 or earlier release.
- The database to which the instance is being saved is a supported version of one of our five standard database platforms (MSSQL, DB2 z/OS, DB2 LUW, Postgres, or Oracle).
- The database driver being used to connect to the database supports JDBC version 4 or later release.
- The database driver being used to connect is not one of our "known buggy" Oracle drivers.
- The setting classmap/usemergestatement is set to true (or not set at all).
- Pega is on a single-tenant system.
- The table to which the instance is being saved has columns that are not part of the table's primary key.
Published July 22, 2016 - Updated July 9, 2019