1

Topic: Rebuild indexes after reorganization. Help!!!!

Kind all day!
We have such problem, the second time already repeats... There is a big table (190 000 000 lines);
After which reorganization, happens rebuid indexes.
Why indexes are marked as invalid?
Reorganization of indexes occupies very durable time. Reorganization it is done with an option read access.
Consult how to exercise judgement in reorganization process: to do it once a week;
Or some times in a week?
And with read access or in an exclusive mode?

2

Re: Rebuild indexes after reorganization. Help!!!!

mataba;
Good afternoon.
Such design at offline reorganization:
REORG INDEXES/TABLE command
A classic table reorganization (offline reorganization) rebuilds the indexes during the last phase of the reorganization.
Concerning when it is necessary to do, the answer can give REORGCHK command . You to it use?
The answer to a question, in what mode to do, depends on several factors: kind of work of users in your system in an operating time reorg, possibility / expediency of application inplace (online) reorg instead of offline etc.

3

Re: Rebuild indexes after reorganization. Help!!!!

in that here is a lot of us. Which cannot analyze a state of tables before reorganization
1. The software which is given by developers for DB update, does not try to carry out the analysis at all. In it it is stupid after each change there is a reorganization of tables and also statistics collection. To supervise that changed or developers did not change it means to assume overall responsibility at update and it is probability 50/50.
2. All messages on those  come to an end with the answer "when carried out the last reorganization?"
And all for one reason of their software works on OpenJPA 2.1, and the state of indexes for them is all
Therefore also we want as that all it to accelerate since Resources is and here speeds absolutely depress.
In this case at us table 960 of millions records in it of 12 indexes all time of reorganization about 11-12 hours. Thus the server approximately really reads 2-3 hours and writes down the data and is occupied by reorganization and the rest of the time it is simply a little occupied % on 5-10. In db2diag.log between these messages anything is not present.
mataba - forgive for interference in your message.
p.s. Tried as that after failure when reorgchk really told that indexes dead
To launch reorganization online but all it appeared it is sad

4

Re: Rebuild indexes after reorganization. Help!!!!

When there is a reorganization, lines change places - hence, rid' change - hence, indexes should become invalid. I doubt that any other DBMS would consult better.
The very first question therefore - whether it is valid such frequent reorganization of such big table it is necessary. I suspect that was not present. Perhaps, once a year is enough. Or even it is too frequent.
To change structure that it was possible to carry out reorganization in parts. To lead  (if it is accessible).
Exists and " for poor": to break the table on some, assigning on them constraint's on which the line gets precisely to one, and to unite with view through union all.
That is instead of table t to create
create table t1 (... constraint c1 check...)
create table t2 (... constraint c2 check...)
....
create table tn (... constraint cN check...)
create view t as
select * from t1
union all
...
select * from tn.
And at insertion attempt insert into t... Lines
All constraints c1... cn, except one, should give false
(By the way, not all know that, unlike where where lines for which the predicate gives undefined are not selected, the DBMS allows to interpose a line if the result of a predicate in check appeared undefined).
To add it is a lot of RAM, to pass from winchesters on .
To replace a software, to find at what developers more made.

5

Re: Rebuild indexes after reorganization. Help!!!!

@Chumakov_JA
You ask how optimally to approach to the decision, but say that it not in your forces - to change process.
In what sense of a question then?
If it is necessary to accelerate process it demands, most likely, structural changes. For example, table sectioning to reorganize, say, only one last section. Or usage DPF, but is already serious alteration.

Chumakov_JA wrote:

p.s. Tried as that after failure when reorgchk really told that indexes dead
To launch reorganization online but all it appeared it is sad

Dead indexes? It as? A utility output can show?
What exactly appeared sad?

6

Re: Rebuild indexes after reorganization. Help!!!!

Mark Barinstein;
On the first point. We have license ESE but in those the job is strict  WSE
Accordingly developers cannot use multipartite the table.
And we cannot pass on multipartite the table without approval which to us did not give.
For itself tested operation of a software I with multipartite tables, productivity on 40 % raised also reorganized only those parts which were last. All is simply remarkable. But alas the answer "not the typical circuit"
On the second point. For a day the server was ungeared "casually" time by three.
After that reorgchk produced that indexes are unsuitable the answer did not save.
Launched reorganization online, but the copy of basis and all reorganization in the empty in the evening is necessary.
And while there was a reorganization of lock were well very often. 2 days  that only the full reorganization helps also the total of 11 hours of waiting and all became simple to fly.
p.s. Here when basis  developers all tables collected one more moment in one tabular space.

7

Re: Rebuild indexes after reorganization. Help!!!!

Victor Metelitsa;
http://www.sql.ru/forum/1264275/reorg-t … i-indeksov
There practically all answers to your sentences

8

Re: Rebuild indexes after reorganization. Help!!!!

Chumakov_JA wrote:

Victor Metelitsa;
http://www.sql.ru/forum/1264275/reorg-t … i-indeksov
There practically all answers to your sentences

Miracles does not happen.

9

Re: Rebuild indexes after reorganization. Help!!!!

Chumakov_JA wrote:

Mark Barinstein;
And while there was a reorganization of lock were well very often. 2 days  that only the full reorganization helps also the total of 11 hours of waiting and all became simple to fly.

Why "only the full reorganization helps" and in what consisted ? Not in beaten indexes and not what there were brakes while reorganization went?

10

Re: Rebuild indexes after reorganization. Help!!!!

Chumakov_JA wrote:

On the first point. We have license ESE but in those the job is strict  WSE
Accordingly developers cannot use multipartite the table.

Table partitioning, since 10.5, it is accessible in WSE.
Functionality in DB2 product editions and DB2 10.5 offerings .

11

Re: Rebuild indexes after reorganization. Help!!!!

Mark Barinstein;
Let's know BUT on  should be DB2 9.7.6 and so for transfer of tables to other tabular space to use a command move table (I can be mistaken) it did not turn out to use. In 9.7.6 there is a jamb if the description of fields in Russian.
Output idle time to install fix pack 10, and so officially it is impossible.
And you about 10.5
And here such variant during the moment when I need to carry out table reorganization
I change parameters DB2 so that all resources have been given only on reorganization
Question what parameters accelerate reorganization?

12

Re: Rebuild indexes after reorganization. Help!!!!

Chumakov_JA;
In a week 9.7 acts in film from support. You, in an amicable way, all the same should reflect on migration.
Reorg - operation not fast. You do not accelerate cardinally its change of parameters.
If there is a possibility, try to transfer "manually" the data from this table in new same with the help load from the cursor or  insert select with activation in the transaction beginning not logged initially at the purpose. But both these of the approach have minuses, and in both cases this re-creation of the table and the bound objects.