Re: SQL Finding & Using -- iNav Run SQL -- MIDRANGE-L

Jim

Once you are in the Visual Explain window, go to Help - there is tremendous assistance there - as in most well-designed Windows apps. That has all the "how to" stuff. And every icon in the resulting graph has a right-click context menu with Help as one of its items. BTW, I was looking in iSeries Access V5R3 - no relation to OS versions. Every release adds new capabilities, so this Help text is the best first place to look.

The main "Run SQL Scripts" window has a couple buttons for doing a VE against a statement you just ran or running a statement you've entered and doing a VE, all in one swell foop.

The first page of the VE window help points you to the Performance and Optimization link in InfoCenter - unfortunately, it is broken - IBM keeps changing the links - but the v5r3 link is
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/rzajq/rzajqkickoff.htm

For other info, try www.iseries.ibm.com/access and look around for stuff about the various things in iNav. Then try www.iseries.ibm.com/db2 and dig around - lots of stuff, white papers, education both online and classroom offerings, sample code, etc.

There also used to be a Redbook, but it is probably out of date as releases change. The basics will be there, however. It might be linked to at the DB2 site I gave, although those things do change.

Remember that the results you see in VE might not accurately predict what happens in production. SQL's optimizer bases its decisions on many factors, even memory pool size and file size, as well now as various statistics about distribution of values in indexes, if captured. So a statement run against a small copy of a file in a large pool may very likely use a table scan while the same statement run against the production file with 10 million records in constrained memory might create a temporary index.

If you can, get to the optimization classes at COMMON or Tech Conf or the soon to come DB2/RPG Summit here in Minneapolis. One of the people who WROTE the new query engine teaches the classes at these things. I met him when I was part of the testing team in 2001 down in Rochester.

There are other database tools in iNav that are extremely helpful - this may be one of the best parts of iNav, actually. Things like reverse engineering from DDS to DDL, even to being able to choose the level, whether for iSeries DB2 or generic ANSI SQL, is very useful when taking an iSeries schema and duplicating it in, say, mySQL.

HTH
Vern

At 01:26 AM 9/22/2007, you wrote:

Speaking of Visual Explain, any one have handy a url to point to information
about how to use it, what it means etc.?

On 9/21/07, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> Not to knock on STRSQL as I still use it for its own advantages, but:
>
> - You can call stored procedures that return result sets and actually see
> the result set data
> - you can run CL commands by prefixing them with CL: prefix
> - you can run multiple statements or entire scripts at a time
> - you have access to Visual Explain
> - you can save scripts to a PC for reuse
> .
> .
> .
>
> Elvis
>
>
> RPG & DB2 Summit | Minneapolis | October 1-4
> Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
> October 16
> 2007 System i Fall Technical Conference | Orlando | November 4-7
> Celebrating 10-Years of SQL Performance Excellence on IBM System i,
> eServer
> iSeries and the server affectionately known as the AS/400
>
> -----Original Message-----
> Subject: RE: SQL Finding & Using -- iNav Run SQL
>
> Thanks All, I Found It. What's the benefit over using STRSQL?
>
> --
> 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.
>
>
--
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.