1. Stop all batch cron entries by user 'ingres'. Simply comment these out. Request database owners on upgraded host to do so with their cron entries.
   
   
2. On the host to be upgraded completly remove ~ingres/II/scripts then refresh this directory from bucket.

3. Comment out the iistartup command from the hosts automated start up script: /sbin/init.d/ingres. Root access is needed for this.
   
   

Close the servers and then remove all user processes to prevent subsequent user access.

Record the existing journaling status of every database in the installation and then checkpoint all databases turning journaling off.

This can best be done using the 'recording' feature provided in the checkpoint script:

These checkpoints will be used for recovery in the event of upgrade failure. Furthermore, the file of existing journal statuses will be used at a later stage to restore the databases journaling status.

pre_upgrade.pl -p . The script will ensure that the following steps are performed upon each database in the installation:

1. Statistics are saved and then dropped. The saved statistics are kept in the 'statistics' subdirectory of the Work Directory as '<dbname>.stats'. The statdump log for each database is also stored in subdirectory 'statistics' of the Work Directory as 'statdump.<dbname>.log'.
   
2. Drop all grants, views, rules, procedures and dbevents.
   
   1. The copy.in script prepared by the prior unloaddb will be massaged to make an sql script copy.in.create to be executed at a later stage to reload all these items.
      
   2. A log of sql activity is kept in each databases unload directory and is named 'drops.sql.log'.

Should the process fail during or after the dropping of grants etc then if necessary they can be reloaded by use of the copy.in.create scripts.

Furthermore the following steps are taken to help in the recovery of the OI1.2/01 Installation should the upgrade fail.

1. Save the current data and dump configuration files of each database into subdirectory 'config' of the Work Directory.
   
2. Save the catalog 'iifile_info' from each database into subdirectory 'iifile_info' of the Work Directory.
   

   The following scripts are all created in the 'recovery' subdirectory of the Work Directory. They are used only if the upgrade to IngresII fails and a retreat to OI1.2 becomes necessary.
   

   1. replace_config_files.sh; Used to restore all the saved data and dump configuration files to their correct locations.
      
   2. recover_databases.sh; Used to control the recovery of all databases from checkpoint.
      
   3. statistics.sh; Used to reload the saved statistics into each database.

And, as an added bonus the following scripts are prepared dynamically to aid in the installation of IngresII.

1. The script 'upgradedb_<Inst>.sh' (where <Inst> is the installation identifier) is created in the scripts area. It will be used to help in the execution of the 'upgradedb' command on all databases in a later step.
   
2. The script 'save_existing_setup_<Inst>.sh' is created in the scripts area. It will be used to save the existing OpenIngres1.2 files under $II_SYSTEM/ingres (excluding silly things like the log etc) and to ensure the appropriate terminal definition and map files are available for the screen based install program.
   
   

Save existing (OpenIngres 1.2) Setup.

1. Use CBF to record the nature of the current log file. Note its size, block size, location and whether it is cooked or raw. Also note if Dual Logging is in effect.

Install IngresII.

1. Copy the distribution media to <Wherever>. Then:
   
   1. cd $II_SYSTEM/ingres
      
   2. tar xvf <Wherever/name_of_distribution_media> install.
      

      Note that the distribution media is required by the interactive install program so do not remove it until installation is completed.
      

2. As root
   
   1. define and export II_SYSTEM
      
   2. cd $II_SYSTEM/ingres
      
   3. execute install/mklicense.
      
   4. copy bucket:/ca_lic/ca.olf to /ca_lic on this host
      
   5. Chmod 644 /ca_lic/ca.olf
      
      
3. The install directory created includes termcap and map files used in the forms based install utility, we assume that this is a more uptodate set than the OpenIngres 1.2 set but that it will not include the asis termcap entries and map files, so:
   
   1. Ensure the asis.termcap entry is available in the files directory.
      
   2. cp install/termcap files/ingres.termcap
      
   3. cp install/*.map files, (Install any newer ingres key maps)
      
   4. cat files/ingres.termcap files/asis.termcap > files/termcap
      
   5. cp files/termcap install
      
   6. cp files/*.map install, (Now provides the asis keymaps as well)
      
4. Execute command install/ingbuild. Then follow the steps listed for the Interactive Mode described in the Ingres II Getting Started manual.
   
   1. Package Install - Select Ingres Stand Alone DBMS Server
      
   2. Install - Use the standard Install option.
      

      The IngresII Utilities were created for version 3.2 of the OS. They will break under v4.0D. Hence, after the binaries are unpacked and before the installation proceeds you must install the latest IngresII patch. The ingbuild program will prompt for continuation permission with a popup menu when this point is reached. The menu will display the lines:

      All requested products have been successfully installed.
      

      One or more of these products requires setup.

      At which point, on a separate session:
      

      • Ensure the file $II_SYSTEM/ingres/files/config.dat exists. If not, then touch it. (Presumably it doesnt exist because this is a fresh installation and not an upgrade! If not then check you have completed step 6.a correctly!) This file is required by the patch install program to convince it to use IngresII routines rather than Ingres6.4 equivalents.
        
      • Install the latest patch to IngresII following the standard procedure laid out in the document:
        

        ~ingres/docs/Patch_Details

        In particular, ensure that the sed fix to mkrawlog and iisudbms are completed. Do not restart IngresII when that document says to.

      Having completed the patch install, return to the ingbuild program and select 'Execute setup programs now' at the popup menu.

      During the 'Setup Phase':
      

      • Activity is logged in $II_SYSTEM/ingres/files/install.log
        
      • Set connected sessions to the value of 'connect_max' in the old config.dat file (see printout or backed up copies)
        
      • Do NOT configure a Backup Transaction Log file.
        
      • If the existing OI1.2 Transaction Log File was cooked then configure a cooked log of at least the same size. For fresh installations you might like to select a 250M cooked log.
        
      • If the existing OI1.2 Transaction Log File was a raw device, then the program will detect this and reconfigure the log for IngresII.
        
      • Do NOT comply with SQL-92 standard.
        
      • On fresh installations, ingbuild will create and checkpoint iidbdb and imadb.
        
      • On Upgrades, ingbuild will prompt when it is time to upgrade the databases. Do NOT select the upgrade option at this point! As it may take a very long time and can be performed by script at a later point. Ingbuild will still upgrade iidbdb. To do this it will attempt to start IngresII. It will also perform a ckpdb +j iidbdb. Rmcmd objects will be installed in the iidbdb and imadb will be created. Note: the initial drop of rmcmd objects will fail but the install will then proceed regardless.
        

1. Use ipcs and ipcrm to detect and remove any shared memory and semaphores in use by Ingres. Use caution at this step.
   
   
2. Use CBF to:
   
   1. If a raw log was setup then reconfigure the rawlog.
      
   2. Alter the startup count of the Remote Command Server to 0. This is absolutely required or all following upgradedb calls will stall attempting to get a lock on iidbdb. The rmcmd maintanes a connection to the iidbdb.
      
   3. Ensure Security Auditing is OFF (Screen: Configure Security Auditing)
      
   4. Remove Bridge, Net and Star Servers. (Probably won't be present anyway!)

3. Alter the Ingres environment with:
   
   1. ingunset II_AUTH_STRING
      
   2. ingsetenv II_NUM_SLAVES 0
      
   3. ingsetenv II_THREAD_TYPE OS

Having done this attempt to start Ingres, monitoring the errlog.log for any errors during startup.

For the other databases in the installation execute the script 'upgradedb_<Inst>.sh' prepared by the execution of the pre_upgrade.pl program. Note:

1. This script may be run from cron or in background if required.
   
2. Each databases upgradedb activity is logged in the 'upgradedb' subdirectory of the Work Directory as <dbname>.log . The script cats this log file and this should inform you if the database was promoted to IngresII or not. If not then inspect the log for any errors.
   
   

NOTE:The most common cause of errors is a failure installing details for Windows_4GL and VISION. Errors are duplicate key inserts. Easiest way to fix these are with:

Sql dbname << SQL_END

Delete from ii_client_dep_mod where client_name in ('WINDOWS_4GL', 'VISION') and client_version=2

SQL_END

If a subsequent upgradedb fails to solve the problem then execute with II_EMBED_SET to save queries to a file and analyse the output in that file.

1. Ensure databases that were private are still private, ditto public databases. Use data saved in the iidbdb.basics section of the Basics File to confirm this. Check/make changes with accessdb.

In the event of a failure during the upgradedb process that cannot be rectified perform the following actions. Otherwise continue to the next step as the Upgrade has succeeded!

1. The installation will have to be returned to an OpenIngres 1.2 installation. This requires:
   
   1. Shutdown the IngresII Installation with ingstop.
      
   2. Reinstate $II_SYSTEM/ingres from the backup.
      
      1. cd $II_SYSTEM/ingres; rm -rf files bin utility lib sig
         
      2. tar xvpf $II_SYSTEM/OI1.2.tar
         
   3. For every database, reinstate the saved configuration files from the Work Directory config area. Do this using Work_Directory/recovery/replace_config_files.sh
      
   4. Restart OpenIngres 1.2.
      
2. Recover the databases. Do this using the script Work_Directory/recovery/recover_databases.sh
   
3. Restore the statistics. Do this using the script Work_Directory/recovery/statistics.sh
   
4. Checkpoint turning journaling on (where appropriate). Do this with the commands:
   
   1. ckpdb -v +j iidbdb
      
   2. ~/scripts/perl/checkpoint.pl =j/~/II/work.<inst>/journal_status
      
5. Reinstate the ingres and asis cron entries, plus the automated startup command.
   
6. Report the failure to CA.
   
7. Do not continue past this point!

The OpenIngres 1.2 environment variables are retained by the upgrade process and should be acceptable. If this is a fresh installation then set as follows (note the setenv on the II_PARAM_PASSING)

1. ingsetenv II_DATE_FORMAT DMY
   
2. ingsetenv II_DMFRCP_STOP_ON_INCONS_DB Y
   
3. ingsetenv II_NUMERIC_LITERAL float
   
4. ingsetenv II_DATE_CENTURY_BOUNDARY 33
   
5. setenv II_PARAM_PASSING FORCEMAX
   

To allow 6.4/05 \nocontinue like go_block processing in IngresII set the new environment variables as follows:

1. ingsetenv II_TM_ON_ERROR nocontinue
   
2. ingsetenv II_TM_SWITCH true
   
   

Furthermore,

1. If II_NUM_OF_PROCESSORS > 1 then also set:
   

   II_MAX_SEM_LOOPS 2000
   

   (Recommended by David Quigly to improve process swapping)
   

2. ING_SYSTEM_SET, - will require you to create the global file as per other hosts.
   
3. II_EMBED_SET "programquit;dbmserror"
   
4. II_PATTERN_MATCH sql
   

Use CBF to set:

NOTE:The upgrade process will largly retain the existing OI1.2 configuration. Use the printout of the OI1.2 config file to ensure that the new servers are configured as before. The major exception here being that under OI1.2 we often had several servers sharing a cache. Under IngresII we intend to take advantage of the OS thread capacity. Hence we will configure a single sole server to take the place of all servers on this cache. Specific database servers will be consumed by the new sole server as long as they shared the same cache. The new sole server must allow for an appropriate amount of user connection.

Having set the following parameters, shutdown and restart IngresII checking for any errors in the error log.

DBMS Server Parameters:

Ensure each servers startup count is set to 1 before editing its configuration as many derived parameters are calculated on this.

1. Async_ioOFF
   
2. Cache_Sharing OFF
   
3. Connect_Limit 500 on production Hosts 100 on Development Hosts
   
4. Default_Journaling OFF
   
5. Default_Page_Size 2048
   
6. Log_esc_lpr_sc ON
   
7. Log_esc_lpr_ut ON
   
8. Log_esc_lpt_sc ON
   
9. Log_esc_lpt_ut ON
   
10. Log_readnolock OFF
    
11. Qef_maxmemf 50
    
12. log_writer 4
    
13. Max_Tuple_Length 2008
    
14. Qef_qep_mem = old_value * 10
    
15. Qef_sort_mem = old_value * 10
    
16. System_MaxlocksSame as maxlocks value set in ING_SYSTEM_SET global file.
    
17. System_Readlock Shared
    
18. System_Isolation Serializable
    
19. Write_behind 4
    

1. Database_limit set as per prior config.dat
   
2. Define_address ON

DBMS Database parameters: (As per prior installation)

DBMS Cache Parameters:

Configure 2k Cache only. Ensure all others are turned OFF

1. Dmf_group_size 128(This is probably a radical departure from previous installation value. It will effect the locking system parameters default values. However, word of mouth is good on this change)
   

Locking System Parameters:

Defaults probably acceptable, Simply check the overall number of locks per transaction is still 2000.

Logging System Parameters:

1. Buffer_Count = old_value * 2
   
2. (Derived) database_Limit as per that set in DBMS SERVER)
   
   

Recovery Server Parameters:

1. Stack size 131072 (as per that in dbms server)

   If the value of log.block_size was altered then shutdown OpenIngres and use CBF to reformat the log file. Then restart OpenIngres and check for errors in the error log.

Check the following files in $II_SYSTEM/ingres/files.

1. abflnk.opt.Ensure there is a line '-lrt -lpthread' in the file. If not, put it in.
   
2. abfdyn.opt.Ensure there is a line '-lrt -lpthread' in the file. If not, put it in.
   
3. utld.def.Ensure the cc call includes a '-lrt -lpthread' link. If not, put it in.

Run the following scripts:

1. 'post_upgrade.pl'. This will act on all databases and ensure that:
   
   1. Changes to files in $II_SYSTEM/ingres/bin:
      
      1. ingprenv1. This utility no longer exists in IngresII. It has been replaced by parameterizing the ingprenv call. However many of our scripts assume its existence so a link is installed ie. ln -s ingprenv ingprenv1
         
      2. ipm. Install a set-uid bit ie: chmod u+s ipm
         
      3. catalogdb. chmod 755 catalogdb
         
   2. Changes to files in $II_SYSTEM/ingres/files:
      
      1. acpexit to only send last 500 lines of iiacp.log.
         
      2. cktmpl.def to perform compressed disk checkpoints for both databases and tables as well as allowing tape backups of multiple areas to a single tape.
         
      3. dictfiles/cor02min.mfy to speed up compilations add an extra secondary index to ii_objects.
         
      4. utexe.def to change the format of the parameter strings passed to report writer from being enclosed in double quotes(") to being enclosed in single quotes(').
         
   3. Changes to files in $II_SYSTEM/ingres/utility:
      
      1. ingstop. Replace all awk references with gawk.
         
   4. Catalog iiprotect is modifed to have minpages=64000. This should allow the following reload of grants to proceed rapidly on large databases. Note that the sysmod of the database performed by the later modify will return the catalog to normal.
      
   5. The dbevents, views, procedures, rules and grants are reloaded. Data in the unloaddb/<dbname> subdirectory of the Work Directory is used. This was created by the pre_upgrade.pl procedure. Log of this upload is kept as: Work_Directory/unloaddb/<dbname>/cp<user>.in.create.log
      
   6. Statistics are recreated by executing an optimizedb. The log of the optimizedb run on each database is kept as Work_Dir/statistics/optimizedb.<dbname>.log
      
   7. Check all databases are marked valid.
      
2. If time permits:
   
   1. ~/scripts/perl/modify.pl -ckpdb
      

      This will act on all databases and ensure that:
      

      1. All user tables are set to correct structure and secondary indexes recreated..
         
      2. Perform a sysmod.
         
   2. Checkpoint restoring journals status.
      
      1. ckpdb -v +j iidbdb (Should have already been done by the install, check and perform this if required)
         
      2. ~/scripts/perl/checkpoint.pl =j/~/II/work.<inst>/journal_status
         

1. manually sysmod all databases and then checkpoint turning journaling on where appropriate.
   

   eg. ~/scripts/perl/checkpoint.pl =j/~/II/work.<inst>/journal_status
   
   

Do not continue until you have verified that the modifies are free of error and that all subsequent sysmods have been performed. The modify script will indicate via email if there have been any such failures. Furthermore, ensure all the checkpoints have succeeded and have turned journaling on (where appropriate).

Reinstate the ingres and asis cron entries with the following alterations.

1. Remove the audit references from the asis cron.

Recompile applications and various utilities. Note that this may best be done by:

1. Remove all directories under ING_ABFDIR. Inform Team Leaders when this is done (if required).
   
2. Ensure that the Makefiles to be used (on DEC Alpha) use link -lrt -lpthread in their calls for Embedded SQL C programs. Ensure the Team Leaders are aware of this.
   
3. As user asis we must recompile:
   
   1. services by:
      
      1. Transfer the most uptodate Makefile from buckets ~asis/services. Then issue command: make services
         
      2. On the upgraded host ensure there is the most recent copy of ~asis/bin/asis_utils available.
         
      3. Then link ~asis/bin/services to ~asis/bin/asis_utils.
         
   2. audit by:
      
      1. cd ~asis/bin
         
      2. abf audit and create image in current directory
         
   3. its_run_application, its_group_update, normalise, permits, deft2sql
      
      1. Transfer the most uptodate Makefile from bucket:~asis/src/its_progs.
         
      2. Recompile by executing make all in ~asis/src/its_progs. This will compile each of these programs and then install the compiled code in ~asis/bin.
         
      3. Ensure that the current version of ~asis/bin/asis_utils is on the upgraded host.
         
      4. link each of the programs named above to asis_utils.

1. setup.sh, setup.csh:
   
   1. Consider DRP recovery installation. ie may need to be altered from 'te' to 'mu' for this host.

NOTE:Proceed to Appendix 1 for further work on the imadb

Appendix 1: Creating the imadb

The standard upgrade procedure above will not create the imadb. If a fresh install is performed the imadb will be created but we wish to alter some registrations and so some editing will be required.

1.If the imadb was not created then do so now with:

createdb -u'$ingres' imadb

2.Alter the table registrations with:

• cd $II_SYSTEM/ingres/vdba
  
• vi makimau.sql
  

  Alter the registration for table ima_dmf_cache_stats to read:
  

  register table ima_dmf_cache_stats (
  

  server varchar(64) not null not default

Is 'SERVER',
/* Add Page Size, suggested by Karl Schendel */

page_size integer not null not default

Is 'exp.dmf.dm0p.bm_pgsize',

3.If the imadb was created in step 1 then:
sql imadb < makimau.sql > mkimau.out

Examine the output file for errors ignoring codes E_US0965 and E_US0AC1.

Else

Cut out the SQL relevant to the altered registrations and execute that code.

Appendix 2. Trojan Horse Tape Checkpoint Script.

Steps 2, 3, 4a and 4b may be performed in place of the regular tape checkpoint. The advantage of which is that they will then be immediatly followed by a system dump (ie Step 4e). These tasks can then be performed in the early morning and leave considerable time to perform the rest of the upgrade.

Most of our hosts have only a single installation ('mu') and hence the tape checkpoint script is kept as $II_SYSTEM(mu)/checkpoint/totape.

The Trojan Horse Tape checkpoint script is ~/II/scripts/totape.def.

Simply:

2.1Save the existing tape checkpoint script.

2.2Copy the Trojan Horse over the existing tape checkpoint script ie.

cp ~/II/scripts/totape.def $II_SYSTEM(mu)/checkpoint/totape

2.3Edit the new totape (ie. Trojan Horse) to replace as follows:
i.<Inst> with the identifier of the installation to be upgraded.
ii.<Wherever> with the full directory path of the directory to hold the tar of $II_SYSTEM/ingres and the tar's log of activity.
iii.<Who_To> with the username of whoever is to receive the mail of upgrade activity.
2.4Ensure the new totape (ie. modified Trojan Horse) has permissions 755.

After the script executes ensure that the saved copy of the tape checkpoint script is restored.

Furthermore, the system dump that follows the tape checkpont may then be checked by examining the most recent log in /usr/local/etc/dumplogs.

If any section of the Trojan Horse or the following system dump fails then appropriate remedial action will need to be determined.

If we are granted access to the host well before the tape checkpoint cycle then an equivalent shell script may be used. Namely: ~/II/scripts/trojan_horse.sh.

This script is edited in the same fashion as for the totape.def script. Having completed the editing it may be executed in the background overnight. However, if this is the chosen mode of operation you will still have to ensure that a decent system dump was performed by the tape checkpoint.

Ingres Database Reference

To William's Home Page

© William Yuan 2000

Email William