Does this site look plain?

This site uses advanced css techniques

Note - Though iSystems has adopted this scripting technology with the Killington upgrade, they don't support this version, which sometimes has changes not reflected in theirs. We always use our own version for patching our SB customers, as we have since at least the Charlotte release of Evolution.
URGENT - there have been two versions of the patch script, and it's vital to know which one you're using. The original Killington patch was applied on the older DB system with Firebird 1.5, but the new one is done after the Firebird 2.0 upgrade. You must know which version you're working with!
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. The original code for patching did the files one at a time, and it made patches take much too long while unused CPU cores sat idle. With some db servers running dual quad-core processors, this is a lot of wasted time.

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. Post-patch considerations
  10. Command-line Reference

This script uses what amounts to multiple threads to use all the available resources on the system, and it's dramatically faster than the sequential methods. It can optionally backup or sweep the files after patching, which is often a required step, fully integrated in the multithreaded engine. It also offers much better error checking and recovery.

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

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.

Killington uses the same EvolUDFs as Garfield, Hartland, Irasburg, and Jamaica 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-Killington
# cd /db/patch-Killington
# 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. We almost always refer to previous patch directories when doing a new one.

Fetch the script

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

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

Those who have followed our Setting up a Linux Evolution database server Evo Tip probably don't need to take this step, as this is included in the initial setup of a DB server.

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 usually three files.

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

These are all delivered individually from the Patch_Scripts/ subdirectory of the Killington upgrade directory (they have previously been offerred in a single tarball, but no longer appear to be).

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: 

# cd /db/patch-Killington
# wget --http-user=user --http-passwd=pass \
    http://is1.isystemsllc.com/sbupdate/9.0_Killington/Patch_Scripts/sb_9-0-0.sql
# wget --http-user=user --http-passwd=pass \
    http://is1.isystemsllc.com/sbupdate/9.0_Killington/Patch_Scripts/tmp_9-0-0.sql
# wget --http-user=user --http-passwd=pass \
    http://is1.isystemsllc.com/sbupdate/9.0_Killington/Patch_Scripts/cl_9-0-0.sql

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.

The patch files as provided may have a CONNECT statement at the top, which must be removed prior to patching:

delete if found in any SQL file
CONNECT "dbserver:/db/evolution/TMP_TBLS.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-Killington 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-Killington 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-Killington

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

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

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).

IMPORTANT NOTE: if there are multiple DB servers used to spread the load, only one server 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.

the "runpatch" script for DB servers without S_BUREAU and TMP_TBLS
#
# runpatch - patch Killington database files
#
exec ./evo-patch-Killington --dbpath=/db/patch-Killington --verbose --backup --clients-only

Backing up and restoring the DB files

Prior versions of this script automatically performed a full sweep of the database files (a backup and restore using the gbak command), but this is no longer the default behavior. Because Killington requires a backup only, all backup and/or restore operations must be requested explicitly with command-line options.

Adding --sweep does the full backup and restore, while --backup does just the backup portion, leaving the *.gbk files in the patching directory.

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. Simply omitting the requests for a sweep or a backup means that the patch fille means it does patching only.

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

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-Killington

cp -v ../evolution/TMP_TBLS.gdb .
cp -v ../evolution/S_BUREAU.gdb .
cp -v ../evolution/CL_BASE.gdb .
cp -v ../evolution/CL_100?.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_1000 .. CL_1009 (just ten files). Pick more or less files to taste.

Furthermore, when doing this testing it's probably best to omit the --sweep or --backup options to speed up the process. It's common to run into all kinds of little issues, and it's sure helpful to find them out without having to rerun a thousand client files.

This suggests that running a test patch is done with: 

# cd /db/patch-Killington

# sh getfiles

# rm -f nohup.out

# nohup sh runpatch &

# tail -f nohup.out

Firebird Database Users

Firebird usernames
Users wishing more information on Evolution versus Firebird users may consult our Users and Passwords in the Evolution System Evo Tip.

This program talks directly to the Firebird database files, and it has no knowledge of any Evolution usernames. The Evolution program 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.

Post-patch considerations

This section is still in progress

The Killington upgrade includes not just updated code and SQL, but requires a an upgrade to the underlying Firebird database system, from 1.5 to 2.0. This requires that the files be backed up with the old system and restored using the new system, and this suggests two possible sets of procedures:

  1. patch WITHOUT using the --backup option
  2. run a full backup from *.gdb to *.gbk
  3. upgrade database software to Firebird 2.0
  4. do a full restore from the *.gbk files

or

  1. patch WITH the --backup option, leaving the .gbk files behind
  2. upgrade database software to Firebird 2.0
  3. do a full restore from the *.gbk files

Our feeling is that the second approach is going to be the most efficient, because the time-consuming backup operation is done in parallel by the multithreaded patching engine.

If patching with the --backup option, disregard any other "backup all your databases" instructions

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-Killington, 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.
--skip=F
Omit patching of file F (no path or extension required), which is required to fine-tune patching when the S_BUREAU and TMP_TBLS files are not on the same machine. On machine one, --skip=TMP_TBLS would be used, while on the other one --skip=S_BUREAU. It's not expected that many service bureaus would need this option.
--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 Killington 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.
--sweep
Do a full sweep (backup and restore) of each database after patching, as this is typically a recommended step to get rid of the dead space in the files. However, it adds substantially to the patch time, and it can sometimes be skipped while testing.
However, this option should not be used for Killington, as a sweep is not necessary. Use the --backup option instead.
--backup
Run a Firebird database backup on each file after patching. This is the first half of a sweep, and was introduced for the Killington release.

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

2005/07/13: First published for Garfield
2006/04/24: Updated for Irasburg
2006/11/27: Updated for Jamaica
2007/05/26: Updated for Killington