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
 

 

Matching & non matching on multiple keys

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
rj007

New User


Joined: 08 Apr 2007
Posts: 7
Location: NJ

PostPosted: Mon Apr 09, 2007 12:35 pm    Post subject: Matching & non matching on multiple keys
Reply with quote

I have to write matching & non matching file, I need to consider 4 fields as indicated below in files.

File 1 (LRECL =70)
Code:
Field 1      Field 2           Field 3             Field 4
length 5     lenght 6          length 1            length 5

12345        AAAAAA              1                 12345
12346        BBBBBB              1                 12345
12346        BBBBBB              2                 12345
12346        BBBBBB              3                 12345
12346        BBBBBB              4                 12345
12347        BBBBBB              1                 12345

File 2 (LRECL =80,additional fields at end)
Code:
Field 1      Field 2           Field 3             Field 4
length 5     lenght 6          length 1            length 5

12345        AAAAAA              1                 12345 
12345        AAAAAA              1                 12345 
12346        BBBBBB              1                 12345 
12346        BBBBBB              2                 12345 
12346        BBBBBB              3                 12345 
12346        BBBBBB              4                 12345 

I want output like

Output file 1:
Code:
12345        AAAAAA              1                 12345
12346        BBBBBB              1                 12345
12346        BBBBBB              2                 12345
12346        BBBBBB              3                 12345
12346        BBBBBB              4                 12345

Output file 2:
Code:
12345        AAAAAA              1                 12345

File 2 can contain dups on these keys

I read in forum about such problem of matching & non matching could be done with SPLICE but I have not seen Matching & non matching 4 keys example like my problem & I checked sort tricks but could not figure out solution.

Can some one help me on this ?

Thanks in advance !!
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Apr 09, 2007 2:33 pm    Post subject:
Reply with quote

rj007,

Following could be useful to you -


Code:

//STEP1  EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILE1 DD *
12345AAAAAA112345
12346BBBBBB112345
12346BBBBBB212345
12346BBBBBB312345
12346BBBBBB412345
12347BBBBBB112345
/*
//FILE2 DD *
12345AAAAAA112345
12345AAAAAA112345
12346BBBBBB112345
12346BBBBBB212345
12346BBBBBB312345
12346BBBBBB412345
/*
//TEMPFILE DD DSN=&&TEMP,DISP=(MOD,PASS,DELETE),
//       LRECL=80,RECFM=FB,SPACE=(TRK,2,RLSE)
//*
//OUTPUT DD SYSOUT=*
//DUPS   DD SYSOUT=*
//TOOLIN DD *
  COPY FROM(FILE1) TO(TEMPFILE)
  COPY FROM(FILE2) TO(TEMPFILE)
  SELECT FROM(TEMPFILE) TO(OUTPUT) FIRSTDUP     -
                 ON(1,5,CH) -      * FIRST FIELD
                 ON(6,6,CH)  -     * SECOND FIELD
                 ON(12,1,CH) -     * THIRD FIELD
                 ON(13,5,CH) -     * FOURTH FIELD
                 DISCARD(DUPS)
/*



Output (Matched keys)-

Code:
12345AAAAAA112345
12346BBBBBB112345
12346BBBBBB212345
12346BBBBBB312345
12346BBBBBB412345



Dups (duplicate records)

Code:

12345AAAAAA112345
12345AAAAAA112345
12346BBBBBB112345
12346BBBBBB212345
12346BBBBBB312345
12346BBBBBB412345
12347BBBBBB112345
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Mon Apr 09, 2007 8:36 pm    Post subject:
Reply with quote

rj007.

SPLICE with multiple keys is just like SPLICE with one key except that you use multiple ON fields for the multiple keys, e.g.

ON(1,5,CH) ON(9,6,CH) ...

If you want more specific help, you need to explain the "rules" for going from input to output. It's not clear if the record in output file2 is from input file1 or input file2. You need to put something in the records for your example that indicates which file a record comes from and you need to explain which input records go to which output files and why. Also, give the RECFM of the input files, and the starting position of each field.
Back to top
View user's profile Send private message
rj007

New User


Joined: 08 Apr 2007
Posts: 7
Location: NJ

PostPosted: Tue Apr 10, 2007 3:17 am    Post subject:
Reply with quote

rj007 wrote:
Hi Frank,

I have rewritten this, Let me know if it makes things clear.


File 1 (LRECL =70)

Code:
Field 1 Field 2 Field 3 Field 4
length 5 lenght 6 length 1 length 5
(1-5) (20,6) (30,1) (40,5)

12345 AAAAAA 1 12345
12346 BBBBBB 1 12345
12346 BBBBBB 2 12345
12346 BBBBBB 3 12345
12346 BBBBBB 4 12345
12347 BBBBBB 1 12345

File 2 (LRECL =80,additional fields at end)
Field 1 Field 2 Field 3 Field 4
length 5 lenght 6 length 1 length 5
(1-5) (20,6) (30,1) (40,5)

12345 AAAAAA 1 12345
12345 AAAAAA 1 12345
12346 BBBBBB 1 12345
12346 BBBBBB 2 12345
12346 BBBBBB 3 12345
12346 BBBBBB 4 12345

I want output like

Output file 1

(Records from File 1 that matched with File 2 on all Field 1, Field2, Field 3, Field 4):
Code:
12345 AAAAAA 1 12345
12346 BBBBBB 1 12345
12346 BBBBBB 2 12345
12346 BBBBBB 3 12345
12346 BBBBBB 4 12345

Output file 2:
(Records from File 1 has no matched with File 2 on any of Field 1, Field2, Field 3, Field 4):
Code:
12347 BBBBBB 1 12345

Output file 3:
(Records from File 2 has no matched with File 1 on any of Field 1, Field2, Field 3, Field 4):
Code:
12345 AAAAAA 1 12345

File 2 can contain dups on any of these keys


Thanks in advance !!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue Apr 10, 2007 5:47 am    Post subject:
Reply with quote

It's still not clear what you want.

You say
Quote:
File 2 can contain dups on any of these keys


Does that mean that File1 cannot contain dups on the keys?

You show:

Quote:

Output file 3:
(Records from File 2 has no matched with File 1 on any of Field 1, Field2, Field 3, Field 4):
Code:
12345 AAAAAA 1 12345


but this record matches all of the fields in the first record of file1. You happen to have two of these records in file2, but they both match the first record in file1. It's not clear how you want to match these dups in file2 with the records in file1. For example what would you want for output if you had the following in file1 and file2:

file1
12345 AAAAAA 1 12345 F1R1
12345 BAAAAA 1 12345 F1R2
12345 CAAAAA 1 12345 F1R3
12345 FAAAAA 1 12345 F1R4

file2
12345 AAAAAA 1 12345 F2R1
12345 BAAAAA 1 12345 F2R2
12345 BAAAAA 1 12345 F2R3
12345 CAAAAA 1 12345 F2R4
12345 CAAAAA 1 12345 F2R5
12345 CAAAAA 1 12345 F2R6
12345 DAAAAA 1 12345 F2R7
12345 DAAAAA 1 12345 F2R8
12345 EAAAAA 1 12345 F2R9
12345 EAAAAA 1 12345 F2R10
12345 EAAAAA 1 12345 F2R11
12344 AAAAAA 1 12345 F2R12
Back to top
View user's profile Send private message
rj007

New User


Joined: 08 Apr 2007
Posts: 7
Location: NJ

PostPosted: Tue Apr 10, 2007 7:59 am    Post subject:
Reply with quote

Hi Frank,

Rule for File 1:-
1. Atleast one fields would be diff then other records in file

with your example, I want following in output

File 1:-

12345 AAAAAA 1 12345 F1R1
12345 BAAAAA 1 12345 F1R2
12345 CAAAAA 1 12345 F1R3

File 2:-

12345 FAAAAA 1 12345 F1R4


File 3:-

12345 CAAAAA 1 12345 F2R5
12345 CAAAAA 1 12345 F2R6
12345 DAAAAA 1 12345 F2R7
12345 DAAAAA 1 12345 F2R8
12345 EAAAAA 1 12345 F2R9
12345 EAAAAA 1 12345 F2R10
12345 EAAAAA 1 12345 F2R11
12344 AAAAAA 1 12345 F2R12


Thanks in advance !!
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Tue Apr 10, 2007 10:49 pm    Post subject:
Reply with quote

I think you also want the following in output file3:

12345 BAAAAA 1 12345 F2R3

This is rather tricky, but 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/70)
//IN2 DD DSN=...  input file2 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD DSN=...  output file1 (FB/70)
//OUT2 DD DSN=...  output file2 (FB/70)
//OUT3 DD DSN=...  output file3 (FB/80)
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
SELECT FROM(IN2) TO(T1) ON(1,44,CH) FIRST USING(CTL2)
SELECT FROM(T1) TO(OUT1) ON(1,44,CH) FIRSTDUP DISCARD(OUT2) -
  USING(CTL3)
COPY FROM(IN1) TO(T2) USING(CTL4)
SORT FROM(IN2) TO(T2) USING(CTL5)
SPLICE FROM(T2) TO(OUT3) ON(1,44,CH) KEEPNODUPS KEEPBASE -
  WITHALL WITH(45,45) USING(CTL6)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(71:C'1')
/*
//CTL2CNTL DD *
  INREC BUILD=(1,44,71:C'2')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,BUILD=(1,70)
  OUTFIL FNAMES=OUT2,INCLUDE=(71,1,CH,EQ,C'1'),
    BUILD=(1,70)
/*
//CTL4CNTL DD *
  INREC OVERLAY=(89:C'BB')
/*
//CTL5CNTL DD *
  OPTION EQUALS
  SORT FIELDS=(1,44,CH,A)
  OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,44),89:C'VV')
/*
//CTL6CNTL DD *
  OUTFIL FNAMES=OUT3,
    INCLUDE=(89,2,CH,EQ,C'VV',OR,
             (89,2,CH,EQ,C'VB',AND,81,8,ZD,GT,+1)),
    BUILD=(1,80)
/*
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Matching and unmatching records scdinesh DFSORT/ICETOOL 11 Tue Feb 07, 2017 12:41 am
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm


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