How to split each record into different records in a ffile



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

How to split each record into different records in a ffile

Postby naveenkumar sudha » Tue Dec 01, 2009 10:59 pm

I have a requirement like the following:
This is the first record in my input file:
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570 --- first line

The above has to be splitted into like the following following:
UPDATE PL1_SUP* --- i.e . after splitting this should come in the first line
WHERE POLICY_ID = 101737893 ---- i.e. t his should come in the first line
AND STATUS CODE = M570 ---------i.e. this should come in the first line


I have around 30,000 records in my input file and each record has to be splitted as above.

Could any help me out.Is this possible any sort utility?

Please help me.


Many thanks,
Naveen S
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to split each record into different records in a ffile

Postby Frank Yaeger » Wed Dec 02, 2009 10:32 pm

Are the fields in the records in fixed positions? Or are they delimited in some way?

Please show more example input records so we could see the pattern.

Also give the RECFM and LRECL of the input file, and the starting position, length and format of each field, or the delimiters (that is, the rules for breaking up the record).
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: How to split each record into different records in a ffile

Postby naveenkumar sudha » Wed Dec 02, 2009 11:18 pm

Many thanks Yaeger.

See below a few input records of the input file starting from position one.
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570
UPDATE PL1_SUP* WHERE POLICY_ID = 101737893 AND STATUS CODE = M570
Dataset characteristics are here follows:

Organization . . . : PS
Record format . . . : FB
Record length . . . : 123
Block size . . . . : 27921
1st extent cylinders: 9
Secondary cylinders : 2


Requirement is each record has to splitted to three records like the following :
(Small correctiin to my previous posting-- sorry)
The above(i.e each record) has to be splitted into like the following following:
UPDATE PL1_SUP* --- i.e . after splitting this should come in the first line
WHERE POLICY_ID = 101737893 ---- i.e. t his should come in the second line
AND STATUS CODE = M570 ---------i.e. this should come in the third line
Should follow second record elements in the input file.

i.e. Rules for break up:
First line in the ouput file should have 1 to 16 of input first record
second line in the ouput file should have 17 to 44 of input first record
Third line in the ouput file should have 45 to 66 of input first record
The same has to be repeated for the remaining input records.

Are the fields in the records in fixed positions? --- Yes they are in fixed positions.
Thanks,
Naveen s
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to split each record into different records in a ffile

Postby Frank Yaeger » Thu Dec 03, 2009 12:29 am

Here's a DFSORT job that will do what you asked for:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/123)
//SORTOUT DD DSN=...  output file (FB/28)
//SYSIN    DD    *
  OPTION COPY
  OUTFIL BUILD=(1,16,/,17,28,/,45,22)
/*


If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss? ... g3T7000080
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: How to split each record into different records in a ffile

Postby naveenkumar sudha » Thu Dec 03, 2009 7:13 pm

Many thanks for that.
I have a record like the following :
UPDATE PL1_SUP* WHERE POLICY_ID EQ 1000 AND DATE_STAMP EQ '20091203' AND TIME_STAMP EQ '121212'

This has to be splitted into the following:

UPDATE PL1_SUP*
WHERE POLICY_ID EQ 1000
AND DATE_STAMP EQ '20091203'
AND TIME_STAMP EQ '121212'


I want this to be done based on the character values not based on the position as said above.

It has to be like this:

1) Read first record and write in the output record first line till it finds string "WHE" in the output record first line
2)Continue reading the first record from "WHE' and write till it finds string "AND" in the output record first line
3)Continue reading the first record from "AND' and write till it finds string "AND" in the output record first line
4)Continue reading the first record from "AND' and write till it finds string " " in the output record first line

Could you please help me.
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to split each record into different records in a ffile

Postby naveenkumar sudha » Thu Dec 03, 2009 7:16 pm

Small correction to the above:
1) Read first record and write in the output record first line till it finds string "WHE" in the output record first line
2)Continue reading the first record from "WHE' and write till it finds string "AND" in the output record Second line
3)Continue reading the first record from "AND' and write till it finds string "AND" in the output record third line
4)Continue reading the first record from "AND' and write till it finds string " " in the output record fourth line
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to split each record into different records in a ffile

Postby Frank Yaeger » Thu Dec 03, 2009 11:07 pm

You can use a DFSORT job like the following for your new requirement:

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file
//SORTOUT DD DSN=...  output file
//SYSIN    DD    *
  OPTION COPY
  OUTFIL PARSE=(%01=(ENDBEFR=C'WHE',FIXLEN=30),
                %02=(SUBPOS=3,ENDBEFR=C'AND',FIXLEN=30),
                %03=(SUBPOS=3,FIXLEN=3),
                %04=(ENDBEFR=C'AND',FIXLEN=30),
                %05=(SUBPOS=3,FIXLEN=30)),
   BUILD=(%01,/,%02,/,%03,%04,/,%05)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: How to split each record into different records in a ffile

Postby naveenkumar sudha » Thu Dec 03, 2009 11:31 pm

Thanks a lot Frank Yaeger .
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to split each record into different records in a ffile

Postby naveenkumar sudha » Mon Dec 07, 2009 12:04 pm

Hi,

If i run with the above given jcl: i am getting the following error.

OPTION COPY
OUTFIL PARSE=(%01=(ENDBEFR=C'WHE',FIXLEN=30),
%02=(SUBPOS=3,ENDBEFR=C'AND',FIXLEN=30),
%03=(SUBPOS=3,FIXLEN=3),
%04=(ENDBEFR=C'AND',FIXLEN=30),
%05=(SUBPOS=3,FIXLEN=30)),
BUILD=(%01,/,%02,/,%03,%04,/,%05)

ICE201I F RECORD TYPE IS F - DATA STARTS IN POSITION 1
ICE222A 0 33 BYTE FIXED RECORD LENGTH IS NOT EQUAL TO 28 BYTE LRECL FOR SORTOUT
ICE751I 0 C5-K90013 C6-K90013 C7-K90000 C8-K42135 E9-K90013 E7-K44563
ICE052I 3 END OF DFSORT

Also, i want the output to be written in sequential dataset of length 80.

Could you please help.
I couldnt find any example with SUBPOS in DFSORT pdf.Please give me any pdf lik which will explain OUTFIL PARSE COMMAND in detail.


Many thanks,
Naveen S
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: How to split each record into different records in a ffile

Postby Frank Yaeger » Tue Dec 08, 2009 2:54 am

Since you didn't say, I assumed your fields could be a maximum of 30 bytes long. If they can only be 28 bytes long and you want the output LRECL to be 80, then change the OUTFIL statement to the following:

  OUTFIL PARSE=(%01=(ENDBEFR=C'WHE',FIXLEN=28),             
                %02=(SUBPOS=3,ENDBEFR=C'AND',FIXLEN=28),   
                %03=(SUBPOS=3,FIXLEN=3),                   
                %04=(ENDBEFR=C'AND',FIXLEN=25),             
                %05=(SUBPOS=3,FIXLEN=28)),                 
   BUILD=(%01,/,%02,/,%03,%04,/,%05,80:X)                       


For complete details of DFSORT's PARSE function, see:

http://www.ibm.com/support/docview.wss? ... g3T7000086
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post