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
 

 

Compare and Replace from two Input Files

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

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Tue Jun 15, 2010 7:48 pm    Post subject: Compare and Replace from two Input Files
Reply with quote

Hi,

I would like to achieve the following result using Syncsort. I can use Ezytrieve but I would like to know if it is possible using Syncsort.

Input File 01
Account (Key), Status, Effective Date, End Date

Input File 02
Account (Key), Status, Effective Date, End Date

Output File
Account, Status, Effective Date, End Date

The Status (single byte) can be anything from A - Z.

Input File 01 and 02 can have identical number of records or it is possible that File 01 records > File 02 records.

I will write the Input File 01 to the output except that when Input File 01 has a Status of 'P', I will use the End Date of Input File 02 and replace the End Date of File 01 and write it to the output. Also, please show if it can be done on a single step.

For ex.
Input File 01
A123456789X10/21/200911/21/2009
A987654321P10/21/200911/21/2009

Input File 02
A123456789X10/21/200911/21/2009
A987654321P11/21/200912/21/2009

Output
A123456789X10/21/200911/21/2009
A987654321P10/21/200912/21/2009


I tried to search on something similar like this and end up seeing mostly JOIN statements..but I don't have any guidelines on how to use it..mostly I rely on examples but examples don't explain everything.

Thank you in advance.
Back to top
View user's profile Send private message

Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Tue Jun 15, 2010 9:41 pm    Post subject:
Reply with quote

Here is a SyncSort for z/OS job that will provide you with the requested output:
Code:
//STEP1  EXEC PGM=SORT                                       
//SORTJNF1 DD *                                             
A123456789X10/21/200911/21/2009                             
A987654321P10/21/200911/21/2009                             
//SORTJNF2 DD *                                             
A123456789X10/21/200911/21/2009                             
A987654321P11/21/200912/21/2009                             
//SORTOUT  DD SYSOUT=*                                     
//SYSOUT   DD SYSOUT=*                                     
//SYSIN    DD *                                             
  JOINKEYS FILES=F1,FIELDS=(1,10,A)                         
  JOINKEYS FILES=F2,FIELDS=(1,10,A)                         
  JOIN UNPAIRED,F1                                         
  REFORMAT FIELDS=(F1:1,31,F2:22,10)                       
  SORT FIELDS=COPY                                         
  OUTREC IFTHEN=(WHEN=(11,1,CH,EQ,C'P'),BUILD=(1,21,32,10)),
  IFTHEN=(WHEN=NONE,BUILD=(1,31)) 
/*                         
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Wed Jun 16, 2010 7:20 am    Post subject:
Reply with quote

thanks for that Alissa..you always answer questions promptly on syncsort problems.

can you explain the JOIN and the other statements used on the sysin as I can only understand a bit of it

here is how I understand it

JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
*accounts were used as keys
JOIN UNPAIRED,F1
** no idea
REFORMAT FIELDS=(F1:1,31,F2:22,10)
*this one is a copy of F1 file plus the End date of F2?
OUTREC IFTHEN=(WHEN=(11,1,CH,EQ,C'P'),BUILD=(1,21,32,10)),
*since you reformatted the record F1 + F2 End date, when the condition is met you put the F2 End date on the position of F1 End date
IFTHEN=(WHEN=NONE,BUILD=(1,31))
*not sure what is WHEN=NONE, but I am assuming that this one just writes the F1 to the output.
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Wed Jun 16, 2010 7:53 am    Post subject:
Reply with quote

I tried the code Alissa and it works. Big Thanks!

Now I wish I have a manual on this. It would save me a lot of time on coding similar ezytrieve programs.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Wed Jun 16, 2010 7:59 am    Post subject:
Reply with quote

Michael Angelo Pagala wrote:
JOIN UNPAIRED,F1
** no idea
Exploiting SyncSort JOIN wrote:
JOIN - UNPAIRED - Join only paired records
Quote:
IFTHEN=(WHEN=NONE,BUILD=(1,31))
*not sure what is WHEN=NONE, but I am assuming that this one just writes the F1 to the output.
SyncSort for z/OS 1.3 Programmer’s Guide wrote:
WHEN=NONE
The WHEN=NONE subparameter condition is satisfied if none of the preceding WHEN=(conditions) subparameter conditions is satisfied or if there are no WHEN=(conditions) subparameters. If the
WHEN=NONE subparameter condition is satisfied, it applies the specified second subparameter to the temporary record.
I would suggest you contact Alissa Margulies or the Syncsort support staff to get a current level manual and any additional supporting manuals for the current level of Syncsort you are currently at........
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Wed Jun 16, 2010 3:41 pm    Post subject:
Reply with quote

thank you CICS guy..I will try to PM Alissa for that


---
I can't see any PM button here. I hope Alissa will post here again and provide her contact details
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Wed Jun 16, 2010 5:55 pm    Post subject:
Reply with quote

Have a look at her signature, this is the Id you can use: zos_tech@syncsort.com .
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Wed Jun 16, 2010 7:13 pm    Post subject:
Reply with quote

Just to clarify... JOIN UNPAIRED,F1 will select the paired records in addition to the unique or unpaired records from the first file. If JOIN UNPAIRED,F1,ONLY were specified, then only the unmatched records from File1 would be selected.

Feel free to contact me at alissa.margulies@syncsort.com and I would be happy to assist you with acquiring the manuals.
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Thu Jun 17, 2010 7:31 am    Post subject:
Reply with quote

thanks for the clarification Alissa.I will also try and ask if they have a copy of the latest syncsort manual here
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: Thu Jun 17, 2010 8:14 am    Post subject:
Reply with quote

Hello,

Suggest you also do as Alissa mentioned:
Quote:
Feel free to contact me at alissa.margulies@syncsort.com and I would be happy to assist you with acquiring the manuals.
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Thu Jun 17, 2010 10:06 am    Post subject:
Reply with quote

May I ask another question?

What if I just want to write only the paired records and write that to the output which falls under a certain condition and omit the rest including unpaired and those that did not meet the condition.

So the output will be either empty or have records (paired and met condition).

Key is the 1st byte
Input 1
A1234501/01/2010
B1234502/01/2010

Input 2
A1234502/01/2010
B1234501/01/2010

Condition - Date of Paired Records must be equal and be written to the output.

Expected output
Empty (if the condition is met then this will not be the case)

Since UNPAIRED select both paired and unpaired, is there anyway I can get only the paired that met the condition and omit everything else.

is there a code such as WHEN=NONE,OMIT? :d
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Thu Jun 17, 2010 11:05 am    Post subject:
Reply with quote

I think I found a solution to my prob.

I used the INCLUDE COND instead of the IFTHEN..

I filtered the paired by checking the keys if they are spaces in the reformat fields just like with the other examples posted. If there is any other option please post it here.
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Thu Jun 17, 2010 7:49 pm    Post subject:
Reply with quote

If you only want the paired records, then you can simply remove the JOIN UNPAIRED,F1 statement altogether.
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Fri Jun 18, 2010 8:16 am    Post subject:
Reply with quote

Alissa Margulies wrote:
If you only want the paired records, then you can simply remove the JOIN UNPAIRED,F1 statement altogether.


so it means that JOINKEYS does not necessarily mean that we need to indicate JOIN UNPAIRED too? so JOINKEYS default selects paired records?

I have requested a copy of the manual here and they gave me a 1.2 version..IFTHEN,BUILD statements were not included in the manual.
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: Fri Jun 18, 2010 8:48 am    Post subject:
Reply with quote

Hello,

If you successfully execute IFTHEN/WHEN/etc, suggest you work with Alissa to download the current documentation. . .

When you run Syncsort, which release is shown in the informational output?
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Fri Jun 18, 2010 11:14 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

If you successfully execute IFTHEN/WHEN/etc, suggest you work with Alissa to download the current documentation. . .

When you run Syncsort, which release is shown in the informational output?


Here is what I can see on the sysout after I run a syncsort >
SYNCSORT FOR Z/OS 1.3.2.1R

yeah I will try to find a way to email Alissa. I can't use my office email for such.

thanks!
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: Fri Jun 18, 2010 7:21 pm    Post subject:
Reply with quote

Hello,

The good news is that your release supports IFTHEN. . .

If you can get to the forum at work, you can use the PM facility to send Alissa a Private Message. . .
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Fri Jun 18, 2010 8:33 pm    Post subject:
Reply with quote

Michael,

You can contact me offline at alissa.margulies@syncsort.com for an Addendum to the SyncSort for z/OS 1.2 Programmer's Guide, which contains the documentation for IFTHEN/BUILD/OVERLAY, etc.

Based on your previous post, you are running SyncSort for z/OS 1.3.2.1. The SyncSort for z/OS 1.3 Programmer's Guide will also contain the full documentation for those features.

(If email is an issue, you can send me a private message here in the forum).
Back to top
View user's profile Send private message
Michael Angelo Pagala

New User


Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle

PostPosted: Mon Jun 21, 2010 3:19 pm    Post subject:
Reply with quote

Thank you very much for the info and help!

I already sent a PM to Alissa.
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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Replace repeated JCL with looping REXX prino CLIST & REXX 3 Thu Oct 13, 2016 5:30 am
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


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