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

Help with extracting Delta files


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

New User


Joined: 29 Dec 2009
Posts: 3
Location: Arkansas, USA

PostPosted: Wed Dec 30, 2009 9:15 am
Reply with quote

Hello.

I have a requirement to get the delta file on daily basis as well as maintain a master file. let me explain the requirement with example.

File 1: say this is master

111111 arizona 06440
222222 New York 11001
333333 Arkansas 72712

File 2 : say this is todays input file.

111111 arizona 74400
222222 New York 11001
444444 Arkansas 72756

In both the files first 6 bytes are the key and total record length is 25

What i need is,

Out1 : this should contain the records which are unique to File 1 and FIle2. Also it should have the updated record from file2, if the same key record is available on File1.

For example
Out 1 : i.e updated master file

111111 arizona 74400
222222 New York 11001
333333 Arkansas 72712
444444 Arkansas 72756

Out 2 : todays delta file
should contain only the records that are unique to File2.

111111 arizona 74400
444444 Arkansas 72756
Back to top
View user's profile Send private message
rakesha.hg

Active User


Joined: 21 Mar 2008
Posts: 161
Location: bangalore

PostPosted: Wed Dec 30, 2009 3:29 pm
Reply with quote

hi,

This worked for me ...

Code:
//CG56T010 JOB HNA81624DNOE00,DEVT,CLASS=A,MSGCLASS=T,NOTIFY=&SYSUID,
//             PRTY=10                                               
//******************************************************************
//STEP     EXEC PGM=ICETOOL                                         
//*                                                                 
//******************************************************************
//F1       DD *                                                     
111111 ARIZONA  06440                                               
222222 NEW YORK 11001                                               
333333 ARKANSAS 72712                                               
//F2       DD *                                                     
111111 ARIZONA  74400                                               
222222 NEW YORK 11001                                               
444444 ARKANSAS 72756                                               
/*                                                                   
//OUT      DD DSN=CG56.DELTA,DISP=(NEW,CATLG,DELETE),               
//         SPACE=(8,(10,10),RLSE)                                   
//OU1      DD DSN=CG56.MASTER,DISP=(NEW,CATLG,DELETE),               
//         SPACE=(8,(10,10),RLSE)                                   
//*T1      DD SYSOUT=*                                               
//T1       DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS) 
//T2       DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS) 
//TOOLIN   DD *                                                     
  COPY FROM(F1) TO(T1) USING(CTL1)                           
  COPY FROM(F2) TO(T1) USING(CTL2)                           
  SELECT FROM(T1) TO(OU1) ON(1,6,CH) USING(CTL4) LAST       
  SPLICE FROM(T1) TO(T2) ON(1,21,CH) WITH(23,1) KEEPNODUPS -
  USING(CTL5)                                               
  COPY FROM(T2) TO(OUT) USING(CTL6)                         
/*                                                           
//CTL1CNTL DD *                                             
 OPTION COPY                                                 
 OUTREC BUILD=(1,21,22:C'AA')                               
//*                                                         
//CTL2CNTL DD *                                             
 OPTION COPY                                                 
 OUTREC BUILD=(1,21,22:C'BB')                               
//*                                                         
//CTL3CNTL DD *                                             
 OPTION COPY                                                 
//*                                                         
//CTL4CNTL DD *                                             
 OUTFIL FNAMES=OU1,BUILD=(1,21)                             
//*                                                         
//CTL5CNTL DD *                                             
 OUTFIL FNAMES=T2,INCLUDE=(22,2,CH,EQ,C'BB')                 
//*                                                           
//CTL6CNTL DD *                                               
 OPTION COPY                                                 
 OUTREC BUILD=(1,21)                                         
//TOOLMSG DD SYSOUT=*                                         
//DFSMSG DD SYSOUT=*                                         


Code:
VIEW       CG56.MASTER     
Command ===>               
****** *********************
000001 111111 ARIZONA  74400
000002 222222 NEW YORK 11001
000003 333333 ARKANSAS 72712
000004 444444 ARKANSAS 72756


Code:
VIEW       CG56.DELTA       
Command ===>               
****** *********************
000001 111111 ARIZONA  74400
000002 444444 ARKANSAS 72756


This is very crude way & limited to my novice knowledge on icetool.
Please delete this post if its not useful.
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 Dec 30, 2009 11:54 pm
Reply with quote

Deepak,

Your example and description do not make sense.

You say that the key is positions 1-6, so that means 111111 is in both files, 222222 is in both files, 333333 is only in file1 and 444444 is only in file2. But then you talk about records that are unique to File1 (which would only be 333333) and unique to File2 (which would only be 444444), but the records you show for output don't match those cases.

You need to do a better job of explaining what you want to do and showing an example of input records and expected output records that correspond to your description of what you want to do.
Back to top
View user's profile Send private message
Deepak Sunke

New User


Joined: 29 Dec 2009
Posts: 3
Location: Arkansas, USA

PostPosted: Thu Dec 31, 2009 12:29 am
Reply with quote

Frank,

Sorry for the confusion. let me try to explain it better. My example holds good with below descripton.

The files we are getting is the club membership information file, where first 6 bytes are the membership number, the next field is state and last field is zip code.

What we want to do it, maintain a master file (File1). Then we get a daily feed of changes to membership(File2), which include updates to existing memberships and also new memberships.
When we get a new membership we need to pass that new membership to the next process (Out2). At the same time write this record to the master file(Out1), so that master is up to date.
When we get an update to an existing membership, we want to pass this information to the next process(Out2) and at the same time want to update the master file(Out1).

The reason we want to maintain a master is avoid sending duplicate records to Out2. So if i get today a record and then get the same record on day 5, when I compare it with master file on day5, i will find it and will not write it to Out2.

Hope this clarifies. Please let me know if you need any more information
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: Thu Dec 31, 2009 5:37 am
Reply with quote

The confusion here is your use of the terms "key", "new", "update", "same".

What makes a record a "new" one? Which of the three fields in that record must be different from the corresponding fields in an existing record so that it will be considered "new". Must all three fields be different, or must two fields be different (which two) or must one field be different (which one).

Same question for what makes a record an "updated" one, and what makes a record the "same" one.

You need to explain clearly what you mean by a "new" record, an "updated" record and the "same" record in terms of the corresponding fields in file1 and file2, and relate that to your example of input and output records (a better example with all cases would help).
Back to top
View user's profile Send private message
Deepak Sunke

New User


Joined: 29 Dec 2009
Posts: 3
Location: Arkansas, USA

PostPosted: Thu Dec 31, 2009 5:36 pm
Reply with quote

Frank,

A record is considered as updated when either state or Zip code changes or both of them change in File2.
A record is new if the key field is new and available only in File2
A record is same when all 3 fields are same in File2 and File1

Below example of file2 shows what i mean by new, updated and same record.

File 1: say this is master

111111 arizona 06440
222222 New York 11001
333333 Arkansas 72712

File 2 : say this is todays input file.

111111 arizona 74400 --> Updated record, since this key is available in Master, but the zip code changed
222222 New York 11001 --> Same record (duplicate) - Since this Key is avaiable in master and there is no change to other 2 fields also .
444444 Arkansas 72756 --> New Record, Since this key is not available in Master
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 Jan 05, 2010 12:05 am
Reply with quote

Based on your description and example, this DFSORT/ICETOOL job will do what you asked for:

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/25)
//IN2 DD DSN=...  input file2 (FB/25)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT1 DD DSN=...  output file1 (FB/25)
//OUT2 DD DSN=...  output file2 (FB/25)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT1) ON(1,6,CH) KEEPNODUPS -
  WITH(26,25) WITH(51,1) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(26:1,25,51:C'BB')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(26:1,25,51:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=OUT1,BUILD=(26,25)
  OUTFIL FNAMES=OUT2,INCLUDE=(51,2,CH,EQ,C'VV',OR,
    (51,2,CH,EQ,C'VB',AND,8,18,CH,NE,33,18,CH)),
    BUILD=(26,25)
/*
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 Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top