RE: Is this an SQL problem or quirk

Did you compare your session defaults used in the STRSQL command? Look
at the "Allow copy data" option in particular. Perhaps you should try
running the statement in I Navigator to see what results you get.

I'm wondering about the APTSCAN file... I know I've been clobbered in
the past when passing a "LIKE" pattern into embedded SQL, where the
blanks at the end of the field become part of the search string....
Perhaps you need to make your APTSCAN.APTSCANDEF field varying length,
so that your scan tokens do not carry unwanted blanks.

However, this would not explain why YOU get the results you are looking
for....

Hth,
Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Tuesday, February 10, 2009 10:16 AM
To: Midrange Systems Technical Discussion
Subject: Re: Is this an SQL problem or quirk

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.