Issue: “Error: Commit failed” when loading CPM 6.1 RuleSets into a UTF-8 encoded database
If you are using Pega 7.1.8 or a later release, you can skip the steps of the Solution that relate to triggers. In later releases of the Pega 7 Platform, database triggers are no longer used.
See SA-12905, https://pdn.pega.com/support-articles/database-triggers-removed-during-upgrade-pega-718.
If the PegaRULES database is encoded for the UTF-8 character set, you need to widen some of the PegaRULES database table columns to prevent Commit failures during the installation of Customer Process Manager (CPM) Version 6.1. While this symptom presents itself during CPM Version 6.1 installation, it has the potential to surface during the installation of other solution frameworks that use databases with UTF-8 encoding.
Here is an example of the error message that appears in the PegaRULES log if you do not perform the installation prerequisite steps provided as a solution:
Committed 5080 of 15256 (33.30%) 51:19 minutes remaining
Error: Commit failed - iterating instances: Database-Saver-InsertUpdatefail ()
Committed 5180 of 15256 (33.95%) 51:07 minutes remaining
The Commit failure is a symptom of the underlying database problems, which are identified in the application server log. The following examples illustrate how the database problems are logged by supported application servers:
On a Tomcat application server, the catalina.out log file could contain entries like the following for an Oracle database:
Problem #1, SQLState 72000, Error code 12899: java.sql.SQLException: ORA-12899: value too large for column "RULES"."PR_INDEX_REFERENCE"."PXREFERENCINGDESCRIPTION" (actual: 65, maximum: 64)
Problem #1, SQLState 72000, Error code 12899: java.sql.SQLException: ORA-12899: value too large for column "RULES"."PR4_RULE"."PYLABEL" (actual: 65, maximum: 64)
On a WebSphere Application Server, the SystemErr.log file could contain entries like the following for a DB2 UDB database:
Problem #1, SQLState 22001, Error code -302: com.ibm.db2.jcc.a.in: The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.. SQLCODE=-302, SQLSTATE=22001, DRIVER=3.52.95
On an Oracle WebLogic Server, the same problems are identified in the logs located in the application server path: xyz_domain\servers\Server_Name\logs\server_ name.log
If the PegaRULES database is encoded for UTF-8, you need to increase the width of certain database table columns from 64 characters to 128 characters. This solution applies to all supported database platforms.
On databases with UTF-8 encoding, you will encounter the Commit failure during the installation of Customer Process Manager (CPM) Version 6.1 if you do not increase the size of certain database table columns prior to loading the RuleSets. CPM uses some rules that require increased column widths.
At Step 4 of the CPM installation procedure, Load the CPM RuleSets, if you attempt to load the rule archive file CPM_61_Rules.zip and have not completed the installation pre-requisite, the PegaRULES log reports the database Commit failure and some CPM rules will not load successfully.
For a PegaRULES database that is encoded for UTF-8, complete the following steps before you install CPM:
- Use the ALTER TABLE statement to increase the width of the following database table columns from 64 to128 characters:
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DATA TYPE VARCHAR (128)@
- For an Oracle database, change the definition of trigger
SPtpyLabel VARCHAR (64)
SPtpyLabel VARCHAR (128)
- For a SQL Server database, change the definition of trigger
declare @tpyLabel VARCHAR (64)
declare @tpyLabel VARCHAR (128)
- For a DB2 UDB database, you do not need to change trigger definitions. However, if after you reload the rule archive (for example, CPM_61_Rules.zip), error messages similar to the following appear (SQLState 56098), drop and re-create all database triggers:
Caused by SQL Problems. Problem #1, SQLState 56098, Error code -727: com.ibm.db2.jcc.b.SqlException: An error occurred during implicit system action type "7". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message tokens "CCUSR|DELETE|CCUSR.PR4_RULE_VW".
There are eighteen (18) database triggers to drop and re-create. Here's a sample SQL script showing the DROP TRIGGER statements:
drop trigger trpr4_base_del@
drop trigger trpr4_base_ins@
drop trigger trpr4_base_upd@
drop trigger trpr4_fv_del@
drop trigger trpr4_fv_ins@
drop trigger trpr4_fv_upd@
drop trigger trpr4_rf_del@
drop trigger trpr4_rf_ins@
drop trigger trpr4_rf_upd@
drop trigger trpr4_rfw_del@
drop trigger trpr4_rfw_ins@
drop trigger trpr4_rfw_upd@
drop trigger trpr4_rp_del@
drop trigger trpr4_rp_ins@
drop trigger trpr4_rp_upd@
drop trigger trpr4_rule_del@
drop trigger trpr4_rule_ins@
drop trigger trpr4_rule_upd@
Use the CREATE TRIGGER statement to re-create the database triggers that you drop.
- For a DB2 for z/OS database, follow the instructions in Chapter 7 of the guide, Installing the Database on DB2 for z/OS. This process includes a series of jobs that modify the database, triggers, and stored procedures to support UTF-8. Before beginning this activity, contact Global Services to ensure you have the latest information.