[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
[sqr-users] Program running more than 12 hrs needs performanc e tuning
Hi Steve
Sorry I've pasted the wrong select statement it should read as -B10000 instead
of -B99.
The below error is the correct one. expecting ur reply and Thanks in advance.
--------------------------------------------------------------------------------------
Begin-Procedure Main-Process
Begin-Select -B10000
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
...................................
ERROR
Error on line 178:
(SQR 3721) Bad param found on 'BEGIN-SELECT' line; Format is:
BEGIN-SELECT ÝDISTINCT¨ Ý-Cnn¨ Ý-Bnn¨ ÝLOOPS=nn¨
ÝON-ERROR=procedureÝ(arg1Ý,argi¨...)¨¨
Begin-Select -B10000
Errors were found in the program file.
SQR: Program Aborting.
------------------------------------------------------------------------------------------
bye
Aravind
-----Original Message-----
From: KAVALAPARA Aravind Subramanian
Sent: Sunday, August 10, 2003 7:19 PM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] Program running more than 12 hrs needs
performanc e tuning - clarification regarding lak
Hi Steve
Thank u for ur response, i have clubbed two queries thats why it was clumsy, i
have implemented ur query and the run time has been reduced by 2 hrs, thank u
very much
Reagrding the buffer size i have given the following and i am getting an error
like this cud u help me out.
Begin-Procedure Main-Process
Begin-Select -B10000
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
...................................
ERROR
Error on line 178:
(SQR 3721) Bad param found on 'BEGIN-SELECT' line; Format is:
BEGIN-SELECT ÝDISTINCT¨ Ý-Cnn¨ Ý-Bnn¨ ÝLOOPS=nn¨
ÝON-ERROR=procedureÝ(arg1Ý,argi¨...)¨¨
Begin-Select -B99
Errors were found in the program file.
SQR: Program Aborting.
Thanks in Advance
Aravind
-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Alexander, Steve
Sent: Friday, August 08, 2003 9:32 PM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] Program running more than 12 hrs needs
performanc e tuning - clarification regarding lak
Here's what I think you're trying to do:
where 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
and (A.EFFDT > '2000-12-31' or A.EMPL_STATUS in ('A','L','P'))
and A.EMPLID = B.EMPLID
and B.EMPLID = C.EMPLID
Order By A.EMPLID
-----Original Message-----
From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us]
Sent: Friday, August 08, 2003 8:50 AM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] Program running more than 12 hrs needs
performanc e tuning - clarification regarding lak
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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users