Select only paired records



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

Select only paired records

Postby venubhat » Mon Jul 14, 2014 4:30 pm

Hi to all,

I'm new to this forum. so excuse me if the query posted is duplicate etc.

I need help in setting up a new sort card to get the result i wanted.

Below is the i/p file record format.

pos 1:8    pos 9:18          pos 19:33
20006      ADMINLOGUT   111111111111111
20006      USERLOGIUT    222222222222222
20007      USERLOGIUT    555555555555555
20008      USERLOGIUT    777777777777777
20009      ADMINLOGUT   888888888888888
20009      USERLOGIUT    999999999999999


o/p should only have records(ex. 20006/20009) which have both ADMIN & USER entry in a single line and rest others to be dropped and it look like this.
pos 1:8    pos 9:18           pos 19:33                pos 34:43         pos 44:58
20006      ADMINLOGUT    111111111111111    USERLOGIUT    222222222222222
20009      ADMINLOGUT    888888888888888    USERLOGIUT    999999999999999


We need to achieve this using sort. so any help/guide would be much appreciated.

thanks
venu

Code'd
venubhat
 
Posts: 14
Joined: Mon Jul 14, 2014 4:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select only paired records

 

Re: Help on Sort Card

Postby BillyBoyo » Mon Jul 14, 2014 5:46 pm

So it looks like you have "groups" on the first five digits, and the groups can have either one record, or two records, but not more? If there are two records, one will be ADMIN and the other USER. Does that cover your data?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Help on Sort Card

Postby venubhat » Mon Jul 14, 2014 7:48 pm

Hi Billy

yes. you are right. each cust num(1st 5 digits) will have either ADMIN/USER or both(not more than two) and if we have both these for a cust num then both to be written to o/p in a single line as I said above and suppose if a cust num has only ADMIN, it is supposed to be discarded or should be dropped in o/p file.

yes. I checked some of your previous posts but could not make use of the groups to modify as per my req(since there was not clear about the positions for various fields), so would be great if you could give the sort card which addresses my issue.

thanks
venu
venubhat
 
Posts: 14
Joined: Mon Jul 14, 2014 4:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select only paired records

Postby Magesh23586 » Mon Jul 14, 2014 9:00 pm

Try this and let us know the results.
 OPTION COPY                                             
 INREC IFTHEN=(WHEN=GROUP,BEGIN=(7,5,CH,EQ,C'ADMIN'),   
              END=(7,4,CH,EQ,C'USER'),                   
              PUSH=(34:7,26,70:ID=1))                   
 OUTFIL REMOVECC,NODETAIL,                               
  SECTIONS=(70,1,TRAILER3=(1,5,34,26,X,7,26)),           
  INCLUDE=(70,1,CH,NE,C' ')                             


Thanks
Magesh
Magesh23586
 
Posts: 35
Joined: Sat Jul 05, 2014 5:36 pm
Has thanked: 1 time
Been thanked: 3 times

Re: Select only paired records

Postby BillyBoyo » Mon Jul 14, 2014 10:32 pm

I'd suggest KEYBEGIN and PUSH a SEQ=1, not an ID. PUSH of the whole record.

On OUTFIL, INCLUDE= for SEQ-field equal to 2. Use BUILD with slash operator (/) to output two records from one input record. BUILD=(82,80,/,1,80) where 81 is the SEQ-field. Example-only, with 80 byte records.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Select only paired records

Postby venubhat » Mon Jul 14, 2014 10:36 pm

hi magesh

thanks a lot...it's working but just off on the position/alignment expected in the o/p file format. also would be good if you could explain bit about the significance of REMOVECC,NODETAIL, SECTIONS and TRAILER3 and INCLUDE parms in the sort card..so that i can modify accordingly for my stuff..coz there are some more fields in i/p file after pos 58 which i need into my o/p file.
venubhat
 
Posts: 14
Joined: Mon Jul 14, 2014 4:04 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Select only paired records

Postby Magesh23586 » Mon Jul 14, 2014 11:17 pm

Please refer dfsort getting started... and dfsort application programming. . Alternatively type qw in command line in mainframes and search for required keywords.

Thanks
Magesh
Magesh23586
 
Posts: 35
Joined: Sat Jul 05, 2014 5:36 pm
Has thanked: 1 time
Been thanked: 3 times

Re: Select only paired records

Postby Robert Sample » Tue Jul 15, 2014 12:05 am

Magesh:

Alternatively type qw in command line in mainframes and search for required keywords.
Are you aware that Quick-Ref is a third-party product for which a site must pay to use? Therefore, not all sites support this product and if someone at one of these sites issues the qw command, they won't get anything back.

These users thanked the author Robert Sample for the post:
Magesh23586 (Tue Jul 15, 2014 12:18 am)
Robert Sample
Global moderator
 
Posts: 3367
Joined: Sat Dec 19, 2009 8:32 pm
Location: East Dubuque, Illinois
Has thanked: 1 time
Been thanked: 222 times

Re: Select only paired records

Postby Magesh23586 » Tue Jul 15, 2014 12:16 am

You may use Bill suggestion for best solution..

Explanation of my code.

//GROUPTST JOB (TEST,T),T,                               
//   CLASS=S,MSGCLASS=X,NOTIFY=TMJ201U                   
//STEP0100 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD *                                         
20006 ADMINLOGUT 111111111111111                         
20006 USERLOGIUT 222222222222222                         
20007 USERLOGIUT 555555555555555                         
20008 USERLOGIUT 777777777777777                         
20009 ADMINLOGUT 888888888888888                         
20009 USERLOGIUT 999999999999999                         
//SORTOUT  DD SYSOUT=*                                   
//SYSIN    DD *                                                                                         
   OPTION COPY                                Copy input record
  INREC IFTHEN=(WHEN=GROUP,
                BEGIN=(7,5,CH,EQ,C'ADMIN'),  Setting  Admin as a start record for marking
               END=(7,4,CH,EQ,C'USER'),      Setting  User as end record for marking
               PUSH=(34:7,26,70:ID=1))       Copying the two lines of records to one,
                                             Marking sequence for the record
                                             which I am interested i.e . admin and users.
  OUTFIL REMOVECC,NODETAIL,                  Excluding all the other records other than the copied one.
   SECTIONS=(70,1,                           A section break field divides the report into sets of
                                             sequential OUTFIL input records with
                                             the same binary value for that field(70,1)
   TRAILER3=(1,5,34,26,X,7,26)),             just re-arranging the copied records.
   INCLUDE=(70,1,CH,NE,C' ')                 the Final report may have records other 
                                             than the marked one,
                                             so excluding the non marked records(70,1)


Try executing your program first with following sysin

//SORTIN   DD *                                       
20006 ADMINLOGUT 111111111111111                       
20006 USERLOGIUT 222222222222222                       
20007 USERLOGIUT 555555555555555                       
20008 USERLOGIUT 777777777777777                       
20009 ADMINLOGUT 888888888888888                       
20009 USERLOGIUT 999999999999999                       
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                       
  OPTION COPY                                         
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(7,5,CH,EQ,C'ADMIN'),
               END=(7,4,CH,EQ,C'USER'),               
               PUSH=(34:7,26,70:ID=1))                 
/*       


you could see I am adding a sequence for the interested records and pushing two record to one 70,1. Finally use nodetail to remove unnecessary records
Similarly add/remove one by one, you will know the significance.

Trailer3 is nothing but a section trailer for each section you will have trailer records.



Thanks
Magesh
Magesh23586
 
Posts: 35
Joined: Sat Jul 05, 2014 5:36 pm
Has thanked: 1 time
Been thanked: 3 times

Re: Select only paired records

Postby venubhat » Tue Jul 15, 2014 10:18 am

Thanks magesh. i am able to get the results now. but a small fix s needed. right now i'm getting the result like below.

current result using the sort step you have provided:
20006 ADMINLOGUT 111111111111111 USERLOGIUT 222222222222222
20007 USERLOGIUT 555555555555555
20008 USERLOGIUT 777777777777777
20009 ADMINLOGUT 888888888888888 USERLOGIUT 999999999999999

expected result:
20006 ADMINLOGUT 111111111111111 USERLOGIUT 222222222222222
20009 ADMINLOGUT 888888888888888 USERLOGIUT 999999999999999

we need to have only records that have both ADMIN & USER for a cust num like 20006/20009 in the ex. and rest of the records where only ADMIN is exist should not be coming up in o/p file.

thanks
venubhat
 
Posts: 14
Joined: Mon Jul 14, 2014 4:04 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post