Having difficultly making my DB2 queries case sensitive

350310 pts.
Tags:
AS/400
IBM DB2
Stored Procedures
I'm been having a lot of problems making my DB2 (AS/400) queries case sensitive. Here's an example of what I'm trying to do:
SELECT *
FROM NameTable
WHERE LastName = 'smith'
That returns zero results but this returns 1000's or results:
SELECT *
FROM NameTable
WHERE LastName = 'Smith'
Does anyone have any experience with this? Here's my stored procedure too.
BEGIN
DECLARE CR CURSOR FOR
SELECT  T . ID ,
    T . LASTNAME ,
    T . FIRSTNAME ,
    T . MIDDLENAME ,
    T . STREETNAME || ' ' || T . ADDRESS2 || ' ' || T . CITY || ' ' || T . STATE || ' ' || T . ZIPCODE AS ADDRESS ,
    T . GENDER ,
    T . DOB ,
    T . SSN ,
    T . OTHERINFO ,
    T . APPLICATION
FROM
    ( SELECT R . * , ROW_NUMBER ( ) OVER ( ) AS ROW_NUM
    FROM CPSAB32.VW_MYVIEW
    WHERE R . LASTNAME = IFNULL ( @LASTNAME , LASTNAME )
    AND R . FIRSTNAME = IFNULL ( @FIRSTNAME , FIRSTNAME )
    AND R . MIDDLENAME = IFNULL ( @MIDDLENAME , MIDDLENAME )
    AND R . DOB = IFNULL ( @DOB , DOB )
    AND R . STREETNAME = IFNULL ( @STREETNAME , STREETNAME )
    AND R . CITY = IFNULL ( @CITY , CITY )
    AND R . STATE = IFNULL ( @STATE , STATE )
    AND R . ZIPCODE = IFNULL ( @ZIPCODE , ZIPCODE )
    AND R . SSN = IFNULL ( @SSN , SSN )
    FETCH FIRST 500 ROWS ONLY )
AS T
WHERE ROW_NUM <= @MAXRECORDS
OPTIMIZE FOR 500 ROW ;

OPEN CR ;
RETURN ;

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta

    You need to add this statement to your stored proc:

    SET OPTION SRTSEQ *LANGIDSHR

    Your ordering or selection should be done using a "shared-weight" sort sequence. That will assign the same 'weight' to letters such as 'a' and 'A' or 'x' and 'X'.

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb

    Perhaps this will help

    WHERE Upper(LastName) = Upper('Smith')

    Which I suppose looks something like this?

    WHERE Upper(R . LASTNAME) = Upper(IFNULL ( @LASTNAME , LASTNAME ))


     

    50,385 pointsBadges:
    report
  • TomLiotta
    The UPPER() function can be used; but as soon as it appears on the left-hand side of the condition, indexing is out and a table scan is in. Technically, the index ought to be created as "shared-weight". -- Tom
    125,585 pointsBadges:
    report
  • philpl1jb

    Good info Tom and it's sure easier to code.

    I think that this will end up as a scan anyway because of the nature of the query

     .. he's passing one or more of 9 fields

     .. I don't think the system can be very smart on record indexing given the problem.

    One time he might pass DOB and Zip

    The next might be LastName and City and State. 

    50,385 pointsBadges:
    report
  • TomLiotta
    It's actually difficult to be sure since the rest of the proc isn't shown. It might work out because the search conditions are all ANDed together. The query optimizer is free to use them in any order it thinks is best, depending on what INDEXes exist and other factors. We'd need to know more about the database definitions to guess how the query might be restructured. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following