× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: SQL Performance Improvements Suggestions
  • From: Ata510@xxxxxxx
  • Date: Sat, 27 Feb 1999 02:06:34 EST

In a message dated 2/19/99 11:08:13 AM Central Standard Time,
DMunro@badgerminingcorp.com writes:

> 
>  In our discussions with SSA about performance, I received a list of 27
files
>  from Jack Tyse that SSA had identified as needing logicals created.  These
I
>  created using DDS and are stored in the BPCSUSRF library.  
>  
>  The other recommendation was to run the command PRTSQLINF "after" running
>  any program that "seemed" to take longer than what you were expecting.
>  Looking through the report that was generated, I would look for a line
>  saying 
>  "SQL4012   Access path created from keyed file IPPL15 for file 1."  If this
>  line did not appear in the listing, it meant an existing logical was used.
>  A few lines above this would be the SQL Declare statement and in there you
>  would find the "order by" phrase, which is how the logical key is
>  constructed.  This may seem like a lot of work but we went through the
order
>  entry, billing, and some of the CEA programs looking for logicals to make
>  permanent.  Until I see a performance problem, I will not do this again.
>  Actually, I've done this once in the 7+ months we have been live.  I think
>  it took me longer to find were I had "filed" these instructions to write
>  this e-mail than the time it would take you to determine which logicals
need
>  to be created.
>  
>  I believe it is worth the effort involved.  Of course, the deciding factor
>  in whether or not to add a logical is the system overhead involved in
>  keeping "another" view updated.  That each user will have to determine
based
>  on their own circumstances.


There is a bit easier way to do this.....find system recommeded
logicals.....if you run STRDBG with UPDPROD *YES (and don't specify a
program...yes you can do that) over a batch or interactive job, with CL
message level logging at *YES, you will see the optimizer's actual
recommendations for logical files. You will also be able to tell if it is re-
using Open Data Paths (good) or not (not so good) etc.. What you miss by doing
this instead of DBMON is that it will not tell you if that particular logical
is from the SQL statement that is taking the most time to run or not....DBMON
will tell you that.
Then you can build the recommended logical, re-run the program, and do a
PRTSQLINF (but heck -- when 10 programs might be called downstream...which
program do you do this command over, and chances are you might not even know
every program that called . . . ) at any rate, you can either re-run DEBUG, or
PRTSQLINF if you are reasonably certain of the program it came from . . . and
look to see if the new logical is used. 
+---
| 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
+---


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.