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
 

 

Add a header to a file and reformat the fields using SORT.

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

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Fri May 16, 2008 1:56 pm    Post subject: Add a header to a file and reformat the fields using SORT.
Reply with quote

Hi,

Could you please help me in this

I have an input file with LRECL=60. I need to create an output file with a header and reformat the file like mentioned below. Can I do this with the Help of JCL SORT.

Input file:-
Code:
***************************** Top of Data ******************
----+----1----+----2----+----3----+----4----+----5----+----6
ARL2008031BHHL     110110140146511                         
ARL2008031BHHL     510110140490785                         
ARL2008032PHHL     110110142209491                         


And it should be reformatted like below

Output file:-
Code:
System;Date;    Monat;GV1;GV2;GV3;GEBO;GV-ABC
GRL;     200803;1;       1;    104; 104; 4;      12435,45


Here the first line will be header. Below that all records should come. for GV-ABC the decimal point should separated with coma.

Thanks in Advance.
Back to top
View user's profile Send private message

CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri May 16, 2008 2:10 pm    Post subject: Re: Add a header to a file and reformat the fields using SOR
Reply with quote

jsnair wrote:
Could you please help me in this
Yes...
Quote:
and reformat the file like mentioned below.
Not really clear, could you explain this 'reformatting'?
Quote:
Can I do this with the Help of JCL SORT.
Probably, I think, but without more clarity, I can't say for sure....
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Fri May 16, 2008 3:12 pm    Post subject:
Reply with quote

In my above post the output file format alignment is not correct. My requirement is I need to create a an output file with LRECL = 60 with below format. Here the first Line will be header. and after that all the records should come.

Code:
System; Date;    Monat; GV1; GV2; GV3; GEBO; GV-ABC
ARL;    200803;  1;     1;   104; 104; 4;    12435,45
Back to top
View user's profile Send private message
Varun Singh

New User


Joined: 01 Aug 2007
Posts: 25
Location: Delhi

PostPosted: Fri May 16, 2008 3:29 pm    Post subject: Reply to: Add a header to a file and reformat the fields usi
Reply with quote

Hi Nair,

Not at all clear with your example
Input :-

ARL2008031BHHL 110110140146511

Do u need output as :-

System; Date; Monat; GV1; GV2; GV3; GEBO; GV-ABC
ARL; 200803;1; 1; 101; 101; 4; 01465,11

But I couldn't see any decimal point should I take it for granted that
for GV-ABC u need comma before last 2 numbers....
confused icon_exclaim.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri May 16, 2008 9:26 pm    Post subject:
Reply with quote

jsnair,

Yes, you can do this with DFSORT. But you need to tell me exactly what you want to do before I can show you how.

Give the starting position and length of each field in your input record (System, Date, Monat, GV1, etc) that you want in your output record. For example:

System - starts in 1, length is 3
Date - starts in 4, length is 8
...

Give the starting position of each string in your header. For example:

'System;' starts in 1.
...

Give the starting position and length of each field in your output record.

System - starts in 1, length is 3
...

Show the output records that correspond to the input records in your example (all of the input records). Use code tags and the preview function to get the alignment correct.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Fri May 16, 2008 10:35 pm    Post subject:
Reply with quote

jsnair wrote:
In my above post the output file format alignment is not correct.
jsnair,

Try following the instuctions in [code]Suggestion Whenever indentation representation needed
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Mon May 19, 2008 12:20 pm    Post subject:
Reply with quote

Here I am trying to give you more info...

Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6
 000001 ARL2008031BHHL     110110140146511                         
 000002 ARL2008031BHHL     510110140490785                         


The above will be my Input record. It's LRECL=60. Below please find the length and

starting positions.

Length of 1st field = 3, starting position = 1
Length of 2nd field = 6, starting position = 4
Length of 3rd field = 1, starting position = 10
Length of 4th field = 1, starting position = 11
Length of 5th field = 3, starting position = 12
After 5th field starting position = 15 there will be spaces length 5
Length of 6th field = 1, starting position = 20
Length of 7th field = 3, starting position = 21
Length of 8th field = 3, starting position = 24
Length of 9th field = 1, starting position = 27
Length of 10th field = 7, starting position = 28


My output file should be also LRECL=60 with below format.

(a) Should contain a HEADER with below format.

SYSTEM; DATE; MONAT; BEK-POT; GV1; GV2; GV3; GEVO; GV-BWR

SYSTEM - length 6 stats in position 1.
DATE - length 4 stats in position 8.
MONAT - length 5 stats in position 15.
BEK-POT - length 7 stats in position 21.
MANDANT - length 7 stats in position 29.
GV1 - length 3 stats in position 37.
GV2 - length 3 stats in position 41.
GV3 - length 3 stats in position 45.
GEVO - length 4 stats in position 49.
GV-BWR - length 6 stats in position 55.




And my final output will be like below. And each field should be separated with a semi

colon like below.

Code:
 =COLS> ----+----1----+----2----+----3----+----4----+----5----+----6
 000007 SYSTEM;DATE;  MONAT;BEK-POT;MANDANT;GV1;GV2;GV3;GEVO; GV-BWR
 '''''' GRL;   200803;1;    B;      HHL;    1;  101;101;  4; 1465,11


Here the value for GV-BWR will have decimat points so the last 2 charecters should

separated with comma.

Hope this will give you some idea. If you want more info please let me know.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon May 19, 2008 9:47 pm    Post subject:
Reply with quote

Here's a DFSORT job that will do what I think you asked for.

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/60)
//SORTOUT DD DSN=...  output file (FB/60)
//SYSIN    DD    *
  OPTION COPY
  OUTFIL REMOVECC,
    HEADER1=('SYSTEM;DATE;  MONAT;BEK-POT;MANDANT;',
     'GV1;GV2;GV3;GEVO; GV-BWR'),
    BUILD=(1,3,C';',8:4,6,C';',15:10,1,C';',21:11,1,C';',
      29:12,3,C';',37:20,1,C';',41:21,3,C';',45:24,3,C';',
      51:27,1,C';',54:28,7,ZD,EDIT=(IIIT,TT))
/*


For the two input records in your example, SORTOUT will have:

Code:

SYSTEM;DATE;  MONAT;BEK-POT;MANDANT;GV1;GV2;GV3;GEVO; GV-BWR
ARL;   200803;1;    B;      HHL;    1;  101;101;  4; 1465,11
ARL;   200803;1;    B;      HHL;    5;  101;101;  4; 4907,85


I'm assuming that when you showed GRL in the output record, you really meant to show ARL since that's what appeared in the input record.
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Tue May 20, 2008 4:44 pm    Post subject:
Reply with quote

Hi Frank,

Thanks very much for your help. It is working fine. I need one more help.

I have 2 input files with LRECL = 60. I need to merge these 2 in to a new one with LRECL = 67. Please look in to the below sample files.


FILE-1:-

Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6
000001 AB100722901  ARL2008031BHHL     51011014                  l%
000002 AB103236801  ARL2008031BHHL     51011014                  í
000003 AB319116801  ARL2008031BHHL     51011014                  Î

FILE-2:-

Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6
000001 AB100722901  ARL2008032PHHL     51011014                  p
000002 AB100809801  ARL2008032PHHL     11011014                 ë @
000003 AB100826901  ARL2008032PHHL     11011014                 ãkæ


output file-

Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+--
000001 AB100722901  ARL2008031BHHL     51011014                  l%0146511
000002 AB100809801  ARL2008032PHHL     11011014                 ë @0490785
000003 AB100826901  ARL2008032PHHL     11011014                 ãkæ2209491




Steps to follow while merging-
1. The key fields in input files will start from position 1 and it's length is 13.
2. If this key fields are duplicating in File-2 we need to take corresponding record from File-1.
3. We need to add one extra field in the output file for the testing purpose. In input files the last fields will be packed decimal fields.It's starting position will be 54 and length 7.
4. So while merging we need to conver packed decimal to a ZD field in readable form. So we are adding the new fileld at the end with starting position 61 and length 7.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue May 20, 2008 9:43 pm    Post subject:
Reply with quote

jsnair,

Concatenate both files together and make sure that file from which you want to pick the record is first in the concatenation list. Any matching key from the 2 files is a dup and we only pick the firstdup and convert the 7 byte pd filed to readable form


Code:

 //STEP0100 EXEC PGM=ICETOOL                                   
 //TOOLMSG  DD SYSOUT=*                                         
 //DFSMSG   DD SYSOUT=*                                         
 //IN       DD DSN=your file1,DISP=SHR
 //         DD DSN=your file2,DISP=SHR
 //OUT      DD SYSOUT=*                                         
 //TOOLIN   DD *                                               
   SELECT FROM(IN) ON(1,6,CH) TO(OUT) FIRSTDUP USING(CTL1)     
 //CTL1CNTL DD *                                               
   OUTFIL FNAMES=OUT,BUILD=(1,60,54,7,PD,M11,LENGTH=7)         
/*


Hope this helps....

Cheers
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Wed May 21, 2008 9:49 am    Post subject:
Reply with quote

Hi Skolusu,

Thanks for your help. Also I need to clarify one more thing. In my output I need to get the Packed decimal value also. So my LRECL for input files are 60 and for output file the LRECL will be 67.

Code:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+--
000001 AB100722901  ARL2008031BHHL     51011014                  l%0146511
000002 AB100809801  ARL2008032PHHL     11011014                 ë @0490785
000003 AB100826901  ARL2008032PHHL     11011014                 ãkæ2209491

Please find the sample output file above. Here we can find both packed decimal field(starting position 54 and lenth 7)also from 61 to 67 the readable new fields. Hope this is clear.

Even I tired with your JCL and I was hitting some errors. Below I tried with PGM=SORT/ICEMAN. The errror which I am getting is below.

Code:
********************************* TOP OF DATA **
ICE063A 1 OPEN ERROR SYSIN                     
ICE751I 0 C5-K90007 E7-K11698                   
ICE052I 3 END OF DFSORT                         
******************************** BOTTOM OF DATA


When I tried with PGM=ICETOOL It was abending with MAXCC=20. As per your suggestion I had given 2 inputs in correct order and the output file. Also please keep in mind that I need to add one extra field in output file for showing readable form.


Code:
//SRT1    EXEC PGM=SORT


//TOOLIN   DD *                                           
   SELECT FROM(IN) ON(1,6,CH) TO(OUT) FIRSTDUP USING(CTL1)
//CTL1CNTL DD *                                           
   OUTFIL FNAMES=OUT,BUILD=(1,60,54,7,PD,M11,LENGTH=7)     
/*                                                         
//
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed May 21, 2008 8:41 pm    Post subject:
Reply with quote

Quote:

Hi Skolusu,

Thanks for your help. hanks for your help. Also I need to clarify one more thing. In my output I need to get the Packed decimal value also. So my LRECL for input files are 60 and for output file the LRECL will be 67.


The Job I have shown gives you what you asked for

Quote:

Even I tired with your JCL and I was hitting some errors. Below I tried with PGM=SORT/ICEMAN. The errror which I am getting is below.


If you had the run the job as is with just changing your input dataset names you will NOT get any errors. I specifically showed ICETOOL example and if you run a sort program it will NOT work.

Just copy the JOB I have given and just change the file names thats all you need

Kolusu
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed May 21, 2008 9:16 pm    Post subject:
Reply with quote

Quote:
Even I tired with your JCL and I was hitting some errors. Below I tried with PGM=SORT/ICEMAN. The errror which I am getting is below.


Kolusu's job had PGM=ICETOOL. You can't just change that to PGM=ICEMAN and expect it to work. PGM=ICETOOL invokes DFSORT's ICETOOL. PGM=ICEMAN invokes DFSORT. They are two different programs with different JCL statements. They are NOT interchangeable.

Quote:
When I tried with PGM=ICETOOL It was abending with MAXCC=20.


That means you were missing the //TOOLMSG DD statement. If you look at Kolusu's job, you'll see he has that DD statement.
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Fri May 23, 2008 2:21 pm    Post subject: Reply to: Add a header to a file and reformat the fields usi
Reply with quote

Hi Skolusu and Frank,

Thanks for your support. Now it is working fine. Earlier I was mising //TOOLMSG DD statement.

Now I am facing with a small problem with the desired output. May be my explanation above was not clear enough. After running this job i am getting only duplicated recodrs from the FILE1.

But my actual requirement is if there is any duplicates we need to take those from FILE1. Also we need to get all the remaining records from FILE1 and FILE2.

For Eg:-

FILE1

Code:
****** ***************************** Top of Data ********************
000001 AL100722922  ARL2008031BHHL     51011014                  l% 
000002 AL132236801  ARL2008031BHHL     51011014                  í   
000003 AL319116801  ARL2008031BHHL     51011014                  Π  
000004 AL339053115  ARL2008031BHHL     11011014                     
000005 AL339053129  ARL2008031BHHL     11011014                     



FILE2

Code:
****** ***************************** Top of Data *******************
000001 AL100722922  ARL2008032PHHL    51011014                  p 
000002 AL100809801  ARL2008032PHHL    11011014                 ë @
000003 AL100826901  ARL2008032PHHL    11011014                 ãkæ
000004 AL100826902  ARL2008032PHHL    11011014                   *
000005 AL100852901  ARL2008032PHHL    11011014                 ñ %



Output-

Code:
****** ***************************** Top of Data ********************
000001 AL100722922  ARL2008031BHHL    51011014                  l% 
000002 AL132236801  ARL2008031BHHL    51011014                  í   
000003 AL319116801  ARL2008031BHHL    51011014                  Π  
000004 AL339053115  ARL2008031BHHL    11011014                     
000005 AL339053129  ARL2008031BHHL    11011014                     
000006 AL100809801  ARL2008032PHHL    11011014                 ë @
000007 AL100826901  ARL2008032PHHL    11011014                 ãkæ
000008 AL100826902  ARL2008032PHHL    11011014                   *
000009 AL100852901  ARL2008032PHHL    11011014                 ñ %

So total 9 records should be there in the output. The key will start at postion 1 and it's length will be 13.

Sorry for the confusion I made.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri May 23, 2008 10:33 pm    Post subject: Reply to: Add a header to a file and reformat the fields usi
Reply with quote

jsnair,

Assuming that both files do not have duplicate keys the following DFSORT/ICETOOL JCL will give you the desired results. Concatenate both files together and make sure that file from which you want to pick the record is first in the concatenation list.
Code:


 //STEP0100 EXEC PGM=ICETOOL                                   
 //TOOLMSG  DD SYSOUT=*                                         
 //DFSMSG   DD SYSOUT=*                                         
 //IN       DD DSN=your file1,DISP=SHR
 //         DD DSN=your file2,DISP=SHR
 //OUT      DD SYSOUT=*                                         
 //TOOLIN   DD *                                               
   SELECT FROM(IN) ON(1,6,CH) TO(OUT) FIRSTDUP USING(CTL1)     
 //CTL1CNTL DD *                                               
   OUTFIL FNAMES=OUT,BUILD=(1,60,54,7,PD,M11,LENGTH=7)         
/*


Hope this helps....

Cheers
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Mon May 26, 2008 10:58 am    Post subject:
Reply with quote

Hi Skolusu,

This is same JCL as you have posted earlier. I my case there may have Duplicate keys so we need to consider that also. As I had explaied earlier. From my above explanation we van see this here FILE1 contain record with key AL100722922. The same we can see in FILE2. So we need to take this record from FILE1 not from FILE2. In FILE1 there are 5 records and in FILE2 also 5 records. Because there is one duplicate record (AL100722922) we are taking it only from the FILE1. So in output file we should get 9 records. Hope now it is clear.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue May 27, 2008 9:08 pm    Post subject:
Reply with quote

jsnair,

oops I guess I pasted the wrong control cards. Change your toolin statement to the following and re-run your job

Quote:

//TOOLIN DD *
SELECT FROM(IN) ON(1,6,CH) TO(OUT) FIRST USING(CTL1)
Back to top
View user's profile Send private message
jsnair

New User


Joined: 26 Sep 2006
Posts: 38
Location: India

PostPosted: Wed May 28, 2008 10:13 am    Post subject:
Reply with quote

Hi Skolusu,

Thanks very much..It is working fine.
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 Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
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 CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


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