View previous topic :: View next topic
|
Author |
Message |
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
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 |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
Varun Singh
New User
Joined: 01 Aug 2007 Posts: 25 Location: Delhi
|
|
|
|
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 |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
Back to top |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
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 |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
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 |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
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 |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
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 |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
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 |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
|
|
|
|
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 |
|
|
jsnair
New User
Joined: 26 Sep 2006 Posts: 38 Location: India
|
|
|
|
Hi Skolusu,
Thanks very much..It is working fine. |
|
Back to top |
|
|
|