Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Copying records from a file based on records in another file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Aug 26, 2009 4:11 pm    Post subject: Copying records from a file based on records in another file
Reply with quote

Hello all,

I have two input files (say file1 and file2). Both the input files have same record layout. Let us assume a record length of 10 bytes each. But the input files are in sorted order. I want to copy the records from file1 into a output file (say file3) based on the following criteria,

1) If col1 of file1 matches with col1 of file2(any record, not just the same record number), then copy only if col2 not equal to zeroes. [assuming col1 occupies 3-5 pos and col2 occupies 7,8 pos]

2) If col1 of file1 doesn't match with col1 of file2, then copy the record.

For Example, Consider the following sample files,

FILE1
-------

AAAAAAA32A
AAAABAA12A
AAAACAA55A
AAAADAA23A
AAABAAA23A
AAABBAA13A
AAABDAD23A
AAADAAS43A
AABAAAD43A
AACEAAD44A


FILE2
-------
AAAAAAA32A
AAAABAA00A
AAAACAA55A
AAABBAA00A
AAABDAD23A

DESIRED OUTPUT : FILE3
------------------
AAAAAAA32A
AAAACAA55A
AAAADAA23A
AAABAAA23A
AAABDAD23A
AAADAAS43A
AABAAAD43A
AACEAAD44A

I was reading through ICETOOL options to find out ways to do this. But could not find any clue as in how to start with control card to process records by comparing records in one file with records in another file. That too not the same record number necessarily.

Can anyone pls advice me if this is feasible using DFSORT /ICETOOL ???
Back to top
View user's profile Send private message

rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Aug 26, 2009 9:34 pm    Post subject:
Reply with quote

Dear all,

One of my friend suggested me to read through SPLICE command and thanks to him, I have achieved this result :-)

Thank you all..
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Aug 27, 2009 12:01 am    Post subject:
Reply with quote

Dear all,

Following is the sort card i finally used..

Code:
//TOOLIN   DD *                                                     
  COPY FROM(IN1) TO(T1) USING(CTL1)                                 
  COPY FROM(IN2) TO(T2) USING(CTL2)                                 
   SPLICE FROM(CONCT) TO(OUTPUT) ON(1,25,CH) WITH(151,1) WITH(153,1)
     USING(CTL3) KEEPNODUPS                                         
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,OUTREC=(1,150,152:C'11'),OMIT=(17,6,ZD,GT,XXXXXX)
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T2,OUTREC=(1,150,88,1,152:C'22')                   
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUTPUT,INCLUDE=(152,2,ZD,EQ,11,OR,151,1,ZD,NE,0,AND,
  152,2,ZD,EQ,12),OUTREC=(1,150)       


This SORT card works fine as per my requirement when the splicing field (pos 1 - 25) has unique values..

But when the first 25 bytes has duplicate records in any case then the duplicate record is not getting filtered in the output. Can anybody advice how this can be achieved for duplicate records as well ???
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Aug 27, 2009 12:59 am    Post subject: Reply to: Copying records from a file based on records in an
Reply with quote

rockish,

Does both files have duplicates? or is it just 1 file which has duplicates? Splice can handle "one to one" "one to many" match but has a limitation with "many to many" match.

Please provide the following details

1. What is the LRECL and RECFM of File1
2. What is the Key position and format in file1
3. What is the LRECL and RECFM of File2
4. What is the Key position and format in file2
5. Which file has duplicates?
6. What is lrecl and recfm of the output file?
7. What are the fields that needs to picked from file1 and file2
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Aug 27, 2009 1:23 am    Post subject:
Reply with quote

Hi Kolusu,

I will first answer your 7 questions before explaining for the first question on duplicates.

1. What is the LRECL and RECFM of File1
LRECL=150,RECFM=FB
2. What is the Key position and format in file1
Key Position=1:25, Format=9(25)
3. What is the LRECL and RECFM of File2
LRECL=150,RECFM=FB
4. What is the Key position and format in file2
Key Position=1:25, Format=9(25)
5. Which file has duplicates?
Both the file can have duplicates. More explanation below.
6. What is lrecl and recfm of the output file?
LRECL=150,RECFM=FB
7. What are the fields that needs to picked from file1 and file2
All the 150 bytes from file1 is picked for output. File2 content not required in the output.

Now, to explain in detail about the duplicate records. Ideally both the files can have duplicates. As already mentioned, it is only the records from file 1 that would be getting picked into the output file. The various cases possible are,

Case 1 : 1 Instance in File 1, 1 instance in File 2
Desired Output : Record from file 1 getting picked for output

Case 2 : 1 instance in File 1, more than one in File 2
Desired Output : The only record from file 1 getting picked for output

Case 3 : More than one instance in File 1, only one instance in file 2
Desired Output : All the instances in file 1 getting picked for output

Case 4 : More than one instance in both file1 and file2
Desired Output : All the instances from file 1 getting picked for output irrespective of the exact number of occurrences.

I hope that gives you some understanding of what I want to achieve !!

Please revert in case of any concerns.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Aug 27, 2009 2:13 am    Post subject:
Reply with quote

rockish,

Based on your requirements, the following DFSORT/ICETOOL JCl will give you the desired results. The trick here is to eliminate the duplicates in file 2 as we concerned only on picking the records from file1.

1. First sort operation eliminates duplicates from file2 and creates the file with unique keys and an indicator '22' in pos 151

2. Second copy operation just copies the file 1 with an indicator at pos 151

Once spliced the matching records all will have '12' as indicator and we pick them using an include cond and get rid of the indicator

Code:

//STEP0100 EXEC PGM=ICETOOL   
//TOOLMSG  DD SYSOUT=*       
//DFSMSG   DD SYSOUT=*       
//IN1      DD DSN=your file1 FB 150 byte file,DISP=SHR
//IN2      DD DSN=your file2 FB 150 byte file,DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)   
//OUT      DD SYSOUT=*                                           
//TOOLIN   DD *                                                 
  SORT FROM(IN2) USING(CTL1)                                     
  COPY FROM(IN1) USING(CTL2)                                     
  SPLICE FROM(T1) ON(1,25,CH) TO(OUT) -                         
  WITHALL WITH(1,150) WITH(151,1) KEEPNODUPS USING(CTL3)         
//CTL1CNTL DD *                                                 
  SORT FIELDS=(1,25,CH,A)                                       
  OUTREC OVERLAY=(151:C'22')                                     
  OUTFIL FNAMES=T1,REMOVECC,                                     
  SECTIONS=(1,25,TRAILER3=(1,25,151:151,2))                     
//CTL2CNTL DD *                                                 
  OUTFIL FNAMES=T1,OVERLAY=(151:C'11')                           
//CTL3CNTL DD *                                                 
  OUTFIL FNAMES=OUT,INCLUDE=(151,2,ZD,EQ,12),BUILD=(1,150)       
/*
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Aug 27, 2009 8:20 am    Post subject:
Reply with quote

OMG.. again, my DFSORT Release 14 not supporting this command i guess !!!!

Please find my error message below,

Code:
            SECTIONS=(1,25,TRAILER3=(1,25,151:151,2))
            \                                 
ICE005A 0 STATEMENT DEFINER ERROR             


I got a similar message for using PDC and Frank told me it is because my site version doesn't support it :-(
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 27, 2009 8:41 am    Post subject:
Reply with quote

Hello,

Quote:
my DFSORT Release 14 not supporting this command i guess

Not only is the release on your system old, please note:
Quote:
Note that z/OS 1.4 is out of service.
which means very old.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Thu Aug 27, 2009 9:53 am    Post subject: Reply to: Copying records from a file based on records in an
Reply with quote

rockish,

change your CTL1CNTL control cards to the following and re-run your job
Code:

//CTL1CNTL DD *
  SORT FIELDS=(1,25,CH,A)
  SUM FIELDS=NONE
  OUTFIL FNAMES=T1,OUTREC=(1,125,151:C'22')
/*
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Aug 27, 2009 2:49 pm    Post subject:
Reply with quote

Thanks for your reply,

My output was not what I expected :-(

I think here the problem will come for the following case,
File 1 - Multiple instance of the value.
File 2 - No instance of the value.

In such a situation I expect the record from File 1 to be written. But here the splicing will happen among the records in file 1 and ending in losing a record..
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Aug 27, 2009 3:22 pm    Post subject:
Reply with quote

As i have missed few of the points that I mentioned in the first post, when I answered your questions, there has been some confusion.

I would rather summarize my requirement one more time.. I actually have a cobol program. Which processes two files to generate a output file.

Both the input files are RECFM-FB and LRECL-150
Desired output file also RECFM-FB and LRECL-150

Sort key of both the input files: 1-25 (record type 9(25))

The program does the following process,
1) Read the first record from file1 and file2.
2) Compare the records based on the key (first 25 bytes, let us call it as field 1)
3) If field1 of file 1 < field1 of file 2, read the next record from file 1.
4) If the field1 of file 1 > field1 of file 2, check if the field3 (pos 17,6) from file 2 is greater than a 00 or not. If greater, write the record from file2 in to the output file. If not skip the record. Read the next record from file 2.
5) If field 1 of file1 = field 1 of file2, check for two conditions,
(i) is field 3 (17,6) from file 2 is greater than 0 ?
(ii) is filed 4 (88,1) from file 1 is not equal to zero ?
If both the conditions are satisfied, write the record from file2. If not skip and read the next record from file 1 and file2.
6) Continue this until EOF is reached.
7) If file1 EOF is reached before EOF of file2, write all the records there after from file2, if field 3 is greater than 0.

I felt this can be replaced using ICETOOL. The JCL I used is available below,

Code:
//T1       DD DSN=&&T1,DISP=(,PASS),UNIT=TMPDA,SPACE=(TRK,(5,5))       
//T2       DD DSN=&&T2,DISP=(,PASS),UNIT=TMPDA,SPACE=(TRK,(5,5))       
//CONCT    DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,DELETE)                 
//         DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,DELETE)                 
//TOOLIN   DD *                                                       
  COPY FROM(IN1) TO(T1) USING(CTL1)                                   
  COPY FROM(IN2) TO(T2) USING(CTL2)                                   
   SPLICE FROM(CONCT) TO(OUTPUT) ON(1,25,CH) WITH(151,1) WITH(153,1) -
     USING(CTL3) KEEPNODUPS                                           
//CTL1CNTL DD *                                                       
  OUTFIL FNAMES=T1,OUTREC=(1,150,152:C'11'),OMIT=(17,6,ZD,GT,XXXXXX)   
//CTL2CNTL DD *                                                       
  OUTFIL FNAMES=T2,OUTREC=(1,150,88,1,152:C'22')                       
//CTL3CNTL DD *                                                       
  OUTFIL FNAMES=OUTPUT,INCLUDE=(152,2,ZD,EQ,11,OR,151,1,ZD,NE,0,AND,   
  152,2,ZD,EQ,12),OUTREC=(1,150)                                       


This code works fine, if there are no duplicate records in the first 25 bytes in both the files. But now that my input file (both file1 and file2) can hold duplicates, I wish to enhance this JCL to support that as well.

Could you pls suggest if that is feasible ??

Please revert if you have any doubts over my requirement.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Aug 28, 2009 1:48 am    Post subject: Reply to: Copying records from a file based on records in an
Reply with quote

rockish,

wow , I don't see a point in providing you with an answer when you code your own logic to get the desired results and point out that my solution does NOT work.

Did you even come close to copying my code as is? The job I have provided earlier completely satisfies all the conditions you mentioned in your last post. Now you change the requirement completely.

You don't even pay attention to how I used DISP=MOD instead of concatenating the two temp files. If you are seeking help from us, try to use the solution provided instead of coming up with your own logic and asking us to correct it.

Isn't your key(1,25) overlapping the field 3 (17,6) to check?

If you need help , you need to show me relevant fields and format of the fileds with examples for both inputs and outputs. cover all requirements in one single post, don't keep on changing the requirements as I have my regular job to attend.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Aug 28, 2009 2:45 am    Post subject:
Reply with quote

Kolusu,

First of all, I am terribly sorry for troubling you.

Actually I was trying to clarify the concept from experts and code a little on my own. That is why I was quoting simple examples which is similar to my actual problem statement. And based on your advice, I was coding for the actual files.

Regarding the usage of MOD and avoiding the concatenation, I swear I replaced the concatenation with a single DD usage.. But the only mistake I made was I dint note the (MOD,PASS) and used (,PASS) and that is why I got undesired results.. Now after I made that change also to the code I had, it works :-)

The JCL that I pasted you in the most recent post was a old one that I had working for unique records before I got first comments from you.. Though I posted that, I was sincerely trying based on your suggestions.

Anyways, after testing your temper, I have somehow managed to achieve what I wanted to. My requirement is now working, thanks to your suggestion. All the troubles were only because of that MOD which I failed to note and you correctly pointed me out..

Finally, thanks once again for helping me out. And a BIG SORRY one more time for troubling you by not being clear on my requirements.

I assure you, I would be 200% clear in my future post.

Thanks for now and have a nice day :-)
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Aug 28, 2009 10:01 pm    Post subject:
Reply with quote

rockish,

I don't mind answering questions as I long as I get the requirement clearly. Frank and my self answer questions related to DFSORT across 5 or 6 helpboards and via email apart from our regular work. It gets tiring when we have to go back and forth on the same topic but with a different requirement each time. Of course frank has lot of patience but I just happen to snap once in a while. sorry about that
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Aug 28, 2009 10:59 pm    Post subject:
Reply with quote

You need not be sorry at all :-)

It was totally my mistake that time and it would not be repeated in future for sure :-)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us