Add * to the matched record



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

Add * to the matched record

Postby Kitz » Mon Aug 15, 2011 7:32 pm

Hello there,

I have to compare two files; if there is a match then an asterisk needs to added to the record of the second file. However, the first file is a fixparm wherein it contains different type of record to be compared with the second file.

1st file (40 lrecl):The first field is 8 length and second filed start at 14 position.

PARDA001 aaaaaaaaaaaaaaaaaaaaaaa
PBCCA002 aaaaaaaaaaaaaaaaaaaaaaa
PLUAK005 aaaaaaaaaaaaaaaaaaaaaaa

2nd file is a fixparm
PAR
PLUAK

The output should contain all the records from the 1st file but for the matched records an asterisk(*) needs to be added after first 8 length. The second field should start from 14th position
The output should appear something like below:
PARDA001* aaaaaaaaaaaaaaaaaaaaaaa
PBCCA002 aaaaaaaaaaaaaaaaaaaaaaa
PLUAK005* aaaaaaaaaaaaaaaaaaaaaaa

I am just learning joins now and already using simple joins in my jcls.

I tried to do this like below (considering the fixparms are hard coded like PARDA001*):
JOINKEYS FILE=F1,FIELDS=(1,8,A)
JOINKEYS FILE=F2,FIELDS=(1,8,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F2:1,9,F1:10,40)
OPTION COPY

but this takes me into complex as I need to use all wild characters after PAR and also the it doesn't give complete record of the unmatched file.

Could you please help?

Thanks & Regards,
Kitz
Kitz
 
Posts: 47
Joined: Thu Mar 17, 2011 3:46 am
Has thanked: 0 time
Been thanked: 1 time

Re: Add * to the matched record

Postby skolusu » Mon Aug 15, 2011 9:27 pm

Kitz,

Asumming the 2nd file has the full 8 byte key to be matched with the first file, the following DFSORT JCL will give you the desired results.

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
----+----1----+----2----+----3----+----4----+----5----+----6----+---
//INA      DD *                                                     
PARDA001     AAAAAAAAAAAAAAAAAAAAAAA                               
PBCCA002     AAAAAAAAAAAAAAAAAAAAAAA                               
PLUAK005     AAAAAAAAAAAAAAAAAAAAAAA                               
//INB      DD *                                                     
PARDA001                                                           
PLUAK005                                                           
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(1,8,A)                                   
  JOINKEYS F2=INB,FIELDS=(1,8,A)                                   
  JOIN UNPAIRED,F1                                                 
  REFORMAT FIELDS=(F1:1,40,?)                                       
  INREC IFOUTLEN=40,IFTHEN=(WHEN=(41,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))
//*



The output from this job is

----+----1----+----2----+----3----+----4----+----5----+----6----+---
PARDA001*    AAAAAAAAAAAAAAAAAAAAAAA
PBCCA002     AAAAAAAAAAAAAAAAAAAAAAA
PLUAK005*    AAAAAAAAAAAAAAAAAAAAAAA


If that is not what you want then you need to explain with a better example.
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: Add * to the matched record

Postby Kitz » Tue Aug 16, 2011 12:00 am

Thanks Kolusu.

Sorry for the confusion.

1st file (40 lrecl):The first field is 8 length and second filed start at 14 position.

PARDA001 aaaaaaaaaaaaaaaaaaaaaaa
PBCCA002 aaaaaaaaaaaaaaaaaaaaaaa
PLUAK005 aaaaaaaaaaaaaaaaaaaaaaa

2nd file is a fixparm
PAR
PLUAK
PVRLK
56D

The output should contain all the records from the 1st file but for the matched records an asterisk(*) needs to be added after first 8 length. The second field should start from 14th position
The output should appear something like below:
PARDA001* aaaaaaaaaaaaaaaaaaaaaaa
PBCCA002 aaaaaaaaaaaaaaaaaaaaaaa
PLUAK005* aaaaaaaaaaaaaaaaaaaaaaa

The fixparm field will have a length from 3 to 8 and it can appear anywhere in 1-8 position of the first file.

Thanks & Regards,
Kitz
Kitz
 
Posts: 47
Joined: Thu Mar 17, 2011 3:46 am
Has thanked: 0 time
Been thanked: 1 time

Re: Add * to the matched record

Postby skolusu » Tue Aug 16, 2011 2:04 am

Kitz,

DFSORT does not have the ability of Wild card search , however here is a trick to get the desired results. The trick here is to concatenate the same input file 6 times so that we do the match from 3 bytes to max of 8 bytes.

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=Your input file,DISP=SHR
//SORTOUT  DD DSN=&&HDR,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//SYSIN    DD *                                             
  OPTION COPY,STOPAFT=1                                     
  OUTFIL REMOVECC,NODETAIL,HEADER1=(3'$')                   
//*                                     
//STEP0200 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//INA      DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD Your input file,DISP=SHR       
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD Your input file,DISP=SHR       
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD Your input file,DISP=SHR       
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD Your input file,DISP=SHR       
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD Your input file,DISP=SHR       
//         DD DSN=&&HDR,DISP=SHR,VOL=REF=*.STEP0100.SORTOUT         
//         DD Your input file,DISP=SHR       
//INB      DD *                                                     
PAR                                                                 
PLUAK                                                               
PVRLK                                                               
56D                                                                 
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                 
  JOINKEYS F1=INA,FIELDS=(50,8,A)                               
  JOINKEYS F2=INB,FIELDS=(1,8,A)                               
  JOIN UNPAIRED,F1                                             
  REFORMAT FIELDS=(F1:1,57,?,F2:1,8)                           
  SORT FIELDS=(1,8,CH,A,58,1,CH,A),EQUALS                       
 
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),PUSH=(58:58,1,41,1)),
  IFTHEN=(WHEN=(58,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))             
 
  OUTFIL BUILD=(1,40),                                         
  OMIT=(1,3,CH,EQ,C'$$$',OR,                                   
        58,1,SS,EQ,C'B,1',AND,(59,1,CH,NE,41,1,CH))             
//*
//JNF1CNTL DD *                                                 
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'$$$'),           
  PUSH=(41:ID=1,SEQ=8)),                                       
  IFTHEN=(WHEN=(41,1,CH,EQ,C'1'),OVERLAY=(50:1,3)),             
  IFTHEN=(WHEN=(41,1,CH,EQ,C'2'),OVERLAY=(50:1,4)),             
  IFTHEN=(WHEN=(41,1,CH,EQ,C'3'),OVERLAY=(50:1,5)),             
  IFTHEN=(WHEN=(41,1,CH,EQ,C'4'),OVERLAY=(50:1,6)),             
  IFTHEN=(WHEN=(41,1,CH,EQ,C'5'),OVERLAY=(50:1,7)),             
  IFTHEN=(WHEN=(41,1,CH,EQ,C'6'),OVERLAY=(50:1,8))             
//*


The output from this
PARDA001*    AAAAAAAAAAAAAAAAAAAAAAA
PBCCA002     AAAAAAAAAAAAAAAAAAAAAAA
PLUAK005*    AAAAAAAAAAAAAAAAAAAAAAA
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: Add * to the matched record

Postby Kitz » Tue Aug 16, 2011 4:56 pm

Thanks a lot Kolusu.

The first approach is quite simple, but we have a long list of fixparm members and so we are currenly dicussing on its impact.

I tried the second approach to concatenate the files for 6 times, but it gives out an error:
JOINKEYS F1=INA,FIELDS=(50,8,A)
JOINKEYS F2=INB,FIELDS=(1,8,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,57,?,F2:1,8)
SORT FIELDS=(1,8,CH,A,58,1,CH,A),EQUALS

OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),PUSH=(58:58,1,41,1)),
£
0 OPERAND DEFINER ERROR
IFTHEN=(WHEN=(58,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))
£
0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY

OUTFIL BUILD=(1,40),
OMIT=(1,3,CH,EQ,C'$$$',OR,
58,1,SS,EQ,C'B,1',AND,(59,1,CH,NE,41,1,CH))
0 C5-K51707 C6-K51707 C7-K54603 C8-K51707 E7-K51707
3 END OF DFSORT

I am not much familier with this advanced KEYBEGIN but tried few thoughts but it did not work.

Please help.

Thanks & Regards,
Kitz
Kitz
 
Posts: 47
Joined: Thu Mar 17, 2011 3:46 am
Has thanked: 0 time
Been thanked: 1 time

Re: Add * to the matched record

Postby BillyBoyo » Tue Aug 16, 2011 5:09 pm

Do you have a blank in front of this?

IFTHEN=(WHEN=(58,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Add * to the matched record

Postby Kitz » Tue Aug 16, 2011 6:06 pm

Its like this:
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),PUSH=(58:58,1,41,1)),
IFTHEN=(WHEN=(58,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))
Kitz
 
Posts: 47
Joined: Thu Mar 17, 2011 3:46 am
Has thanked: 0 time
Been thanked: 1 time

Re: Add * to the matched record

Postby BillyBoyo » Tue Aug 16, 2011 6:38 pm

That looks like there is no blank. Try a blank as the first character.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Add * to the matched record

Postby skolusu » Tue Aug 16, 2011 10:03 pm

Kitz,

Looks like you don't have the PTFH which supports the KEYBEGIN feature. Change your OUTREC statements to the following. i.e

  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,8),PUSH=(58:58,1,41,1)),
  IFTHEN=(WHEN=(58,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))   


To
   OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(59:SEQNUM,8,ZD,RESTART=(1,8))),
   IFTHEN=(WHEN=GROUP,BEGIN=(59,8,ZD,EQ,1),PUSH=(58:58,1,41,1)),     
   IFTHEN=(WHEN=(58,1,CH,EQ,C'B'),OVERLAY=(9:C'*'))                 
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: Add * to the matched record

Postby Kitz » Thu Aug 18, 2011 4:51 pm

Thanks a lot Kolusu.

I too felt that the KEYBIGIN error should be something related to updated version software of SORT may not be available with us.

I can say this worked partially, but for some test cases it did not worked. I trying to figure out what could be the problem. I will provide that test data by today evening which probably, you can better say what is going wrong.

Additionally, I was also trying to work out a simple solution for this. In my trial, I was just working out with your first solution. Strangely, our scheduler is not recognising the '?' in REFORMAT FIELDS=(F1:1,40,?). Please advise is there any other alternative for this?

Thanks & Regards,
Kitz
Kitz
 
Posts: 47
Joined: Thu Mar 17, 2011 3:46 am
Has thanked: 0 time
Been thanked: 1 time

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post