DB2 Version 10.1 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. If you are upgrading from Version 9.5 to Version 10.1, 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.

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.

Important: The db2IdentifyType1 command has been deprecated in Version 10.1 and might be removed in future release. Type-1 indexes were discontinued in Version 9.7. You can still use this command to help you convert type-1 indexes before upgrading databases from Version 9.5 to Version 10.1. For more information, see db2IdentifyType1 command has been deprecated.

About this task

You should only perform this task if you are upgrading from Version 9.5 to Version 10.1 and you know or suspect that your Version 9.5 databases have type-1 indexes.

Also, you must perform this task before upgrading to Version 10.1. the command file generated by the db2IdentifyType1 command contains REORG INDEXES ALL commands with the CONVERT parameter for each identified type-1 index. The CONVERT parameter is discontinued in Version 10.1.

By default, all new indexes created in earlier 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.

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 10.1 installation.
- On Windows operating system, you need to insert the DB2 Version 10.1 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.
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 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. This must be run from a Version 9.5 client since REORG INDEXES with the CONVERT clause is no longer supported in Version 10.