SA-2437

Summary

Admin trying to update from 7.1.5 to 7.1.6.  PRPC database utilizes a split schema and is DB2-LUW.

For the first attempt, admin generated the DDL via the appropriate bat file command. He then took the resulting udb.sql file and sent it to DBAs to be reviewed and applied. After DBAs applied the script, admin then used the GUI installer to complete the process. He checked the checkbox to indicate that the DDL was applied manually. The installer then ran for many hours before failing. 

Please note that our DBAs will not give the application ID being used to run the upgrade the SYSADM priv. We have a separation of duties and any table structure changes being made or database objects being created / deleted need to be done via udb.sql.

Error Messages

     [java] com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=PEGA716;ALTER TABLE;PRPCUPGRADE.PR4_BASE, DRIVER=4.17.30
     [java]     at com.ibm.db2.jcc.am.hd.a(hd.java:747)
     [java]     at com.ibm.db2.jcc.am.hd.a(hd.java:66)
     [java]     at com.ibm.db2.jcc.am.hd.a(hd.java:135)
     [java]     at com.ibm.db2.jcc.am.wo.c(wo.java:2771)
     [java]     at com.ibm.db2.jcc.am.wo.d(wo.java:2759)
     [java]     at com.ibm.db2.jcc.am.wo.b(wo.java:2126)
     [java]     at com.ibm.db2.jcc.t4.ab.i(ab.java:226)
     [java]     at com.ibm.db2.jcc.t4.ab.c(ab.java:48)
     [java]     at com.ibm.db2.jcc.t4.o.b(o.java:38)
     [java]     at com.ibm.db2.jcc.t4.tb.h(tb.java:124)
     [java]     at com.ibm.db2.jcc.am.wo.jb(wo.java:2121)
     [java]     at com.ibm.db2.jcc.am.wo.a(wo.java:3294)
     [java]     at com.ibm.db2.jcc.am.wo.e(wo.java:1091)
     [java]     at com.ibm.db2.jcc.am.wo.execute(wo.java:1070)
     [java]     at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:298)

Steps to Reproduce

1.  Run the generateDDL.bat and have DBA apply SQL
2.  Run update.bat

Root Cause

The user running the update does not have the required permissions to do the final few steps of the update, which creates a trigger in the database and clears the database cache.

Resolution

This issue is resolved through the following local change: 

Altered the setupDatabase.xml file to remove the final steps before cleanup where the tool is attempting to enable the database triggers and truncate the cache table.

                                              Enable Database Triggers
                                             Truncate Updates Cache

As a post update step the DBA must manually run the scripts in the "schema\generated\udb\util\triggers\enable" directory to enable the database triggers and will truncate the pr_sys_updatescache table which is in the data schema.

TRUNCATE TABLE ${updatesCacheSchema}PR_SYS_UPDATESCACHE IMMEDIATE