Thanks for the information. Now I have another question. I implemented
some changes and the initial response time for the first call of the stored
procedure was much better. However, the procedure hadn't been called since
last Friday and the response time was much slower again today. I there
some sort of timeout limit associated with the DARI processes that would
cause them to be slow after not being called for several days?
Thanks,
Tim
----- Forwarded by Tim Traxson/Corporate/JBHunt on 08/19/2003 08:50 AM
-----
"Sinha, Abhijit"
<[EMAIL PROTECTED] To: "'[EMAIL PROTECTED]'"
<[EMAIL PROTECTED]>, [EMAIL PROTECTED]
.com> cc:
Subject: RE: [DB2EUG] Stored Procedure
Cache?
08/13/2003 12:08
PM
Tim,
There are a few DBM cfg parameters which affect the performance of java
stored procs. These are:
Keep DARI process (KEEPDARI) = YES
Max number of DARI processes (MAXDARI) = MAX_COORDAGENTS
Initialize DARI process with JVM (INITDARI_JVM) = NO
Initial number of fenced DARI process (NUM_INITDARIS) = 10
You can read up on them in Admin - Performance guide.
When a Java SP is invoked the first time, most of the time is spent in
initializing the JVM and spawning a JVM thread (otherwise known as the db2
DARI process) to service the SP call.
prstat | grep <instance-owner or fenced-id>
This command will show the db2dari processes running currently.
Also, ACTIVATE DATABASE command might help.
Abhijit
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 13, 2003 12:47 PM
To: [EMAIL PROTECTED]
Subject: [DB2EUG] Stored Procedure Cache?
Hello List,
We have a some java stored procedures that are defined on a DB2 UDB version
7.2 fixpack 9 machine. When the stored procedure is invoked for the first
time it takes around 20 seconds to return results. When it is executed
again with the same variables, it returns quickly. Now most people would
say that this is a bufferpool problem, but I don't believe that it is.
When a program is invoked that performs the same SQL, in returns fast
everytime no matter what parameters are being used in the SQL. It almost
seems like there is some type of stored procedure cache that is not sized
correctly but I am not familiar with any setting like this. Can someone
steer me in some direction on this? Does a cache of this sort exist and if
so what is it?
Thanks,
Tim Traxson
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
-
::: When replying to the list, please use 'Reply-All' and make sure
::: a copy goes to the list ([EMAIL PROTECTED]).
*** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
*** For more information, check http://www.db2eug.uni.cc
