Re: Inquiry : Queries and number of ODPs in programs

[PaulMmn <PaulMmn@xxxxxxxxxxxxx>]

We've been letting our user base create and run their own queries for at
least 10 years now.  Some of them are rather good at it.  Others....

Our resident Query expert (IS Background, programmer, knows the
applications, knows the data base) can take a user's query that uses 5 or
10 files and runs for 6 hours, and turn it into about 20-30 queries that
run in about 10 minutes (if the system's heavily loaded).

He's found that the biggest bottleneck is doing -anything- in a Query that
results in the system building a new index.  He uses his first (few)
queries to extract the data from the BIG files to a (series of) work
file(s).  Then, once his data's been chopped down to manageable size, he'll
sort his data.

If your users are trained how to correctly write a query, and know the
database, and what the data -means-, a query can be the correct solution to
a gotta-have-it-now report.  Or a daily job.

Of course, there's always Dave.  He can take a simple, 2-file Query and
make it run for hours and fill all available DASD.  We're still working on
Dave.

I try to encourage Query users to use 2-3 files (MAX) in a single Query,
and be liberal in their use of work files (which we automatically clear and
delete).  Of course, there are those folks who can take 10 files in a
single query and make it work efficiently, but they are in a TINY minority.
And their queries tend to be 'write only;' you -can't- figure out what
they're doing!  Or why!!

We've also offloaded our queries to our 2nd machine, running on a MIMIXed
copy of our data.  Keeps the 'Dave' queries from fouling up the interactive
response time.



Number of ODPs....  Fewer is better (imho).  But it depends on the needs of
your program.

However, like the Queries, it might be better to split up a program that
uses so many ODPs into a series of programs...


--Paul E Musselman
PaulMmn@ix.netcom.com

=-=-=-=-=-=-=-=-=-

>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.
>---------------------------
>We think that this command should mainly be used by the DP-staff itself
>(testing purposes ) or for ad-hoc reports.
>The programmers on the otherhand are using this command in practicly 50 %
>of the applications
>(including interactive ones ).
>Some of the batchjobs are 5 or more RUNQRY-commands that run after each
>other in order to produce one single query-report.
>They say : "its easy to use and with minimum on effort".
>The course about SQL has just finished, we're shaking already.
>
>2) Number of ODPs in a program.
>-------------------------------
>We try to encourage them to us as less ACCPTHs in a program as possible. (
>whatever that means ).
>What we cannot understand is that a simple report needs 40 or more ODPs and
>some applications uses more than 100 ODPs (yes, hundred).
>
>We have about 120 interactive users ( still growing ) and 10 batch-programs
>running simultaneous.
>
>When we say that the users are complaining about the response-times, the
>programmers cannot understand that it is due to the usage of RUNQRY-cmd and
>the number of ODPs open in the jobs.
>
>What are your opinions and did you have similar discussions ??
>
>Many thanks,
>
>Chris


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