DataFlux Data Management Studio 2.5: User Guide

Data Connection Usage Notes

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:

• 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

• 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).

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.

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.

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.

How should the "Disable _0 override parsing Option" be set for SAS connections via ODBC? 

To prevent a SAS error that occurred in SAS 6 and earlier, the SAS ODBC Driver removes the string _0 in the eighth or greater position of a table name. However, if you are using a SAS 7 server or later, this default behavior should be turned off. The SQL option Disable _0 override parsing is used to override the default behavior for the SAS ODBC Driver. Starting with SAS 9.3, the Disable _0 override parsing option is set by default on the SAS ODBC Driver.

If the Disable _0 override parsing option is not set on the SAS ODBC driver, you can get errors such as "ERROR: Column X cannot be found in table, Y" when using a SAS 7 or later server. If you get such errors with a SAS ODBC connection, verify that the Disable _0 override parsing option is properly set.

To verify the setting for this option, from the Data Connections riser in Data Management Studio, right-click the SAS ODBC connection and select Edit. The ODBC Data Source Administrator dialog displays. Locate the DSN connection, and then click Configure. The SAS ODBC Driver Configuration dialog displays. On the General tab, verify that the Disable _0 override parsing option is selected. For more information, see the discussion of the Disable _0 override parsing option in the SAS 9.2 Drivers for ODBC: User's Guide.

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.

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 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.

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.

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.

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.

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 DM Studio. 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.

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.

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.