How You Upgrade from Ingres 6.4 Using Upgradedb (Alternate)

To upgrade from Ingres 6.4 using the alternate upgradedb procedure, follow this process.

Note: In this procedure, the notation [Each DB] means: "For each database, not including the iidbdb, become the DBA for that database, cd to the unload directory for the database created in Step 1, and perform this step." Do not include the iidbdb or Ingres Star databases unless instructed. If using Ingres Star, remember to include the coordinator database in the list of databases.

1. [Each DB including Ingres Star DDBs] Create Unload Directory.
2. [Each DB including Ingres Star DDBs] Run Unloaddb.

   Note: You can omit Steps 2 through 4 if procedures already exist to recreate all database objects and storage structures. However, it will be necessary to make the appropriate changes to the oi_prep.sh script-see the step Remove Non-table Objects-for re-modifying all tables.

3. [Each DB including Ingres Star DDBs] Check for Obsolete Users.
4. [Each DB] Edit the Unloaddb Output.
5. [Each DB including iidbdb] (Optional) Checkpoint the Database.
6. Disable User Access.
7. Shut Down Ingres and Back Up System.
8. [Each DB] (Optional) Print Optimizer Statistics.
9. [Each DB] Remove Non-table Objects.
10. [Each DB] Record Database Information.
11. Clean iidbdb Database.
12. [Each DB including iidbdb] Checkpoint and Turn Off Journaling.
13. Record Ingres Configuration.
14. Shut Down Ingres.
15. Disable Ingres Startup.
16. Preserve Site Modifications
17. Fix Logins.
18. Save Ingres Settings.
19. Clean Up Ingres 6.4.
20. Create Work Location.
21. Install Ingres.
22. Create imadb Database.
23. Restore Site Modifications.
24. Start Ingres.
25. Run Upgradedb Utility.
26. Configure Ingres.
27. Set Up Ingres Net.
28. [Each DB] Recreate Objects.
29. [Each DB] Reapply Storage Structures.
30. [Each DB] Reapply Optimizer Statistics.
31. [Each DB including iidbdb] Checkpoint the Database.
32. Install Upgraded Applications.

The sections that follow provide details on steps that differ from those described previously in this guide.

Create Unload Directory

You must create a directory to hold scripts, but no data. Make the directory writable by anyone. The disk space needed is a maximum of 1 MB per directory.

To create a directory, issue the following commands for each database, including the Ingres Star databases:

UNIX:

mkdir /someplace/dbname
chmod 777 /someplace/dbname

Windows:

mkdir d:\someplace\dbname

Run Unloaddb

Note on Steps 2 through 4: You can omit Steps 2 through 4 if procedures already exist to recreate all database objects and storage structures. However, it will be necessary to make the appropriate changes to the oi_prep.sh script-see the step Remove Non-table Objects-for re-modifying all tables.

Run unloaddb against each database. The unloaddb command does not unload the database; it simply creates scripts. You can edit these scripts to produce a collection of scripts that recreate various database objects and storage structures.

For Ingres Star databases, unload the CDB in the same way as for a local database. For a DDB, use unloaddb/star.

For a regular DB or CDB, issue this command:

unloaddb dbname

For an Ingres Star DDB, issue this command:

unloaddb ddbname/star

Edit the Unloaddb Output

The unloaddb output must be modified for recreating just the database objects and storage structures.

To edit the unloaddb output, manually edit each cp{user}.in file that unloaddb created to extract the following statements:

• Create rule statements into a file named {user}_rule.sql
• Create procedure related statements into {user}_dbp.sql
• Create dbevent related statements into {user}_event.sql
• Modify statements into {user}_modify.sql
• Modify and create index statements into {user}_modindex.sql
• All other non-base-table related statements into {user}_grantview.sql. This file will contain grants, QUEL permits, QUEL integrities, and view definitions.

For UNIX, the extract_unloaddb.sh shellscript is available that extracts one user's object definitions. The script is available on the Ingres Technical Support web site.

Note: The $ingres user should not own any non-catalog objects, so do not process the cp_ingre.in file that unloaddb creates.

As a result of this step, SQL scripts are created that can recreate any database object or storage structure owned by any user in any database.

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 Ingres Technical 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 Ingres Technical Support for help, if necessary.

Checkpoint and Turn Off Journaling

For each database, including the iidbdb, checkpoint each database and turn off journaling. Then save the configuration file.

The upgradedb process turns off journaling, so it is best to turn if off now. If upgradedb fails, you can use this checkpoint to recover and try again.

To checkpoint and turn off journaling

1. Checkpoint each database, using the ckpdb command with -j option to turn off journaling. Issue the following command:

   ckpdb -d -j dbname

   Note: For the iidbdb, use the ckpdb -s option. The iidbdb database does not have an "unload" directory.

2. Save the configuration file stored in the dump area after each checkpoint. The configuration file is small. Issue the following command:

   cp $II_DUMP/ingres/dmp/default/dbname/aaaaaaaa.cnf {somewhere secure}

Save Ingres Settings

Save your installation ID and default locations, which are kept in a file named symbol.tbl. Copy $II_SYSTEM/ingres/files/symbol.tbl to a safe area not in the Ingres directory tree.

Recreate Objects

Using the scripts generated by the step Edit the Unloaddb Output, recreate the views and other database objects.

Recreate objects in the following sequence:

1. Views, QUEL integrities, and grants:

   sql -uuser dbname <user_grantview.sql

2. Dbevents:

   sql -uuser dbname <user_event.sql

3. Database procedures:

   sql -uuser dbname <user_dbsp.sql

4. Rules:

   sql -uuser dbname <user_rule.sql

Remember to run all four scripts for each user who owns objects in each database.

If your application system has its own scripts to recreate database objects, you may use them instead of the unloaddb-generated scripts.

Reapply Storage Structures

For each user_modindex.sql script generated by the step Edit the Unloaddb Output, reapply storage structures and indexes:

sql -uuser dbname <user_modindex.sql

If your application system has its own scripts to reapply storage structures and create indexes, you may use them instead of the unloaddb-generated scripts.