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.

This Tech Tip is obsolete. We've written additional notes on patching for the Irasburg release.

Evolution Logo

The Evolution payroll service-bureau system by iSystems sees a new major release roughly once a year, and there has always been a substantial database patch process 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 Pat adopted it for the Franklin upgrade.

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

We've customized the script again for Garfield and have used it on several installations; we're making it available to others who care to give it a try.

Our script patches all the database files (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.

NOTE - the Garfield patch files (which are provided by iSystems) require the latest EvolUDFs. These are user-defined functions used by the database, and it will fail with many error messages of the form: 

Statement failed, SQLCODE = -104

invalid request BLR at offset 13
-function GETBLOBSIZE is not defined
-module name or entrypoint could not be found

Be sure that the EvolUDFs are installed in the /opt/firebird/UDF area before attemping to patch.

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
# cd /db/patch
# 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.

Fetch the script

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

# cd /db/patch
# wget http://www.unixwiz.net/evo/evo-patch-Garfield
# chmod +x evo-patch-Garfield        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

We also need to be sure that the /opt/firebird/bin/ directory is in the command search $PATH so that the gbak and isql commands are found. 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

The rest of this document assumes that the $PATH is set up correctly.

It's also required that the Garfield .sql patch files be in the working directory - these are obtained from the iSystems support site and unpacked from the ZIP or .bz2 file.

Getting ready to patch

The evo-patch-Garfield 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

# ./evo-patch-Garfield --dbpath=/db/patch --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 Garfield database files
#
exec ./evo-patch-Garfield --dbpath=/db/patch --verbose

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

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-Garfield 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 --skip-reload parameter to the end of the command line in the runpatch script.

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

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

cp -v ../evolution/TMP_TBLS.gdb .
cp -v ../evolution/S_BUREAU.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 --skip-reload parameter to speed up the process.

This suggests that running a test patch is done with: 

# cd /db/patch

# sh getfiles

# rm -f nohup.out

# nohup sh runpatch &

# tail -f nohup.out

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

First published: 2005/07/13