Data Sources

A Data Source is an object that defines the information CIC Studio needs to connect to a single database. It also specifies which tables, views, and columns you can interact with. A Data Source 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.

Adding 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:

• Type the database's URL.
• Click the Global Variable button to select a Global Variable.
• 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.
• In the password parameter, type the password needed to access the database. 
• 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.