RE: Another V5R2 question -- SYSTEM21

Another thing to think about... Along the same lines as Richard's comment:

By default, the query engine checks a certain number of access paths before
it stops looking.  This is controlled by a setting somewhere( my memory
fails me ) that may have been reset when you migrated to V5R2.  If it is
spending less time looking for access paths, it may have to build them more
frequently.

If that number is lower on the V5R2 system, you might experience an increase
of performance (maybe) by setting it higher.  It is possible that the query
engine just says "I've searched all that I'm allowed to search, so I'll pick
the best access path that I've found".





Here is what I found on the IBM website:  (I've heard that you can adjust
the optimization time to increase your performance in certain circumstances)






Optimizer decision-making rules
The optimizer uses a general set of guidelines to choose the best method for
accessing data. The optimizer: 

Determines the default filter factor for each predicate in the selection
clause. 
Extracts attributes of the table from internally stored information. 
Performs a key range estimate to determine the true filter factor of the
predicates when the selection predicates match the left-most keys of an
index. 
Determines the cost of creating an index over a table if an index is
required. This index is created by performing either a table scan or
creating an index-from-index. 
Determines the cost of using a sort routine or hashing method if selection
conditions apply and an index is required. 
Determines the cost of table scan processing if an index is not required. 
For each index available, generally in the order of most recently created to
oldest, the optimizer does the following until its time limit is exceeded: 
Extracts attributes of the index from internally stored statistics. 
Determines if the index meets the selection criteria. 
Determines the cost of using the index using the estimated page faults and
the predicate filter factors to help determine the cost. 
Compares the cost of using this index with the previous cost (current best).

Picks the cheaper one. 
Continues to search for best index until time out or no more indexes. 
The time limit controls how much time the optimizer spends choosing an
implementation. It is based on how much time was spent so far and the
current best implementation cost found. Dynamic SQL queries are subject to
the optimizer time restrictions. Static SQL queries optimization time is not
limited. For OPNQRYF, if you specify OPTALLAP(*YES), the optimization time
is not limited. 

For small tables, the query optimizer spends little time in query
optimization. For large tables, the query optimizer considers more indexes.
Generally, the optimizer considers five or six indexes (for each table of a
join) before running out of optimization time. Because of this, it is normal
for the optimizer to spend longer lengths of time analyzing queries against
larger tables. 



-----Original Message-----
From: Richard_Caldicott@xxxxxxxxxxxxxxx
[mailto:Richard_Caldicott@xxxxxxxxxxxxxxx] 
Sent: Thursday, February 05, 2004 1:40 PM
To: System 21 Users
Subject: RE: [SYSTEM21] Another V5R2 question


Could someone clarify a point.  I was under the impression that the SQL 
code was efficient and worked well until IBM changed the way SQL worked, 
which prompted the need to change the statements.  Is this the case or 
have I been misled?

Richard




"Brunk, Kevin" <KBrunk@xxxxxxxxxxxx>
Sent by: system21-bounces@xxxxxxxxxxxx
02/05/2004 12:31 PM
Please respond to System 21 Users

 
        To:     "System 21 Users" <system21@xxxxxxxxxxxx>
        cc: 
        Subject:        RE: [SYSTEM21]   Another V5R2 question


Juan,

It's hard to imagine that a vendor would code embedded SQL so poorly that 
throughout their package EVERYTHING would perform poorly - I'm not saying 
it's impossible, but you'd think a reputable vendor would have tested the 
code under varying workloads to be sure they'd coded it most efficiently.

That said, is it possible that the poor performance is the result of the 
need for some permanent access paths?  That is, have you looked at some of 
the more common selections and order bys that the statements do and tried 
to build access paths to emulate them.  That technique can improve SQL 
performance because the system doesn't have to build access paths in some 
cases - and that could save a LOT of time.

There are a couple of companies I'm aware of that make software that will 
help you analyze and assess SQL performance including suggesting access 
paths to improve performance.  This is not an endorsement of these 
companies - we don't use the products - but I've seen demos of them and 
it's interesting what they can tell you, and that might quickly resolve 
some or all of the DW SQL problems.  The companies are MB Software and 
Centerfield Technology.  Both have websites you can visit for more 
information.  There are probably other companies, too - again, these are 
just two I've seen demos of.

==Kevin

-----Original Message-----
From: system21-bounces@xxxxxxxxxxxx [mailto:system21-bounces@xxxxxxxxxxxx]On
Behalf Of MLemon@xxxxxxxxxxx
Sent: Thursday, February 05, 2004 1:03 PM
To: system21@xxxxxxxxxxxx
Subject: RE: [SYSTEM21] Another V5R2 question


Juan,

I'm curious to know what GEAC is charging you for new auth codes?

We are planning on a v5r2 upgrade/hardware upgrade as this summer and will
need to get new auth codes.

Initial talks with GEAC indicate that they would charge us about 
$10,000.00
U.S. for auth codes.  (We are not currently under maintenance with them)

Mike

-----Original Message-----
From: Juan Doe [mailto:juandoe_2001@xxxxxxxxx] 
Sent: Thursday, February 05, 2004 6:27 AM
To: System 21 Users
Subject: RE: [SYSTEM21] Another V5R2 question


Thanks very much :-)

I checked if the programs were used and they weren't
so I've ignored them too! Everything seems to be
running okay.

>From the performance side, I am happy to say the
system is generally running as good as before (which
wasn't the case when we tried to upgrade last year) so
the PTF's have obviously sorted a lot of things out.

Unfortunately our Data Warehousing module is still a
disaster. This module has some really heavy use of
imbedded SQL, far more than anything in System21. The
overnight job for example, is taking minimum twice as
long to run :-( The imbedded SQL is poorly written and
can be streamlined for performance, which is yet
another job for me. 
As a company, we are not in a position to upgrade
machine right now - we priced some options up, but
GEAC also wanted their pound of flesh just to issue
new Auth.Codes because of a new Serial No.s and we
have had to put things on hold.

I'm not sure that an OS upgrade (which you are forced
to take as they withdraw support for the old version)
that means you have to rewrite large chunks of code is
a "step forward", but I suppose for shops who are
lucky enough to be playing with the latest technology,
V5R2 is a must.

Juan



--- "Brunk, Kevin" <KBrunk@xxxxxxxxxxxx> wrote:
> Juan,
> 
> When we upgraded from V4R5 to V5R2 in November, we
> only had three S21 pgms that wouldn't convert and I
> believe it was due to observability.  We are on
> version 3.5.0b of S21.  From looking at the "last
> used date" information, it appeared that they were
> never used, so I ignored them and we've not had a
> problem.  If these ARE used in your shop, you might
> want to check with GEAC - I would think they could
> give you modules updated for V5R2.
> 
> With regard to performance, my first guess was that
> you hadn't converted your data libraries (files are converted, too).
> But since you mentioned that in the first paragraph, you must have. 
> SQL might very well be the culprit.  In doing some reading this
> weekend about SQL cursors, I learned that the less
> specific you are about the use of the cursor, the
> more resource SQL brings to handle all variations. 
> So, the more specific you are, the more opportunity
> there is for SQL to be more efficient in the
> resources it chooses.  If the vendor's software is
> using a "lazy" approach to SQL coding, it could be
> causing the degradation.  I would think your
> warehousing module vendor should be able to help you
> optimize the software for V5R2.  V5R2 also includes
> (through Ops Nav) some tools to help investigate SQL
> performance issues.
> 
> We only noticed a slight degradation post V5R2, but
> within two weeks, we had converted from our 730 to
> an 825, and all our performance problems disappeared
> after that!  The 825 is a screamer. We partitioned
> it and moved our development system into a partition
> on it and now the development system is even light
> years faster - our testing users are delighted.
> 
> ==Kevin
> 
> -----Original Message-----
> From: system21-bounces@xxxxxxxxxxxx
> [mailto:system21-bounces@xxxxxxxxxxxx]On Behalf Of Juan Doe
> Sent: Tuesday, February 03, 2004 8:12 AM
> To: System 21 Users
> Subject: [SYSTEM21] Another V5R2 question
> 
> 
> Hi
> 
> we are currently upgrading our test box to V5R2 and
> during the Object Conversion, a lot of System21
> programs are failing to convert. I believe this is
> because Observability was removed from the program
> before being delivered by JBA. Some of the programs
> we
> have the source for, but the ones in the IPGxxxxx
> libraries did not come with source code.
> 
> I guess you people already at V5R2 must have
> encountered this as well - so can anyone advise how
> you got around this issue please.
> 
> Another quickie - we tried going to V5R2 about 10
> months ago, but encountered a big performance
> degradation, mainly noticeable on a non System21
> module we have for data warehousing. This has a lot
> of
> imbedded SQL and as the whole SQL engine was changed
> in this release, our decision was to hold off until
> IBM had released their usual lengthy batch of PTF's.
> 
> For anyone who has recently gone to V5R2 - did you
> notice any significant performance reduction?
> 
> Many Thanks for any help...
> 
> 
> Juan
> 
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free web site building tool.
> Try it!
> http://webhosting.yahoo.com/ps/sb/
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/system21.
> 
> 
> 
>
**********************************************************************
> CONFIDENTIALITY NOTICE:  The information transmitted
> in this message is intended only for the person or
> entity to which it is addressed and may contain
> confidential and/or privileged material.  Any
> review, retransmission, dissemination or other use
> of this information by persons or entities other
> than the intended recipient is prohibited.  If you
> received this in error, please contact the sender
> and destroy all copies of this document.  Thank you.
> 
> The Butler Company
>
**********************************************************************
> 
> 
> _______________________________________________
> This is the System 21 Users (SYSTEM21) mailing list
> To post a message email: SYSTEM21@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:
> http://lists.midrange.com/mailman/listinfo/system21
> or email: SYSTEM21-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the
> archives
> at http://archive.midrange.com/system21.
> 


__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.
_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.



**********************************************************************
CONFIDENTIALITY NOTICE:  The information transmitted in this message is 
intended only for the person or entity to which it is addressed and may 
contain confidential and/or privileged material.  Any review, 
retransmission, dissemination or other use of this information by persons 
or entities other than the intended recipient is prohibited.  If you 
received this in error, please contact the sender and destroy all copies 
of this document.  Thank you. 
The Butler Company
**********************************************************************


_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.



_______________________________________________
This is the System 21 Users (SYSTEM21) mailing list
To post a message email: SYSTEM21@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/system21
or email: SYSTEM21-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/system21.