This article looks at After Image Journaling and audit trails in Recital using SET JOURNAL and associated commands.

Overview

After Image Journaling, used in conjunction with a structured backup policy is an effective disaster recovery solution. Any transaction that takes place on a table that is being journaled is logged in the journal file. In the event of a disk crash or similar event in which the table is lost, the journaled transactions can be reapplied to the latest backup copy of the table. Alternatively or additionally, the journal can be used to provide an audit trail to all modifications made to the table data.

NOTE: Recital also provides Before Image Journaling via BEGIN TRANSACTION / END TRANSACTION blocks, allowing unsuccessful transactions to be rolled back to a set saved state.

SET JOURNAL and RECOVER

Regular backups are an essential routine for any system, but in high-transaction environments restoration of the latest backup can still mean a major loss of data. After image journaling can successfully be used as part of your disaster recovery strategy to minimize data loss and down time. Recital after image journaling functionality is based on the use of the SET JOURNAL and RECOVER commands.

SET JOURNAL

SET JOURNAL TO [<.dbj filename> | ()]
SET JOURNAL ON | OFF | ()

The SET JOURNAL command is used to enable the After Image Journaling and audit trail for the active table. The TO <.dbj filename> clause associates the specified transaction journal file with the active table.  If the journal file does not exist, it will be created.  The filename can be substituted with a <expC>, enclosed in round brackets, which returns a valid filename.  If no file extension is specified, ‘.dbj’ is used. When specifying a journal file, it is recommended that the journal file is stored on a different disk than that which the table is stored on, so that if a fatal disk error occurs, then the journal file will not be lost along with the table. 

//Enable journaling for the southwind!orders table
open database southwind
use orders
set journal to /journals/ord_journ

The <.dbj filename> is a standard table.  It contains seven fields that are specific to a journal file, followed by the first 249 fields of the associated table.

The first seven fields in the journal are:

Field	Type	Display	Storage	Description
AUD_DATE	Date	8 | 10 *	4	The date on which the transaction was performed.
AUD_TIME	Character	8	8	The time at which the transaction was performed, in the format HH:MM:SS.
AUD_TERM	Character	12	12	The name of the terminal from which the transaction was performed
AUD_UID	Short	5	2	The ID of the user who performed the transaction.
AUD_GID	Short	5	2	The group ID of the user who performed the transaction.
AUD_CMD	Short	4	2	The command number of the transaction performed from the command table below
AUD_RECNO	Integer	7	4	The record number in the associated table which the transaction was performed on.

* Dependent on SET CENTURY setting.

The AUD_CMD Command Reference Numbers are as follows:

Command	Number
DELETE	14
RECALL	36
REPLACE	41
BROWSE	6
CHANGE	8
EDIT	17
INSERT	26
APPEND	5
READ	35

Since journal files are standard Recital tables, you can use standard Recital commands such as the REPORT command to print audit trails, transaction logs, etc.

//Enable journaling for the southwind!orders table
open database southwind
use orders
set journal to /journals/ord_journ
//.. transactions
close data
//View journaled records
use /journals/ord_journ.dbj

Click image to display full size

Fig 1: Journal Record Example.

The SET JOURNAL TO command without a <.dbj filename> specified closes the active journal file and no further journaling will take place on the active table until the SET JOURNAL TO <.dbj filename> is reissued.

The journaling features are mainly used with shared tables.  It should be noted that there is an overhead in enabling transaction journaling, as records updated in a table are also written to the journal file. When records are appended into a journal file, locking is automatically performed so that multiple users can update the journal concurrently.  The associated table must be opened shareable for this to occur.  Each table can have a journal file associated with it. 

The SET JOURNAL ON | OFF command enables or disables transaction journaling.  This command is primarily used in applications where journaling can be disabled for a certain class of operations. By default, SET JOURNAL is ON, but no journal files are set.

NOTE: Only the first 249 fields of a table can be journaled: subsequent fields are ignored. The maximum number of fields in a Recital table is 256.

RECOVER

RECOVER FROM <.dbj filename> | ()

The RECOVER command uses the journal file to reapply lost transactions to a previous backup of the data after a fatal error such as a disk head crash. The FROM clause specifies the journal file to use. The file name can be substituted with an <expC>, enclosed in round brackets, which returns a valid filename.  If no file extension is specified, then ‘.dbj’ is assumed. 

Regular backups are essential to the successful use of After Image Journaling.   It is also very important to reinitialize the journal file after each backup: either open the journal file as you would a normal table and use the ZAP command, or delete the file completely. If a fatal error occurs, such as a disk head crash, the table and index files must be restored from a backup, then the RECOVER command executed. RECOVER will reapply' all of the transactions in the journal file to the table, and update the indexes.  After the RECOVER command has completed, you can continue with normal processing. 

//Create a backup of the southwind!orders table
//...backup table and associated files
//Reinitialize the journal file
erase /journals/ord_journ.dbj
//Enable journaling for the southwind!orders table
open database southwind
use orders
set journal to /journals/ord_journ
//.. transactions
//Restore the backup of the southwind!orders table
//...restore
//Open the restored backup
open database southwind
use orders
//Reapply the transactions using the journal
recover from /journals/ord_journ.dbj
//Now, enable the journal file again or
//restart with a new backup

Journaling Memo Fields

By default, memo fields - variable length text fields - are not journaled due to the possible storage overhead of multiple copies of potentially large blocks of text. But, if memo journaling is required, the SET MEMOJOURNAL ON command can be used to enable this.

SET MEMOJOURNAL

SET MEMOJOURNAL ON | OFF | ()

The SET MEMOJOURNAL command causes memo fields to be journaled when journaling is set on a table. This command allows the optional logical expression <expL> to be evaluated.  If a value of  .T. is returned, MEMOJOURNAL is set ON.  If a value of .F. is returned, MEMOJOURNAL is set OFF.  By default SET MEMOJOURNAL is OFF.

Like a normal Recital table, the journal holds only a pointer to a data block in an associated memo file, not the actual memo data itself. The journal's memo file has a file extension of .dbm rather than the standard Recital .dbt. Therefore, if the journal is being opened as a table, in order to view the journal's memo data, the SET MEMOEXT command should be used.

//Enable journaling for the southwind!suppliers table
open database southwind
use suppliers
set journal to /journals/sup_journ
//.. transactions
close data
//Set filename extension for memo file
set memoext to '.dbm'
//View journaled records
use /journals/sup_journ.dbj

Summary

The After Image Journaling enabled by the SET JOURNAL and RECOVER commands can be used in conjunction with a strict backup regime to minimize data loss in cases where tables become damaged or irretrievable. Journal files can be accessed like standard Recital tables and provide detailed information about the transactions applied to a table, so can be used for auditing purposes.

To insert code samples into your articles in joomla, you use the pre tag like this:

<pre xml:lang="xxx"> your code goes here </pre>

You can use any of the following languages for colorizing your code in articles. 
 

recital, abap, dos, lotusscript, rails, actionscript3, dot, lscript, reg, actionscript, d, lua, robots, ada, eiffel, m68k, ruby, apache, email, make, sas, applescript, fortran, matlab, scala, apt_sources, freebasic, mirc, scheme, asm, genero, mpasm, scilab, asp, gettext, mxml, sdlbasic, autoit, glsl, mysql, smalltalk, avisynth, gml, nsis, smarty, bash, gnuplot, objc, sql, basic4gl, groovy,

ocaml-brieftcl, bf, haskell, ocaml, teraterm, blitzbasic, hq9plus, oobas, text, bnf, html4strict, oracle11, thinbasic, boo, idl, oracle8, tsql, caddcl, index.html, pascal, typoscript, cadlisp, ini, perl, vbnet, cfdg, inno, per, vb, cfm, intercal, php-brief, verilog, cil, io, php, vhdl, c_mac, java5, pic16, vim, cobol, java, pixelbender, visualfoxpro, c, javascript, plsql, visualprolog, cpp, kixtart, povray, whitespace, cpp-qt, klonec, powershell, winbatch, csharp, klonecpp, progress, xml, css, latex, prolog, xorg_conf, delphi, lisp, providex, xpp, diff, lolcode, python, z80, div, lotusformulas, qbasic

A quick tip for optimizing TCP performance on linux.

edit /etc/sysctl.conf add the lines:

If using gigabit ethernet:

net.ipv4.tcp_mem= 98304 131072 196608
net.ipv4.tcp_window_scaling=1
net.core.wmem_default = 65536
net.core.rmem_default = 65536
net.core.wmem_max=8388608

To reload these use:

# sysctl -p

If using infiniband:

net.ipv4.tcp_window_scaling=1
net.ipv4.tcp_timestamps=0
net.ipv4.tcp_sack=0
net.ipv4.tcp_rmem=10000000 10000000 10000000
net.ipv4.tcp_wmem=10000000 10000000 10000000
net.ipv4.tcp_mem=10000000 10000000 10000000
net.core.rmem_max=524287
net.core.wmem_max=524287
net.core.rmem_default=524287
net.core.wmem_default=524287
net.core.optmem_max=524287
net.core.netdev_max_backlog=300000

For Recital to run correctly on 64bit Linux you require the ia32 shared libraries.

The 64bit port of Recital requires these libraries to allow access to 32bit Xbase and C-ISAM data files which are 32bit.

If you do not have these libraries installed you will either get a "can't find db.exe" or an "error loading shared libraries" when trying to run or license Recital.

Installing the ia32 shared libraries

Redhat EL 5 / Centos 5 / Fedora 10

1. Insert the Red Hat Enterprise Linux 5 Supplementary CD, which contains the ia32el package.

2. After the system has mounted the CD, change to the directory containing the Supplementary packages. For example:

   cd /media/cdrom/Supplementary/

3. Install the ia32el package:

   rpm -Uvh ia32el-<version>.ia64.rpm

Alternatively: Note you must have the required repo's enabled.

 yum install ia32el

Ubuntu / Debian

sudo apt-get install ia32-libs