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

COPY INPUT2 BUT OMIT MATCHING RECORDS FOUND IN INPUT1


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

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Sat Jan 31, 2009 1:33 am
Reply with quote

Hi There!

I have 2 input files
INPUTFILE 1 (PS,FB 80-80) looks like this:
(note: dataset names start in COL1 and can be as long as 44 chars)
BAD.DATASET.NAME1
BAD.DATASET.NAME4
BAD.DATASET.NAME11
BAD.DATASET.NAME305


INPUTFILE 2 (Tape file) looks like this:
(note: dataset names start in COL1 and can be as long as 44 chars)
GOOD.DATASET.NAME2 .......greek......... .... ....
BAD.DATASET.NAME1 ...gibberish........... .... ....
BAD.DATASET.NAME305.. ................ anyting. ................ ...
BAD.DATASET.NAME4 . .. ....... ........... .......... ......
GOOD.DATASET.NAME1000 .... ........sa$@#..........
BAD.DATASET.NAME11 ... ........ ........ ......... . whatever ..

DESIRED PROCESSING: Copy all records from inputfile2, omitting records that have matching dataset names from inputfile1

DESIRE OUTPUT:
GOOD.DATASET.NAME2 .......greek......... .... ....
GOOD.DATASET.NAME1000 .... ........sa$@#..........

Can you please help me out with the COPY/OMIT statements to get the desired result. Thanks
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: Sat Jan 31, 2009 2:33 am
Reply with quote

You would use SELECT or SPLICE for this, not OMIT. But I need more information before I can give you the best job for what you want to do.

What is the RECFM and LRECL of input file2?

In your example, there is a match in input file2 for every dsname in input file1. Is that always the case, or can input file1 have a dsname that isn't in input file2 (e.g. BAD.DATASET.NAME12 is in input file1 but not in input file2)?
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Sat Jan 31, 2009 7:58 am
Reply with quote

Frank you're intuitive! That's correct. Records in inputfile1 will always have matching records in inputfile2. So in essence, the resultant output will be an exact copy of inputfile2, but filtering out records that have a match in inputfile1.

that's a copy and omit/exlude operation right?
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: Sat Jan 31, 2009 9:46 pm
Reply with quote

Whether you could do this with OMIT would depend on the number of records in input file1. But SELECT would be a better choice since the number of records in input file1 wouldn't matter. Here's a DFSORT/ICETOOL job using SELECT. I assumed input file2 has RECFM=FB and LRECL=80 (I asked but you didn't say).

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//CON DD DSN=...  input file1 (FB/80)
//    DD DSN=...  input file2 (FB/80)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
SELECT FROM(CON) TO(OUT) ON(1,44,CH) NODUPS
/*
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Mon Feb 02, 2009 7:34 pm
Reply with quote

Sorry about that.
Input file1 = ps,fb,80-80
Input file2 = ps,vb,13544-32760 (tape file)
Out = ps,vb,13544-32760 (disk file)

Input file1 is usually small, will contain only a few records. Only 1-44 cols matters (DSNs). Inputfile1 records (DSNames) will be coded instream in the JCL (eg: //INPUTFILE DD * )

Input file 2 is huge usually around 2K cyl (3390)
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: Mon Feb 02, 2009 10:33 pm
Reply with quote

If input file2 is VB, then the dsname starts in position 5 after the RDW, not in position 1.

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
BAD.DATASET.NAME1
BAD.DATASET.NAME4
BAD.DATASET.NAME11
BAD.DATASET.NAME305
/*
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(,PASS)
//CON DD DSN=...  input file2 (VB/13544)
//    DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//OUT DD DSN=...  output file2 (VB/13544)
//TOOLIN DD *
COPY FROM(IN1) USING(CTL1)
SELECT FROM(CON) TO(OUT) ON(5,44,CH) NODUPS
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,FTOV
/*
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Mon Feb 02, 2009 11:53 pm
Reply with quote

ok i'll give that a shot. will keep you posted on the result. thanks.
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 12:00 am
Reply with quote

one more thing.. could you pls explain the significance of or the reference to: USING(CTL1) and OUTFIL FNAMES=T1,FTOV ?
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: Tue Feb 03, 2009 12:15 am
Reply with quote

Code:

COPY FROM(IN1) USING(CTL1)
...
//CTL1CNTL DD *
   OUTFIL FNAMES=T1,FTOV


The COPY operator tells ICETOOL to copy the IN1 data set using the control statements in CTL1CNTL.

FNAMES=T1 gives the ddname of the output data set (T1).
FTOV converts from FB records to VB records.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 12:35 am
Reply with quote

Im getting this error -
ICE632I 0 SOURCE FOR ICETOOL STATEMENTS: TOOLIN

ICE630I 0 MODE IN EFFECT :STOP

COPY FROM(IN1) USING(CTL1)
ICE605A 2 REQUIRED DD STATEMENT NOT FOUND: CTL1CNTL
ICE602I 0 OPERATION RETURN CODE: 12

ICE630I 2 MODE IN EFFECT: SCAN

SELECT FROM(CON) TO(OUT) ON(5,44,CH) NODUPS
ICE612I 0 NO ERRORS FOUND IN STATEMENT


ICE601I 0 DFSORT ICETOOL UTILITY RUN ENDED - RETURN CODE: 12
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 12:37 am
Reply with quote

nevermind.. typo CTL1CNT
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 12:56 am
Reply with quote

Ran ok CC=0. Only thing is the output records was sorted ascending. We dont desire any sorting. And more records were deleted than expected (There should be only 2 matches/deletions. I checked this manually before running). Thoughts?
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: Tue Feb 03, 2009 1:50 am
Reply with quote

I gave you a solution based on what you told/showed me.

You didn't say anything previously about the order of the output so I didn't code for that. If you don't want any sorting, then you'll need to add a sequence number to the original records, and use another pass to sort them back into order by that sequence number and remove the sequence number.

When you say that you got more records deleted than expected, do you mean with the sample input records you showed or with different input records? I would have to know what your input records looked like, what you ran and what you got for output before I could tell you what was different from what you originally asked for. For example, does input file2 have duplicate dsnames within it? Your example didn't show that so I didn't code for it.

If you want me to give you a solution that works for different data then you originally showed, then you need to show me a better example of your data with all its variations and what you want for output.
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 2:09 am
Reply with quote

I can see the (your) confusion. I should have been more precise with my example and requirement.

- I'd like to avoid adding seq# if possible. Is there no other way (recode, products) ?
- There may be duplicate dsnames within file2. Those should remain untouched. Only delete records that have a match in file1.
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 2:16 am
Reply with quote

I can see the (your) confusion. I should have been more precise with my example and requirement.

- I'd like to avoid adding seq# if possible. Is there no other way (recode, products) ?
- There may be duplicate dsnames within file2. Those should remain untouched. However, if the dups have a matching record in file1 then delete them as well.

Only delete ANY or ALL records that have a match in file1.
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: Tue Feb 03, 2009 2:22 am
Reply with quote

Quote:
There may be duplicate dsnames within file2. Those should remain untouched. Only delete records that have a match in file1.


What if the duplicate dsnames within file2 have a match in file1? Wouldn't you want to delete them rather than having them remain untouched? That's why I asked you to show me a better example of your data with all its variations and what you want for output. That way, I don't have to guess what you want.

If you have a maximum of less than about 590 dsnames in input file1, then we could generate an OMIT statement and avoid sorting. Is that the case?

Otherwise, since you have duplicate dsnames in file2, we would need to use SPLICE, not SELECT. SPLICE, like SELECT, requires that you do a sort so you'd need the seqnum to get the records back in the original order.

I don't know what "recode, products" means.
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Tue Feb 03, 2009 7:59 pm
Reply with quote

"What if the duplicate dsnames within file2 have a match in file1? Wouldn't you want to delete them rather than having them remain untouched?"

> Yes. delete them.

"If you have a maximum of less than about 590 dsnames in input file1, then we could generate an OMIT statement and avoid sorting. Is that the case?"

> Yes
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: Tue Feb 03, 2009 11:13 pm
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/80)
//IN2 DD DSN=...  input file2 (VB)
//CTL2CNTL DD DSN=&&C2,UNIT=SYSDA,SPACE=(TRK,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (VB)
//TOOLIN DD *
COPY FROM(IN1) USING(CTL1)
COPY FROM(IN2) TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=CTL2CNTL,REMOVECC,
   HEADER1=('  OMIT FORMAT=CH,COND=(1,1,NE,1,1,OR, '),
   BUILD=(C'  5,44,EQ,C''',1,44,C''',OR, ',80:X),
   TRAILER1=('  1,1,NE,1,1)')
/*
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Wed Feb 04, 2009 12:43 am
Reply with quote

It works! The stmt set is quite compact and tight, not to mention the BUILD stmt is quite neat.

As you can see there are 4 records in IN1, all 4 have matching dataset names in IN2. One of the records has dups(2) within IN2. Therefore, total deletes expected is 5.

BUILD stmt o/p:
Code:
OMIT FORMAT=CH,COND=(1,1,NE,1,1,OR,                         
5,44,EQ,C'BAD.UBIJ.JDVC.D090123                   ',OR,
5,44,EQ,C'BAD.PPBN.ESPMDB.RES.G6519V00     ',OR,
5,44,EQ,C'BAD.KEVINS.QUICKREF.JCL               ',OR,
5,44,EQ,C'BAD.SHIRLEYT.STMT.CNTL                 ',OR,
1,1,NE,1,1)                                                 


Other Stmts o/p:
Code:
OPTION MSGDDN=DFSMSG,LIST,MSGPRT=ALL,RESINV=0,SORTDD=CTL2,SORTIN=IN2,SORTOUT=OUT,DYNALLOC                                           
SORT FIELDS=COPY                                                           
RECORD TYPE IS V - DATA STARTS IN POSITION 5           


OUTPUT LRECL = 13544, BLKSIZE = 32760, TYPE = VB
INSERT 0, DELETE 5
RECORDS - IN: 3029017, OUT: 3029012
END OF DFSORT

I'm a happy camper icon_smile.gif
Thanks a lot Frank
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 Feb 04, 2009 12:46 am
Reply with quote

Good. I guess I should have asked you the maximum number of records in input file1 in the first place instead of assuming there were too many for an OMIT statement.
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Fri Feb 06, 2009 8:43 pm
Reply with quote

Frank, again thanks for the assistance. I think I know how this works but in your own words, could you please explain the workings of the stmts.
No rush. I know you're a busy guy constantly juggling and managing your time. btw, You can PM me the answer if you like.

Code:
COPY FROM(IN1) USING(CTL1)                             
COPY FROM(IN2) TO(OUT) USING(CTL2)                     
/*                                                     
//CTL1CNTL  DD *                                       
  OUTFIL FNAMES=CTL2CNTL,REMOVECC,                     
   HEADER1=('  OMIT FORMAT=CH,COND=(1,1,NE,1,1,OR, '), 
   BUILD=(C'  5,44,EQ,C''',1,44,C''',OR, ',80:X),       
   TRAILER1=('  1,1,NE,1,1)')                           
/*                                                     
//IN1       DD *                                       
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: Fri Feb 06, 2009 10:10 pm
Reply with quote

We are trying to create an OMIT statement from the constants in file1 to use against the records in file2.

The OMIT statement should look like this:

Code:

   OMIT FORMAT=CH,COND=(1,1,NE,1,1,OR,
      5,44,EQ,'44-byte constant1...',OR,
      5,44,EQ,'44-byte constant2...',OR,
      ...
      1,1,NE,1,1)


The first COPY operator creates this OMIT statement in the CTL2CNTL data set. It uses an OUTFIL statement in CTL1CNTL to do it as follows:

HEADER1 - creates the first line of the OMIT statement.
BUILD - creates a line for each constant in file1.
TRAILER1 - creates the last line of the OMIT statement.

The second COPY operator uses the OMIT statement in CTL2CNTL against input file2.
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Sat Feb 07, 2009 12:39 am
Reply with quote

Thank you. what about 1,1,NE,11 in the header and trailer?
How are records from IN1 written one by one into CTL2?
How are records in IN1 compared to records in CTL2, and matched records filtered out?

As you can tell Im not a heavy SORT user but Im having real interest in this, I should read up on it more. Quite powerful. I can see using it here more often..I'd have to look around our installation & applications that can benefit from this.

Again no rush. Reply whenever you find time.. thanks
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: Sat Feb 07, 2009 12:55 am
Reply with quote

Quote:
Thank you. what about 1,1,NE,11 in the header and trailer?


Those are NOPs to allow the correct syntax to be generated more easily:

Code:

  OMIT FORMAT=CH,COND=(line1,
     line2,
     line3,
     ...
     linen)


It's easier to use a NOP for the first line and last line then to do the coding to put real constants into those lines. So I use 1,1,NE,1,1 for line1 and linen, and the real constants for the other lines. 1,1,NE,1,1 can never be true, so it has no effect on the records omitted - it's a NOP. Just a trick.

Quote:
How are records from IN1 written one by one into CTL2?


HEADER1 writes an output line before the detail lines.
BUILD writes one output line (detail line) for each record in IN1.
TRAILER1 writes an output line after the detail lines.

Quote:
How are records in IN1 compared to records in CTL2, and matched records filtered out?


I assume you mean IN2 here, not IN1. It's the records in IN2 we want to do the compare against - right? For the second COPY operator, USING(CTL2) tells DFSORT to use the DFSORT statements in CTL2CNTL against the input from IN2. CTL2CNTL has the OMIT statement, so the OMIT is evaluated for each IN2 record.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
Kevin Santos

New User


Joined: 27 Jan 2009
Posts: 26
Location: toronto

PostPosted: Sat Feb 07, 2009 1:00 am
Reply with quote

that was quick. thanks frank.
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 INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts How I Found a Bug in a FORTRAN Compiler All Other Mainframe Topics 4
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts VB to VB copy - Full length reached SYNCSORT 8
Search our Forums:

Back to Top