We have a complex Connect-SQL query (on top of some DB2 views) that takes ~10 seconds to execute.
In order to speed this up we'd like to use a temporary session table. This would bring performance to ~2 seconds.
Here's roughly what we'd like to do:
DECLARE GLOBAL TEMPORARY TABLE session.T1 LIKE <our DB view>;
INSERT INTO session.T1 (SELECT * FROM <our DB view>);
SELECT column1, .... from session.T1 left outer join .....
When running the above 3 statements within the ConnectSQL in PEGA I'm getting following error code: There was a problem getting a list: code: -104 SQLState: 42601 Message: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601
I assume this is because I'm trying to run 3 DB statements (and not just one), and Pega doesn't recognize the output of the DECLARE statement as the list it expects?
Is there any way to do the above within a Connect-SQL or is there any other way of defining, populating and using (DB2) session tables in Pega?
***Updated by moderator: Lochan to update Categories***
If IBM DB2 has any default temp session table, just query that in Connect-SQL to understand if its an issue with select or returned resultset.
Though Java step in activity is not receomended, you can try using it via JDBC API calls to validate.
The other option is to use any of RDB-* methods.
In Pega, Declare Index Rule can be used on Pega to improve database performance and facilitate reporting. An index can improve search and reporting access for properties that cannot be exposed as database columns because they are embedded within an aggregate property. https://pdn.pega.com/how-create-declarative-indexes-embedded-properties