Does this site look plain?

This site uses advanced css techniques

This procedure is not supported by iSystems.

You must have a familiarity with Linux, as well as good backups, before using this tool. However, a number of Service Bureaus have used this with success in the Garfield and Irasburg releases.

Evolution Logo

The Evolution payroll service-bureau system by iSystems sees a new major release once or twice a year, and there are almost always substantial database patch processes involved. Several Evolution releases ago, we modified Pat Warn's original perl program used to patch the databases to be much more intelligent, and it was apparently sufficiently so that iSystems adopted it for the Franklin upgrade.

Table of Contents
  1. Setting up a patch area
  2. Fetch the program
  3. Set up command $PATH
  4. Setting up the SQL Patch Files
  5. Getting ready to patch
  6. Backing up and restoring the DB files
  7. Running patches more than once
  8. Firebird Database users
  9. Command-line Reference

Inexplicably, the Garfield upgrade introduced a python script, patch.py that we consider a distinct step backwards.

Unhappy with this, we continued to revise our own program for patching. We and a number of service bureaus have used it quite successfully, and we've used it with Irasburg as well (Hartland didn't require any per-client patches, so this wasn't needed). We're making it available to others who care to give it a try.

Our script patches all the database files in-place (including TMP_TBLS and S_BUREAU, has robust error checking, and will use all the CPUs available in the machine automatically.

While running, it makes an ongoing estimate of completion time into the logfile, and it's usually good enough to plan around dinner/sleep time.

Jamaica uses the same EvolUDF database files as Garfield, Hartland, and Irasburg so no updates should be required on this front.

Setting up a patch area

IMPORTANT - work on a copy of the data only! This can be done by working on a spare area (as root). Shut down all the package servers first so they "let go" of the databses (the programs must exit, not just [STOP]), then create a new directory to do the patching in: 

# mkdir /db/patch-Jamaica
# cd /db/patch-Jamaica
# cp -v ../evolution/*.gdb .

The last command (copy) will take some time depending on how many database files there are to work with, though the -v option will show each file as it's copied.

Though it's possible to run with just /db/patch, adding the name of the release lets you keep some of the files lying around for the next release to remind you what you did. It's really common to revisit the little helper scripts or output logs to see what worked last time.

Fetch the script

Next, download evo-patch-Jamaica to the Linux DB server, and this can be done from the command line: 

# cd /db/patch-Jamaica
# wget http://www.unixwiz.net/evo/evo-patch-Jamaica
# chmod +x evo-patch-Jamaica        make sure it's executable

This fetches the file and leaves it in the current directory. We also make sure that it's executable so it may be run as a command.

Set up command $PATH

The script uses the isql and gbak programs, both of which are part of the Firebird suite, and they're typically found in the /opt/firebird/bin/ directory. Previous versions of this patch program required putting this directory in the command search $PATH, but this version adds it automatically.

However, it's a good practice to put this in the $PATH anyway, because it's common to perform system-administrative tasks by using isql or gbak by hand. The best way to achieve this is to edit /etc/profile and look for lines of the form:

part of /etc/profile
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin

Simply add pathmunge /opt/firebird/bin, save the file, then logout and back in so that it takes effect. Alternately, one can manually put a directory into the path at the command line, though this is forgotten when the user logs out: 

# export PATH=/opt/firebird/bin:$PATH

Setting up the SQL Patch Files

The patch process also requires the actual patch files, which are fairly large collections of SQL commands which update the individual database files. They are available on the iSystems SB Update site in several forms, but ultimately there are three files.

Always check for the latest versions before patching, but as of this writing, the files are:

These can be downloaded individually or in a single Linux bundle file, but ultimately they all must be placed on each database server which is to run the patching operation.

Though one can download from a Windows system and transfer to the DB servers. we prefer to fetch the files directly from the iSystems site to the Linux machines. Again turning to the wget command, we can grab the Linux patching bundle in a single tarball, unpacking it after receipt: 

# wget --http-user=user --http-passwd=pass \
    http://is1.isystemsllc.com/sbupdate/8.0_Jamaica/DB_Patch_Scripts/Jamaica_Linux_db_patches.tar.bz2

# tar -xjvf Jamaica_Linux_db_patches.tar.bz2

For user and pass, fill in your standard service-bureau login for the iSystems sbupdate website or FTP site.

Remember that all database servers must have the patch files, and you should always check the iSystems SB Update site for the latest versions of the patch files — it's easiest to right-click on the URL in the web browser and paste it into the terminal session on the wget command line.

Note that the Windows and Linux versions of the patch script are not entirely identical: though the main SQL is the same, the Windows versions have an introductory CONNECT statement at the top:

delete if found in any SQL file
CONNECT "C:\ISystems\Evolution Support\Databases\Irasburg\CL_5.gdb"
USER "SYSDBA"
PASSWORD "pps97";

If this is present at the very start of the file, it must be removed (we typically use the vi editor). evo-patch-Jamaica attempts to detect this condition by inspecting the patch files, but it's a really good idea to check the .sql files first.

Getting ready to patch

The evo-patch-Jamaica program has a --help option, though most won't be used, and one can see what the program would do without actually doing it by using the --noexec option. 

# cd /db/patch-Jamaica

# ./evo-patch-Jamaica --dbpath=. --noexec

This does not patch the files; it merely shows the commands that will be used when it's run "for real". As it runs it makes an estimated time of completion, though of course with --noexec this is meaningless. One can also add the --verbose option one or more times to get additional runtime detail.

In order to actually do the patching, the --noexec option must be omitted, but we prefer to take one extra step: we put the instructions for running the program in a small shell script so we have a record of the options used - this is helpful when running it more than once for testing. We usually call it "runpatch", and create it with vi:

the "runpatch" script
#
# runpatch - patch Jamaica database files
#
exec ./evo-patch-Jamaica --dbpath=/db/patch-Jamaica --verbose

Of course, make sure that the --dbpath parameter points to the place you're actually working if different from /db/patch-Jamaica.

Now it's time to start patching. Once started, it really should not be interrupted, and this includes hangups due to network problems if run via a telnet or ssh session. To obviate this, we typically use the nohup command to run the script, and it "hides" the program from interruptions of this sort. By routing the output to a file, we can watch it even if we get disconnected and reconnected: 

# rm -f nohup.out

# nohup sh runpatch &           — run in the background

# tail -f nohup.out             — watch ongoing progress

The program keeps track of the number of errors found, and reports them upon completion of the job. If any are found, examine the .log files to get an idea what the problem is, correct, and set up to run the patches again (see the last section of this Evo Tip for information on how to do this).

NOTE: if there are multiple DB servers used to spread the load, only one system will have the system files (S_BUREAU, and TMP_TBLS) — the others will have CL_ client files only. The --clients-only option should be used on those system so the patch script won't fail due to missing systemfiles.

Backing up and restoring the DB files

Normally, evo-patch-Jamaica performs a full backup and restore (using the gbak command) of each database file after patching: this is a step that iSystems recommends to get rid of deleted space in the database tables, making them smaller. It's a great idea, though time consuming.

Users who wish to test the patch process before doing it live may wish to skip this step in order to save time and make sure that the rest of the process is working properly. The --nosweep parameter to the end of the command line in the runpatch script.

the "runpatch" script
#
# runpatch - patch Jamaica database files
#
exec ./evo-patch-Jamaica --dbpath=/db/patch-Jamaica --verbose --nosweep

Running patches more than once

Any individual database file may be patched one time only, but it's not out of the question that one might wish to test the whole patch process a few times to get the kinks worked out (missing patch file, forgot to run as root, /opt/firebird/bin not in $PATH, ran with wrong parameters, etc.).

To allow for this, it's wise to test with just a subset of representative files. This can be done by copying only a few of the CL_###.gdb files (along with TMP_TBLS and S_BUREAU) into the patch area. Again, we usually do this with a script so that it's easy to reproduce:

the "getfiles" script
#
# get subset of DB files for test patching
#
cd /db/patch-Jamaica

cp -v ../evolution/TMP_TBLS.gdb .
cp -v ../evolution/S_BUREAU.gdb .
cp -v ../evolution/CL_BASE.gdb .
cp -v ../evolution/CL_1??.gdb .		# adjust to local taste

The last line should be designed to copy just a few of the files to the patch area, rather than all of them, and the exact text of the line depends on the format of local files. In the example above, this fetches all client DB files from CL_100 .. CL_199. Pick more or less files to taste.

Furthermore, when doing this testing it's probably best to use the --nosweep parameter to speed up the process.

This suggests that running a test patch is done with: 

# cd /db/patch-Jamaica

# sh getfiles

# rm -f nohup.out

# nohup sh runpatch &

# tail -f nohup.out

Firebird Database Users

This program talks directly to the Firebird database files, and it has no knowledge of any Evolution usernames. Evolution uses two Firebird database users: SYSDBA and EUSER. The former is the default database superuser, while the latter is used strictly by Evolution.

The patch process is typically done with SYSDBA, as is the database backup, but the restore is done with EUSER: this is a tricky relationship which must be gotten right, or there will be ugly permissions issues down the line.

The program normally runs with the standard usernames and passwords, but the Evolution Service Bureau which has changed them from the default must edit the program to reflect the local password choices. This bit of code near the top of the program should be edited by hand: 

# These define the credentials for the various operations: patch, backup, and restore.
my $patchuserinfo   = " -user SYSDBA -pass password";	# run the patch
my $backupuserinfo  = " -user SYSDBA -pass password";	# backup the DB
my $restoreuserinfo = " -user EUSER  -pass password";	# restore the DB

Only change the passwords, not the usernames!

Previous versions of this program provided command-line parameters to set the database user and password, but since Garfield introduced the EUSER account, it seemed to be overkill to provide this on the command line. Just edit the source.

Command-line reference

The program takes many command-line parameters, most of which won't ever be used except testing. But, for completeness, we list them all here.

--help
Show a brief help listing, then exit with success status
--dbpath=DIR
Set the database directory pathy to DIR, and this option is required. Common values are /db/evolution or /db/patch-Jamaica, but we won't ever pick a default for you — it must be specified explicitly on the command line.
Note that it's possible to run with --dbpath=. (with a dot) to point to database files in the current directory.
--nthreads=N
Set the number of "threads" (really "subprocesses") to N. This is normally set automatically by the program by examining the running system (it looks in /proc/cpuinfo). The default is normally optimal, but it can be changed by hand if necessary.
--no-chown
Normally, the entire database working directory has its owner and group changed to firebird:firebird, as this is required to run Evolution, but this option suppresses that change-owner operation. We don't know why anybody would ever want to not change the owners.
--limit=N
Normally, this program patches all appropriate files in the database directory, but for testing it's possible to limit the run to just N client files only. This would never be used for production patching.
--clients-only
Normally, this program patches both the client files (CL_xxx) and the system files (S_BUREAU and TMP_TBLS), but this is not appropriate for systems with multiple database servers which keep the system files on one server only. This option suppresses the system database patching.
--patch-dotclients
Some service bureaus move deleted client files out of the way by renaming the database files from CL_xxx.gdb to .CL_xxx.gdb: this makes them invisible to Evolution but nevertheless keeps them nearby and easy to restore. These deleted databases are not generally required for Evolution operations in the short term, but it's common enough that they may be required someday in the future.
--sysfiles-only
This patches only the TMP_TBLS.gdb and S_BUREAU.gdb files: no client files are touched. This is really only useful when you accidentally ran with the --clients-only option on a DB server that actually has the system files on it: this lets you make a final cleanup pass to patch those system files.
The best strategy for using this option is to omit it during testing and trial runs, because the files aren't really needed to insure that Jamaica is running properly, but to include it during the final patch run.
--verbose
Add a bit more runtime debugging information without changing the actual execution flow of the program. This option can be provided twice for even more information (it's quite verbose).
--addpath=DIR
Add directory DIR to the command-execution $PATH. This should not normally be necessary, because the most common path to add (/opt/firebird/bin/) is added automatically by the program at startup, and the current environment should have this set up anyway, but it's available should it be necessary.
--noexec
Show what commands we would execute, but don't actually do them: this is a readonly operation which can be used to find out what the program will do for real, but without actually executing anything. This is very useful for learning about the program.
--exiterror
Normally, this program continues running even if it finds an error, because it's not entirely uncommon for there to be an error while patching one of the client files. The idea is that the patch process should run to completion, and errors examined after the fact.
But for testing, it's common to have errors show up immediately because of a bad parameter, missing directory in path, permissions problems, or other issues. By providing the --exiterror parameter, this program stops all work when it finds an error, though it will wrap up any work in progress by other threads before exiting.
--nosweep
Normally we do a full backup and restore (a "sweep") of each database file after patching, as this is a recommended step to get rid of the dead space in the files, but this adds substantially to the time required for patching. This option skips the backup and restore operation entirely (you should nevertheless do it later).

Feedback on evo-patch-Jamaica or this Evo Tip is welcome.

2005/07/13: First published
2006/04/24: Updated for Irasburg
2006/11/27: Updated for Jamaica