SA-41725

Summary

The alternate database option in List View / Report Definition does not work when the schema name used by the alternate database is different to the main Pega Data schema.

Error Messages

Log file error examples:

ERROR xxxxxxxx|xx.x.x.xxxx Admin@SR - Not returning connection 3 for database "pega721rep" to the pool as it previously encountered the following error
User ID: Admin@SR
Last SQL: SELECT "PC0"."PYID" AS "pyID" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."PZINSKEY" AS "pzInsKey" FROM PEGA721.pc_SR_Sandbox_Work "PC0" WHERE "PC0"."PXOBJCLASS" IN (? , ? , ? , ? , ? , ? , ? , ? , ? )
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
.
.
.

ERROR xxxxxxxx|xx.x.x.xxxx Admin@SR - An error occurred on executing the query for the report definition - There was a problem getting a list: code: 942 SQLState: 42000 Message: ORA-00942: table or view does not exist

DatabaseException caused by prior exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

| SQL Code: 942 | SQL State: 42000




On the screen:

Steps to Reproduce

1. Configure a reports database as documented in the help, where the schema name is not the same as the main Pega Data schema. (For example a separate schema in the same physical database).
2. Create a List View or Report Definition and check the use alternate database option.
3. Run the report

Root Cause

The system assumes that the schema name of the alternate / reporting database will be the same as the main Pega Data schema, and the SQL queries generated for the report will prefix the table name with this schema name rather than using the one defined in the database rule for the alternate database.

For example, the query run for the report is as below. The alternate database schema is "PEGA721REP", but the query uses PEGA721 as the table prefix.

SELECT "PC0"."PYID" AS "pyID" , "PC0"."PYSTATUSWORK" AS "pyStatusWork" , "PC0"."PZINSKEY" AS "pzInsKey" FROM PEGA721.pc_SR_Sandbox_Work "PC0" WHERE "PC0"."PXOBJCLASS" IN (? , ? , ? , ? , ? , ? , ? , ? , ? )

Resolution

• List Views are deprecated in favour of Report Definitions. If using a List View, first update to a Report Definition.

• Apply HFix-35521 Alternate DB option in list view fails after Pega 7 upgrade (Pega 7.2.1)

Once installed the following prconfig setting can be used to specify the default schema name for the alternate / reporting database. A restart will be required for this setting to take effect.

database/databases/<DB Name>/defaultSchema
value: <Schema Name>

Examples:

prconfig.xml

<env name="database/databases/<Database Name>/defaultSchema" value="<Schema Name>"/>

Dynamic System Setting

Setting Purpose: prconfig/database/databases/<Database Name>/defaultSchema/default
Value: <Schema Name>