This content has been archived.

Issue: “Error: Commit failed” when loading CPM 6.1 RuleSets into a UTF-8 encoded database

As indicated in the Comment thread, this article pertains to CPM 6.1 on PRPC 5.5 SP1 because it was derived from the Related GCS Case Object Numbers cited below.
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.

Symptom

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

 

Example

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.

Example: CPM_61_Rules.zip

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.

Solution

Workaround

Installation Prerequisite

For a PegaRULES database that is encoded for UTF-8, complete the following steps before you install CPM:

  1. Use the ALTER TABLE statement to increase the width of the following database table columns from 64 to128 characters:
    • pr_index_reference.pxreferencingdescription
    • pr4_rule.pylabel
    • pr4_rule_vw.pyLabel

    Example:

    ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DATA TYPE VARCHAR (128)@

  2. For an Oracle database, change the definition of trigger TRPR4_RULE_VW_UPD
    • from SPtpyLabel VARCHAR (64)
    • to SPtpyLabel VARCHAR (128)
  3. For a SQL Server database, change the definition of trigger TRPR4_RULE_VW_UPD
    • from declare @tpyLabel VARCHAR (64)
    • to declare @tpyLabel VARCHAR (128)
  4. 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.

  5. 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.

Related Topics

PDF DocumentCustomer Process Manager 6.1

PDF DocumentMigrating Database Character Sets for Oracle, PegaRULES Process Commander v 5.1

PDF DocumentInstalling the Database on DB2 for z/OS

IBM References

wwwDB2 Version 9.1 for Linux, UNIX, and Windows, Unicode character encoding

wwwDB2 Version 9.5 for Linux, UNIX, and Windows, Unicode table and data considerations

Oracle References

wwwOracle® Database Globalization Support Guide, 11g Release 1 (11.1), Supporting Multilingual Databases with Unicode

Microsoft Developer Network Reference

wwwSQL Server 2008 Books Online (September 2009), Collation and Unicode Support

Suggest Edit

100% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.