I realize this topic has been covered many times. I'm looking at the
9.7 upgrade steps outlined on the IBM website. It says, for 8.2
databases, I need to run a sql statement to identify tables, the
inspect command to identify type-1 indexes, then cross reference the
output from those two commands to get the table names. Here's what it
says in IBM document, in the Pre-Upgrade section.


If you are upgrading from DB2 UDB Version 8, perform the following
actions to determine whether your typed tables have type-1 indexes and
manually add the REORG INDEXES ALL commands to the command file:

a.Generate a list of all the root tables for typed tables by issuing
the following query:

SELECT DISTINCT H.ROOT_SCHEMA, H.ROOT_NAME,
T.TBSPACEID, T.TABLEID
FROM SYSCAT.TABLES T, SYSCAT.INDEXES I,
SYSCAT.HIERARCHIES H, SYSCAT.NAMEMAPPINGS N
WHERE T.TYPE = 'H' AND T.TABSCHEMA = I.TABSCHEMA
AND T.TABNAME = I.TABNAME AND H.METATYPE='U'
AND H.ROOT_SCHEMA=N.LOGICAL_SCHEMA AND H.ROOT_NAME=N.LOGICAL_NAME
AND T.TABSCHEMA=N.IMPL_SCHEMA AND T.TABNAME=N.IMPL_NAME

b.Identify any typed tables with type-1 indexes using the INSPECT
command as follows:

db2 INSPECT CHECK DATABASE RESULTS KEEP sample.log
db2inspf $INSTHOME/sqllib/db2dump/sample.log sample.out

Use the values for TBSPACEID and TABLEID from the query result in the
previous step to match the Object and Tablespace identifiers in the
formatted output from the db2inspf command and determine the index
type for each root table as shown in the following example:
....
Table phase start (ID Signed: 4, Unsigned: 4;
Tablespace ID: 3) :

Data phase start. Object: 4 Tablespace: 3
The index type is 1 for this table.
Traversing DAT extent map, anchor 96.
Extent map traversal complete.
DAT Object Summary: Total Pages 20 - Used Pages 20
- Free Space 2 %
Data phase end.

Index phase start. Object: 4 Tablespace: 3
Traversing INX extent map, anchor 160.
Extent map traversal complete.
INX Object Summary: Total Pages 17 - Used Pages 17
Index phase end.
Table phase end.
Tablespace phase end.

....c.Add a REORG INDEXES ALL command to the convert-t1-indexes-
dbname.db2 command file for each root table with type-1 indexes, that
you identified in the previous step, as shown in the following
example:
REORG INDEXES ALL FOR TABLE table-name
ALLOW WRITE ACCESS CONVERT


The problem I have is, when I run the first query, I don't get any
rows returned. Yet, the inspect command identifies 61 table-id's that
have type-1 indexes.


While other threads on this subject have stated a variety of ways to
identify type-1 indexes, I was really hoping to use the IBM upgrade
document, verbatim, to do the work (to cover my a$$). Am I missing
something?

Thanks.

On 2010-12-15 17:07, Richard wrote:
[...]
What is the result of:

select count(1) from SYSCAT.NAMEMAPPINGS

and

select count(1) from SYSCAT.HIERARCHIES H

?

/Lennart

I get zero rows for both queries. That explains the zero result set I
got earlier, but why is IBM telling me
to run this query if it isn't reliable in all 8.2 environments?

Thanks BTW. :-)

"Richard" <rmcgorman (AT) gmail (DOT) com> wrote

If you do a "classic" offline reorg on all tables (type = T in
syscat.tables) for all schemas (including sysibm, etc), then DB2 8.2 will
automatically convert any type 1 indexes to type 2. When you do a "classic"
reorg (not inplace) then all the indexes get reorged (and converted) along
with the table reorg.

On 2010-12-15 17:32, Richard wrote:
I think the instruction is regarding typed tables only. I assume (don't
know though) there is another query regarding regular tables. On the
other hand it would probably be possible to construct a more general
query that deals with both cases


/Lennart

Thanks for the replies.

I think I'll just select tbspaceid, tableid, tabname from
syscat.tables,
and then get the tbspaceid and tableid for tables with type-1 indexes
from the inspect output to determine which indexes need to be
reorg'ed.