IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Include header record when find detail record in a group.
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Thu Aug 12, 2010 7:32 pm
 Reply with quote
Hi,

Please help me to code in DFSORT for below scenario:

Input file (FB/80)

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
1 ABC
2 999
2 LEA
2 999
2 GRN
2 999
1 DEF
2 LEA
2 WJF
1 GHI
2 999
2 999
1 JKL
2 GRN
2 CON
2 BAT
1 MNO
2 999
2 999


Need the below out put file (FB/80):

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
1 ABC
2 999
2 999
2 999
1 GHI
2 999
2 999
1 MNO
2 999
2 999


Check for '999' at position '3' and include those records only along with corresponding header record which represents with '1' at first position.

i tried using parsing statements but no use. please help me to write a code in DFSORT.

Thanks & Regards,
Nath.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Aug 12, 2010 8:07 pm
 Reply with quote
Nath,
Could you have any other values than 1 or 2 in column 1? Does your group always end in 2 999?

Thanks,
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Thu Aug 12, 2010 8:56 pm
 Reply with quote
Hi sqlcode1,


Quote:

Could you have any other values than 1 or 2 in column 1?


No, i dont have any other value in positon '1' apart from 1 and 2

Quote:

Does your group always end in 2 999?


Not alwaz... some times ends with 2 GRN or some other value... its not similar value at end for 2.

-Nath.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Aug 12, 2010 9:44 pm
 Reply with quote
rguhanath,

Below job will give your expected output. Since it increases your LRECL by 8 bytes, I would recommend coding LRECL parm in your output file.
I guess we will wait until we hear from Frank or Kolusu to know if its appropriate to do so.

Code:

//STEP01  EXEC  PGM=ICETOOL                                             
//TOOLMSG DD SYSOUT=*                                                   
//DFSMSG  DD SYSOUT=*                                                   
//IN      DD *                                                         
1 ABC                                                                   
2 999                                                                   
2 LEA                                                                   
2 999                                                                   
2 GRN                                                                   
2 999                                                                   
1 DEF                                                                   
2 LEA                                                                   
2 WJF                                                                   
1 GHI                                                                   
2 999                                                                   
2 999                                                                   
1 JKL                                                                   
2 GRN                                                                   
2 CON                                                                   
2 BAT                                                                   
1 MNO                                                                   
2 999                                                                   
2 999                                                                   
/*                                                                     
//OUT     DD  DSN=YOURID.TEST.OUTPUT,                                 
//            DISP=(,CATLG,DELETE),                                     
//            LRECL=80,
//            UNIT=SYSDA
//TOOLIN DD *                                                           
 SELECT FROM(IN) TO(OUT) ON(31,8,CH) HIGHER(1) USING(CTL1)             
/*                                                                     
//CTL1CNTL DD *                                                         
 INCLUDE COND=(1,1,CH,EQ,C'1',OR,1,5,CH,EQ,C'2 999')                   
 INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C'1'),                       
               PUSH=(81:ID=8))                                         
 SORT FIELDS=COPY                                                       
/*


OUTPUT
Code:

1 ABC
2 999
2 999
2 999
1 GHI
2 999
2 999
1 MNO
2 999
2 999



Thanks,
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Thu Aug 12, 2010 10:00 pm
 Reply with quote
Hi sqlcode,

Many Thanks for your help !!!

Below i am giving you the exact picture of the requirement.

Quote:

Check for '999' at position '3' and include those records only


According your code, you are using
Code:

1,5,CH,EQ,C'2 999')


Here i want the record which satisfies the value '999' at 3rd position. means might be the '2' record having some value at 2 nd position instead of space. so i cant check with '2 999' value. But your logic is correct. Please modify according to my requirement.

Thanks,
Nath.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Aug 12, 2010 10:09 pm
 Reply with quote
rguhanath,
Quote:
Please modify according to my requirement.

YES SIR!!! You could have done it yourself.

Please find below updated jcl...
Code:

//STEP01  EXEC  PGM=ICETOOL                                             
//TOOLMSG DD SYSOUT=*                                                   
//DFSMSG  DD SYSOUT=*                                                   
//IN      DD *                                                         
1 ABC                                                                   
2 999                                                                   
2 LEA                                                                   
2 999                                                                   
2 GRN                                                                   
2 999                                                                   
1 DEF                                                                   
2 LEA                                                                   
2 WJF                                                                   
1 GHI                                                                   
2 999                                                                   
2 999                                                                   
1 JKL                                                                   
2 GRN                                                                   
2 CON                                                                   
2 BAT                                                                   
1 MNO                                                                   
2 999                                                                   
2 999                                                                   
/*                                                                     
//OUT     DD  DSN=YOURID.TEST.OUTPUT,                                 
//            DISP=(,CATLG,DELETE),                                     
//            LRECL=80,
//            UNIT=SYSDA
//TOOLIN DD *                                                           
 SELECT FROM(IN) TO(OUT) ON(31,8,CH) HIGHER(1) USING(CTL1)             
/*                                                                     
//CTL1CNTL DD *                                                         
 INCLUDE COND=(1,1,CH,EQ,C'1',OR,3,3,CH,EQ,C'999')                   
 INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C'1'),                       
               PUSH=(81:ID=8))                                         
 SORT FIELDS=COPY                                                       
/*


Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Aug 12, 2010 11:44 pm
 Reply with quote
sqlcode1,

Your SELECT statement should have ON(81,8,CH), not ON(31,8,CH).

Quote:
Since it increases your LRECL by 8 bytes, I would recommend coding LRECL parm in your output file.
I guess we will wait until we hear from Frank or Kolusu to know if its appropriate to do so.


The ID characters can be removed by adding the following to CTL1CNTL:

Code:

   OUTFIL FNAMES=OUT,BUILD=(1,80)


Here's a corrected version of the DFSORT job:

Code:

//STEP01  EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN      DD DSN=...  input file (FB/80)
//OUT     DD DSN=...  output file (FB/80)
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(81,8,CH) HIGHER(1) USING(CTL1)
/*
//CTL1CNTL DD *
 OPTION COPY
 INCLUDE COND=(1,1,CH,EQ,C'1',OR,3,3,CH,EQ,C'999')
 INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,CH,EQ,C'1'),
               PUSH=(81:ID=8))
 OUTFIL FNAMES=OUT,BUILD=(1,80)
/*
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Aug 12, 2010 11:52 pm
 Reply with quote
Frank,
I was testing job in sysout and added seqnum at 31 instead of 81. I forgot to change the same in SELECT statement.

Apologize for the mistake and thanks for correction on seqnum and FNAMES.

Thanks,
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Fri Aug 13, 2010 3:38 pm
 Reply with quote
Hi Sqlcode1,

I apologies for the way of ask you about my work. And many thanks to you for the JCL code and it is working fine. Thanks to Frank also. You people are doing a great help to the IBM Mainframe world.

Here I want to change my requirement a slight. Could you please help me for the changed requirement also? I tried it by using your code, but I am getting different results.

Changed requirement is:
Code:


INPUT FILE IS:

1 ABC
2 (some data)                                                                   
3 999                                                                   
3 LEA                                                                   
3 999                                                                   
3 GRN                                                                   
3 999                                                                   
1 DEF                                                                   
2 (some data)                                                                   
3 LEA                                                                   
3 WJF                                                                   
1 GHI                                                                   
2 (some data)
3 999                                                                   
3 999                                                                   
1 JKL 
2 (some data)                                                                 
3 GRN                                                                   
3 CON                                                                   
3 BAT                                                                   
1 MNO
2 (some data)                                                                   
3 999                                                                   
3 999   


I want the out put file like below:

Code:

 
1 ABC
2 (some data)                                                                   
3 999         
3 999                     
3 999                     
1 GHI                                                                   
2 (some data)
3 999                                                                   
3 999                                                                   
1 MNO
2 (some data)                                                                   
3 999                                                                   
3 999   



Please help me to get the above output file and please explain the parameter ‘HIGHER(1)’ in SELECT clause which I didn’t understand the purpose.

Thanks in advance.

-Nath.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Fri Aug 13, 2010 5:06 pm
 Reply with quote
Nath,
1) Is it safe to assume that this time you don't want type '3' records with value other than '999' in the position 3 through 5?

2) After omitting unwanted type '3' records, your group will always end
with type '3' records with '999' in position 3 through 5?

Thanks,
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Fri Aug 13, 2010 7:43 pm
 Reply with quote
Hi sqlcode,

Quote:

1) Is it safe to assume that this time you don't want type '3' records with value other than '999' in the position 3 through 5?


Yes, i dont want other than '999' for type '3' records.

Quote:

2) After omitting unwanted type '3' records, your group will always end
with type '3' records with '999' in position 3 through 5?


After omitting unwanted type '3' records, the group isl always end
with type '3' records only. But the value is not sure, it might be change. like

1 ABC
2 (some data)
3 999
3 LEA
3 999
3 999
3 GRN
1 DEF
........
........
........

-Nath.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Aug 13, 2010 10:36 pm
 Reply with quote
rguhanath,

The following DFSORT JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT   
//SYSOUT   DD SYSOUT=*     
//INA      DD DSN=Your input FB 80 byte file,DISP=SHR
//INB      DD DSN=same input FB 80 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*   
//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(81,8,A),SORTED,NOSEQCK               
  JOINKEYS F2=INB,FIELDS=(81,8,A)                             
  JOIN UNPAIRED                                               
  REFORMAT FIELDS=(F1:1,80,F2:89,3)                           
  OUTFIL BUILD=(1,80),                                         
  OMIT=(81,3,ZD,EQ,0,OR,(1,1,ZD,EQ,3,AND,3,3,ZD,NE,999))       
//*
//JNF1CNTL DD *                                               
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,1,ZD,EQ,1),PUSH=(81:ID=8))
//*
//JNF2CNTL DD *                                               
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(89:3C'0')),                 
  IFTHEN=(WHEN=GROUP,BEGIN=(1,1,ZD,EQ,1),PUSH=(81:ID=8)),     
  IFTHEN=(WHEN=(3,3,ZD,EQ,999),OVERLAY=(89:C'001'))           
  SUM FIELDS=(89,3,ZD)                                         
//*
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Thu Aug 19, 2010 12:34 pm
 Reply with quote
Hi Kolusu,

Thanks for your help. I am in vacation for few days so that I am not able to reply you. I have tried with your code but I am getting the below mentioned error in SPOOL area.

Code:

1ICE143I 0 BLOCKSET     COPY  TECHNIQUE SELECTED                               
 ICE250I 0 VISIT http://www.ibm.com/storage/dfsort FOR DFSORT PAPERS, EXAMPLES A
 ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R10 - 02:48 ON THU A
0           OPTION COPY                                                         
            JOINKEYS F1=INA,FIELDS=(81,8,A),SORTED,NOSEQCK                     
            $                                                                   
 ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY         
            JOINKEYS F2=INB,FIELDS=(81,8,A)                                   
            $                                                                   
 ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY         
            JOIN UNPAIRED                                                       
            $                                                                   
 ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY         
            REFORMAT FIELDS=(F1:1,80,F2:89,3)                                 
            $                                                                   
ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY


Please let me know, whether my DFSORT version (Based on above SPOOL log) is compatible or not for using JOINKEYS.

Thanks and Regards,
-Nath.
Back to top
View user's profile Send private message
ssmukul

New User


Joined: 22 Jan 2008
Posts: 19
Location: India
PostPosted: Thu Aug 19, 2010 1:55 pm
 Reply with quote
Hi Nath,

Have you given these SYSIN statements from 1st column? If yes, then move those to 2nd column.
Back to top
View user's profile Send private message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 81
Location: chennai
PostPosted: Thu Aug 19, 2010 3:16 pm
 Reply with quote
Hi ssmukul,

i have given the space in first column like below

Code:

//SYSIN    DD *                                               
  OPTION COPY                                                 
  JOINKEYS F1=INA,FIELDS=(81,8,A),SORTED,NOSEQCK               
  JOINKEYS F2=INB,FIELDS=(81,8,A)                             
  JOIN UNPAIRED                                               
  REFORMAT FIELDS=(F1:1,80,F2:89,3)                           
  OUTFIL BUILD=(1,80),                                         
  OMIT=(81,3,ZD,EQ,0,OR,(1,1,ZD,EQ,3,AND,3,3,ZD,NE,999))       


-Nath
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Thu Aug 19, 2010 10:41 pm
 Reply with quote
If the ICE005A messages are flagging JOINKEYS in position 2, then you don't have the DFSORT PTF for JOINKEYS installed. Ask your System Programmer to install z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009). For more information on the functions available with this DFSORT PTF, see:

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us