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

Get Matched records - Rewrite KSDS file using JCL SORT


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

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Sep 05, 2008 9:54 pm
Reply with quote

Hi,
Job has 2 steps:
1) Get matching unique key-values from File1 and File2.
File 1 has non-dup records and file2 can have duplicates. One to many records.

File 1 - FB, LRECL - X(05)
Code:
BBBBB
EEEEE
CCCCC
AAAAA
DDDDD
FFFFF


File2 - FB - LRECL - X(15)
Code:
BBBBB3456789346
AAAAA1234567890
BBBBB6790436789
CCCCC1234567890
BBBBB4567892456
DDDDD5436789035
CCCCC4567892456
FFFFF111111111


Output1 - Sorted on fields 6,10 with no duplicates. FB-LRECL - X(10)
Code:
1234567890
3456789346
4567892456
5436789035
6790436789



I am stuck at this step itself. Getting empty records as output!...

This is the sort card:
Code:
//TOOLIN DD *                                   
SORT FROM(IN1) TO(T1) USING(CTL1)               
SORT FROM(IN2) TO(T1) USING(CTL2)               
SPLICE FROM(T1) TO(T2) ON(1,05,CH) -             
  WITHALL WITH(1,15) USING(CTL3)                 
 SORT FROM(T2) TO(OUT) USING(CTL4)               
/*                                               
//CTL1CNTL DD *                                 
  INREC OVERLAY=(16:C'11')                       
  SORT FIELDS=(1,05,CH,A)                       
/*                                               
//CTL2CNTL DD *                                 
  INREC OVERLAY=(16:C'22')                       
  SORT FIELDS=(1,05,CH,A)                       
/*                                               
//CTL3CNTL DD *                                 
  OUTFIL FNAMES=T2,INCLUDE=(16,2,CH,EQ,C'12'),   
    OUTREC=(1:6,10)                                 
/*                                               
//CTL4CNTL DD *                                 
  OPTION EQUALS                                 
  SORT FIELDS=(1,10,CH,A)   
/*                         


2) Using the output file output1, re-write another KSDS VSAM file for those key values.

Can this be done using SPLICE itself?

The field at (220,1) in KSDS should be set to 'Y' for those key values in file output file 1.

Please help me understand where did I went wrong in step1? Also is it possible to update KSDS file using ICETOOL?
Thanks,
Viji
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: Fri Sep 05, 2008 10:08 pm
Reply with quote

Quote:
1) Get matching unique key-values from File1 and File2.


It's not clear what this means. I can't tell which field you are matching on, sorting on, eliminating duplicates on, etc. What are the "rules" you're using to get from input to output for the first step? Please show which output values came from which input values (since the first field from the file2 values is not shown in the output values, I can't tell which input values you kept or why) and explain why those input values were kept.
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Sep 05, 2008 10:21 pm
Reply with quote

Thanks Frank.
I am sorry for not making it clear.
File1 and File2 are sorted and spliced on (1,5,ch) field.
After splice the output will be

Intermediate output: T2
Code:
BBBBB3456789346
BBBBB6790436789
BBBBB4567892456
CCCCC1234567890
CCCCC4567892456
DDDDD5436789035
FFFFF1111111111


(here BBBBB and CCCCC has one duplicate account number - total 7 records)

Output1 - Sorted and dups removed. FB-LRECL - X(10) - total 6 records.
Code:
1111111111
1234567890
3456789346
4567892456
5436789035
6790436789


Please let me know if you need any other details.
Thanks,
Viji
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Fri Sep 05, 2008 10:44 pm
Reply with quote

vijikesavan,

The following DFSORT/ICETOOL JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1      DD *                                               
BBBBB                                                         
EEEEE                                                         
CCCCC                                                         
AAAAA                                                         
DDDDD                                                         
FFFFF                                                         
//IN2      DD *                                               
BBBBB3456789346                                               
AAAAA1234567890                                               
BBBBB6790436789                                               
CCCCC1234567890                                               
BBBBB4567892456                                               
DDDDD5436789035                                               
CCCCC4567892456                                               
FFFFF1111111111                                               
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE) 
//T2       DD DSN=&&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)     
//OUT      DD SYSOUT=*                                         
//TOOLIN   DD *     
//TOOLIN   DD *                                           
  COPY FROM(IN1) USING(CTL1)                               
  COPY FROM(IN2) USING(CTL2)                               
  SPLICE FROM(T1) TO(T2) ON(1,5,CH) KEEPNODUPS -           
  WITHALL WITH(01,15) WITH(22,1) USING(CTL3)               
  SORT FROM(T2) TO(OUT) USING(CTL4)                       
//CTL1CNTL DD *                                           
  OUTFIL FNAMES=T1,BUILD=(1,5,16:1,5,C'11')               
//CTL2CNTL DD *                                           
  OUTFIL FNAMES=T1,OVERLAY=(16:5X,C'22')                   
//CTL3CNTL DD *                                           
  OUTFIL FNAMES=T2,BUILD=(6,10),INCLUDE=(21,2,CH,EQ,C'12')
//CTL4CNTL DD *                                           
  OPTION EQUALS                                           
  SORT FIELDS=(1,10,CH,A)                                 
  SUM FIELDS=NONE                                         
/* 
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Sep 05, 2008 11:00 pm
Reply with quote

Thanks Skolusu.
This worked fine. I understand we need to make these 2 files of equal length. Is there is any significance to have 5 spaces after 16th position or its just a random one?

For my step 2, Can I update KSDS using similar technique?

Appreciate your help.
Thanks,
Viji
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: Fri Sep 05, 2008 11:06 pm
Reply with quote

<Note: I posted this before I saw Kolusu's post>

Well, you really didn't explain what you wanted to do, but I played around with your job and figured it out. You want to match file1 and file2 on the first field, and for the resulting file2 records keep unique values for the second field in file2. The intermediate output you showed should have the AAAAA record, but you didn't show that record which threw me off until I figured it should be there.

A few problems with your job:

You don't have to SORT each file - you can COPY them and SPLICE will sort them.

You're putting an id in 16-17 and trying to INCLUDE on an id of '12', but you use WITH(1,15) so you'll never get an id of '12'. That's why you're not getting any output records. You should be using WITH(1,16) and checking for an id of '21'.

Your CTL4CNTL statements don't actually eliminate duplicates or add the 'Y'.

Here's a DFSORT/ICETOOL job that I think will do what you want.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/5)
//IN2 DD DSN=...  input file2 (FB/15)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,05,CH) -
  WITHALL WITH(1,16) USING(CTL3)
SELECT FROM(T2) TO(OUT) ON(1,10,CH) FIRST VSAMTYPE(F)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(16:C'11')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(16:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=T2,INCLUDE=(16,2,CH,EQ,C'21'),
    BUILD=(1:6,10)
/*
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Sep 05, 2008 11:56 pm
Reply with quote

Thanks for your valuable inputs Frank. yes AAAAA was there, sorry my bad typo!
My first part of requirement is complete.

Actually my requirement to step2 is as follows.

As per my understanding, the last step in your JCL writes the records into an output vsam file with 'y' in 220th position.

I have a KSDSfile say - FILEKSDS
Lrecl - 1300 , key length -10
Code:
0------------------------------------------------220-------------------------1300
1111111111 asxdrff...............................N............................. 
1234567890 98sajkhgaiug .........................A.............................
3456789346 ioeugdauyg796t7e6usvjks...............G.............................
4567892456 kwuytw986sijhgnkshgniusym.............U.............................
5436789035 sdguhsiguyiygsiuhgsig.................E.............................
6790436789 9867987jshn isug87g6b 7y..............O.............................
7876436633 9867987jshn isug87g6b 7y..............w.............................
8234836123 9867987jshn isug87g6b 7y..............Q.............................


After: (only account numbers matched from output file from step1 will have 'Y')
Code:
0------------------------------------------------220-------------------------1300
1111111111 asxdrff...............................Y............................. 
1234567890 98sajkhgaiug .........................Y.............................
3456789346 ioeugdauyg796t7e6usvjks...............Y.............................
4567892456 kwuytw986sijhgnkshgniusym.............Y.............................
5436789035 sdguhsiguyiygsiuhgsig.................Y.............................
6790436789 9867987jshn isug87g6b 7y..............Y.............................
7876436633 9867987jshn isug87g6b 7y..............W.............................
8234836123 9867987jshn isug87g6b 7y..............Q.............................


matched records
When (1301,2,CH,EQ,C'12') then Y in 220th position.
Other records from KSDSfile as is when (1301,2,ch,eq,c'11') (Assuming 11 is for KSDS file)

Hope I am making clear. Otherwise please let me know. Appreciate your help as always.
Thanks,
Viji
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Sep 05, 2008 11:58 pm
Reply with quote

My final step is to update KSDS file instead of writing into a VSAM file.
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: Sat Sep 06, 2008 1:04 am
Reply with quote

It's difficult to get a handle on what you want since you keep changing your requirements.

If you only want the 'Y' for matched records, you can use an IFTHEN, e.g.

Code:

   OUTFIL FNAMES=T2,
      IFTHEN=(WHEN=(p,2,CH,EQ,C'id'),OVERLAY=(220:C'Y'))


where p is the starting position of the idenfier and id is the identifier (21 in my job) you want the 'Y' for.

Both Kolusu and I used INCLUDE to only get the matched records based on your original job. But it appears you also want the unmatched records.

Quote:
My final step is to update KSDS file instead of writing into a VSAM file.


Huh? the VSAM output file (//OUT) is the KSDS file. You can update the KSDS file this way as long as the keys defined for the KSDS file are in the correct binary order (which I assume they are).
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Sat Sep 06, 2008 1:15 am
Reply with quote

I didnt specify about the unmatched records bse I was not sure if it is possible to re-write KSDS file.
There are three files involded

File1 - FB 5
File2 - FB 15
FileKSDS - KSDS VSAM File FB 1300, Key length 10.

FIRST STEP:
Get matched records from File1 and File2 into output file say OUT1.
This is accomplished by the Jcls provided by you and Kolusu.

SECOND STEP:

Input are Files OUT1 and FileKSDS.

For all the matching record for OUT1 in FileKSDS, 220th position to be set as 'Y' in FileKSDS.

Basically re-writing KSDS file. Matched records should have 'Y' in 220th position and unmatched from KSDS should remain as it is.

Hope this is clear now. Sorry for the confusion.

Thanks,
Viji
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: Sat Sep 06, 2008 1:56 am
Reply with quote

Well, I think I understand what you want so I'll give it a shot. Some caveats:

I wasn't sure if you wanted OUT1 as a separate file so I wrote it as one. If you don't need OUT1 as a separate file, you can remove the //OUT1 DD and the OUTFIL FNAMES=OUT1 statement from CTL4CNTL.

I also wasn't sure if you wanted to write the final output to the original VSAM input data set so I didn't. If you want to do that, you can use TO(IN3) in the second SPLICE and FNAMES=IN3 in CTL6CNTL, and remove the //OUT DD. However, if you use the same file (IN3) for input and output that way and something goes wrong with the job (e.g. I/O error), you could lose that file (so you should have a backup).

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/5)
//IN2 DD DSN=.... input file2 (FB/15)
//IN3 DD DSN=...  input file3 (VSAM KSDS F/1300)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T3 DD DSN=&&T3,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//*  Remove //OUT1 if you don't need OUT1 as a separate file
//OUT1 DD DSN=...  output file from step1 (FB/10)
//* Remove //OUT if you want to use IN3 for the final output
//OUT DD DSN=...  output file from step2 (VSAM KSDS F/1300)
//TOOLIN DD *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,5,CH) -
  WITHALL WITH(1,16) USING(CTL3)
SELECT FROM(T2) TO(T3) ON(1,10,CH) FIRST USING(CTL4)
COPY FROM(IN3) TO(T3) USING(CTL5)
* Change TO(OUT) to TO(IN3) if you want to use IN3 for the final output
SPLICE FROM(T3) TO(OUT) ON(1,10,CH) KEEPNODUPS -
  WITH(1,1301) USING(CTL6)
/*
//CTL1CNTL DD *
  INREC OVERLAY=(16:C'11')
/*
//CTL2CNTL DD *
  INREC OVERLAY=(16:C'22')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=T2,INCLUDE=(16,2,CH,EQ,C'21'),
    BUILD=(1:6,10)
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=T3,OVERLAY=(1301:C'BB')
* Remove next OUTFIL if you don't need OUT1 as
* a separate file
  OUTFIL FNAMES=OUT1
/*
//CTL5CNTL DD *
  INREC OVERLAY=(1301:C'VV')
//CTL6CNTL DD *
* Change FNAMES=OUT to FNAMES=IN3 if you want
* to use IN3 for the final output
  OUTFIL FNAMES=OUT,INCLUDE=(1301,2,SS,EQ,C'VB,VV'),
    IFOUTLEN=1300,
    IFTHEN=(WHEN=(1301,2,CH,EQ,C'VB'),OVERLAY=(220:C'Y'))
/*
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Sat Sep 06, 2008 2:18 am
Reply with quote

Thanks Frank. i dont want OUT1 as a separate file. Letme change and try this.
Thanks for your help.
Viji
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 How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top