Rearrage the Feilds - DF SORT
Moderators: Frank Yaeger, Moderator Group
Rearrage the Feilds - DF SORT
Hi
I want to rearrange only few feilds positions in a file.
EG: File A
NameDept Salary DOB City Pin country Class state
Raul S&F 7000 19/03/1984 HYD 500083 Spain a AP
Raul S&F 7000 19/03/1984 HYD 500083 Spain a TN
Raul S&F 7000 19/03/1984 HYD 500083 Spain a KN
I want to City to come after salary and State before Country. And I want rest of the file as same.
Please help me out.
Raul
I want to rearrange only few feilds positions in a file.
EG: File A
NameDept Salary DOB City Pin country Class state
Raul S&F 7000 19/03/1984 HYD 500083 Spain a AP
Raul S&F 7000 19/03/1984 HYD 500083 Spain a TN
Raul S&F 7000 19/03/1984 HYD 500083 Spain a KN
I want to City to come after salary and State before Country. And I want rest of the file as same.
Please help me out.
Raul
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
Dear Anuj,
Please find the midified data.
Name Dept Salary DOB City Pin Country Class state
Raul S&F 7000 19/03/1984 HYD 500083 Spain a AP
Raul S&F 7000 19/03/1984 HYD 500083 Spain a TN
Raul S&F 7000 19/03/1984 HYD 500083 Spain a KN
I want to City to come after salary and State before Country. And I want rest of the file as same.
Thanks,
Raul
Please find the midified data.
Name Dept Salary DOB City Pin Country Class state
Raul S&F 7000 19/03/1984 HYD 500083 Spain a AP
Raul S&F 7000 19/03/1984 HYD 500083 Spain a TN
Raul S&F 7000 19/03/1984 HYD 500083 Spain a KN
I want to City to come after salary and State before Country. And I want rest of the file as same.
Thanks,
Raul
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Raul,
Please show the expected output for your example input records.
Give the RECFM and LRECL of the input file.
Give the starting position, length and format of each field in the input records and in the output records.
Please show the expected output for your example input records.
Give the RECFM and LRECL of the input file.
Give the starting position, length and format of each field in the input records and in the output records.
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Input File
INSTRING1 INSTRING2 INSTRING3 INNUM1 INNUM2 INNUM3 INNUM4
5/AN 10/AN 5/AN 5/NUM 5/NUM 3/PS 5/PS
(1-5) (6-15) (16-20) (21-25) (26-30) (31-33) (34-38)
***************************** TOP OF DATA ******************
ABCDE QRSTUVWXYZ !@#$% 123.45 12345 123.45 123.45678
FGHIH QWERTY1234 ASDFG 568.79 56789 567.89 567.89012
I want Output file in the following order:
Instring1 , Insttring2, Innum1, Innum3, Innum2, Innum4, Instring3
I know we can write this using Build (1,5, x, 6,10, x, 21,5, x, 31,3, x, 26,5, x, 34,5, x, 16,5).
We have only 7 fields here in this case, but what if I have 100 fields, and need to change only 3 or four fields? How can I rearrange those 3 or 4 fields and copy the rest of the file as it is?
Please help me out...[/code][img][/img]
INSTRING1 INSTRING2 INSTRING3 INNUM1 INNUM2 INNUM3 INNUM4
5/AN 10/AN 5/AN 5/NUM 5/NUM 3/PS 5/PS
(1-5) (6-15) (16-20) (21-25) (26-30) (31-33) (34-38)
***************************** TOP OF DATA ******************
ABCDE QRSTUVWXYZ !@#$% 123.45 12345 123.45 123.45678
FGHIH QWERTY1234 ASDFG 568.79 56789 567.89 567.89012
I want Output file in the following order:
Instring1 , Insttring2, Innum1, Innum3, Innum2, Innum4, Instring3
I know we can write this using Build (1,5, x, 6,10, x, 21,5, x, 31,3, x, 26,5, x, 34,5, x, 16,5).
We have only 7 fields here in this case, but what if I have 100 fields, and need to change only 3 or four fields? How can I rearrange those 3 or 4 fields and copy the rest of the file as it is?
Please help me out...[/code][img][/img]
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
I do not have edit-autority in this Forum, so I post your data using BBcode tags below:is this correct? If yes, you are close in your "build" statement, however, as instring1 and 2 are contigious fields - you could combine them. If I get all the thigns correctly, try this:
Also if you have 100-fields and you want to shuffle all of them with new positions - well, you've to tell the SORT what are the new positions, no escape!
However, if you just want to shuffle first 10-bytes only out of 100 bytes - you could use:If this is not what you are asking -- please explain again.
Code: Select all
***************************** TOP OF DATA ******************
ABCDE QRSTUVWXYZ !@#$% 123.45 12345 123.45 123.45678
FGHIH QWERTY1234 ASDFG 568.79 56789 567.89 567.89012
Code: Select all
//STEP001 EXEC PGM=SORT
//SORTIN DD *
ABCDE QRSTUVWXYZ !@#$% 123.45 12345 123.45 123.45678
FGHIH QWERTY1234 ASDFG 568.79 56789 567.89 567.89012
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC FIELDS=(1,15,16:21,5,21:31,3,24:26,5,29:34,5,34:16,5)
//SYSOUT DD SYSOUT=*
//*
However, if you just want to shuffle first 10-bytes only out of 100 bytes - you could use:
Code: Select all
INREC FIELDS=(1:5,5,6:1,5,11:11,90)
Regards,
Anuj
Anuj
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Your description of your fields is very confusing For example, you show 5/NUM for 21-25, but your example shows 123.45 which is 6 characters, not 5.We have only 7 fields here in this case, but what if I have 100
fields, and need to change only 3 or four fields? How can I
rearrange those 3 or 4 fields and copy the rest of the file as it
is?
If the fields were all the same length, you could use OVERLAY to just rearrange the needed fields. But since your fields are different lengths, you must use BUILD.
If you just want to move different length fields around without editing them, the best way to do that would be to set up a symbol for each field and then code the symbols in the order you need in INREC. For example, you could set up your symbols like this:
Code: Select all
//SYMNAMES DD *
//SYMNAMES DD *
INSTRING1,1,5
INSTRING2,*,10
INSTRING3,*,5
INNUM1,*,5
INNUM2,*,5
INNUM3,*,3
INNUM4,*,5
Code: Select all
INREC BUILD=(INSTRING1,INSTRING2,
INNUM1,INNUM3,INNUM2,INNUM4,INSTRING3)
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
You're welcome.
This INREC control statement specifies three data fields from a 90-byte record:
(1:5,5 -> The first field begins in byte 5 of the input record and is 5 bytes long.
Same explanation stands for other fields too.
To use them on this board, once you are done with writing your statements, hit the "preview" button -- under "Message Body" you'll see the button "Code" -- select your text, you want to wrap in "code tags" and click the "code button", you'll get it the way I had.
You know, you've Frank on this thread - master of masters, when it comes to DFSort, I'll try to answer however:raulravi wrote:1) Can you explain this INREC FIELDS=(1:5,5,6:1,5,11:11,90)?
This INREC control statement specifies three data fields from a 90-byte record:
(1:5,5 -> The first field begins in byte 5 of the input record and is 5 bytes long.
Same explanation stands for other fields too.
I did not use any "image" - I used BBcode tags.2) How to insert image in the message? As you did Mainframe code...
To use them on this board, once you are done with writing your statements, hit the "preview" button -- under "Message Body" you'll see the button "Code" -- select your text, you want to wrap in "code tags" and click the "code button", you'll get it the way I had.
Regards,
Anuj
Anuj
Hello Frank,
My Requirement:
I have three DB2 Table Unload Files with different record length. The Files are real big, We might have 20, 30 million records.
I have to pick 50 columns from file1, 30 from the 2nd and 20 from the third one, and write all these in to a new Output file in respective positions.
The Record Length of the Output file is 1900.
Can we do this using DFsort? If so can you please help me out, on how to do this effectively?
For Eg:
Thanks,
Raul
My Requirement:
I have three DB2 Table Unload Files with different record length. The Files are real big, We might have 20, 30 million records.
I have to pick 50 columns from file1, 30 from the 2nd and 20 from the third one, and write all these in to a new Output file in respective positions.
The Record Length of the Output file is 1900.
Can we do this using DFsort? If so can you please help me out, on how to do this effectively?
For Eg:
Code: Select all
File1:
Custno custname Dept Role
1 Raul HR MG
2 Frank IT DEV
Code: Select all
File2:
Custno Address state Country
1 Arizona AR USA
2 Atlanta AL USA
Code: Select all
File3:
Custno EmpType Dateofjoining DateofBirth SALARY
1 Pertl DD/MM/YYYY DD/MM/YYYY $15000
2 Temp DD/MM/YYYY DD/MM/YYYY $15000
Code: Select all
Output File: I want this in the below order
Custno custname Address SALARY Dept Role EmpType DOJ DOBDept state Country
Raul
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Personally, I would use two JOINKEYS passes to do this. First, I'd join the needed fields from File1 and File2 to create records in a temporary file. The REFORMAT would have the F1 and F2 fields you need. Then I'd join the needed fields from temporary file and File3 to create records with all of the fields. The REFORMAT would have all of the fields from the 3 files you need. Finally I'd use OUTFIL to rearrange all the fields the the way you need them.
Since your new requirement is completely different from your original requirement, and you are talking about 50 fields from File1, 30 from File2 and 20 from File3, but you haven't given any real information on the starting position, length and format of these fields, I'd rather not spend time giving you a solution based on your current example. You would undoubtedly come back and ask me how to adapt that to your real situation.
I'd suggest you take a look at the doc on JOINKEYS at:
http://www.ibm.com/support/docview.wss? ... g3T7000174
and try to work it out yourself. You might need to experiment some, but you'd learn more.
Since your new requirement is completely different from your original requirement, and you are talking about 50 fields from File1, 30 from File2 and 20 from File3, but you haven't given any real information on the starting position, length and format of these fields, I'd rather not spend time giving you a solution based on your current example. You would undoubtedly come back and ask me how to adapt that to your real situation.
I'd suggest you take a look at the doc on JOINKEYS at:
http://www.ibm.com/support/docview.wss? ... g3T7000174
and try to work it out yourself. You might need to experiment some, but you'd learn more.
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Hi Frank,
I trie dto run the below job & i got some syntax error,
Error:
Raul
I trie dto run the below job & i got some syntax error,
Code: Select all
//TTYASAS3 JOB ,CLASS=3,MSGCLASS=0,NOTIFY=&SYSUID
//* RUN THROUGH JCLPREP ON 21/04/2011 AT 08:58:17 BY GONUGRK FOR QBOS
//*
//JKE1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=TTYA.GONUGRK.JOIN1,DISP=SHR
//SORTJNF2 DD DSN=TTYA.GONUGRK.JOIN2,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION
JOINKEYS FILE=F1,FIELDS=(1,8,A,9,3,A)
JOINKEYS FILE=F2,FIELDS=(17,8,A,1,8,A)
REFORMAT FIELDS=(F1:9,3,F2:,F2:9,8,1,8)
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)
SORT FIELDS=COPY
/*
Code: Select all
BLOCKSET COPY TECHNIQUE SELECTED
VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS,
- CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 10:
* CONTROL STATEMENTS FOR JOINKEYS APPLICATION
JOINKEYS FILE=F1,FIELDS=(1,8,A,9,3,A)
JOINKEYS FILE=F2,FIELDS=(17,8,A,1,8,A)
REFORMAT FIELDS=(F1:9,3,F2:,F2:9,8,1,8)
£
SYNTAX ERROR
* CONTROL STATEMENTS FOR MAIN TASK (JOINED RECORDS)
SORT FIELDS=COPY
C5-K51707 C6-K51707 C7-K54603 C8-K51707 E7-K51707
END OF DFSORT
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
You have several problems with your job.
The syntax error is just that - a syntax error:
Not sure what you're trying to do with F2:, but it's invalid syntax. Valid syntax would be:
But a less obvious error is that you have different lengths for the keys in the two JOINKEYS FIELDS operands. For F1, your keys have lengths 8 and 3, but for F2, your keys have lengths 8 and 8. The lengths must match - either both 8 and 3, or both 8 and 8.
You would have gotten an error message for this after you fixed the syntax error.
The syntax error is just that - a syntax error:
Code: Select all
REFORMAT FIELDS=(F1:9,3,F2:,F2:9,8,1,8)
£
Code: Select all
REFORMAT FIELDS=(F1:9,3,F2:9,8,1,8)
You would have gotten an error message for this after you fixed the syntax error.
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page