DB2 Version 9.7 for Linux, UNIX, and Windows

Converting type-1 indexes to type-2 indexes

Type-1 indexes are discontinued in DB2® Version 9.7 and are marked invalid during database upgrade. Consider converting any existing type-1 indexes to type-2 indexes before the upgrade to avoid the overhead of automatic index rebuild after the upgrade.

About this task

You should only perform this task if you know or suspect that your database has type-1 indexes.

By default, all new indexes created in pre-Version 9.7 releases were type-2 indexes except when you created an index on a table that already had type-1 indexes, in which case the new index is also type-1. You might have type-1 indexes on databases that you created on DB2 UDB Version 7 or earlier and that you upgraded all the way through to DB2 Version 9.5 or databases under an instance where the DB2_INDEX_TYPE2 registry variable was set to OFF.

If you decide not to convert your type-1 indexes before the database upgrade, the type-1 indexes are marked invalid during database upgrade. If the indexrec database configuration parameter is set to RESTART, indexes marked invalid are rebuilt when the database is restarted. Otherwise, the type-1 index rebuild starts on your first access to the table and you might experience an unexpected degradation in response time.

Before you begin

Ensure that you have the required authorization for the db2IdentifyType1, INSPECT, and REORG INDEXES/TABLE command. See the Command Reference for details on the required authorization.

Procedure

To convert type-1 indexes to type-2 indexes:

Ensure that you have enough disk space to convert your type-1 indexes. Type-2 indexes require one more byte per row. Refer to Space requirements for indexes for details on how to estimate the space needed.
From a DB2 command line prompt, change to the appropriate directory:
- On UNIX or Linux operating systems, change to the $DB2DIR/bin directory where DB2DIR is the location that you specified during the DB2 Version 9.7 installation.
- On Windows operating system, you need to insert the DB2 Version 9.7 product CD in the drive and change to the \db2\Windows\utilities directory.
Verify if you have type-1 indexes by issuing the db2IdentifyType1 command:
```
   db2IdentifyType1 -d database-name
                    -o convert-t1-indexes-dbname.db2
```
If you have type-1 indexes, you will receive the following message: Type-1 indexes were found in the inspected tables. The convert-t1-indexes-dbname.db2 command file contains REORG INDEXES ALL commands with the CONVERT parameter for each identified type-1 index.
If you are running the db2IdentifyType1 command on DB2 UDB Version 8 databases, this command does not check for type-1 indexes in typed tables.

The running time of the db2IdentifyType1 command is proportional to the number of tables in the database and the number of database partitions. For databases with a large number of tables, large number of database partitions, or both, use the -s or -t parameters to run this command on specific schemas or tables until you process all your tables. See the Command Reference for additional performance considerations.
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:
1. 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
```
2. 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.
...
```
3. 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
```
If you decide not to run the INSPECT command because of time and resources limitations, you can simply add a REORG INDEXES ALL command with the CONVERT parameter to the convert-t1-indexes-dbname.db2 command file for each of the root tables listed in step 4.a. The CONVERT parameter converts only your type-1 indexes and has no effect on your type-2 indexes.
If you have type-1 indexes, convert them to type-2 indexes by running the convert-t1-indexes-dbname.db2 command file:
```
   db2 -tvf convert-t1-indexes-dbname.db2
```
You can edit this command file and add or remove commands to convert type-1 indexes.