User Guide : Map Connectors : Types of Connections : SQL Connections
 
Share this page                  
SQL Connections
This section provides information about the SQL connections.
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.
See Also
AccountMate
PayChex (DJF) Import
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:
DROP INDEX index1
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).
Example
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
formatdigits(digits)
    dim value
    dim digit
    dim result
    dim
multiplier
    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"))
else
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
    rnd(seed)
    guid = "{" & formatdigits(8) & "-" & formatdigits(4) & "-4" & formatdigits(3) & "-b" & formatdigits(3)
& "-" & formatdigits(12) & "}"
    return guid
end function
Authentication
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.