Hi all,
I DB2 v8.1 FixPack 15 on Linux. I have a table which has over 6 Million rows When I query that table it takes too much time even if I query for first 10 rows only. It also gives error "Log File has reached its saturation point", and ADM5543E Index rebuild of indexes on table.
Some times it crashes the database manager.
Please help how can I manage that table efficiently???
and why the database manager crashes???
Sincerely,
Fahim
2  9009   sakumar9 127
Recognized Expert New Member
Hi all,
I DB2 v8.1 FixPack 15 on Linux. I have a table which has over 6 Million rows When I query that table it takes too much time even if I query for first 10 rows only. It also gives error "Log File has reached its saturation point", and ADM5543E Index rebuild of indexes on table.
Some times it crashes the database manager.
Please help how can I manage that table efficiently???
and why the database manager crashes???
Sincerely,
Fahim
Things that you can try:
1. Use proper indexes. You can decide upon the search criteria. You can also take help of visual explain to check if the indexes that you have created are being used or not. Visual explain will also give you the time consumed at each level of operation. This will help you in deciding the suitable candidates for indexes.
2. Log file reaching saturation point: Check the type of logging that you are using. You can use circular logging or infinite logging whichever suits you. Using infinite logging will prevent log full error. I am not sure if this can cause dbm crash.
3. Enable INTRA_PARALLEL so that the index rebuilding is done with parallel processing.
4. Tune the sortheap and sheapthres database configuration parameters to control space for sorts. Since each processor will perform a private sort, the value of sheapthres should be at least sortheap x the number of processors used.
5. Ensure dirty index pages are getting cleaned from the buffer pool as soon as possible by tuning the number of page cleaners.
6. Your table might have gone through many updates and inserts/deletes. It is possible that your table is fragmented. You can reorg the table to get rid of these fragments.
7. Refer to this link: http://publib.boulder. ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw .admin.perf.doc/doc/c0008170.html
Let me know if your problem is solved now.
Regards
-- Sanjay
Thanks Sanjay,
I am really grateful to you, you replied with details.
I'll try these tips in evening, Will tell you the result.
Thanks again
Fahim
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Matt Liverance |
last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables
working any faster.
I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
raid 5 on u320 perc raid cards, dell 2600/4600's with single channel
backplanes (new ones will have dual channel)
All have 2 gig of ram, but I've never seen mysql use more than 300mb
of ram.
|
by: dfurtney |
last post by:
SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)
that we need to add some indexes for. In a test, it took over 12 hours
to CREATE a new INDEX against this table. One of us suggested that we
create a temp table with the new index and copy the data from the old
table into the new one, then rename it. I understand this took 15
minutes. Why the heck would it be faster to move the data and build
multiple indexes...
|
by: charlies224 |
last post by:
Hi,
I am using SQL 2000 and has a table that contains more than 2 million
rows of data (and growing). Right now, I have encountered 2 problems:
1) Sometimes, when I try to query against this table, I would get sql
command time out. Hence, I did more testing with Query Analyser and to
find out that the same queries would not always take about the same
time to be executed. Could anyone please tell me what would affect the
speed of the...
|
by: G.W. Lucas |
last post by:
I apologize if this is a RTFM question, but I have not been able
to find a definitive answer elsewhere.
Does a "REINDEX TABLE" lock the table while it is working?
Can applications write data to the table during the REINDEX?
I am working on a real-time application that handles over 2 million
"events" per day and runs 7-by-24. Most of these events involve an
UPDATE to one or two tables, though a small subset involve a bit
more work. ...
|
by: Gavin Scott |
last post by:
Hi,
I'm having a performance problem with a large database table
we use with postgres 7.3.4. The table is:
db=> \d log
Table "public.log"
Column | Type | Modifiers
---------+----------+-----------
hid | integer |
| | |
by: Kevin Chambers |
last post by:
Hi all--
In an attempt to commit an Access MDB to a versioning system (subversion),
I'm trying to figure out how to convert a jet table's metadata to text, a
la SaveAsText. The end goal is to be able to build an MDB completely from
the svn repository text files.
Has anybody dealt with this?
Thanks in advance,
|
by: mfrsousa |
last post by:
hi there,
i have a huge large text file (350.000 lines) that i want to import to
a MS Acccess Database, of course i don't want to use Access, but do it
with C#.
i already have tried the AddRow method or Insert, reading each line of
the text file, the problem of course is velocity, it would take more
than 4 hours to add all lines/records to the database.
|
by: nflacco |
last post by:
I'm tinkering around with a data collection system, and have come up
with a very hackish way to store my data- for reference, I'm
anticipating collecting at least 100 million different dataId
whatevers per year, possibly much more.
---366 data tables ( one for each day of the year ), each row being
assigned a unique DataId ( unique across all 366 tables too )
---100 data_map tables, table 0 having all DataIds ending in 00, table
99...
|
by: Dilip1983 |
last post by:
Hi All,
I want to delete duplicate records from a large table.
There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state.
First of all when i tried to rebuild index it showed error as unique key violation.
So i want to delete duplicate records for col1,col2,col3,col4 combination.
How can i delete the duplicate records from this large table?
|
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: 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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
