Enabling Before-images During Table Creation

Learn how to enable before-images while creating a table.

You can create a table with an optional clause to enable the generation and persistent storage of before-images.

Syntax:

before_image_definition ::= ENABLE BEFORE IMAGE [ttl_definition]

Semantics

The ENABLE BEFORE IMAGE clause generates before-images for table rows for subsequent update and delete operations. For insert operations, before-images will be empty. In addition to enabling before-images while creating the table, you must also enable before-images in the subscription configuration through the Streams API. For more details, see Using NoSQLSubscriptionConfig in the Streams Developer’s Guide.

If you don’t include the ENABLE BEFORE IMAGE clause, the subscribed table can only stream after-images.

To enable before-images generation after the table is created, you must use the ALTER TABLE Statement with an ENABLE BEFORE IMAGE clause.

To limit the storage consumed by before-images, you can use a TTL definition to expire the before-images. The TTL value indicates that the before-images are stored on the disk from the time the before-images are generated until the TTL expires. After this duration, the before-images expire, freeing up the disk space they consumed, and will not appear in the stream.

If you don’t supply a TTL definition for before-images, the generated before-images remain for 24 hours.

Note:

• It is possible to set both table TTL and before-images TTL together in the CREATE TABLE statement. Both TTL values work independently of each other.

• It is possible that a row will expire before its before-image TTL has expired.

• Before-images are beneficial when used in conjunction with streaming. For more details, see the Before-images Streaming section.

• The before-images TTL governs the time window a before-image can be placed on the stream

Example 1 - Enable before-images while creating airline baggage tracking application table

CREATE TABLE BaggageInfo (ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)USING TTL 5 DAYS ENABLE BEFORE IMAGE USING TTL 48 HOURS

Explanation:The CREATE TABLE statement above creates a BaggageInfo table and enables before-images generation for any subsequent DML operations. The TTL value for table data is set to 5 days. The TTL value for before-images is set to 48 hours.