WSP-PA - Rule Schema is getting picked up instead of Data Schema
Connect-SQL is executing a query to get next sequence number from databse sequence YOUR_SEQ that is defined in the data schema. At runtime system is picking up the Rule Schema instead of Data schema. This is a DB2 database where currentSchema is set to the rules schema in the datasource.
MSG][Error in RDB-Open][STACK][com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: -204 SQLState: 42704 Message: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=RULESSCHEMA.YOUR_SEQ, DRIVER=3.69.49 From: () SQL: select trim(NEXT VALUE FOR YOUR_SEQ) As ".YOUR_SEQID" from sysibm.sysdummy1 SQL Inserts: Caused by SQL Problems. Problem #1, SQLState 42704, Error code -204: com.ibm.websphere.ce.cm.StaleConnectionException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704,
Steps to Reproduce
Run Connect-SQL rule which executes this query: select trim(NEXT VALUE FOR YOUR_SEQ) As ".YOUR_SEQID" from sysibm.sysdummy1
A defect or configuration issue in the operating environment.
In the Connect-SQL rule the reference to the sequence is not schema qualified. As a result the database looks at what the currentSchema is and since it is set to the rules schema, it is looking for the sequence in the rules schema.
Make one of the following changes to this environment to correct where the database looks for the sequence:
- Schema qualify the reference to YOUR_SEQ in the connect-sql rule.
- Change currentSchema to the data schema in the PegaRULES datasource.
- Add synonym or alias so when one does not schema qualify the YOUR_SEQ sequence db2 looks for it in the correct schema (in this case the data schema).