DB2 V8 EBCDIC to UNICODE conversion question

Georg Peter

DB2 V8 EBCDIC to UNICODE conversion question
Environment: DB2 z/OS V8

Fellow Collegues,

we intend to do an EBCDIC to UNICODE conversion in one of our applications (207 tables).

A first test - without expanding the CHAR column formats - was not successful - here is the scenario:

We did an UNLOAD on the existing EBCDIC table and we got a sequential file and a LOAD Statement like this

LOAD DATA INDDN SYSREC LOG NO RESUME YES
EBCDIC CCSID(01141,00000,00000)
INTO TABLE "LEWISU "."DEW02 "
WHEN(00001:00002) = X'016A'
( "DEW02_AGS " POSITION( 00003:00011) CHAR(00009)
........

We edited LOG NO into LOG YES and we changed the table owner from LEWISU into LEWISX (the tables behind this owner are with CCSID UNICODE).

Then we run the LOAD Utility of version 8 - and now the story begins.......

If there is a column with a german Umlaut (Ä,Ü,Ö, ä,ü,ö) DB2 converts this Umlaut into a 2-Byte-Value. And when DB2 wants to do the LOAD, the length of the specific CHAR-column is to long now - the LOAD utility ends with an DSNU334I, ERROR CODE '19'.

While examining the column we saw that the column value looks like this:

.ö08999149 .l.ø.....< 2004-02-11-08.32.27.345868HUTTER JBrüssel

and in HEX-Mode like this

JBrüssel
DC9DAA894444444444444444 etc.
129022530000000000000000 etc.

And now my question:

Is there a way to tell the LOAD "okay my dear utility if you

- have expanded a length (because of a converted 2-byte-value) a n d
- you find more than two blanks in sequence to the right site of this column (e.g.

4444444444444444
0000000000000000

please ignore one blank for every value that was converted into two byte in this column....."

Okay, I know that sounds funny - but may be there is a good solution on this ????

Thanks in advance for any advice you may be able to provide.

With kind regards - mit freundlichen Gruessen,
G e o r g H . P e t e r
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Development and Product Support (E3)
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
»Never say "Oops?" Always say "Ah, interesting!"«

Abonnieren Sie den monatlichen Infobrief der Datenzentrale Baden-Württemberg und erfahren Sie regelmäßig die neuesten Nachrichten über aktuelle Projekte und Entwicklungen. Melden Sie sich an mit diesem Link http://www.datenzentrale.de/Info-Brief
_______________________________________________________________________________

Datenzentrale Baden-Württemberg, Anstalt des öffentlichen Rechts
Krailenshaldenstr. 44, 70469 Stuttgart
Telefon (0711) 8108-0, Telefax (0711) 8108-350
E-Mail [login to unmask email], Internet www.datenzentrale.de
Vorstand: Karl Tramer (Vors.) und Harald Schätzle, Vorsitzender des Verwaltungsrats: Gunter Czisch
USt-Id-Nr. DE147794223
_______________________________________________________________________________

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Vanroose

Re: DB2 V8 EBCDIC to UNICODE conversion question
(in response to Georg Peter)
I'd suggest you do the UNLOAD with "DELIMITED UNICODE",
that will probably avoid the problem at LOAD;
if not, just edit the data by removing trailing blanks (which should be
fairly simple with the DELIMITED format) then LOAD again.

-- Peter Vanroose
ABIS Training & Consulting



















__________________________________________________________
Låna pengar utan säkerhet. Jämför vilkor online hos Yahoo!
http://shopping.yahoo.se/c-100390123-lan-utan-sakerhet.html?partnerId=96915014

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: DB2 V8 EBCDIC to UNICODE conversion question
(in response to Peter Vanroose)
Hello Georg

I don't know, if there is such a utility, but I think, there will remain a problem.
What do you do, if there is an EBCDIC-Character-String, which has the
maximum value for the EBCDIC-column and contains German umlaute?

I cannot imagine, that there is a tool, ahich kwows, that trailing blanks can be
truncated.

Another option would be, to define those columns VARCHAR, then I think the
load will work.

P.S. I just get the mail fron Darius, that you have to retire from the DB2 Guide.
That's really a pity.

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm