Hello been a bit , but I wanted to share some information around the MIM Generic SQL connector and Export operations.
The Generic SQL Connector technical reference found here
Download of the connector can be found here as well fyi we just released an update today
Now the Export changes can have the following operations with exported attribute:
1. Value Add.
2. Value Delete.
Value add change has the following behavior:
1. A new row is inserted (with added value) into the table with multi-valued attributes if either of following is true:
a. table with the multi-valued attributes does not have any rows
b. multi-valued attribute column does not have any cell with the NULL value
2. Existing cell is updated if multi-valued attribute column has the cell with the NULL value
Value delete change from a multi-valued attribute has the following behavior:
1. Entire row is deleted if all other cells in this row have NULL values
2. Cell containing deleted value is set to NULL if any other cell in the same row has not NULL value.
Fig. 1 presents an example where a group has two multi-valued attributes: “owners” and “members”.
Fig. 1 – An example with two multi-valued attributes.
Let’s consider insert and delete operations for table from Fig 1 (all examples are against initial table look).
1. If it is necessary to insert an additional value into multi-valued attribute “members”, in this case the management agent adds a new row into the table. The new row will contain the new value in “members” field and the other fields will have NULL values.
Fig 1.1 – Adding an additional value into multi-valued attribute “members”.
2. If it is necessary to insert an additional value into multi-valued attribute “owners”, in this case the management agent adds value into row 3, replacing NULL value (via SQL update for row 3).
Fig 1.2 – Adding new value to multi-valued attribute “owners”.
3. If it is necessary to delete “Third” value from multi-valued attribute “members”, in this case management agent deletes the whole row which contains “Third” value, because all other multi-valued attributes have NULL values.
Fig 1.3 – Deleting “Third” value from multi-valued attribute “members”.
4. If it is necessary to delete “First” value from multi-valued attribute “members”, in this case management agent replaces deleted value with NULL in row 1 (via SQL update).
Fig 1.4 – Deleting “First” value from multi-valued attribute “members”
Recommendation: if database has few separate tables for each a multi-valued attribute, in this case it is recommended to unite all them into one view/table.
