|
Alan,true)
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
ONLY2/. he was signed onto a different box (not true - the file APTSCAN
expected.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
SQLRun 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
wrote: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>
oneThanks for your reply Charles, but the SQL statement he ran was
the
Hencethat I had used. He copied and pasted it into his STRSQL session.
areour
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
thatin effect.
While it's possible you've found a bug, I'd think it more likly
now.wrote: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>
Good morning list
For those faint of heart, please take your nitroglycerine
pills
a
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
recordseparate
containingfield. I came up with the following idea I have a file
(APTSCAN) with one 12 character field (APTSCANDEF)
displayed.(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
similar.
I then sent the above SQL to an associate who has to do
something
He ran the same sql and the results were COMPLETELY different
He had displayed ALL the customer records, with each customer
followscustomersbeing
displayed once for each record on the APTSCAN, even if the
abovedata
did or did not contain what was in the APTSCAN file.
To cut a LONG story short, my associate has read and followed
the
article.
The changes he had made to his SQL query options file was as
was(this
is from an e-mail he sent to me) Quote There were 2 difference
in my SQL query options file. The first
*NO.alwaysMESSAGES_DEBUG. Mine was set to *YES, which would force SQL to
dump
results wethe debug messages to the job log. I do not see this causing
the
saw.
The second difference was IGNORE_DERIVED_INDEX. It defaults to
derivedselectMine
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
omit
index exists over a table in the query. SQE will ignore the
*NO.index
(s) and continue. QQVAL: *DEFAULT--The default value is set to
regardprocess*YES--Allow the SQE optimizer to ignore the derived index and
the
query. The resulting query plan will be created without any
yourto
containingthe
index. Ifexistence of the derived index(s). *NO--Do not ignore the
derived
performeda derived index exists CQE will process the query.
I tested my config by setting this back to *DEFAULT and the
query
iSeries,as normal.
From my understanding, there are 2 query engines present on
the
SQE and CQE. CQE is for "legacy" type queries using logicals
newerSettingSelect/Omits. CQE is also very bad when it comes to performance.
IGNORE_DERIVED_INDEX to *YES forces all queries to always use
the
engine (gaining better performance). My understanding was: if
Select/Omits,SQL
statements do not specifically refer to a logical with
nameyou
are fine to do this. This is why I always use the physical
file
details.in
my
statements and let the optimizer figure out the underlying
happening.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
ofIt
would be MUCH appreciated
P.S. I have also sent this same inquiry to Ted Holt, the
author
mailingthe
mailingabove 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)
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)
mailinglist
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)
listlist
listTo 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
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
listTo 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
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 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.