IBM Mainframe Forum

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?

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.

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:

My Updated JCL



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

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.

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.

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

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?

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.

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.