|
TechTip: Flexible SQL Journaling |
|
|
|
|
Written by Kent Milligan
|
|
Thursday, 08 December 2005 |
V5R3's new QDFTJRN allows you to name and locate the journal however and wherever you want.
Journaling is
an important technique that can improve the availability and recoverability of
the databases your applications use. DB2 UDB for iSeries has always tried to
make it easy to journal by automatically attempting to journal any SQL table
created. DB2 UDB did this by looking for a journal named QSQJRN in the same
schema (library) that the table was being created into.
This automatic
behavior is a nice feature, but it wasn't very flexible. If your company had a
naming convention for journal objects, this automatic behavior couldn't be used
because SQL required the journal name to be QSQJRN. The QSQJRN object also had
to be in the same library as the table, so if you needed the journal to reside
in a different library (maybe a requirement of a high availability solution),
you were out of luck. In these cases, the SQL table would have to be manually
journaled.
V5R3 provides a new option that gives you added flexibility to
name and locate the journal in any manner that you choose. The SQL Create Table
statement was changed to first look for a data area named QDFTJRN in the table's
library before trying to journal the table into a journal named QSQJRN. If DB2
UDB finds the QDFTRN data area in the schema that it's creating a table into,
then it will read the contents of the data area to find which journal it should
be using.
The QDFTJRN needs to be created as a character data area with a
minimum length of 25. The first 10 bytes will contain the name of the schema in
which to find the journal, and the next 10 bytes will contain the name of the
journal itself. The last 5 bytes must contain the value *FILE or *NONE. The
*FILE value is used to start journaling on the table being created, while the
*NONE value will prevent DB2 UDB from journaling the newly created table. The
user creating the table would need authority to the journal referenced in the
QDFTJRN data area.
As an example, say that all of the tables being
created into the DBLIB needed to be journaled to the HAJRN journal object in the
HAJRNLIB schema. The following CRTDTAARA command would create the QDFTJRN area
needed to redirect the automatic journaling from QSQJRN in DBLIB schema to the
HAJRN journal in HAJRNLIB.
CRTDTAARA DTAARA(DBLIB/QDFTJRN) TYPE(*(CHAR) LEN(25)
VALUE('HAJRNLIB HAJRN *FILE')
Once this data area is created, any SQL tables created into the DBLIB
schema will cause DB2 UDB to automatically journal those tables into the HAJRN
journal in the HAJRNLIB schema.
More details on the QDFTJRN data area
can be found in the CREATE TABLE statement documentation in the DB2 UDB for
iSeries SQL Reference in the IBM eServer
iSeries Information Center.
Kent Milligan
is a DB2 UDB Technology Specialist on IBM's eServer Solutions Enablement team.
He spent the first seven years at IBM as a member of the DB2 development team in
Rochester. He can be reached at
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
.
|
Last Updated ( Thursday, 08 December 2005 )
|
No Comments Have Been Posted.
