DB2 UDB LUW: Upgrade all type-1 in indices to type-2, check (afterwards)

Bernard Dhooghe

Environment: DB2 UDB LUW.

The be able to use on-line reorg, indices must be of type-2.

1. How to migrate a 8.1 database that was created on 7.1 (with type-1
indices)at once to type-2 indices

2. how to check that indeed all indices are type-2 indices (or in
general how to know if an index is type-1 or type-2).
Bernard Dhooghe

Nov 12 '05 #1

Mark A

"Bernard Dhooghe" <no***@attglobal.net> wrote in message
news:25**************************@posting.google.c om...

Environment: DB2 UDB LUW.

The be able to use on-line reorg, indices must be of type-2.

1. How to migrate a 8.1 database that was created on 7.1 (with type-1
indices)at once to type-2 indices
You can regorg the index, which will autmatically migrate them (if not
already migrated) unless you use the cleanup option on the reorg.

You can also use the CONVERT option of the reorg to convert them to type-2
(without reorging them).

2. how to check that indeed all indices are type-2 indices (or in
general how to know if an index is type-1 or type-2).
I believe the INSPECT INDEX command can tell you, but it is supposedly
slower than the reorg with convert option.

Bernard Dhooghe

Nov 12 '05 #2

Pierre Saint-Jacques

The REORG command in V8 has an option to CONVERT the indexes of any
table from Type 1 to 2.
I'm not sure but I think the syscat.indexes has a column that will
indicate the type.
All indexes will stay of Type 1 on any table after migrate, even new
ones created, until the convert has been done.
HTH, Pierre.

Bernard Dhooghe wrote:

Environment: DB2 UDB LUW.

The be able to use on-line reorg, indices must be of type-2.

1. How to migrate a 8.1 database that was created on 7.1 (with type-1
indices)at once to type-2 indices

2. how to check that indeed all indices are type-2 indices (or in
general how to know if an index is type-1 or type-2).
Bernard Dhooghe

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #3

Mark A

"Pierre Saint-Jacques" <se*****@attglobal.net> wrote in message
news:41**************@attglobal.net...

The REORG command in V8 has an option to CONVERT the indexes of any
table from Type 1 to 2.
I'm not sure but I think the syscat.indexes has a column that will
indicate the type.
All indexes will stay of Type 1 on any table after migrate, even new
ones created, until the convert has been done.
HTH, Pierre.

REORG INDEXES will always convert type-1 indexes to type-2 indexes unless
you use the CLEANUP option.

If you use the CONVERT option on the REORG, the indexes will be converted to
type-2, but will not actually be reorged.

Nov 12 '05 #4

Bernard Dhooghe

"Mark A" <no****@nowhere.com> wrote in message news:<Sh****************@news.uswest.net>...

"Pierre Saint-Jacques" <se*****@attglobal.net> wrote in message
news:41**************@attglobal.net...

The REORG command in V8 has an option to CONVERT the indexes of any
table from Type 1 to 2.
I'm not sure but I think the syscat.indexes has a column that will
indicate the type.
All indexes will stay of Type 1 on any table after migrate, even new
ones created, until the convert has been done.
HTH, Pierre.

REORG INDEXES will always convert type-1 indexes to type-2 indexes unless
you use the CLEANUP option.

If you use the CONVERT option on the REORG, the indexes will be converted to
type-2, but will not actually be reorged.

REORG is on table level. So to migrate indexes, a table by table
approach is needed at this time.

Knowing for sure if an index is type-1 or type-2 is something more
than nice to know. But I don't find a command for this (or a
documented syscat indicator).

Bernard Dhooghe

Nov 12 '05 #5

Joachim Müller

Bernard,

you can use only :
db2 "inspect check tablespaces tbspaceid <nn> results keep inspect.out",
after that you have to format the output:
db2inspf inspect.out inspect.rpt

You will find a line in the report like:

"The index type is 2 for this table"
after a convert to index-type2.

regards,
Joachim Müller

"Bernard Dhooghe" <no***@attglobal.net> schrieb im Newsbeitrag
news:25**************************@posting.google.c om...

"Mark A" <no****@nowhere.com> wrote in message

news:<Sh****************@news.uswest.net>...

"Pierre Saint-Jacques" <se*****@attglobal.net> wrote in message
news:41**************@attglobal.net...

The REORG command in V8 has an option to CONVERT the indexes of any
table from Type 1 to 2.
I'm not sure but I think the syscat.indexes has a column that will
indicate the type.
All indexes will stay of Type 1 on any table after migrate, even new
ones created, until the convert has been done.
HTH, Pierre.

REORG INDEXES will always convert type-1 indexes to type-2 indexes unless you use the CLEANUP option.

If you use the CONVERT option on the REORG, the indexes will be converted to type-2, but will not actually be reorged.

REORG is on table level. So to migrate indexes, a table by table
approach is needed at this time.

Knowing for sure if an index is type-1 or type-2 is something more
than nice to know. But I don't find a command for this (or a
documented syscat indicator).

Bernard Dhooghe

Nov 12 '05 #6

Bernard Dhooghe

"Joachim Müller" <jo*****@douglas-informatik.de> wrote in message news:<ce**********@news.dtag.de>...

Bernard,

you can use only :
db2 "inspect check tablespaces tbspaceid <nn> results keep inspect.out",
after that you have to format the output:
db2inspf inspect.out inspect.rpt

You will find a line in the report like:

"The index type is 2 for this table"
after a convert to index-type2.

regards,
Joachim Müller

Joachim,

This is what I was looking for, thank you. Works fine (Remark: the
check does not need a s on tablespace: inspect check tablespace
tbspaceid ...).
Bernard

Nov 12 '05 #7

AnandG

You can also use the following command.

db2 "load query table <tablename>"

If the output returns as below, then all the indices on the table are type 2.
Tablestate:
Normal

If the indices are type-1, then it will clearly spell out as Type 1.

Anand

no***@attglobal.net (Bernard Dhooghe) wrote in message news:<25**************************@posting.google. com>...

"Joachim Müller" <jo*****@douglas-informatik.de> wrote in message news:<ce**********@news.dtag.de>...

Bernard,

you can use only :
db2 "inspect check tablespaces tbspaceid <nn> results keep inspect.out",
after that you have to format the output:
db2inspf inspect.out inspect.rpt

You will find a line in the report like:

"The index type is 2 for this table"
after a convert to index-type2.

regards,
Joachim Müller

Joachim,

This is what I was looking for, thank you. Works fine (Remark: the
check does not need a s on tablespace: inspect check tablespace
tbspaceid ...).
Bernard

Nov 12 '05 #8

Bernard Dhooghe

ag*************@hotmail.com (AnandG) wrote in message news:<c1**************************@posting.google. com>...

You can also use the following command.

db2 "load query table <tablename>"

If the output returns as below, then all the indices on the table are type 2.
Tablestate:
Normal

If the indices are type-1, then it will clearly spell out as Type 1.

Anand

Works also you mention.
..
A table with type-1 indexes shows:

Tablestate:
Normal
Type-1 Indexes

With type-2:

Tablestate:
Normal

Thank you for the info.

Bernard.

Nov 12 '05 #9