MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Comparing fields in VSAM dataset using FileAid JCL
Goto page 1, 2  Next
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Thu Apr 27, 2006 10:09 am    Post subject: Comparing fields in VSAM dataset using FileAid JCL Reply with quote
Hi all.
Question for ya from a noob.

I've got a million-record VSAM file sorted by position 1 (account #).
Some records also have an old account number in pos 143.

How can I extract all accounts like this using fileaid.

Select * from dataset a, dataset b
where pos_1 a = pos_143 b?

Basically, I need the current records which have old records, and the corresponding old records.

Does this make sense?

Thanks...
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu Apr 27, 2006 10:42 am    Post subject: Reply with quote
youtalkintome,

If you can use sort then it is very easy. try this sample JCL

Code:

//STEP0100 EXEC PGM=SORT           
//SYSOUT   DD  SYSOUT=*           
//SORTIN   DD  *                   
AAAAA              AAAAA           
BBBBB              AAAAA           
//SORTOUT  DD  SYSOUT=*           
//SYSIN    DD  *                   
  SORT FIELDS=COPY                 
  INCLUDE COND=(1,5,CH,EQ,20,5,CH)
/*


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Fri Apr 28, 2006 9:38 am    Post subject: Reply with quote
Kolusu,

Thanks for the quick reponse. I'm not sure I follow you though...
Where you have AAAAA and BBBBB above...what do they refer to?


Here's a better (I hope) explanation as to what I'm after....

a) All the records have a policy number in position 1 (see row A)
b) Some records also have an 'old' policy number (which has it's own original record) in position 143 (see row B)

Position 1-------------------------------143

A 654654654<-------------------------->..................<--------------------------
B 987987987<-------------------------->654654654<--------------------------
The example shown above may be repeated e.g. 1000 times. Therefore I want to pull those 2000 records.


Thanks again.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri Apr 28, 2006 9:55 am    Post subject: Reply with quote
youtalkintome,

I thought you wanted to compare 2 fields on the same record. But with your sample data my assumption was wrong.

few questions.

1. what is the contents of pos 143 for A rows? you show ........ Are they low values ?

Let me know and I will show a way to pull these records.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Fri Apr 28, 2006 10:16 am    Post subject: Reply with quote
Hi,

The contents of pos 143 for A rows will be blanks/spaces. I just used ..... for illustration purposes.

These are the details of the record if it helps...

Data class . . . . . : EXTENDRL
Organization . . . : PS
Record format . . . : FB
Record length . . . : 400
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri Apr 28, 2006 10:30 am    Post subject: Reply with quote
The following DFSORT/ICETOOL JCl will give you the desired results. I assumed that key to be 9 bytes long.

Code:

//STEP0100 EXEC PGM=ICETOOL     
//TOOLMSG  DD SYSOUT=*         
//DFSMSG   DD SYSOUT=*         
//IN       DD DSN=YOUR INPUT VSAM FILE,
//            DISP=SHR
//OUT      DD DSN=YOUR OUTPUT DUP FILE,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//TOOLIN   DD *                                           
  SELECT FROM(IN) TO(OUT) ON(401,9,CH) ALLDUPS USING(CTL1)   
//CTL1CNTL DD *                                           
  INREC IFTHEN=(WHEN=(143,9,CH,EQ,C' '),     
         OVERLAY=(401:001,9)),                               
        IFTHEN=(WHEN=NONE,                                 
         OVERLAY=(401:143,9))                               
  OUTFIL FNAMES=OUT,                                       
  OUTREC=(1,400)                                           
/*     


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Apr 28, 2006 10:52 am    Post subject: Reply with quote
A couple of caveats on Kolusu's solution.

1) It requires the new INREC with SELECT function available with z/OS DFSORT V1R5 PTF UK90007 or DFSORT R14 PTF UK90006 (April, 2006).

2) I believe it will only work if all of the policy numbers are unique. For example, with the input:

Code:

654654654<-------------------------->             
987987987<-------------------------->654654654<   
654654655<-------------------------->             
987987987<-------------------------->654654654< 


The output is:

Code:

654654654<-------------------------->             
987987987<-------------------------->654654654<   
987987987<-------------------------->654654654<   


youtalkintome,

If 2) is a problem for you, you need to give us more details about what your records look like with respect to duplicate new/old policy numbers.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Fri Apr 28, 2006 11:22 am    Post subject: Reply with quote
OK I used this (A/c # is 8 chars long)


Code:

//STEP0100 EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//IN       DD DISP=SHR,DSN=THIS IS MY INPUT,
//OUT      DD DSN=THIS IS MY OUTPUT,             
//            DISP=(MOD,CATLG,DELETE),UNIT=DASD,                 
//            RECFM=FB,LRECL=400,BLKSIZE=0,AVGREC=K,             
//            SPACE=(400,(1600,400),RLSE),                       
//            LABEL=RETPD=400                                   
//TOOLIN   DD *                                                 
  SELECT FROM(IN) TO(OUT) ON(401,8,CH) ALLDUPS USING(CTL1)       
//CTL1CNTL DD *                                                 
  INREC IFTHEN=(WHEN=(143,8,CH,EQ,C' '),                         
         OVERLAY=(401:001,8 )),                                   
        IFTHEN=(WHEN=NONE,                                       
         OVERLAY=(401:143,8 ))                                   
  OUTFIL FNAMES=OUT,                                             
  OUTREC=(1,400)
/*               


and I got this back from my TOOLMSG output
Code:

SYT000I  SYNCTOOL RELEASE 1.4D - COPYRIGHT 2003  SYNCSORT INC.                 
SYT001I  INITIAL PROCESSING MODE IS "STOP"                                     
SYT002I  "TOOLIN" INTERFACE BEING USED                                         
                                                                               
           SELECT FROM(IN) TO(OUT) ON(401,8,CH) ALLDUPS USING(CTL1)           
SYT020I  SYNCSORT CALLED WITH IDENTIFIER "0001"                               
SYT012E  SYNCSORT COMPLETED UNSUCCESSFULLY                                     
SYT030I  OPERATION COMPLETED WITH RETURN CODE 16                               
                                                                               
SYT015I  PROCESSING MODE CHANGED FROM "STOP" TO "SCAN" DUE TO OPERATION FAILURE
                                                                               
SYT004I  SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 16           


and this from my DFSMSG

Code:
CTL1CNTL :                                   
  INREC IFTHEN=(WHEN=(143,8,CH,EQ,C' '),     
        *                                     
         OVERLAY=(401:001,8)),               
        IFTHEN=(WHEN=NONE,                   
         OVERLAY=(401:143,8))                 
  OUTFIL FNAMES=OUT,                         
  OUTREC=(1,400)                             
WER428I  CALLER-PROVIDED IDENTIFIER IS "0001"
WER268A  INREC STATEMENT   : SYNTAX ERROR     
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE


Any idea?

Thanks for all your help on this, by the way !!!
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Fri Apr 28, 2006 11:42 am    Post subject: Reply with quote
youtalkintome,

Your TOOLMSG messages indicate that you are using syncsort which does not support IFTHEN and OVERLAY features.

So try this job.

Code:

//STEP0100 EXEC PGM=SYNCTOOL                                     
//TOOLMSG  DD SYSOUT=*                                           
//DFSMSG   DD SYSOUT=*                                           
//IN       DD DISP=SHR,DSN=THIS IS MY INPUT,
//OUT      DD DSN=THIS IS MY OUTPUT,             
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=DASD,                 
//            SPACE=(CYL,(200,100),RLSE)                       
//T1       DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(400,100),RLSE)     
//T2       DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(400,100),RLSE)     
//CON      DD DSN=&T1,DISP=OLD,VOL=REF=*.T1                   
//         DD DSN=&T2,DISP=OLD,VOL=REF=*.T2                   
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                               
  COPY FROM(IN) USING(CTL1)                                   
  SELECT FROM(CON) TO(OUT) ON(401,8,CH) ALLDUPS USING(CTL2)   
//CTL1CNTL DD *                                               
  SORT FIELDS=COPY                                           
  OUTFIL FNAMES=T1,INCLUDE=(143,8,CH,EQ,C' '),                 
  OUTREC=(1,400,1,8)                                           
  OUTFIL FNAMES=T2,SAVE,                                     
  OUTREC=(1,400,143,8)                                         
//CTL2CNTL DD *                                               
  OUTFIL FNAMES=OUT,                                         
  OUTREC=(1,400)                                               
/*             


If you still get an error on the select statement then your version of syncsort does not support USING parm on SELECT operator.

so in that case use this SELECT Operator. Note that by doing your output file length is 408 instead of 400.

Code:

SELECT FROM(CON) TO(OUT) ON(401,8,CH) ALLDUPS


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Wed May 03, 2006 5:58 am    Post subject: Reply with quote
Hi again.

Frank Yaeger, thanks for that heads-up but the scenario you posted won't occur (thankfully).

Kolusu, thanks...that Synctool solution worked !!!!

However, now I've got a change requested to the task and judging by what you said about my system not supporting the Icetool, I think I'm outta luck.

I now need to select records from the file based on certain criteria which I can pull using Fileaid
(eg:
$$DD01 USER SELECT=10,IF=(220,EQ,C''AA1''),',
WRITE=OUTPUT5,OUT=25')

However, on selecting the required records. I also need to get their original record (if they have one!)

So, if the statement above returns a record like:
987987987<-------------------------->654654654<
then I need to also extract
654654655<-------------------------->

can the Synctool cope with this by doing the same as before but using 2 files.
The master file and the file of results returned from the USER command.

So if this is returned from the USER command
987987987<-------------------------->654654654<
then pull this from the master
654654655<-------------------------->

and write them out to the one file.


Is there ANY possibility of this happening?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed May 03, 2006 7:42 am    Post subject: Reply with quote
youtalkintome,

Based on your sysout messages you have the latest version of syncsort which supports the JOIN feature. Check this topic which shows an example of matching 2 files and getting the desired records

http://www.mvsforums.com/helpboards/viewtopic.php?t=4690&highlight=joinkeys

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Wed May 03, 2006 11:21 am    Post subject: Reply with quote
Ok, I used the following code but got an empty file after a successful completion which leads me to my parameters for the OUTREC or the OUTFIL commands ??

Quote:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DISP=SHR,DSN=BXRL25.WBQ.MCP570.REINS.TEST.CONVERT
//IN2 DD DISP=SHR,DSN=BXRL25.WBQ.MCP570.REINS.ORIGS.CONV
//OUT12 DD DSN=BXRL25.WBQ.MCP570.REINS.TEST.FILE,
// DISP=(MOD,CATLG,DELETE),UNIT=DASD,
// RECFM=FB,LRECL=400,BLKSIZE=0,AVGREC=K,
// SPACE=(400,(1600,400),RLSE),
// LABEL=RETPD=400
//OUT1 DD SYSOUT=* NAMES IN FILE1 ONLY
//OUT2 DD SYSOUT=* NAMES IN FILE2 ONLY
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),
//** USE MOD FOR T1
// DISP=(MOD,PASS)
//TOOLIN DD *
//
* ADD '11' IDENTIFIER FOR FILE1 RECORDS.
COPY FROM(IN1) TO(T1) USING(CTL1)
//
* ADD '22' IDENTIFIER FOR FILE2 RECORDS.
COPY FROM(IN2) TO(T1) USING(CTL2)
//
* SPLICE TO MATCH UP RECORDS AND WRITE THEM TO THEIR
* APPROPRIATE OUTPUT FILES.
SPLICE FROM(T1) TO(OUT12) ON(1,400,CH) WITH(403,1) -
USING(CTL3)
/*
//CTL1CNTL DD *
* MARK FILE1 RECORDS WITH '11'
OUTREC FIELDS=(1,400,402:C'11')
/*
//CTL2CNTL DD *
* MARK FILE2 RECORDS WITH '22'
OUTREC FIELDS=(1,400,402:C'22')
/*
//CTL3CNTL DD *
* WRITE MATCHING RECORDS TO OUT12 FILE. REMOVE ID.
OUTFIL FNAMES=OUT12,INCLUDE=(402,2,CH,EQ,C'12'),OUTREC=(1,400)
* WRITE FILE1 ONLY RECORDS TO OUT1 FILE. REMOVE ID.
* OUTFIL FNAMES=OUT1,INCLUDE=(12,2,CH,EQ,C'11'),OUTREC=(1,10)
* WRITE FILE2 ONLY RECORDS TO OUT2 FILE. REMOVE ID.
* OUTFIL FNAMES=OUT2,INCLUDE=(12,2,CH,EQ,C'22'),OUTREC=(1,10)
/*



My TOOLMSG for this step is

Quote:

SYT000I SYNCTOOL RELEASE 1.4D - COPYRIGHT 2003 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT002I "TOOLIN" INTERFACE BEING USED

SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 0




I'm confused now !!
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed May 03, 2006 11:38 am    Post subject: Reply with quote
Quote:

Ok, I used the following code but got an empty file after a successful completion which leads me to my parameters for the OUTREC or the OUTFIL commands ??

I'm confused now !!



youtalkintome,

*Sigh* How do you expect us to help you without looking at your data ? How am I supposed to read your mind as to what output are you expecting?

One more thing , I showed you example to compare 2 files and get the matched records using JOIN feature and you come back with a different solution even after knowing that your version of syncsort does NOT support all the features of ICETOOL. You are just wasting my time as well as your time.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
youtalkintome
Beginner


Joined: 27 Apr 2006
Posts: 9
Topics: 1
PostPosted: Thu May 04, 2006 9:41 am    Post subject: Reply with quote
Kolusu,

I don't mean to waste your time. I'm trying everything to get this solution.
I read the thread on the Join that you posted but that merges 2 records from 2 files into one larger record in one file.. I want the 2 records still separate in one file.

I thought you understood what I was after.
Apologies for the confusion.

Does this help?
Code:

File A (input)
12345678aaaabbbbccccddddiiiikkkkjjjjeee        luejdnsuhhheeeed
38874869hhhheeeeyyyyyssssooooiiiieeesff        hfgfyrhekjhkhjkj
56473863ffffccccsssseeeewwwwfcvghbgfnjk        hytrfdewsaqwsxza
24353738hhhheeeeyyyyyssssooooiiiieeesff45857458kfjrudshytgtsgsh

File B (input)
29282726ghgsdalkjdaliujhnmbsadhsiudyiuy12345678skjhsduyeiuygytg

File C (Expected output)
29282726ghgsdalkjdaliujhnmbsadhsiudyiuy12345678skjhsduyeiuygytg
12345678aaaabbbbccccddddiiiikkkkjjjjeee        luejdnsuhhheeeed


Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Thu May 04, 2006 10:11 am    Post subject: Reply with quote
Quote:

I read the thread on the Join that you posted but that merges 2 records from 2 files into one larger record in one file.. I want the 2 records still separate in one file.

youtalkintome,

What makes you think that the Join feature can NOT write 2 seperate records?

Run this step as is and see the output for match dd
Code:

//STEP0200 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD *                                               
12345678AAAABBBBCCCCDDDDIIIIKKKKJJJJEEE        LUEJDNSUHHHEEEED
38874869HHHHEEEEYYYYYSSSSOOOOIIIIEEESFF        HFGFYRHEKJHKHJKJ
56473863FFFFCCCCSSSSEEEEWWWWFCVGHBGFNJK        HYTRFDEWSAQWSXZA
24353738HHHHEEEEYYYYYSSSSOOOOIIIIEEESFF45857458KFJRUDSHYTGTSGSH
//SORTJNF2 DD *                                               
29282726GHGSDALKJDALIUJHNMBSADHSIUDYIUY12345678SKJHSDUYEIUYGYTG
//MATCH    DD SYSOUT=*                                         
//SYSIN    DD *                                               
  JOINKEYS FILES=F1,FIELDS=(01,8,A)                           
  JOINKEYS FILES=F2,FIELDS=(40,8,A)                           
  REFORMAT FIELDS=(F1:1,65,                                   
                   F2:40,8,                                   
                   F2:1,65),FILL=X'FF'                         
  SORT FIELDS=(1,8,CH,A)                                       
  JOIN UNPAIRED                                               
  OUTFIL FNAMES=MATCH,                                         
  OMIT=(01,01,BI,EQ,X'FF',OR,                                 
        66,01,BI,EQ,X'FF'),                                   
  OUTREC=(1,65,/,74,65)                                       
/*                                                             


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group