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
 

 

Syncsort ONLY Updated records needs to be writen Output file

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

New User


Joined: 26 Jun 2009
Posts: 13
Location: Hyderabad

PostPosted: Wed Sep 02, 2009 2:54 pm    Post subject: Syncsort ONLY Updated records needs to be writen Output file
Reply with quote

I have a requirement as below.

I/P File 1 : Data is
---------------------
123...........ABC
345...........PQR
456...........TUV

I/P File 2 : Data is
---------------------
123...........ABC
345...........XYZ
789...........MNO

Key is first 3 digits and assume the length of file is say some 40.

In the 2nd file the 2nd record is updated/having different data in last 3 positions(let me say it is from 38-40 positions(assume PIC clause as CHAR).

Basically the requirement is like the the records updated in file 2(345 record) should be written to O/P file and the new record(789 record) in file 2 should not be written.

O/P File : Data should as
----------------------------

345...........XYZ

Note: There will be millions of data so the KEY value should not be mentioned directly(I mean we should not be using "345" and can be used as (1,3,CH)...something like this).
Back to top
View user's profile Send private message

viveksurya

New User


Joined: 08 Jun 2009
Posts: 36
Location: Bangalore

PostPosted: Thu Sep 03, 2009 1:12 pm    Post subject:
Reply with quote

Please use the following code:

Code:
000003 //S010     EXEC PGM=SORT
000004 //SYSOUT   DD SYSOUT=*
000005 //SORTJNF1 DD *
000006 123ABC
000007 234BCD
000008 345CDE
000009 456DEF
000010 567EFG
000011 678FGH
000012 789GHI
000013 //SORTJNF2 DD *
000014 234PQR
000015 589BCD
000016 678FGH
000017 678RAM
000018 989ABC
000019 //SORTOUT  DD DSN=NBKOWJN.FILE.TEST5,DISP=(NEW,CATLG,DELETE),
000020 //            DCB=(RECFM=FB,LRECL=80),
000021 //            SPACE=(TRK,(2,12),RLSE)
000022 //SYSIN    DD *
000023   JOINKEYS FILE=F1,FIELDS=(1,3,A)
000024   JOINKEYS FILE=F2,FIELDS=(1,3,A)
000025   REFORMAT FIELDS=(F1:1,6,F2:1,6)
000026   INCLUDE COND=((1,3,ZD,EQ,7,3,ZD),AND,(4,3,CH,NE,10,3,CH))
000027   SORT FIELDS=COPY
000028   OUTREC FIELDS=(1:7,6)
000029 /*
[/list]

and the output :

Code:
000001 234PQR
000002 678RAM


PS: I have used 6 byte input, likewise, you can change as per ur need.
Back to top
View user's profile Send private message
Rajendra Kumar

New User


Joined: 26 Jun 2009
Posts: 13
Location: Hyderabad

PostPosted: Tue Sep 08, 2009 3:28 pm    Post subject:
Reply with quote

Hi Vivek,

Thanks a lot for the Input.

The logic which you have given is working but I am facing a small problem and below is the same.

File 1:(Data which you have mentioned but adding another record to it)
-------
123ABC
234BCD
345CDE
456DEF
567EFG
678FGH
678RAM ===> I am adding this record
789GHI

File 2:(Data which you have mentioned but adding another record to it)
-------
234PQR
589BCD
678FGH
678RAM
678SAM ==> I am adding this record
989ABC

Now I am getting in the Output file as

234PQR
678RAM ==> This should not be according to my requirement
678SAM

Explanation: The file 1 record 678FGH compares with 678RAM and according to the logic given in sysin the record 678RAM comes to output file which actually should not come according to my requirement.

So,Is there a chance to handle such type of situation.

Thanks in advance for the help.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Tue Sep 08, 2009 8:24 pm    Post subject:
Reply with quote

Rajendra Kumar,

Do you expect duplicate keys in both of your input files? If yes, please provide a sample input covering all possible scenarios and the expected output out of it. Mention all the rules for reaching your expected output. What would you expect in output if you can have the below input?

I/P File 1 :
Code:
123...........ABC
345...........PPP
345...........QQQ
345...........RRR
345...........PQR
456...........TUV
I/P File 2 :
Code:
123...........ABC
345...........RRR
345...........PQR
345...........XYZ
789...........MNO
Back to top
View user's profile Send private message
Rajendra Kumar

New User


Joined: 26 Jun 2009
Posts: 13
Location: Hyderabad

PostPosted: Tue Sep 08, 2009 8:26 pm    Post subject:
Reply with quote

I would expect only the below record which is from 2nd file

345...........XYZ
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 09, 2009 12:42 am    Post subject:
Reply with quote

Hello,

Quote:
I would expect only the below record which is from 2nd file
This is not what was requested. . .
Quote:
please provide a sample input covering all possible scenarios and the expected output out of it.
Please provide the requested info showing the inputs with multiple sets of duplicates and the output you want from that input and explain why which output was the required output.
Back to top
View user's profile Send private message
Rajendra Kumar

New User


Joined: 26 Jun 2009
Posts: 13
Location: Hyderabad

PostPosted: Wed Sep 09, 2009 11:53 am    Post subject:
Reply with quote

Hi,

I am sorry if I confused anybody.

Here is my explanation and the output which I am expecting.

I am having 2 Input file(1 can be assumed as yesterdays file and the other one as todays file).let me say file1 as yesterday's file and file2 as today's file.

In file1 let me say there are 14 records(record number from 1...14) and in today's file(file2) let me say there are 18 records out of which 10 records are same as the records in file1 and 2 records(13 & 14 of file1) are deleted and 2 records(9 & 12) are updated there are newly 4 records in today's file(file2).

So now my requirement is that only the updated records(2 records) from yesterday's file(file1) are present in today's file(file2)...so only these 2 records should be present.Neither the delted records of file 1 or newly inserted records of file2 should be coming to output file.

Below is the explanation with the help of data

file1:
------

123...........ABC
345...........BCD
456...........CDE
678...........DEF
789...........EFG
987...........FGH
876...........GHI
876...........XYZ
876...........ABC
765...........HIJ
765...........BCD
765...........TUV
654...........IJK --> Will get deleted in file2
543...........JKL --> Will get deleted in file2


File 2:
-------
123...........ABC
345...........BCD
456...........CDE
678...........DEF
789...........EFG
987...........FGH
876...........GHI
876...........XYZ
876...........pqr --> UPDATED(value changed from "ABC" to "pqr")
765...........HIJ
765...........BCD
765...........wxy --> UPDATED(value changed from "TUV" to "wxy")
432...........PQR --> NEW
321...........TUV --> NEW
210...........MNO --> NEW
109...........NOP --> NEW

Output file:
-------------
876...........pqr --> UPDATED record
765...........wxy --> UPDATED record


As mentioned already the KEY is first 3 chars and the data that will get changed is the last 3 chars.....total file length can be taken as 40.

Please let me know if this is still confusing....Thx
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Wed Sep 09, 2009 4:45 pm    Post subject:
Reply with quote

A few more qns.

In your latest example, say for the key: 876, you have equal number of records(3 records) each in each input file. Is it possible to have unequal number of records for a key in both the files? If yes, show such an example and explain how you reached the output.

Are your input files sorted on any field?
Back to top
View user's profile Send private message
Rajendra Kumar

New User


Joined: 26 Jun 2009
Posts: 13
Location: Hyderabad

PostPosted: Fri Sep 11, 2009 3:46 am    Post subject:
Reply with quote

Hi,

Is it possible to have unequal number of records for a key in both the files?............Ans:Yes

As I mentioned previously the files will be coming from DB2 table....for example in yesterday's file there are 3 records with KEY 876 and in today's file there's a chance that 1 record is deleted and othe other one is updated and also we can have a new record inserted in today's file.

Are your input files sorted on any field?...Answer:Yes,they are sorted on the complete record.

Anyway,Thx to all for your replies...I was able to do this using ICETOOL in 2 steps.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Sep 11, 2009 7:24 am    Post subject:
Reply with quote

Quote:
I was able to do this using ICETOOL in 2 steps
Rajendra Kumar,

Good to hear that you found the solution yourself. Do you mind posting your solution here.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Sep 14, 2009 10:46 pm    Post subject:
Reply with quote

Rajendra Kumar,

You can achieve the same in a 1-step SYNCTOOL job like this.
Code:
//STEP0100 EXEC PGM=SYNCTOOL                       
//TOOLMSG  DD SYSOUT=*                             
//DFSMSG   DD SYSOUT=*                             
//CTL1JNF1 DD DSN= Input file1 (FB/LRECL=40)       
//CTL1JNF2 DD DSN= Input file2 (FB/LRECL=40)       
//CTL2JNF1 DD DSN=&&T1,UNIT=SYSDA,DISP=(,PASS)     
//CTL2JNF2 DD DSN=&&T2,UNIT=SYSDA,DISP=(,PASS)     
//OUT      DD DSN= Output file (FB/LRECL=40)       
//TOOLIN   DD *                                   
  SORT FROM(CTL1JNF1) TO(CTL2JNF1) USING(CTL1)     
  SORT FROM(CTL2JNF1) TO(OUT)      USING(CTL2)     
//CTL1CNTL DD *                                                   
  JOINKEYS FILE=F1,FIELDS=(1,3,A,38,3,A)                           
  JOINKEYS FILE=F2,FIELDS=(1,3,A,38,3,A)                           
  REFORMAT FIELDS=(F1:1,40,F2:1,40)                               
  JOIN UNPAIRED                                                   
  SORT FIELDS=(1,3,CH,A)                                           
  OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))                   
  OUTFIL FNAMES=CTL2JNF1,INCLUDE=(1,1,CH,NE,C' ',AND,81,8,ZD,EQ,1),
                   BUILD=(1,3)                                     
  OUTFIL FNAMES=CTL2JNF2,INCLUDE=(1,1,CH,EQ,C' '),                 
                   BUILD=(41,40)                                   
//CTL2CNTL DD *                                                   
  JOINKEYS FILE=F1,FIELDS=(1,3,A)                                 
  JOINKEYS FILE=F2,FIELDS=(1,3,A)                                 
  REFORMAT FIELDS=(F2:1,40)                                       
  SORT FIELDS=COPY
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
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm


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