Protection Features Teradata

The data warehouse of today is "mission critical" and protecting the data can become a mission. "What goes up must come down – just ask any system administrator." I was recently on a plane when I heard the words, "In case of a water landing your seat cushion will act as a floating device." Personally, this is not my idea of protection! As comedian Steven Wright once said, "Why don't they make the whole plane out of that black box stuff?" Teradata gives you a lot more assurance than a floating seat cover in shark infested waters. In fact, Teradata protects the data in numerous ways and has no single point of failure. These features are built directly into the database and can be assigned at the table level.

Attributes can be assigned within a table to provide a variety of protection features and storage characteristics. Although each of these has a default at the system level, they can be over-ridden at the time a table is created. This section discusses and demonstrates these features.
Teradata allows you to specify which data protection features to use for a table. These features include FALLBACK and Permanent Journaling.


FALLBACK requests that a second copy of each row inserted into a table be stored on another AMP in the same cluster. This is done in case the AMP goes down or the disks fail. There are usually four AMPs grouped together in a cluster. FALLBACK allows for processing to continue in the event that an AMP is lost. As a matter of fact, FALLBACK allows for theloss of one AMP in each cluster. A cluster is normally comprised of two or four AMPs that work together.

FALLBACK provides an extra insurance policy that guarantees data availability in case an AMP is lost. However, as with all insurance policies, there is a cost. The cost for FALLBACK is that twice as much disk storage space is used for row storage as compared to not using FALLBACK. The cost is also twice the I/O on inserts, updates, and deletes because there are always two copies to write. One copy goes to the primary AMP where it belongs and the other copy goes to different AMP in the same cluster. The good news is that Teradata AMPs operate in parallel; so, it does not take twice as long to store a duplicate row. Both rows are placed on their respective AMPs at nearly the same time.

FALLBACK is an optional feature. You can use FALLBACK protection on all tables, no tables, or some tables. FALLBACK is usually created on important tables that are mission critical. Therefore, the DD is automatically FALLBACK protected. Fallback is a great feature to ensure extra protection on important data that needs to be readily available. Here is an example of a table created with FALLBACK.

Here is another example that explicitly states NO FALLBACK

Teradata naturally defaults to NO FALLBACK. Because these protection features can be assigned at the database or user level it can be a good idea to explicitly state whether or not you want FALLBACK. When in doubt spell it out!

Permanent Journal

Fallback is different than the Permanent Journal. Where FALLBACK has a duplicate copy of all rows in a protected FALLBACK table, permanent journaling involves keeping an audit trail of all new, deleted or changed data. For example, if a table has one million rows and is fallback protected then there are one million fallback rows. If a Permanent Journal is used instead of FALLBACK, the only rows copied to the journal are for the inserts, updates, and deletes since the last JOURNAL backup. Therefore, in most cases this is far less expensive than storing the additional one million rows needed for fallback. Plus, you can tailor your JOURNAL with specific options. It is called a Permanent Journal because the rows stay there until the user deletes them. The user deletes them when they have been backed up to tape. This is the philosophy of the Permanent Journal. Journal options are:

  • BEFORE JOURNAL - captures an image of a table row before it changes
  • AFTER JOURNAL - captures an image of a table row after it changes
  • DUAL BEFORE JOURNAL - captures two images of a table row before it changes and stores the images on two different AMPs
  • DUAL AFTER JOURNAL – captures two images of a table row after it changes and stores the images on two different AMPs

If a table is FALLBACK protected the journal is also FALLBACK protected. However, the NO DUAL option can be added to allow the table to use FALLBACK and the journals not to utilize FALLBACK.

Besides the DUAL option on the JOURNAL table, you can also request LOCAL or NOT LOCAL. These are in reference to the AMP used in respect to the data row. LOCAL puts the journal entry on the same AMP as the data row. The default is NOT LOCAL for minimizing the loss of data relevant to the loss of an AMP.

Permanent journals are optional. They can be used for all tables, no tables, or some tables. There is one purpose for a BEFORE journal and a different purpose for an AFTER journal. The BEFORE journal is designed to perform a manual rollback in case of a programming error. An AFTER journal is designed to perform a manual roll forward in the event of a hardware failure that causes data to be lost on disk. Lets discuss both of these scenarios using two examples.

BEFORE Journal

To explain journaling, lets say that the EMPLOYEE table is created with a BEFORE journal. Then, a programmer is told to give every employee a 5% raise. Instead, every employee is accidentally given a 50% raise. Because there is a BEFORE journal you have the ability to manually rollback the data to a point in time BEFORE the employee update to correct the problem. As you can see, a BEFORE Journal might be used to rollback a programmingerror.

AFTER Journal

Lets look at an AFTER journal example. Lets say management has decided not to use FALLBACK on any tables. They have concluded that the data is not mission-critical and it can be restored from backup tapes in a reasonable time frame, if necessary. To make sure the system is backed up a FULL SYSTEM BACKUP is performed on the first day of each month. Plus, an AFTER JOURNAL has been defined on all the tables in the system. Every time a new row is added or a change is made to an existing row, Teradata captures the AFTER image. Then, a hardware failure occurs on the 5th day of the month and the data is lost.
To recover, you fix the hardware problem, and then reload your data from the FULL SYSTEM BACKUP done at the 1st of the month. Then apply your AFTER JOURNAL to restore the rows that were added or changed from the 1st to the 5th day of the month. So, asillustrated here, you can use an AFTER JOURNAL to roll data forward. This is usually done to restore data lost due to a hardware problem.

The following example shows the use of the PERMANENT JOURNAL:

This example has created the table called employee in the TomC database. We have chosen a BEFORE JOURNAL and a DUAL AFTER JOURNAL. Journaling has a natural default of NO. That means if you don't specify this protection at either the table or database level the default is NO JOURNALING.

Knowing what you now know from the previous paragraph, how many times do you think the AFTER JOURNAL row will be stored on disk?

The answer is 4 times. This is because the table is FALLBACK protected, so the journals are also FALLBACK protected. Therefore, because the AFTER JOURNAL is DUAL each change to a row is stored twice, and two more times because both DUAL entries are also FALLBACK protected.

As you soon realize, FALLBACK with DUAL journaling can be very expensive in terms of disk space, even more so than FALLBACK by itself.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics