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

What does it mean or tell us about my DB2 system?


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

I was wondering if from the two errors, one triggers the other. For example, the DIA8004C A cursor is still active, file token "" warning may have led to the max 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 or too little and why? Any trick of the trade to prove this?

Thanks

You do not state your DB2 version or OS.

For the Cursor is still open, it is telling you that a cursor for a select was not closed properly. There can be various reasons for this. I would venture to say that it is an application programming issue.

You should set maxappls to "automatic". This will prevent the error you are getting.

Andy

Sorry about that ARWinner...my DB2 is V8.2 (FP14), OS is a mix of Linux, and Windows.

Now, you recommend setting maxappls=automatic to prevent my errors. But in some quarters, they caution against doing precisely what you have recommended because that will take away DBA control, allow applications to pile up and often leads to database crashes.

What do you say to that?

Again thanks

I would say that being a DBA is performing a balancing act. The users need access to the data. This is always the first priority. Setting an arbitrary limit to how many can actually have access to it at any one time seems to defeat this ultimate priority. But you also must make sure that the DB is up and running so that everyone has access. The system needs to be looked at as a whole. Not in a piecemeal fashion. I hate setting hard limits, because sooner or later, they will be reached, and then you are denying service.

These days, the DBA's job is no longer to just maintain the database. They need to work hand in hand with the developers to make sure that not only is the database capable, but the application is also capable. It is as much the DBA's job as well as the developers' job to prevent run-away applications.

Andy

Where exactly is someone speaking against maxappls=automatic and what are those arguments?

Knut, Thank you for chipping in...I always enjoy reading from you...

You see, when I have a problem I research solutions and try to find the reasoning behind the solution. Such was the case when I had max current connection issue during a performance test exercise.
Yes, I found recommendations to increase maxappls parameter to some calculated or non-arbitrary number, and one recommendation not to set maxappls=automatic because (a) it takes away control from DBA monitoring, etc and (b) could lead to applications piling up on and risking DB2 crashes.

My goal here was to get comments and opinions if indeed maxappls=automatic is a risk to avoid completely or not...

So, I ask, what's your take on maxappls=automatic vs maxappls=500 for example.

While we are at it Knut, could you please give your technical opinion on whether one should really keep watch on connections or to deploy the db2govd to monitor connections and threads in today's DB2 UDB products?

Of all the DB2 UDB processes, could you name the 10 most important to watch?

Thanks

Argument (b) is BS. If DB2 would crash, it is a DB2 problem that must be fixed in DB2. Period. So you should call IBM in such cases (with such cases = all DB2 crashes). It may be acceptable for some applications that they crash more or less ofter, it should never be acceptable that a crash in DB2 is tolerated.

I would see (a) from a different angle: If you want to burden the DBA with the work to fine-tune the system, then do not set it to "automatic".

Personally, I would stick with automatic parameters wherever possible, until I hit a problem. Then there are two ways to deal with that: (a) either moving back to manual tuning, or (b) talk to IBM support to get the problem fixed. In the long run, (b) should turn out to be the cheaper approach. In the short run (a) may give you a quicker fix.

p.s: I don't know DB2 Governor, so I can't comment on your other question.

Thank you Knut, as always you are a jewel in our business...I think I agree with your argument and both ARWinner and your recommendation.

Thanks