Re: Performance & SQL coding

MacWheel99@aol.com wrote:

> >  SQL has the coding methods to avoid poor performance, but it is very
> >  easy to cause poor perfromance with SQL.
>
> I am new to SQL - we converted to BPCS 405 CD from BPCS/36 last year & I am
> covered up with lots of aftermath implications - one of which is learning how
> to use SQL productively.
>

Al I too am new to SQL but I've been coding for 20+ years.  In the end it is 
just
common sense.  You create an index that allows you to access the data you need
with the minimum possible DB I/Os.  SQL has techniques to create indexes,
position cursors and get the records and ONLY those records you need.May I
presume that those of you doing analysis of your perfomance dogs are

> discovering that the inexperienced SQL programmers are apparently on the
> staff of SSA & that these problems are not purely in-house errors of
> omission, so that OSQ will have lists of upgrades needed?

I dont know that SSA will do anything.  We are going to use STRDBMON to have the
system tells us the indexes we need.  STRDBMON dumps and burns and we need a PTF
upgrade to get it to work.  Will keep you updated as to results.

Following was supplied by Mike ( Immense thanks Mike, greatly appreciated. 
Frank)

<quote>
Users looking for a way to analyze DBMON output without writing your own queries
should either look at enhancements in Operations Navigator with the latest
version of Client Access, or a third party product.  Operations Navigator in it
latest version, released at the same time as V4R4, contains graphical interface
to start/stop the monitor and also predefined reports that can be generated
using the GUI.  Centerfield Technologies has a product which is a windows based
for analyzing AS/400 DB2 SQL performance and uses the DBMON data plus its own
logic to walk you through the analysis of the system, a job, a SQL statement,
etc from a windows GUI.

Mike Breitbach  -  Software Engineer
IBM Rochester,  AS/400 ERP Development
email: mbreit@us.ibm.com
<end quote>

following is a reply from Clare.  (Immense thanks Clare, greatly appreciated.
Frank)
<quote>
Subject:           Re: Antwort:Re: Performance solutions - more than one kind
exist
     Date:           Fri, 9 Jul 1999 14:26:03 +0100
     From:          Clare.Holtham@ssa.co.uk
QPFRDATA or whatever. You will get a new member in that file each time you
run it, so will need to change your query to access the latest member each
time you run it. This isn't so easy to do with SQL by the way!
Just one more caveat: you MUST get the PTF from IBM that stops the
DBMonitor crashing your BPCS jobs before you run it - it is SF51242 for
V4R2 and SF53723 for V4R3, and is too big to download.
Hope this helps,
Clare
<end quote>

> We have end users creating queries.
> They are able to figure out which file contains the data they need, thanks to
> the wonders of external file descriptions, but my end users are not using the
> available logicals - it is almost too much to ask that a non-programmer
> comprehend their importance & the performance hit taken by not using them.

Al this is precisely what is happening with SQL.  The code (not every bit) is
written without taking into account access paths (indexes, logicals). Strange
isn't it how professional programmers are performing as though they are
end-users.



+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---