Problem in unloading numeric column.

akumar · New User Joined: 12 Jan 2007 Posts: 8 Location: bangalore

Hi all,
I am using DSANTIAUL to unload data from a particular table.the problem is that after unloading the numeric data are displayed in a compressed format,but character data are displayed correctly.I am not clear why the numeric data is displayed in the compressed format.I also tried using DSNUPROC but the same result is coming.my project reqiurement is i need to unload in readble format.please help me in this issue.

Actual data in the table

000001 krishan jalandar 987680990
000002 venkat bangalore 998789998
000003 pradeep chennai 989898877
000004 arun hyderabad 998673979
000005 arun13 palkad 998788987

Data in the output file after running the unload job

000001 krishan jalandar Ãº}Ãº
000002 venkat bangalore hÃ«>
000003 pradeep chennai y'
000004 arun hyderabad fÃ¾
000005 arun13 palkad h|#

Syspunch file

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE
G50168.TESTATB2
(
NAME POSITION( 1 )
CHAR( 15) ,
CITY POSITION( 16 )
CHAR( 15) ,
PHONE POSITION( 31 )
INTEGER
)

Thanks,
Arul

William Thompson · Posted: Thu Aug 23, 2007 4:01 pm

Looks good to me, isn't INTEGER the same as binary?

akumar · New User Joined: 12 Jan 2007 Posts: 8 Location: bangalore

I didn't able to get.can you be a little more elloborate

akumar · New User Joined: 12 Jan 2007 Posts: 8 Location: bangalore

I didn't able to get.can you be a little more elloborate

saiprasadh · Active User Joined: 20 Sep 2006 Posts: 154 Location: US

Hi Kumar,
Since the column is declared as INTEGER , while unloading it will be in compressed format only, You can see the value by opening the dataset in hex mode Or you can unload the data in the External mode.

(i,e) you have to specify in the Unload card as

stodolas · Posted: Thu Aug 23, 2007 6:54 pm

William, I thought DB2 internally stored all numeric as packed or binary of some sort. The Unload pulls it out like this, unreadable unless you look at the hex. I don't know if saiprasadh's solution will work.

William Thompson · Posted: Thu Aug 23, 2007 7:16 pm

If you really want the PHONE to be displayable after the unload, add something like "DECIMAL EXTERNAL" after the PHONE selection......

I saw this in the section "Specifying output field types" in the chapter on UNLOAD in the OSUtility Guide and Reference.

dbzTHEdinosauer · Posted: Thu Aug 23, 2007 7:41 pm

there is a big difference in the meaning of the words;

compressed - a compression algorithm has been applied and duplicate/common byte configurations have been replaced with a code, that will expand to original byte configuration when decompressed by the compression processor - e.g.zip
decimal - (packed-decimal) base 10 characters (0-9) high-order (8-5) bits are dropped and two sets of low-order (4-1) bits are combined in one byte
binary or hex - value is in base 16 (0-F).

dick scherrer · Posted: Thu Aug 23, 2007 7:48 pm

Hello,

You asked for PHONE to be unloaded in the internal (more efficient) format.

If you are going to load this into another table or use it in some code, the format is correct and will work (again more effeciently).

If there is some reason that you must be able to "see" the numbers in the file, then you need to do as suggested and expand PHONE to an external format.

akumar · New User Joined: 12 Jan 2007 Posts: 8 Location: bangalore

Hi all,
thanks for your answers.i tried the options and its unloading in the readable format when i used DSNUPROC.but while using DSNTIUAL the same problem comes.it will be really helpful if someone share unload jcl using DSNTIUAL with its sysin parameters.

CICS Guy · Posted: Fri Aug 24, 2007 1:02 pm

akumar · New User Joined: 12 Jan 2007 Posts: 8 Location: bangalore

//UNLOAD1 JOB ,,NOTIFY=&SYSUID,CLASS=B,MSGLEVEL=(1,1),MSGCLASS=X
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=XIEW.SYSREC.PACQT9,
// SPACE=(CYL,(5,5),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=30000,DSORG=PS),
// DISP=(NEW,CATLG)
//SYSPUNCH DD DSN=XIEW.SYSPUNP9,
// SPACE=(CYL,(5,5),RLSE),
// DCB=(LRECL=80,RECFM=FB,BLKSIZE=800,DSORG=PS),
// DISP=(NEW,CATLG)
//SYSIN DD *
G50168.TESTATB2
/*

THIS JCL UNLOADS THE INTEGER COLUMN IN COMPRESSED FORMAT

If i replace the sysin like
//SYSIN DD *
SELECT NAME,CITY,DIGITS(PHONE) PHONE FROM G50168.TESTATB2
/*
it showing error message as SQL ERROR DURING SQL STATEMENT PREPARE , TABLE SELECT NAME,CITY,DIG
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "<EMPTY>". SOME SYMBOLS THAT MIG

did iam using the correct format.