By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/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

P: n/a
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

P: n/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

P: n/a
"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

P: n/a
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

P: n/a
"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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.