MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

To write duplicate records into another file using SORT.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL)
View previous topic :: View next topic  
Author Message
ayashp
Beginner


Joined: 20 Dec 2005
Posts: 9
Topics: 3
PostPosted: Mon Jan 16, 2006 4:26 am    Post subject: To write duplicate records into another file using SORT. Reply with quote
Hi,
I have a problem while doing the SORT of a file. My file contains data in the following format. Suppose the file has two fields A1 and A2 and the file format is as below :
Code:

 A1          A2
------      ----
XXXX         Y
BBBB         F
GHGH         Y
XXXX         Y
AAAA         Y
BBBB         I


I want to count the total number of duplicate records for which the fields in A1 match and the value of A2 is Y (for the above given example the record is XXXX Y). At the sametime I also want to write the duplicate records into an output file. Can anyone tell me what will be the SUM FILEDS parameter and also how to write to a new file for this if at all this is possible by JCL. Else I think I have to write a COBOL program using internal sort for this.

Thanks in advance.
ayashp
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Mon Jan 16, 2006 5:23 am    Post subject: Reply with quote
ayashp,

Please provide the following information: LRECL & RECFM of your input file and a sample output file. The word "Duplicate" can be interpreted in different ways. For example, you have 2 records with "XXXX Y" - the first record is considered as a unique record and the second & subsequent ones are considered duplicate entries. In such case, how you like to display the count - (2 or 1 - Including the first entry or not ?)

Quote:

I want to count the total number of duplicate records for which the fields in A1 match and the value of A2 is Y


So, do you want to have totals for each key ??? or do you need a grand total ???. Please provide a sample output, which can avoid all sorts of guess work !.

Thanks,

Phantom
Back to top
View user's profile Send private message
ayashp
Beginner


Joined: 20 Dec 2005
Posts: 9
Topics: 3
PostPosted: Tue Jan 17, 2006 12:43 am    Post subject: Reply with quote
Hi Phantom,
Below is a sample file that can be considered as input file.

XXXXY
FGHJB
FGHJB
XXXXY
XXXX
AAAAY
BBBBY
XXXXY

The File is a Fixed length file and LRECL is 5. I want to write all the duplicates from this input file into another output file. The duplicate records should have the last field as Y. I need a grand total of all the duplicates.
So my output file can look like this

XXXXY
XXXXY
XXXXY

Hope I have given a clear picture this time.

Thanks in advance,
ayashp
Back to top
View user's profile Send private message
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Jan 17, 2006 4:49 am    Post subject: Reply with quote
ayashp,

Try this code,
Code:

//R010   EXEC  PGM=SYNCTOOL                                       
//TOOLMSG  DD  SYSOUT=*                                           
//DFSMSG   DD  SYSOUT=*                                           
//INPUT    DD  *                                                   
XXXXY                                                             
FGHJB                                                             
FGHJB                                                             
XXXXY                                                             
XXXX                                                               
AAAAY                                                             
BBBBY                                                             
XXXXY                                                             
//OUTDUPS  DD  DSN=MY.OUTPUT.FILE.DUPRECS,
//             DISP=(NEW,CATLG,DELETE),                           
//             UNIT=SYSDA,                                         
//             SPACE=(TRK,(1,0),RLSE)                             
//OUTTOTAL DD  DSN=MY.OUTPUT.FILE.DUPCOUNT,
//             DISP=(NEW,CATLG,DELETE),                           
//             UNIT=SYSDA,                                         
//             SPACE=(TRK,(1,0),RLSE)                             
//TOOLIN   DD  *                                                   
  SELECT FROM(INPUT)   TO(OUTDUPS)  ON(1,5,CH) USING(CTL1) ALLDUPS
  COPY   FROM(OUTDUPS) TO(OUTTOTAL) USING(CTL2)                     
/*                                                                 
//CTL1CNTL DD  *                                                   
  INCLUDE COND=(5,1,CH,EQ,C'Y')                                     
/*                                                                 
//CTL2CNTL DD  *                                                   
  OUTFIL TRAILER1=('TOTAL NUMBER OF DUPLICATE RECORDS: ',COUNT),   
         REMOVECC,NODETAIL                                         
/*                                                                 


If you are using DFSORT, change the PGM name from SYNCTOOL to ICETOOL.

Hope this helps,

Cheers,

Phantom
Back to top
View user's profile Send private message
ayashp
Beginner


Joined: 20 Dec 2005
Posts: 9
Topics: 3
PostPosted: Tue Jan 17, 2006 6:43 am    Post subject: Reply with quote
Hi Phantom,

That was great.It worked. Thanks alot for the help.

ayashp
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12385
Topics: 75
Location: San Jose
PostPosted: Tue Jan 17, 2006 7:53 am    Post subject: Reply with quote
Phantom,

You don't need to 2 passes of the data. you can do it in just one pass with the help USING clause on the SELECT statement.

Code:

//STEP0100 EXEC  PGM=ICETOOL                                       
//TOOLMSG  DD  SYSOUT=*                                           
//DFSMSG   DD  SYSOUT=*                                           
//INPUT    DD  *                                                   
XXXXY                                                             
FGHJB                                                             
FGHJB                                                             
XXXXY                                                             
XXXX                                                               
AAAAY                                                             
BBBBY                                                             
XXXXY                                                             
//OUTDUPS  DD  DSN=MY.OUTPUT.FILE.DUPRECS,
//             DISP=(NEW,CATLG,DELETE),                           
//             UNIT=SYSDA,                                         
//             SPACE=(TRK,(1,0),RLSE)                             
//OUTTOTAL DD  DSN=MY.OUTPUT.FILE.DUPCOUNT,
//             DISP=(NEW,CATLG,DELETE),                           
//             UNIT=SYSDA,                                         
//             SPACE=(TRK,(1,0),RLSE)                             
//TOOLIN   DD  *                                                   
  SELECT FROM(INPUT)   TO(OUTDUPS)  ON(1,5,CH) USING(CTL1) ALLDUPS
//CTL1CNTL DD  *                                                   
  INCLUDE COND=(5,1,CH,EQ,C'Y')                                     
  OUTFIL FNAMES=OUTDUPS
  OUTFIL FNAMES=OUTTOTAL,REMOVECC,NODETAIL,
  TRAILER1=('TOTAL NUMBER OF DUPLICATE RECORDS: ',COUNT)
/*                         


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Phantom
Data Mgmt Moderator
Data Mgmt Moderator


Joined: 07 Jan 2003
Posts: 1056
Topics: 91
Location: The Blue Planet
PostPosted: Tue Jan 17, 2006 9:01 am    Post subject: Reply with quote
Oh Yesss !!!...Thanks for pointing that out kolusu. 8)

Regards,

Phantom
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL) All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group