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
 

 

Varchar lengths in DFSORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Daniel Prosser

New User


Joined: 05 Nov 2010
Posts: 41
Location: Amsterdam

PostPosted: Thu Nov 03, 2011 6:05 pm    Post subject: Varchar lengths in DFSORT
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    Post subject:
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: 41
Location: Amsterdam

PostPosted: Thu Nov 03, 2011 7:47 pm    Post subject:
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: 41
Location: Amsterdam

PostPosted: Thu Nov 03, 2011 7:48 pm    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Thu Nov 03, 2011 7:56 pm    Post subject:
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: 41
Location: Amsterdam

PostPosted: Fri Nov 04, 2011 12:18 pm    Post subject:
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: 578
Location: USA

PostPosted: Fri Nov 04, 2011 9:01 pm    Post subject:
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: 41
Location: Amsterdam

PostPosted: Mon Nov 07, 2011 2:59 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DFSORT - Overriding default option TU... Steve Ironmonger DFSORT/ICETOOL 3 Tue Apr 04, 2017 3:54 pm
No new posts DFSORT MUL FIELDS tspr52 DFSORT/ICETOOL 16 Fri Mar 03, 2017 11:53 pm
No new posts How to get the remainder and quotient... vnktrrd DFSORT/ICETOOL 2 Mon Oct 31, 2016 10:59 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm


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