Data Sources

The Data Source object defines certain information required by objects in CIC Studio to connect to a single database. This includes the specific tables, views, and columns you plan to with. This object defines:

• The JDBC Driver to use when connecting to the database
• The database's URL
• The username and password to use when connecting to the database
• The tables, views, and columns that CIC Studio must read, write, update, or delete

Business Use

If any of the data involved in transformation comes from or goes to a database and you want CIC Studio to interact with the database directly, you must use a Data Source.

Studio Use

CIC Studio (and the Integration Engine) use Data Sources to interact with databases. These objects are referenced in:

• Database Adapters
• SQL Access objects

Data Sources exist independently and, once created, should remain fairly static. A single Data Source can be referenced by multiple objects, including multiple objects of the same type across multiple Projects.

However, you might want to use multiple Data Sources to access the same database if you have multiple Database Schemas referring to the same database but using different tables, views, and/or columns. Although you can specify which tables, views, and columns to use in a Schema, you may have to repopulate that Schema from the Data Source if you ever make changes to the actual database. In that instance, you would have to remove any extra tables, etc. that come over from the Data Source that you aren't using in the Schema.

CIC Studio includes - and recommends using - the Database Refresh Wizard to streamline any changes to Data Sources and their affected Schemas and Adapters.

To use a Data Source, you must:

• Create a Data Source in your Package.
• Specify the information CIC Studio needs to connect to the database in its editor.
• Specify which tables and columns to be used.
• Reference the Data Source in the appropriate objects that ask for it (Database Adapters, etc.)

How to define

Use a Data Source to connect to a database, and specify the tables, views, and columns of the database that will be referenced. 

Specify path to the database

Under JDBC Driver, in the Classpath field, click the Add button to specify the path to your database. You can either:

• Click the Browse button to locate the database
• Click the Global Variable button to select a Global Variable.

Add the java.sql.Driverto a Project's Classpath (Required for CIC)

The changes here allow the selection of a java.sql.Driver from the Project's classpath.

1. Add a lib directory to your project (recommended practice)
2. Add the JDBC driver jar to the lib directory
3. Open the Manifest Editor
4. In the Runtime tab, click Add... in the Classpath section, and add the driver jar
5. In the Build tab, ensure that the driver jar and/or lib directory is selected

Specify the Driver and Connection Info

Click in the Driver Name field to specify the driver to use to connect to the database. JDBC drivers must meet the version requirements set by the database type.

1. Under Connection Information, in the URL field, either manually enter the database's URL or click the Global Variable button to select a Global Variable (recommended).

2. Select your Authentication Mode. Options include Vault (recommended and default) or username/password.

   For Vault: When selected, the Vault Entry Alias field appears and allows you to enter an auth vault entry from the drop-down menu.

   An Auth Vault must already exist. When creating an Auth Vault to be used in a Data Source:
   
   A) Create an Alias name that you’ll recognize when configuring your Data Source.
   B) Select Manual Authentication.
   C) Select Basic from the Auth Info drop-down.

   Note: You do not need to complete any other fields on this authentication screen. For more information, please refer to the steps on creating an auth vault.

   Once created, your vault entry will display as an option in your Data Source editor (Vault Entry Alias) as well as the Admin Console<Settings View.
   
   

   For Username/Password: In the Username field, either: a) Type the username needed to access the database, or b) Click the Global Variable button to select a Global Variable. Then enter the password needed to access the database. 
   
   Note: manual entry of username and password does not offer the same amount of security as using the Vault option, and therefore is not the recommended action.
   
   
3. If your database uses/supports catalogs, specify one in the Catalog parameter.

   Note: Both catalog and schema support varies depending on the database. If your database does not use/support them, just leave the fields blank.

   If your database uses/supports schemas, specify one in the Default Schema field. This field refers to the schema that your database uses. It does not refer to a Schema object.

Test Connection and Fetch Metadata

In the top-right corner of the Data Source editor, click the Test Connection button to ensure Studio can successfully connect to the database.

Under Database Metadata, click the Fetch Metadata button to populate the Tables and Views area.

Customize the tables and columns that will be referenced

• Under Tables and Views, you can see the columns within a table by clicking on the table name.
• You can remove a table you do not need to use by highlighting it and clicking the Remove Table(s)/View(s) button.
• You can remove a column you do not need to use by highlighting it and clicking the Remove Column(s) button.