|
View previous topic :: View next topic
|
| Author |
Message |
vamskrish
Warnings : 1
New User
Joined: 31 Jan 2007
Posts: 27
Location: virginia
|
|
|
|
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 |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Susanth,
If i understand what is needed it is to reduce the field length by the number of trailing spaces.
I may misunderstand  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
worse case you can always do an update afterwards :
update tab1 set col1 = strip(col1) |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
vamskrish
Warnings : 1
New User
Joined: 31 Jan 2007
Posts: 27
Location: virginia
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
vamskrish
Warnings : 1
New User
Joined: 31 Jan 2007
Posts: 27
Location: virginia
|
|
|
|
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 |
|
|
|
|
