Case Sensitivity Issue

[damianp]

Hi there

The Applications we use (web-based) allow the users to enter data into tables that are a combination of upper and lower case.
This causes a problem when you have to start searching for data as it is case sensitive.
To mod all the application to convert the text to upper case before storing it in to the database is unviable.
Is there a way one can maybe do the conversion from a
database side (DB2) before records are inserted.

 

[BrianTyler]

Several possibilities are come to mind:

You could use a trigger to force a conversion to upper case when the data us stored / modified.

An alternative would be to change the Searches to run against a view which defines the data as upper case (This could prevent the search using some indexes).

The database could hold both an 'as-entered' version and an UPPER version of the data, though this would be a big space waster.

 

[damianp]

Thanks BrianTyler,

The first seems to b the best option. Unfortunately we are working on an iSeries box (AS/400 v5r2) I am more familiar doing something like this in Oracle on UNIX. We also need to add a field to a table to be populated via trigger entering date/time in julian date format. Could you recommend any good tutorials/reading?

 

[juliane26]

the advantage in the 3rd option of keeping an UPPER version of the data is that an index can be defined here as well and that supports search which is not case sensitive.

But triggers would definitely do the job. If you actually do not need the case sensitive data anymore, that is probaly what I would do.


Juliane

 

[ddiamond]

On DB2/UDB, the trigger would be a good option, but not on iSeries AS400. Triggers perform very slowly on the AS400, and are difficult to maintain unless you know RPG. As far as I can tell, the AS400 does not support SQL code in triggers.

 

[flapeyre]

You can define a logical file using DDS that uses a translation table to force case-insensitive searches. We use this in our customer master file and the data still shows up in mixed case, but is indexed without case sensitivity:

     A*ARCUS - CUSTOMER MASTER FILE                             
     A*                                                         
     A                                      ALTSEQ(*LIBL/ALTTBL)
     A          R CUS                       PFILE(ARCUS)        
     A*                                                         
     A          K CUSLNM                                        
     A          K CUSFNM                                        
     A          K CUSNO

The key is the ALTSEQ keyword.

We defined the ALTTBL table ourselves; but there are a bunch of tables out there in QSYS you could use (just pick the one that works with your code page).

Use the WRKTBL command to view them (and create a new one, if desired).

Me transmitte sursum, Caledoni!

 

[Mercury2]

Why don't you try the UPPER function ?
f.e. if MyField contains 'MySearch', the statement
Select ... Where UPPER(MyField) = 'MYSEARCH' (all caps)
will give the expected result.

 

[gregsimpson]

damian,

If you're not overloaded on alternatives by now, on a z/os box, I believe we could accomplish this with the use of a fieldproc for a particular field(s) or an edit proc for the whole row.

Are they available to you on AS/400?

Greg