Re: Is this an SQL problem or quirk

[Alan Shore <AlanShore@xxxxxxxx>]

Definitely no overrides in play.
My main concern is that if we get IBM involved and it turns out to be some
stupid thing that we did (or didn't do) then IBM will charge us some
exorbitant hourly fee for them to turn around and say, well it was ALL your
fault because of (insert answer her). Heres our bill.



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

midrange-l-bounces@xxxxxxxxxxxx wrote on 02/10/2009 12:13:02 PM:

> Alan,
>
> While the result set is displayed, do a WRKJOB from another session
> and confirm that the same fiels are actually open, no overrides in
> effect.
>
> After that, probably should just call IBM.
>
> Charles
>
> On Tue, Feb 10, 2009 at 11:16 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:
> > Thanks for your reply Charles
> > It may seem that either, we both need new glasses, or its some mass
> > hallucination between us, but the LONG story was
> > 1/. we thought that he had a weird version of the custadrp file (not
true)
> > 2/. he was signed onto a different box (not true - the file APTSCAN
ONLY
> > exists in my library on the development box)
> > 3/. we changed the SQL to ONLY look at the files in the named libraries
> > the result was still the same.
> > Run the SQL signed on as me - no problems, the results were as
expected.
> > Run the SQL signed on as him (at  the same terminal in fact, different
> > sessions) the weird results are displayed
> > In all circumstances we copied the SQL statement and pasted the same
SQL
> > statement into each sessions STRSQL
> > So we can definitely say that the SQL statement is NOT the problem
> >
> >
> > 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
> >
> > midrange-l-bounces@xxxxxxxxxxxx wrote on 02/10/2009 10:56:22 AM:
> >
> > > This is on the same machine right?
> > >
> > > Double check his SQL.  Try the statement again
> > >
> > > It sounds as if he missed the where when he copied & pasted.
> > >
> > > Are you still getting the same results?
> > >
> > > Charles
> > >
> > >
> > > On Tue, Feb 10, 2009 at 10:44 AM, Alan Shore <AlanShore@xxxxxxxx>
wrote:
> > > > Thanks for your reply Charles, but the SQL statement he ran was the
one
> > > > that I had used. He copied and pasted it into his STRSQL session.
Hence
> > our
> > > > quandary.
> > > >
> > > >
> > > > 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
> > > >
> > > > midrange-l-bounces@xxxxxxxxxxxx wrote on 02/10/2009 10:28:42 AM:
> > > >
> > > > > Alan,
> > > > >
> > > > > The result set (but not necessarily the order of that result set)
> > > > > should always be the same no matter what query engine or options
are
> > > > > in effect.
> > > > >
> > > > > While it's possible you've found a bug, I'd think it more likly
that
> > > > > your associate's SQL statement was not correct.
> > > > >
> > > > > Can you post it?
> > > > >
> > > > > Charles
> > > > >
> > > > > On Tue, Feb 10, 2009 at 9:30 AM, Alan Shore <AlanShore@xxxxxxxx>
> > wrote:
> > > > > > 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
> > > > > > --
> > > > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > mailing
> > > > list
> > > > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > > > To subscribe, unsubscribe, or change list options,
> > > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > > > Before posting, please take a moment to review the archives
> > > > > > at http://archive.midrange.com/midrange-l.
> > > > > --
> > > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> > > > list
> > > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > > To subscribe, unsubscribe, or change list options,
> > > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > > Before posting, please take a moment to review the archives
> > > > > at http://archive.midrange.com/midrange-l.
> > > > --
> > > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
> > list
> > > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > > Before posting, please take a moment to review the archives
> > > > at http://archive.midrange.com/midrange-l.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.