473,698 Members | 2,751 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 Performance problems after REORG

1 New Member
he brief explanation of the problem is,

After DB2 maintenance window performance has gone down, it takes some hours to retrieve the data.

We have provided some table spaces to Reorg/Runstat during the maintenance window .

After REORG some of the tablespaces, the performance is very slow .This is really strange because no code changes has been involved and also nothing was changed, it's just a Reorg of some tablespaces.

I think this problem might be with the Index space cluster ratio, because some of the tables cluster ratio is very low and also it is accessing the wrong cluster indexes.

Please let me know your comments

Jun 9 '06 #1
3 8560
1 New Member

I have some experience about this. Do you try db2 performance expert? it is very useful, it monitors, analyzes and tunes the performance of DB2 and DB2 applications, can help further optimize DB2 performance and maximize DBA productivity.

One book "DB2 High Performance Design and Tuning" should help you, you can search and find it.

software reviews
Mar 19 '07 #2
24 New Member
You may want to look into SYSPACKDEP and determine which packages are using any of the tables in the tablespaces you have re-org'd.

Are you running a rebind after the RE-ORG and RUNSTATS?

If not then DB2 may not be using the best path to access the data after the RE-ORG.

Also, did you check the runstats before with the runstasts after the "RE-ORG/RUNSTAT" activity to understand what changed?

If you can isolate the affected packages, from syspackdep and catalogue value changes applied by the RUNSTATs you may be able to isolate this down to some specific SQL. If you can get that far then you can run an EXPLAIN of the SQL with the new RUNSTATs catalogue value and check this against the current access path being used by the SQL in the pacakge. This will help to determine if a REBIND after the RE-ORG/RUNSTAT activity will resolve the problem.

The other possibility is that the organisation of the data on the table has "out grown" the current index organisation or primary key setup e.g. you have a "hotspot" in your data so any access into this part of your key range results in a performance isssue. This will not show up on an EXPLAIN as the SQL access path could be fine. You need to look at the column cardinately for the columns in the indexes accessed by the SQL. If combinations of columns in an index has a low cardinately then a large number of rows will need to be accessed. If the SQL has other predicates that act on columns not in the index pages then DB2 will retrieve the data page for each of these rows to resolve the predicates - this can result in a lot of I/O in the back ground for a piece of SQL that looks really good in the EXPLAIN. This would require you to review your data structure, column contents and index structure to resolve this kind of problem.


Mar 19 '07 #3
24 New Member
Another cause I have come across is when the NLEVELS (think this is the row name) on for the index exceeds 3 or 4. This values indicates the maximum (I think) number of index levels that DB2 has to navigate to get to a leaf page - which contains the the actual index data. If I remember correctly 3 is the most people expect to see on a well organised index, rarely 4. If the nlevels is more than this it can cause performance problems.

This is another value you should check out before and after the RE-ORG for any indexes affected by the activity.

When this occured for me I think it was related to low cardinately on the key and also a high free space/free page combination.

Something else to consider is the current growth rate and maturity of the data. If the database is "young" the growth rate may still be high. If the maturity of the data is low in relation to the diversity of values in each field then this can affect how well distributed the data is and how quickly an index can become declustered.

When re-org a database with these characteristic you need to consider do rebinds and then executing an analysis of the new access paths against the old to attempt to identify any that have degraded i.e. dropped to table space or index space scans.

Hope this helps,

Mar 19 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: AC Slater | last post by:
Hi All, Out of nowhere my udb system (v8) performance has went terrible. Its gotten about 10x worse, (some tests that used to take 2 seconds to run now take 20)... I'm not sure what happened. I did reorg/runstat/rebind on everything, no luck... I'm not sure what to do next...? Any recommendations on something to try to start narrowing down the possible problem. Things I've tried:
by: Mario.Reif | last post by:
We have developed an application which was running under DB2 v7.2.5 quite well for some years. Four weeks ago we installed DB2 v8.1.5 Express Fixpak 5 on a new Server (hardware is nearly the same as on the DB2 v7.2.5 machine). The new Server runs on Windows 2003. Last week we installed another server with the same hardware with DB2 8.1.5 Workgroup Server Fixpak 5 under Windows 2000 Server. Both servers running DB2 v8 databases are about...
by: Rudolf Bargholz | last post by:
Hi, I have a ralatively simple SQL: select FK from TABLE where upper(A) like 'B%' and upper(C) like 'D%' We have DB2 UDB v7.1 FP 12 installed on Linux and on Windows 2003 On Linux using optimization level 5 as well as 9 and 0 the SQL uses 3'100'000'000 timerons !
by: Lara | last post by:
Hello freaks, we have many problems with our online reorg and no idea how to resolve it. We had to do an online reorg beacause of 24 h online business. We start the reorg-statements (table by table) in a loop with the following statement: reorg table xxx.yyy inplace allow write access.
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when inserting/deleting rows from a large table. My scenario: Table (lets call it FACT1) with 1000 million rows distributed on 12
by: GB | last post by:
Hi All, I would like to optimize my reorgs. Here are several ways to shorten reorgs or keep data online during reorgs: - db2 reorg has some shrlevel option: reference or change - db2 v7 has an option for reorganizing everything but not the blob's - third party tools like bmc reorg plus - others ?
by: 73blazer | last post by:
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8). For the most part, things are faster, but there is one query that is much much slower, and it is a query that is used all the time. select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list where S_PART_NUMBER like '%KJS%' The widlcard before and after seems to be hosing it, but for this particular piece of the application, this type of query is neccessary.
by: bernhard.willems | last post by:
We are using DB2 UDB version 8.1 fp7 & fp9 on AIX 5.2. On our databases we do massive inserts and deletes (millions of rows). The performance is dramatically reduced because of pseudo deleted keys. In a 10 minutes it goes from 18000 transaction per minute to 9000 and lower. When an index reorg with CLEANUP ONLY PAGES is executed, the number of transactions climbs again. But a few minutes later it drops again to 9000. We have to run the...
by: datapro01 | last post by:
Running DB2 8.2.6 on AIX 5.3 I recently took on an assignment supporting a DB2 database which supports a Siebel application. Siebel version is 7.5.3 I have a reorg script that carefully follows Siebel guidance on reorgs and have had great success with it at two other locations. Quick background; entire database had NEVER been reorged in 6 years
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.