How You Upgrade from Ingres 6.4 Using Unload/Reload

To upgrade from Ingres 6.4 using the unload/reload 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." If using Ingres Star, include the coordinator database in the list of databases. Steps that apply to a particular upgrade type only (that is, in-place upgrade or clean install upgrade) are marked accordingly.

1. [Each DB including iidbdb] Create Unload Directory.
2. [Each DB] Run Unloaddb.
3. [Each DB] Check for Obsolete Users.
4. [Each DB including iidbdb] (Optional) Checkpoint the Database.
5. Disable User Access.
6. Shut Down Ingres and Back Up System.
7. [Each DB] Unload the Database.
8. [Each DB] (Optional) Print Optimizer Statistics.
9. [Each DB] Record Database Information.
10. Record Database Privileges.
11. Save Users, Groups, and Roles.
12. [Each DB] Destroy the Database.
13. Clean iidbdb Database.
14. Record Ingres Configuration.
15. Shut Down Ingres.
16. Disable Ingres Startup.
17. Preserve Site Modifications.
18. Fix Logins.
19. Save Ingres Settings.
20. Clean Up Ingres 6.4.
21. Create Work Location.
22. Install Ingres.
23. Create imadb Database.
24. Restore Site Modifications.
25. Configure Ingres.
26. Set Up Ingres Net.
27. Start Ingres.
28. Recreate Users, Groups, and Roles.
29. Recreate Locations.
30. [Each DB] Recreate the Database.
31. [Each DB] Extend the Database.
32. Recreate Database Privileges.
33. [Each DB] Fix FE Reload Script.
34. [Each DB] Reload the Database.
35. [Each DB] Upgrade Front-End Catalogs.
36. [Each DB] Reapply Optimizer Statistics.
37. [Each DB including iidbdb] Checkpoint the Database.
38. Install Upgraded Applications.

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

Check for Obsolete Users

Old databases may have objects created by users who no longer exist. Check for obsolete users for each database.

To check for obsolete users:

1. Examine the scripts created by unloaddb earlier in the upgrade procedure.

   Each script contains one line for each user who owns a database object.

2. Make sure that all users listed are valid.
3. If obsolete users are found, delete the relevant lines from the scripts.
4. Delete the cp{user}.in and cp{user}.out files.
5. Go into the database and clean out these unwanted objects.

Record Database Privileges

To record database privileges

1. As the installation owner, change directories to the unload directory for iidbdb created in Step 1 of this upgrade procedure.
2. Run the following SQL to save private database access lists and user database privileges:

   sql iidbdb
   \script dbaccess.out
   select dbname, usrname
   from iidbaccess
   order by dbname, usrname
   \go
   \script
   \script dbprivs.out
   select *
   from iidbprivileges
   where database_name <> ''
   order by database_name,grantee_name
   \go
   \script
   \quit

   This procedure creates two files, dbaccess.out and dbprivs.out.

Save Users, Groups, and Roles

Note: This step is required only for a clean-install upgrade.

To save users, groups,and roles

1. As the installation owner, change directory to the iidbdb unload directory created in Step 1 of the upgrade procedure.
2. Run the following SQL to save users, groups, and roles:

   sql iidbdb
   create table unload_tmp as
   select name,status,default_group
   from iiuser
   where name not in ('ingres','$ingres','root')
   \go
   copy unload_tmp (
      name=c0comma,status=c0comma,default_group=c0nl
   ) into 'users.out'
   \go
   drop unload_tmp;commit
   \go
   
    copy iiusergroup (
     groupid=c0comma,groupmem=c0nl
   ) into 'groups.out'
   \go
   
    copy iirole(
     roleid=c0nl
   ) into 'roles.out'
   \go
    \quit

Clean iidbdb Database

Note: This step is required only for an in-place upgrade.

To clean the iidbdb database, become the installation owner and run the following steps against the master database iidbdb.

Note: It is assumed that there are no objects created by users in the iidbdb.

statdump '-u$ingres' -zdl iidbdb

sysmod -s iidbdb

verifydb -mrun -sdbname iidbdb -opurge

verifydb -mrun -sdbname iidbdb -odbms

ckpdb -s -j iidbdb

The verifydb command may issue 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.

You can also ignore the "patch warning" message that warns of the loss of user tables in "runinteractive" mode. This mode will not be used.

Record Ingres Configuration

To record Ingres configuration:

1. As the installation owner, execute the "showrcp" command.
2. Record the contents of the rundbms.opt file found in $II_SYSTEM/ingres/files.

You will use this information later as a guide for configuring Ingres. The Ingres installation procedure does not preserve the Version 6.4 parameter settings. During installation, the ingres/files directory is deleted, so save the information.

Shut Down Ingres

Shut down Ingres with the iishutdown command.

Fix Logins

To fix logins:

1. If necessary, make sure that the login for the installation owner sets LD_LIBRARY_PATH or the platform equivalent.
2. Make sure that the login for the user does not use ingprenv1, or install your ingprenv1 substitute, as described in ingprenv Replaces ingprenv1.
3. Check all your database owner (DBA) logins to ensure that they are properly set up for Ingres, with LD_LIBRARY_PATH or equivalent, and no use of ingprenv1.
4. Define LD_LIBRARY_PATH or equivalent for the installation owner user session that you will use to install and upgrade Ingres.
5. If you are doing a clean-install upgrade on a different machine, make sure that your login fixes are applied to the new machine, not to the old one.

Save Ingres Settings

Note: This step is required only for an in-place upgrade.

The upgrade runs more smoothly if the Ingres 6.4 executables, control files, and environment variables are deleted. However, you do not want to lose your installation ID and default locations. These 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.

Clean Up Ingres 6.4

Note: This step is required only for an in-place upgrade.

To guarantee a clean environment for Ingres:

1. Invoke the following commands:

   cd $II_SYSTEM/ingres

   rm -rf bin files lib utility dbtmplt version.rel admin

   mkdir files

2. Copy your saved symbol.tbl back into the $II_SYSTEM/ingres/files directory.

Create Work Location

Note: This step is required only for an in-place upgrade.

The Ingres installation procedure asks for a location for temporary files and sorting, and creates the directories if they do not exist. However, you should create this location manually because some versions of the installation procedure may not properly set the protections for the directories, which can cause upgradedb to fail when upgrading the iidbdb database.

For information on placement of your default work location, see the Database Administrator Guide.

As the installation owner, assume a work location called /mywork:

UNIX:

/mywork:
mkdir /mywork/ingres
mkdir /mywork/ingres/work
mkdir /mywork/ingres/work/default
mkdir /mywork/ingres/work/default/iidbdb
chmod 755 /mywork/ingres
chmod 700 /mywork/ingres/work
chmod 777 /mywork/ingres/work/default
chmod 777 /mywork/ingres/work/default/iidbdb

Windows:

md \mywork\ingres
md \mywork\ingres\work
md \mywork\ingres\work\default
md \mywork\ingres\work\default\iidbdb

Restore Site Modifications

Restore any site-specific files that you copied in the step Preserve Site Modifications.

If the checkpoint template file cktmpl.def has been modified, the modifications may need to be carried forward into Ingres. The cktmpl.def from Ingres 6.4 cannot be used with Ingres, as the file format has changed. This means that you must recreate the changes using the Ingres 6.4 cktmpl.def as a guide. See the Ingres 6.4 Database Administrator's Guide.

If the archiver exit script acpexit was changed in Ingres 6.4, you must make the changes to the Ingres template (acpexit.def), and then move that file to $II_SYSTEM/ingres/files/acpexit.

Configure Ingres

Run Configuration-By-Forms (CBF) and initially configure the Ingres installation. Use the rundbms.opt and showrcp information from Ingres 6.4 as a guideline. For information about CBF and the various tuning parameters, see the System Administrator Guide.

Information on the correlation between 6.4 and Ingres parameter names, is described in Corresponding Parameter Names.

Derived parameters are recalculated when values they depend on are changed. If derived parameters are set, they can be "protected" against change.

Ingres versions from 2.0 through 2.6 may calculate very large default lock and resource limits parameters. Check the lock_limit and resource_limit settings, and consider reducing these limits to the Ingres 6.4 settings.

On OS-thread platforms, do not turn on async_io; and do not declare the II_NUM_SLAVES Ingres variable.

Ingres supports larger qef_sort_mem values than Ingres 6.4. Ingres may not need as much qsf_memory as did Ingres 6.4. OS-thread platforms should not reduce quantum_size, as it does not improve performance on those platforms.

Recreate Users, Groups, and Roles

Note: This step is required only for a clean-installation upgrade.

If your 6.4 installation has only a few Ingres users defined, you should use the accessdb utility or the CREATE USER SQL statement to recreate those users in the Ingres installation. As a guide, use the file users.out or refer to the 6.4 installation.

If you have many users, the following procedure recreates them in mass.

As the installation owner, change directory to your iidbdb unloaddb directory where you stored the files from the step Save Users, Groups, and Roles.

Run this SQL:

sql '-u$ingres' iidbdb
copy iiuser(name=c0comma,status=c0comma,default_group=c0nl)
from 'users.out'
\go
update iiuser
set default_priv = status, user_priv = status,
flags_mask = case when default_group <> ' ' then 28 else 24 end
where user_priv = 0 and flags_mask = 0;
\go
copy iiusergroup(groupid=c0comma,groupmem=c0nl)
from 'groups.out'
\go
commit
\go
\quit

Windows: Omit the quotes from the sql command line.

Ingres has new user privileges that do not exist in 6.4. If you recreate users using the above bulk load procedure, you should review the added users with accessdb to make sure that all user privileges are set the way you want them. In particular, review the definitions for any 6.4 "superusers."

Ingres handles the "update system catalog" privilege differently than did 6.4. You must explicitly grant this privilege to the Ingres user after you recreate it, with a grant statement, as follows:

grant update_syscat on current installation to user-name

If your 6.4 installation had roles defined, recreate them with the ADD ROLE SQL statement. Use the file roles.out as a guide. Roles cannot be reliably bulk-loaded from the 6.4 installation, so you must recreate them by hand. After you recreate each role, issue the following SQL statement:

grant rolename to public; commit

This allows the role to be used in the same manner as in 6.4.

Recreate Database Privileges

To recreate database privileges:

1. As the installation owner, change to the iidbdb unloaddb directory.
2. Refer to the file dbaccess.out created in the step Record Database Privileges.
3. Start an iidbdb Terminal Monitor session:

   sql iidbdb

4. For each database and user combination listed in dbaccess.out, issue the statement:

   grant access on database database-name to username; commit

5. Review the file dbprivs.out created in the step Record Database Privilege.

   Each row describes one or more database privileges given to the user grantee-name. A Y or N in a privilege column indicates the specific privilege. (A U in a column means Unchanged.)

6. For each row, issue the statement

   grant privilege on database database-name to grantee-name;commit

   If the privilege column is N, grant noprivilege instead of privilege.

7. When finished, use \quit to exit the iidbdb session.

If you have defined many privileges, or recreated many users, groups, or roles, you should run sysmod on the iidbdb, which will accelerate query processing. Issue the sysmod command, as follows:

sysmod iidbdb

Fix FE Reload Script

Because the new database was not created with front-end catalogs, it is not necessary to drop them.

To fix the front-end reload script, for each database:

1. Open the file cp_ingres.in.
2. Delete the following lines:

   \include/ing64/ingres/files/iiud.scr
   \include/ing64/ingres/files/iiud64.scr

   Note: The directory path may differ.

3. Save the file.