RE: [sqr-users] Program running more than 12 hrs needs performance tuning - clarification regarding lak

["Alexander, Steve" <Steve.Alexander@ci.sj.ca.us>]

Aravind, could you explain what you're trying to do with this selection?  It
looks like you want all the employees from a particular company as long as
they didn't terminate by 2001.  If so, there are ways to do it that are
easier and faster than "C.EMPLID NOT IN (SELECT ...".

-----Original Message-----
From: KAVALAPARA Aravind Subramanian [mailto:AKavalapara@covansys.com]
Sent: Friday, August 08, 2003 7:43 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Program running more than 12 hrs needs performance
tuning - clarification regarding lak


Hi Clark 

Thanks for ur response Clark

Lak is nothing but 1/10th of a million, i.e 12 laks(spelled as Lakh) is 1.2
million Ok is that fine. It is used in India.

Regarding the query, i cannot ask the dba for any explain plan to be
executed, cos i'm working in offshore india and requesting the dba for any
work need to go thru so many processes, any way i'm trying that buffer
concept of -B1000, hope it works fine, i'll let u know all.

Thanks everybody for ur response.

bye
Aravind



-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of the dragon
Sent: Friday, August 08, 2003 5:13 PM
To: sqr-users@sqrug.org
Cc: KAVALAPARA Aravind Subramanian
Subject: Re: [sqr-users] Program running more than 12 hrs needs
performance tuning


Aravind,

First, what is a lak????

Change these lines of code from this
where  C.EMPLID NOT IN(SELECT Z.EMPLID
             From
               AHRSO75Q.PS_JOB Z
             Where (etc)
                  to this
where   not exists (SELECT 1
             From AHRSO75Q.PS_JOB Z
             Where c.empid = z.emplid
              and (etc)

have your dba perform an explain plan on this query, and make sure your 
environment has enough resources.  Usually batch jobs are at the bottom of 
the heah in mainframe land.

clark 'the dragon' willis
dragon enterprises, consulting services

PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your

services for 40 hours a week, not purchasing your soul.  Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent.  Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)
----Original Message Follows----

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

End-Select

Thanks in advance

bye
Aravind

_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
http://join.msn.com/?page=features/virus




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

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