Re: Inquiry : Queries and number of ODPs in programs

At 09:50 PM 4/8/1998 +0200, you wrote:
>
>Fellow-midrangers,
>
>A colleague and I are responsible for 2 AS/400 ( 620/600 ).
>One of our tasks is of course the tuning of those systems.
>
>Currently we're having 2 "hot" discussions with the programmers :
>
>1) Usage of RUNQRY-command. 
>---------------------------

-snip-

>2) Number of ODPs in a program.
>-------------------------------

-snip-

>
>What are your opinions and did you have similar discussions ??
>

Well, here goes!

1) I believe that Query/400 queries _can_ be better than OPNQRYF or any
other ad hoc (interactive or dynamic) SQL evocation, because *QRYDFN
include an access plan as part of the object. The ad hoc varieties need to
run optimization every time they are executed, and this can have an affect
on performance. Therefore, RUNQRY of an _existing_ *QRYDFN is not too bad,
depending on how the *QRYDFN is constructed. IMO, however, RUNQRY over a
file or set of files, with *RCDSLT, is an ad hoc process, requiring
optimization at the time of execution.

The system now gives us lots of help in optimizing our SQL/OPNQRYF/Query
processes. Just do the following series of commands:

    CHGJOB LOG(4 0 *SECLVL) LOGCLPGM(*YES)
        /* This will assure you of the most detail when you print the log */
    CHGQRYA QRYTIMLMT(0)
    STRDBG
    Execute the SQL/OPNQRYF/Query

When you get the message that the time limit is exceeded, cancel (C). Then
look at your joblog. You will see all kinds of info, including what the
system tried to do to optimize the query, as well assuggestions for what
would improve things, such as access paths, etc. The more recent the
release level of OS/400, the more information is provided.

Things like the order of files listed can result in 20-fold improvement.
Redundant index fields can be beneficial, as well. Check out the SQL
manuals, as well as Database Management and/or Programming, for their
performance and optimization chapters. Lots of good info there.

In the end, the best solution, IMO, for interactive work is embedded SQL
with replacement variables. This will have an access plan embedded in the
*PGM object and will usually get around optimization. It also seems that
the query engine loads faster in this case.

In addition, SQL is the de facto standard for DB access, and its support on
the 400 is getting better all the time. As we leave our 400 island and see
more of the world, SQL knowledge will be one of our most valuable assets.
That's why I've been promoting the move from OPNQRYF to some form of SQL in
our shop. (We don't have the licensed product yet, but we _do_ have a
command that uses QMQRY to run ad hoc SQL statements. It's loosely based on
one the Midrange Computing has.)

Finally, any of the query varieties are great tools. But any of them,
wrongly used, can bring the biggest, fastest system to its knees. (Been
there, done that.) What's the old ratio, "ease of end-use is inversely
proportional to the amount of developer-effort" or something like that?

2) I think that your ODP situation is a real problem. Sounds like y'all
need to look at shared access paths, as well as using *SHARE between
programs, etc. The system handles shared access paths very well, if you've
created the logicals in the right order (RTFM-read the
fine/free/fill-in-the-blank :-) manuals). But I think this is a symptom of
lazy development. Or a remnant of history. We've an
expression—"TwelveYears!" Most of us do the best we know, at the time. We
can always look back and see better ways!

Cheers

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN 55401
(612) 371-1111 x480


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---