Hi,

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?

Thanks

Peter

***Updated by moderator: Lochan to update Categories***