Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DSNUTILB - loading char field into varchar field

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vamskrish
Warnings : 1

New User


Joined: 31 Jan 2007
Posts: 27
Location: virginia

PostPosted: Wed Jul 07, 2010 5:53 am    Post subject: DSNUTILB - loading char field into varchar field
Reply with quote

hi,
i have never used db2 laod utility like DSNUTILIB or any other.
i have a incoming file of fixed length of 32 bytes(this is only one field). the data in it is right padded with spaces for all the values whose length is less than 32 bytes.

here is an example of input file with a field lets say Last Name:
---------------------------------------------
12345678901234567890123456789012
lineone
lineonehundred
linethreethousandfourtysix
thislinehasthirtytwocharacterstoloadnow
---------------------------------------------

i need to load this data into my db2 table on MVS where the table column is defined as varchar(32). i have reviewed some posts and understand that we need a binary length field before the field. Is it must to have the binary length field? Cant we do it without having that field. i am getting this file from another system and another team and they say they cant put a binary length field. What are my options? are there any options i can use in my LOAD statement that converts CHAR to VARCHAR?

Appreciate your prompt reply.

thanks
krish
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: Wed Jul 07, 2010 7:48 am    Post subject:
Reply with quote

Hello,

One way to do this is Copy the file appending the length to each record. . .

Use any utility you know and are comfortable with or write a bit of code.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 07, 2010 9:07 am    Post subject:
Reply with quote

Krish,

Quote:
i am getting this file from another system and another team and they say they cant put a binary length field. What are my options? are there any options i can use in my LOAD statement that converts CHAR to VARCHAR?


I think you don't need length field and you also don't need to convert into varchar.

Try loading with the below field positions. Not Tested.
Code:

column-name POSITION (1:32) CHAR(32),


Sushanth
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: Wed Jul 07, 2010 9:21 am    Post subject:
Reply with quote

Hi Susanth,

If i understand what is needed it is to reduce the field length by the number of trailing spaces.

I may misunderstand icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jul 07, 2010 11:46 am    Post subject:
Reply with quote

worse case you can always do an update afterwards :
update tab1 set col1 = strip(col1)
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 07, 2010 10:22 pm    Post subject:
Reply with quote

Hi,

Strip functionality in LOAD can be used to remove all the trailing and leading spaces,

Code:
DUMMY_TEXT POSITION (1:32) CHAR(32) STRIP BOTH 



Thanks,
Sushanth
Back to top
View user's profile Send private message
vamskrish
Warnings : 1

New User


Joined: 31 Jan 2007
Posts: 27
Location: virginia

PostPosted: Thu Jul 08, 2010 6:58 pm    Post subject:
Reply with quote

hi all,
thanks a lot for your solutions. the 'strip' worked.

COLUMN1 POSITION(1:32) CHARACTER STRIP TRAILING ' '

i think it would be the same as what sushanth had suggested.


thanks again
krish
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jul 08, 2010 7:06 pm    Post subject:
Reply with quote

vamskrish wrote:
hi all,
i think it would be the same as what sushanth had suggested.


no,
if there are leading spaces, they have been included in the varchar.
Back to top
View user's profile Send private message
vamskrish
Warnings : 1

New User


Joined: 31 Jan 2007
Posts: 27
Location: virginia

PostPosted: Fri Jul 09, 2010 8:12 pm    Post subject:
Reply with quote

yes, because i am using 'Trailing'. what i meant was they both work depending upon how u want to handle your spaces.

thanks
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 Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm
No new posts Remove leading spaces from numeric field rexx77 SYNCSORT 6 Wed Sep 06, 2017 2:15 am
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us