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
 

 

Selecting the matching record using OR condition

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

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Thu Apr 21, 2011 10:53 pm    Post subject: Selecting the matching record using OR condition
Reply with quote

Hello Team,

I have a requirement in my current project and looking to solve it without using a program.

I have two files, Say File1 and File2.
Requirement is:- Take 1st record from File1 and scan the File2 one by one record and Fetch the matching record from File2 and write it into a output file File3. Do not stop if we found the match in file2. Scan the complete file2 and write all the matching records from File2.

Please note- We would be having duplicates or SPACES on all the below mentioned fields. We should not consider match on Spaces and keep moving till the end of File2.

Match Conditions are--
IF Field-1A = Field-2A OR Field-1B = Field-2B OR Field-1C = Field-2C OR Field-1A = Field-2D OR Field-1B = Field-2E
-------------------------------------------------------------------

File1 has LRECL=150, I have mentioned THREE specific fields which we used for matching creteria as below.....
Field-1A==> Position=(13~21), Length=9
Field-1B==> Position=(22~33), Length=12
Field-1C==> Position=(39~45), Length=7

File2 has LRECL=550, Here, I have mentioned FIVE specific fields which we used for matching creteria as below.....
Field-2A==> Position=(1~9), Length=9
Field-2B==> Position=(10~21), Length=12
Field-2C==> Position=(22~28), Length=7
Field-2D==> Position=(29~37), Length=9
Field-2E==> Position=(38~49), Length=12

Please let me know if you have any concern.

Thanks,
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: Thu Apr 21, 2011 11:00 pm    Post subject:
Reply with quote

Please show an example of the records in each input file (relevant fields only) and what you expect for output. If file1 can have duplicates within it, show that in your example. If file2 can have duplicates within it, show that in your example.

Also, run this job and show the //SYSOUT messages you receive, so I can see what level you're at:

Code:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN    DD    *
    OPTION COPY
/*
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Thu Apr 21, 2011 11:38 pm    Post subject:
Reply with quote

Thanks Frank for looking into it!

File1 Looks like--- I am coping the (first 55 rec. length) only

Code:
BC31234     BRSBFTNU5                        SUUSDAlpha
BC33592     BRSBFTNU5                        SUUSDPSERS
BC31234     BRSBFTNW1                        SUUSDAlpha
BC33592     BRSBFTNW1                        SUUSDPSERS
BC31234     BRSBFTNX9                        SUUSDAlpha
BC33592     BRSBFTNX9                        SUUSDPSERS
BC31234     BRSBFTNZ4                        SUUSDAlpha
04-MLR                                       SUUSD04-ML
04-OER                                       SUUSD04-OE
04-0DR                                       SUUSD04-0D
04-LTR      BLANK BLA                        SUUSD04-LT
BC21538     BRSAAGZG8                        SUGBPBlack
BC35753     BRSAMS7R6                        SUEURBSF -
BC21505     BRSANTS90                 B3MP7R9SUUSDBIEF
BC21780     BRSANTS90                 B3MP7R9SUUSDBlack
BC21805     BRSANTS90                 B3MP7R9SUUSDBGF E
BC21817     BRSANTS90                 B3MP7R9SUUSDBlack
BC35008     BRSANTS90                 B3MP7R9SUUSDDanic
BC30500     BRSAN4P31                 9961670SUAUDiShar
BC30500     BRSAN4P56                 9996166SUAUDiShar
BC30504     SB291WY56AEDFXA0M6V00     B291WY5SUUSDiShar
BC31285     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBGI F
BC31286     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBGI F
BC33897     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBCIF
BC36212     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBlack
BC24286     SB1GPBP77AED000901010     B1GPBP7SUAEDBlack
BC30504     SB1GPBP77AED000901010     B1GPBP7SUAEDiShar
BC31285     SB1GPBP77AED000901010     B1GPBP7SUAEDBGI F
BC31286     SB1GPBP77AED000901010     B1GPBP7SUAEDBGI F


File2 Looks like--- I am coping the (first 55 rec. length) only

Code:
X62071398
BRS4WVG27                   S47127980FR0000131906
BRSBU2TH0                   XB59BVS30
X61866699
X62324326
BRSBQEJ56GB00B4VGWF30B4VGWF3SB3CPT841CNE100000CP9
BRS8UL466                   SB0P6L876TW0003702007
228227104US22822710462285991
SB2QKYZ03US55953Q2021B2QKYZ0
X68878093
BRSBFUJL7                   XB030GJ74
SB09BYK17ZAE000030920B09BYK1
BRSBPZM72                   74144T108US74144T1088
BRSBXPX99                   SB5M73711AU000000SXL4
BRSBU2TA5                   X64277555
SB66FWT34US17313X1789B66FWT3S62767199CNE0000006Y9
S62496369KR70004300096249636
BRSA6THX8GB00B60FJ762B60FJ76S68083617CNE000000DH5
BRS42N553
BRSBQ91J6                   69351T106US69351T1060
XB09C0Z18
BRS8ULAL6                   SB13C2G96TRESLEC00014
S63700207AU000000UXC96370020
SB03PGQ91US1730753837B03PGQ9S61001863INE101A01026
S54763164IT00012232775476316
SB51PJP61BRRDTRACNOR7B51PJP6
SB29TFJ50            B29TFJ5
SB03MS971TRAEREGL91G3B03MS97
BRSBFVA26                   X63668473
SB2374987US17311M7039B237498S69842946CNE0000006K8
BRSBG7X07                   SB61BJY08INE695A01019
SB138NB90LU0253815640B138NB9
SB3CF1G69JP3274280001B3CF1G6
BRSBMNLU2                   SB0684C79PHY569991086

Regarding running of the job you mentioned below--

Code:
//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
RECORD
//SORTOUT DD DUMMY
//SYSIN    DD    *
    OPTION COPY
/*


You can see lot of duplicates in the input file1. Let me know if you looking for anything else.

Should i run this job as it is.. What's this RECORD show above ?

Thanks.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Apr 21, 2011 11:41 pm    Post subject:
Reply with quote

How many records in each file?

"We should not consider match on Spaces " so If a field in file 1 is spaces and the field in file 2 it is to matched against is spaces that would not be considered as a match?
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: Thu Apr 21, 2011 11:51 pm    Post subject:
Reply with quote

Deepak,

Quote:
Should i run this job as it is


Yes. Just run the job as is and show me the //SYSOUT messages. The job just copies one record, but that doesn't matter - it's just a way to print out the DFSORT messages so I can see what level you're at.

Quote:
Please show an example of the records in each input file (relevant fields only) and what you expect for output.


I want you to show me the expected output records for a sample of input records. That will help me understand what you're trying to do. It would be best if you showed input records and expected output that illustrate the various situations you need to deal with.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Fri Apr 22, 2011 12:36 am    Post subject:
Reply with quote

Here is the SYSOUT-

Code:
0 BLOCKSET     COPY  TECHNIQUE SELECTED
0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES AND MORE ,
1,- CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 14:37 ON THU APR 21, 20
      OPTION COPY                                                         001000
H RECORD TYPE IS F - DATA STARTS IN POSITION 1
0 C5-K90025 C6-K90025 C7-K54603 C8-K62201 E9-K51707 C9-BASE   E5-K62201 E7-K6220
0 ICEAM1 INVOCATION ENVIRONMENT IN EFFECT - ICEAM1 ENVIRONMENT SELECTED
1 PARMLIB OPTIONS WERE MERGED WITH INSTALLATION MODULE DEFAULTS
0 SBDEEMUX.STEP10R .        , INPUT LRECL = 80, BLKSIZE = 80, TYPE = F
0 MAIN STORAGE = (MAX,4194304,4181086)
0 MAIN STORAGE ABOVE 16MB = (4063454,4063454)
0 OPTIONS: OVFLO=RC0 ,PAD=RC0 ,TRUNC=RC0 ,SPANINC=RC16,VLSCMP=N,SZERO=Y,RESET=Y,
0 OPTIONS: SIZE=4194304,MAXLIM=1048576,MINLIM=450560,EQUALS=N,LIST=Y,ERET=ABEND,
0 OPTIONS: VIO=N,RESDNT=NONE,SMF=SHORT,WRKSEC=Y,OUTSEC=Y,VERIFY=N,CHALT=N,DYNALO
0 OPTIONS: RESALL=4096,RESINV=0,SVC=109 ,CHECK=N,WRKREL=N,OUTREL=Y,CKPT=N,COBEXI
0 OPTIONS: TMAXLIM=4194304,ARESALL=0,ARESINV=0,OVERRGN=65536,CINV=N,CFW=Y,DSA=0
0 OPTIONS: VLSHRT=N,ZDPRINT=Y,IEXIT=Y,TEXIT=N,LISTX=N,EFS=NONE    ,EXITCK=W,PARM
0 OPTIONS: HIPRMAX=OPTIMAL,DSPSIZE=0   ,ODMAXBF=0,SOLRF=N,VLLONG=N,VSAMIO=N,MOSI
0 OPTIONS: NULLOUT=RC0


File1 records are---

Code:
04-LTR      BLANK BLA                        SUUSD04-LT
BC21538     BRSAAGZG8                        SUGBPBlack
BC35753     BRSAMS7R6                        SUEURBSF -
BC21505     BRSANTS90                 B3MP7R9SUUSDBIEF
BC21780     BRSANTS90                 B3MP7R9SUUSDBlack
BC21805     BRSANTS90                 B3MP7R9SUUSDBGF E
BC21817     BRSBFUJL7                 B3MP7R9SUUSDBlack
BC35008     BRSANTS90                 B3MP7R9SUUSDDanic
BC30500     BRSAN4P31                 9961670SUAUDiShar
BC30500     BRSAN4P56                 9996166SUAUDiShar
BC30504     SB291WY56AEDFXA0M6V00     B291WY5SUUSDiShar
BC31285     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBGI F
BC31286     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBGI F
BC33897     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBCIF
BC36212     SB291WY56AEDFXA0M6V00     B291WY5SUUSDBlack
BC24286     SB1GPBP77AED000901010     B1GPBP7SUAEDBlack


File2 records are---

Code:
BRS4WVG27                   S47127980FR0000131906
BRSAAGZG8                   XB59BVS30
X61866699
BRSBQEJ56GB00B4VGWF30B4VGWF3SB3CPT841CNE100000CP9
BRS8UL466                   SB0P6L876TW0003702007
228227104US2282271046B3MP7R9
SB2QKYZ03US55953Q2021B2QKYZ0
BRSBFUJL7
BRSBFUJL7                   XB030GJ74
SB09BYK17AEDFXA0M6V00B09BYK1
BRSBPZM72                   74144T108US74144T1088
BRSBXPX99                   SB5M73711AEDFXA0M6V00
BRSBU2TA5                   SB1GPBP77

Output file File3 would be looks exactly like File2 ---

Code:
BRSAAGZG8                   XB59BVS30
228227104US2282271046B3MP7R9
BRSBFUJL7
BRSBFUJL7                   XB030GJ74
SB09BYK17AEDFXA0M6V00B09BYK1
BRSBXPX99                   SB5M73711AEDFXA0M6V00
BRSBU2TA5                   SB1GPBP77



Just a FYI... I am trying to extract all the security records from security file(FILE2) related to the Positions we recived in Position file(FILE1).. And All the security records would be fetched if..
=> Position file record's CUSIP(Field-1A) is equal to Security file record's CUSIP(Field-2A) or UNDERLYING CUSIP(Field-2D)
=> Position file record's ISIN(Field-1B) is equal to Security file record's ISIN(Field-2B) or UNDERLYING ISIN(Field-2E)
=> Position file record's SEDOL(Field-1C) is equal to Security file record's SEDOL(Field-2C)

Let me know if you have any concern.

Thanks.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Fri Apr 22, 2011 12:40 am    Post subject:
Reply with quote

If you want we can make use of ICETOOL also, whichever you thing would be good/easier.

@Craq.. Records would be around 7Millions in each files.

Thanks.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Apr 22, 2011 1:54 am    Post subject: Reply to: Selecting the matching record using OR condition
Reply with quote

deepak_munjal,

What is the LRECL and RECFM of both input files ?
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Fri Apr 22, 2011 2:15 am    Post subject:
Reply with quote

Both are flat files, RECFM(FB)--
File1 has LRECL= 150
File2 has LRECL= 550

Thanks.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Fri Apr 22, 2011 3:00 am    Post subject: Reply to: Selecting the matching record using OR condition
Reply with quote

deepak_munjal,

The following DFSORT JCL will give you the desired results.

Code:

//***************************************************************
//* CREATE 3 RECORDS FROM A SINGLE RECORD WITH THE 3 KEY VALUES *
//***************************************************************
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD DSN=Your input 150 lrecl file1,DISP=SHR
//SORTOUT  DD DSN=&&JFB,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)     
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                               
  OUTFIL BUILD=(13,9,/,22,12,/,39,7)                             
//*                                                             
//***************************************************************
//* CREATE 5 RECORDS FROM A SINGLE RECORD WITH THE 5 KEY VALUES *
//***************************************************************
//STEP0200 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD DSN=Your input 550 lrecl file2,DISP=SHR
//SORTOUT  DD DSN=&&JFA,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)     
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                               
  OUTREC OVERLAY=(551:SEQNUM,8,ZD)                               
  OUTFIL BUILD=(1,558,01,09,/,                                   
                1,558,10,12,/,                                   
                1,558,22,07,/,                                   
                1,558,29,09,/,                                   
                1,558,38,12)                                     
//*                                                 
//***************************************************************
//* MATCH TREATING THE 12 BYTE KEY FROM BOTH FILES              *
//***************************************************************
//STEP0300 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//INA      DD DSN=&&JFA,DISP=SHR                                 
//INB      DD DSN=&&JFB,DISP=SHR                                 
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                 
  JOINKEYS F1=INA,FIELDS=(559,12,A)                             
  JOINKEYS F2=INB,FIELDS=(001,12,A)                             
  REFORMAT FIELDS=(F1:1,558)                                     
  SORT FIELDS=(551,8,CH,A),EQUALS
  OUTREC BUILD=(1,550)                                 
//*                                                             
//JNF1CNTL DD *                                                 
  OMIT COND=(559,12,CH,EQ,C' ')                                   
//*                                                             
//JNF2CNTL DD *                                                 
  OMIT COND=(001,12,CH,EQ,C' ')                                   
  SUM FIELDS=NONE                                               
//*
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 How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm


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