MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Using SPLICE to merge records.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Senthil Prabhu
Beginner


Joined: 09 Aug 2006
Posts: 4
Topics: 4
Location: India
PostPosted: Fri Mar 16, 2007 7:27 am    Post subject: Using SPLICE to merge records. Reply with quote
Hi all,

Please let me know how to use SPLICE in ICETOOL to merge 4 file and create a single file.

Example:

File1 :-
It is a 50 byte fixed block file. The contents are like below and it has 10 records,
Code:
ACTIVE BASIC                     
   PVRC  ,  EMP  ,  DEP  , Total ,
0001_0001,      2,      2,      4,
0003_0003,     81,     96,    177,
0005_0005,       ,       ,       ,
0007_0007,     83,    101,    184,
0009_0009,     42,     42,     84,
0011_0011,     37,     25,     62,
0013_0013,       ,       ,       ,
  Total  ,  41694,  44376,  86070,


File2 :-
It is a 50 byte fixed block file. The contents are like below and it has 13 records,
Code:
ACTIVE ENHANCED                   
   PVRC  ,  EMP  ,  DEP  , Total ,
0002_0002,       ,       ,       ,
0004_0004,      8,     10,     18,
0006_0006,       ,       ,       ,
0008_0008,      5,     12,     17,
0010_0010,     11,     20,     31,
0012_0012,      5,      6,     11,
0014_0014,       ,       ,       ,
0016_0016,       ,       ,       ,
0018_0018,       ,       ,       ,
0020_0020,     19,     26,     45,
  Total  ,   9752,  14269,  24021,


File3 :-
It is a 50 byte fixed block file. The contents are like below and it has 5 records,
Code:
JUDGES                           
   PVRC  ,  EMP  ,  DEP  , Total ,
0345_0345,    317,    372,    689,
0354_0354,     19,      3,     22,
  Total  ,    336,    375,    711,


File4 :-
It is a 50 byte fixed block file. The contents are like below and it has 5 records,
Code:
RETIREES                         
   PVRC  ,  EMP  ,  DEP  , Total ,
0347_0347,  26069,  13417,  39486,
0348_0348,   1832,   1221,   3053,
  Total  ,  27901,  14638,  42539,


The Output file should be of 200 byte fixed block file with first record of file1, file2, file3 and file4 all in same line in the output file. Similarly second record of file1, file2, file3 and file4 in second line of output file and so on. With 11th, 12 and 13th record of file2 alone in the output file form the position 51 till 100 and remaining spaces.

Some thing like below. Please copy paste the below contents in textpad and it will appear in a better maner.
Code:

ACTIVE BASIC                         ACTIVE ENHANCED                      JUDGES                               RETIREES                         
   PVRC  ,  EMP  ,  DEP  , Total ,      PVRC  ,  EMP  ,  DEP  , Total ,      PVRC  ,  EMP  ,  DEP  , Total ,      PVRC  ,  EMP  ,  DEP  , Total ,
0001_0001,      2,      2,      4,   0002_0002,       ,       ,       ,   0345_0345,    317,    372,    689,   0347_0347,  26069,  13417,  39486,
0003_0003,     81,     96,    177,   0004_0004,      8,     10,     18,   0354_0354,     19,      3,     22,   0348_0348,   1832,   1221,   3053,
0005_0005,       ,       ,       ,   0006_0006,       ,       ,       ,     Total  ,    336,    375,    711,     Total  ,  27901,  14638,  42539,
0007_0007,     83,    101,    184,   0008_0008,      5,     12,     17,
0009_0009,     42,     42,     84,   0010_0010,     11,     20,     31,
0011_0011,     37,     25,     62,   0012_0012,      5,      6,     11,
0013_0013,       ,       ,       ,   0014_0014,       ,       ,       ,
  Total  ,  41694,  44376,  86070,   0016_0016,       ,       ,       ,
                 0018_0018,       ,       ,       ,
                 0020_0020,     19,     26,     45,
                   Total  ,   9752,  14269,  24021,

Thanks,
P.Senthil Prabhu.
_________________
Senthil Prabhu
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Fri Mar 16, 2007 11:58 am    Post subject: Reply with quote
Senthil Prabhu,

Check this link which explains in detail with the exact same issue

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA10/6.13.2.2?SHELF=&DT=20050222160456&CASE=

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Mar 16, 2007 12:28 pm    Post subject: Reply with quote
Senthil,

This is rather tricky because the files have different numbers of records. But this DFSORT/ICETOOL job will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/50)
//IN2 DD DSN=...  input file2 (FB/50)
//IN3 DD DSN=...  input file3 (FB/50)
//IN4 DD DSN=...  input file4 (FB/50)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CON1 DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
//     DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//T3 DD DSN=&&T3,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/200)
//TOOLIN   DD    *
COPY FROM(IN1) USING(CTL1)
COPY FROM(IN2) USING(CTL2)
COPY FROM(IN3) USING(CTL3)
COPY FROM(IN4) USING(CTL4)
*
* USE FROM(INX) WHERE INX HAS THE MOST RECORDS
COPY FROM(IN2) USING(CTL5)
*
SPLICE FROM(CON1) TO(T3) ON(201,9,ZD) -
  KEEPNODUPS WITH(201,8)
SPLICE FROM(T3) TO(OUT) ON(201,8,ZD) -
  WITHEACH WITH(51,50) WITH(101,50) WITH(151,50) USING(CTL6)
//CTL1CNTL DD *
  INREC OVERLAY=(201:SEQNUM,8,ZD)
  OUTFIL FNAMES=T1,BUILD=(1,50,201:201,8,209:C'1')
//CTL2CNTL DD *
  INREC OVERLAY=(201:SEQNUM,8,ZD)
  OUTFIL FNAMES=T1,BUILD=(51:1,50,201:201,8,209:C'2')
//CTL3CNTL DD *
  INREC OVERLAY=(201:SEQNUM,8,ZD)
  OUTFIL FNAMES=T1,BUILD=(101:1,50,201:201,8,209:C'3')
//CTL4CNTL DD *
  INREC OVERLAY=(201:SEQNUM,8,ZD)
  OUTFIL FNAMES=T1,BUILD=(151:1,50,201:201,8,209:C'4')
//CTL5CNTL DD *
  INREC OVERLAY=(201:SEQNUM,8,ZD)
  OUTFIL FNAMES=T2,
    BUILD=(201:201,8,209:C'1',/,
           201:201,8,209:C'2',/,
           201:201,8,209:C'3',/,
           201:201,8,209:C'4')
//CTL6CNTL DD *
  OUTFIL FNAMES=OUT,BUILD=(1,200)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Mar 16, 2007 12:48 pm    Post subject: Reply with quote
Kolusu,

That version of the trick won't work because the number of records in the files are not equal. Some of the records will be lost. It would happen to work if we added KEEPNODUPS to the SPLICE operator, but that's just a quirk for this particular order of the files. If we reversed the order of file2 and file3, the basic trick wouldn't work, whereas my solution would. The key here is to generate blank records by using the file with the most records to take care of any differences in the number of records (e.g. file1 has 3 records, file2 has 5 records and file3 has 4 records).
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
aruna_space
Beginner


Joined: 13 Mar 2007
Posts: 15
Topics: 5
PostPosted: Fri Mar 23, 2007 6:15 am    Post subject: Need to splice a file of variable format Reply with quote
File 1:
Code:

FieldA FieldB FieldC
100    200    1234
101    201    1234
102    202    1111


File 2:
Code:

FieldA FieldB Field2 Field3 FieldC
100    200     1100  1200  1234
102    202     2100  2200  1111

Output file:
Code:

FieldB FieldC Field2 Field3
200    1234   1100   1200
201    1234
202    1111

where FieldA and FieldB are common to both the files.

Please guide me in doing this
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Mar 23, 2007 10:59 am    Post subject: Reply with quote
I don't see the pattern here. FieldA and FieldB are common in the 100 and 102 records, but you treat them differently for output. Why? You need to explain the "rules" for going from input to output. An example with more variations might also help.

And it would have been better to start a New Topic for this new question.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group