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

Need 30% of the sorted record from the input file
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Wed Aug 13, 2008 10:41 am
 Reply with quote
Hi,

example

My input file1 contains

Code:

KEY   
----         
001
002
003
004
005
006
007
008
009
010


My input file2 contains

Code:

KEY FIELD1 FIELD2
---- -------- --------
001 1          1
002 2          2
003 3          3
004 4          4
005 5          5
006 2          2
007 1          9
008 1          2
009 2          9
010 1          1
011 9          9
012 9          9



The Field1 should get multiply with field2 for the key matching field of both the file.

If field1*field2=field3,then field3 should be sorted in descending order.

I need the key in the output file for the top 30% of the above sorted record.

The output file should contain

Code:

005
009
004



No duplicate keys available....

Kindly provide me the JCL for the above requirements...

Attributes are
KEY - X(10)
Field1 and field2- s9(7)v(2) comp-3

Input file1 and file2 ,LRECL = 80 and RECFM=FB

The number of records in the input files is un-predictable,
it may contain more than 2 million records....
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Wed Aug 13, 2008 10:46 am
 Reply with quote
Only 10 keys from file1 matches with file2,so 30% of that 10 record is
3 record.Thats why output file contains only 3 record.

Some case the key in the file1 will not be available in file2 and has to be ignored.
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: Wed Aug 13, 2008 11:32 pm
 Reply with quote
Here's a DFSORT/ICETOOL job that will do what you asked for. Be sure to concatenate input file2 (with FIELD1 and FIELD2) before input file1.

Code:

//S1   EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG    DD  SYSOUT=*
//****>
//** Concatenate input file2 BEFORE input file1
//****>
//CON DD DSN=...  input file2 with FIELD1 and FIELD2 (FB/80)
//    DD DSN=...  input file1 (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//CT DD DSN=&&CT,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//C1 DD DSN=&&C1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
SELECT FROM(CON) TO(T1) ON(1,10,CH) FIRSTDUP USING(CTL1)
COPY FROM(CT) TO(C1) USING(CTL2)
SORT FROM(T1) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTFIL FNAMES=T1,
    OVERLAY=(81:11,5,PD,MUL,16,5,PD,TO=PD,LENGTH=12)
  OUTFIL FNAMES=CT,REMOVECC,NODETAIL,
    TRAILER1=(COUNT=(M11,LENGTH=8))
/*
//CTL2CNTL DD *
  INREC BUILD=(C'  OUTFIL ENDREC=',
    (1,8,ZD,MUL,+30),DIV,+100,TO=ZD,LENGTH=8,80:X)
/*
//CTL3CNTL DD *
  SORT FIELDS=(81,12,PD,D)
  OUTREC BUILD=(1,10,80:X)
//    DD DSN=*.C1,VOL=REF=*.C1,DISP=(OLD,PASS)
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Thu Aug 14, 2008 10:01 am
 Reply with quote
Will try with the above JCl..
Thanks a lot.......
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Thu Aug 14, 2008 2:50 pm
 Reply with quote
Hi,

I executed the same, but am getting MAXCC=16

JCL
Code:

//S1  EXEC  PGM=ICETOOL                                         
//TOOLMSG   DD  SYSOUT=*                                         
//DFSMSG    DD  SYSOUT=*                                         
//CON DD DSN=TPROD.TEST.INPUT.MTCHKEY2,DISP=SHR (File1)                 
//    DD DSN=TPROD.TEST.INPUT.MTCHKEY1,DISP=SHR (File2)                 
//*T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)     
//*CT DD DSN=&&CT,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)     
//*C1 DD DSN=&&C1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)     
//*OUT DD DSN=...  OUTPUT FILE (FB/80)                           
//T1     DD DISP=(,CATLG),                                       
//     SPACE=(CYL,(1000,200)),DCB=(LRECL=92,RECFM=FB,BLKSIZE=0),
//     UNIT=PROD,                                               
//       DSN=TPROD.TEST.OUTPUT.MTCHKEY1                         
//CT     DD DISP=(,CATLG),                                       
//     SPACE=(CYL,(1000,200)),DCB=(LRECL=80,RECFM=FB,BLKSIZE=0),
//     UNIT=PROD,                                               
//       DSN=TPROD.TEST.OUTPUT.MTCHKEY2                         
//C1     DD DISP=(,CATLG),                                       
//     SPACE=(CYL,(1000,200)),DCB=(LRECL=80,RECFM=FB,BLKSIZE=0),
//     UNIT=PROD,                                               
//       DSN=TPROD.TEST.OUTPUT.MTCHKEY3                         
//OUT    DD DISP=(,CATLG),                                     
//     SPACE=(CYL,(1000,200)),DCB=(LRECL=80,RECFM=FB,BLKSIZE=0),
//     UNIT=PROD,                                               
//       DSN=TPROD.TEST.OUTPUT.MTCHKEY4                         
//TOOLIN DD *                                                   
SELECT FROM(CON) TO(T1) ON(1,10,CH) FIRSTDUP USING(CTL1)       
COPY FROM(CT) TO(C1) USING(CTL2)                               
SORT FROM(T1) TO(OUT) USING(CTL3)                               
/*                                                             
//CTL1CNTL DD *                                                 
  OUTFIL FNAMES=T1,                                             
    OVERLAY=(81:11,5,PD,MUL,16,5,PD,TO=PD,LENGTH=12)           
  OUTFIL FNAMES=CT,REMOVECC,NODETAIL,                           
    TRAILER1=(COUNT=(M11,LENGTH=8))                             
/*                                                             
//CTL2CNTL DD *                                                 
   INREC BUILD=(C'  OUTFIL ENDREC=',                           
     (1,8,ZD,MUL,+30),DIV,+100,TO=ZD,LENGTH=8,80:X)
/*                                                 
//CTL3CNTL DD *                                   
  SORT FIELDS=(81,12,PD,D)                         
  OUTREC BUILD=(1,10,80:X)                         
//*   DD DSN=*.C1,VOL=REF=*.C1,DISP=(OLD,PASS)     
//   DD DSN=*.C1,VOL=REF=*.C1,DISP=SHR             


TPROD.TEST.INPUT.MTCHKEY1
Code:

0000000001
0000000002
0000000003
0000000004
0000000005
0000000006
0000000007
0000000008
0000000009
0000000010
0000000011
0000000012
0000000013
0000000014
0000000015
0000000016
0000000017
0000000018
0000000019
0000000020


TPROD.TEST.INPUT.MTCHKEY2
Code:

0000000001                        
0000000002                        
0000000003                        
0000000004                        
0000000005                        
0000000006      jæ      jæ           
0000000007                        
0000000008      jæ      jæ           
0000000019      jæ      jæ           
0000000020                        



My output is

TPROD.TEST.OUTPUT.MTCHKEY1
Code:

0000000001                
0000000002                
0000000003                
0000000004                
0000000005                
0000000006      jæ      jæ   
0000000007                
0000000008      jæ      jæ   
0000000019      jæ      jæ   
0000000020                


TPROD.TEST.OUTPUT.MTCHKEY2
Code:

00000010



TPROD.TEST.OUTPUT.MTCHKEY3
Code:

  OUTFIL ENDREC=0000000C 


TPROD.TEST.OUTPUT.MTCHKEY4 - empty


The ouput file TPROD.TEST.OUTPUT.MTCHKEY3
contains the invalid numeric value '0000000C'

Due to this am getting the error message like
Code:

SYNCSORT LICENSED FOR CPU SERIAL NUMBER 1367D, MODEL 2094 704             
CTL3CNTL :                                                               
  SORT FIELDS=(81,12,PD,D)                                               
  OUTREC BUILD=(1,10,80:X)                                               
  OUTFIL ENDREC=0000000C                                                 
                *                                                         
WER428I  CALLER-PROVIDED IDENTIFIER IS "0003"                             
WER271A  OUTFIL STATEMENT  : NUMERIC FIELD ERROR                         
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                             





At the time of multiplying by +30 and dividing by +100 ,we are getting the invalid numeric value which is mentioned in the error message

What to do in this case?
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Thu Aug 14, 2008 3:52 pm
 Reply with quote
Hi,

here we go again, Frank's code is for DFSORT not SYNCSORT.




Gerry
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Thu Aug 14, 2008 3:58 pm
 Reply with quote
Now ,what to do in this case?
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Thu Aug 14, 2008 4:01 pm
 Reply with quote
Is there any solution to get a JCL for the above requirements?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10873
Location: italy
PostPosted: Thu Aug 14, 2008 4:13 pm
 Reply with quote
Quote:
Now ,what to do in this case?
well...
to start with, say.... Sorry Frank for posting to the wrong forum
The WER... prefixed messages should have told You a long time ago that You were dealing with SYNCSORT rather than DFSORT
wait for somebody with SYNCSORT skills to save You from drowning
Quote:

Is there any solution to get a JCL for the above requirements?
if You are in a hurry, relying on a forum reply might not be the best solution


I will move he topic to the JCL forum
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Thu Aug 14, 2008 6:13 pm
 Reply with quote
Hello,

Can you try executing the same job with the following cards.

Code:
//TOOLIN DD *                                           
SELECT FROM(CON) TO(T1) ON(1,10,CH) FIRSTDUP USING(CTL1)
COPY FROM(CT) TO(C1) USING(CTL2)                       
SORT FROM(T1) TO(OUT) USING(CTL3)                       
//CTL1CNTL DD *                                         
  OUTFIL FNAMES=T1,                                     
    OVERLAY=(81:11,5,PD,MUL,16,5,PD,TO=PD,LENGTH=12)   
  OUTFIL FNAMES=CT,REMOVECC,NODETAIL,                   
    TRAILER1=(COUNT=(M11,LENGTH=8))                     
/*                                                     
//CTL2CNTL DD *                                         
  INREC BUILD=(C'  OUTFIL ENDREC=',                     
    (1,8,ZD,MUL,+30),DIV,+100,M11,LENGTH=8,80:X)       
/*                                                     
//CTL3CNTL DD *                                         
  SORT FIELDS=(81,12,PD,D)                             
  OUTREC FIELDS=(1,10)                                 
//    DD DSN=*.C1,VOL=REF=*.C1,DISP=(OLD,PASS)         


Thanks,
Arun
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 14, 2008 9:40 pm
 Reply with quote
The job I posted works fine with DFSORT which uses an F sign for TO=ZD. This results in

Code:

  OUTFIL ENDREC=00000003


Syncsort uses a C sign for TO=ZD. That results in:

Code:

  OUTFIL ENDREC=0000000C 


which doesn't work. arcvns suggested M11 which I believe will work. I suspect TO=ZDF will work too (DFSORT has supported TO=ZDF and TO=ZDC since Dec, 2004 so I imagine Syncsort has copied them by now) - it forces an F sign.
Back to top
View user's profile Send private message
ibmmainframesyntel

Active User


Joined: 26 Feb 2007
Posts: 126
Location: Chennai
PostPosted: Mon Aug 18, 2008 10:07 am
 Reply with quote
Hi,

Both are working fine...

With M11 as arcvns suggested

and with TO=ZDF as frank suggested

Is working fine..

I got the expected output...

Thank u so much...
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 -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 2
No new posts TRIM everything from input, output co... DFSORT/ICETOOL 1
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
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