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 

Exclude records present in File1 from File2 - DFSORT/ICETOOL

 
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
SAHANA
Beginner


Joined: 18 Jul 2007
Posts: 18
Topics: 5
Location: United Kingdom

PostPosted: Sat Feb 06, 2010 3:09 pm    Post subject: Exclude records present in File1 from File2 - DFSORT/ICETOOL Reply with quote

Hi,

Can anyone please give me the solution for the below requirement.

File1 has agent nos in Column 1 and in Column 51. The agent no field is 5 digits.

File2 has agents nos starting from Column 2 and is 5 digits.

I have to OMIT the agent nos present in File1 from File2. and the output can be in File2 itself. I have to exlcude all the agents present in File1 in column1 and in column 51 from File2.

Thanks
Sahana
Back to top
View user's profile Send private message
papadi
Supermod


Joined: 20 Oct 2009
Posts: 594
Topics: 1

PostPosted: Sat Feb 06, 2010 3:29 pm    Post subject: Reply with quote

Quote:
and the output can be in File2 itself.
It will help us help you if you post some sample data from both input files and the output you want when this input data is processed.

What should happen when one of the agent numbers in file1 is in file2 and the other is not?

What should be the format of the output (which should not be written in file2)?
_________________
All the best,

di
Back to top
View user's profile Send private message
SAHANA
Beginner


Joined: 18 Jul 2007
Posts: 18
Topics: 5
Location: United Kingdom

PostPosted: Sun Feb 07, 2010 6:14 am    Post subject: Reply with quote

File1:

Column1 and column 51 in File1 has the agent nos. i.e. for example A0001 and A0002

A0001 SAHANA A0002 SADHANA
A0003 SATHYA A0004 SEKAR

File2:

File2 has agent nos in column1..

A0001 SAHANA
A0004 SEKAR
A0003 SATHYA
A0008 SUJAY
A0006 SUJAN

A0001 and A0004 agent nos present in File1 have to be excluded from File2 since these agent nos are present in File2 as well...

So the output File2 should have records like below...

File2:

A0008 SUJAY
A0006 SUJAN

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


Joined: 01 Feb 2007
Posts: 1075
Topics: 7
Location: At Home

PostPosted: Sun Feb 07, 2010 10:06 am    Post subject: Reply with quote

A0003 is in both files so what happens to that? You say only A0001 and A0004 get dropped but you are not showing A0003 in your output.

You say the output can be 'file 2 itself' - if you follow the threads in the forum you would know that that is a BAD thing to do.
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.
Back to top
View user's profile Send private message
SAHANA
Beginner


Joined: 18 Jul 2007
Posts: 18
Topics: 5
Location: United Kingdom

PostPosted: Mon Feb 08, 2010 7:31 am    Post subject: Reply with quote

Hi All,

I am really sorry I missed out to say that A0003 should not be in File2.

I will be cautious in the future while I post.

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


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Mon Feb 08, 2010 11:45 am    Post subject: Reply with quote

SAHANA,

The following DFSORT jcl will give you the desired results. Since you need to validate the account numbers in pos 1 and 51 , we concatenate the same file twice and using when=group we propagate the file number and key on to end of record and then sort on the common key.

Using another when=group we push the file number when there is match and get the desired results using an include on OUTFIL

I assumed that your input is FB recfm and 80 bytes LRECL

Code:

//STEP0100 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*   
//SORTIN   DD *         
$$$               
//         DD DSN=Your file1 with actno at 1 and 51,DISP=SHR
//         DD *
$$$               
//         DD DSN=Your file1 AGAIN with actno at 1 and 51,DISP=SHR
//         DD *
$$$             
//         DD DSN=your file2 with act no at 1,DISP=SHR
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(81:ID=1)),
  IFTHEN=(WHEN=(81,1,ZD,EQ,1),OVERLAY=(83:51,5)),                   
  IFTHEN=(WHEN=(81,1,ZD,GT,1),OVERLAY=(83:01,5))                   
  SORT FIELDS=(83,5,CH,A),EQUALS                                   
 
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(88:SEQNUM,8,ZD,RESTART=(83,5))),
  IFTHEN=(WHEN=GROUP,BEGIN=(88,8,ZD,EQ,1),PUSH=(82:81,1))           
  OUTFIL INCLUDE=(81,2,ZD,EQ,33,AND,1,3,CH,NE,C'$$$'),BUILD=(1,80) 
//*

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


Joined: 18 Jul 2007
Posts: 18
Topics: 5
Location: United Kingdom

PostPosted: Thu Feb 11, 2010 8:18 am    Post subject: Reply with quote

Hi Kolusu,

File1 is 100 bytes LRECL and File2 is 80bytes LRECL.

Records in the File1 are like below. Agent nos are present in Column 1 and 51.
Code:

A0001 SAHANA                                A0002 SADHANA
A0003 SATHYA                                A0004 SEKAR

Records in the FIle2 are like below. Record Layout of File1 and File2 are different.
[code:1:7e8f8b35fa]
A00012 & & 0012478135219577481117122 &
A00102
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

PostPosted: Thu Feb 11, 2010 11:32 am    Post subject: Reply with quote

SAHANA,

You could have saved a lot of time had you been clear with the requirements. You canNOT concatenate different FB LRECL files. Please provide detailed information on what you're trying to accomplish. Do not make people guess what you mean. This will give you a much better chance of getting a good answer to your question.

Any use the following DFSORT JCL which will give you the desired results

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=Your 100 byte FB file,DISP=SHR
//SORTOUT  DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)         
//HDR      DD DSN=&&HD,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)         
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                                 
  OUTFIL BUILD=(1,5,/,51,5,80:X)                                   
  OUTFIL FNAMES=HDR,REMOVECC,NODETAIL,BUILD=(80X),HEADER1=('$$$') 
//*
//STEP0200 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD DSN=&&HD,DISP=SHR,VOL=REF=*.STEP0100.HDR   
//         DD DSN=&&T1,DISP=SHR                         
//         DD DSN=&&HD,DISP=SHR,VOL=REF=*.STEP0100.HDR   
//         DD DSN=your 80 byte FB file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(81:ID=1)) 
  SORT FIELDS=(1,5,CH,A),EQUALS                                     
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(83:SEQNUM,8,ZD,RESTART=(1,5))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(83,8,ZD,EQ,1),PUSH=(82:81,1))           
  OUTFIL INCLUDE=(81,2,ZD,EQ,22,AND,1,3,CH,NE,C'$$$'),BUILD=(1,80)   
//*

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


Joined: 18 Jul 2007
Posts: 18
Topics: 5
Location: United Kingdom

PostPosted: Thu Mar 18, 2010 8:55 am    Post subject: Reply with quote

Thanks Kolusu.. It worked!!

But there was a change in requirement.. The client wanted me to reverse the logic of what was stated earlier.. i.e.

File1 has below records:
Code:

A0001 SAHANA      A0002 SADHANA
A0003 SATHYA      A0004 SEKAR

File2 has below records:
Code:

A0001 SAHANA
A0004 SEKAR
A0003 SATHYA
A0008 SUJAY
A0006 SUJAN

So the output file i.e. File2 should have only the below records:
Code:

A0001 SAHANA
A0004 SEKAR
A0003 SATHYA

I changed the JCL like below and it worked. Thanks a lott!
Code:

//SYSIN    DD *
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(81:ID=1))
  SORT FIELDS=(1,5,CH,A),EQUALS
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(83:SEQNUM,8,ZD,RESTART=(1,5))),
  IFTHEN=(WHEN=GROUP,BEGIN=(83,8,ZD,NE,1),PUSH=(82:81,1))
  OUTFIL INCLUDE=(81,2,ZD,EQ,22,AND,1,3,CH,NE,C'$$$'),BUILD=(1,80)
//*
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