EBCIDIC processing for ASCII data in IBM UNLOAD

Saurabh Tripathi

EBCIDIC processing for ASCII data in IBM UNLOAD

Hi,

While UNLOADING a table defined with CCSID ASCII, I see a message in IBM UNLOAD job "DSNU1044I   DSNUGTIS - PROCESSING SYSIN AS EBCDIC". I am using DB2 for z/OS V11. After the UNLOAD , the SYSRE has output data in EBCIDIC format and not ASCII. I even tried UNLOAD by adding option ASCII, still no effect on output data. Any thought what could be missing here?

Peter Vanroose

RE: EBCDIC processing for ASCII data in IBM UNLOAD
(in response to Saurabh Tripathi)

Saurabh,

"PROCESSING SYSIN AS EBCDIC" just means that the UNLOAD command statement (in your JCL) was written in EBCDIC.

On the other hand, the encoding of a table is transparent to any data processing done on it, so it's not surprising that an UNLOAD returns EBCDIC data from an ASCII table, or an SQL SELECT returns Unicode data from an EBCDIC table (when the application sending the SQL to DB2 is mentioning it uses Unicode).

Specifying the keyword ASCII on the UNLOAD command should suffice to let DB2 generate an output dataset (SYSREC) in ASCII. I've no idea why that wouldn't work. Did you check the content of the output dataset?

In Reply to Saurabh Tripathi:

While UNLOADING a table defined with CCSID ASCII, I see a message in IBM UNLOAD job "DSNU1044I   DSNUGTIS - PROCESSING SYSIN AS EBCDIC". I am using DB2 for z/OS V11. After the UNLOAD , the SYSREC has output data in EBCDIC format and not ASCII. I even tried UNLOAD by adding option ASCII, still no effect on output data. Any thought what could be missing here?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Saurabh Tripathi

RE: EBCDIC processing for ASCII data in IBM UNLOAD
(in response to Peter Vanroose)

Thanks Peter for your reply. Yes, I did check the output file, but did not find ASCII characters. 

Peter Vanroose

Re: EBCDIC processing for ASCII data in IBM UNLOAD
(in response to Saurabh Tripathi)

It seems to work for me; here is the JCL (create table + UNLOAD) and the output dataset view:

//DSNTEP4 EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M                  
//SYSTSIN DD *
DSN SYSTEM(TBD2)
RUN PROGRAM(DSNTEP4) PLAN(DSNTP410) LIB('DSNA102.RUNLIB.LOAD')
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
--#SET MAXERRORS 1
--#SET SQLFORMAT SQLPL
--
CREATE TABLE TESTJE (A INT, B CHAR(4), C VARCHAR(4)) CCSID ASCII ;
INSERT INTO TESTJE VALUES (1, 'ABCD', 'EFGH') ;
/*
//UNLOAD EXEC PGM=DSNUTILB,PARM=(TBD2,UNLD),REGION=0M
//STEPLIB DD DISP=SHR,DSN=DSNA102.SDSNLOAD
//SYSIN DD *
TEMPLATE DDREC1
DSN OUTPUT.ASCII DISP(MOD,CATLG,CATLG) UNIT SYSDA
UNLOAD DATA FROM TABLE TESTJE ASCII UNLDDN(DDREC1)
TEMPLATE DDREC2
DSN OUTPUT.EBCDIC DISP(MOD,CATLG,CATLG) UNIT SYSDA
UNLOAD DATA FROM TABLE TESTJE EBCDIC UNLDDN(DDREC2)
TEMPLATE DDREC3
DSN OUTPUT.DELIM DISP(MOD,CATLG,CATLG) UNIT SYSDA
UNLOAD DATA FROM TABLE TESTJE ASCII DELIMITED UNLDDN(DDREC3)
/*

OUTPUT.EBCDIC :

000001         ABCD   EFGH
00000000CCCC000CCCC
0300001012340045678

OUTPUT.ASCII (after "SOURCE ASCII" command in editor):

000001         ABCD   EFGH
0000000044440004444
0300001012340045678

OUTPUT.DELIM:

000001 1,"ABCD","EFGH"@@@@@@@
3224444222444424444444
1C212342C2567820000000

(I don't like the "404040..." at the end, though ...  => why not "202020..." (which are ASCII blanks) ?)

In Reply to Saurabh Tripathi:

Thanks Peter for your reply. Yes, I did check the output file, but did not find ASCII characters.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Saurabh Tripathi

Re: EBCDIC processing for ASCII data in IBM UNLOAD
(in response to Peter Vanroose)

Thanks Peter. I was not aware of ""SOURCE ASCII" command in editor". Will try it out. Also if I FTP the ASCII output to PC(using FTP in ascii mode), and then load the data into Oracle DB (defined with UTF8), data gets loaded, however a subsequent select returns "box" special characters and not equivalent English characters wherever applicable. Any Idea what could be going wrong? 

Peter Vanroose

Re: EBCDIC processing for ASCII data in IBM UNLOAD
(in response to Saurabh Tripathi)

Sorry for the late reply (holidays).

If you want to load the data into an UTF-8 encoded table, you should use the UNICODE keyword, not ASCII with your UNLOAD.

ASCII (and actually also EBCDIC) is not a single code page but rather a bunch of different ones. Only the first 128 (of the 256) entries in all ASCII code page tables are identical (and are also identical to the first 128 entries of the UFT-8 code page).  The "problematic" characters are essentially the "accented" letters like àçéèïöù (and a few more) all of which are relatively uncommon in English but rather common in some other European languages.

So, even if you would load that ASCII data into an ASCII table, there still is a risk for mis-interpreted characters.
Much more so with an UTF-8 table since now an ASCII accent letter (and the character just following it) will be interpreted as one Unicode character but since not all possible 2-byte combinations are valid UTF-8 coded Unicode characters, this would give a conversion error (hence the "box" you are observing).

So in summary: (1) use UNICODE instead of ASCII on your UNLOAD; and next (2) use BINARY transfer mode with FTP, not ASCII.

In Reply to Saurabh Tripathi:

If I FTP the ASCII output to PC (using FTP in ascii mode), and then load the data into Oracle DB (defined with UTF8), data gets loaded, however a subsequent select returns "box" special characters and not equivalent English characters wherever applicable. Any Idea what could be going wrong?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Saurabh Tripathi

Re: EBCDIC processing for ASCII data in IBM UNLOAD
(in response to Peter Vanroose)

Thanks Peter. I was able to FTP with sbd option. Used DB2 CCSID for source and ANSI for target. With this I avoided character conversion.