473,238 Members | 2,129 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

table index organized ?

Hi,
how to know for a particular table if it is organized by index, and which
one ?

reorg table index ind1 -> the table is organised by ind1

i wan't to know that for all table

thx

Nov 12 '05 #1
10 6380
Jean wrote:
Hi,
how to know for a particular table if it is organized by index, and which
one ?

reorg table index ind1 -> the table is organised by ind1

i wan't to know that for all table


Have a look at SYSSTAT.INDEXES.CLUSTERRATIO and CLUSTERFACTOR.

And if you have range clustered tables defined using the ORGANIZE BY KEY
SEQUENCE option, have a look at SYSCAT.INDEXES.INDEXTYPE. It will contain
"RCT" for such tables.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2
"Jean" <te**@est.com> wrote in message news:<38**************************************@pro xyusinor.usinor.com>...
Hi,
how to know for a particular table if it is organized by index, and which
one ?

reorg table index ind1 -> the table is organised by ind1

i wan't to know that for all table

thx


If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.
Nov 12 '05 #3
>
If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.


i don't understand

example :
table1 has 3 indexes : index1, index2, index3

i issue the following command
db2 reorg table table1 index index2

after that, how can i retrieve the table has benn reorganised by using the
index2
I think that now index2 is a cluster index ?

If yes, :
in db2 v7, how can i reorganise all index without issuing all these commands
:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3


Nov 12 '05 #4
Jean wrote:

If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.
i don't understand

example :
table1 has 3 indexes : index1, index2, index3

i issue the following command
db2 reorg table table1 index index2

after that, how can i retrieve the table has benn reorganised by using the
index2
I think that now index2 is a cluster index ?


It depends on what you mean with "cluster index". You can create an index
using the CLUSTER keyword
(http://publib.boulder.ibm.com/infoce...n/r0000919.htm)
or you can reorganize the table as you described above. In both cases, you
will find the information about the cluster index for the table using the
catalog table SYSCAT.INDEXES (or SYSSTAT.INDEXES). In particular, the
columns CLUSTERRATIO and CLUSTERFACTOR are of interest to you as I wrote
before.

If you have range clustered tables defined using the ORGANIZE BY KEY
SEQUENCE option, have a look at SYSCAT.INDEXES.INDEXTYPE.**It*will*contain
"RCT" for such tables.
If yes, :
in db2 v7, how can i reorganise all index without issuing all these
commands
You want to reorganize the table, not the index, right?
:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3


You have to run all the statements. You can query the DB2 catalog and
generate a script that way. Then you can simply execute the script...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5


Knut Stolze wrote:
Jean wrote:

......
If yes, :
in db2 v7, how can i reorganise all index without issuing all these
commands

You want to reorganize the table, not the index, right?

:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3

You have to run all the statements. You can query the DB2 catalog and
generate a script that way. Then you can simply execute the script...

I don't quite understand. I thought indexes get reorg'ed any way and
that specifying an index name with the index clause caused the table to
be roerg'ed in sequencng rows following that index sequence.
If he uses the script, the table will get sorted three times and end up
sequenced by index3. Yes, No??
Regards, Pierre.
--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.
Nov 12 '05 #6
Pierre Saint-Jacques wrote:


Knut Stolze wrote:
Jean wrote:

.....
If yes, :
in db2 v7, how can i reorganise all index without issuing all these
commands

You want to reorganize the table, not the index, right?

:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3

You have to run all the statements. You can query the DB2 catalog and
generate a script that way. Then you can simply execute the script...

I don't quite understand. I thought indexes get reorg'ed any way and
that specifying an index name with the index clause caused the table to
be roerg'ed in sequencng rows following that index sequence.
If he uses the script, the table will get sorted three times and end up
sequenced by index3. Yes, No??


You are right, of course. I haven't read carefully enough and thought Jean
wanted to reorg different tables.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #7
> > If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.


i don't understand

example :
table1 has 3 indexes : index1, index2, index3

i issue the following command
db2 reorg table table1 index index2

after that, how can i retrieve the table has benn reorganised by using the
index2
I think that now index2 is a cluster index ?

If yes, :
in db2 v7, how can i reorganise all index without issuing all these commands
:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3


In V6, if reorg the table, all the indexes get reorged. When you
specify the index in the reorg column, it is used to specify the order
of the rows in the table.

Specifying an index to use during the reorg will not make it the
clustering index (which is only specified in the create index
statement). It will order the rows during the reorg, but will have no
effect where DB2 inserts new rows (as would be the case if you
specified a clustering index).

When specifying a clustering index, you need to leave enough percent
free on the table (in-betwen reorgs) for DB2 to attempt to insert the
rows in the desired order within the table.
Nov 12 '05 #8
> In V6, if reorg the table, all the indexes get reorged. When you
specify the index in the reorg column, it is used to specify the order
of the rows in the table.

Specifying an index to use during the reorg will not make it the
clustering index (which is only specified in the create index
statement). It will order the rows during the reorg, but will have no
effect where DB2 inserts new rows (as would be the case if you
specified a clustering index).

When specifying a clustering index, you need to leave enough percent
free on the table (in-betwen reorgs) for DB2 to attempt to insert the
rows in the desired order within the table.

hi thx for confirmation
this is what i wanted to be confirmed
Nov 12 '05 #9

You are right, of course. I haven't read carefully enough and
thought Jean wanted to reorg different tables.

yes that reassures me
thx for the explanation anyway
Nov 12 '05 #10
"Jean" <te**@est.com> wrote in message news:<38**************************************@pro xyusinor.usinor.com>...
In V6, if reorg the table, all the indexes get reorged. When you
specify the index in the reorg column, it is used to specify the order
of the rows in the table.

Specifying an index to use during the reorg will not make it the
clustering index (which is only specified in the create index
statement). It will order the rows during the reorg, but will have no
effect where DB2 inserts new rows (as would be the case if you
specified a clustering index).

When specifying a clustering index, you need to leave enough percent
free on the table (in-betwen reorgs) for DB2 to attempt to insert the
rows in the desired order within the table.

hi thx for confirmation
this is what i wanted to be confirmed


BTW I meant V7, not V6.
Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: John | last post by:
I have a table with two rows. On the first row is a text box and in the second row is an image. I have set the table cellpadding to 0 and cellspacing to 0. The table is leaving extra spaces in the...
16
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around...
9
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with...
5
by: aboycalled3 | last post by:
I'm interested in using the fascinating CSS available at http://www.moronicbajebus.com/playground/cssplay/reformat-table/ which allows one to present tabular data in a way that's more appealing...
7
by: Bing Wu | last post by:
Hi Folks, I have a very large table containing 170 million rows of coordinats: CREATE TABLE "DB2ADMIN"."COORDINATE" ( "FID" INTEGER NOT NULL , "AID" INTEGER NOT NULL , "X" REAL NOT NULL ,...
1
by: Riley DeWiley | last post by:
I have an application with two tables, A and B. Each has an autonumber unique ID field, plus other data. I have a junction table, AB, containing fields AID, BID, and Count (a number). AB has...
10
by: Omar | last post by:
I'm looking for a programming language or module that sorta looks and feels like MS Excel (I love and think in tables), yet has the power and open-endedness of python or javascript. I'm still...
3
by: Prochot | last post by:
I'm having trouble figuring out how to design my tables and forms to suite my purpose. I'm creating a crewing database to assign and track employees and jobs over multiple shifts. I would like...
0
bilibytes
by: bilibytes | last post by:
hi, I was wondering how to determine whether an information is redundant or not. if you have in a table "Main", the basic information for a thing: thing_id | name | image_id | city_id ok if...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.