Change Views (Reference)

Go Up to System Tables, Temporary Tables, and Views

Specifically, the subscription tracks all row inserts, updates, and deletes to one or more tables at a column-level granularity over a disconnected, extended period of time.
The InterBase SQL query language is modified to search on columns where data has changed since the prior observation.
These data changes are tracked at a column granularity.

Using Change Views

See Getting Started with Change Views for a complete explanation of these topics:

ODS Platform Updates
Migration Issues and Dependencies
Requirements and Constraints

Requirements
Constraints
Backup/Restore Considerations
Deferred Constraints Checking
Trigger Inactivation
Database Restore from a Backup

Creating Subscriptions to Change Views (Reference)

To establish interest in observing changed data on a set of tables beyond the natural boundary of a database connection, a subscription must be created on a list of tables (base tables or views).

In creating subscriptions you would

Grant Subscribe: Grants the user subscribe privileges

Set Subscription: To set a subscription as active, an application issues a SET SUBSCRIPTION statement. The SET SUBSCRIPTION statement allows multiple subscriptions to be activated and includes an AT clause to denote a destination or device name as a recipient of subscribed changes. The subscriber user name is implied by the user identity of the database connection.

See Creating Subscriptions to Change Views or a complete explanation and examples of how to create subscriptions.

Statement Execution (Reference)

Once a statement is prepared, it is unnecessary to re-prepare the statement due to subscription activation or deactivation. A statement dynamically adjusts to the subscription environment of the transaction when it begins execution. Statement execution is also consistent in that once it begins, it returns change view result sets even if the subscription is deactivated before the full resultset has been fetched.

See Statement Execution for a complete explanation of how the Statement Execution feature works.

Change View API Support

Change Views API support is provided through the extended SQLVAR structure, XSQLVAR, via a new interpretation of the SQLIND member. To review, a developer places a pointer to a variable in XSQLVAR.SQLIND to request NULL state. When the query is executed, InterBase places a zero at that pointer address if the column value for the returned row is non-NULL and sets it to -1 if it is NULL.

See Change Views API Support or a complete explanation of how the Statement Execution feature works.

Change View SQL Language Support

To display a list of subscriptions defined in the database, you can execute the SHOW SUBSCRIPTIONS command. To display details for a particular subscription, you can execute SHOW SUBSCRIPTION.

See Change Views SQL Language Support for examples showing a retooling of the ISQL command-line utility that supports change views.

Metadata Support (Reference)

Subscription information is stored in a new system relation RDB$SUBSCRIPTIONS with a unique key on RDB$SUBSCRIPTION_NAME, RDB$SUBSCRIBER_NAME, RDB$DESTINATION. Additional fields store control information to facilitate "check in" and "check out" of changed data. This includes transaction IDs, timestamps and transactional context of last observation of changed data on the schema object.

The term "check out" denotes SELECT of changed columns of rows from subscribed tables when a subscription has been activated.
The term "check in" refers to INSERT, UPDATE and DELETE of changed columns of rows from subscribed tables when a subscription has been activated.
A subscription becomes activated during a database session with the execution of SET SUBSCRIPTION ACTIVE.
It is deactivated with the execution of SET SUBSCRIPTION INACTIVE.

RDB$SUBSCRIPTION and RDB$SUBSCRIBERS are new tables covering the subscription/subscriber elements. The other tables listed show columns that have been updated or added to an existing table.

For more information on the new and updated columns for the implementation of the Change View feature see Metadata Support.

Advance To:

Character Sets and Collation Orders