Rearrage the Feilds - DF SORT

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Rearrage the Feilds - DF SORT

Post by raulravi » Mon Apr 18, 2011 10:13 am

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

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Mon Apr 18, 2011 1:33 pm

It can be done uisng INREC/OUTREC - please seach the forums on these key-words or post your data again with BBcode tags so we can understand the position of records.
Regards,
Anuj

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Mon Apr 18, 2011 1:44 pm

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

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Apr 19, 2011 1:39 am

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.
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Tue Apr 19, 2011 1:55 pm

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]

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Tue Apr 19, 2011 3:18 pm

I do not have edit-autority in this Forum, so I post your data using BBcode tags below:

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 
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:

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=*                                          
//*                                                           
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:

Code: Select all

INREC FIELDS=(1:5,5,6:1,5,11:11,90)
If this is not what you are asking -- please explain again.
Regards,
Anuj

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Tue Apr 19, 2011 3:30 pm

Thanks A lot Anuj,

1) Can you explain this INREC FIELDS=(1:5,5,6:1,5,11:11,90)?

2) How to insert image in the message? As you did Mainframe code...

Raul

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed Apr 20, 2011 5:47 am

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?
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.

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
and then use an INREC statement like this:

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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Apr 20, 2011 10:54 am

Dear Frank,

Thank you very much..

1)Do we need to set up symbols like this for all the 100 feilds in the
INREC?

2) How to paste my Mainframe Screen here in this message, to explain
you better?

Raul[/img]

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Wed Apr 20, 2011 1:31 pm

You're welcome.
raulravi wrote:1) Can you explain this INREC FIELDS=(1:5,5,6:1,5,11:11,90)?
You know, you've Frank on this thread - master of masters, when it comes to DFSort, I'll try to answer however:

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.
2) How to insert image in the message? As you did Mainframe code...
I did not use any "image" - I used BBcode tags.

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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Wed Apr 20, 2011 4:01 pm

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:

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
Thanks,
Raul

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Apr 21, 2011 1:12 am

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.
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

raulravi
Member
Posts: 47
Joined: Mon Apr 18, 2011 9:51 am
Location: India

Post by raulravi » Thu Apr 21, 2011 2:07 pm

Hi Frank,

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                                                    
/*                                                                   
Error:

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                                             
Raul

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Apr 21, 2011 9:00 pm

You have several problems with your job.

The syntax error is just that - a syntax error:

Code: Select all

 REFORMAT FIELDS=(F1:9,3,F2:,F2:9,8,1,8)                  
                            £        
Not sure what you're trying to do with F2:, but it's invalid syntax. Valid syntax would be:

Code: Select all

  REFORMAT FIELDS=(F1:9,3,F2:9,8,1,8)                  
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.
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

Post Reply

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



Other References
Mainframe Tools and others