Selecting data from a "mixed-case" filed -- MIDRANGE-L

Hi all -

We have been fighting a problem where the AS/400 seems to always want to do
a full table scan when we try to select rows from a "mixed cased" field...

I was wondering how other people have solved this problem....

Background
==========
Here's a summary of the problem to date as described by one of our SQL
application developers -

Our MMS system architecture consists of a VB6 front-end connecting to the
AS/400 DB2 V5R1 via ADO v.2.5 using the IBM-supplied OLE DB Provider
IBMDA400.  All data access is coded in SQL stored procedures on the AS/400 -
created & maintained via Ops Navigator.

Basically the problem centers around case-sensitivity - the current version
of DB2 is case-sensitive in that data is stored in mixed-case.  As part of
the MMS project we have a need to carry out case-insensitive searches e.g.
searches on street names, customer names, etc.  Our 1st attempt to do this
followed the standard approach of coercing the arguments in our 'where'
clause in our select statements to uppercase e.g. select * from table where
ucase(col) like ucase(search-arg).  Unfortunately in DB2 on the AS/400 at
this release any kind of column-function on a predicate causes the optimizer
to bypass all indices on the table & perform a table-scan; clearly not the
desired effect when we have 1M+ rows in the tables we're trying to search
against.

Our initial investigation both via IBM tech support & via our own research
using the web, AS/400 & DB2 newsgroups showed that there were 2 suggested
solutions :-

1/ Create derived columns on the tables we want to search which are
upper-case versions of the columns we want to search on.  Create indices on
these new derived columns to aid the optimizer when the search sql is
compiled / submitted.

2/ Create logical / views on the tables we want to search for the columns we
want to search on & compile those logicals using a SRTSEQ parameter of
*LANGIDSHR (rather than the default of *HEX).  The effect of this parameter
is to treat upper & lower case letters in the same manner (shared weight).

Clearly option 2 is desirable as no table changes are required - in fact
logicals / views would have to be created anyway to support the searches.
In addition to space considerations, adopting option 1 would require us to
make table changes anytime a user requested changes to our search arguments.

So at this point we went ahead & created a logical over 1 of the tables as a
test & compiled it the SRTSEQ parameter set to *LANGIDSHR.  We were then
able to run sql tests on the AS/400 via STRSQL using this new logical over
the table - this was a  dynamic sql statement along the lines of 'select *
from table where col like 'arg%'.  One side-note: we had to set the STRSQL
session attributes SRTSEQ parameter to match (*LANGIDSHR also).  However,
running the same SQL via either Ops Navigator or via VB did not yield the
same results & from running VISUAL EXPLAIN we could see that it did indeed
still do a table-scan  i.e. the new logical was not being picked up.

Since the IBM OLE DB Provider (unlike the ODBC driver) provided no mechanism
to specify the SRTSEQ parameter at connection time, I decided to investigate
what other 3rd party OLE DB drivers were available.  After digging around I
located Hit Software's product which does allow this parameter to be
supplied - I downloaded their evaluation version, installed & configured it.
On 1st test calling the dynamic sql as above we got the desired results.
However, as I mentioned earlier all of our data access is via sql stored
procedures; changing the VB code to call the same kind of sql via a stored
procedure did not yield the correct results.  At this point we realized that
the stored procedure which had been created via Ops Navigator was not
compiled using the correct SRTSEQ parameter - we had been supplying this in
the CREATE PROCEDURE via the SET OPTION syntax; for some reason it was being
ignored / overridden.  So we created the stored proc by running the CREATE
PROCEDURE sql through our VB program using the Hit OLE DB Provider (with the
SRTSEQ set appropriately) - this created / compiled the proc correctly on
the AS/400 and we were then able to call it from VB successfully.  Good news
but it meant relying on an additional purchase of software (& dealing with
whatever new limitations it would bring!).

After some digging around & talking to our AS/400 admins it seemed apparent
that the job settings for the QZDASOINIT job spawned when using the SQL
window via Ops Nav were the problem.  We then decided to change the user
profile for QUSER so that the default SRTSEQ would be *LANGIDSHR.


Latest Update
=============
So last night we changed the QUSER user profile so that the SRTSEQ would
default to *LANGIDSHR.  The hope was that any jobs spawned for QUSER from
the client machine (either via VB / OLE DB or Ops Navigator) would inherit
this setting.  Unfortunately this does not always seem to be the case - if
we launch a SQL window from Ops Navigator  & look at the associated job
attributes they show that the SRTSEQ is still set to *HEX.  Conversely,
launching the DB2 Stored Proc Debug tool (JDBC based?) & the job behind it
does have it's SRTSEQ changed?

Anyway, If we change the SRTSEQ setting for the job via Ops Navigator to
*LANGIDSHR then we can run queries, stored procs etc. & the result is as
desired.  Similarly, if we break our VB code in the debugger after the ADO
connection has been opened, we can also change the spawned job's SRTSEQ
settings, continue running our code & get the desired results.


The Work-Around
===============
Luckily, since all of our data access is coded via stored procedures then as
long as these are created & compiled with the correct SRTSEQ, then our
client access via VB with the IBM OLE DB Provider will work fine.  To do
this we need to do the following :-

1/ Fire up Ops Navigator & open a SQL window.
2/ From the Job Log locate the job # for the job (QUSER/QZDASOINIT) spawned
via the SQL window.
3/ Locate this job via Ops Navigator & change it's sort parameters on the
'International 1' tab via it's properties.  Change from a value of 'None'
(*HEX) to 'Use shared-weight sort' (*LANGIDSHR).
4/ Create all stored procs via this SQL window - they will then be created /
compiled with the appropriate SRTSEQ.

Additionally I think we need to leave the QUSER profile set also to use
*LANGIDSHR - I need to discuss this with the AS/400 admins to make sure
there are no long-term ramifications with leaving this set to this value.


Kenneth

****************************************
Kenneth E. Graap
IBM Certified Specialist
AS/400e Professional System Administrator
NW Natural (Gas Services)
keg@nwnatural.com
Phone: 503-226-4211 x5537
FAX:    603-849-0591
****************************************