Summary

You can configure multiple instances of Process Commander to use a single instance of the PegaRULES database by creating a separate schema in the database for each of the Process Commander installations.

However, when a database contains multiple schemas, supporting multiple installations of Process Commander, changes that add columns to one schema can cause later SQL queries to the database to fail. As a result of this limitation, when you try to save Data-Admin-Operator-ID instances and flow rules, SQL Insert/Update statements fail, triggering the Database-Saver-InsertUpdatefail error. In addition, you cannot export and import rules.

The underlying issue has been resolved in Process Commander v6.1 and later versions. To apply this software change to a version of Process Commander between v5.4 and v5.5 SP1, the prescribed hotfixes must be installed in the exact sequence indicated in Best Practice and to all prdbutil and prweb instances. (For more information, see also PRKB-23183: Troubleshooting: "Obj-Save is trying to write to a non-existent column.")

Example

Here is an excerpt of an example of the DB2 z/OS database error and SQL problems that are logged:

com.pega.pegarules.pub.database.DatabaseException:
Database-Saver-InsertUpdatefail
From: (H242BA69E15DB7141D481631C206E7A66:20.20.189.99)
SQL: update pr_index_operators set PXCREATEDATETIME = ? ,
PXCREATEOPERATOR = ? , PXCREATEOPNAME = ? , PXCREATESYSTEMID = ? ,
PXINDEXCOUNT = ? , PXINDEXPURPOSE = ? , PXINSINDEXEDCLASS = ? ,
PXINSINDEXEDKEY = ? , PXINSNAME = ? , PXOBJCLASS = ? , PXPRIVILEGECLASS =
? , PXPRIVILEGENAME = ? , PXUPDATEDATETIME = ? , PXUPDATEOPERATOR = ? ,
PXUPDATEOPNAME = ? , PXUPDATESYSTEMID = ? , PYACCESSGROUPADDITIONAL = ? ,
PYLABEL = ?
, PYWORKBASKET = ? where pzInsKey = ?
SQL Inserts: <<null>> <<null>> <<null>> <<null>>
<1> <AdditionalAccessGroup> <Data-Admin-Operator-ID>
<DATA-ADMIN-OPERATOR-ID <name@your_company.COM> <DATA-ADMIN-OPERATOR-ID
<name@your_company.COM>!1!ADDITIONALACCESSGROUP> <Index-Operator> <<null>>
<<null>> <<null>> <<null>> <<null>> <<null>>
<Your_CompanyDevs:Developer_1> <<null>> <<null>> <INDEX-OPERATOR
DATA-ADMIN-OPERATOR-ID <name@your_company.COM>!1!ADDITIONALACCESSGROUP>

Caused by SQL Problems.
Problem #1, SQLState 42703, Error code -206:
com.ibm.websphere.ce.cm.StaleConnectionException: PYWORKBASKET IS NOT
VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206, SQLSTATE=42703,
DRIVER=3.50.152
Problem #2, SQLState 42703, Error code -206: com.ibm.db2.jcc.b.nm:
PYWORKBASKET IS NOT VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206,
SQLSTATE=42703, DRIVER=3.50.152
Problem #3, SQLState 26501, Error code -516:
com.ibm.db2.jcc.b.SqlException: THE DESCRIBE STATEMENT DOES NOT SPECIFY A
PREPARED STATEMENT. SQLCODE=-516, SQLSTATE=26501, DRIVER=3.50.152
Problem #4, SQLState 07003, Error code -518:
com.ibm.db2.jcc.b.SqlException: THE EXECUTE STATEMENT DOES NOT IDENTIFY A
VALID PREPARED STATEMENT. SQLCODE=-518, SQLSTATE=07003, DRIVER=3.50.152
at
com.pega.pegarules.engine.database.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:174)

Suggested Approach

Best Practice

Hotfixes for Process Commander 5.5 SP1 resolve this issue. However, the dependency checker of Update Manager cannot identify the functional dependencies among the hotfixes. Therefore, when you select the following hotfixes to download from the Hotfix Self-Service site, be certain to install them in this order:

1. HFix-2105

2. HFix-2407

3. HFix-2553

4. HFix-2562

Because the prprivate.jar and prpublic.jar files are different for the prdbutil and prweb servlets, you must install the four hotfixes in the prescribed order for each Web module.

Example Scenarios and Alternative Solutions

As an alternative to installing the prescribed set of hotfixes, you might want to manage your database schemas as described for the scenarios identified here. The scenarios and solutions also explain the details of the multiple schemas issue.

The solution for Scenario 1 corrects the immediate problem experienced when you add a solution framework to your base installation. However, your system is still vulnerable to the underlying problem. To avoid this problem with later schema changes, you should also apply the modifications described in the solutions for Scenario 2.

Scenario 1: Multiple database schemas configured on one version of Process Commander with a solution framework installed

You have deployed one version of Process Commander configured with multiple database schemas and have installed a solution framework. For example, you are using V5.5 SP1 and have configured three database schemas for one instance of the PegaRULES database: a Development schema, a Test schema, and a User Acceptance Test (UAT) schema.

The Test team installs Customer Process Manager Version 6.1, which causes new table columns to be added to the Test schema. When you create an Operator ID and try to save it, you get the Database-Saver-InsertUpdatefail error with SQLState problems because the Development and UAT schemas are not being accessed by the JDBC, which is sending all SQL queries to the schema with the additional table columns, the Test schema.

Solution for Scenario 1

If you have deployed one version of Process Commander configured with multiple database schemas and have installed a solution framework that adds table columns to one of the schemas, you need to synchronize all schemas to use the same level of tables and columns. Be sure to install the database scripts for the solution framework on top of Process Commander. For example, install Customer Process Manager 6.1 database scripts on top of Process Commander Version 5.5 SP1.

Scenario 2: Multiple database schemas configured on different versions of Process Commander with or without a solution framework installed

You have deployed more than one version of Process Commander and each version uses a different database schema for one instance of the PegaRULES database. For example, you are using Versions 5.5 SP1, 5.4 SP3, 5.3 SP3, and 5.2 SP2 for different environments of your enterprise: proof of concept, development, test, user acceptance test, production.

For this deployment scenario, the solution you apply depends on whether you are installing or upgrading Process Commander or simply adding another database schema by cloning an existing schema.

Solutions for Scenario 2

Solution 2A: When installing or upgrading, edit the Process Commander application JAR file and the configuration file to specify the database schemas.

If you install another instance of Process Commander or upgrade an existing installation, perform the following steps for each database schema:

1. Edit the PRPC JAR file:
   1. Using Winzip, open the PRPC JAR file, for example, PRPCv5_v55_relcand_0053jar.
   2. In the application.properties, edit the property _parfile_schema_name=  to specify the schema name, for example:    _parfile_schema_name=D55Q1DBO
   3. Save the PRPC JAR file and close Winzip.
2. Run the Database Utilities application (prdbutil.war), upload the PRPC JAR file, run it to load the rules and the specified schema name in every definition of the PegaRULES database table.
3. After the Database Utilities application (prdbutil.war)has finished, verify the schema:
   1. Log in to Process Commander.
   2. Under the Class Explorer, click Data-Admin-DB-Table and select any table.
4. Update the Process Commander configuration (prconfig.xml) in the core application (prweb.war) and in the Database Utilities application (prdbutil.war):
   1. Find the prweb.war file in the prresources.jar.
   2. Find the prdbutil.war in the folder WEB-INF/classes.
   3. In the prconfig.xmlof each application, specify the following properties:

      <env name=database/baseTable/rununderschemaid value=true />
      <env name=database/baseTable/schema value=D55Q1DBO />

      This second property setting continues the example used in Step 1b.

5. Repeat these steps for each database schema used with each version of Process Commander that you install or upgrade.

Solution 2B: When cloning a schema, specify the new schema name in Framework Data-Admin-DB Table Instances.

You might need to add a schema for a solution framework by cloning the schema of the base Process Commander deployment. To ensure that framework-specific database tables reflect the new schema name when the framework is installed on a database with multiple pre-existing PegaRULES schemas, complete these steps:

1. From the Class Explorer, select   Data- > Admin- > DB- > Table.  Sort the instances by Class Name.
2. Update all database tables, particularly the base tables pr4_rule, pr4base, pr_data, and pc_work to specify the new schema name, for example, D55Q1DBO.
3. Update the Process Commander configuration (prconfig.xml) in the core application (prweb.war) and in the Database Utilities application (prdbutil.war):
   1. Find the prweb.war file in the prresources.jar.
   2. Find the prdbutil.war in the folder WEB-INF/classes.
   3. In the prconfig.xmlof each application, specify the following properties:

      <env name=database/baseTable/rununderschemaid value=true />
      <env name=database/baseTable/schema value=D55Q1DBO />

      This second property setting continues the example used in Step 2.

Related Topics

Connecting to multiple databases with Data-Admin-DB-Name

Troubleshooting: "Obj-Save is trying to write to a non-existent column"

Database Table form - Completing the Database tab