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

Varchar lengths in DFSORT


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 57
Location: Amsterdam

PostPosted: Thu Nov 03, 2011 6:05 pm
Reply with quote

Hi all,

I have a CSV VB file which I need to load into a DB2 table, my idea was to use DFSORT PARSE and BUILD to achieve this.

I have created some Sort steps to

a) PARSE the VB file & BUILD a FB file using %nn etc
b) Some Overlays to correct dates etc.
c) DB2 Load

This all works fine.

BUT I have a problem with VARCHAR fields/columns.

Some of our tables have many VARCHAR(256) columns, the DB2 load syspunch cards are expecting the VARCHAR to start at a certain position which is the column length.

Now I can assign the MAX to that position fine, but this is going to make the DB storage enormous as most of the VARCHAR(256) contain less than 10 characters.

I want to find a way to retrieve the LENGTH of the VARCHAR field during the PARSE and insert that length into the file to be LOADED to DB2.

(There may be a better way to load a CSV file into DB2 and I will post a Q on the DB2 forum)

e.g.
Input file
Code:
ABC,QQQ,T


When the first and second fields are VARCHAR(10) I would expect the output to be like this

Code:
??1234567890??1234567890
..ABC       ..QQQ       T


Where the ?? on both VARCHAR fields is set to x'0003'

Does anyone have any bright ideas. icon_rolleyes.gif

Daniel
Back to top
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Thu Nov 03, 2011 7:44 pm
Reply with quote

Hi Daniel,

You can use below mentioned load card to load CSV file data to table.

Code:
 //SYSIN    DD *                                                 
    LOAD DATA                                                   
          FORMAT DELIMITED COLDEL ',' CHARDEL '"' DECPT '.'                 
          INTO TABLE TBQB0103                                               
              (FILENO   CHAR,                                               
               DATE1    DATE EXTERNAL,                                       
               TIME1    TIME EXTERNAL,                                       
               TIMESTMP TIMESTAMP EXTERNAL)                                 
   /*                                                                       
  //SYSREC   DD *                                                           
   "001", 2000-02-16, 00.00.00, 2000-02-16-00.00.00.0000                     
   "002", 2001-04-17, 06.30.00, 2001-04-17-06.30.00.2000                     
   "003", 2002-06-18, 12.30.59, 2002-06-18-12.30.59.4000                     
   "004", 1991-08-19, 18.59.30, 1991-08-19-18.59.30.8000                     
   "005", 2000-12-20, 24.00.00, 2000-12-20-24.00.00.0000                     
   /*                 


Note : Moderator since this post is not created in Db2 forum i am providing the solution here.


Sai
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 57
Location: Amsterdam

PostPosted: Thu Nov 03, 2011 7:47 pm
Reply with quote

Well I found the http://ibmmainframes.com/viewtopic.php?t=29307&highlight=variable

which uses the solution

Code:
IFTHEN=(WHEN=(6,1,CH,EQ,C' '),OVERLAY=(3:X'0001')),
etc

we have up to 11 VARCHAR(256) in our files making this a bit unmanageable.

I was hoping for another way.
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 57
Location: Amsterdam

PostPosted: Thu Nov 03, 2011 7:48 pm
Reply with quote

Thanks saiprasadh,

that looks interestering.

Dan.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Nov 03, 2011 7:56 pm
Reply with quote

Daniel,
I apologize,
your exact question has not been previously asked

what you want is exactly what asked for.

now, have you experimented with a few (2 or 3) rows to insure that the varchar(10) with a preceding length will properly load?

if so, then you have a map - the syspunch.

from that you can use parse to build the records. (as you have probably been able to do).

what i see is that you need help determining the 'non space count' of each field and have that inserted as binary in the preceding 2 char.

if so, that is the sort solution that you are looking for.

at this point, i imagine you can provide the starting and ending column of each field, and where the length has to be inserted/overlayed.

provide that map / definition of input file and i am sure someone can provide the control cards necessary. from that you can build and work on the rows of other tables.

thx, dbz
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 57
Location: Amsterdam

PostPosted: Fri Nov 04, 2011 12:18 pm
Reply with quote

Thanks guys, the LOAD stmt works brilliantly.

Dan. icon_biggrin.gif
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Fri Nov 04, 2011 9:01 pm
Reply with quote

Daniel,
What utility do you use to load, db2 table?

Reason I am asking is because there is an option to load CSV file directly to the table. Something like FORMAT DELIMITED COLDEL ',' CHARDEL '"', in the load control card. You probably have to do some work to with PARSE, PAIR=QUOTE but it should be easier.

I haven't used this option in a while but I believe this takes care of setting correct length for the VARCHAR field.

Thanks,
Back to top
View user's profile Send private message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 57
Location: Amsterdam

PostPosted: Mon Nov 07, 2011 2:59 pm
Reply with quote

Hey Sqlcode1 - that's exactly what I did.

I parsed the CSV file making some changes and created a new CSV which I loaded using the COLDEL you mentioned. saiprasadh mentioned this was possible.

So as far as I am concerned it's all working fine, icon_lol.gif oh for the record I am using DSNUPROC to load the tables. icon_rolleyes.gif

thanks

Daniel
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts DFsort help with SUM() DFSORT/ICETOOL 12
No new posts DFSORT - VB file RDW getting overridden DFSORT/ICETOOL 3
Search our Forums:

Back to Top