Sort File



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

Sort File

Postby Vineet » Tue Dec 06, 2011 3:16 am

I am describing my Problem along with the Conditions. I am Having 2 Sequential Files File A (LRECL = 277, RECFM = FB) & File B (LRECL=500, RECFM=FB). Both the Files are Sorted on Item Number Field. For File A Item Number = 94,6 & for File B = 1,6. Below is my Requirement.

1. If Item Number is Present in Both The Files & > 499999. All Records From File A to be Written To File C (O/P File, LRECL = 279, RECFM=FB) & Last Two Bytes 278,2 Need to Have Space.
2. If Item Number is Present in Both The Files & < 499999. All Records From File A to be Written To File C (O/P File, LRECL = 279, RECFM=FB) & Last Two Bytes 278,2 Need to Have Value from File B Starting (172,2)
3. If Item Number Present in File A But Not in File and > 499999 OR < 499999 OR = 499999 All Records From File A to be Written To File C (O/P File, LRECL = 279, RECFM=FB) & Last Two Bytes 278,2 Need to Have Space.

Note: Output File C Should have Length = 279 i.e. Length of File A + 2 Bytes Extra at the End. I have Raised a Similar Query, as Requirement is Changed so mailing U. Below is the JCL I wrote. I am Getting Error Please Correct me.

JCL:

JOINKEYS F1=IN1,FIELDS=(94,6,A)
JOINKEYS F2=IN2,FIELDS=(1,6,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,278,?,F2:172,2)
INREC IFTHEN=(WHEN=(278,1,CH,EQ,C'B',AND,94,6,ZD,GT,499999),
OVERLAY=(278:2X)),
IFTHEN=(WHEN=(278,1,CH,EQ,C'B',AND,94,6,ZD,LT,499999),
OVERLAY=(278:172,2)),
IFTHEN=(WHEN=(278,1,CH,EQ,C'1',AND,94,6,ZD,LE,499999),
OVERLAY=(278:2X))
IFTHEN=(WHEN=(278,1,CH,EQ,C'1',AND,94,6,ZD,GE,499999),
OVERLAY=(278:2X))
SORT FIELDS = (94,6,ZD,A)
OUTREC BUILD=(1,279)

ERROR:
ICE411I 0 THIS IS THE JOINKEYS MAIN TASK FOR JOINING F1 AND F2
ICE416I 0 JOINKEYS IS USING THE F1 SUBTASK FOR IN1 - SEE JNF1JMSG MESSAGES
ICE416I 1 JOINKEYS IS USING THE F2 SUBTASK FOR IN2 - SEE JNF2JMSG MESSAGES
ICE414A 0 IN1 (F1) REFORMAT FIELD END AT 278 IS BEYOND LENGTH OF 277
ICE751I 0 C5-K90025 C6-K90025 C7-K54603 E7-K62201
ICE052I 3 END OF DFSORT

I appreciate UR Help.
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sort File

Postby Frank Yaeger » Tue Dec 06, 2011 3:47 am

CE414A 0 IN1 (F1) REFORMAT FIELD END AT 278 IS BEYOND LENGTH OF 277

This error message seems pretty clear. You have:

REFORMAT FIELDS=(F1:1,278,?,F2:172,2)

So you are asking for positions 1-278 from F1, but F1 has an LRECL of only 277 so it should be 1,277, not 1,278.

REFORMAT FIELDS=(F1:1,277,?,F2:172,2)

So your reformat positions would be:

1-277: F1 positions 1-277
278: ? - indicator
279-280: F2 positions 172-173

I'm guessing something like this would do what you want, although I haven't actually tested it:

   JOINKEYS F1=IN1,FIELDS=(94,6,A)                             
   JOINKEYS F2=IN2,FIELDS=(1,6,A)                             
   JOIN UNPAIRED,F1                                             
   REFORMAT FIELDS=(F1:1,277,?,F2:172,2) 
   SORT FIELDS = (94,6,ZD,A)                 
   OUTREC IFOUTLEN=279,
      IFTHEN=(WHEN=(278,1,CH,EQ,C'B',AND,94,6,ZD,LT,499999),
               BUILD=(1,277,278:279,2)),
      IFTHEN=(WHEN=NONE,BUILD=(1,277,278:2X))                         
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: Sort File

Postby Vineet » Tue Dec 06, 2011 5:58 pm

Hi Frank,

Thanks for the Help. As Suggested I made the Required Changes but I am not getting the Desire Result. Below am Present my JCL along with Data & Expected O/P.

File A: Item Number = 000008, Position 94,6. LRECL = 277, RECFM=FB. - Input File
File B: Item Number = 000008, Position 01,6. LRECL = 1200, RECFM=FB. - Input File
File C: All Data From File A, Along With 2 Bytes Appearing in Position 172,2 Of File B. Addiional Field Should Appear In the Last. In My Case Record Appearing in File B at Position is 16 So Final O/P
<------------ File A Records (277 Bytes) -------------><16 (2 Bytes From File B)>. I am Getting Space. Attributes for File C = LRECL = 279, RECFM = FB.

JOINKEYS F1=IN1,FIELDS=(94,6,A)
JOINKEYS F2=IN2,FIELDS=(1,6,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,277,?,F2:172,2)
SORT FIELDS=(94,6,ZD,A)
OUTREC IFOUTLEN=279,
IFTHEN=(WHEN=(278,1,CH,EQ,C'B',AND,94,6,ZD,GT,499999),
BUILD=(1,277,278:2X)),
IFTHEN=(WHEN=(278,1,CH,EQ,C'B',AND,94,6,ZD,LE,499999),
BUILD=(1,277,278:279,2)), -----------------------------------------> A
IFTHEN=(WHEN=(278,1,CH,EQ,C'1',AND,94,6,ZD,LE,499999),
BUILD=(1,277,278:279,2)),
IFTHEN=(WHEN=(278,1,CH,EQ,C'1',AND,94,6,ZD,GT,499999),
BUILD=(1,277,278:279,2))

Please Suggest. As Per The Requirment, Condition Which is Satisfied is Marked As A. Is there any Limit of Using IFTHEN Or If we are having Multiple Instance of IFTHEN we Need to Have WHEN=NONE
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sort File

Postby Robert Sample » Tue Dec 06, 2011 9:12 pm

Vineet: you ARE aware, I hope, that the IBM SORT group is on the West Coast of the U.S., and it is not yet 8 AM Tuesday morning there?
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: Sort File

Postby Vineet » Tue Dec 06, 2011 9:21 pm

I am Sorry Robert My Apologies for Same. Well I am bit Panici as I have to make the delivery. I'll Make Note of It
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sort File

Postby skolusu » Tue Dec 06, 2011 10:48 pm

vineet,

You need to explain what is NOT working for you instead of a blanket statement , "It doesn't work" as we can neither see your input or output or read your mind. However I see a couple of errors in your control cards

When you don't have a matching record there is nothing to populate in the last 2 bytes. The match indicator will be 1 in this case and you don't have to code an IFTHEN statement for each and every condition. You also don't have to SORT the input file once again after the JOIN.

Use the following JCL which will give you the desired results.

[code]
//STEP0100 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//INA      DD DSN=Your input FB 0277 byte file,DISP=SHR
//INB      DD DSN=Your input FB 1200 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=INA,FIELDS=(94,6,A)                       
  JOINKEYS F2=INB,FIELDS=(01,6,A)                       
  JOIN UNPAIRED,F1                                       
  REFORMAT FIELDS=(F1:1,277,F2:172,2,?),FILL=C' '       
 
  INREC IFOUTLEN=279,                                   
  IFTHEN=(WHEN=(280,1,CH,EQ,C'B',AND,94,6,ZD,GT,499999),
  OVERLAY=(278:2X))                                     
//*
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: Sort File

Postby Vineet » Tue Dec 06, 2011 11:26 pm

Hi KOLUSU,
Thanks for Quick Reply. I am Sorry For Icomplete Information. Let me Explain you my Requirement & Issue I am Facing. I am having 2 Sequential Files File A (LRECL = 277, RECFM = FB) ANd File B (LRECL = 1204,RECFM = FB). Both File Having Item Number as Key Field. For File A Item Number Start Position (94,6) And For File B Item Number Start Position (1,6). Need to Write The O/P After Comparision to File C Having Lrecl = 279 & RECFM = FB. File C is Having Same Attribute as File A, In Addition 2 Bytes Extra at The Last Position 278 - 279. Where We Need To Write Value from File B.
We need to Write The Records To O/P File From File A, Along with 2 Additional Bytes which is Based on the Below Listed Criteria.

1. If Item Number Present in Both The Files & Item and Item Number > 499999, Last 2 Bytes of File C(O/P) Should Have Spaces.
2. If Item Number Present in Both The Files & Item and Item Number < 499999, Last 2 Bytes of File C(O/P) Should Have Value From File B, For Which Start Position is 172,2.
3. If Item Present in File A Only & Not Present in File B. If Item Number > 499999 Or < 499999 Last 2 BYtes Should have Spaces.
4. If Item present in File A & File B And Item Number = 499999, O/P File Should Have Value From File B, For Which Start Position is 172,2.

Issue I was Facing That To the O/P File I am Able to Write The Records from File A, Where as Last 2 Bytes Always Appearing as Blank (Space). Solution What U suggested is Not Giving the desire O/P, Its Printing 'C' And Spaces Instead of Value Appearing for 172,2. I am Presenting Below Example.

File A: Item Number = 000008, Position 94,6. LRECL = 277, RECFM=FB. - Input File
File B: Item Number = 000008, Position 01,6. LRECL = 1204, RECFM=FB. - Input File
File C: All Data From File A, Along With 2 Bytes Appearing in Position 172,2 Of File B. Additional Field Should Appear In the Last. In My Case Record Appearing in File B at Position is 16 So Final O/P
<------------ File A Records (277 Bytes) -------------><16 (2 Bytes From File B)>. I am Getting Space. Attributes for File C = LRECL = 279, RECFM = FB.

I Hope I am Clear. If ANy Query / Concern please Do let me Know.

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

Re: Sort File

Postby skolusu » Wed Dec 07, 2011 12:35 am

vineet,


Vineet wrote:

Your 1st post

File B (LRECL=500, RECFM=FB)

Your 2nd post

File B: LRECL = 1200, RECFM=FB. - Input File

Your 3rd post

File B: LRECL = 1204, RECFM=FB. - Input File


It is amazing how your file B attributes changes with every post. Are sure that your input is FB and NOT VB?

vineet wrote:Issue I was Facing That To the O/P File I am Able to Write The Records from File A, Where as Last 2 Bytes Always Appearing as Blank (Space). Solution What U suggested is Not Giving the desire O/P, Its Printing 'C' And Spaces Instead of Value Appearing for 172,2. I am Presenting Below Example.


Post the contents of the key (1,6) and contents from pos 172, 2 of file B or better yet show us the output from JNF2MSG sysout and we will figure out if your input is FB or VB
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: Sort File

Postby Vineet » Wed Dec 07, 2011 1:31 am

I am Getting Required O/P, I rectified my error. Thanks for Help. but I am getting one Extra Character 'C' Please Refer below. Here 16 is the Actual Value appearing in File B Position 172,2. Regarding C I am not Sure. How To Remove C.

0000000009 T BOGO 16
0000000009 T Promoted Price 16
0000000001 J Cents Off C
0000000001 J Cents Off C
0000000001 J Cents Off C
0000000001 J Cents Off C
0000000001 J Cents Off C
0000000001 J Cents Off C
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sort File

Postby skolusu » Wed Dec 07, 2011 1:54 am

vineet wrote:I am Getting Required O/P, I rectified my error.


And the error is?

vineet wrote:but I am getting one Extra Character 'C' Please Refer below. Here 16 is the Actual Value appearing in File B Position 172,2. Regarding C I am not Sure. How To Remove C.


If your input had C and space for the item number 0000000001 you obviously will get the same in your output. Are you telling me that your input file B does not have C & space in pos 172 in your input file B for the item number 0000000001?
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

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post