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

Writng matched records into output file as single record


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Wed Jan 27, 2021 7:44 pm
Reply with quote

Hi Team,

I am trying to find out solution for below requirement, but couldn't succeeded.
Can some one help me using sort utilities?
I tried ICETOOL, DFSORT and even googled out but no luck.

Input file1:
111INDIA
222AUSTRALIA
333GERMANY
444USA

Input file2:
111DELHI
222CANBERRA
444WASHINGTON

Input File3:
111GOLD
222DIMOND
333SILVER
444PLATINUM

Output should be:
111 INDIA DELHI GOLD
222 AUSTRALIA CANBERRA DIMOND
333 GERMANY SILVER
444 USA WASHINGTON PLATINUM

Thank You.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Wed Jan 27, 2021 9:53 pm
Reply with quote

Please, present your results of trying DFSORT, ICETOOL, etc.

RTFM, or search for: 'SORT JOIN operation'
Back to top
View user's profile Send private message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Wed Jan 27, 2021 10:16 pm
Reply with quote

Hi Sergeyken,

I tried with join keys, but couldn't find solution for Sort Join with three input files.

Will try with RTFM.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1222
Location: Bamberg, Germany

PostPosted: Wed Jan 27, 2021 10:45 pm
Reply with quote

If you have always three records, try MERGE and IFTHEN=(WHEN=GROUP.. PUSH, that should work. Other than that, do what sergeyken suggests.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jan 28, 2021 12:05 am
Reply with quote

Or You could do this with SPLICE as its very powerful feature of DFSORT, but I prefer what Joreg suggested to GROUP them RECORDS=3 and so on....
Code:
//STEP1 EXEC  PGM=ICETOOL 
//TOOLMSG   DD  SYSOUT=*   
//DFSMSG    DD  SYSOUT=*   
//DSN1 DD *               
111INDIA                   
222AUSTRALIA               
333GERMANY                 
444USA                     
/*                         
//DSN2 DD *               
111DELHI                   
222CANBERRA               
444WASHINGTON             
/*                         
//DSN3 DD *               
111GOLD                   
222DIMOND                 
333SILVER                 
444PLATINUM               
//TMP DD DSN=&&TMP,UNIT=SYSDA,SPACE=(CYL,(1,1)),DISP=(MOD,PASS)       
//MERGE DD SYSOUT=*                                                   
//TOOLIN DD *                                                         
COPY FROM(DSN1) TO(TMP)                                               
COPY FROM(DSN2) TO(TMP) USING(CTL2)                                   
COPY FROM(DSN3) TO(TMP) USING(CTL3)                                   
SPLICE FROM(TMP) TO(MERGE) ON(1,3,CH) KEEPNODUPS WITHANY WITH(21,20) -
  WITH(41,20) USING(CTL4)                                             
//CTL2CNTL DD *                                                       
  INREC BUILD=(1,3,21:4,20)                                           
//CTL3CNTL DD *                                                       
  INREC BUILD=(1,3,41:4,20)                                           
//CTL4CNTL DD *                                                       
  OUTFILE REMOVECC,BUILD=(1,80,SQZ=(SHIFT=LEFT))                     

Output-
Code:
    COMMAND INPUT ===>                                                  SCROLL ===>    CSR 
********************************* TOP OF DATA **********************************
111INDIADELHIGOLD                                                               
222AUSTRALIACANBERRADIMOND                                                     
333GERMANYSILVER                                                               
444USAWASHINGTONPLATINUM                                                       
******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2010
Location: USA

PostPosted: Thu Jan 28, 2021 12:48 am
Reply with quote

Giving any ready-to-use solutions to those who has no habit on thinking a little bit with his own mind would not help to reduce the number of kindergarten-level "questions"...
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jan 28, 2021 7:47 am
Reply with quote

I don't disagree but I had time so tried. There is a small change that is needed to get a byte space between each word, that I will leave it for the TS to work on it. Btw everyone learn with their own capabilities and time , no rush.
Back to top
View user's profile Send private message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Thu Jan 28, 2021 11:08 am
Reply with quote

Hi Sergeyken,

Please don't think we are posting without putting any efforts from our side.

Joinkeys are having limitation of two inputs.
I also tried ICETOOL, but did not get required output, may be somewhere I did wrong.

My requirement is not as simple as I posted, I posted this example because using this example I can achieve it.

We have approximately 20 db2 unload files. we are combining all these unload files on a key and create singe record and preparing single unload file.
Once file is ready, planning to load same file into new master table.

Anyways, Thank you so much for all responses.
Will try all and choose the best one.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jan 28, 2021 11:19 am
Reply with quote

The one I provided should work , Good Luck !

Btw you could write one big select with all 20 tables and do concate in select , easy to maintain as well , why use DFSORT. You can think about it.
Back to top
View user's profile Send private message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Thu Jan 28, 2021 12:21 pm
Reply with quote

Hi Rohit,

I am not using select because i am formatting data before loading into new table.

BTW, i tried your solution but i am not getting desired output. I am not sure if i am doing something wrong. Below is my code and output.

Code:
//S001     EXEC PGM=ICETOOL   
//MERGE    DD SYSOUT=*         
//TOOLMSG  DD SYSOUT=*         
//DFSMSG   DD SYSOUT=*         
//IN1       DD *               
111INDIA                       
222AUSTRALIA                   
333GERMANY                     
444USA                         
/*                             
//IN2       DD *               
111DELHI                       
222CANBERRA                   
444WASHINGTON                 
/*                             
//IN3       DD *                                                     
111GOLD                                                               
222DIMOND                                                             
333SILVER                                                             
444PLATINUM                                                           
//T1          DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT         DD SYSOUT=*                                             
//TOOLIN DD *                                                         
COPY FROM(IN1) TO(T1)                                                 
COPY FROM(IN2) TO(T1) USING(CTL2)                                     
COPY FROM(IN3) TO(T1) USING(CTL3)                                     
SPLICE FROM(T1) TO(OUT) ON(1,3,CH) KEEPNODUPS WITHANY -               
  WITH(21,20) WITH(41,20) USING(CTL4)                                 
//CTL2CNTL DD *                                                       
  INREC BUILD=(1,3,21:4,20)                                           
//CTL3CNTL DD *                                                       
  INREC BUILD=(1,3,41:4,20)                                           
//CTL4CNTL DD *                                 
  OUTFILE REMOVECC,BUILD=(1,80,SQZ=(SHIFT=LEFT))

Output:

********************************* TOP OF DATA ***************
111INDIA                                                     
222AUSTRALIA                                                 
333GERMANY                                                   
444USA                                                       
******************************** BOTTOM OF DATA *************

Coded
Back to top
View user's profile Send private message
murali.andaluri

New User


Joined: 07 Jun 2013
Posts: 23
Location: USA

PostPosted: Thu Jan 28, 2021 7:01 pm
Reply with quote

Hi Rohit,

Your coding working fine.

Thanks for your help.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Jan 28, 2021 8:13 pm
Reply with quote

Welcome Murali!
Please use code tags when you represents the code. It will help for all of us to understand and see it better.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
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 Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
Search our Forums:

Back to Top