In a common database, each row in the database represents a single object. For example, an Employees table in a Human Resources database may have one row for each employee in the company, with each column in that row having only one value for an attribute.

EmployeeID	object_type	LastName	FirstName	Title
E005	user	Thorpe	Steven	Sales Manager

When an object in a single row has an attribute that can have more than one value, that attribute is referred to as a multi-value attribute. There is no standardized table configuration for objects in a single row that have multi-value attributes. For example, the following table combines each value of a multi-value attribute in a single column (Directs).

EmployeeID	object_type	LastName	FirstName	Title	Directs
E005	user	Thorpe	Steven	Sales Manager	E001, E003, E004

FIM does not support this type of table, where multiple values exist in a single column. Because a single column for a single row can have more than one value, there is no guaranteed way of changing any one value. For example, if Sales employee E003 moves to a different department and will no longer report to Sales Manager E005, there is no way to guarantee that E003 is in any particular location among all the individual values in the Directs column.

One solution is to create a table with a row for each separate value for an object with multi-value attributes. For example, a single user object may exist in three rows, with each row containing the same columns of single values and a single instance of each of the three multiple values.

EmployeeID	object_type	LastName	FirstName	Title	Directs
E005	user	Thorpe	Steven	Sales Manager	E001
E005	user	Thorpe	Steven	Sales Manager	E003
E005	user	Thorpe	Steven	Sales Manager	E004

This multiplication of a single object or row to contain only a single value of a multi-value attribute is inefficient, causing excessively large storage overhead and, potentially, diminished performance.

To import objects with multi-value attributes from a database, both a primary table and a secondary table for multi-value attributes must be specified. This separate table must reside in the same database as the primary table. When you configure your management agent, on the Connect to Database page of Management Agent Designer, you can specify both the primary table and the multi-value table. You use the primary table to import all objects into the connector space and export all objects from the connector space. You use the secondary, multi-value table to import and export the multi-value attributes.

The following requirements must be met for the primary table:

• All columns must contain only a single value.
  
  
• More than one anchor columns are supported if the columns are of the same type.
  
  
• Tables can contain an optional object type column that defines the object type for each row.
  
  
• If a reference column is used, you must also use a single anchor column. In certain cases, to guarantee a unique object identifier, you must also create a separate column that contains an identifier (such as employeeID) with an additional column containing the object type, as shown in the previous examples.
  
  

For example, a table with the following user objects can be selected as the primary table. The primary table must contain only single-value columns.

EmployeeID	object_type	LastName	FirstName	Title
E001	user	Doyle	Patricia	Sales
E002	user	DeVoe	Michael	Vice President
E003	user	Lysaker	Jenny	Sales
E004	user	Truelson	Kim	Sales
E005	user	Thorpe	Steven	Sales Manager

An additional secondary table is required for those attributes that have multiple values. The secondary, multi-value table must contain the following columns:

• Anchor column or columns
  
  
• Attribute name column
  
  

The secondary, multi-value table must also contain one or more of the following columns for their associated value types:

• A string column
  
  
• A numeric column
  
  
• A binary column
  
  
• A large string column
  
  
• A large binary column
  
  

In the following example of a secondary, multi-value table, user E005, Steven Thorpe, has three employees that report to him (referred to as Directs in the previous example). Each Direct is entered into the STRING_VALUE column as an individual row. The first column, EmployeeID, is an anchor column. The anchor column uniquely identifies each object in reference from the primary table. The second column, ATTRIBUTE_NAME, contains only an attribute name, configured as a multi-value attribute of string type. The third column, STRING_VALUE, is the column containing each individual value of the multi-value attribute.

EmployeeID	ATTRIBUTE_NAME	STRING_VALUE
E005	DIRECTS	E001
E005	DIRECTS	E003
E005	DIRECTS	E004

This table, which contains only the required anchor attributes, reference attributes, and multi-value attributes, allows for the most scalability while maintaining optimum performance. This is the recommended configuration for use with FIM.

	Note
	Indexing a multi-value table can significantly improve performance.

When you configure a management agent for use with a multi-value table, you must select both a primary table and a secondary, multi-value table on the Connect to database type page in Management Agent Designer. You must also configure the additional columns on the Configure Columns page. For more information, see Configure Columns.

When you run the management agent, an object that has attributes in both the primary table and the secondary, multi-value table are then consolidated as a single object in the connector space. Both import and export processes occur just as they do for any other management agent.

For information about how to import multi-value attributes that have changed using a delta table or view, see See Also.

• Delta Views 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