RECOVER INDEX

< Day Day Up >

`RECOVER INDEX`

Through DB2 V5, the RECOVER INDEX utility is used to re-create indexes from current data. RECOVER INDEX scans the table on which the index is based and regenerates the index based on the actual data. Indexes are always recovered from actual table data, not from image copy and log data.

DB2 Version 6 changes the functionality of the RECOVER INDEX utility changes. Instead of rebuilding indexes from the current data, RECOVER INDEX will actually recover the index data by reading an image copy of the index data set. So, with DB2 V6, you can use the COPY utility to make backups of DB2 indexes and the RECOVER utility to restore them.

To provide equivalent functionality for re-creating an index from the current data, IBM provides a new utility called REBUILD INDEX . The REBUILD INDEX utility works exactly like RECOVER INDEX used to.

Organizations should begin changing all of their current RECOVER INDEX jobs to use REBUILD INDEX syntax instead. The REBUILD INDEX syntax is available in DB2 V5 and V4 (with PTF PQ09842) and will work exactly like RECOVER INDEX . After you migrate to DB2 V6, the RECOVER INDEX utility will cease to function if the proper index backup copies are not available to use during recovery.

< Day Day Up >

Host Variables Without Colons

All DB2 programmers should know that host variables used in SQL statements in a program should be preceded by a colon . So, if a host variable is named HV it should be coded in the SQL statement as :HV . However, most programmers do not know that through V5, DB2 programs tolerate host variables that are not preceded by a colon. DB2 will spit out a warning message, but will process the SQL containing the offending host variable. This "feature" is no longer supported as of DB2 V6.

The reason IBM eliminated this feature is the rising complexity of SQL. It is getting too difficult for DB2 to differentiate host variables from SQL when it parses the SQL to be prepared for execution. With all of the new features being added to DB2, the rising complexity of the SQL language will continue unabated. As such, for DB2 V6 and onward, all host variables must be prefixed with a colon, or the statement will fail to execute.

This change should not impact many programs because most organizations have DB2 standards that dictate all host variables must begin with a colon. However, because DB2 has tolerated host variables without a colon for many years (through DB2 V5), you should inspect all DB2 SQL statements in application programs to ensure compliance prior to migrating to DB2 V6.

This is the most difficult problem to find and fix as a result of moving to DB2 Version 6. If you do not fix the problem prior to migrating to V6, any programs containing offending host variables will fail the next time they are rebound.

< Day Day Up >

Dataset Passwords

The ability to provide security via dataset passwords was a little-used feature of DB2. Using the DSETPASS keyword of the CREATE TABLESPACE and CREATE INDEX statement, it was possible to password protect DB2 datasets.

This feature disappeared with DB2 V6. If you need to protect your DB2 datasets outside of DB2 security, you can use RACF, ACF2, Top Secret, or whatever security package you have installed at your site to accomplish this.

To find datasets that are password protected using DSETPASS , issue the following SQL statement:

SELECT   'INDEX ', CREATOR, NAME

FROM     SYSIBM.SYSINDEXES

WHERE    DSETPASS <> '        '

UNION ALL

SELECT   'TSPACE', DBNAME, NAME

FROM     SYSIBM.SYSTABLESPACE

WHERE    DSETPASS <> '        '

< Day Day Up >