Link to home
Create AccountLog in
Avatar of Enuda
Enuda

asked on

I am seeing a lot of "DIA8004C A cursor is still active, file token "". " in DB2DIAG.LOG. What does it mean or tell us about the DB2 system?

Hi all,
Looking at my log after a performance Test for this database, we found quite a lot of the message below and many others of this type:
          DIA8004C A cursor is still active, file token "".
RETCODE : ZRC=0x80040004=-2147221500=SQLD_NODROP "CURSORS ACTIVE ON DROP OBJECT"
FUNCTION: DB2 UDB, catalog services, sqlrl_drop_g_t_table, probe:20
APPHDL  : 0-1067               APPID: A4338AE1.G093.080305021307
INSTANCE: db2inst1             NODE : 000         DB   : FSNST201
PID     : 32427                TID  : 3086689984  PROC : db2agent (FSNST201) 0
2008-03-04-21.24.14.598307-300 I4467919G484       LEVEL: Warning

During this Test periods, we also encountered SQL1040N  The maximum number of applications is already connected to the database.  SQLSTATE=57030.

I was wondering if from the twwo errors, one triggers the other. For example, the DIA8004C A cursor is still active, file token "" warning may have led to the maqx connection error.?

Finally, what criteria should I use to set my maxappl parameter? I estimated at peak time the number of users to be 2000. Is maxappl=500 too much orr too little and why?

Thanks


Avatar of momi_sabag
momi_sabag
Flag of United States of America image

regarding your maxappl question -
how many users will be using the database concurrently ?
for example, if it's a web application, 2000 online users can sometimes mean only 50 concurrent users
does each user need a dedicated connection ?
can you use connection pooling ?
what kind of server are you using ?

each concurrent application you allow will consume resources from your server so this parameter should be configured with care, and once you set it, you need to try to increase / decrease it and see how it affects your system

regarding the DIA8004C message,
it's weird becuase it does not show anywhere in the documentation
but since it's a warning level error, i guess you can live with it
anyways, i don't think it is connected to the SQL1040N  problem

what did your performance test included ?
did you drop some tables ?
it seems that the DIA8004C message has something to do with dropping tables
Avatar of Enuda
Enuda

ASKER

momi_sabag,

Ok, thanks for the excellent post and some food for thought for me. I have followup questions though.
SQL1040N can be caused by idle threads just lying in the database from previous use and not released back to connection pool, etc. One of the recommended  solution is to Use the built-in DB2 governor function to deal with this. The governor works like a daemon: Wakes at t your interval, checks idle connections since your time specified duration and optionally forces the connections off.

Do you know of how this can be accomplished?
How can I go about proving to myself that I have connected iddle threads in the database?

Of course the immediate solution is to increase the maxappls in the database configuration file for that database, ie: db2 update db cfg for <datababase name> using maxappls <new value> IMMEDIATE.
Before I go off setting maxappls, I need to know how to answer the two questions above...

Thanks


you can go to this link
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/admin/c0004985.htm
and from this link you can find instructions on how to configure the governor, and how to start / stop it

regarding your question on idle threads, what do you mean by idle threads ?
do you use connection pooling ?
or you just have theards that commit / rollback thier transactions but do not disconnect from the database ?

you can look at the idle_agents monitor elemnt to see how many threads are idle
Avatar of Enuda

ASKER


Yes, we do use connection pooling...I

Ok, let me ask it this way...Can threads be idle (ie not in use) but still be connected to the database?
Why would that happen?
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Enuda

ASKER

As always, you have been a very educating influence...informative