Working with Databases
All Ataccama solutions use databases in one way or another. They can be used as data sources and repositories for storing data. Profiles can be created from data stored in databases too.
Installing Database Connectivity Drivers
The Ataccama IDE uses the Java Database Connectivity (JDBC) API for connecting to databases. JDBC drivers are available for most database engines and are distributed as a component of the database engine or separately as connectivity components. The licensing terms do not always allow distribution of these drivers with Ataccama products. Therefore only a basic set of drivers for the most common databases is shipped. You may download and install additional drivers on your own.
The following drivers are shipped with the product and are installed automatically when the IDE is started for the first time:
- Apache Derby. JDBC driver for the Apache Derby database.
- HSQLDB. JDBC driver for the HSQLDB database.
- IBM DB2. JDBC driver for the IBM DB2 database.
- jTDS. jTDS open-source driver for connecting to both MS SQL and Sybase servers.
- Oracle. JDBC driver version 11g for Oracle databases.
- Microsoft SQL Server. JDBC driver for Microsoft SQL Server 2000, SQL Server 2005, and SQL Server 2008. Requires JRE version 1.6.
- PostgreSQL. JDBC driver for the PostgreSQL database. Supports PostgreSQL 7.3 or newer.
- Teradata. JDBC driver version 13.10.00.18 for Teradata databases.
Additional drivers may be installed with the following procedure:
- Download the required driver (usually an archive).
Extract the archive to a desired location.
- Go to Window > Preferences > [Your Product] > Database.
- Add/edit a database driver:
- In case of a pre-configured driver – it is in the list, e.g., MySQL – double-click it.
- In case you want to add a new database driver, click Add... and provide the name of the new database.
- Click Add to Classpath... and locate the
.jardriver in the extraction folder. - Click OK to finish.
- The driver will have a green dot and YES in the Configured column of the table.
Connecting to a Database
To connect to a database, right-click the Databases node in the File Explorer and select New Database Connection.
Creating a Database Connection
This will invoke the following dialog:
Configuring a Database Connection
A detailed explanation of the New Database Connection parameters is provided below.
| Name | Required | Description |
|---|---|---|
| Database type | Yes | Specifies the database connection type. |
| Connection name | Yes | Defines a name for the new database connection. |
| Host | Yes | Specifies the host name or IP of the database host. |
| Port | Yes | Port for which the database connection will be used. |
| Database name | Catalog | Yes | Specifies the name of the database instance located on the host. |
| Username | No | The username to connect to the database. |
| Password | No | The password to connect to the database. |
After the database connection has been made, the database will be shown in the Databases node in the explorer panel. Right-click the connection name and select Connect to establish a connection.
Connecting a Database
Viewing Table Properties
Clicking on the table name will show its metadata in the Properties tab.
Table Properties in the Status Panel
Executing SQL Queries
To view the results of an SQL query on a table, double-click the table.
SQL Editor Mode
A default query will be shown, listing all table entries (grouped in batches if the number of rows is large). To change the query, edit the query text and click Execute. To retrieve more results from the query, click Next batch or Read Rest (to show all results).
Refer to the documentation for the JDBC Reader step to learn how to use data from a database inside a Plan file.