Import from Database Job

The job details are as follows:

• Adapter: Import from DB

• Input CI Type: Database

• Discovered CIs: ConfigurationItem

• Required Protocol: SQL

This job has no trigger queries associated with it. The job tries to get the Instance name and Port using the attributes Name and Application Listening Port Number of the Input Database CI. If these attributes are empty, it uses the Instance Name and Port number defined in Generic DB Protocol (SQL) credentials.

The following parameters are included by default:

For details on overriding an adapter parameter, see "Override Adapter Parameters" in Universal CMDB Developer Reference section of the UCMDB Online Help.

The following use cases are supported by the Import from Database job (a single SQL query is performed):

• Import data using the schema name and table name parameters:



The SQL query is generated from these parameters.

• Import data specifying an arbitrary SQL query as the source of the data:



The SQL query is generated from the defined query. For more details, see Importing Data with an SQL Query.

SQL naming conventions suggest a usage of a <database.schema.table> syntax for the fully qualified name of a table. Note, however, that each vendor treats the specification in a different way. DFM uses the following notation:

• The schemaName parameter specifies the name of a database.

• The tableName parameter specifies the name of a table.

• A schema name cannot be specified in a parameter but can be included in a SQL query.

For Oracle, the SQL query is:

SELECT * FROM <schemaName.tableName>

For Microsoft SQL Server, the SQL query is:

SELECT * FROM dbo.tableName

You can use arbitrarily-complex SQL query expressions, for example, joins, sub-selects and other options, as long as the query is valid and complies with the database usage. Currently, you must use a fully-qualified table name in the query according to the specific database.

Types enable you to specify, in the mapping file, the type of column that exists in the external source. For example, a database includes information about column types, and the value of this type needs to be included in the CI's attributes. This is done by adding a type element to the map element (in mapping_[your mapping file name].xml):

            <column type="int"></column>

Supported type attributes are:

• string

• Boolean

• date

• int

• long

• double

• float

• timestamp

Note

• You use the type attribute for database mapping only.

• If the column element does not include a type attribute, the element is mapped as a string.

Example of adding a type attribute

A database column has an integer type and can be either 0 or 1. This integer must be mapped to a Boolean attribute of a CIT in UCMDB. Use the binaryIntToBoolean converter, as follows:

        <map>
            <attribute>cluster_is_active</attribute>
            <column type="int">cluster_is_active</column>
            <converter module="import_converters">binaryIntToBoolean</converter>
        </map>

type="int". This attribute specifies that the value of cluster_is_active should be retrieved as an integer, and that the value passed to the converter method should be an integer.

If the cluster_is_active attribute of the CIT is of type integer, the converter is not needed here, and the mapping file should say:

        <map>
            <attribute>cluster_is_active</attribute>
            <column type="int">cluster_is_active</column>
        </map>

The main purpose of the Import from database job is importing CIs that do not require a root container attribute. For example: Node, IP address, etc. However, there are many CIs that depend on root CIs (for example: CPUs, SQL servers, etc) that cannot be deployed into UCMDB without the Node on which they are installed. In such cases, it is better to use a different discovery approach. For example, by importing data from Excel, you can import CIs with corresponding relationships (including the composition relationship). You can import such CIs using the Import from database job only if all of the following apply:

1. The root CI (for example: Node) was already populated into UCMDB.
2. It is possible to construct an SQL query to select the UCMDB ID of already imported root CIs from an external database.

In such cases, you can map the selected UCMDB ID of the root CI to the root container attribute of the contained CI.