Question

1
Replies
599
Views
Peter Poelz (PeterP80)
Morgan Stanley

Morgan Stanley
GB
PeterP80 Member since 2013 7 posts
Morgan Stanley
Posted: March 1, 2017
Last activity: March 9, 2017
Posted: 1 Mar 2017 7:10 EST
Last activity: 9 Mar 2017 1:57 EST
Closed

How to use temporary session tables to improve performance in PRPC?

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***

Enterprise Application Development Dev/Designer Studio Data Integration System Administration
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.