Page 1 of 1

Concatenate Multiple rows to single row based on Key field

PostPosted: Mon Nov 09, 2015 8:36 pm
by Vinu1234
Hi,

I am trying to concatenate data of multiple rows in an i/p file to Single row based on a key field. Please see the below requirement and also the JCL and error i am getting.

Requirement:
My input file layout is like below (FB, RECL=20297)

2015083120150825123456789branch Q01: WHAT IS YOUR NAME? A01: I AM MARK
2015083120150825123456789branch Q02: WHAT IS YOUR AGE? A02: 52
2015083120150825123456789branch Q03: WHAT IS YOUR GENDER? A03: MALE
2015083120150825123456789branch Q04: WHAT IS YOUR ADDRESS? A04: 1-10/4,CA
2015083120150823021345788care Q01: WHAT IS YOUR NAME? A01: MY NAME IS Leo
2015083120150823021345788care Q02: WHAT IS YOUR NATIONALITY? A02: Canada
2015083120150823021345788care Q03: WHAT IS YOUR AGE? A03: 65

Field name and Length
--------------------------
FL Date:8 (Pos:1-8)
PR Date:8 (Pos:9-16)
Key :9 (Pos:17-25)
Channel:15 (Pos: 26-40)
Q Field: 255 (Pos:41-295)
A Field: 261 (pos:296-556)

Requirement: Concatenate the multiple rows to single row based on key field.

Output should be as below.

2015083120150825123456789branch Q01: WHAT IS YOUR NAME? A01: I AM MARK Q02: WHAT IS YOUR AGE? A02: 52 Q03: WHAT IS YOUR GENDER? A03: MALE Q04: WHAT IS YOUR ADDRESS? A04: 1-10/4,CA
2015083120150823021345788care Q01: WHAT IS YOUR NAME? A01: MY NAME IS Leo Q02: WHAT IS YOUR NATIONALITY? A02: Canada Q03: WHAT IS YOUR AGE? A03: 65


Note: Maximum questions will be 12.

My JCL:

//SIBSORT1 JOB 8445235,'TESTCOPY',CLASS=D,MSGCLASS=X,NOTIFY=&SYSUID
//*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*--*-*-*-*--*-*-*-*-*-*-*-*-*-*
//* CONCATENATE MULTIPLE ROWS TO SINGLE ROW
//*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*--*-*-*-*--*-*-*-*-*-*-*-*-*-*
//STEP010 EXEC PGM=ICETOOL
//IN1 DD DSN=ABC.T.DR0A9N99.POC.CF.OUT4,DISP=SHR
//TMP1 DD DSN=&&TEMP1,DISP=(MOD,PASS),
// SPACE=(CYL,(50,50)),UNIT=SYSDA
//TMP2 DD DSN=&&TEMP2,DISP=(MOD,PASS),
// SPACE=(CYL,(50,50)),UNIT=SYSDA
//OUT DD DSN=ABC.T.DR0A9N99.POC.CF.OUT1,
//* DCB=(*.SORTIN),
// DCB=(RECFM=FB,LRECL=20297),
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,SPACE=(CYL,(50,50),RLSE)
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN1) TO(TMP1) USING(CP01)
COPY FROM(TMP1) TO(TMP2) USING(CP02)
SPLICE FROM(TMP2) TO(OUT) ON(1,40,CH) KEEPNODUPS -
WITHEACH WITH(557,516) WITH(1073,516) WITH(1589,516) -
WITH(2105,516) WITH(2621,516) WITH(3137,516) -
WITH(3653,516) WITH(4169,516) WITH(4685,516) -
WITH(5201,516) WITH(5717,516) USING(CP03)
/*
//CP01CNTL DD *
SORT FIELDS=(1,40,CH,A)
OUTREC BUILD=(1,20293,20294:SEQNUM,4,ZD,RESTART=(1,40))
/*
//CP02CNTL DD *
OUTREC IFTHEN=(WHEN=(20294,4,ZD,EQ,2),BUILD=(1,40,557:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,3),BUILD=(1,40,1073:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,4),BUILD=(1,40,1589:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,5),BUILD=(1,40,2105:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,6),BUILD=(1,40,2621:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,7),BUILD=(1,40,3137:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,8),BUILD=(1,40,3653:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,9),BUILD=(1,40,4169:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,10),BUILD=(1,40,4685:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,11),BUILD=(1,40,5201:41,516)),
IFTHEN=(WHEN=(20294,4,ZD,EQ,12),BUILD=(1,40,5717:41,516))
/*
//CP03CNTL DD *
OUTREC BUILD=(1,20297)
/*

Error message from SPOOL:

09.00.54 J0687525 -JOBNAME STEPNAME PROCSTEP RC EXCP CPU SRB CLOCK
09.00.54 J0687525 -SIBSORT1 STEP010 U0016 99 .00 .00 .00
09.00.54 J0687525 IEF404I SIBSORT1 - ENDED - TIME=09.00.54
09.00.54 J0687525 -SIBSORT1 ENDED. NAME-TESTCOPY TOTAL CPU TIME=

I am not able to see proper error message in SPOOL following is the TOOLMSG from SPOOL

SYT000I SYNCTOOL RELEASE 1.7.1 - COPYRIGHT 2008 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT002I "TOOLIN" INTERFACE BEING USED

SORT FROM(IN1) TO(TMP1) USING(CP01)
SYT020I SYNCSORT CALLED WITH IDENTIFIER "0001"
SYT030I OPERATION COMPLETED WITH RETURN CODE 0

COPY FROM(TMP1) TO(TMP2) USING(CP02)
SYT020I SYNCSORT CALLED WITH IDENTIFIER "0002"
SYT030I OPERATION COMPLETED WITH RETURN CODE 0

SPLICE FROM(TMP2) TO(OUT) ON(1,40,CH) KEEPNODUPS -
WITHEACH WITH(557,516) WITH(1073,516) WITH(1589,516) -
WITH(2105,516) WITH(2621,516) WITH(3137,516) -
WITH(3653,516) WITH(4169,516) WITH(4685,516) -
WITH(3653,516) WITH(4169,516) WITH(4685,516) -
WITH(5201,516) WITH(5717,516) USING(CP03)
SYT020I SYNCSORT CALLED WITH IDENTIFIER "0003"

Can someone please help on this?

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Mon Nov 09, 2015 11:59 pm
by BillyBoyo
Is this the last line of output you see?
SYT020I SYNCSORT CALLED WITH IDENTIFIER "0003"


SPLICEing on up to 12 records with such large fields may be the problem.

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 1:25 pm
by Vinu1234
Hi,

I have modified the key as 1 to 40 in the Job and also modified the control statements and ran it. The output for me is showing as below (I can see that multiple records are eliminated in the output as expected but in each row only one Question and one answer are being populated) but i need to populate multiple question and answers in one row for one key field. In other words Q2 and A2, Q3 & A3...etc are not being populated in my output (Showing spaces)

Output File:
2015100220150325000052279Branch         Q01: How likely are you to recommend
2015100220150404000078287Branch         Q01: How likely are you to recommend
2015100220150408000033226Branch         Q01: How likely are you to recommend
2015100220150408000120557Branch         Q01: How likely are you to recommend
2015100220150408000126122Branch         Q01: How likely are you to recommend
2015100220150409000015292Branch         Q01: How likely are you to recommend
2015100220150411000056457Branch         Q01: How likely are you to recommend
2015100220150414000098066Branch         Q01: How likely are you to recommend
2015100220150416000115446Branch         Q01: How likely are you to recommend

My Updated JCL

//STEP010 EXEC PGM=ICETOOL                             
//IN1       DD DSN=ABC.T.DR0A9N99.POC.CF.OUT4,DISP=SHR
//T1        DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),   
//             DISP=(MOD,PASS)                         
//OUT1      DD DSN=ABC.T.DR0A9N99.POC.CF.OUT1,         
//             DISP=(NEW,CATLG,DELETE),                 
//             DCB=(RECFM=FB,LRECL=20299),             
//             UNIT=SYSDA,SPACE=(CYL,(50,50),RLSE)     
//*                                                     
//TOOLMSG   DD SYSOUT=*                                 
//DFSMSG    DD SYSOUT=*                                 
//SYSOUT    DD SYSOUT=*                                 
//TOOLIN    DD *                                       
 SELECT FROM(IN1) TO(T1) ON(1,40,CH) FIRST USING(CTL1) 
 SORT FROM(IN1) TO(T1) USING(CTL2)                     
 SPLICE FROM(T1) TO(OUT1) ON(1,40,CH) KEEPNODUPS -     
  WITHEACH WITH(557,516) WITH(1073,516) WITH(1589,516) -         
  WITH(2105,516) WITH(2621,516) WITH(3137,516) -                 
  WITH(3653,516) WITH(4169,516) WITH(4685,516) -                 
  WITH(5201,516) WITH(5717,516)                                   
/*                                                               
//CTL1CNTL DD *                                                   
  SORT FIELDS=COPY                                               
  OUTFIL FNAMES=T1,BUILD=(1,6233,6234:557,3,20297:X)             
/*                                                               
//CTL2CNTL DD *                                                   
  OPTION EQUALS                                                   
  SORT FIELDS=(1,40,CH,A)                                         
  OUTREC IFOUTLEN=20297,                                         
    IFTHEN=(WHEN=INIT,OVERLAY=(20298:SEQNUM,2,ZD,RESTART=(1,40))),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,1),BUILD=(1,40,41:41,516)),       
    IFTHEN=(WHEN=(20298,2,ZD,EQ,2),BUILD=(1,40,557:41,516)),     
    IFTHEN=(WHEN=(20298,2,ZD,EQ,3),BUILD=(1,40,1073:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,4),BUILD=(1,40,1589:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,5),BUILD=(1,40,2105:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,6),BUILD=(1,40,2621:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,7),BUILD=(1,40,3137:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,8),BUILD=(1,40,3653:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,9),BUILD=(1,40,4169:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,10),BUILD=(1,40,4685:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,11),BUILD=(1,40,5201:41,516)),
    IFTHEN=(WHEN=(20298,2,ZD,EQ,12),BUILD=(1,40,5717:41,516))
/*                                                           


The job is running with max cc =00. Please help.

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 2:35 pm
by BillyBoyo
Have a look at your SELECT and consider what it is doing. It would be clearer to you if it were in a separate step, so do that at least for looking at the output from it.

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 3:05 pm
by NicC
Please use the code tags - html removes multiple spaces so they do not show in your posts. Coding your data (search the forum for this or just click on the 'post reply button') preserves the extra spaces.

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 3:31 pm
by Vinu1234
Hi All,

I got the problem solved and my output is as expected. Corrected code is shown below.
Issue: No sync between splice and CTL2.


SPLICE FROM(T1) TO(OUT1) ON(1,40,CH) WITHEACH -
WITH(41,516) WITH(557,516) WITH(1073,516) -
WITH(1589,516) WITH(2105,516)



//CTL2CNTL DD *
SORT FIELDS=(1,40,CH,A)
OUTREC IFOUTLEN=20297,
IFTHEN=(WHEN=INIT,OVERLAY=(20298:SEQNUM,2,ZD,RESTART=(1,40))),
IFTHEN=(WHEN=(20298,2,ZD,EQ,+1),BUILD=(1,40,41:41,516)),
IFTHEN=(WHEN=(20298,2,ZD,EQ,+2),BUILD=(1,40,557:41,516)),
IFTHEN=(WHEN=(20298,2,ZD,EQ,+3),BUILD=(1,40,1073:41,516)),
IFTHEN=(WHEN=(20298,2,ZD,EQ,+4),BUILD=(1,40,1589:41,516)),
IFTHEN=(WHEN=(20298,2,ZD,EQ,+5),BUILD=(1,40,2105:41,516))
/*

Thanks,
Vinu

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 4:37 pm
by BillyBoyo
OK, thanks for letting us know that the USING was missing. I see that the SELECT was entirely superfluous, as the T1 output gets immediately overwritten by the SORT. You also have a SORT in the CTL2. I'm sure you can cut down on the amount of time the data is sorted. Does it even need to be sorted?

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 7:58 pm
by Vinu1234
No, it does not need more sorting. Removed the SORT in CTL2 and ran the Job and I got the result as expected.
It saves time!

Thanks Billy.

Re: Concatenate Multiple rows to single row based on Key fie

PostPosted: Tue Nov 10, 2015 8:14 pm
by BillyBoyo
No problem. Next question, does it need sorting at all? If the data to be conglomerated is always contiguous, it can be done without SORTing. If you do need the output in a sequence other than the input, you can always SORT your conglomerated records, which contain only 1/12 the number of items, although the records are larger.