View previous topic :: View next topic
|
Author |
Message |
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
Please, present your results of trying DFSORT, ICETOOL, etc.
RTFM, or search for: 'SORT JOIN operation' |
|
Back to top |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
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 |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015 Posts: 1222 Location: Bamberg, Germany
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
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 |
|
|
murali.andaluri
New User
Joined: 07 Jun 2013 Posts: 23 Location: USA
|
|
|
|
Hi Rohit,
Your coding working fine.
Thanks for your help. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
|