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

Sorting Matched Records and counting the group of records..


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

New User


Joined: 12 May 2008
Posts: 13
Location: Hartford

PostPosted: Tue Jul 22, 2008 11:58 pm
Reply with quote

Hello,

I have below records in input file

Policy Number(9) Commision(4) Type(1) so LRECL is 14 bytes.

9960824240012I
9960824240012B
9890368720015I
9960824240015B
9960824240012B
9960824240012I
9890368720015I
9890368720014I
9890853380014B
9326603840015I

I require the output file to have matched duplicate records for Policy number and the Commision. so the output will look alike below mentioned.

9960824240012I
9960824240012I
9960824240012B
9960824240012B
9890368720015I
9890368720015I

Again I need to read this out file and create one more file based on 'TYPE'. Now the set of policy shuld have atleast 2 same TYPES mandatory..so the outpout will look alike

9960824240012I
9960824240012I
9960824240012B
9960824240012B

why another policy did not came out becaase it had 2 'I' but not 2 'B'.

Now here i have 1 set of policy out of 4 which came in input.

Can i get any count number in IOF having such type of policy sets?

I know this is bit tricky or twisted which i am not aware of..

Can you please guide or help to get any solution for this using SORT/ICETOOL?

Thanks,
Vikas
icon_neutral.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Wed Jul 23, 2008 1:48 am
Reply with quote

Some questions for clarification:

What order do you want the output records in (original order, sorted order, some other order)?

Do you want two output files - one with matched duplicates and another based on TYPE? Or do you just want the output file based on TYPE?

It's not clear what you mean by "Now the set of policy shuld have atleast 2 same TYPES mandatory". Are B and I the only types? Do you want all of the records with a specific policy and commission only if one of those records has TYPE B and another has TYPE I, or do you want something else? Please clarify the "rules".

I don't know what you mean by "Can i get any count number in IOF having such type of policy sets?". What is IOF? Are you looking for a "count" of some kind? In what form? As a separate output file or what?

It would really help if you showed a better example of your input records with more variations and the complete expected output or outputs, and explained the rules for getting from input to output in terms of this better example.
Back to top
View user's profile Send private message
Vsonawane

New User


Joined: 12 May 2008
Posts: 13
Location: Hartford

PostPosted: Wed Jul 23, 2008 9:09 am
Reply with quote

Hi Frank,

i am trying to break up my requirement below as it is quite confusing to explain.

1) The Final Output file should have records sorted on policy Number and Commision value; however this final output file must have only duplicate policy record.

2) By Duplicate i mean I require only those policy records records which have same corresponding Commision value (some type of code) e.g Policy-1 has 2 commission values 0100 and 0200 this is not the duplicate it must have two same commission value. Then only this policy should be there in the Out file.

Policy Commision Value
XXXXX 1111
XXXXX 2222
XXXX1 1111

This are not the duplicate values

Policy Commision Value
XXXXX 1111
XXXXX 1111
XXXX1 1111

the first 2 records are duplicates and must exist in the output file.

Only those policy which are duplicates and have matching commision shuld be there in outfile.

3) The Above one is the basic requirement. Now there are only 2 policy type 'I' and 'B'. In my example i have 2 set of policies(one with 4 duplicates and other one with 2 duplicates) as explained in above two requirement:

9960824240012I
9960824240012I
9960824240012B
9960824240012B
9890368720015I
9890368720015I

Now i require only those records which have the matching set of values e,g the policy 996082424 with commision value 0012 has two matching sets one with 'I' and other with 'B', all 4 recods of this policy should come in final output file leaving the last two records as they don't have matching set with TYPe 'B'.

i Hope i am able to justify my requirements..I have tried to explain it keeping 2 sort step in mind. may be this whole thing can be done in one sort step which i might not know.

3) I am trying to find the the Count on policy type sets records in my output file. Where my input file may have millions of records.

9960824240012I
9960824240012I
9960824240012B
9960824240012B
9890368720015I
9890368720015I
9890368720015B
9890368720015B

e.g if above is my final output with 8 rows then the Policy type set count is 2.

Thanks
Vik
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Jul 23, 2008 9:50 am
Reply with quote

Frank,
(I)interactive (O)output (F)acility is a product that is similar to and competes with IBM's (S)pool (D)isplay and (S)earch (F)acility. It displays spooled input and output of jobs.
Back to top
View user's profile Send private message
vijay_pampana

New User


Joined: 19 Jan 2008
Posts: 15
Location: Hyderabad

PostPosted: Wed Jul 23, 2008 5:05 pm
Reply with quote

Using ICETOOL we can acheive this requirement. May be I am giving this reply little late and by this time you may have completed your requirement but i just want to test my knowledge.

1) First select the records containing same type into one file.
i.e records with all I's into one file and records with all b's to another.

2) Using ICETOOL 'ALLDUPS' get the records which have duplicte on first 13 characters from the above files.

3) Using splice you can now match the two files and get your required output.

Thanks
Vijay
Back to top
View user's profile Send private message
Vsonawane

New User


Joined: 12 May 2008
Posts: 13
Location: Hartford

PostPosted: Wed Jul 23, 2008 6:48 pm
Reply with quote

Hi Vijay,

The way you suggested is doable. But i am bit concerned on the 3rd point.
Say there 5 duplicates of one policy. 3 Duplicates have TYPE 'I' and 2 Duplicates have type 'B' and both these I and B will be in two seperate file. In this case i dont require the data. I would like to see the dupliacte policies only if there are exact 2 occurence of I and B each.

Thanks,
Vikas
Back to top
View user's profile Send private message
Vsonawane

New User


Joined: 12 May 2008
Posts: 13
Location: Hartford

PostPosted: Wed Jul 23, 2008 6:51 pm
Reply with quote

Vijay, Do you have the link for the ICETOOL manual?
Back to top
View user's profile Send private message
vijay_pampana

New User


Joined: 19 Jan 2008
Posts: 15
Location: Hyderabad

PostPosted: Wed Jul 23, 2008 9:23 pm
Reply with quote

Instead of 'ALLDUPS' use 'FIRSTDUP' in the ICETOOL JCL and create two temp files
File1: INCLUDE COND where TYP='I' and retrieve the first dup record on policy and commission
File2: INCLUDE COND where TYP='B' and retrieve the first dup record on policy and commission

Merge the above two files and get FIRSTDUP to get the required count.

If you need any more information please feel free to reply me.

Thanks
Vijay
Back to top
View user's profile Send private message
Vsonawane

New User


Joined: 12 May 2008
Posts: 13
Location: Hartford

PostPosted: Wed Jul 23, 2008 10:36 pm
Reply with quote

Sorry to say Vijay...I am not following what you are trying to say..
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Jul 24, 2008 3:04 am
Reply with quote

The following DFSORT/ICETOOL JCL will give you desired results. This will pick all duplicate records which have 2 'I' Records and 2 'B' records. If the input has only 2 'I' records per key or 2 'B' per key then we will skip such records.

Code:

//STEP0100  EXEC  PGM=ICETOOL                                       
//TOOLMSG   DD  SYSOUT=*                                           
//DFSMSG    DD  SYSOUT=*                                           
//IN        DD *                                                   
9960824240012I                                                     
9960824240012B                                                     
9890368720015I                                                     
9960824240015B                                                     
9960824240012B                                                     
9960824240012I                                                     
9890368720015I                                                     
9890368720014I                                                     
9890853380014B                                                     
9326603840015I                                                     
9890853390014B                                                     
9890853390014B                                                     
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  SELECT FROM(IN) TO(T1) ON(01,13,CH) ALLDUPS USING(CTL1)           
  SPLICE FROM(T1) TO(OUT) ON(1,13,CH) WITH(1,14) WITHALL USING(CTL2)
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,14,2X),                       
  SECTIONS=(1,13,                                                   
  TRAILER3=(1,13,'$',                                               
            MIN=(14,1,ZD,M11,LENGTH=1),                             
            MAX=(14,1,ZD,M11,LENGTH=1)))                           
//CTL2CNTL DD *                                                     
  SORT FIELDS=(1,13,CH,A,15,1,CH,D)                                 
  OUTFIL FNAMES=OUT,INCLUDE=(15,2,ZD,EQ,29),                       
  BUILD=(1,14)                                                     
//*                                                                 


The output from this job is

Code:

9960824240012I
9960824240012B
9960824240012B
9960824240012I


Hope this helps...
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top