User Guide : Map Connectors : Types of Connections : Database Connections
Share this page             
Database Connections
The following are the available database connections:
ODBC and Other Middleware Connections
SQL Connections
Installing an ODBC Driver
ODBC Connectivity Tips
Oracle Connectivity Guidelines
ODBC and Other Middleware 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.
Driver Installation
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.
Text Box to Enter Driver Options
The text box is used to enter the following:
Messages in the Log Message
Constraint DDL information
Primary key information
The text box is a simple text editor where you type commands.
SQL Connections
This section provides information about the SQL connections:
Relational Database Management Systems (RDBMS)
Creating SQL Table Indexes and Primary Keys
Calling PL/SQL Stored Procedures
Passing Variables
Generating a Random Unique Identifier
Relational Database Management Systems (RDBMS)
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.
Creating SQL Table Indexes and Primary Keys
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.
Creating Indexes
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:
CREATE UNIQUE INDEX index1 ON mytable (Field1 ASC)
CREATE INDEX index2 ON mytable (Field2, Field3)
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.
Note:  The ConstraintDDL is executed only if the REPLACE mode is used for the target. If there are any errors, the errors are written to the error and event log file. If you want to ignore the DDL errors, you may continue the transformation.
Deleting Indexes
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:
Creating PrimaryKeys
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.
@CREATE INDEX pk_mytable ON mytable (Field1, Field2) WITH PRIMARY
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.
Note:  This option works only in REPLACE mode.
Calling PL/SQL Stored Procedures
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.
Single Procedure with No Parameters
To call a single PL/SQL procedure that takes no parameters, see the following examples.
BEGIN ProcedureName\; END\;
BEGIN "Package"."ProcedureName"\; END\;
Single Output Parameter of Type refcursor
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:
tutor1_get.getacn(pointer in number, refc out refcursor)
Enter the SQL query command in the Query Statement as follows:
execute tutor1_get.getacn(10056, :cv)
Passing Variables
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:
DJX("BEGIN Package.ProcedureName(" & var & "); END;")
Note that var is the name of the variable you are passing to the stored procedure.
Caution!  Notice that the initial DJX command is not enclosed in quotes, but the entire SQL statement within the outer parentheses IS enclosed in quotes. Within the inner parentheses, the quotes are present because you must concatenate the first part of the SQL statement with the variable contents – and then you concatenate the second part of the SQL statement at the end of that part of the statement. This is because you must dynamically assign the value of the variable to the SQL statement (not to the literal name of the variable).
Oracle Join Hints
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.
SELECT *FROM emp, dept
WHERE emp.deptno = dept.deptno;
SELECT dept.deptno, name_view.emp_fullname FROM emp_fullname, dept WHERE dept.deptno = name_view.deptno (+) AND dept.deptloc = 'London';
Join Order Hints
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.
Join Operation Hints
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.
Generating a Random Unique Identifier
For the following connectors, you can use an EZScript to auto-generate a GUID for a variable declared as the uniqueidentifier data type:
SQL Server 2000, 2005, and 2008
Microsoft SQL Azure
Microsoft SQL Azure Multimode
Pervasive.SQL v11
Pervasive.SQL v11 Multimode
Refer to the following example:
public function
    dim value
    dim digit
    dim result
    value = rnd()
    multiplier =
16 ^ digits
    value = value
* multiplier
    result = ""
    for i = 1 to digits
digit = value mod 16
value = value / 16
if digit < 10 then
     printed = chr(digit + asc("0"))
printed = chr(digit - 10 + asc("a"))
end if
result = result & printed
    next i
    return result
end function
public function generateGUID()
    dim guid
    seed = now()
    seed = seed * 10000000000
    seed = seed mod 1000000
    if seed > 0 then seed = -seed
    guid = "{" & formatdigits(8) & "-" & formatdigits(4) & "-4" & formatdigits(3) & "-b" & formatdigits(3)
& "-" & formatdigits(12) & "}"
    return guid
end function
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.
Using Kerberos
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:
Forward Only
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.
Installing an ODBC Driver
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.
Note:  In Windows, the ODBC administrator is called Data Sources (ODBC) and is available in Control Panel > Administrative Tools.

In current versions of Windows on 64-bit platforms, the administrator is ODBC Data Sources (64-bit). There is also a 32-bit variant. But for DataConnect, 64-bit data sources are required.
Configuring ODBC Data Source
To add a data source to ODBC Control Panel
1. Go to Administrative Tools from the Start menu.
2. Double-click ODBC Data Sources (64-bit).
The ODBC Data Source Administrator window is displayed.
3. Go to User DSN or System DSN tab and click Add.
The Create New Data Source window appears.
4. Select the appropriate driver and click Finish.
The ODBC Administrator window appears for that driver.
The options varies depending on the selected driver. Check the documentation of the particular driver for information to setup the driver.
5. Click Apply and then click OK.
The created data source is displayed on the User DSN or System DSN tab (based on the selection in step 3). The name of the driver is also displayed.
6. Click OK to close the ODBC administrator window.
ODBC Connectivity Tips
This section provides information about ODBC connectivity tips.
Connecting to a Table
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.
ODBC Driver Setup Error
If you receive the error "Source connection information is missing," or if the Reselect DSN dialog box keeps appearing, do the following.
1. Delete the DriverOptions (leave blank).
2. Change ModifyDriverOptions to false.
3. Change DriverCompletion to No Prompt.
4. For TransactionIsolation, select Read Uncommitted.
5. Click Apply.
Returning Data Types
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.
Oracle Connectivity Guidelines
The following notes apply to Oracle connectors.
Oracle Client
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.
TNS name
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.
Table Names
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.
To troubleshoot your Oracle connection
Connect to the Database Alias, not the SID.
Connect to Oracle with ODBC 3.x. If you are unable to connect, there could be conflict between the client and server versions. See Version.
Issues and Their Solutions
Cannot find the oci.dll file.
The oci.dll is not in the standard path. All Oracle components must be installed and configured properly. See Path.
Connect to ora12154:tns. Could not resolve service name (25519).
Must set up in TNS Names.ora.
Connect (session begin): error 1005. [Ora01005:null password given: Logon denied] in connecttoapi [vbxsupp.bas] (25519)
Check for a version mismatch between the Oracle client and server. See Upgrades above. Remove and reinstall the integration platform. For instructions, see the Getting Started help topic.
Oracle Database Global Transaction Support
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 configure an Oracle database instance
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:
Existing databases (already configured) - You can use the Oracle DataBase Configuration Assistant to change the mode of the database. Open the database and choose Configure Database Options in a database. Click Next and then Next again. In the screen provided, set to either dedicated or shared mode. After changing this setting, stop and then restart the service for the change to take effect.
New databases - When you create a new database, you can select shared mode from the initial setup options.