To import delta changes of objects in a database, you must provide a separate delta view of those changes. When you configure your management agent, on the Connect to Database page in Management Agent Designer, you can specify a primary table and a delta view. You can use the primary table for full import of all objects into a connector space. On subsequent delta import runs, you can use the delta view. This separate delta view must reside in the same database as the primary table that is selected for import. All exports are full export, and they do not require a separate view.

For example, you can select a table with the following user objects as the primary table. All objects are imported using a full import run.

Primary Table

User information can change regularly. It is inefficient to run a full import of the primary table each time a change occurs. It is necessary to import again (Insert, Modify, or Delete) only the objects that have changed. A user-written trigger on the primary table determines these changes and writes the changed objects to a secondary delta view. The delta view with the user objects that have changed is specified on the Connect to Database page in Management Agent Designer.

For a delta view, the following requirements must be met:

• The delta view must have all of the columns that exist in the primary table.
  
  
• The delta view must have at least one additional column of string type. This is for the change type.
  
  
• If the Configure attribute-level change type synchronization option is selected in Configure delta, in the Configure Columns page in Management Agent Designer, an additional string-type column is required for the delta view. This column contains the names of the attributes that have been modified.
  
  
• The delta view must be constructed so that the rows are returned in the order in which entries were made into the delta view. This can be achieved, for example, by sorting based on a time stamp column.
  
  
• After a delta import run, it is necessary to remove the rows from the delta view that have already been imported. If the rows that have already been imported are not removed, they will be imported again on subsequent delta runs, making the same changes that have already been applied.
  
  

In the following example of a delta view, employee E001 has a new title, employee E003 has a last name change, employee E004 has left the company and can be removed, and employee E006 is new and needs to be added. No change has occurred for employee E002; therefore, no object for E002 exists in the delta view. The table in the previous example is specified as the primary table. This delta view has the following columns:

• The EmployeeID column is required because it exists in the primary table. This column serves as a reference\anchor column, guranteeing uniqueness of each object.
  
  
• The LastName column is required because it exists in the primary table. This column contains each user object's last name.
  
  
• The FirstName column is required because it exists in the primary table. This column contains each user object's first name.
  
  
• The Title column is required because it exists in the primary table. This column contains each user object's job title.
  
  
• The change_type column is required because it is used to identify the change type operation for each delta object.
  
  

Delta View

To synchronize changes to objects with multi-value attributes, a primary table, a multi-value table, and a delta view are required. Triggers are required to detect changes to the multi-value table and to translate the change to a change type in the delta view. This delta view can contain both single-value attribute changes and multi-value attribute changes. For more information about configuring a multi-value table, see Related Topics.

In the following example, the primary table has one row containing a user object for department manager Steven Thorpe (employee E005) and one row for newly added employee Michael Zeman (E006). There is a secondary multi-value table that identifies the employees that Steve manages (DIRECTS) to which—in addition to the primary table—Michael Zeman (E006) will be added. Finally, there is a delta view that contains the object-level change that will occur for adding Michael Zeman (E006) and the attribute-level change that will occur to the object for Steven Thorpe for adding a new employee (E006) to those employees that he manages (DIRECTS).

Primary Table

Multi-Value Table

Delta View

• Multi-Value Tables for Database Management Agents
• Configure Columns
• Using the Management Agent for IBM DB2 Universal Database
• Using the Management Agent for Microsoft SQL Server
• Using the Management Agent for Oracle Database