The following are the available database connections:

Open Database Connectivity (ODBC) provides a means of connecting to data sources. ODBC is a standard generic interface layer to SQL-type data sources. In cases where the integration platform does not have a physical or native API to data, ODBC can often provide that connection. Windows and Linux systems support ODBC.

ODBC drivers are normally supplied by the vendor whose data is source or target or in the aftermarket of add-on tools. ODBC drivers are available for a wide set of data sources.

After acquiring the ODBC driver for your source, you must go through an installation step (called ODBC Administration in Windows) to set the driver for your environment.

When the integration platform uses ODBC to connect to a source or target, it acts as a client through the ODBC driver.

Contact Support for information about ODBC driver vendors.

The text box is used to enter the following:

The text box is a simple text editor where you type commands.

The following topics provides information about the SQL connections:

The RDBMS is a database management system (DBMS) that stores data in the form of related tables. This file type category includes a wide variety of SQL and relational database systems. Although the integration platform reads the data from these systems already parsed into fields and records, there are special features that necessitate putting this type of data into a separate category.

The most striking feature of these database systems is the storage of data in multiple tables, many of which are linked together by the use of PrimaryKey fields. When you transform data from (or to) one of these systems, you typically transform only one table at a time. It may be necessary to create and save many maps to complete your data transformation project.

Some of the applications in this category are IBM DB2 Universal Database, Informix, Oracle, SQL Server, and Sybase.

To connect to RDBMS tables, several requirements must be met. The database client application must be installed and configured and server configuration and permissions must be set correctly. Each RDBMS application has its own rules for special characters, case sensitivity, data type support, date/time field support, null handling, query syntax, and stored procedure syntax.

SQL indexes allow rapid search and retrieval of data. You may use these powerful tools within the SQL and ODBC connectors with the ConstraintDDL property option.

The ConstraintDDL property allows you to specify some additional SQL data definition language statements to be executed after their target table is created. This is similar to the support provided for SQL pass-through in the SQL import connectors.

Each line must be a valid ODBC DDL statement.

For example, you could have the statements:

These statements would create two indices on the table mytable. The first does not allow duplicates and the index values are stored in ascending order. The second is a compound index on fields Field2 and Field3.

If you find that an index you have created is no longer useful, takes too much disk space, or is too out of date, you may delete the index with the following statement:

ConstraintDDL also supports an escaping mechanism that allows you to specify DDL in the native SQL of the DBMS. Any statement that is preceded by an @ sign is sent straight to the DBMS.

The following statement is a DDL statement for creating a primary key for the table mytable.

Some ODBC drivers do not support the SQL extensions needed to create a primary key with the ODBC variant of the SQL CREATE statement. So to create a primary key, you must use native SQL.

You can pass PL/SQL code within a map. To do this, create a dataset and select the Query statement radio button. For Query statement, enter your PL/SQL statement.

To call a single PL/SQL procedure that takes no parameters, see the following examples.

Use the EXECUTE keyword in the Query Statement and provide the stored procedure with all parameters except the output refcursor given values. The output refcursor should be indicated as SQL-Plus does for host variables, using a colon and variable name (such as :cv).

Using the example below, where the stored procedure is defined as:

Enter the SQL query command in the Query Statement as follows:

To pass variables with a stored procedure call, use DJX. This enables you to escape temporarily from SQL into EZscript to manipulate strings and access variables. For example:

Note that var is the name of the variable you are passing to the stored procedure.

The information below is provided as a general overview of the syntax involved in creating Oracle joins. For more precise information, see your Oracle documentation.

ORDERED - Tells Oracle to join tables left to right, in the same order in which they are listed in the FROM clause.

STAR - Tells Oracle to use a start query execution plan, if at all possible. This can only work if there are at least three tables being joined and the largest table has a concatenated index on columns that reference the two smaller tables. The two smaller tables are joined first and then a nested-loop join is used to retrieve the required rows from the largest table.

STAR_TRANSFORMATION - Tells Oracle to transform the query into a star query, if possible and then use the best plan for that query.

USE_NL(table_name) - Tells Oracle to use a nested loop when joining this table. The table specified by this hint is the one accessed by the innermost loop. The other table is the driving table. Faster return times than merge joins, but may result in a greater number of disk reads.

USE_MERGE(table_name) - Tells Oracle to use the sort merge method when joining this table. Uses fewer resources, but have to wait until all the records have been sorted before you get the first one back. You also have to have enough memory and temporary disk space to handle the sort.

USE_HASH(table_name) - Tells Oracle to use a hash join for the specified table. Similar to merge join, but does not require a sort. The hash table is built in memory and allows quicker response.

NO_MERGE - This is not the opposite of USE_MERGE. The NO_MERGE hint applies to queries that contain joins on one or more views. It prevents Oracle from merging the query from a view into the main query.

DRIVING_SITE(table_name) - This hint applies when you are executing a distributed join, one that joins tables from two or more databases. Without a hint, Oracle chooses which database actually collects the tables and does the join. By using the hint, you are telling Oracle that you want the join performed by the database containing the specified table.

For the following connectors, you can use an EZScript to auto-generate a GUID for a variable declared as the uniqueidentifier data type:

Refer to the following example:

The SQL Server connectors support SQL Server Authentication and Windows Authentication. The integration platform is a client/server one that supports remotely connected users that are connected using potentially similar client platforms. As such, using Windows Authentication to access SQL Server databases cannot be supported in a straightforward and secure manner. User and password (provided to SQL Server running in SQL Server Authentication mode), masked by macros, is the preferred and supported mechanism for authenticating to SQL Server. See Using Macros for more information about macros.

To use Kerberos to connect to a SQL Server database, the local user account on the integration platform server is the one requiring access because the connection is created directly from the integration platform to the SQL Server database.

During the installation process, a local user is created and added to the Users and DI Users groups. This account must be set up with Kerberos authentication privileges to the database in order to use Kerberos.

When configuring the connector, leave the username and password credentials blank to use the Kerberos credentials for the account running the integration platform on the server.

A cursor is a window into the result set of a SQL query. From this window, you can work with a few rows of data at a time instead of the entire result set. This is useful when working with large tables or database servers over a network.

Cursor support improves the importing of data in two important areas. Because the cursor acts as a window to the data, the integration platform does not need to read the entire result set into memory before using it. You can work with very large tables without running out of memory and without having to wait for the data to display. The other benefit of cursors is that the integration platform can read directly any record from the result set. For transformations, cursors help with record ranges or record sampling, as the integration platform does not need to read each record sequentially.

Connections may offer one or more of the following cursor options:

The Forward Only cursor option offers the most efficient performance for large transformations in SQL sources. Connection is made almost immediately and transformation speed is at its highest. In a test case with SQL Server, using a Static cursor on a 150,000 record table added about 4 minutes to the transformation and record reading was twice as slow. The startup cost of using the Static cursor was greater than the total transformation time when using the Forward Only cursor.

However, with the Forward Only cursor, the integration platform still has to iterate over each record in the result set. This means that things still have to be done sequentially, making the Forward Only cursor option somewhat clumsy for browsing source data. The Source Browser loads quickly, but any time you move from one section of the table to another, you have to wait for it to load completely again.

Static and dynamic cursors improve browsing of source data. Initial loading of the data may be slow, but you can move from the beginning of the table to the end, or stop in the middle, with little or no delay in browser display.

The Static cursor option does not detect updates to data. If the data changes while you are browsing the table, you must exit the browser and reopen it to see the changes. The Dynamic cursor option does keep track of changes to data. This difference may be relevant if you keep a target browser open while running an integration. The Dynamic cursor option, however, demands more computing resources, so the Static cursor option is generally preferred.

With dynamic cursors, all committed changes (including insertions) made by anyone and all uncommitted changes made by the cursor owner are visible to the cursor owner. Deletions are not flagged as missing—the row simply no longer appears. Updates made by anyone can affect membership and ordering of the result set, including updates that do not directly affect result set values, such as a change to another table referenced in a sub-selection.

With a keyset cursor, the membership and ordering of the result set are fixed at open time but values can change. If a change causes a row to fail to qualify for membership in the result set, the row remains visible until the cursor is reopened. If a change affects where a row should appear in the result set, the row does not move until the cursor is reopened. If a row is deleted, the key acts as a placeholder in the result set to permit a fetch by absolute position within the result set. Insertions by others are not visible. Insertions by the cursor owner should appear at the end of the result set. The advantage of this type of cursor is its ability to access the most up-to-date values and yet be able to fetch rows based on absolute position within the result set.

We do not sell or distribute ODBC drivers. You must obtain ODBC drivers from another vendor.

Some basic ODBC drivers are installed automatically with Windows operating systems. However, the drivers are not configured automatically, so you must do this by using the operating system ODBC administrator privileges.

The following topics provide information about ODBC connectivity tips.

As a general rule, when connecting to a database with ODBC, most programs require that the database engine is up and running at the time the connection is attempted.

To connect to a table in the database with ODBC, start the integration platform, create a new data set, and follow the procedure for your connector.

If you receive the error "Source connection information is missing," or if the Reselect DSN dialog box keeps appearing, do the following.

Some connectors (such as ODBC or connectors that support custom fields) returns a full set of data types only when a session is established with the database or application. In addition, you will either get valid (not necessarily correct, but valid) data types when you copy the fields, or if the connector supports user-defined data types, they will be changed to valid types after the connection is established.

When the data is fetched from the database for FLOAT, DOUBLE and REAL data types, only two digits after the decimal is displayed in the Data Browser. The actual values are preserved in the database and it is used during transformation.

The following notes apply to Oracle connectors.

You must have the Oracle engine running to connect to an Oracle database. The integration platform calls the oci.dll file provided by Oracle, a component in the Oracle client. The client must be installed and properly configured on your system. Being able to connect with a third-party client such as SQL *Plus does not mean that your system is set correctly for the integration platform to connect.

DataConnect requires the ORACLE_HOME environment variable to be set to identify the correct location of the tnsnames.ora file.

If ORACLE_HOME is set, then you can choose the database alias from the Database drop-down list.

If ORACLE_HOME is not set, then you can manually specify the database alias in the Database text box.

The client version must be the same or newer than the server version. You can check this by going to Help > About in Net8 Assistant or in SQL *Plus.

Oracle upgrades can cause erroneous versions to be written to your machine, which may prevent the integration platform from connecting to an Oracle database. If this happens, try a lower version and re-attempt the connection.

In older Oracle versions, there is an issue with lower case characters in table names. For more information, see the Connector-Specific Notes section for your specific Oracle connector type.

This topic is for the Oracle 10g and 11g source connector types. Oracle has global transaction support, but you must configure your Oracle database instances for shared server mode. If you do not, when you try to link two Oracle databases, you receive an ORA-24777 (use of non migrateable database link not allowed) error. This is because the transaction, which must be able to migrate between sessions, tried to access a remote database from a non multi-threaded server process.

To resolve this issue, either do your work on the local database or open a connection to the remote database from the client. If the multi-threaded server option is installed, connect to the Oracle instance through the dispatcher. Then follow the configuration instructions: