Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Include header record when find detail record in a group.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
rguhanath

New User


Joined: 16 Oct 2006
Posts: 80
Location: chennai

PostPosted: Thu Aug 12, 2010 7:32 pm    Post subject: Include header record when find detail record in a group.
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: 578
Location: USA

PostPosted: Thu Aug 12, 2010 8:07 pm    Post subject:
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: 80
Location: chennai

PostPosted: Thu Aug 12, 2010 8:56 pm    Post subject:
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: 578
Location: USA

PostPosted: Thu Aug 12, 2010 9:44 pm    Post subject:
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: 80
Location: chennai

PostPosted: Thu Aug 12, 2010 10:00 pm    Post subject:
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: 578
Location: USA

PostPosted: Thu Aug 12, 2010 10:09 pm    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 12, 2010 11:44 pm    Post subject:
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: 578
Location: USA

PostPosted: Thu Aug 12, 2010 11:52 pm    Post subject:
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: 80
Location: chennai

PostPosted: Fri Aug 13, 2010 3:38 pm    Post subject:
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: 578
Location: USA

PostPosted: Fri Aug 13, 2010 5:06 pm    Post subject:
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: 80
Location: chennai

PostPosted: Fri Aug 13, 2010 7:43 pm    Post subject:
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    Post subject:
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: 80
Location: chennai

PostPosted: Thu Aug 19, 2010 12:34 pm    Post subject:
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    Post subject:
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: 80
Location: chennai

PostPosted: Thu Aug 19, 2010 3:16 pm    Post subject:
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 Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Aug 19, 2010 10:41 pm    Post subject:
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:

http://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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Find out a active PGM jpsager JCL & VSAM 10 Fri Oct 07, 2016 4:32 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us