Support Article
DatabaseException running sppr_aggregate_usage stored procedure
SA-37468
Summary
In the logs there are a lot of DB2 exceptions related to sppr_aggregate_usage. The stored procedure is trying to access the pr_hourly_usage table in the rules schema when it is actually in the data schema.
Error Messages
2017-04-24 21:00:58,710 [.PegaWorkManager : 2] [ STANDARD] [ ] [ PegaRULES:07.10] (rnal.license.LicenseDaemonImpl) ERROR - problem while running the procedure in License Daemon:com.pega.pegarules.pub.database.DatabaseException: Database-General Problem encountered executing a stored procedure -204 42704 DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=RULES.PR_HOURLY_USAGE, DRIVER=4.18.60
DatabaseException caused by prior exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=RULES.PR_HOURLY_USAGE, DRIVER=4.18.60
| SQL Code: -204 | SQL State: 42704
From: (B598A43DFF827F30767488A1891443329:(License Daemon))
SQL: {call DATA.sppr_aggregate_usage ()}
SQL Inserts:
Steps to Reproduce
Not Applicable
Root Cause
A defect or configuration issue in the operating environment. There are a couple of references to the pr_hourly_usage and pr_daily_usage tables in this stored procedure which are not schema qualified. The currentSchema custom property defined in the jdbc/PegaRULES datasource is set to the rules-schema-name of RULES and as a result the stored procedure is looking for those tables in the RULES schema. Per the Pega Deployment Guides for DB2 currentSchema should be set to the data-schema-name schema.
Resolution
Make the following change to the operating environment:
In the custom properties in the jdbc/PegaRULES datasource set currentSchema to the data-schema-name
currentSchema=data-schema-name
currentFunctionPath=SYSIBM,SYSFUN,,data-schema-name
Published May 6, 2017 - 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.