Hello,

We are using db2 v8.1 FP17 on AIX 5.1.

We have db performance issue, so i set the event monitor for statements and tried executing a simple select count(*) query.

Even though the query was executed only once, but in log (o/p of db2evmon) it shows 4 entries for the same query!
The value for "Operation" feild in log is "Prepare", "Open", "Describe" snd "Close".

Is it a normal behaviour? or somthing is going wrong?

Also The "execution time" for last entry 18 sec, I think it is more.[note: The count of records in this table is 5336191]


Please help me if you have any information on this.


Thanks for your time

This is normal. Queries executed from CLP (dynamic) will show prepare/open/describe/close.

Thank You Bella.

One more question from me... How can i go ahead with performace issue? i tried getting execution time for queries. it is around 60-70 Seconds on avg. i don't know if it is normal for these queries.

How can i determine if i requre sql tunning or bufferpool tunning or cfg tunning or something else?

Can you suggest any important weblink on this?

I think no one can answer your question if 60-70 sec is normal for these queries on this forum... the answer would be "it depends".

I find a lot of helpful performance related info on this DB2 forum. Some people (Marcus_A for example) have provided really good info on how to tune bufferpools/queries/etc so search the forum.

I personally like the following performance related docs:
IBM Redbooks | DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI
ftp://ftp.software.ibm.com/ps/produc.../db2d3e951.pdf


You can also search google for optimizer related articles written by Samir Kapoor from IBM.

do a reorg and runstats on the tables in the queries,
what is size of the table if its huge, check with explain plan
whether index is been used or not, create proper indexes
and check
regds
Paul