Connecting to external data sources

Overview

An administrator can create connections that enable data integration with external data sources, such as databases. The add-on can also connect to TIBCO Data Virtualization (TDV) where multiple databases can be combined into views. In addition to the external data source connection, an administrator must configure a connection between the database and data model. This ensures that end users can only integrate data between a given data model and its approved data sources and targets.

To successfully complete these tasks, start with the database connection information configuration. This is a prerequisite for connecting a database and data model.

Step 1: Configuring a database connection

Configure a database connection using one of the following methods:

• Defining a connection in the UI

• Defining a connection in the server configuration file

Defining a connection in the UI

This section describes how to create a connection with a database, or TDV to enable data integration.

Create a database or TDV connection:

1. In the Administration panel, select TIBCO EBX® Data Exchange Add-on (New).

2. From the Reference data group, select the Databases table and create a new record.

3. Supply the required information:

   • Name: The name used to identify this connection.

   • URL: The URL used by the database for incoming connections. When using:

     • The External system authentication method, enter the URL for AWS.

     • The Direct authentication method, enter the URL specific to your database. For example:

       • MsSQL: jdbc:sqlserver://<server>:<port>;databaseName=<databaseName>

       • Oracle: jdbc:oracle:<drivertype>:@<server>:<port>:<databaseName>

       • Postgresql: jdbc:postgresql://<server>:<port>/<databaseName>

       • TDV: jdbc:compositesw:dbapi@<server name>:<port>?domain=<domain name>&dataSource=<datasource name>

   • Authentication: Sets the authentication type for this connection. A direct connection uses a username and password; external system requires an access key.

   • Username and Password: The login credentials for this database connection. If database access is granted through Windows permissions, enter those credentials here.

   • Access key: The access key required to access the external database.

4. Optionally, test the connection.

5. Save and close.

   This connection information is now available to use when connecting a database with a data model.

Defining a connection in the server configuration file

This section describes how to create a database connection using a Tomcat server configuration file.

Create a database connection in server.xml:

1. Ensure EBX® is properly shutdown.

2. In your <GlobalNamingResources> element add a <Resource> child element and define the database information. A sample configuration is shown below:

   

3. Link the <Resource> element to the ebx-manager and ebx-addon-dint<Context>s. A sample configuration is shown below:

   

4. Start your EBX® server.

   This connection information is now available to use when connecting a database with a data model.

Step 2: Connecting a database and data model

The option to import or export SQL is not available to end users until an administrator configures a connection between a database and a data model. To connect a database with a data model:

1. In the Administration panel, select TIBCO EBX® Data Exchange Add-on (New).

2. From the Reference data group, select Database to data model connections and create a new record.

3. Supply the following information:

   • Database: Select the database from the dropdown menu. This list populates with databases that you provided connection information for using the Databases table.

   • Data model: Select the data model to enable import and export between it and the selected database.

   • Optionally, provide a Table name pattern, Schema name pattern, and Description.

4. Save and close.

   Users that have access to data in the specified data model can use the SQL Import and SQL Export services integrate data between the tables in the connected database and EBX®.