Concatenating Records



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Concatenating Records

Postby Vineet » Sun Feb 13, 2011 6:47 am

I have a Sequential File with RECFM=FB, LRECL = 1000. File has Multiple Set of Records.My Requirement is, I want to concatenate all Records where Record starting from Position 1 To 14 Having same value. All Records where Starting 14 Bytes are same, should Apprear Once & Remaning fileds should get Concatenated & Written to Ouput File, If there is a Single Occurence of Record should get written as-is. Attribute of Output File: LRECL = 2000, RECFM = FB. Below is Example:

INPUT FILE:

123|ABC|XYZ123|DBA|DB2|MF
123|ABC|XYZ123|ORA|JOB|KF
999|XYZ|XYZ123|CAT|JOB|AF
888|kBC|ABC123|MAT|MAN|NF
123|ABC|XYZ123|DIG|CAL|BC
888|kBC|ABC123|PAN|MAL|KK

OUTPUT FILE:

123|ABC|XYZ123|DBA|DB2|MF|ORA|JOB|KF|DIG|CAL|BC ----> Pipe(|) After MF,KF.3 Instance Of Record. Here
123|ABC|XYZ123| is Common in all Records.

888|kBC|ABC123|MAT|MAN|NF|PAN|MAL|KK ----> Pipe(|) After NF.2 Instance Of Record. Here
888|kBC|ABC123| is Common in all Records.

999|XYZ|XYZ123|CAT|JOB|AF ----> Single Instance of Record.

Note: While Concatenating Files Pipe Symbol (|) to Be Appended after end of each Record if we have Multiple Instance of Records. If Single Instance Then Record Should be Printed without Pipe Symbol (|).

Thanks
Kind Rgds

Vineet Anand
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Concatenating Records

 

Re: Concatenating Records

Postby skolusu » Mon Feb 14, 2011 10:57 pm

Vineet,

1.What is the maximum number of duplicates you can have per each key?

2.What is the length of the field to be concatenated? (ex :|DBA|DB2|MF, ORA|JOB|KF ....)
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Concatenating Records

Postby Vineet » Wed Feb 16, 2011 7:03 pm

Hi SKOLUSU,

Below is the Reply to UR Queries.
1.What is the maximum number of duplicates you can have per each key?
Maximum Duplicate (14 Bytes, Starting form Position 1 To Position 14) we can have is 3. If we have a Single Instance of
Record i.e. No Duplicate then Record to be printed as It is & remaning should be Spaces. Similarly if we have 2 Instance of
Duplicate then after concatinating Remaning Fields to be Space filled.

2.What is the length of the field to be concatenated? (ex :|DBA|DB2|MF, ORA|JOB|KF ....)
For INput LRECL = 500 & For Output File LRECL = 1500.

I hope I am Clear. If any Clarification Required do let me know.

Thanks
Kind Rgds

Vineet
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Concatenating Records

Postby skolusu » Thu Feb 17, 2011 3:46 am

Vineet,

The following DFSORT JCL will give you the desired results.

                   
//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=Your input FB 500 file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  SORT FIELDS=(1,14,CH,A),EQUALS                                       

  OUTREC IFOUTLEN=1500,IFTHEN=(WHEN=INIT,BUILD=(1,14,1501:X,15,486)),   
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,14),PUSH=(1501:SEQ=1)),               
  IFTHEN=(WHEN=GROUP,BEGIN=(1501,1,ZD,EQ,1),PUSH=(0015:1502,486)),     
  IFTHEN=(WHEN=GROUP,BEGIN=(1501,1,ZD,EQ,2),PUSH=(0501:1502,486)),     
  IFTHEN=(WHEN=(1501,1,ZD,EQ,1),OVERLAY=(0501:1000X)),                 
  IFTHEN=(WHEN=(1501,1,ZD,EQ,2),OVERLAY=(1001:0500X)),                 
  IFTHEN=(WHEN=(1501,1,ZD,EQ,3),OVERLAY=(1001:1502,486))               

  OUTFIL REMOVECC,NODETAIL,SECTIONS=(1,14,TRAILER3=(1,1500))           
//*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post