Good morning list
For those faint of heart, please take your nitroglycerine pills now.
The article I need to ask you about can be found at
http://www.itjungle.com/fhg/fhg041608-story01.html
First some groundwork
On our customer file within the 2 address lines we have addresses
containing the words
"APT "
APT. "
"UNIT "
etc
The project is to attempt to "pull" this data out and place into a separate
field. I came up with the following idea
I have a file (APTSCAN) with one 12 character field (APTSCANDEF) containing
(at the moment) the following data, % characters included
%APT %
%APT #%
%APT. #%
%APT.%
%BLDG%
%DEPARTMENT%
%DEPT %
%FLOOR %
%FLR %
%FLR.%
%P O BOX%
%P.O. BOX%
%PO BOX%
%PO.BOX%
%POBOX%
%ROOM %
%UNIT %
%UNIT #%
I then ran the following SQL statement
SELECT * FROM custadrp a, APTSCAN b
WHERE a.CADD1 like b.APTSCANDEF or
a.CADD2 like b.APTSCANDEF
and it worked perfectly. All the relevant customer records were displayed.
I then sent the above SQL to an associate who has to do something similar.
He ran the same sql and the results were COMPLETELY different
He had displayed ALL the customer records, with each customer record being
displayed once for each record on the APTSCAN, even if the customers data
did or did not contain what was in the APTSCAN file.
To cut a LONG story short, my associate has read and followed the above
article.
The changes he had made to his SQL query options file was as follows(this
is from an e-mail he sent to me)
Quote
There were 2 difference in my SQL query options file. The first was
MESSAGES_DEBUG. Mine was set to *YES, which would force SQL to always dump
the debug messages to the job log. I do not see this causing the results we
saw.
The second difference was IGNORE_DERIVED_INDEX. It defaults to *NO. Mine
was set to *YES. According to the text in the file, it says....
Allows SQE to process the query even when a mapped key index or select omit
index exists over a table in the query. SQE will ignore the derived index
(s) and continue. QQVAL: *DEFAULT--The default value is set to *NO.
*YES--Allow the SQE optimizer to ignore the derived index and process the
query. The resulting query plan will be created without any regard to the
existence of the derived index(s). *NO--Do not ignore the derived index. If
a derived index exists CQE will process the query.
I tested my config by setting this back to *DEFAULT and the query performed
as normal.
From my understanding, there are 2 query engines present on the iSeries,
SQE and CQE. CQE is for "legacy" type queries using logicals containing
Select/Omits. CQE is also very bad when it comes to performance. Setting
IGNORE_DERIVED_INDEX to *YES forces all queries to always use the newer
engine (gaining better performance). My understanding was: if your SQL
statements do not specifically refer to a logical with Select/Omits, you
are fine to do this. This is why I always use the physical file name in my
statements and let the optimizer figure out the underlying details.
End Quote
The questions that come to mind are
1. Are we missing any PTF's?
2. Is this a problem that should be reported to IBM
If anyone has some ideas and/or insight as to why this is happening. It
would be MUCH appreciated
P.S. I have also sent this same inquiry to Ted Holt, the author of the
above article
Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
As an Amazon Associate we earn from qualifying purchases.