Saturday, September 4, 2010

Permanent Journal

I did some homework on the topic and would like to share the same . comments and suggestions are welcome :)


Permanent Journal

The purpose of a permanent journal is to maintain a sequential history of all changes made to the rows of one or more tables.
Permanent journals help protect user data when users commit, uncommit or abort transactions.
A permanent journal can capture a snapshot of rows before a change, after a change, or both.
 Permanent journaling is usually used to  protect data.
likein case of  the automatic journal, the contents of a permanent journal remain until you drop them.

When you create a new journal table, you can use several options to control the type of information to be captured.


 We create Permanent Journal  when the User or Database is created.
Consider following example for creation of database
CREATE DATABASE testdat
FROM xxspace
AS
PERM = 20000000
SPOOL = 2000000
NO FALLBACK
ACCOUNT = '$xxxxx'
NO BEFORE JOURNAL
AFTER JOURNAL
DEFAULT JOURNAL TABLE = testdat.journal;


Here Admin has opted   for only AFTER JOURNAL and he has name the journal table as "testdat.journal".
When user creates a table in the database "testdat" , by default AFTER JOURNAL is available for him to protect his data when the hardware failure occurs.

He can opt for NO AFTER JOURNAL by overriding the default. Follwoing is the example.

Scenario1 : Here  by default the table has AFTER JOURNAL option.

"CREATE TABLE testdat.table
( field1 INTEGER,
field2 INTEGER)
PRIMARY INDEX field1;

Scenario2: in this case, user has specifically stated he wanted no AFTER JOURNAL for his data. This is how user can override the defult.

CREATE TABLE testdat.table2
FALLBACK,
NO AFTER JOURNAL
( field1 INTEGER,
field2 INTEGER)
PRIMARY INDEX field1;

In this case whenever the user inserts/updates and the transaction is committed , then the affected rows will be taken backup in the journal table "testdat.journal".


Please note :
You must allocate sufficient permanent space to a database or user that will contain permanent journals. If a database or user that contains a permanent journal runs out of space, all table updates that write to that journal abort.

No comments:

Post a Comment