Support Article
WSP-PA - Rule Schema is getting picked up instead of Data Schema
SA-24855
Summary
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.
Error Messages
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
Root Cause
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.
Resolution
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).
Published June 30, 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.