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 4, 2017 - Updated May 5, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.