Change Views Quick Start with isql

Go Up to Change Views Quick Start

Creating a Subscription with isql

Subscriptions allow you to observe the changes on data across multiple connections, you can track changes like row inserts, updates, and deletes at a column-level. In this guide you create subscriptions for the EMPLOYEE and CUSTOMER tables on the employee database.

To create a subscription using isql follow these steps:

1. Connect to the employee database.
2. Create a subscription to track the changes on the employee table:

   CREATE SUBSCRIPTION sub_employee_changes ON EMPLOYEE (EMP_NO, DEPT_NO, SALARY) DESCRIPTION  'Subscribe to changes in EMPLOYEE table';
   

3. Create a subscription to track any delete operations on the customers table.

   CREATE SUBSCRIPTION sub_customer_deletes ON CUSTOMER FOR ROW (DELETE) DESCRIPTION  'Subscribe to deletes in CUSTOMERS table';
   

4. Verify the subscriptions are present running:

   SHOW SUBSCRIPTIONS;
   

For more information on CREATE SUBSCRIPTION refer to Creating Subscriptions to Change Views and CREATE SUBSCRIPTION.

Granting Subscribe with isql

After creating a subscription you need to update its permissions so users can have access it and track changes. You can grant SUBSCRIBE privileges using the GRANT SUBSCRIBE statement. You also need to activate the subscription using the SET SUBSCRIPTION statement. For more information refer to: Creating Subscriptions to Change Views, GRANT SUBSCRIBE, and SET SUBSCRIPTION.

Note: This guide employs the HRMANAGER user mentioned on the Before you Begin section.

To grant SUBSCRIBE to users using isql follow these steps:

1. Make sure you are connected to the employee database.
2. Grant SUBSCRIBE to user HRMANAGER on the "sub_employee_changes" subscription:

   GRANT SUBSCRIBE ON SUBSCRIPTION sub_employee_changes TO HRMANAGER;
   

3. Grant SUBSCRIBE to user HRMANAGER on the "sub_customer_deletes" subscription:

   GRANT SUBSCRIBE ON SUBSCRIPTION sub_customer_deletes TO HRMANAGER;
   

Setting Subscribe with isql

The SET SUBSCRIPTION statement sets a subscription as active or inactive, it also includes an optional AT clause to assign an ID or device name to the recipient of subscribed changes. You can set a subscription using SET SUBSCRIPTION <subscription_name> [AT <destination>] {ACTIVE | INACTIVE};

For more information refer to: Creating Subscriptions to Change Views and SET SUBSCRIPTION.

To set a subscription with isql follow these steps:

1. Make sure you are connected to the employee database.
2. Set a subscription with ID "device_1" for the "sub_employee_changes" subscription:

   SET SUBSCRIPTION sub_employee_changes AT 'device_1' ACTIVE;
   

3. Set a subscription with ID "device_1" for the "sub_customers_deletes" subscription:

   SET SUBSCRIPTION sub_customers_deletes AT 'device_1' ACTIVE;
   

See Also:

• SHOW SUBSCRIPTION
• CONNECT

Advance To:

Tracking Changes with isql