wel..i am Ramchandra Raikar basically from India (Goa) working as a DBA in bahrain.
April 6, 2009 at 5:39 pm
from content of your site, I thing you may be the person, who can help me with some advice with measuring of the udb performance.
We are moving some applications from DB2 Z/OS to UDB V9.1 (9.5) on Windows. I have mostly experience with DB2 Z/OS performance stats collection.
Running some programs with static SQL on LUW Windows server takes several times longer than on DB2 Z/OS. For example program running 15 min on the mainframe runs 3 hours on Windows. Analyzing explains of the SQL from both platforms does not show any big discrepancies or problems.
I am trying to find “bad” sql statements in the package, identify which are taking most of the elapsed time in UDB using event monitor statements report.
I have some problems using these stats. I am not able to get db2 elapsed time – not total elapsed time (that includes also application time).
Elapsed and CPU time for OPEN statement is small compared to the CLOSE stats. On DB2 Z/OS OPEN metrics represent most of ” db2 work” like sorting … up to the 1st fetch, when the result set is ready. In UDB statistics for OPEN look like not containing this activity.
CLOSE elapsed time contains time from SQL start (OPEN) to end of sql. Therefore also any time spend on network and application.
Could you give me some advice, how can I get db elapsed time – not network and application time. I need this information to give me an idea, where program spends time in DB2.
Yours Lubomir Kuruc
April 7, 2009 at 1:36 pm
1.First of all i would suggest you to do reorg and runstat on all tables / indexes and then rebind all the packages.
2.To find which sql is taking more CPU/time create a event monitor for statements with write to table option, where in all the event monitor details you will have in the table and its very easy to get the details.
3.Check if you have good bufferpool hit ratio and have enough memory on the system(use db2pd -db -buff)
4.check if you have temporay tablespace with proper pages sizes.
5.identity the sql which takes more time and get the explain plans for them. you may have to create indexes based on explain plain
Fill in your details below or click an icon to log in:
You are commenting using your WordPress.com account. ( Log Out / Change )
You are commenting using your Twitter account. ( Log Out / Change )
You are commenting using your Facebook account. ( Log Out / Change )
You are commenting using your Google+ account. ( Log Out / Change )
Connecting to %s
Notify me of new comments via email.
Create a free website or blog at WordPress.com.