× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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 process the 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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.