RE: Is this an SQL problem or quirk

[Alan Shore <AlanShore@xxxxxxxx>]

I ALWAYS forget to include that info
We are on 5.4


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 04:04:33 PM:

> Alan;
>
> What version are you on?
>
> We are on 6.1 and we changed IGNORE_DERIVED_INDEX to *YES on day one
> and haven't looked back. We have had some SQL bugs but nothing like
> you described, most of the bugs we have hit have been with
> communications (RDB and otherwise) between the (i)s and the
> mainframe and *nixs. We don't talk to no stinking windows... :)
>
> Duane Christen
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> Sent: Tuesday, February 10, 2009 1:43 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Is this an SQL problem or quirk
>
> Thanks for your reply Duane
> That is in fact what is causing the problem, however, the difference
> between his and the system version does not make any sense as to the
> difference in the results Here is what I posted in an earlier e-mail
> we were thinking it was something to do with IGNORE_DERIVED_INDEX
> being changed from the default of *NO 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 processthe
query.
> Once this was changed back to the default, the results for BOTH of
> us was what was expected.
>
>
>
> 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 02:20:54 PM:
>
> > Alan;
> >
> > do a WRKOBJ OBJ(QAQQINI)under both users. The QAQQINI file is searched
> > for just like any other object, I be leave, and if there are different
> > versions in your library lists this might explain why you each are
> > getting different results.
> >
> > Duane Christen
> >
> >
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> > bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> > Sent: Tuesday, February 10, 2009 11:21 AM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: Is this an SQL problem or quirk
> >
> >
> > 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.
> > --
> > 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 email has been scanned by the MessageLabs Email Security System.
> > For more information please visit http://www.messagelabs.com/email
> > ______________________________________________________________________
> >
> > NOTICE: This electronic mail transmission may contain confidential
> > information and is intended only for the person(s) named.  Any use,
> > copying, or disclosure by any other person is strictly prohibited.
> > If you have received this transmission in error, please notify the
> > sender via e-mail.
> > --
> > 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 email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
>
> NOTICE: This electronic mail transmission may contain confidential
> information and is intended only for the person(s) named.  Any use,
> copying, or disclosure by any other person is strictly prohibited.
> If you have received this transmission in error, please notify the
> sender via e-mail.
> --
> 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.