AS/400 Case Sensitivity?

[DBohner-(db)]

Over the weekend we moved from Intel/SQL 7.0 environments to an AS/400.
(this is not a discussion of which platform is better, please)

There was a lot of prep work laid out ~ some very long hours ~ but the
transfer has gone pretty fluid for our implementation staff (we are not
live, but preparing for go-live during the summer and fall ~ we are at
configuration & testing)... BIG KUDOS for our internal staff reading the
list, as well as toward our IBM and JDE consultants (job well done!).
Sincerely, for any of those reading the list that worked on the J.R. Simplot
migration to 400 ~ BIG THANKS!

<Slight deviation from praise inserted here>
Almost immediately some of our users showed enormous concern. In the MS SQL
environments we had Case-In-Sensitivity. On the AS/400 we have Case
Sensitivity...

I realize the reasons for Case ~ I am a programmer type and do see the world
in black and white... I'd prefer a world of black and white... But my job
is to make my users happy (spelt with a smile). <outside the six walls of
my cubicle I am a photographer ~ I see the world in 63 million colors when I
am permitted to escape>

There are lots of ways within JDE to make the database appear
Case-In-Sensitive. For those that have faced this issue(winners & losers),
can you provide any recommendations? Experiences? Suggestions?

So, how do I make my users happy with Case-Sensitivity?

Daniel Bohner
[email protected]
www.existinglight.net

 

[franck_blettner]

I had this problem before, but it was just a service pack. Do you have the
same service pack on your AS400 ?

-----Message d'origine-----
De : [email protected]
[mailto[email protected]]De la part de DBohner
Envoyé : lundi 9 avril 2001 18:41
À : [email protected]
Objet : AS/400 Case Sensitivity?


Over the weekend we moved from Intel/SQL 7.0 environments to an AS/400.
(this is not a discussion of which platform is better, please)

There was a lot of prep work laid out ~ some very long hours ~ but the
transfer has gone pretty fluid for our implementation staff (we are not
live, but preparing for go-live during the summer and fall ~ we are at
configuration & testing)... BIG KUDOS for our internal staff reading the
list, as well as toward our IBM and JDE consultants (job well done!).
Sincerely, for any of those reading the list that worked on the J.R. Simplot
migration to 400 ~ BIG THANKS!

<Slight deviation from praise inserted here>
Almost immediately some of our users showed enormous concern. In the MS SQL
environments we had Case-In-Sensitivity. On the AS/400 we have Case
Sensitivity...

I realize the reasons for Case ~ I am a programmer type and do see the world
in black and white... I'd prefer a world of black and white... But my job
is to make my users happy (spelt with a smile). <outside the six walls of
my cubicle I am a photographer ~ I see the world in 63 million colors when I
am permitted to escape>

There are lots of ways within JDE to make the database appear
Case-In-Sensitive. For those that have faced this issue(winners & losers),
can you provide any recommendations? Experiences? Suggestions?

So, how do I make my users happy with Case-Sensitivity?

Daniel Bohner
[email protected]
www.existinglight.net




--------------------------


Franck BLETTNER
Service Informatique RAVATE
Tel : 02 62 90 40 48
E-mail : [email protected]

 

[DBohner-(db)]

Frank,

This doesn't seem to be a service pack issue ~ but I could be wrong. The
AS/400's DB/2 is case sensitive by default...

When we were running on MS SQL 7 our QBE lines were case in-sensitive. We
moved to DB/2-400 and our QBE lines were suddenly case sensitive... If it
can be quickly done, the turning of a bit..., we'd like to have QBE lines on
JDE/XE over DB/2-400 that are case in-sensitive ~ like those we had
experienced on JDE/XE over MS SQL 7.

Takers, suggestions, recommendations... we are all ears!

Daniel Bohner
[email protected]
www.existinglight.net

- and -

Daniel Bohner
Simplot Corporate Information Systems
Pioneer Building
Boise, ID
(208)332-0613 desk phone
(208)250-1917 personal cell
[email protected]

 

[Zoltan_Gyimesi]

Daniel,
This post not a solution for your issue, just a goodwill warning.
If you turn a case-sensitive database into a case-insensitive state then
it could cause violations on the unique indices and crash the logical
structure of your db.
Although this is obvious, here is an example:
* the name field is unique on a table and links a lot of other tables
too
* There are two records in this table with the values in the mentioned
field:
** Little John Jr.
** little john jr.
* and they has several linked child records in other tables
* If you change the case sensitivity then the two value will violate and
the system won't be able to evaluate the links too
* etc.

I really hope that you will get the solution for your problem in the
near future, so I strongly recommend you that make a backup on your
database before you apply the solution (supposly you will do it without
my notice too ;-)

Good luck,
Zoltán
P.S.: I know very well how do customers hates case-sensitive QBE )




B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[TAdams]

Daniel,

Have you thought about collating sequence. I know in DDS you can define an alternate collating sequence to ensure fields in upper and lower case are sorted without case sensitivity.

Look in the IBM manual 'DB2 UDB for AS/400 SQL Programming V4R4' and you will see reference to shared weight and unique weight sort sequences specified by SRTSEQ(*LANGIDUNQ) and LANGID(ENU) - around page 50.

You may need to re-create the necessary indices on the 400 rather than through JDE but it may work.

Good luck.
Tim


Sorry Zoltan I'll try and figure out that E-mail signature thing.




Frank,

This doesn't seem to be a service pack issue ~ but I could be wrong. The
AS/400's DB/2 is case sensitive by default...

When we were running on MS SQL 7 our QBE lines were case in-sensitive. We
moved to DB/2-400 and our QBE lines were suddenly case sensitive... If it
can be quickly done, the turning of a bit..., we'd like to have QBE lines on
JDE/XE over DB/2-400 that are case in-sensitive ~ like those we had
experienced on JDE/XE over MS SQL 7.

Takers, suggestions, recommendations... we are all ears!

Daniel Bohner
[email protected]
www.existinglight.net

- and -

Daniel Bohner
Simplot Corporate Information Systems
Pioneer Building
Boise, ID
(208)332-0613 desk phone
(208)250-1917 personal cell
[email protected]




--------------------------

 

[Terry B]

Re: RE: AS/400 Case Sensitivity?

Hi All

When a user searches for a description using QBE, we would like the search to be case insensitive. I know, by default, the AS400 SQL is case sensitive but is there a way to disable this?
It doesn't have to be system wide, but at table level or field level might keep the Users happy.
Any help is very much appreciated.

Regards,
Terry