Re: Stored Procedure Speed/Visual Explain

Was just at RPG-DB2 Summit - we are encouraged to put as much into a single SELECT statement as we can, by the IBMer, Tom McKinley, who did a presentation on set-based thinking with SQL.

Tom specifically said to avoid multiple steps of putting data into separate tables in QTEMP. instead, look for ways to combine as JOINs or maybe as IN predicates against a subquery.

If the OPs stored procedure could do what the RPG program does - and it probably can - then everything could be put into the SP. SPs have lots of programming capability, and some of it is easier than doing RPG!

Vern

On 10/18/2013 12:02 PM, Michael Schutte wrote:

Sometimes it's better to split the SQL statements up and return multiple
result sets. then let the business logic take care of the rest. Other
times, I have selected what I've needed from each file and put them into
QTEMP. Then returned the result set of the QTEMP table.

declare global temporary table tmp_mytable like mytable with replace;
declare global temporary table tmp_mytable2 like mytable2 with replace;

insert into tmp_mytable select * from mytable where field = 1;


insert into tmp_mytable2 select * from mytable2 where field = 'abc';


select * from tmp_mytable a join tmp_mytable2 b on (a.otherfield =
b.otherfield);

etc.





On Thu, Oct 17, 2013 at 7:56 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

Could gather and review the PRTSQLINF against the SQL RPG program.
Could also try to get debug messages in a job that invokes the SQLRPG.

Regards, Chuck

On 10/17/13 12:17 PM, Michael Ryan wrote:

No, it's definitely SQL. Big honking SQL statement in there.

On Thu, Oct 17, 2013 at 2:40 PM,<rob@xxxxxxxxx> wrote:

Maybe it doesn't need/use any?

You have it call an RPG program. Perhaps your RPG program does
RLA instead of SQL <<SNIP>>

Michael Ryan on 10/17/2013 02:31 PM wrote:

Visual Explain/index Advisor woes. I have a stored procedure that
calls an RPG procedure that returns a result set. <<SNIP>>

How can I get more information regarding the indexes used or
needed?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.