Hello,
I am using DB2 UDB v.8.2, with TPC-H 1GB database.
When I use the design advisor, and set a disk space limit of 100 MB,
the design advisor does not recommend anything.
db2advis -l 100 -d TPCH -i c:\thequeries.s ql -k OFF -m I -t 10
With another database, I did the same, but with a space constriant of
45 MB, again the advisor did not recommend indexes. Yet, I was able to
create 14 single-column indexes using the 45 MB myself, and improved by
the workload by %10.
Can some one help me here? its just not logical.
Thanks 3 1667
"UnixSlaxer " <un********@hot mail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. . Hello,
I am using DB2 UDB v.8.2, with TPC-H 1GB database.
When I use the design advisor, and set a disk space limit of 100 MB, the design advisor does not recommend anything.
db2advis -l 100 -d TPCH -i c:\thequeries.s ql -k OFF -m I -t 10
With another database, I did the same, but with a space constriant of 45 MB, again the advisor did not recommend indexes. Yet, I was able to create 14 single-column indexes using the 45 MB myself, and improved by the workload by %10.
Can some one help me here? its just not logical.
Thanks
The DB2 design advisor is for sissies. Any decent DBA can do better by
themselves.
If you want to see what indexes IBM used when they ran the DB2 benchmarks,
look a the "Full Disclosure Report" for the benchmark test on the www.tpc.org site. In some cases IBM used "organize by" (which is really
MDC), but if you just use the same columns for regular indexes it should
work about the same.
"UnixSlaxer " <un********@hot mail.com> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.com.. . Hello,
I am using DB2 UDB v.8.2, with TPC-H 1GB database.
When I use the design advisor, and set a disk space limit of 100 MB, the design advisor does not recommend anything.
db2advis -l 100 -d TPCH -i c:\thequeries.s ql -k OFF -m I -t 10
With another database, I did the same, but with a space constriant of 45 MB, again the advisor did not recommend indexes. Yet, I was able to create 14 single-column indexes using the 45 MB myself, and improved by the workload by %10.
Can some one help me here? its just not logical.
Thanks
Tuning is an art. Why would you want to use robot? in****@gmx.ch wrote: Tuning is an art. Why would you want to use robot?
Where i can learn this? Any tuts available?
Your nearest IBM Education Center also gives yearly Performance Tuning
courses (at least, the EC in Amsterdam does :p).
The course is nice, you forget most the day after, but you get some
Learning Services Student notebooks, which make excellent reference
guides. (+a nice certificate you can hang in your office :S)
-R- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: ma740988 |
last post by:
I'm hoping my post here doesn't fall into the 'hard to say' category,
nonetheless I've been advised that multiple uses of accessor/mutator
(get/set) member functions can be viewed as a 'design flaw'. In that
regard I'm trying to create an 'example' class that's an alternative
to the accessor/mutator approach. To further describe the problem
consider the class BAR (below) which has a member data in_use that FOO
needs visibility into. ...
|
by: Jean-Marc Blaise |
last post by:
Dear all,
It would be nice if the Design advisor was not outputing all "unused
indexes" of the whole database (10000 tables at least), when the workload is
only using 4 tables.
Why not limiting these "unused indexes" to the 4 tables in the workload ?
Best regards,
|
by: Peter Arrenbrecht |
last post by:
Does anyone have experience with running the DB2 Design Advisor on huge
workloads? I have a workload for a single day for some 132'000
statements in a file. Will the advisor handle this or should I take a
sample?
Thanks,
Peter Arrenbrecht
Opus Software AG
|
by: datapro01 |
last post by:
Running DB2 8.1.1 on AIX 5.1
When I run the design advisor on selected sql I am getting
'recommendations' for indexes which already exist. I don't understand
why this is happening. They are described in the recommendations
exactly as they already exist. Relatively new to DB2.
Not sure if this is normal but don't understand, yet, why it would be.
Thanks
|
by: datapro01 |
last post by:
I am running DB2 8.1.1 on AIX 5.1
Running the db2advis command on a set group of sql statements. They are
taking default values on frequency.
I run the command
db2advis -d scdoltpt -i allclaims_3220916
and I get a list of 13 suggested indexes. I pick 1 that I think would
| |
by: Sathya |
last post by:
We are facing design issues, Could you please advice us how to proceed?
Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records
Requirement: Turn around time of the SQL query should be as far as
possible minimum.
|
by: Quasar |
last post by:
Hi Gents,
I'm trying to tune our DBs using the Desing Advisor feature. I'm
interested just in the indexes area.
Following the IBM infocenter I came to this page:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0011424.htm
which explains how to create a workload from the dynamic statements
cache.
The problem comes when I reset the statements cache before starting
the monitoring period,...
|
by: Okonita |
last post by:
Hello community,
My environment is DB2 UDB v8.2 LUW. I am experiencing a vexing problem
with using my DB2 Design Advisor. I submitted a SQL script to Advisor
for index recommendation and I am getting the error:
IBM] SQL0180N The syntax of the string
representation of a datetime value is incorrect.
SQLSTATE"007
|
by: Michel Esber |
last post by:
DB2 LUW v8 FP15 Linux.
Consider the table T (ID varchar, FIELD_TIME timestamp, Field3
integer). There are approx. 1k different IDs.
Each minute, one application inserts around (avg. 200, max 5k rows)
for each ID. That makes 200k rows per minute, and 12M rows per hour.
As soon as there is at least 1h of inserted data for each ID, another
application summarizes rows then deletes this 'hour', exploiting
|
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: 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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |