Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to concatinate the record of the matching fields

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Thu Mar 08, 2007 7:55 pm    Post subject: How to concatinate the record of the matching fields
Reply with quote

first 3 bytes is a key field

Input1
------
E01 11
E02 12
E03 13
E04 14

Input2
------
E02 21
E03 21
E05 23

Input3
------
E01 31
E03 32
E07 33

Output
-------
T1 E01 11
T3 E01 31
T1 E02 12
T2 E02 21
T1 E03 13
T2 E03 21
T3 E03 32
T1 E04 14
Back to top
View user's profile Send private message

santhunaveen

New User


Joined: 22 Sep 2006
Posts: 33

PostPosted: Thu Mar 08, 2007 8:29 pm    Post subject:
Reply with quote

Hi plz go thru.....this suedo code ..may helps u..

first concatenate the 3 files then sort......

Code:

//STEP01  EXEC PGM=IEBGENER                                             
//SYSDBOUT DD SYSOUT=*                                                 
//SYSABEND DD SYSOUT=*                                                 
//SYSUT2   DD DSN=OUT PUT FILE NAME,                             
//            DISP=(NEW,CATLG),                                         
//            UNIT=SYSDA,                                               
//            DCB=(MLDSCB,RECFM=FB,LRECL=80,BLKSIZE=3840),             
//            SPACE=(CYL,(200,10),RLSE)                                 
//SYSUT1   DD DSN=INPUT FILE1,DISP=SHR                             
//*        DD DSN=INPUT FILE2,DISP=SHR                       
//*        DD DSN=INPUT FILE3,DISP=SHR                       
//*                                                                     
//SYSIN    DD DUMMY     
//SYSPRINT DD SYSOUT=*               
//SYSOUT   DD SYSOUT=*               
//* 
//STEP02  EXEC PGM=SORT,COND=(4,LT),PARM='CORE=MAX'           
//SORTIN    DD DSN=MSDV.TEST.D040183.DATA,                     
//             DISP=SHR                                       
//SORTOUT   DD DSN=MSDV.TEST.D040183.DATA.SORTF1,             
//             DISP=(NEW,CATLG,DELETE),                       
//             UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),           
//             DCB=MLDSCB                                     
//SYSIN     DD *
SORT FIELDS=(01,03,CH,A,04,06,ZD,A))  /*just give sort fields according to u'r key field  )
SUM FIELDS=NONE           
//*                                                         
//SORTWK01  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK02  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK03  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK04  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK05  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK06  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK07  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK08  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK09  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//SORTWK10  DD UNIT=SYSDA,SPACE=(CYL,(50,25),RLSE)             
//*                                                                     
//SYSOUT   DD  SYSOUT=*                    /* SYSTEM OUTPUT             
//SYSPRINT DD  SYSOUT=*                    /* SYSTEM PRINT             
//SYSTSPRT DD  SYSOUT=*                    /* SYSTEM SYSTSPRT           
//SYSDBOUT DD  SYSOUT=*                /* SYSTEM VS-COBOL/II DEBUG 
//SYSUDUMP DD  SYSOUT=*                    /* SYSTEM DUMP               
//SYSABOUT DD  SYSOUT=*                    /* SYSTEM SYSABOUT           
//*
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Thu Mar 08, 2007 8:42 pm    Post subject:
Reply with quote

After sorting how i will check differenciate the matching field.....

the key from first file which doesn't matches should not come in the output file...

how i will do that?
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Thu Mar 08, 2007 8:47 pm    Post subject:
Reply with quote

Key 'E05' in file2 and 'E07' in file3 is not in the ouput file

output suppose to be like this...

The record from file2&file3 which doesn't matches it should not be in the output file..
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Mar 08, 2007 10:15 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1
//IN2 DD DSN=...  input file2
//IN3 DD DSN=...  input file3
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
SPLICE FROM(T1) TO(OUT) ON(4,3,CH) KEEPBASE KEEPNODUPS -
  WITHALL WITH(1,10) USING(CTL4)
/*
//CTL1CNTL DD *
  INREC BUILD=(C'T1',4:1,6,10:C'BB')
/*
//CTL2CNTL DD *
  INREC BUILD=(C'T2',4:1,6,10:C'VV')
/*
//CTL3CNTL DD *
  INREC BUILD=(C'T3',4:1,6,10:C'VV')
/*
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(10,2,CH,NE,C'VV'),
    BUILD=(1,9)
/*
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Fri Mar 09, 2007 7:14 pm    Post subject:
Reply with quote

Frank.....

with out knowing the exact function of

SPLICE FROM(T1) TO(OUT) ON(4,3,CH) KEEPBASE KEEPNODUPS -
WITHALL WITH(1,10) USING(CTL4)

I can't go further..

pls help me ...


Frank...
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Fri Mar 09, 2007 7:18 pm    Post subject:
Reply with quote

Expected output is

Ignore the previous Format which i submitted

consider this one...

First two byte is a key field

The respective record which matches the key field
from input-1 to Input-2 and Input-3 should be in output file.


Input-1
--------

E1 first file record
E3 first file record
E4 first file record
E6 first file record

Input-2
-------

E1 second file record
E2 second file record
E3 second file record
E3 second file record DUp



Input-3
-------

E3 third file record
E4 third file record
E5 third file record

Ouput
-----
T1 E1 first file record
T2 E1 second file record
T1 E3 first file record
T2 E3 second file record
T2 E3 second file record Dup
T3 E3 third file record
T1 E4 first file record
T3 E4 third file record
T1 E6 first file record

First file record should be prefix with 'T1' in the output
Second file record should be prefix with 'T2' in the output
third file record should be prefix with 'T3' in the output

My record length is 150
Recfm is FB


"E2 second file record "
is not in the output file
coz no respective key field in Input-1

"E5 third file record"
is not in the output file
coz no respective key field in Input-1

The key 'E3' matches in all the file
so respective records are concatinated according to that..

The key 'E6' not matches in second and third output
file
so E6 first file record is in the output file without respective record from other file

Pls let me knw...

hope u understood..
Back to top
View user's profile Send private message
santhunaveen

New User


Joined: 22 Sep 2006
Posts: 33

PostPosted: Fri Mar 09, 2007 8:56 pm    Post subject:
Reply with quote

Hi,

Do 1 thing first compare file 1 and file2 then u will get 1 matching file...after tht compare tht matching file with the 3 rd file then u will get desired output......

Code:

//STEP0100 EXEC PGM=ICETOOL                                             
//TOOLMSG  DD SYSOUT=*                                                 
//DFSMSG   DD SYSOUT=*                                                 
//*FIRST FILE                                                           
//IN1      DD DSNAME= 1st fil name, DISP=SHR           
//*SECOND FILE                                                         
//IN2      DD DSNAME=2nd fil name,DISP=SHR                   
//T1       DD DSN=&T1,SPACE=(CYL,(5,5),RLSE),DISP=(MOD,PASS)           
//T2       DD DSN=&T2,SPACE=(CYL,(5,5),RLSE),DISP=(MOD,PASS)           
//CONCT    DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,DELETE)                   
//         DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,DELETE)                   
//* THIS FILE WILL HAVE THE MATCHING RECORDS WHICH EXIST               
//* IN BOTH THE FILES                                                   
//OUT12    DD DSNAME=give some file name,                   
//            DISP=(NEW,CATLG,DELETE),                                 
//            UNIT=SYSDA,                                               
//            SPACE=(CYL,(10,5),RLSE),                                 
//            DCB=*.IN1                                                 
//*           DCB=(RECFM=FB,LRECL=17,BLKSIZE=1700)                     
//* THIS FILE WILL HAVE THE RECORDS FROM FIRST FILE           
//* WITH NO MATCHING FOUND                                     
//OUT1     DD DSNAME=for un matching recds give 1 file name,         
//            DISP=(NEW,CATLG,DELETE),                         
//            UNIT=SYSDA,                                     
//            SPACE=(CYL,(10,5),RLSE),                         
//            DCB=*.IN1                                       
//*           DCB=(RECFM=FB,LRECL=17,BLKSIZE=1700)             
       
//TOOLIN   DD *                                               
  COPY FROM(IN1) TO(T1) USING(CTL1)                           
  COPY FROM(IN2) TO(T2) USING(CTL2)                           
   SPLICE FROM(CONCT) TO(OUT12) ON(1,08,CH) WITH(10,1) -           
   USING(CTL3) KEEPNODUPS     //this will ntg but removes duplicates                                     
 /*                                                                 
 //CTL1CNTL DD *                                                   
   OUTREC FIELDS=(1:1,08,9:C'11')        //here give sort key fields                           
 //CTL2CNTL DD *                                                   
   OUTREC FIELDS=(1:12,08,9:C'22')        //here give sort key                           
 //*MATCHING CRITERION SHOULD BE SPECIFIED HERE                     
 //CTL3CNTL DD *                                           //here give sort key         
   OUTFIL FNAMES=OUT12,INCLUDE=(9,2,CH,EQ,C'12'),OUTREC=(1,08)     
   OUTFIL FNAMES=OUT1,INCLUDE=(9,2,CH,EQ,C'11'),OUTREC=(1,08)       
 /*
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Sat Mar 10, 2007 10:04 am    Post subject:
Reply with quote

I don't what exactly the below statement doing..

please explain me..

SPLICE FROM(CONCT) TO(OUT12) ON(1,08,CH) WITH(10,1) -
USING(CTL3) KEEPNODUPS //this will ntg but removes duplicates


Mainly pls explain abt WITH(10,1)...what exact function it is doing..


with out this i can't go further.

Because Iam having LRECL=150

and Key field is first 23 bytes

according to that i have to update ..

so please explain it clearly..
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sat Mar 10, 2007 3:00 pm    Post subject:
Reply with quote

ibmmainframesyntel wrote:
Mainly pls explain abt WITH(10,1)...what exact function it is doing..
Have you looked in the manual for guidance?
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Sat Mar 10, 2007 5:42 pm    Post subject:
Reply with quote

Hi got it....but there is one problem...

According to the JCl which i coded
it will work for the file LRECL=76,ReCFM=FB,BLKSIZE=76000

But
actual file attributes is

First input LRECL=3504,RECFM=VB,BLKSIZE=27998
Second input LRECL=2504,RECFM=VB,BLKSIZE=27998
Third input LRECL=1004,RECFM=VB,BLKSIZE=27998

pls let me know how to match for the following JCL according to the
above attributes..

i tried with that........am getting attributes mismatch...

Code:
                   
//DELT EXEC PGM=IDCAMS                                       
//SYSPRINT DD SYSOUT=*                                       
//SYSOUT   DD SYSOUT=*                                       
//SYSIN    DD *                                             
    DEL 'I088.FLAT.SEQ.INPUT8'                               
   IF MAXCC = 08 THEN SET MAXCC = 00                         
/*                                                           
//S1    EXEC  PGM=ICETOOL                                   
//TOOLMSG DD SYSOUT=*                                       
//DFSMSG  DD SYSOUT=*                                       
//IN1 DD DSN=I088.FLAT.SEQ.INPUT5,DISP=SHR                   
//IN2 DD DSN=I088.FLAT.SEQ.INPUT6,DISP=SHR                   
//IN3 DD DSN=I088.FLAT.SEQ.INPUT7,DISP=SHR                   
//T1 DD DISP=(,PASS),                                       
 //    SPACE=(CYL,(1,5))           
 //T2 DD DISP=(,PASS),                                                   
 //    SPACE=(CYL,(1,5))           
 //T3 DD DISP=(,PASS),                                                   
 //    SPACE=(CYL,(1,5))           
 //TT DD DSN=*.T1,DISP=(OLD,PASS),VOL=REF=*.T1                           
 //   DD DSN=*.T2,DISP=(OLD,PASS),VOL=REF=*.T2                           
 //   DD DSN=*.T3,DISP=(OLD,PASS),VOL=REF=*.T3                           
 //OUT1 DD DISP=(,PASS),                                                 
 //    SPACE=(CYL,(1,5))           
 //OUT2 DD DISP=(,PASS),                                                 
 //    SPACE=(CYL,(1,5))           
 //OUT3  DD DISP=(,PASS),                                               
 //     SPACE=(CYL,(1,5))           
 //OUT4 DD DISP=(,PASS),                                                 
 //    SPACE=(CYL,(1,5))           
 //OUT5 DD DISP=(,PASS),                                                 
 //    SPACE=(CYL,(1,5))           
 //OUT6 DD DISP=(,PASS),                                                 
//    SPACE=(CYL,(1,5))     
//OUT7 DD DSN=*.OUT4,DISP=(OLD,PASS),VOL=REF=*.OUT4             
//     DD DSN=*.OUT5,DISP=(OLD,PASS),VOL=REF=*.OUT5             
//     DD DSN=*.OUT6,DISP=(OLD,PASS),VOL=REF=*.OUT6             
//OUT8 DD DSN=I088.FLAT.SEQ.INPUT8,DISP=(,CATLG),               
//    SPACE=(CYL,(1,5))     
//TOOLIN   DD *                                                 
  COPY FROM(IN1) TO(T1) USING(CTL1)                             
  COPY FROM(IN2) TO(T2) USING(CTL2)                             
  COPY FROM(IN3) TO(T3) USING(CTL3)                             
   SPLICE FROM(TT) TO(OUT1) ON(1,23,CH) KEEPBASE  KEEPNODUPS -   
   WITH(1,26) WITH(28,1) USING(CTL4) WITHALL                     
 SORT FROM(OUT1) USING(CTL5)                                     
 SORT FROM(OUT2) USING(CTL6)                                     
 SORT FROM(OUT3) USING(CTL7)                                     
 SORT FROM(OUT7) USING(CTL8)                                     
/*                                                               
//CTL1CNTL DD *                                                 
   OUTREC FIELDS=(1:1,26,27:C'11')                               
 //CTL2CNTL DD *                                                       
    OUTREC FIELDS=(1:1,26,27:C'22')                                     
 //CTL3CNTL DD *                                                       
    OUTREC FIELDS=(1:1,26,27:C'33')                                     
 //CTL4CNTL DD *                                                       
    OUTFIL FNAMES=OUT1,INCLUDE=(27,2,CH,EQ,C'11'),OUTREC=(1:1,76)       
    OUTFIL FNAMES=OUT2,INCLUDE=(27,2,CH,EQ,C'12'),OUTREC=(1:1,76)       
    OUTFIL FNAMES=OUT3,INCLUDE=(27,2,CH,EQ,C'13'),OUTREC=(1:1,76)       
 /*                                                                     
 //CTL5CNTL DD *                                                       
    INREC FIELDS=(1:C'E1 ',4:1,26,47X)                                 
    SORT FIELDS=(4,23,CH,A)                                             
    OUTFIL FNAMES=OUT4                                                 
 /*                                                                     
 //CTL6CNTL DD *                                                       
    INREC FIELDS=(1:C'E2 ',4:1,26,47X)                                 
    SORT FIELDS=(4,23,CH,A)                                             
    OUTFIL FNAMES=OUT5                                                 
 /*                                                                     
//CTL7CNTL DD *                                                         
   INREC FIELDS=(1:C'E3 ',4:1,26,47X)                                   
   SORT FIELDS=(4,23,CH,A)                                             
   OUTFIL FNAMES=OUT6                                                   
/*                                                                     
//CTL8CNTL DD *                                                         
   SORT FIELDS=(4,23,CH,A)                                             
   OUTFIL FNAMES=OUT8                                                   
/*                                                                     
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sat Mar 10, 2007 10:53 pm    Post subject:
Reply with quote

I don't know why you're using all those extra SORT operators for the FB file - they aren't needed.

But for a VB file, you have to deal with the RDW in positions 1-4 and the fact that the records can be variable length.

Here's a DFSORT/ICETOOL job that will do what you asked for your latest "change":

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1 DD DSN=...  input file1 (VB/3504)
//IN2 DD DSN=...  input file2 (VB/2504)
//IN3 DD DSN=...  input file3 (VB/1004)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (VB/33)
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
COPY FROM(IN3) TO(T1) USING(CTL3)
SPLICE FROM(T1) TO(OUT) ON(10,2,CH) KEEPBASE KEEPNODUPS VLENOVLY -
  WITHALL WITH(6,29) USING(CTL4)
/*
//CTL1CNTL DD *
* INREC BUILD=(C'T1',4:1,30,35:C'BB')
  INREC BUILD=(1,4,5:C'BB',7:C'T1 ',10:5)
//CTL2CNTL DD *
* INREC BUILD=(C'T2',4:1,30,35:C'VV')
  INREC BUILD=(1,4,5:C'VV',7:C'T2 ',10:5)
//CTL3CNTL DD *
* INREC BUILD=(C'T3',4:1,30,35:C'VV')
  INREC BUILD=(1,4,5:C'VV',7:C'T3 ',10:5)
//CTL4CNTL DD *
  OUTFIL FNAMES=OUT,INCLUDE=(5,2,CH,NE,C'VV'),
    BUILD=(1,4,5:7,29),VLFILL=C' '
/*


Next time you need help, please spend some more time figuring out exactly what you need before you post the first time. That will save us all a lot of time.
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Sun Mar 11, 2007 11:35 am    Post subject:
Reply with quote

VLFILL=C' '
VLENOVLY

what exactly it does?

First four byte what it will do?


What is RDW?

i don't knw ...

before posting this query i don't knw abt splice...

but now i came to knw ....it will be very useful in future task......

Thank you very much...who r gave a good suggestion...

FRANK>>THANK YOU VERY MUCH>>>
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Sun Mar 11, 2007 10:14 pm    Post subject:
Reply with quote

All of the DFSORT and ICETOOL statements and parameters are discussed in detail in "z/OS DFSORT Application Programming Guide" which you can access online along with all of the other DFSORT books from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online from the link given above.

Quote:
First four byte what it will do?

What is RDW?


RDW is "record descriptor word". The first two bytes of the four-byte RDW contains the length of the record in binary. You should really read up on this stuff.
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai

PostPosted: Mon Mar 26, 2007 10:11 am    Post subject:
Reply with quote

i got it........
Using VTOF,i completed that JCL
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JES2 job size field matching Windows ... SRICOBSAS All Other Mainframe Topics 4 Tue Sep 05, 2017 5:49 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Split file based on comparsion to 2 f... Div Grad DFSORT/ICETOOL 3 Thu Jul 27, 2017 9:16 pm
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us