Type 1 Indexes

Prior to DB2 Version 6 there were two types of indexes available to DB2: type 1 and type 2. Type 2 indexes were introduced with DB2 Version 4 and are now the standard index type implemented in every DB2 shop. Even prior to V6, most organizations favored creating type 2 indexes over type 1 indexes because they offer the following benefits:

• Eliminate index locking (the predominant cause of contention in most pre-V4 DB2 applications).

• Type 2 indexes do not use index subpages.

• Type 2 indexes are the only type supported for ASCII encoded tables.

• Many newer DB2 features cannot be used unless Type 2 indexes are used; these features include row level locking, data sharing, full partition independence, uncommitted reads ( ISOLATION(UR) ), UNIQUE WHERE NOT NULL , and CPU and Sysplex parallelism.

As of DB2 V6, type 1 indexes are no longer supported by DB2. So, if you are running V6, V7, or V8, all of your shop's indexes will be type 2.

For those few shops not yet on Version 6, it is wise to begin migrating from type 1 to type 2 indexes as soon as possible, not just because of the benefits outlined earlier, but also because type 1 indexes are obsolete.

NOTE

If your shop is running DB2 V3 or an earlier release, you cannot implement type 2 indexes because they are not supported. If you are running on such an old version of DB2, you should really begin to migrate to a more recent DB2 version.

DB2 V4 was the first version of DB2 to begin supporting type 2 indexes.

To find all type 1 indexes in your DB2 subsystems issue the following SQL statement:

 SELECT  CREATOR, NAME FROM    SYSIBM.SYSINDEXES WHERE   INDEXTYPE = ' '; 

For DB2 V4 and V5 subsystems type 1 indexes are still supported. However, you should convert to type 2 indexes as soon as possible because of the benefits they provide. Additionally, you can set the DSNZPARM parameter DEFIXTP=2 to make type 2 indexes the default index type.