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
 

 

Find Delta Records of current and prev versions of file

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

New User


Joined: 16 Oct 2012
Posts: 34
Location: INDIA

PostPosted: Fri Mar 15, 2013 5:25 pm    Post subject: Find Delta Records of current and prev versions of file
Reply with quote

Hi,

I have a requirement where I need to find the records in which certain attributes have changed in the current file as compared to the prev day file ad write the delta records to a new file.

Details as below:
1) Both files have the same structure and a key in position 1:4
2) Key field cannot change in current version as compared to prev version only attributes field can change.

File Format:


Code:


Key          PIC  S9(09)  COMP        1:4
Attribute1   PIC  S9(08)V COMP-3      18:5
Attribute2   PIC   X(30)        23:30
Attribute3   PIC   X(01)        206:1



I have written the following code and its not working as desired:


Code:


//STEP1    EXEC PGM=SORT                                               
//SORTOUT  DD SYSOUT=*                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD DSN=CURR,DISP=SHR   
//SORTJNF2 DD DSN=PREV,DISP=SHR   
//SYSIN    DD *                                                       
  OPTION COPY                                                         
  JOINKEYS FILES=F1,FIELDS=(1,4,A),SORTED                             
  JOINKEYS FILES=F2,FIELDS=(1,4,A),SORTED                             
  JOIN UNPAIRED                                                       
  REFORMAT FIELDS=(F1:1,4,18,5,23,30,206,1,F2:1,4,18,5,23,30,206,1)   
  OUTFIL REMOVECC,OMIT=(1,40,CH,EQ,41,40,CH),                         
  IFTHEN=(WHEN=(41,1,CH,EQ,C'1'),BUILD=(C' ',1,4)),                   
  IFTHEN=(WHEN=(NONE),BUILD=(C' ',1,4))                               
/*                                                                     
//*         

Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Fri Mar 15, 2013 5:38 pm    Post subject: Reply to: Find Delta Records of current and prev versions of
Reply with quote

Quote:
I have written the following code and its not working as desired:


You just posted a generic whining,
to get good help You should describe HOW it does not behave according to YOUR EXPECTATIONS
Back to top
View user's profile Send private message
frozenblood
Warnings : 1

New User


Joined: 16 Oct 2012
Posts: 34
Location: INDIA

PostPosted: Fri Mar 15, 2013 6:04 pm    Post subject:
Reply with quote

I am getting blank output but deltas are present in current file.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Mar 15, 2013 6:12 pm    Post subject: Reply to: Find Delta Records of current and prev versions of
Reply with quote

Why would you need UNPAIRED?

Although you have two conditions, condition-1 and not-condition-1, you do the same build for each, a blank followed by the key.

Show sample input records which cover all your options, expected output, please.
Back to top
View user's profile Send private message
frozenblood
Warnings : 1

New User


Joined: 16 Oct 2012
Posts: 34
Location: INDIA

PostPosted: Fri Mar 15, 2013 6:24 pm    Post subject:
Reply with quote

Code:


CURRENT FILE:

AAAA 1234 BARBARA S
BBBB 5678 SYMPHONY K
CCCC 9012 AIRLOUNGE Y
DDDD 5555 DAVIDOFF T

PREV FILE:

AAAA 1111 BARBARA S
BBBB 5678 SYMPHONY K
CCCC 9012 AIR Y

OUTPUT

AAAA 1234 BARBARA S
CCCC 9012 AIRLOUNGE Y
DDDD 5555 DAVIDOFF T

Back to top
View user's profile Send private message
frozenblood
Warnings : 1

New User


Joined: 16 Oct 2012
Posts: 34
Location: INDIA

PostPosted: Sat Mar 16, 2013 10:28 am    Post subject:
Reply with quote

any suggestions?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Mar 16, 2013 12:20 pm    Post subject:
Reply with quote

Try:
Code:
//JS001   EXEC PGM=SORT           
//*                               
//SYSOUT   DD SYSOUT=*           
//*                               
//SORTJNF1 DD *                   
AAAA 1234 BARBARA S               
BBBB 5678 SYMPHONY K             
CCCC 9012 AIRLOUNGE Y             
DDDD 5555 DAVIDOFF T             
//SORTJNF2 DD *                   
AAAA 1111 BARBARA S               
BBBB 5678 SYMPHONY K             
CCCC 9012 AIR Y                   
//*                               
//SORTOUT  DD SYSOUT=*           
//*                               
//SYSIN    DD *                   
  SORT FIELDS=COPY               
  JOINKEYS FILE=F1,FIELDS=(1,21,A)
  JOINKEYS FILE=F2,FIELDS=(1,21,A)               
  REFORMAT FIELDS=(F1:1,21,F2:1,21),FILL=C'~'     
  JOIN UNPAIRED F1,F2                             
  OUTFIL FNAMES=SORTOUT,                         
  INCLUDE=(01,01,CH,NE,C'~',AND,22,01,CH,EQ,C'~'),
  BUILD=(1,21)                                   
/*                                               
//*                                               
Back to top
View user's profile Send private message
frozenblood
Warnings : 1

New User


Joined: 16 Oct 2012
Posts: 34
Location: INDIA

PostPosted: Sat Mar 16, 2013 12:41 pm    Post subject:
Reply with quote

Hi Anuj,

Thanks for your response.

The fields are not consecutive, below is the definition


Code:

Key          PIC  S9(09)  COMP        1:4
Attribute1   PIC  S9(08)V COMP-3      18:5
Attribute2   PIC   X(30)        23:30
Attribute3   PIC   X(01)        206:1


so do u want me to make a consecutive file of the above fields using outrec and then run the above ?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Mar 16, 2013 6:04 pm    Post subject:
Reply with quote

I've showed a solution for the inputs you've posted and that give this as output, which is same as you show as expected output:
Code:
AAAA 1234 BARBARA S
CCCC 9012 AIRLOUNGE Y
DDDD 5555 DAVIDOFF T

I've used the inputs as in-stream data so the assumptions are that your input files are FB, LRECL=80. If they differ we'd need to tweak the sort statements. So if the keys to be compared are at multiple positions, mention those positions at JOINKEYS statement as shown here: http://ibmmainframes.com/about51839.html and see if it works for you.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sat Mar 16, 2013 7:03 pm    Post subject: Reply to: Find Delta Records of current and prev versions of
Reply with quote

Code:
  JOIN UNPAIRED F1,F2


I think the F1,F2 is being treated as a comment.

UNPAIRED will get F1,F2 anyway.

Since mismatches are ignored in the INCLUDE, why not drop the JOIN altogether, so you only get matched records?

frozenblood, I at least am unclear about what you mean,

Please show representative inputs, and expected output. Don't expect people to keep providing solutions as you change your inputs - or, unless told it is not possible with what you have - expect people to imagine it a good idea for you to change your data to fit a solution.
Back to top
View user's profile Send private message
frozenblood
Warnings : 1

New User


Joined: 16 Oct 2012
Posts: 34
Location: INDIA

PostPosted: Sat Mar 16, 2013 8:39 pm    Post subject:
Reply with quote

Thanks everyone, I have achieved the above requirement in 2 steps :

Code:


1) Sort the curr and prev file on the basis of key field and write only the required fields(to be compared for delta) using OUTREC to temp datasets respectively. Hence making both datasets of LRECL 40

2)

//STEP3    EXEC PGM=SORT                                             
//SORTOUT  DD DSN=DELTA,             
//            DISP=(NEW,CATLG,DELETE),                               
//            AVGREC=K,SPACE=(40,(1900,300),RLSE),                   
//            RECFM=FB,LRECL=40                                       
//SYSOUT   DD SYSOUT=*                                               
//SORTJNF1 DD DSN=TEMP.CURR,DISP=SHR
//SORTJNF2 DD DSN=TEMP.PREV,DISP=SHR
//SYSIN    DD *                                                       
  SORT FIELDS=COPY                                                   
  JOINKEYS FILE=F1,FIELDS=(1,40,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,40,A)                                   
  REFORMAT FIELDS=(F1:1,40,F2:1,40),FILL=C'~'                         
  JOIN UNPAIRED                                               
  OUTFIL FNAMES=SORTOUT,                                             
  INCLUDE=(01,01,CH,NE,C'~',AND,41,01,CH,EQ,C'~'),                   
  BUILD=(1,40)     
//* 


Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Mar 16, 2013 10:01 pm    Post subject: Re: Reply to: Find Delta Records of current and prev version
Reply with quote

Bill Woodger wrote:
I think the F1,F2 is being treated as a comment.

UNPAIRED will get F1,F2 anyway.
Thanks Bill, I remember of looking at an example of using F1, F2 and used it that way but yes, it work without F1/F2. Thanks 'gain. Even a Saturday night makes you learn something new... icon_smile.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Mar 16, 2013 10:08 pm    Post subject: Re: Reply to: Find Delta Records of current and prev version
Reply with quote

Bill Woodger wrote:
frozenblood, I at least am unclear about what you mean,
I think OP is/was looking for the unpaired F1 records but I'm not very sure.

However, the way it was explained here:
Quote:
I have a requirement where I need to find the records in which certain attributes have changed in the current file as compared to the prev day file ad write the delta records to a new file.
with site specif terminology it was tough to comprehend as what is needed.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Sun Mar 17, 2013 12:25 am    Post subject: Reply to: Find Delta Records of current and prev versions of
Reply with quote

You could try this then:

Code:
//STEP3    EXEC PGM=SORT                                             
//SORTOUT  DD DSN=DELTA,             
//            DISP=(NEW,CATLG,DELETE),                               
//            AVGREC=K,SPACE=(40,(1900,300),RLSE),                   
//            RECFM=FB,LRECL=40                                       
//SYSOUT   DD SYSOUT=*                                               
//SORTJNF1 DD DSN=TEMP.CURR,DISP=SHR
//SORTJNF2 DD DSN=TEMP.PREV,DISP=SHR
//SYSIN    DD *                                                       
  JOINKEYS FILE=F1,FIELDS=(1,40,A),SORTED
  JOINKEYS FILE=F2,FIELDS=(1,40,A),SORTED
  JOIN UNPAIRED,F1,ONLY                       
  REFORMAT FIELDS=(F1:1,40)
  SORT FIELDS=COPY                                                   
//* 
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Mon Mar 18, 2013 12:16 pm    Post subject:
Reply with quote

That's better Bill. OTOH, I just tried to show a generic aproach for the two file match. Hopefully, OP has got something to work upon now.
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 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 How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 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


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