[sqr-users] Program running more than 12 hrs needs performance tuning

["KAVALAPARA Aravind Subramanian" <AKavalapara@covansys.com>]

Hi Everybody

I am executing a SQR pgm in Batch (Mainframes - OS390/DB2), it involves a 
complex query.

The total number of row fetched by that query is around 12 lak and hence the 
program executes for around 12 hrs, to reduce the runtime i have reduced the 
total number of row to be processed (i.e. around 5 laks), still complicating 
the query, but still even for 5 lake rows fetched the pgm executes dor 12 hrs.

The complex query when executed in Batch SPUFI, takes only 10 mins.

I dont know wht to do please help me out.

I have pasted the query below. Other than thsi query the pgm does some 
processing too, such as calculating of service days and hrs for each employee 
(i.e each row).

Begin-Procedure Main-Process      
Begin-Select                      
A.EFFDT                           
A.EFFSEQ                          
A.ELIG_CONFIG1                    
A.BEN_STATUS                      
!A.PAYGROUP                       
B.HIRE_DT                         
B.REHIRE_DT                       
B.TERMINATION_DT                  
B.GH_GSELIG_DT                    
B.GH_PRVELIG_CONFIG1              
C.EMPLID                          
C.NAME                            
C.BIRTHDATE                       
>From                                                          
    PS_JOB A,                                                 
    PS_EMPLOYMENT B,                                          
    PS_PERSONAL_DATA C                                        
Where                                                         
       C.EMPLID NOT IN(SELECT Z.EMPLID                        
            From                                              
              AHRSO75Q.PS_JOB Z                               
            Where                                             
                 Z.EFFDT = (SELECT MAX(Z2.EFFDT)              
                            From AHRSO75Q.PS_JOB Z2           
                            Where Z2.EMPLID = Z.EMPLID        
                              AND Z2.EFFDT <= $extract_dt     
                              and Z2.EMPL_RCD# = 0)           
                                                              
             and Z.EFFSEQ = (select max(Z2.EFFSEQ)            
                            From AHRSO75Q.PS_JOB Z2           
                            Where Z2.EMPLID = Z.EMPLID        
                              and Z2.EFFDT = Z.EFFDT          
                              and Z2.EMPL_RCD# = 0)                
                                                                   
             and Z.COMPANY = '234'                                 
             and Z.EMPL_RCD# = 0                                   
             AND Z.EMPL_STATUS IN('T','D','Q','R','U','V')         
             AND Z.EFFDT <= '2000-12-31')                          
  and A.Emplid = B.Emplid                                          
  and B.Emplid = C.Emplid                                          
                                                                   
  and A.EFFDT = (select max(A2.EFFDT)                              
                 From   PS_JOB A2                                  
                 Where  A2.EMPLID = A.EMPLID                       
                   and  A2.EFFDT <= $extract_dt                    
                   and  A2.EMPL_RCD# = 0)                          
                                                                   
  and A.EFFSEQ = (select max(A2.EFFSEQ)                            
                  From   PS_JOB A2                                 
                  Where  A2.EMPLID = A.EMPLID                      
                    and  A2.EFFDT  = A.EFFDT                       
                    and  A2.EMPL_RCD# = 0)       
                                                 
   and A.COMPANY = '234'                         
   and A.EMPL_RCD# = 0                           
Order By C.EMPLID                                
with ur                                          
End-Select      

Thanks in advance

bye
Aravind                                 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users