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
 

 

SORTJOIN - Copy Matched and Unmatched to the same File

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

New User


Joined: 19 Oct 2015
Posts: 15
Location: UK

PostPosted: Tue Jan 17, 2017 4:26 pm    Post subject: SORTJOIN - Copy Matched and Unmatched to the same File
Reply with quote

I was wondering if anybody could help me with a minor niggle I have....

I've created a Data Mining job that starts with a list of account numbers and builds up the different characteristics for each account from lots of different input files.

As not all of the accounts have a data on the other files, when I update a field on the base file, I create a new file of matched records and then have to re-merge (sort-dedupe) the original base file with the new file so I've always got the same number of records I started out with :-

Code:
**********************************************************************
//***  MERGE ORDER TOTALS INTO ACCOUNT LIST                             
//**********************************************************************
//STEP180  EXEC PGM=SORT,COND=(0,LT)                                   
//SYSPRINT DD SYSOUT=*                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD DISP=SHR,DSN=MINED.DATA.BASE                             
//SORTJNF2 DD DISP=SHR,DSN=ORDER.TOTALS                                 
//SORTOUT  DD DSN=&&TEMP,DISP=(,PASS),SPACE=(TRK,(4500,1500),RLSE)     
//         DCB=(RECFM=FB,LRECL=323,BLKSIZE=0),                         
//SYSIN    DD *                                                         
  JOINKEYS FILES=F1,FIELDS=(1,16,A)                                     
  JOINKEYS FILES=F2,FIELDS=(1,16,A)                                     
  REFORMAT FIELDS=(F1:1,51;F2:22,5;F1:57,267)                           
  SORT FIELDS=COPY                                                     
/*                                                                     
//**********************************************************************
//***  RE-MERGE UPDATED MINED FILE WITH ORIGINAL MINED FILE             
//**********************************************************************
//STEP190  EXEC PGM=SORT,COND=(0,LT)                                   
//SYSOUT   DD SYSOUT=*                                                 
//SYSUDUMP DD SYSOUT=D                                                 
//SORTIN   DD DISP=SHR,DSN=&&TEMP                                       
//         DD DISP=SHR,DSN=MINED.DATA.BASE                             
//SORTOUT  DD DISP=OLD,DSN=MINED.DATA.BASE                             
//SYSIN    DD *                                                         
  SORT FIELDS=(1,16,CH,A)                                               
  SUM FIELDS=NONE                                                       
/*


Is there any way to code the above in 1 step rather than the 2 that i'm currently using ?

The above works perfectly fine, but I'm now up to 17 of these in my datamining job and it's starting to add quite a bit of time/cpu onto the job when it runs.

Any help would be greatly appreciated.

cheers

Steve
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2253
Location: @my desk

PostPosted: Tue Jan 17, 2017 7:11 pm    Post subject:
Reply with quote

Steve,

Will you have duplicate keys on 1,16 in either of your input data sets here : MINED.DATA.BASE/ORDER.TOTALS? If no, then the below might help.

If the field from ORDER.TOTALS has to be written to output only if you find a match in it, and if you like to retain the original data when no match is found you could do it with a JOIN UNPAIRED,F1 in your first step. Currently you do not have a JOIN statement that means - return only matching records. With JOIN UNPAIRED,F1 you get all the records from F1 plus F2 fields when a match is found.

And you might need an INREC IFTHEN to selectively OVERLAY the F2 field only when a match is found. To know whether a match is found, you might want to use the matchmarker ("?") in your REFORMAT that assumes values "1" - File1 Only, "2" - File2 Only OR 'B' - Both.
Back to top
View user's profile Send private message
Steve Ironmonger

New User


Joined: 19 Oct 2015
Posts: 15
Location: UK

PostPosted: Tue Jan 17, 2017 7:58 pm    Post subject:
Reply with quote

Hi Arun,

there are no duplicate keys on 1,16 in either input files.

Basically, I have a base file and I want to merge other data where the account number matches.


Code:
dsn=MINED.DATA.BASE
acc-num          typ1 typ2 typ3 typ4
0123451234567812,   0,   0,   0,   0
2345672345678923,   0,   0,   0,   0

With

dsn=ORDER.TOTALS.TYPE1
0123451234567812,1234

to create

MINED.DATA.BASE.UPDATE1
0123451234567812,1234,   0,   0,   0
2345672345678923,   0,   0,   0,   0

then merge MINED.DATA.BASE.UPDATE1 with

dsn=ORDER.TOTALS.TYPE2
0123451234567812,4321
2345672345678923,5432

to create

dsn=MINED.DATA.BASE.UPDATE2
0123451234567812,1234,4321,   0,   0
2345672345678923,   0,5432,   0,   0


My issue is that after the first merge, the second record is not copied into MINED.DATA.BASE.UPDATE1 as it wasn't matched on the first itteration.

cheers

Steve
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2253
Location: @my desk

PostPosted: Tue Jan 17, 2017 8:13 pm    Post subject:
Reply with quote

Steve,

Please give the JOIN UNPAIRED,F1 a try (explained above) and let us know how it goes.
Back to top
View user's profile Send private message
Steve Ironmonger

New User


Joined: 19 Oct 2015
Posts: 15
Location: UK

PostPosted: Tue Jan 17, 2017 8:54 pm    Post subject:
Reply with quote

Arun,

that's copying both records but leaving the typ1 field of the unmatched record as blank :-

Code:
MINED.DATA.BASE.UPDATE1
0123451234567812,1234,   0,   0,   0
2345672345678923,    ,   0,   0,   0


I need it to retain it's original value.

I'll have a play with INREC IFTHEN when I get a chance....

I'm sure I saw the answer when googleing a while ago, but didn't note it down.....

cheers

Steve
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2253
Location: @my desk

PostPosted: Tue Jan 17, 2017 11:05 pm    Post subject:
Reply with quote

Yes, you should retain the original value in REFORMAT and then in the INREC IFTHEN, evaluate if it is a matched record, and then OVERLAY the data from F2. So only for matched records the field will get overwritten. For non-matching records, the field will remain unchanged.

This is untested, but it would look something like this:
Code:
  JOINKEYS FILES=F1,FIELDS=(1,16,A)                       
  JOINKEYS FILES=F2,FIELDS=(1,16,A)                       
  REFORMAT FIELDS=(F1:1,267,F2:22,5,?)                     
  SORT FIELDS=COPY                                         
  INREC IFOUTLEN=267,                                     
        IFTHEN=(WHEN=(273,1,CH,EQ,C'B'),OVERLAY=(52:268,5))
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 File processing vishwakotin Mainframe Interview Questions 9 Fri Apr 28, 2017 11:38 am
No new posts Occurrence of various strings within ... TS70363 JCL & VSAM 1 Thu Apr 27, 2017 8:18 pm
No new posts copy SEQ to PDS and create stats steve-myers JCL & VSAM 4 Thu Apr 27, 2017 1:15 am
This topic is locked: you cannot edit posts or make replies. SDSF multiple spool datasets extracte... PJAlarcon CLIST & REXX 1 Fri Apr 21, 2017 10:50 pm
No new posts Length of Tape file Mohan Kothakota JCL & VSAM 9 Thu Apr 20, 2017 10:24 pm


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