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

Excluding group of records
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
zh_lad

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Wed Aug 18, 2010 5:02 pm
 Reply with quote
Hi,

FB file
LRECL = 500
Account number, P=1, L=36

It has got group of records per account (Header followed by detail records, however, no trailer)
Record type P=43, L=3 (Header has value CU, Detail records have different values: TX, MX, DR, EX, IA etc). Header is always the first record in a set.

I want to get rid to those set of records where P=60, L=3 has following values:340, 341, 350, 351, 360, or 361 on Header record (CU type)

Example
Input:

700940520050000000996338342500031014 CU100000000001500399001 0
700940520050000000996338342500031014 DR110000000002UKCAPITAL HOUSE
700940520050000000996338342500031014 EX200000000000001GBP 030000000
700940520050000000996338342500031014 TX300000000008001GBPPU04000000
700940520050000000996338342500031014 PF900000000001A094503201008132

700940520050000001006338342500031022 CU100000000001500350001 0
700940520050000001006338342500031022 CL11000000000104008482701CHILL
700940520050000001006338342500031022 DR110000000002UKCAPITAL HOUSE
700940520050000001006338342500031022 LC8000000000000001CASH LIMIT 2

700940520050000001016338342500031212 CU100000000001500398001 0
700940520050000001016338342500031212 CL11000000000104008484603BASIM
700940520050000001016338342500031212 DR110000000002UKCAPITAL HOUSE
700940520050000001016338342500031212 MX800000000010001GBP0000001030
700940520050000001016338342500031212 PF900000000001A094505201008132


Output:
700940520050000000996338342500031014 CU100000000001500399001 0
700940520050000000996338342500031014 DR110000000002UKCAPITAL HOUSE
700940520050000000996338342500031014 EX200000000000001GBP 030000000
700940520050000000996338342500031014 TX300000000008001GBPPU04000000
700940520050000000996338342500031014 PF900000000001A094503201008132

700940520050000001016338342500031212 CU100000000001500398001 0
700940520050000001016338342500031212 CL11000000000104008484603BASIM
700940520050000001016338342500031212 DR110000000002UKCAPITAL HOUSE
700940520050000001016338342500031212 MX800000000010001GBP0000001030
700940520050000001016338342500031212 PF900000000001A094505201008132

Reject records:
700940520050000001006338342500031022 CU100000000001500350001 0
700940520050000001006338342500031022 CL11000000000104008482701CHILL
700940520050000001006338342500031022 DR110000000002UKCAPITAL HOUSE
700940520050000001006338342500031022 LC8000000000000001CASH LIMIT 2

Second set of records have go to Rejected files as P=60, L=3 has got value 350.

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

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Aug 18, 2010 6:57 pm
 Reply with quote
zh_lad,
Your input data doesn't match your description of input position.

Quote:
It has got group of records per account (Header followed by detail records, however, no trailer). Record type P=43, L=3 (Header has value CU, Detail records have different values: TX, MX, DR, EX, IA etc).
In your shown input record, CU is at position 38 and is of length 2.


Quote:
I want to get rid to those set of records where P=60, L=3 has following values:340, 341, 350, 351, 360, or 361 on Header record (CU type)

340,341,350,351,360,361 in your shown input record is at position 55.

I am assuming your input data is correct and below job will give you what you asked for.

Code:
//STEP01 EXEC PGM=SORT                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DISP=SHR,DSN=YOUR.FB500.INPUT.FILE                                               
//REJECT   DD SYSOUT=*                                                 
//SELECT   DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(38,2,CH,EQ,C'CU'),PUSH=(501:55,3))   
  SORT FIELDS=COPY                                                     
  OUTFIL FNAMES=REJECT,INCLUDE=(501,3,CH,EQ,C'340',OR,                 
                                501,3,CH,EQ,C'341',OR,                 
                                501,3,CH,EQ,C'350',OR,                 
                                501,3,CH,EQ,C'351',OR,                 
                                501,3,CH,EQ,C'360',OR,                 
                                501,3,CH,EQ,C'361'),                   
                       BUILD=(1,500)                                   
  OUTFIL FNAMES=SELECT,BUILD=(1,500),SAVE                               
/*                                                                     


If your description is correct then use below job.


Code:
//STEP01 EXEC PGM=SORT                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DISP=SHR,DSN=YOUR.FB500.INPUT.FILE
//REJECT   DD SYSOUT=*                                                 
//SELECT   DD SYSOUT=*                                                 
//SYSIN    DD *                                                         
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(43,3,CH,EQ,C'CU '),PUSH=(501:60,3))   
  SORT FIELDS=COPY                                                     
  OUTFIL FNAMES=REJECT,INCLUDE=(501,3,CH,EQ,C'340',OR,                 
                                501,3,CH,EQ,C'341',OR,                 
                                501,3,CH,EQ,C'350',OR,                 
                                501,3,CH,EQ,C'351',OR,                 
                                501,3,CH,EQ,C'360',OR,                 
                                501,3,CH,EQ,C'361'),                   
                       BUILD=(1,500)                                           
  OUTFIL FNAMES=SELECT,BUILD=(1,500),SAVE                                             
/*                                                                     


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

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Wed Aug 18, 2010 6:57 pm
 Reply with quote
Minor correction:

Record type P=43, L=2 (Header has value CU, Detail records have different values: TX, MX, DR, EX, IA etc). Header is always the first record in a set.

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

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Wed Aug 18, 2010 6:59 pm
 Reply with quote
zh_lad,
Please change first Inrec condition in the 2nd solution as below

Code:
INREC IFTHEN=(WHEN=GROUP,BEGIN=(43,2,CH,EQ,C'CU'),PUSH=(501:60,3))   


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

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Wed Aug 18, 2010 8:49 pm
 Reply with quote
Thanks sqlcode1. I have customized your code to cater my requirement. It worked well.

Thanks alot for your help.
Back to top
View user's profile Send private message
zh_lad

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Wed Aug 18, 2010 10:07 pm
 Reply with quote
It was pleasant surprise when I realized records are group solely based on header record.
I realized no where account number is mentioned to identify groups of records.
I will look for these keyword: begin, group and push on SORT manual to familiarize myself to use them in future requirements.

Many thanks.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Aug 18, 2010 10:18 pm
 Reply with quote
ZH_LAD,

Here is a simplified version of the job.

Code:

//STEP0100 EXEC PGM=SORT       
//SYSOUT   DD SYSOUT=*         
//SORTIN   DD DSN=your input fb 500 byte file,DISP=SHR
//SORTOUT  DD SYSOUT=*                                               
//REJECT   DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  SORT FIELDS=COPY                                                   
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(43,2,CH,EQ,C'CU'),PUSH=(501:60,3))
  OUTFIL BUILD=(1,500),OMIT=(501,3,SS,EQ,C'340,341,350,351,360,361')
  OUTFIL FNAMES=REJECT,SAVE,BUILD=(1,500)                           
//*


If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

Use [URL] BBCode for External Links
Back to top
View user's profile Send private message
zh_lad

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Thu Sep 02, 2010 12:55 pm
 Reply with quote
Thanks for your help so far. I am continuing this thread as I would like to omit different group of records.
Above, PUSH is applied on Header record (CU type). How do I apply Push on a detail record e.g. Record type is PF.
I would like to omit group of records based on one of the detail record.

Many thanks.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Thu Sep 02, 2010 5:56 pm
 Reply with quote
What about other conditions from the original requirement? Please show sample input and output again.

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

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Fri Sep 03, 2010 12:14 am
 Reply with quote
Thanks sqlcode1 for replying.


LRECL = 500
Account number, P=1, L=36

It has got group of records per account. Header followed by detail records, however, no trailer. Header is always the first record.

Header record P=43, L=2, Value = CU
Details records have got values TX, MX, DR, EX, IA, PF etc on P=43, L=2
Header is always the first record in a set.


I want to get rid to such groups where PF record has got value A0 (length 2) on column 57.


Input:
700940520050000000996338342500031014 CU100000000001500399001 0
700940520050000000996338342500031014 DR110000000002UKCAPITAL HOUSE
700940520050000000996338342500031014 EX200000000000001GBP 030000000
700940520050000000996338342500031014 TX300000000008001GBPPU04000000
700940520050000000996338342500031014 PF900000000001A194503201008132

700940520050000001006338342500031022 CU100000000001500350001 0
700940520050000001006338342500031022 CL11000000000104008482701CHILL
700940520050000001006338342500031022 DR110000000002UKCAPITAL HOUSE
700940520050000001006338342500031022 PF900000000001A094505201008132

700940520050000001016338342500031212 CU100000000001500398001 0
700940520050000001016338342500031212 CL11000000000104008484603BASIM
700940520050000001016338342500031212 DR110000000002UKCAPITAL HOUSE
700940520050000001016338342500031212 MX800000000010001GBP0000001030
700940520050000001016338342500031212 PF900000000001A194505201008132


Output:
700940520050000000996338342500031014 CU100000000001500399001 0
700940520050000000996338342500031014 DR110000000002UKCAPITAL HOUSE
700940520050000000996338342500031014 EX200000000000001GBP 030000000
700940520050000000996338342500031014 TX300000000008001GBPPU04000000
700940520050000000996338342500031014 PF900000000001A194503201008132

700940520050000001016338342500031212 CU100000000001500398001 0
700940520050000001016338342500031212 CL11000000000104008484603BASIM
700940520050000001016338342500031212 DR110000000002UKCAPITAL HOUSE
700940520050000001016338342500031212 MX800000000010001GBP0000001030
700940520050000001016338342500031212 PF900000000001A194505201008132

Reject records:
700940520050000001006338342500031022 CU100000000001500350001 0
700940520050000001006338342500031022 CL11000000000104008482701CHILL
700940520050000001006338342500031022 DR110000000002UKCAPITAL HOUSE
700940520050000001006338342500031022 PF900000000001A094505201008132


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

Active Member


Joined: 08 Apr 2010
Posts: 577
Location: USA
PostPosted: Sat Sep 04, 2010 1:33 am
 Reply with quote
zh_lad,

Is your Account Number (bytes 1 through 36) unique for each set of records? If it is unique and provided that you have latest DFSort PTF,below mentioned job should give you desired result.

Let me know if works. I am still trying to see if a solution without JOINKEY is possible.

Code:
//S1 EXEC PGM=SORT                                       
//SYSOUT DD SYSOUT=*                                     
//SORTJNF1 DD DISP=SHR,DSN=YOUR FB500 FILE               
//SORTJNF2 DD DISP=SHR,DSN=YOUR FB500 FILE               
//SORTOUT DD SYSOUT=*                                     
//SYSIN DD *                                             
  JOINKEYS FILE=F1,FIELDS=(01,36,A)                       
  JOINKEYS FILE=F2,FIELDS=(01,36,A)                       
  JOIN UNPAIRED,F2,ONLY                                   
  REFORMAT FIELDS=(F2:1,500)                             
  OPTION COPY                                             
/*                                                       
//JNF1CNTL DD *                                           
 INCLUDE COND=(38,2,CH,EQ,C'PF',AND,52,2,CH,EQ,C'A0')     
/*                                                       


OUTPUT
Code:
700940520050000000996338342500031014 CU100000000001500399001 0     
700940520050000000996338342500031014 DR110000000002UKCAPITAL HOUSE
700940520050000000996338342500031014 EX200000000000001GBP 030000000
700940520050000000996338342500031014 TX300000000008001GBPPU04000000
700940520050000000996338342500031014 PF900000000001A194503201008132
700940520050000001016338342500031212 CU100000000001500398001 0     
700940520050000001016338342500031212 CL11000000000104008484603BASIM
700940520050000001016338342500031212 DR110000000002UKCAPITAL HOUSE
700940520050000001016338342500031212 MX800000000010001GBP0000001030
700940520050000001016338342500031212 PF900000000001A194505201008132


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

Active User


Joined: 06 Jun 2009
Posts: 115
Location: UK
PostPosted: Sat Sep 04, 2010 2:04 am
 Reply with quote
Hi sqlcode1,

Thanks for replying. Yes! account number is unique for each set of record.

I will run your JCL on Monday as its weekend now. If you get a different solution then please do let me know.

many thanks.
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 Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
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