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

Match and update the key


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Thu Mar 25, 2010 4:27 pm
Reply with quote

Hi ,

I have the following requirement to be done in ICETOOL. I have reffered all the manuals but couldn't get a single solution.

I have two input files:-


Input 1:- FIle is FB with rec length 20
The key fields are from 1 to 12, 18 to 20
030401000402WWWER200
030402000402WWWER200
030403000402WWWER200
030404000402WWWER200
030405000402WWWER200


Input 2:- File is FB. Rec length is 15
The key fields are from 1 to 12, 13 to 15

030401000402200
030403000402200
030405000402200

Output : File is FB. Rec length is 20

030401000412WWWER200
030402000402WWWER200
030403000412WWWER200
030404000402WWWER200
030405000412WWWER200

Please solve the above problem.

Thanks,
Thangaprabhu.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Mar 25, 2010 4:46 pm
Reply with quote

Can input file 1 and input file2 have duplicates?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Thu Mar 25, 2010 4:49 pm
Reply with quote

Because the solution for sort related questions may vary from product to product, please ensure that you state clearly which sort product you are using.

As ICETOOL may be used as an alias for SYNCTOOL, please follow the steps below to find out.

If you are not sure, then by running a simple sort step shown below, you will be able to find out for yourself.

If the messages start with ICE then your product is DFSORT. Please also post the output of the complete line which has a message code ICE201I, as this will enable our DFSORT experts to determine which release of DFSORT that you have installed. This may also affect the solution offered.

If the messages start with WER or SYT then the product is SYNCSORT and the topic will be moved into the JCL forum by one of the moderators. Please also post the information telling which version of SYNCSORT is installed, as this may also affect the solution offered.

Thank you for taking your time to ensure that the valuable time of others is not wasted by offering inappropriate solutions which are not relevant due to the sort product being used and/or the release that is installed in your site.

Code:
//SORTSTEP EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN   DD *
ABC
//SORTOUT  DD SYSOUT=*
//SYSIN    DD *
  SORT     FIELDS=COPY


You also need to provide more detail than you already have.
What is the RECFM and LRECL of both input files.
You have shown the expected output, but have not explained the rules from going from input to output. These are required to be able to try and help.

Thangaprabhu wrote:
Please solve the above problem.
Please remember that this is a HELP forum rather than a Do it for me forum.
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Thu Mar 25, 2010 5:03 pm
Reply with quote

Hi Expat,

I got the below message after running the sortstep

SYNCSORT FOR Z/OS 1.3.2.1N U.S. PATENTS: 4210961, 5117495 (C) 2007 SYNCSO
z/OS 1.9.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 17B9E, MODEL 2097 710 LICEN
SYSIN :
SORT FIELDS=COPY
WER276B SYSDIAG= 1679160, 3739009, 3739009, 2207118
WER164B 8,884K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 424K BYTES USED
WER146B 12K BYTES OF EMERGENCY SPACE ALLOCATED
WER108I SORTIN : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER110I SORTOUT : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER410B 7,856K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER410B 0 BYTES RESERVE REQUESTED, 308K BYTES USED
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER416B BSAM WAS USED FOR SORTIN
WER416B BSAM WAS USED FOR SORTOUT
WER054I RCD IN 1, OUT 1
WER169I RELEASE 1.3 BATCH 0492 TPF LEVEL 2.1

Hi Sambaji,

There can be duplicates too.

Thanks,
Thangaprabhu.
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Thu Mar 25, 2010 6:00 pm
Reply with quote

Hi all,

Input file 1:- FIle is FB with rec length 20
Input file 2:- FIle is FB with rec length 15
Output file 1:- FIle is FB with rec length 20

The are two keys.

Input file 1:-
The key fields are from 1 to 12, 18 to 20

Input file 2:-
The key fields are from 1 to 12, 13 to 15

1) There can be duplicates in file 1. After sort there should be only one unique record for the key.

Please let me know if i have to add any other details.

Thanks,
Thangaprabhu
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Fri Mar 26, 2010 12:05 pm
Reply with quote

Solution either by syncsort or icetool will be sufficient.

Thanks,
Thangaprabhu
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Mar 26, 2010 12:20 pm
Reply with quote

Thangaprabhu wrote:
Solution either by syncsort or icetool will be sufficient.
Please be aware that your installed sort product is SYNCSORT, and as such is associated with SYNCTOOL.

ICETOOL is probably an alias pointing to the SYNCSORT equivilent of DFSORTs ICETOOL. To be absolutely certain run an ICETOOL step and check the sysout message codes against my original post.
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Fri Mar 26, 2010 12:34 pm
Reply with quote

Hi Expat,

The sort product installed in my machine is SYNCSORT.

Will there be different solutions for SYNCSORT installed machine and ICETOOL installed machine?.

Thanks,
Thangaprabhu
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Fri Mar 26, 2010 12:37 pm
Reply with quote

Hi,

I can add one more field in input file 2.

Input 2:- File is FB. Rec length is 15
The key fields are from 1 to 12, 13 to 15

030401000402200412
030403000402200412
030405000402200412

Thanks,
Thangaprabhu
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Mar 26, 2010 1:10 pm
Reply with quote

Thangaprabhu wrote:
The sort product installed in my machine is SYNCSORT.

Will there be different solutions for SYNCSORT installed machine and ICETOOL installed machine?.
Yes, they are two completely independant products supplied by different vendors.
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Fri Mar 26, 2010 1:13 pm
Reply with quote

If any of the above requirement is unclear.

I can give out some other examples.

Thanks,
Thangaprabhu
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Fri Mar 26, 2010 3:24 pm
Reply with quote

Code:
//A0372526  JOB (TDEV,TST),'A037252',MSGCLASS=9,                     
//         CLASS=C,NOTIFY=A037252,TIME=(1,20)                     <JX5
//S1        EXEC  PGM=ICETOOL                                         
//TOOLMSG   DD  SYSOUT=*                                             
//DFSMSG    DD  SYSOUT=*                                             
//IN1       DD *                                                     
111111111111111111100004078 M2 51791                                 
222222222222222222200004154 M2 67861                                 
333333333333333333300000096 M2 76481                                 
444444444444444433300000024 M2 43321                                 
//IN2       DD *                                                     
000000001FH7777777777777777777777777777777777777777777               
03460000000021111111111111111111000FPK0420100000 000000000000000000000
03460000000032222222222222222222000FPK0420100000 000000000000000000000
03460000000043333333333333333333000FPK0420100000 000000000000000000000
03460000000054444444444444444333000FPK0420100000 000000000000000000000
000000006FT9999999999999999999999999999999999999999999               
//OUT       DD SYSOUT=*                                               
//T1        DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS) 
//TOOLIN    DD *                                                 
COPY FROM(IN1) TO(T1) USING(CTL1)                               
COPY FROM(IN2) TO(T1) USING(CTL2)                               
SPLICE FROM(T1) TO(OUT) ON(649,1,CH) ON(14,19,CH) KEEPNODUPS -   
  WITH(1,43) WITH(49,600) USING(CTL3)                           
/*                                                               
//CTL1CNTL  DD *                                                 
  INREC BUILD=(14:1,19,44:32,5,649:C'1')                         
/*                                                               
//CTL2CNTL  DD *                                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(649:C'1')),                   
    IFTHEN=(WHEN=(10,2,CH,EQ,C'FH'),OVERLAY=(649:C'0')),         
    IFTHEN=(WHEN=(10,2,CH,EQ,C'FT'),OVERLAY=(649:C'9'))         
/*                                                               
//CTL3CNTL  DD *                                                 
  OUTFIL FNAMES=OUT,BUILD=(1,648)                               
/*                                                               


The above code has a requirement similar to my requirement but it doesn't update the key.

Here the unmatched records are appended with '0' and '9', the matched records is appended with '1'. Then the records are spliced for matched records with one of the field from file1 is added to file2 for the matched records.

I am ready to get the problem solved with two SYNCSORT steps.
Back to top
View user's profile Send private message
Thangaprabhu

New User


Joined: 15 Jul 2009
Posts: 16
Location: INDIA

PostPosted: Mon Mar 29, 2010 1:51 pm
Reply with quote

Hi Guys,

After a long struggle got the solution by myself in two steps.

Code:
//A0372527  JOB (TDEV,TST),'A037252',MSGCLASS=9,                       
//         CLASS=H,NOTIFY=A037252                                       
/*JOBPARM S=(SYTA,SYTB)                                                 
//S1       EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//F1IN     DD DSN=IMNT.NT.IMN9B124.LOCAUD.STMP.PARTA.G0010V00,DISP=SHR 
//F2IN     DD DSN=IMNT.NT.IMN9B124.VIP.RETAIL.FILEY,DISP=SHR           
//T1       DD DSN=&&T1,DISP=(MOD,PASS),UNIT=SYSDA,                     
//            SPACE=(CYL,(5,5),RLSE)                                   
//MATCH    DD DSN=A037252.COURSE.MATCH,DISP=(NEW,CATLG,DELETE),         
//            SPACE=(CYL,(5,5)),UNIT=3390                               
//F1ONLY   DD DSN=A037252.COURSE.INONLY,DISP=(NEW,CATLG,DELETE),       
//            SPACE=(CYL,(5,5)),UNIT=3390                               
//TOOLIN   DD *                                                         
   COPY FROM(F1IN) TO(T1) USING(MATA)                                   
   COPY FROM(F2IN) TO(T1) USING(MATB)                                   
   SPLICE FROM(T1) TO(MATCH) ON(1,8,BI) ON(1897,3,CH)-                 
     WITH(1901,1) KEEPNODUPS USING(MATC)                               
/*                                                                     
//MATACNTL DD *                                                         
  OUTREC FIELDS=(1:1,8,                                                 
                1897:1897,3,                                           
                1900:C'11')                                             
/*                                                                     
//MATBCNTL DD *                                                         
  OUTREC FIELDS=(1:1,8,                                                 
              1897:9,3,                                                 
              1900:C'22')                                               
//MATCCNTL DD *                                                         
  OUTFIL FNAMES=MATCH,INCLUDE=(1900,2,CH,EQ,C'12'),                     
  OUTREC=(1:1,6,                                                       
          7:X'019C',                                                   
          9:9,1891)                                                     
  OUTFIL FNAMES=F1ONLY,INCLUDE=(1900,2,CH,EQ,C'11'),OUTREC=(1,1899)     
/*                                                                     
//STEP020  EXEC SORTD5                                                 
//**********************************************************************
//SORTIN   DD  DSN=A037252.COURSE.MATCH,                               
//             DISP=SHR                                                 
//         DD  DSN=A037252.COURSE.INONLY,                               
//             DISP=SHR                                                 
//*                                                                     
//SORTOUT  DD  DSN=A037252.COURSE.MATCH.VIP,                           
//             DISP=(NEW,CATLG,DELETE),                                 
//             UNIT=PERMDA,                                             
//             SPACE=(CYL,(215,22),RLSE),                               
//             DCB=(DSCB1,RECFM=FB,LRECL=1899,BLKSIZE=18990)           
//*                                                                     
//SYSIN    DD  *                                                       
 SORT FIELDS=(1,8,BI,A,1897,3,CH,A)                                     
/*                                                                     
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts Read a flat file and update DB2 table JCL & VSAM 2
No new posts how to update an ISR appl var from an... TSO/ISPF 8
No new posts DB2 SQL query to read and update data... DB2 12
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts JOINKEYS to match two input files on ... DFSORT/ICETOOL 3
Search our Forums:

Back to Top