DataFlux Data Management Studio 2.6: User Guide

Data Connection Usage Notes

• ODBC Connections General
• ODBC Connections Wire Protocol
• ODBC Domain-Enabled Connections
• ODBC Hadoop Connections
• ODBC Oracle Connections
• SAP Connections
• SAS Data Set Connections
• ODBC SQL Server Connections
• ODBC Teradata Connections
• ODBC XML Connections

ODBC Connections General

ERROR: loading type library/DLL

If you get "error loading type library/DLL" when doing any level of authentication in Data Management Studio, take the following steps. Either remove HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{87AD4890-333C-451F-8986-C46885C0C8E 7}\1.0 from the registry or change the HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{87AD4890-333C-451F-8986-C46885C0C8E 7}\1.0\0\win32 value to reflect the value in HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{87AD4890-333C-451F-8986-C46885C0C8E7}\1.1\0\win32.

How do I change ODBC credentials that have been saved to the Windows Registry?

Data Direct ODBC drivers do not update any settings for System DSNs when changes are made to the connection options in the logon dialogs. Instead, they will write any values that a user changes into the Windows registry for the current user. This prevents multiple users who are sharing a data source from changing each other's settings. However, if you need to change the settings that were written the registry, open the Registry Editor (regedit). Expand HKEY_CURRENT_USER -> Software -> ODBC -> ODBC.INI. Select the DSN with the incorrect values. Update the values as needed and click OK. Close the registry.

ODBC Connections Wire Protocol

Are there any special considerations for ODBC drivers using the wire protocol?

DataDirect provides a number of wire protocol ODBC drivers that communicate directly with a database server, without having to communicate through a client library. If these drivers are available at your site, they are available from the Drivers tab of the ODBC Data Source Administrator dialog.

If you use a wire protocol driver to create an ODBC connection, the following special considerations apply:

• For DSNs where a DataDirect Wire protocol is specified and the catalog option is selected, only the schemas that have tables or views will be listed (not all schemas that exist). If you require different behavior, create a DSN with the catalog option deselected.
• Verify that the Enable Bulk Load option is turned on in the ODBC DSN for those databases that support this option. The Bulk Load option is not on by default in the newer DataDirect drivers. If the Bulk Load option is not on, then insert performance suffers.
• The Oracle bulk load option in the driver configuration has the potential to bypass constraints in the database (such as primary key, non null).
• After you set up an ODBC connection with a wire protocol driver, verify that the appropriate options have been set on the Advanced tab of the connection properties dialog. The Advanced tab can be displayed as follows: from the ODBC Data Source Administrator dialog, click the System DSN tab. Select the ODBC connection with a wire protocol driver, and then click the Configure button. The properties dialog for the connection displays. Click the Advanced tab and review the options on this tab. To turn on an option, click the checkbox beside the option.
• If Enable N-CHAR Support is selected in the Advanced properties for the driver, problems may arise when the driver determines what value to return for the maximum column width in characters for a particular column. An example is seen with SQL and Oracle data types, where the ODBC specification requires that maximum column width for SQL be returned in characters while Oracle returns column wide in bytes, resulting in a truncation error similar to the following example:

[00007350] ERROR DF.DAC - Operation failed: [22001]

[DataFlux][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1. (0)

Since disabling Enable N-CHAR Support is probably not an option, the recommended workaround is to add a new string, ColumnSizeAsChar, to the data source in the Windows registry, or in ODBC.INI for Unix systems.

WARNING: Using a registry editor incorrectly can cause problems with your system. If you are not sure about this procedure, contact your system administrator for assistance.

First, determine what type of DSN you are editing (system or user) because the registry path to a system DSN differs from the path for a user-defined DSN as shown below:

User DSN: HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>

System DSN: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN NAME>

To create the ColumnSizeAsChar string in the registry:

1. Open the registry editor and navigate to the data source (DSN NAME) using one of the paths shown above.
2. With your cursor positioned on the <DSN Name> entry, right-click and select New > String Value.
3. A 'new value' object appears in the registry - key in the name ColumnSizeAsChar and enter.
4. Right-click the new ColumnSizeAsChar object and select Modify.
5. In the Value Data field, enter 1 and click OK. The new string now appears in the registry:

Example: New ODBC String Value

ODBC Domain-Enabled Connections

Domain-Enabled Connections cannot use Shared Logins

Domain-enabled connections cannot use shared logins, as defined on the Shared Login riser in the Authentication Server interface. Domain-enabled connections can use only the logins that are associated with user accounts on the Authentication Server.

Domain-enabled connections are not displayed in lineages

Domain-enabled connections are not displayed in lineages. Instead, the regular connections that the domain-enabled connections refer to are used in lineage displays.

How can I change a domain-enabled ODBC DSN data source to a non-domain-enabled ODBC DSN source in the Data Source node?

After saving a job with a Domain Enabled ODBC Connection, you need to change the data source from a domain enabled ODBC DSN to a regular, non-domain ODBC DSN. The change will not be saved and the domain enabled ODBC DSN will remain selected in the data source node. This behavior occurs because the application is pulling cached information from the domain enabled ODBC DSN.

Correct this behavior as follows: After saving the job, exit and restart DataFlux Data Management Studio to clear the cached information. Revisit the job and change the data source to a non-domain ODBC DSN. When the job is saved, the non-domain ODBC DSN is associated with the job.

ODBC Hadoop Connections

Avoid Using an Impala Table as the Output of Any Job Node

Cloudera Impala is optimized for queries rather than inserts into the Hadoop file system (HDFS). Accordingly, avoid using an Impala table as the output of any job node.

Setting the Maximum Character String Size for Apache Hive

Apache Hive has a single data type for storing text, STRING, which is a variable-length character string with a maximum size of 2Gb. As a result, this can create very large character fields when processing data. Since Hive’s string type is comparable to VARCHAR in other data sources, you can set the ODBC attribute, Max Varchar Size to specify the maximum character string size. Set the Max Varchar Size value using Advanced Options in Windows ODBC Administrator, or in Unix by editing odbc.ini in the specified path or $HOME directory.

The Query Builder Dialog Cannot Build Valid Queries for Hive or Impala Data Sources

The interfaces for profiles, some job nodes, and other components enable you to display the Query Builder dialog. This dialog provides an easy way to create complex SQL Server queries for data sources. However, the Query Builder dialog cannot generate valid queries for Apache Hive data sources or Cloudera Impala data sources.

Can I use a business rule task to insert a row into an Apache Hive table?

Data can only be inserted into an Apache Hive table by reading it from another table, so you cannot use a business rule task to insert a row. Apache Hive does not support the method used by business rule tasks.

If you create an index on your Hive table (INDEX_TABLE), you cannot use Impala.

Hive indexes are not supported on Impala, so you cannot use the DSN ODBC Impala Wire Protocol driver to view Impala table fields and data.

Does Apache Hive Table Support Row-Level Insert, Update, or Delete?

Apache Hive does not support row-level insert, update, or delete. Accordingly, avoid using an Apache Hive table as the output of these data job nodes:

• Data Target (Update)
• Data Target (Insert)
• Delete Record

Why did I received an error message for "Error retrieving data" and the log stated "Failed to load metadata....due to unsupported column type"?

That error indicates that you used an unsupported data type . According to the ODBC User's Guide "Part 4: The Connect XE Drivers", the only data types supported by the ODBC 7.1.3 Impala Wire Protocol driver are:

• BigInt
• Boolean
• Double
• Float
• Int
• Smallint
• String (Maximum of 2GB)
• Timestamp
• Tinyint

ODBC Oracle Connections

What is the proper Wire Protocol Mode setting for Data Direct DSNs for the Oracle Wire Protocol?

Data Direct DSNs for the Oracle Wire Protocol have a Performance tab. This tab has a Wire Protocol Mode field. When the DSN is used to connect to Oracle 11 or later, the Wire Protocol Mode field should be set to a value of 2.

SAP Connections

Error: "Unable to load extension: (tkesapf)" when connecting to an SAP System

This error probably means that the SAP libraries (DLLs) are not installed on the computer where they are needed. See the SAP documentation for details about installing these libraries on various platforms. SAP libraries must be installed on all computers where a connection to the SAP system is attempted. This could include the computer where DataFlux Data Management Studio is installed; the DataFlux Data Management Server where jobs that use SAP connections are executed; and the Federation Server if that server is used to manage connections to an SAP system.

Does the SAP Remote Function Call node support shared logins?

The custom connection that is defined for the SAP Remote Function Call node cannot reference a shared login. The SAP Remote Function Call node does not support shared logins.

SAS Data Set Connections

What Versions of SAS Can Be Accessed with a SAS Data Set Connection?

A SAS Data Set Connection in DataFlux Data Management Studio 2.5 or later uses a SAS 9.4 TKTS Base driver. Accordingly, it can access any SAS version 8 or version 9 data set.

A SAS Data Set Connection in DataFlux Data Management Studio 2.4 or earlier uses a SAS 9.2 TKTS Base driver. Accordingly, it can access any SAS version 8 data set. It can also access any version 9 data set that was not created with the Extend Observation Counter turned on.

ODBC SQL Server Connections

Why should I use the DataFlux SQL Server Wire Protocol driver?

You cannot use a DSN that specifies the DataFlux SQL Server Legacy Wire Protocol driver or the DataFlux SQL Classic Wire Protocol driver. If you have an ODBC DSN that uses one of these drivers, replace that DSN with one that uses the appropriate DataFlux SQL Server Wire Protocol driver.

Why does my table appear twice in common Select Table dialog when I use SQL Server ODBC DSN?

When using a SQL Server ODBC DSN, if you select a table using the Select Table dialog, close that dialog, and then re-open it, you will see your table name appear twice in the table list. This does not affect the functionality of the application. You can select either table listed in the tree.

When I'm running a profile report, if I try to access anything in my repository it appears to lock up the client. What can I do to keep it from locking up?

If you use an SQL Server DSN connection, you can avoid locking issues by setting the DAC option Readuncommitted as described in the "Data Access Component Directives" topic in the DataFlux Data Management Studio Installation and Configuration Guide.

ODBC Teradata Connections

The DSN for my data source uses a DataDirect 7.0 Driver for Teradata. It takes too long to expand the DSN in the Data Connections tree.

In the properties window for the DSN, click the Options tab and deselect the Show Selectable Tables check box.

ODBC XML Connections

How can I read an XML file in a data job?

Due to the limitations of the ODBC 32-bit XML driver, we recommend that you use the XML Input node to read an XML file in a data job.

How can I read an XML File in a profile?

We recommend that you extract the data from the XML file to a text file or to a database table, and then profile the text file or table. To extract the data, create a data job in which the XML Input node is used to read the XML file, and then use other nodes to output a text file or a database table.