MQSeries.net :: View topic - Stored procedure or /PASSTHRU

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Stored procedure or /PASSTHRU

Stored procedure or /PASSTHRU

« View previous topic :: View next topic »

Author

Message

ibmpro

Posted: Thu Jun 23, 2016 8:36 am Post subject: Stored procedure or /PASSTHRU

Novice

Joined: 20 Jun 2016
Posts: 11
Location: USA

any one can help to replace the bellow ESQL Statement to use PASSTHRU or Stored procedure instead . with explanation the advantage of both solutions

We are calling almost 1Mb size file from DB on daily base and sending these files to local folder bellow is the ESQL statement used.. the complain from technical team that the bellow statement oprations are consuming the resources .

CREATE COMPUTE MODULE MF_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
SET OutputRoot.Properties.MessageFormat='DFDL';
SET OutputRoot.Properties.MessageType='SCHMSG';

SET OutputRoot.DFDL.SCHMSG.record[] = SELECT * FROM Database.VREQUEST AS A;

Vitor

Posted: Fri Jun 24, 2016 7:38 am Post subject: Re: Stored procedure or /PASSTHRU

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

ibmpro wrote:

any one can help to replace the bellow ESQL Statement to use PASSTHRU or Stored procedure instead .

Code:

SET OutputRoot.DFDL.SCHMSG.record[] = PASSTHRU('SELECT * FROM VREQUEST;')

Code:

CALL stored.procedure.also.defined.in.esql(OutputRoot.DFDL.SCHMSG.record);

I doubt either will help a lot. An unbounded query (a SELECT with a '*' wildcard rather than a list of column names) is the most resource expensive operation you can do in a database. Simply pushing it down to the db server isn't going to make it cheaper. Especially on a table of any size (which this seems to be given the quoted 1Mb result set).

It's a bad SQL query, not a bad piece of IIB code.
_________________
Honesty is the best policy.
Insanity is the best defence.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Stored procedure or /PASSTHRU

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP