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
 

 

Need 30% of the sorted record from the input file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: Need 30% of the sorted record from the input file
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    Post subject:
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 Moderator


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

PostPosted: Wed Aug 13, 2008 11:32 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1703
Location: Australia

PostPosted: Thu Aug 14, 2008 3:52 pm    Post subject:
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    Post subject: Reply to: Need 30% of the sorted record from the input file
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Thu Aug 14, 2008 4:13 pm    Post subject: Reply to: Need 30% of the sorted record from the input file
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: 2180
Location: @my desk

PostPosted: Thu Aug 14, 2008 6:13 pm    Post subject:
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 Moderator


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

PostPosted: Thu Aug 14, 2008 9:40 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 31 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm


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