Type-2 index question

[cmc]

I look at the table indexes of the system tables to see what kind of indexes
in UDB V8.

Here is my command:
SELECT DISTINCT INDEXTYPE FROM SYSCAT.INDEXES

I got indextype "REG" returned.

My questions:
1. What is REG stand for (seems REGULAR) - type-2 ? What could be other
values ?
2. Where I can figure which index type is used by a table (I suppose the
best place in SYSCAT.TABLES cause onle one index type allowed in any table
but not able to find any column seems related) ? I tried INSPECT as
following:
INSPECT check table name SYSTABLES schema SYSIBM index normal results
keep AABB.lis

But not able to see anything (not even the file).

Thanks for any help
Tom

[Keith]

1. REG is for regular. Others can be blok (block), clus (cluster), dim
(dimension block).

2. try this (after your inspect command): db2inspf AABB.lis AABB.out
You can now view AABB.out and find out your index type. In V8, it's
always Type-2, unless you've migrated from V7 and didn't convert
indexes from type-1 to type-2.

Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for Linux,
UNIX, and Windows
INFORMIX Certified Database Administrator

[Mark A]

"cmc" <cm*****@yahoo.com> wrote in message
news:S5*******************@news20.bellglobal.com.. .

I look at the table indexes of the system tables to see what kind of
indexes
in UDB V8.

Here is my command:
SELECT DISTINCT INDEXTYPE FROM SYSCAT.INDEXES

I got indextype "REG" returned.

My questions:
1. What is REG stand for (seems REGULAR) - type-2 ? What could be other
values ?
2. Where I can figure which index type is used by a table (I suppose the
best place in SYSCAT.TABLES cause onle one index type allowed in any table
but not able to find any column seems related) ? I tried INSPECT as
following:
INSPECT check table name SYSTABLES schema SYSIBM index normal results
keep AABB.lis

But not able to see anything (not even the file).

Thanks for any help
Tom

The values for INDEXTYPE are in the Appendix D of the SQL Reference Vol 1
,where the catalog tables are documented.

If you reorg all your index, they will become type 2 if they were previously
type 1.

[cmc]

Seems INDEXTYPE is not a good way to tell the index is type 1 or 2 because I
suppose a REGULAR index can be type 1 or 2 UDB V8 (while other type
CLUS/DIM/BLOK must be TYPE-2).
Back to my orginal question:
Is there a direct way to tell a table is using type-1 or type-2 index
(without using INSPECT CHECK TABLE as I cannot get it work) ?

Thanks
Tom

"Mark A" <no****@nowhere.com> wrote in message
news:5e********************@comcast.com...

"cmc" <cm*****@yahoo.com> wrote in message
news:S5*******************@news20.bellglobal.com.. .

I look at the table indexes of the system tables to see what kind of
indexes
in UDB V8.

Here is my command:
SELECT DISTINCT INDEXTYPE FROM SYSCAT.INDEXES

I got indextype "REG" returned.

My questions:
1. What is REG stand for (seems REGULAR) - type-2 ? What could be other values ?
2. Where I can figure which index type is used by a table (I suppose the best place in SYSCAT.TABLES cause onle one index type allowed in any table but not able to find any column seems related) ? I tried INSPECT as
following:
INSPECT check table name SYSTABLES schema SYSIBM index normal results
keep AABB.lis

But not able to see anything (not even the file).

Thanks for any help
Tom
The values for INDEXTYPE are in the Appendix D of the SQL Reference Vol 1
,where the catalog tables are documented.

If you reorg all your index, they will become type 2 if they were

previously type 1.