Remove Non-table Objects
The purpose of removing non-table objects is to reduce the database to base tables.
Some database objects such as procedures and views can be very complicated, and some past versions of upgradedb did not always process them successfully. Additionally, processing of some objects (grants in particular) is slow and expensive. Dropping the grants and later recreating them avoids any possible failure due to lack of transaction log space.
Note: Do not process Ingres Star distributed databases.
To remove non-table objects
1. Drop all non-table objects from the database including:
• Optimizer statistics
• Views
• Rules
• Database procedures
• Database events
• Secondary indexes
• Grants and QUEL permits
• QUEL integrities
2. Modify all tables to heap.
UNIX:
To perform this step automatically
1. Use the shell script oi_prep.sh. The script is available from Actian Support.
Using the C shell, issue this command:
oi_prep.sh dbname |& tee oi_prep.log
If there are any dependent views, “drop” errors messages may be reported on those views (oi_prep.sh does not drop views in reverse dependency order); ignore the “drop” errors
2. Run verifydb checks against the database.
The verifydb -odbms command may output the following messages, which you can ignore:
S_DU1611_NO_PROTECTS iirelation indicates that there are protections for table (owner), but none are defined.
S_DU0305_CLEAR_PRTUPS Recommended action is to clear protection information from iirelation, and S_DU1619_NO_VIEW iirelation indicates that there is a view defined for table (owner), but none exists.
S_DU030C_CLEAR_VBASE Recommended action is to clear view base specification from iirelation.
Also ignore the “patch warning” message that warns of the loss of user tables in “runinteractive” mode. This mode will not be used.
3. If some databases produce a “verifydb failed” message and then abort, run the Terminal Monitor with the update system catalogs flag, as follows:
sql +U dbname
SELECT * FROM iistatistics;\go
No rows should be returned. If there are rows, this is the probable cause of the verifydb problem.
4. If there are rows, delete them, as follows:
DELETE FROM iistatistics;COMMIT;\go\quit
5. Rerun the verifydb command as shown at the end of the oi_prep.sh.
6. If error messages are returned from verifydb, correct the problems before continuing. Contact Actian Support for help, if necessary.
