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

Input data to be Split into three files


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

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Tue Aug 11, 2015 10:04 pm
Reply with quote

Hi All,

Need all experts help for my below query.

Below are the Shop 1 numbers:
3004
3005
Below are the shop 2 numbers:
3816
3818
Below are the shop 3 numbers:
2446
2447

I have my input file in the below format,
Code:


0401043004
0401043816
0401042446


I need to split the input into 3 files according to the shop#

The shop# will change dynamically , so not possible to hard code in JCL.
is there any way to achieve this through JCL sort ( Join Keys)?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Aug 11, 2015 10:16 pm
Reply with quote

Yes, if you have a file containing a list of shop-numbers and an indicator of which file they are to go to, it would be a simple JOINKEYS on the shop-number.

The REFORMAT statement would contain the entire F1 record plus the indicator from F2. Three OUTFIL statements (or four, you need to consider where you want "typos" to go, depending on how you create the driver file). Use SAVE for one of the OUTFILs, and BUILD on each to cut off the indicator value.

No JOIN statement needed, so you only get the matches.
Back to top
View user's profile Send private message
kctechpro2

New User


Joined: 27 Apr 2013
Posts: 7
Location: United States

PostPosted: Tue Aug 11, 2015 10:22 pm
Reply with quote

We need more input regarding the requirement. If the shop # changes dynamically, how is that determined?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Tue Aug 11, 2015 10:37 pm
Reply with quote

You need to tell us what is the criteria which determines that a particular number belongs to Shop1, 2 or 3.

e.g. all numbers starting from '30' belong to shop 1, numbers starting from '38' belong to shop 2 and numbers starting from '24' belong to shop 3.

The criteria is essential to get a solution.

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

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 12:08 am
Reply with quote

kctechpro2 wrote:
We need more input regarding the requirement. If the shop # changes dynamically, how is that determined?


The shop is taken from db and loaded into file
the range 3004 - 3005 is shop#1
the range 3816 - 3817 is shop#2
the range 2446 - 2447 is shop#3

tommorrow these ranges can increase as below:
the input file of the shop numbers would be as below
Code:

244630043816
244730053817


These list can grow as below :
Code:

244630043816
244730053817
244830063818
244930073819


the first four bytes is the shop#3 and the next is shop#2 and last four is shop#1
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 12:13 am
Reply with quote

RahulG31 wrote:
You need to tell us what is the criteria which determines that a particular number belongs to Shop1, 2 or 3.

e.g. all numbers starting from '30' belong to shop 1, numbers starting from '38' belong to shop 2 and numbers starting from '24' belong to shop 3.

The criteria is essential to get a solution.

.


Hi Rahul,

Thanks for your reply.

the shop numbers are aligned like this

the first four bytes is shop#3 , the next four is shop#2 and rest is shop#1
Code:

244630043816
244730053817
244830063818
244930073819
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Aug 12, 2015 12:38 am
Reply with quote

Do you expect to have gaps and/or overlap in the shop number? Can your data be like below? In the first example below, we have a gap between record 3rd and 4th and in the second example, 2449 is on the first record as well as on the 4th one.

Code:

244630043816
244730053817
244830063818
245130113821

OR
Code:
244624493816
244730053817
244830063818
244930073819
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Aug 12, 2015 12:43 am
Reply with quote

I don't understand it. What is the output you are looking for?

In your first post, it looked like there could only be one shop number in a row. But in your recent post it seems there will be 3 shop numbers in a single row belonging to shop1, 2 and 3.

Now, if all the shops numbers are present in all the rows, how do you want to split them? Do you want shops number 1 in one file, shop number 2 in file two, etc. ?? (which is really simple to do)

Can you show expected output ?
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 12:47 am
Reply with quote

sqlcode1 wrote:
Do you expect to have gaps and/or overlap in the shop number? Can your data be like below? In the first example below, we have a gap between record 3rd and 4th and in the second example, 2449 is on the first record as well as on the 4th one.

Code:

244630043816
244730053817
244830063818
245130113821

OR
Code:
244624493816
244730053817
244830063818
244930073819


Yes there would be gaps as shown in your first example
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 1:27 am
Reply with quote

RahulG31 wrote:
I don't understand it. What is the output you are looking for?

In your first post, it looked like there could only be one shop number in a row. But in your recent post it seems there will be 3 shop numbers in a single row belonging to shop1, 2 and 3.

Now, if all the shops numbers are present in all the rows, how do you want to split them? Do you want shops number 1 in one file, shop number 2 in file two, etc. ?? (which is really simple to do)

Can you show expected output ?


Hi Rahul,

Sorry for not being specific

there are two input files
File#1 which has the shop numbers as below
Code:

244630043816
244730053817
244830063818
244930073819

File#2 which is in the below format
Code:

0401043004
0401043816
0401042446


I should have three output files for each shop numbers

Kindly let me know if you need more information
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Aug 12, 2015 1:57 am
Reply with quote

If you could show the expected output ? Let's not make anyone guess what you want to do.

Edit: Explain clearly otherwise the thread is being stretched unnecessarily to the point of looking ridiculous.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Aug 12, 2015 2:04 am
Reply with quote

I hope the expected output is:

File 1 contains:
Code:
0401043004


File 2 contains:
Code:
0401043816


File 3 contains:
Code:
0401042446


is this correct ?

OR you have something else in mind.
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 2:07 am
Reply with quote

RahulG31 wrote:
I hope the expected output is:

File 1 contains:
Code:
0401043004


File 2 contains:
Code:
0401043816


File 3 contains:
Code:
0401042446


is this correct ?

OR you have something else in mind.


yes true
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Aug 12, 2015 2:41 am
Reply with quote

There may be a better solution but this should do for you as of now:

Code:
//TOOLIN DD *
 COPY FROM(IN2) TO(OUT1) USING(CTL1)
 COPY FROM(IN2) TO(OUT2) USING(CTL2)
 COPY FROM(IN2) TO(OUT3) USING(CTL3)
//CTL1CNTL DD *
 JOINKEYS F1=IN1,FIELDS=(1,4,A)
 JOINKEYS F2=IN2,FIELDS=(7,4,A)
 REFORMAT FIELDS=(F2:1,10)
//CTL2CNTL DD *
 JOINKEYS F1=IN1,FIELDS=(5,4,A)
 JOINKEYS F2=IN2,FIELDS=(7,4,A)
 REFORMAT FIELDS=(F2:1,10)
//CTL3CNTL DD *
 JOINKEYS F1=IN1,FIELDS=(9,4,A)
 JOINKEYS F2=IN2,FIELDS=(7,4,A)
 REFORMAT FIELDS=(F2:1,10)
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Aug 12, 2015 3:25 am
Reply with quote

Another thing you can do is:

Code:
//TOOLIN DD *
 COPY FROM(IN1) TO(TMP1) USING(CTL1)
 COPY FROM(IN2) TO(OUT1) USING(CTL2)
//CTL1CNTL DD *
 OUTFIL FNAMES=TMP1,BUILD=(1,4,C'1',/,5,4,C'2',/,9,4,C'3')
//CTL2CNTL DD *
 JOINKEYS F1=TMP1,FIELDS=(1,4,A)
 JOINKEYS F2=IN2,FIELDS=(7,4,A)
 REFORMAT FIELDS=(F2:1,10,F1:5,1)
 OUTFIL FNAMES=OUT1,INCLUDE=(11,1,CH,EQ,C'1'),BUILD=(1,10)
 OUTFIL FNAMES=OUT2,INCLUDE=(11,1,CH,EQ,C'2'),BUILD=(1,10)
 OUTFIL FNAMES=OUT3,INCLUDE=(11,1,CH,EQ,C'3'),BUILD=(1,10)
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Aug 12, 2015 3:45 am
Reply with quote

sun_job,
If you don't care about writting records when there is no match its relatively simple solution (first one below) but if you want orphan records to go some file for reporting purpose use second one.

When you don't care about orphan records
Code:
//STEP0002 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD *                                                 
244630043816                                                   
244730053817                                                   
244830063818                                                   
244930073819                                                   
//SORTJNF2 DD *                                                 
0401043004                                                     
0401043009                                                     
0401043816                                                     
0401043817                                                     
0401042446                                                     
0401042447                                                     
0401042448                                                     
0401042449                                                     
//FILE1    DD SYSOUT=*                                         
//FILE2    DD SYSOUT=*                                         
//FILE3    DD SYSOUT=*                                         
//SYSIN DD *                                                   
 JOINKEYS FILE=F1,FIELDS=(81,1,A),SORTED,NOSEQCK               
 JOINKEYS FILE=F2,FIELDS=(81,1,A),SORTED,NOSEQCK               
 REFORMAT FIELDS=(F2:1,15,F1:1,15)                             
 INREC IFTHEN=(WHEN=(7,4,ZD,EQ,16,4,ZD),OVERLAY=(42:C'1')),     
       IFTHEN=(WHEN=(7,4,ZD,EQ,20,4,ZD),OVERLAY=(42:C'2')),     
       IFTHEN=(WHEN=(7,4,ZD,EQ,24,4,ZD),OVERLAY=(42:C'3'))     
 SORT FIELDS=COPY                                               
 OUTFIL FNAMES=FILE1,INCLUDE=(42,1,CH,EQ,C'1'),BUILD=(1,15)     
 OUTFIL FNAMES=FILE2,INCLUDE=(42,1,CH,EQ,C'2'),BUILD=(1,15)     
 OUTFIL FNAMES=FILE3,INCLUDE=(42,1,CH,EQ,C'3'),BUILD=(1,15)     
/*                                                             
//JNF1CNTL DD *                                                 
 INREC OVERLAY=(81:C'X')                                       
/*                                                             
//JNF2CNTL DD *                                                 
 INREC OVERLAY=(81:C'X')                                       
/*                                                             

Orphan records going to some file
Code:
//STEP0001 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD *                                                 
244630043816                                                   
244730053817                                                   
244830063818                                                   
244930073819                                                   
//SORTJNF2 DD *                                                 
0401043004                                                     
0401043009                                                     
0401043816                                                     
0401043817                                                     
0401042446                                                     
0401042447                                                     
0401042448                                                     
0401042449                                                     
//FILE1    DD SYSOUT=*                                         
//FILE2    DD SYSOUT=*                                         
//FILE3    DD SYSOUT=*                                         
//FILEX    DD SYSOUT=*                                         
//SYSIN DD *                                                   
 JOINKEYS FILE=F1,FIELDS=(81,1,A),SORTED,NOSEQCK               
 JOINKEYS FILE=F2,FIELDS=(81,1,A),SORTED,NOSEQCK               
 REFORMAT FIELDS=(F2:1,15,F1:1,15)                             
 INREC IFTHEN=(WHEN=(7,4,ZD,EQ,16,4,ZD),OVERLAY=(42:C'1')),     
       IFTHEN=(WHEN=(7,4,ZD,EQ,20,4,ZD),OVERLAY=(42:C'2')),     
       IFTHEN=(WHEN=(7,4,ZD,EQ,24,4,ZD),OVERLAY=(42:C'3')),     
       IFTHEN=(WHEN=NONE,OVERLAY=(42:C'9'))                     
 SORT FIELDS=(1,15,CH,A,42,1,CH,A)                             
 OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,15),PUSH=(42:42,1))     
 OUTFIL FNAMES=FILE1,INCLUDE=(42,1,CH,EQ,C'1'),                 
        REMOVECC,NODETAIL,SECTIONS=(1,15,TRAILER3=(1,15))       
                                                               
 OUTFIL FNAMES=FILE2,INCLUDE=(42,1,CH,EQ,C'2'),                 
        REMOVECC,NODETAIL,SECTIONS=(1,15,TRAILER3=(1,15))       
                                                               
 OUTFIL FNAMES=FILE3,INCLUDE=(42,1,CH,EQ,C'3'),                 
        REMOVECC,NODETAIL,SECTIONS=(1,15,TRAILER3=(1,15))       
                                                                   
 OUTFIL FNAMES=FILEX,INCLUDE=(42,1,CH,EQ,C'9'),                     
        REMOVECC,NODETAIL,SECTIONS=(1,15,TRAILER3=(1,15))           
                                                                   
/*                                                                 
//JNF1CNTL DD *                                                     
 INREC OVERLAY=(81:C'X')                                           
/*                                                                 
//JNF2CNTL DD *                                                     
 INREC OVERLAY=(81:C'X')                                           
/*                                                                 
//*


OUTPUT
-----------------------
FILE1
Code:
 
0401042446       
0401042447       
0401042448       
0401042449       


FILE2
Code:
 
0401043004     

FILE3
Code:
 
0401043816     
0401043817     


FILEX - When applicable
Code:
0401043009                                 

Thanks,
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Aug 12, 2015 1:14 pm
Reply with quote

You really must learn how to post your query properly: this is not a JCL or VSAM topic but a sort topic so it should have been posted in the appropriate part of the forum for your sort product. Also, you would be asking help with the sort control cards not JCL.

When posting sort queries like yours you should specify representative sample data and the expected output from that data and the rules for getting that output from the input.

Representative data should show duplicates, gaps, different record types etc should they exist.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Aug 12, 2015 3:42 pm
Reply with quote

sqlcode1's solution is a full join, meaning every record on F2 is matched to every record on F1 (through the 'X' added at position 81).

With large amounts of data, there is a performance impact.

With 1000 data records and four "shop code" records, 4000 REFORMAT records will be created.

With 100,000 data records and 50 "shop code" records, 5,000,000 REFORMAT records will be created.

Then all the records will be SORTed.

The larger file is better as F1.

RahulG31's second solution, which I would do as two SORT steps rather than use ICETOOL just to have one step, will likely be more efficient. The data file looks to be in order, so SORTED,NOSEQCK for that.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA

PostPosted: Wed Aug 12, 2015 6:37 pm
Reply with quote

If OP doesn't care about orphan records then no sorting is required. Using two pass approach, there are better ways to handle
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 8:48 pm
Reply with quote

RahulG31 wrote:
Another thing you can do is:

Code:
//TOOLIN DD *
 COPY FROM(IN1) TO(TMP1) USING(CTL1)
 COPY FROM(IN2) TO(OUT1) USING(CTL2)
//CTL1CNTL DD *
 OUTFIL FNAMES=TMP1,BUILD=(1,4,C'1',/,5,4,C'2',/,9,4,C'3')
//CTL2CNTL DD *
 JOINKEYS F1=TMP1,FIELDS=(1,4,A)
 JOINKEYS F2=IN2,FIELDS=(7,4,A)
 REFORMAT FIELDS=(F2:1,10,F1:5,1)
 OUTFIL FNAMES=OUT1,INCLUDE=(11,1,CH,EQ,C'1'),BUILD=(1,10)
 OUTFIL FNAMES=OUT2,INCLUDE=(11,1,CH,EQ,C'2'),BUILD=(1,10)
 OUTFIL FNAMES=OUT3,INCLUDE=(11,1,CH,EQ,C'3'),BUILD=(1,10)


Thanks much Rahulji.

Thanks all, i will try the same and update the results
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed Aug 12, 2015 8:52 pm
Reply with quote

If you don't get the results you want, please describe your problem better, with representative sample data, and an idea of the volumes you have to deal with.
Back to top
View user's profile Send private message
sun_job

New User


Joined: 18 Sep 2007
Posts: 73
Location: Bangalore

PostPosted: Wed Aug 12, 2015 8:58 pm
Reply with quote

Nic Clouston wrote:
You really must learn how to post your query properly: this is not a JCL or VSAM topic but a sort topic so it should have been posted in the appropriate part of the forum for your sort product. Also, you would be asking help with the sort control cards not JCL.

When posting sort queries like yours you should specify representative sample data and the expected output from that data and the rules for getting that output from the input.

Representative data should show duplicates, gaps, different record types etc should they exist.


Sure Nic , will take care in future
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 How to split large record length file... DFSORT/ICETOOL 8
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
Search our Forums:

Back to Top