Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem in unloading numeric column.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Problem in unloading numeric column.
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: 3158
Location: Tucson AZ

PostPosted: Thu Aug 23, 2007 4:01 pm    Post subject:
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    Post subject: Reply to: Problem in unloading numeric column.
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    Post subject: Reply to: Problem in unloading numeric column.
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    Post subject:
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    Post subject:
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: 3158
Location: Tucson AZ

PostPosted: Thu Aug 23, 2007 7:16 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Aug 23, 2007 7:48 pm    Post subject:
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    Post subject: Reply to: Problem in unloading numeric column.
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: 2150
Location: At my coffee table

PostPosted: Fri Aug 24, 2007 1:02 pm    Post subject: Re: Reply to: Problem in unloading numeric column.
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    Post subject: Reply to: Problem in unloading numeric column.
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us