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

Unloading Data from DECIMAL(n,0) Columns using DSNUTILB


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

New User


Joined: 31 Jan 2022
Posts: 2
Location: Germany

PostPosted: Wed Mar 09, 2022 1:13 am
Reply with quote

Hello All,

Can someone help me with the below issue -
While unloading data from DB2 tables using DSNUTILB utility, in case of COLUMNS with datatype DECIMAL(n, 0), there comes an extra '.' in the unloaded data.

How can we get rid of this extra '.'

Unload JCL -
//STEP1 EXEC PGM=DSNUTILB,PARM='TDB1'
//STEPLIB DD DSN='TEST.SDSNLIB',DISP=SHR
//SYSREC DD DSN=TEST.INPUT.TABLE1,
// DISP=(NEW,CATLG,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(2,1))
//SYSPUNCH DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//UTPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
UNLOAD DATA
FROM TABLE TABLE1
DELIMITED CHARDEL '"' COLDEL ','



Table Defination-
COL1 DECIMAL (1,0),
COL2 CHAR (3)


Current Unload -
1.,"ABC"
2.,"DEF"
3.,"GHI"


Expected Unload -
1,"ABC"
2,"DEF"
3,"GHI"
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Wed Mar 09, 2022 1:46 am
Reply with quote

Code:
 ... FINDREP=(INOUT=(C'.,',C','))
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1231
Location: Bamberg, Germany

PostPosted: Wed Mar 09, 2022 2:32 am
Reply with quote

sergeyken wrote:
Code:
 ... FINDREP=(INOUT=(C'.,',C','))

You perhaps should add to use SORT for that.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Wed Mar 09, 2022 2:46 am
Reply with quote

Joerg.Findeisen wrote:
sergeyken wrote:
Code:
 ... FINDREP=(INOUT=(C'.,',C','))

You perhaps should add to use SORT for that.

Furthermore: also //SYSOUT DD must be added, too!!
And: //SYSIN DD *
icon_biggrin.gif
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2012
Location: USA

PostPosted: Thu Mar 10, 2022 12:16 am
Reply with quote

No reaction from TS (as usually...)
Keine Reaktion vom Themenstarter (wie üblich..)

Code:

//*================================================
//* Unload the table temporary as per DSNUTILB rules
//*================================================
//UNLOAD  EXEC PGM=DSNUTILB,PARM='TDB1'
//STEPLIB  DD  DSN=TEST.SDSNLIB,DISP=SHR
//SYSREC   DD  DSN=&&TEMPTAB,
//             DISP=(NEW,PASS),
//             UNIT=SYSDA,SPACE=(TRK,(2,1))
//SYSPUNCH DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//UTPRINT  DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSIN    DD  *
 UNLOAD DATA
 FROM TABLE TABLE1
 DELIMITED CHARDEL '"' COLDEL ','
//*
//*================================================
//* Fix (all) undesired formats in the intermediate dataset
//*================================================
//FIXDOTS EXEC PGM=SORT,COND=(0,NE,UNLOAD)
//SYSOUT   DD  SYSOUT=*
//SORTIN   DD  DSN=&&TEMPTAB,
//             DISP=(OLD,DELETE)
//SORTOUT  DD  DSN=TEST.INPUT.TABLE1,
//             DISP=(NEW,CATLG,CATLG),
//             UNIT=SYSDA,SPACE=(TRK,(2,1))
//SYSIN    DD  *
 INREC FINDREP=(INOUT=(C'.,',C','))
 SORT FIELDS=COPY
 END
//*
//*================================================
Back to top
View user's profile Send private message
prathmone

New User


Joined: 31 Jan 2022
Posts: 2
Location: Germany

PostPosted: Sat Apr 16, 2022 1:45 am
Reply with quote

Ganz Herzlichen Dank sergeyken und Jörg.Findeisen !
Entschuldigung für die späte Antwort.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Tue Apr 26, 2022 1:31 am
Reply with quote

Can you try DIGITS(col1) in your unload statement

Untested
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 Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top