JCL- Filter Large data for small Number based on Condition

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 12:31 pm

I have a dataset "X" which has 1000 Citys Dasets. I need to read the data set for only 50 City data. I have the list of those 50 City. How do I filter at DD statement while reading the data in JCL.

Post by **Krishna** » Tue Oct 14, 2008 12:43 pm

Your dataset "X" contains - records or datasets ?

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 1:19 pm

It contains Records.

Post by **Krishna** » Tue Oct 14, 2008 2:47 pm

we can use either ICETOOL OR EASYTRIEVE for this.
Let us know. which method source you want.

Post by **Krishna** » Tue Oct 14, 2008 3:16 pm

Try following ICE tool example. This is a sample JCL.

Below JCL matches between two files from 6th position ( lengh of 6 )
write the output from IN1.

Code: Select all

//icetool  JOB &#40;DRALD,TEST&#41;,'SORT JCL',CLASS=S,REGION=0M,         
//          MSGCLASS=T,NOTIFY=&SYSUID                              
//S1    EXEC  PGM=SYNCTOOL                                         
//TOOLMSG   DD  SYSOUT=*                                           
//DFSMSG    DD  SYSOUT=*                                           
//IN1 DD *                                                         
1111 AAAAAA  11111111                                              
1112 AAAAAA  22222222                                              
1113 AAAAAA  33333333                                              
1114 AAAAAA  44444444                                              
1111 BBBBBB  11111111                                              
1112 BBBBBB  22222222                                              
1113 BBBBBB  33333333                                              
1114 BBBBBB  44444444                                              
/*                                                                 
//IN2 DD *                                                         
1112 AAAAAA 1111111                                                
1112 CCCCCC 1111111                                                
/*                                                                  
//C1 DD DSN=&&C1,UNIT=SYSDA,SPACE=&#40;CYL,&#40;5,5&#41;&#41;,DISP=&#40;,PASS&#41;          
//OUT DD SYSOUT=*                                                   
//TOOLIN DD *                                                       
* GENERATE AN INCLUDE CONDITION FROM EACH RECORD IN IN2             
  COPY FROM&#40;IN2&#41; TO&#40;C1&#41; USING&#40;CTL1&#41;                                 
* USE THE GENERATED INCLUDE CONDITIONS TO SELECT RECORDS            
* FROM IN1                                                          
  COPY FROM&#40;IN1&#41; TO&#40;OUT&#41; USING&#40;CTL2&#41;                                
/*                                                                  
//CTL1CNTL DD *                                                     
  OUTREC FIELDS=&#40;C'  6,6,EQ,C''',6,6,C''',OR,',80&#58;X&#41;                
/*                                                                  
//CTL2CNTL DD *                                                     
  OPTION COPY                                                       
  INCLUDE FORMAT=CH,COND=&#40;1,1,NE,1,1,OR,  NEVER TRUE                
/*                                                                  
//  DD DSN=*.C1,VOL=REF=*.C1,DISP=&#40;OLD,PASS&#41;                        
//  DD *                                
  1,1,NE,1,1&#41;     NEVER TRUE            
/*

O/p-

1111 AAAAAA 11111111
1112 AAAAAA 22222222
1113 AAAAAA 33333333
1114 AAAAAA 44444444

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 3:17 pm

I have limited or Now knowledge of either of the Procedures.

So any of the best method to Filter to read will help.

IF woudl be Great if you cna share with the Syntax in DD statement.

Post by **Krishna** » Tue Oct 14, 2008 3:24 pm

I am not sure... whether i understood your requirement correctly or not.
As per my understanding - your requirement is as below.

Input file - 'X' databaset - contains 1000 records.

you want to read records from this file, those records which matches the 50 city list that you have.

If above requirement is correct, you need to use, SORT, ICETOOL, EASYTRIEVE to create filted file.

If, my understanding of requirement is wrong, please let me know.

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 4:04 pm

Your UNderstandign is correct.
Please let me know the Syntax if possible. on how to use.

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 4:06 pm

Additon information is

Input file - 'X' databaset - contains 1000 records

the list of 50 city is another dataset "Y".

I need ony those record form "X" where "X" and "Y" matches by City variable

Post by **Krishna** » Tue Oct 14, 2008 4:14 pm

ICETOOL example given above will work.

If you are not comfortable with that, use SORT.

Reference - http://www.geocities.com/srcsinc/drona/ ... .sort.html

SAMPLE SORT CARD

Code: Select all

//STEP10  EXEC PGM=SORT                 
//SYSOUT    DD SYSOUT=*     
//SORTIN    DD DSN=...,DISP=SHR   Input if SORT request           
//SORTOUT   DD DSN=...                  Output for SORT request 
//SYSIN    DD * 
    SORT FIELDS=COPY 
     INCLUDE COND=&#40;1,10,CH,EQ,C'NEWYORK   ',OR,
                                1,10,CH,EQ,C'JERSEYCITY'   &#41; 
/*

Note : You dont require file "Y" here. you need to specifiy the city names directly in the INCLUDE statement.

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 4:21 pm

Hi,
Unfortunately we cannot list all those 50 Right.. it will be huge... INCLUDE COND= command.

It was jsut an example of 1000 (X) records and 50 (Y) records.

If it si in millions in X and need to filer for 100000's then we cannot list all of them in synatx 'INCLUDE COND=".

is there a different method or My understaning of the above syntax is incorrect?

Please let meknow,
Thanks.

Post by **Krishna** » Tue Oct 14, 2008 4:29 pm

that is the reason, i requested you to use ICETOOL example provided above. ( post 5). In that JCL, Use your 'X' file in IN1 & 'Y' file in IN2.

Change the following

Code: Select all

//CTL1CNTL DD *                                                      
  OUTREC FIELDS=&#40;C'  6,6,EQ,C''',6,6,C''',OR,',80&#58;X&#41;

6,6,EQ <-- starting position of city field in file X , Length of the field , EQ
6,6,C <--- starting positoin of city field in file Y , Length of the field ,C

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 4:33 pm

Hi,

Also it will be greta if you can tell me if I need to Filter "X" not jsut by one variable but by 2 variables ( City and street) of "Y" data set. then How Can i perform.
data X is
City Street HH age
NY abc 3 45
NY abc 1 18
PY abc 4 60
XY abc 5 30
NY abc 7 35
NY xyz 9 50
NY xyz 3 22
...
...
( million of obesevraion like this)

Data Y is

City Street
NY abc
NY xyz

then My Final data after filtering should be ( if a and b ) based on City and street will be
City Street HH age
NY abc 3 45
NY abc 1 18
NY abc 7 35
NY xyz 9 50
NY xyz 3 22

Please help me out on this.

One more condition is : As my dataset is too large I cannot list like above . It just need to read in DD statement. HSould not be listed liek above.

mgypraveen · Post by **mgypraveen** » Tue Oct 14, 2008 4:49 pm

Sir any Help on this!!!
Please ...

Thanks!!
Hope i ma not taking too much of your time..by extendignmy question.

Post by **Krishna** » Tue Oct 14, 2008 5:53 pm

Here is easytrieve program to achiece this... make sure the input files are sorted on city&street before excuting this step.

Code: Select all


//EZTRIEVE EXEC PGM=EZTPA00                                     
//STEPLIB  DD  DISP=SHR,                                        
//             DSN=SYS3.EZTRIEVE.V63.xxxx.CAILIB                
//SYSPRINT DD  SYSOUT=*                                         
//SYSOUT   DD  SYSOUT=*                                         
//SYSUDUMP DD  SYSOUT=*                                         
//FILEX    DD  *                                                
NY ABC 12345                                                    
NY ABC 22222                                                    
NY ABC 55555                                                    
XY ABC 33333                                                    
XY ABC 44444                                                    
/*                                                              
//FILEY    DD  *                                                
NY ABC                                                          
/*                                                              
//OUTFILE  DD DSN=DEV.XXXXX.KRISHNA.TEST7,                   
//             DISP=&#40;NEW,CATLG,DELETE&#41;,LRECL=80,                
//             SPACE=&#40;TRK,&#40;1,1&#41;,RLSE&#41;                           
//SYSIN    DD  *                                                
PARM DEBUG&#40;STATE FLOW&#41; ABEXIT&#40;NO&#41;                                
                                                                 
FILE FILEX  FB&#40;80 800&#41;                                           
WS-RECX     1  80   A                                            
 WS-CITY-X        1 2  N                                         
 WS-STREET-X      4 3  N                                         
                                                                 
FILE FILEY  FB&#40;80 800&#41;                                           
WS-RECY      1  80   A                                           
 WS-CITY-Y        1 2  N                                         
 WS-STREET-Y      4 3  N                                         
                                                                 
FILE OUTFILE FB&#40;80 800&#41;                                          
WS-REC  1  80   A                                                
                                                                 
JOB INPUT &#40;FILEY KEY&#40;WS-CITY-Y , WS-STREET-Y&#41; +                  
           FILEX KEY&#40;WS-CITY-X , WS-STREET-X&#41; &#41;                  
                                                                 
    IF MATCHED                                                   
       WS-REC = WS-RECX                                          
       PUT OUTFILE                                               
    END-IF

mainframegurukul.com

JCL- Filter Large data for small Number based on Condition

JCL- Filter Large data for small Number based on Condition

ICETOOL Match

SORT INCLUDE COND INCLUDING RECORDS FROM JCL

EASYTRIEVE MATCHING

FREE TUTORIALS