International data with DB2 z/OS

Norbert Wolf

International data with DB2 z/OS
In our DB2 world we are now faced with the limits of EBCDIC. Starting with DB2 for more than 20 years we had only data that can be stored in EBCDIC tables (with nearly no conversion problems). Two decades later we have not only German or Austrian customers but also customers from all over the world and we are now faced with the limits of EBCDIC.

If you ask your Oracle or SQL-Server colleagues how they are handling different CCSIDs in one table (UNICODE and EBCDIC for example) – they will answer “just define your additional UNICODE-column and that is all”. Of course there are also many questions / problems of handling that multibyte data in the programs, accessing Oracle or SQL Server data too.

If you ask the DB2-experts how to expand an EBCDIC world into a international UNICODE world, they will tell you several limiting factors immediately:

within one table it is not possible to use different CCSIDs
it is not possible to use different CCSIDs in one spaceset

May be there are some more.

Has anyone thought about strategies to move step by step from an existing single byte character set world (20 000 tables with thousands of existing packages) into a multibyte character set world (UNICODE)? We would be happy to hear about some good ideas, like:

move to another database system
try to enrich your EBCDIC world with the help of the new EBCDIC_STR and UNICODE_STR functions


We look forward to hear about more ideas / approaches in that area

regards

Norbert Wolf

Datev eG

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Edward Long

Re: International data with DB2 z/OS
(in response to Norbert Wolf)
Good question.
We looked at this a year or so ago.

The easiest to explain and maintain is build a new system that is completely UNICODE.

Some, most or all of your char and VARCHAR columns will have to expand so you can bundle the conversion into one happy big week.

There is a terrific IDUG presentation by UPS from a couple of years back on the evolution of their packages! Sorry, had to go for the cheap pun.


Edward Long

--- On Wed, 2/9/11, Norbert Wolf <[login to unmask email]> wrote:

From: Norbert Wolf <[login to unmask email]>
Subject: [DB2-L] International data with DB2 z/OS
To: [login to unmask email]
Date: Wednesday, February 9, 2011, 11:30 AM

In our DB2 world we are now faced with the limits of EBCDIC.  Starting with DB2 for more than 20 years we had only data that can be stored in EBCDIC tables (with nearly no conversion problems). Two decades later we have not only German or Austrian customers but also customers from all over the world and we are now faced with the limits of EBCDIC.

If you ask your Oracle or SQL-Server colleagues how they are handling different CCSIDs in one table (UNICODE and EBCDIC for example) – they will answer “just define your additional UNICODE-column and that is all”. Of course there are also many questions / problems of handling that multibyte data in the programs, accessing Oracle or SQL Server data too.

If you ask the DB2-experts how to expand an EBCDIC world into a international UNICODE world, they will tell you several limiting factors immediately:

within one table it is not possible to use different CCSIDs
it is not possible to use different CCSIDs in one spaceset

May be there are some more.

Has anyone thought about strategies to move step by step from an existing single byte character set world (20 000 tables with thousands of existing packages) into a multibyte character set world (UNICODE)?  We would be happy to hear about some good ideas, like:

move  to another database system
try to enrich your EBCDIC world with the help of the new EBCDIC_STR and UNICODE_STR functions


We look forward to hear about more ideas / approaches in that area

regards

Norbert Wolf

Datev eG

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA *
*   Your only source for independent, unbiased, and trusted DB2 information.   *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jan tje

Re: International data with DB2 z/OS
(in response to Edward Long)
On Wed, 9 Feb 2011 11:30:54 -0500, Norbert Wolf <[login to unmask email]> wrote:

>If you ask your Oracle or SQL-Server colleagues how they are handling different CCSIDs in one table (UNICODE and EBCDIC for example) – they will answer “just define your additional UNICODE-column and that is all”. Of course there are also many questions / problems of handling that multibyte data in the programs, accessing Oracle or SQL Server data too.
>
IMHO your Oracle and SQL-Server colleagues are underestimating their difficulties... You write it yourself: "... problems of handling that multibyte data in the programs..." I'am convinced that that is the real pain.

>
>(20 000 tables with thousands of existing packages) into a multibyte character set world (UNICODE)?
>
Don't overestimate your trouble.
I suggest you start with a survey of your data. You need Unicode for names of people and for the names of the streets where they live. You don't need Unicode for part numbers, amounts, code tables and the like. You'll see: less than 1 % of all your data actually needs it.
Sure, you will need to convert some of your tables, but certainly not all.

Anyway, the real sting is in the application code, not at the database level.

>
>move to another database system
>
Don't. You'll regret it.

>try to enrich your EBCDIC world with the help of the new EBCDIC_STR and UNICODE_STR functions
>
Worth looking into. They can solve at least some of your issues.


I am sure others will have lots of suggestions of how to actually proceed and what the pitfalls are. The point I wanted to make is that it is not as bad as it looks.

Cheers,

Jantje.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Max Scarpa

Re: International data with DB2 z/OS
(in response to Jan tje)
Hi all

I never migrated (or I heard someone who migrated) to a 'pure' UNICODE DB2
environment, but I'm becoming old so don't trust me too much for that.

Your situation actually should be relatively common and at least was
expected by IBM and it's (if I understood it correctly, of course)
described in chapter 6 'Multiple CCSIDs in a single table' in V8 redboot
'Everything you ......' There is depicted a possible solution and there
it's stated that 'the need for converting to UNICODE your application data
is a mith', coming directly from Evil Special Forces as there's no need at
all to do it. In this book there are many suggestions about, if really
needed, gradual migration from EBCDIC to UNICODE, about application and so
on. As suggested by other listers check your data to see how big is the
problem, but I think it's limited to some 'departments' or agencies in
other country and they could be solved via applications modification.
But's it's only an opinion based of the few data I found in a past
company.

See also:

Unicode Implementations in DB2: Performance Study by
Konstantin Tadenev IDUG 2009
Unicode experiences with DB2 by
Jan Tielemans
All for One and One for All -Introducing Unicode (Part 1 & 2)

In short moving to a pure UNICODE DB2 for z/OS environment simply thinking
LUW people has no problems seems to me a too drastic decision, if taken.


Just an idea

Max Scarpa


IDUG DB2-L <[login to unmask email]> wrote on 09/02/2011 17.30.54:

> From: Norbert Wolf <[login to unmask email]>
> To: [login to unmask email]
> Date: 09/02/2011 17.31
> Subject: [DB2-L] International data with DB2 z/OS
> Sent by: IDUG DB2-L <[login to unmask email]>
>
> In our DB2 world we are now faced with the limits of EBCDIC.
> Starting with DB2 for more than 20 years we had only data that can
> be stored in EBCDIC tables (with nearly no conversion problems). Two
> decades later we have not only German or Austrian customers but also
> customers from all over the world and we are now faced with the
> limits of EBCDIC.
>
> If you ask your Oracle or SQL-Server colleagues how they are
> handling different CCSIDs in one table (UNICODE and EBCDIC for
> example) – they will answer “just define your additional UNICODE-
> column and that is all”. Of course there are also many questions /
> problems of handling that multibyte data in the programs, accessing
> Oracle or SQL Server data too.
>
> If you ask the DB2-experts how to expand an EBCDIC world into a
> international UNICODE world, they will tell you several limiting
> factors immediately:
>
> within one table it is not possible to use different CCSIDs
> it is not possible to use different CCSIDs in one spaceset
>
> May be there are some more.
>
> Has anyone thought about strategies to move step by step from an
> existing single byte character set world (20 000 tables with
> thousands of existing packages) into a multibyte character set world
> (UNICODE)? We would be happy to hear about some good ideas, like:
>
> move to another database system
> try to enrich your EBCDIC world with the help of the new EBCDIC_STR
> and UNICODE_STR functions
>
>
> We look forward to hear about more ideas / approaches in that area
>
> regards
>
> Norbert Wolf
>
> Datev eG
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 * http://
> IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2
> information. *
> _____________________________________________________________________
> http://www.IDUG.org/mentor
> Mentoring should be a rewarding experience for everyone...
> IDUG is offering up to 80% off when you both come to the conference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
> is the home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv