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
 

 

Convert a record which is fixed length to variable length

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

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Mon Jan 12, 2015 11:06 am    Post subject: Convert a record which is fixed length to variable length
Reply with quote

Hi,

I have a flat csv format format file which I need to load in the DB
To conver the file in DB format using ENDBEFR=C';',FIXLEN=26 command in SORT JCL
Some of the fields are variable length in DB so I need to find the RDW
For eg input File Format



123445 34567890 AAAAAAA FFFFFFF

output file
Record 1 is FB
Record 2 is VB
Record 3 is VB
Record 4 is FB

Need to convert record 2 and 3 to VB to load the file in DB.

Please let know if this is possible with SORT JCL
Thanks
Back to top
View user's profile Send private message

nimesh.chakravarty

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Mon Jan 12, 2015 12:50 pm    Post subject:
Reply with quote

%01,%02 and %03 are variable records for which I need the length preceeding the record
Code:

//STEP01   EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=WOK.PROK.D131122.XXXXXX.CSV,DISP=SHR                 
//SORTOUT  DD DSN=WOK.PROK.D131122.XXXXXX.LOAD,                         
//            DISP=(NEW,CATLG,DELETE),                                 
//            RECFM=FB,LRECL=498,SPACE=(10,(10,10),RLSE)               
//SYSIN    DD *                                                         
  SORT FIELDS=COPY                                                     
  INREC PARSE=(%00=(ENDBEFR=C';',FIXLEN=26),                           
               %01=(ENDBEFR=C';',FIXLEN=128),                           
               %02=(ENDBEFR=C';',FIXLEN=128),                           
               %03=(ENDBEFR=C';',FIXLEN=100),                           
               %04=(ENDBEFR=C';',FIXLEN=1),                             
               %05=(ENDBEFR=C';',FIXLEN=20),                           
               %06=(ENDBEFR=C';',FIXLEN=5),                             
               %07=(ENDBEFR=C';',FIXLEN=10),                           
               %08=(ENDBEFR=C';',FIXLEN=26),                           
               %09=(FIXLEN=26)),                                       
          BUILD=(%00,27:%01,157:%02,287:%03,%04,%05,%06,%07,%08,%09)   
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Mon Jan 12, 2015 3:04 pm    Post subject: Reply to: Convert a record which is fixed length to variable
Reply with quote

Possibly your main training is in non-Mainframe programming.

Fields are not records. Files can be VB or FB, records cannot. You are using PARSE, not a sub-parameter of it.

Show a representative sample of your data, and what output your are expecting and getting.

State what utility/program you are using to load the data which you prepare.
Back to top
View user's profile Send private message
nimesh.chakravarty

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Mon Jan 12, 2015 3:35 pm    Post subject:
Reply with quote

Hi .

Sorry the data is char and Var char
Input file --> FB csv format
WOK.XXXX.D131122.XXXXXX.CSV
Code:

***************************** Top of Data ******************************
ABCDA1P;TXXXXXX1;CCCCCC01;DEDDDDD;N;;;;2015-01-05-00.00.00.000000       
ANCDA1P;TXXXXXX1;CCCCCCC0;EEEEEEEEEM;N;;;;2015-01-05-00.00.00.000000   
**************************** Bottom of Data ****************************

Quote:

Each record is comma seperated. column 2.3 and 4 are var char in the DB
Output File via FileAID : the length of variables fields are invalid
i.e 16448


Code:

---- FIELD LEVEL/NAME ------- -FORMAT- ----+----1----+----2----+----3----+----4
5 XXXXDBN UNALIGNED           24/CHAR ABCDA1P                                 
5 XXXXTBN UNALIGNED           LENGTH  16448 
                                            130/VCHR TXXXXXX1                               
                              (POS 43-82)                                         
                             (POS 83-122)                                         
                            (POS 123-130)                                         
5 XXXXCOL UNALIGNED           LENGTH  16448                                   
                                          130/VCHR CCCCCC0                                 
                           (POS 43-82)                                         
                          (POS 83-122)                                         
                         (POS 123-130)                                         
5 XXXXDSC UNALIGNED          LENGTH  16448                                   
                                      102/VCHR EEEEEEEEEM                             
                           (POS 43-82)                                         
                          (POS 83-102)                                         
5 XXXXCID UNALIGNED            1/CHAR N                                       
5 XXXXTYP UNALIGNED           20/CHAR                                         
5 XXXXRUL UNALIGNED            5/CHAR                                         
5 XXXXLVL UNALIGNED           10/CHAR                                         
5 XXXXTSI UNALIGNED           26/CHAR 2015-01-05-00.00.00.000000             
5 XXXXTSU UNALIGNED           26/CHAR 2015-01-05-00.00.00.000000
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Mon Jan 12, 2015 3:40 pm    Post subject: Reply to: Convert a record which is fixed length to variable
Reply with quote

What is the output from your SORT step and what input does whatever is loading it in to DB2 need? What format is a VARCHAR source?

And what are you using to get the data into DB2?

It seems possible that you are telling File-AID that the field is VARCHAR when it isn't, and perhaps it doesn't need to be.
Back to top
View user's profile Send private message
nimesh.chakravarty

New User


Joined: 18 Nov 2010
Posts: 7
Location: India

PostPosted: Wed Jan 14, 2015 2:53 pm    Post subject:
Reply with quote

Hi Bill,

The issue is fixed.

You were right. Just checked the file from 0.3.4 option and there were some garbage values.

After removing working fine.

Thanks a lot for your help.
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 How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Convert +9999999999999.99 to S9(12)V9(3) vnktrrd DFSORT/ICETOOL 8 Thu Nov 17, 2016 8:15 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm


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