Upgrading to PRPC 8.x on Oracle 12c hanging on RulesetCleanup
While upgrading any version of PRPC to 8.* on ORACLE 12c and you have chosen to do a ruleset clean-up configured in the setupDatabase.properties using below option.
# RUN RULESET CLEANUP?
# Generate and execute an SQL script to clean old rulesets and their rules from the system.
# To generate the script and not execute it use the cleanup.bat/sh script.
The process uses the option in setupDatabase.properties to create and execute the following file PRPC-RulesetCleanup.sql which causes the upgrade process to hang for hours. After evaluating the issue, it was discovered that two statements were the cause of the issue. The sql is looking for a pzinskey column on the pr_engineclasses which does not exist. Oracle does not through an error it just goes into a loop and eventually you will have to kill the thread.
PEGA need to correct the below two SQL that gets generated in the PRPC-RulesetCleanup.sql.
DELETE FROM Schema.pr4_rule_vw WHERE PZINSKEY IN (SELECT PZINSKEY FROM Schema.pr_engineclasses WHERE (PZCODESETVERSION is not null AND PZCODESETVERSION < '08') AND (PZCODESET = 'pega-enginecode'));
DELETE FROM Schema.pr_engineclasses WHERE (PZCODESETVERSION is not null AND PZCODESETVERSION < '08') AND (PZCODESET = 'pega-enginecode');
Hope this helps going forward until PEGA fixes the matter.
Are you sure that the ruleset cleanup script generated the above SQL? The query is comparing the pzInskey of rule summary records from pr4_rule_vw table with records in pr_engineclasses? The engine classes table will not have rules which are meant for classes. In fact, there is no pzInskey column at all in pr_engineclasses.
I request you to create an SR and provide the artefacts like generated scripts, upgrade logs so that we can take appropriate action(BUG) to get it fixed for the upcoming version.