Stored data in upper case

Sibimon Philip

Stored data in upper case
Is there any character set or any other thing allow db2 to store the data
always in upper case in a table. We are on DB2 version 6 on OS/390. Shortly
going to version 7. There are data exist in upper and lower case, but we
want to covert all the data to upper case and stored. Any new data should be
stored in upper case always. Is there any way can we achieve this in DB2.



Thanks..sibi



Eric Pearson

Re: Stored data in upper case
(in response to Sibimon Philip)
Change all the SQL for this table from:
INSERT INTO table-name col1, col2,.... VALUES (:hv1, :hv2, ...)
to:
INSERT INTO table-name col1, col2,.... VALUES (UPPER(:hv1), UPPER(:hv2),
....)
and do the same with the UPDATE statements.

You could encapsulate the INSERTs and UPDATEs in a Stored Procedure and
require that all INSERTs and UPDATEs to this table use the Stored Procedure.
This could reduce the coding effort and prevent future 'surprises'.

To convert the old data you could unload it with DSNTIAUL or
possibly one of the OEM unloads with
a clause like:
SELECT UPPER(col1), UPPER(col2), ....
and then pipe the output of DSNTIAUL into a load.


Regards,
Eric Pearson



-----Original Message-----
From: Philip, Sibimon [mailto:[login to unmask email]
Sent: Monday, December 30, 2002 11:20 AM
To: [login to unmask email]
Subject: Stored data in upper case


Is there any character set or any other thing allow db2 to store the data
always in upper case in a table. We are on DB2 version 6 on OS/390. Shortly
going to version 7. There are data exist in upper and lower case, but we
want to covert all the data to upper case and stored. Any new data should be
stored in upper case always. Is there any way can we achieve this in DB2.



Thanks..sibi








Terry Purcell

Re: Stored data in upper case
(in response to Eric Pearson)
Sibi,

For the data conversion, you can use Eric's suggestion if logging (or
compression) is an issue because you can perform a LOAD REPLACE LOG NO.

If logging is not an issue then a simple UPDATE statement could be
constructed that will convert all existing data such as:

UPDATE MYTABLE
SET COL1 = UPPER(COL1)
,COL2 = UPPER(COL2)
,COL3 = UPPER(COL3)

Otherwise.....what Eric said.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Pearson, Eric L,
Sent: Monday, December 30, 2002 10:53 AM
To: [login to unmask email]
Subject: Re: Stored data in upper case


Change all the SQL for this table from:
INSERT INTO table-name col1, col2,.... VALUES (:hv1, :hv2, ...)
to:
INSERT INTO table-name col1, col2,.... VALUES (UPPER(:hv1), UPPER(:hv2),
....)
and do the same with the UPDATE statements.

You could encapsulate the INSERTs and UPDATEs in a Stored Procedure and
require that all INSERTs and UPDATEs to this table use the Stored Procedure.
This could reduce the coding effort and prevent future 'surprises'.

To convert the old data you could unload it with DSNTIAUL or
possibly one of the OEM unloads with
a clause like:
SELECT UPPER(col1), UPPER(col2), ....
and then pipe the output of DSNTIAUL into a load.


Regards,
Eric Pearson



-----Original Message-----
From: Philip, Sibimon [mailto:[login to unmask email]
Sent: Monday, December 30, 2002 11:20 AM
To: [login to unmask email]
Subject: Stored data in upper case


Is there any character set or any other thing allow db2 to store the data
always in upper case in a table. We are on DB2 version 6 on OS/390. Shortly
going to version 7. There are data exist in upper and lower case, but we
want to covert all the data to upper case and stored. Any new data should be
stored in upper case always. Is there any way can we achieve this in DB2.



Thanks..sibi



Eric Pearson

Re: Stored data in upper case
(in response to Terry Purcell)
Logging and locking were my reasons for LOAD rather than
UPDATE. I can just imagine an UPDATE of a squillion row
table getting cancelled at 1/2 squillion rows and going
into ABORT for the rest of the programmer's brief
remaining job tenure!

Regards,
Eric Pearson



-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Monday, December 30, 2002 12:16 PM
To: [login to unmask email]
Subject: Re: Stored data in upper case


Sibi,

For the data conversion, you can use Eric's suggestion if logging (or
compression) is an issue because you can perform a LOAD REPLACE LOG NO.

If logging is not an issue then a simple UPDATE statement could be
constructed that will convert all existing data such as:

UPDATE MYTABLE
SET COL1 = UPPER(COL1)
,COL2 = UPPER(COL2)
,COL3 = UPPER(COL3)

Otherwise.....what Eric said.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Pearson, Eric L,
Sent: Monday, December 30, 2002 10:53 AM
To: [login to unmask email]
Subject: Re: Stored data in upper case


Change all the SQL for this table from:
INSERT INTO table-name col1, col2,.... VALUES (:hv1, :hv2, ...)
to:
INSERT INTO table-name col1, col2,.... VALUES (UPPER(:hv1), UPPER(:hv2),
....)
and do the same with the UPDATE statements.

You could encapsulate the INSERTs and UPDATEs in a Stored Procedure and
require that all INSERTs and UPDATEs to this table use the Stored Procedure.
This could reduce the coding effort and prevent future 'surprises'.

To convert the old data you could unload it with DSNTIAUL or
possibly one of the OEM unloads with
a clause like:
SELECT UPPER(col1), UPPER(col2), ....
and then pipe the output of DSNTIAUL into a load.


Regards,
Eric Pearson



-----Original Message-----
From: Philip, Sibimon [mailto:[login to unmask email]
Sent: Monday, December 30, 2002 11:20 AM
To: [login to unmask email]
Subject: Stored data in upper case


Is there any character set or any other thing allow db2 to store the data
always in upper case in a table. We are on DB2 version 6 on OS/390. Shortly
going to version 7. There are data exist in upper and lower case, but we
want to covert all the data to upper case and stored. Any new data should be
stored in upper case always. Is there any way can we achieve this in DB2.



Thanks..sibi








David Cohn

Re: Stored data in upper case
(in response to Eric Pearson)
Hi Sibi,
You're pretty much limited to using the UCASE or UPPER function. The
synatax is
UCASE('character string or column with lower case characters')
To change existing data you can use the SQL UPDATE statement. To insure that
future data is all upper case you would best be served by writing a BEFORE
TRIGGER to force the character columns to upper case.
Hope this helps,
David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "Philip, Sibimon" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 30, 2002 8:20 AM
Subject: Stored data in upper case


> Is there any character set or any other thing allow db2 to store the data
> always in upper case in a table. We are on DB2 version 6 on OS/390.
Shortly
> going to version 7. There are data exist in upper and lower case, but we
> want to covert all the data to upper case and stored. Any new data should
be
> stored in upper case always. Is there any way can we achieve this in DB2.
>
>
>
> Thanks..sibi
>
>
>


>