Re: Problems with Query on V4R5

from Al Macintyre

I have noticed this phenomena intermittently on V4R3.

We have numerous files with fields that are called "notes" so that a short 
essay can be attached to an item, customer, order, shipment, engineering 
change, etc. as needed to provide some kind of explanation.

When the queries were originally written, the lines of text printed out in 
the sequence they were in the file, no problem, but I am seeing a growing 
volume of lines being out of sequence.  Fortunately the file designs include 
a sequence number that can be added to the queries when we see this nonsense 
developing.

The problem is of course that it is sporadic & most notes print fine.
My suspicion is that some IBM "enhancement" does this to some queries as we 
make some other adjustment to them such that the ones that work fine are 
those that were created before the "enhancement" got installed.

You might look at the query/400 option that selects what sequence to use ... 
we have experimented with the different rules offered & for example have 
selected some queries to output text sorts without case sensitivity.

> From: murphy@uif.uillinois.edu (Murphy, Guy)

>   Has anyone had some of their queries not work the same since
>  upgrading to V4R5? We've have a set of queries that use three files and
>  print out text fields on the report in FIFO order in effect writing a
>  paragraph of information on the report.  These text fields are not used as
>  key fields in the report and are not used as sort fields in the query.
>  After upgrading to V4R5 these fields are now being printed on the reports 
in
>  alphabetical order, which makes them rather useless as a paragraph of
>  information.
>   IBM says that the query optimizer has been improved for performance
>  in V4R5 and that it's working as designed.  Therefore, it's not a problem
>  and they will not work on solving it.  To me, the optimizer has gone
>  psychotic and is doing more work that is required.  It's takes a non-key,
>  non-sort field and makes a sort field.  IBM recommends either rewriting the
>  queries in SQL using record sequence order or adding a new field to the
>  files and using it as a sort field to get the text fields to print 
properly.
>  Some of these queries have been around since V2R3. Is there any solution to
>  this problem that doesn't require us to rewrite all of these queries or do
>  we have to bite the bullet and rewrite?
>  
>  Guy Murphy - murphyfa@uiuc.edu
>  FACTS system - University of Illinois
>  217-333-8670
>  www.heisercoaching.com


MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
AS/400 Data Manager & Programmer for BPCS 405 CD Rel-02 mixed mode (twinax 
interactive & batch) @ http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies - fax # 812-424-6838

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@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
+---