Re: Performance & SQL & DBMON

>  From:     MacWheel99@aol.com ( Al Macintyre ) 

Immense thanks to all contributors of BPCS_L tips such as Clare (IBM Ptf) 
Dean (BPCS_L) Frank (clarification) George (lots tips) Paranoids (focus) ... 
greatly appreciated by Al

> Subj:  Re: Performance & SQL coding

>  From:        fkolmann@revlon.com.au (Frank)
>  >  > SQL has the coding methods to avoid poor performance, 
>  >  >  but it is very easy to cause poor performance with SQL.

>  From:     MacWheel99@aol.com (Al) 
>  > 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.

          { Frank }
>  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 

         { Al }
>  > .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?

          { Frank }
>  I dont know that SSA will do anything.  We are going to use 
> STRDBMON to have the system tell 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) (ditto from Al)
>  
>  <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 its 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>

       ( Al )
Are there similar products for twinax interactive mixed mode?

      { Frank }
>  following is a reply from Clare.  (Immense thanks Clare, greatly 
appreciated.
>  Frank) (Ditto Al - I will talk about this in a separate e-mail)

        { Al }
>  > 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.
>  
         { Frank }
>  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.

    ( Al )
Could be over-reliance on As/Set & SQL tips 
... falsely assuming it does everything it should do

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