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

Combiningt more than one record to one
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Mon Sep 22, 2008 1:54 pm
 Reply with quote
Hi all,

I just completed one task with u guys help.

I just faced once more prob while processing my tsak.

That is i am having a file of LRECL = 400 where field from 1 to 99 and 122 to 278 are same. The rest 20 bits are having start date and end date.

I need a sort card such that it will combine the duplicate rows into one row with the earliest start date and the latest end date. For ex.:--

input

(1-99);01/01/2006;01/07/2007;(122- 278)
(1-99);01/09/2007;02/05/2008;(122- 278)
(1-99);02/15/2008;09/05/2008;(122- 278)


OUTPUT

(1-99);01/01/2006;09/05/2008;(122- 278)

I suppose it can be done by using include statements.

Could u guys please tell hw to start with this typr of requiremnt.


Thanks in advance.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Mon Sep 22, 2008 2:19 pm
 Reply with quote
Hi,

What does the key field start? Is it from 1-99 and 122-178?

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

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Mon Sep 22, 2008 2:47 pm
 Reply with quote
Hi arun,

The Start Date field starts with position 100 to 109 and the END DATE from 111 to 120.

Except these things all the other stuffs are same for the duplicate records i.e from position 1 - 99 and from 122 - 400.

Please let me know wether i was clear to u or not ??
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Mon Sep 22, 2008 4:12 pm
 Reply with quote
Hi all,

Please find below my input file,

Code:


----7----+----8----+----9----+----0----+----1----+----2----+----3----+--
<---VALUE1                     -->07/21/2008;07/25/2008<--- value2--
<---VALUE1                     -->07/28/2008;08/01/2008<--- value2--
<---VALUE3                      ->08/05/2008;08/08/2008<--- value4--
<---VALUE3                      ->08/11/2008;08/13/2008<--- value4--



My Output should be

Code:


----7----+----8----+----9----+----0----+----1----+----2----+----3----+--
<---VALUE1                     -->07/21/2008;08/01/2008<--- value2--
<---VALUE3                      ->08/05/2008;08/13/2008<--- value4--



Could you guys please help me to do this.

I suppose u can easily get my requirement.

Thanks in advance.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Mon Sep 22, 2008 6:28 pm
 Reply with quote
Well,

Sort the records based on START-DATE and then on END-DATE along with key. (Remember to keep these two fields towards end of the sort key)

Then use a splice on original key (excluding these two fields) to override END-DATE field.
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Mon Sep 22, 2008 6:40 pm
 Reply with quote
Hi,

Thanks for your reply. I have sorted that as per ascending start date and the descending end date.
But i dont know how to use that SPLICE.

Please chk my sort card below,
Code:

//SYSIN DD     *                                                     
          SORT FIELDS=(1,99,CH,A,100,10,CH,D,111,10,CH,D,122,278,CH,A)
                                                                     



Could you please give me some splice example to proceed with the next step to get my output.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Mon Sep 22, 2008 6:54 pm
 Reply with quote
If I understand you correctly this is how your sort should look like.

Code:


       SORT FIELDS=(1,99,CH,A,       
                    122,156,CH,A,     
                    106,04,CH,A,     
                    103,02,CH,A,     
                    100,02,CH,A,     
                    110,02,CH,A,     
                    113,02,CH,A,     
                    116,02,CH,A)     


can you check and let me know, if it works fine with the data. if works fine
can you post me a sample of input and output data, so that i can give you a splice card accordingly.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Mon Sep 22, 2008 7:00 pm
 Reply with quote
Sorry,

It should be

Code:


SORT FIELDS=(1,99,CH,A,       
                    122,156,CH,A,     
                    106,04,CH,A,     
                    103,02,CH,A,     
                    100,02,CH,A,     
                    117,02,CH,A,     
                    114,02,CH,A,     
                    111,02,CH,A) 


Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Mon Sep 22, 2008 7:26 pm
 Reply with quote
Well i have tried but there is some mismatch in the fields,

So i just did as per u told,

The new SORT card looks like,

Code:


SORT FIELDS=(1,99,CH,A,       
                    106,04,CH,A,     
                    103,02,CH,A,     
                    100,02,CH,A,     
                    117,04,CH,A,     
                    114,02,CH,A,     
                    111,02,CH,A,
                    122,278,CH,A)



It worked fine now.
The input is as below,

Code:

--9----+----0----+----1----+----2----+----3----+----4---
WMAC;Y;GWHR;07/21/2008;07/25/2008;Graduated reduced hour
WMAC;Y;GWHR;07/28/2008;08/01/2008;Graduated reduced hour
WOAC;Y;GWHR;08/05/2008;08/08/2008;gradaul rtw schedule 
WOAC;Y;GWHR;08/11/2008;08/13/2008;gradaul rtw schedule 
WMAC;Y;GWHR;06/11/2008;06/17/2008;reduced schedule     
WMAC;Y;GWHR;06/18/2008;06/25/2008;reduced schedule     


ans the output is as below,


Code:
 
WMAC;Y;GWHR;07/21/2008;07/25/2008;Graduated reduced hour
WMAC;Y;GWHR;07/28/2008;08/01/2008;Graduated reduced hour
WOAC;Y;GWHR;08/05/2008;08/08/2008;gradaul rtw schedule 
WOAC;Y;GWHR;08/11/2008;08/13/2008;gradaul rtw schedule 
WMAC;Y;GWHR;06/11/2008;06/17/2008;reduced schedule     
WMAC;Y;GWHR;06/18/2008;06/25/2008;reduced schedule     


I need the output to be
Code:


WMAC;Y;GWHR;07/21/2008;08/01/2008;Graduated reduced hour
WOAC;Y;GWHR;08/05/2008;08/08/2008;gradaul rtw schedule 
WMAC;Y;GWHR;06/11/2008;06/25/2008;reduced schedule     



Please help me regarding this.
Back to top
View user's profile Send private message
hchinnam

New User


Joined: 18 Oct 2006
Posts: 73
PostPosted: Mon Sep 22, 2008 7:34 pm
 Reply with quote
OK,

Full step for your req should look like this.

Code:


//S0001    EXEC PGM=SYNCTOOL                                           
//DFSMSG   DD SYSOUT=*                                               
//SYSPRINT DD SYSOUT=*                                               
//SYSOUT   DD SYSOUT=*                                               
//TOOLMSG  DD SYSOUT=*                                               
//STATA    DD DSN=&&TEMP1,                                           
//            SPACE=(CYL,(10,10)),                                   
//            DISP=(NEW,PASS),DCB=(LRECL=278,BLKSIZE=2780)           
//DDIN     DD DISP=SHR,DSN=In.File                         
//DDOUT    DD SYSOUT=*                                               
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                       
  COPY FROM(DDIN) TO(STATA) USING(CTL1)                               
  SPLICE FROM(STATA) TO(OUT) ON(1,99,CH)            -                 
     ON(122,156,CH) WITH(111,10) KEEPNODUPS                           
//CTL1CNTL DD *                                                       
       SORT FIELDS=(1,99,CH,A,                                       
                    122,156,CH,A,                                     
                    106,04,ZD,A,                                     
                    100,02,ZD,A,                                     
                    103,02,ZD,A,                                     
                    117,02,ZD,A,                                     
                    111,02,ZD,A,                                     
                    114,02,ZD,A)                                     



It worked for me with some small test data. so try it and let me know if you see as issue
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Mon Sep 22, 2008 7:48 pm
 Reply with quote
Hi,

Thank you very very much.

The code is working fine.

Really its gr8 to work with you.


Thanks again.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Mon Sep 22, 2008 8:52 pm
 Reply with quote
Hello,

I just tried with the above code and the below sample input and dint get what was expected.
Input
Code:
---+----9----+----0----+----1----+----2----+----3---
<---VALUE1     -->07/21/2008;07/25/2008<--- value2--
<---VALUE1     -->07/28/2008;08/01/2008<--- value2--
<---VALUE3     -->07/21/2008;07/25/2008<--- value3--
<---VALUE3     -->07/28/2008;08/01/2008<--- value3--
<---VALUE3     -->07/28/2008;08/05/2008<--- value3--
<---VALUE3     -->07/29/2008;08/04/2008<--- value3--
Actual Output
Code:
---+----9----+----0----+----1----+----2----+----3---
<---VALUE1     -->07/21/2008;08/01/2008<--- value2--
<---VALUE3     -->07/21/2008;08/04/2008<--- value3--

Expected Output
Code:
---+----9----+----0----+----1----+----2----+----3---
<---VALUE1     -->07/21/2008;08/01/2008<--- value2--
<---VALUE3     -->07/21/2008;08/05/2008<--- value3--

I m not sure whether you come across such input. Anyway Here's a SYNCTOOL job which should work for the above input as well.
Code:
//STEP1    EXEC PGM=SYNCTOOL                                   
//DFSMSG   DD SYSOUT=*                                         
//TOOLMSG  DD SYSOUT=*                                         
//IN       DD DSN=Input.file  ---------- FB , LRECL=400         
//OUT      DD DSN=Output.file ---------- FB , LRECL=400         
//T1       DD DSN=&&T1,DISP=(MOD,PASS)                         
//TOOLIN   DD *                                                 
 SORT FROM(IN) TO(T1) USING(CTL1)                               
 SORT FROM(IN) TO(T1) USING(CTL2)                               
 SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(201,200,CH) WITH(111,10)
//CTL1CNTL DD *                                                 
   INREC OVERLAY=(401:106,4,100,2,103,2)                       
   SORT FIELDS=(1,99,CH,D,201,200,CH,D,401,8,CH,D)             
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                     
   SECTIONS=(1,99,201,200,TRAILER3=(1,200,201,200))             
//CTL2CNTL DD *                                                 
   INREC OVERLAY=(401:117,4,111,2,114,2)                       
   SORT FIELDS=(1,99,CH,D,201,200,CH,D,401,8,CH,A)             
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                     
   SECTIONS=(1,99,201,200,TRAILER3=(1,200,201,200))       

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

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Mon Sep 22, 2008 9:28 pm
 Reply with quote
Hi,

Sorry for the any inconvenience,
Please ignore the jcl in my previous post and read it as follows. Got some file positions wrong icon_mad.gif .
Code:
//TOOLIN   DD *                                                 
 SORT FROM(IN) TO(T1) USING(CTL1)                               
 SORT FROM(IN) TO(T1) USING(CTL2)                               
 SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(121,280,CH) WITH(111,10)
//CTL1CNTL DD *                                                 
   INREC OVERLAY=(401:106,4,100,2,103,2)                       
   SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,D)             
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                     
   SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))     
//CTL2CNTL DD *                                                 
   INREC OVERLAY=(401:117,4,111,2,114,2)                       
   SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,A)             
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                     
   SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200)) 

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

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Tue Sep 23, 2008 10:08 am
 Reply with quote
Hi arun,

Thanks for your concern regarding this.


It was really helpful.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Tue Sep 23, 2008 12:49 pm
 Reply with quote
Siva,
Quote:
Thanks for your concern regarding this

You're welcome. icon_smile.gif

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

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Fri Sep 26, 2008 10:56 am
 Reply with quote
Hi all,

Thanks for all your replys.
But for this combinig records only i faced two more situation where i am unable to proceed.

Please help me regarding this.

For the above example i need to do the following if the condition came like below,
e.g: -


Code:
Start Date        End Date
                           01/11/2008
08/28/2008           10/09/2008


The above Sort gives the output like
Code:
Start Date       End Date
                      10/09/2008


but i need it to be

Code:
Start Date       End Date
08/28/2008           10/09/2008


Same for the End date too.

can anyone please help me regarding this ?
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Fri Sep 26, 2008 11:42 am
 Reply with quote
Siva,

Modify the CTL1/CTL2 cards in my last post as below.
Code:
//CTL1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'9')),                     
    IFTHEN=(WHEN=(100,10,CH,NE,C' '),OVERLAY=(401:106,4,100,2,103,2))
  SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,D)                   
  OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                           
  SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))           
//CTL2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'0')),                     
    IFTHEN=(WHEN=(111,10,CH,NE,C' '),OVERLAY=(401:117,4,111,2,114,2))
  SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,A)                   
  OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                           
  SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))           
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Fri Sep 26, 2008 11:52 am
 Reply with quote
Hi arun,

Thanks for your reply.

Actully my input file length everything has been changed and this time the LRECL comes 700. so i substitute the values with the correct ones. like below

Code:

//TOOLIN   DD *                                                     
     SORT FROM(IN) TO(T1) USING(CTL1)                               
     SORT FROM(IN) TO(T1) USING(CTL2)                               
     SPLICE FROM(T1) TO(OUT) ON(1,421,CH) ON(444,256,CH) WITH(433,10)
//CTL1CNTL DD *                                                     
   INREC OVERLAY=(428,4,422,2,425,2)                                 
   SORT FIELDS=(1,421,CH,D,444,256,CH,D)                             
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,700),                           
//CTL2CNTL DD *                                                     
   INREC OVERLAY=(439,4,433,2,436,2)                                 
   SORT FIELDS=(1,421,CH,D,444,256,CH,D)                             
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,700) 
                         


But i am getting nothing in the output file.

could you please tell me how u r defining the section part and if the LRECL of the input file is 400 and u r using 401:, Is not it abending ????
In my case it got abend ...

Please help me ???
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Fri Sep 26, 2008 12:09 pm
 Reply with quote
Hi arun,

I have runned your Sort card with the previous recordlength i. 400.

I am getting null output means no value in the output.

Could you please check it out once and let me know.

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

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Fri Sep 26, 2008 12:19 pm
 Reply with quote
Quote:
Could you please check it out once and let me know.


Siva, what about YOU checking it out and posting what you have tried and the resulting errors. I am sure that Arun is not getting paid to spend so much time to resolve your problems.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Fri Sep 26, 2008 12:22 pm
 Reply with quote
Quote:
Actully my input file length everything has been changed and this time the LRECL comes 700

The jcl which I posted works fine for me with an input LRECL of 400 and the date positions provided by you. The same jcl might not work for all the input LRECLs. If it is not giving the results, you need to post the jcl you used.
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Fri Sep 26, 2008 12:24 pm
 Reply with quote
Hi expat,

Please dont go on my words.

I mean to say like i was having the problem in preparation of that Sort card. and Arun was really helping me from starting onwards.

He gave me a lot of solutions...I just checked wioth the code and i got null output. I tried my level the best to get the reason but unable to find it out. So requested Arun to please check wts the prob ??

Is thr any problem in asking a soln.....

Moreover SORRY if that hurts u alot .
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
PostPosted: Fri Sep 26, 2008 12:44 pm
 Reply with quote
Siva,

Have replied via PM icon_lol.gif
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai
PostPosted: Fri Sep 26, 2008 12:58 pm
 Reply with quote
Hi Arun,

Thanks for replying. There was some misunderstanding between me and expat.

please find the JCl below,
Code:

//STEP     EXEC PGM=SYNCTOOL                                         
//DFSMSG   DD SYSOUT=*                                               
//TOOLMSG  DD SYSOUT=*                                               
//IN       DD DSN=Input File    - length = 400
//OUT      DD DSN=OUTput File    - length = 400
//T1       DD DSN=&&T1,DISP=(MOD,PASS)                               
//TOOLIN   DD *                                                       
  SORT FROM(IN) TO(T1) USING(CTL1)                                   
   SORT FROM(IN) TO(T1) USING(CTL2)                                   
   SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(201,200,CH) WITH(111,10)   
//CTL1CNTL DD *                                                       
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'9')),                       
    IFTHEN=(WHEN=(100,10,CH,NE,C' '),OVERLAY=(401:106,4,100,2,103,2))
  SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,D)                     
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                             
   SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))             
 //CTL2CNTL DD *                                                       
   INREC IFTHEN=(WHEN=INIT,OVERLAY=(401:8C'0')),                       
     IFTHEN=(WHEN=(111,10,CH,NE,C' '),OVERLAY=(401:117,4,111,2,114,2))
   SORT FIELDS=(1,99,CH,D,121,280,CH,D,401,8,CH,A)                     
   OUTFIL REMOVECC,NODETAIL,BUILD=(1,400),                             
   SECTIONS=(1,99,121,80,201,200,TRAILER3=(1,200,201,200))             
 /*                                         
                         



The error i m getting is
Code:

IN       : RECFM=FB   ; LRECL=   400; BLKSIZE= 27600
INREC RECORD LENGTH =   408                         
POTENTIALLY INEFFICIENT USE OF INREC                 


Bcos here we have been using OVERLAY=(401:8C'9')).

Could you please suggest any other trick to get the output.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
PostPosted: Fri Sep 26, 2008 1:34 pm
 Reply with quote
Siva,

The error you are getting is not a critical error and is NOT the reason for getting an empty output as you think.

You have coded the SPLICE statement as
Quote:
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(201,200,CH) WITH(111,10)
instead of
Quote:
SPLICE FROM(T1) TO(OUT) ON(1,99,CH) ON(121,280,CH) WITH(111,10)
Correct this and rerun your job
Back to top
View user's profile Send private message
View previous topic : : View next topic  
Post new topic   Reply to topic All times are GMT + 6 Hours
Forum Index -> JCL & VSAM Goto page 1, 2  Next

 

Similar Topics
Topic Forum Replies
No new posts To Populate Trailer cnt in the file w... SYNCSORT 1
No new posts Write record twice based on condition... SYNCSORT 7
No new posts Create a specific record/file based o... SYNCSORT 8
No new posts Sort to insert record in an empty file. DFSORT/ICETOOL 5
No new posts Create a FTP record with help of SYNC... SYNCSORT 3
Search our Forums:


Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us