IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Problem in unloading numeric column.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
akumar

New User


Joined: 12 Jan 2007
Posts: 8
Location: bangalore

PostPosted: Thu Aug 23, 2007 3:53 pm
Reply with quote

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
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Thu Aug 23, 2007 4:01 pm
Reply with quote

Looks good to me, isn't INTEGER the same as binary?
Back to top
View user's profile Send private message
akumar

New User


Joined: 12 Jan 2007
Posts: 8
Location: bangalore

PostPosted: Thu Aug 23, 2007 4:24 pm
Reply with quote

I didn't able to get.can you be a little more elloborate
Back to top
View user's profile Send private message
akumar

New User


Joined: 12 Jan 2007
Posts: 8
Location: bangalore

PostPosted: Thu Aug 23, 2007 4:56 pm
Reply with quote

I didn't able to get.can you be a little more elloborate
Back to top
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Thu Aug 23, 2007 5:34 pm
Reply with quote

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

Code:
INPUT-FORMAT     E


Correct me if i am wrong

Thanks
Sai
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Aug 23, 2007 6:54 pm
Reply with quote

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.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ

PostPosted: Thu Aug 23, 2007 7:16 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Aug 23, 2007 7:41 pm
Reply with quote

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).
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Aug 23, 2007 7:48 pm
Reply with quote

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.
Back to top
View user's profile Send private message
akumar

New User


Joined: 12 Jan 2007
Posts: 8
Location: bangalore

PostPosted: Fri Aug 24, 2007 11:42 am
Reply with quote

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.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Fri Aug 24, 2007 1:02 pm
Reply with quote

akumar wrote:
it will be really helpful if someone share unload jcl using DSNTIUAL with its sysin parameters.
It will really help if you would share the unload jcl using DSNTIUAL that you are currently using......
Back to top
View user's profile Send private message
akumar

New User


Joined: 12 Jan 2007
Posts: 8
Location: bangalore

PostPosted: Fri Aug 24, 2007 4:47 pm
Reply with quote

//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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts z/vm installation problem All Other Mainframe Topics 0
No new posts Split a record with data in a differe... DFSORT/ICETOOL 8
No new posts Find a record count/numeric is multip... COBOL Programming 1
No new posts Infosphere Optim - unable to save Col... IBM Tools 0
No new posts Job scheduling problem. JCL & VSAM 9
Search our Forums:

Back to Top