Support Article
SQL Exception for SystePulse, SystemIndexer, SystemWorkIndexer
SA-5641
Summary
User has recently upgraded their system from Pega 6.2 to 7.1.6 using split-schema configuration. Pega-RULES Agents SystemPulse, Systemindexer, and SystemWorkIndexer are throwing a SQL Exception that pr_sys_updatescache table does not exists in DATA Schema.
Error Messages
Agent Pega-RULES #1: Code-.SystemPulse encountered an execution error. Restarting after delay.
Exception at 20150115T163857.192 GMT:
com.pega.pegarules.pub.PRRuntimeException: [ Pulse ] The System Pulse encountered an error. Contact your PegaRULES and database administrators immediately.
at com.pegarules.generated.activity.ra_action_systempulse_a1462dda5b31f9ac9c168dc5d8498965.step1_circum0(ra_action_systempulse_a1462dda5b31f9ac9c168dc5d8498965.java:212)
at com.pegarules.generated.activity.ra_action_systempulse_a1462dda5b31f9ac9c168dc5d8498965.perform(ra_action_systempulse_a1462dda5b31f9ac9c168dc5d8498965.java:69)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3275)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:721)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:602)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:884)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1192)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:930)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:829)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:617)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.pega.pegarules.pub.database.DatabaseException: Problem performing pulse: code: 942 SQLState: 42000 Message: ORA-00942: table or view does not exist
From: (BEB6958C4366CEFBA450CB0D5A3052ACB)
SQL: select pzClearCacheType, pzObjClass, pzKeyString, pzParameters from PRPCDATA.pr_sys_updatescache where ((pzNodeName <> ?) or (pzNodeName is null)) and (pzCreateDateTime >= ?) and (pzCreateDateTime < ?) and (pzClearCacheType <> 'Index') order by pzCreateDateTime asc
SQL Inserts: <NODE_NAME> <2015-01-14 16:19:31.0> <2015-01-15 11:38:57.0>
Caused by SQL Problems.
Problem #1, SQLState 42000, Error code 942: java.sql.SQLException: ORA-00942: table or view does not exist
Steps to Reproduce
Try to start 7.1.6 split schema system.
Root Cause
The root cause of this problem is defect/misconfiguration in the PRPC operating environment. The application server datasource configuration was not setup by the Installation Guide. The PegaRULES datasource was connecting using the RULES schema name instead of the DATA schema name. There were also missing environment variables for the default schema names.
Resolution
This issue is resolved by making the following change to the PRPC operating environment:
1. Update the datasource to use the DATA schema name as outlined in the Oracle Tomcat install guide.
2. Remove the unwanted PegaDATA datasource if configured on the application server.
3. Add entries to conf/context.xml file to define the default rules and data schema names
<Environment name="prconfig/database/databases/PegaRULES/defaultSchema" value="<v71rules>" type="java.lang.String"/>
<Environment name="prconfig/database/databases/PegaDATA/defaultSchema" value="<v71data>" type="java.lang.String"/>
4. Update prconfig.xml file to the version that comes with the 7.1 media to make sure environment variables are set like the following:
<env name="database/databases/PegaRULES/dataSource" value="java:comp/env/jdbc/PegaRULES"/>
<env name="database/databases/PegaDATA/dataSource" value="java:comp/env/jdbc/PegaRULES"/>
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.