8.6. Configure ODBC Options on Microsoft Windows

Prerequisites

  • You must have logged into the workstation with administrative rights.
  • You must have used the Control Panel’s Data Sources (ODBC) applet to add a new data source name.
    Each data source name you configure can only access one VDB within a Teiid System. To make more than one VDB available, you need to configure more than one data source name.
  1. From the Start menu, select Settings -> Control Panel.
  2. The Control Panel displays. Double-click Administrative Tools.
  3. Double-click Data Sources (ODBC).
  4. The ODBC Data Source Administrator applet displays. Click the tab associated with the type of DSN you want to add.
  5. The Create New Data Source dialog box displays. In the Select a driver for which you want to set up a data source table, select PostgreSQL Unicode.
  6. Click Finish.
  7. In the Data Source Name edit box, type the name you want to assign to this data source.
  8. In the Database edit box, type the name of the virtual database you want to access through this data source.
  9. In the Server edit box, type the host name or IP address of your Teiid runtime.

    Note

    If you are connecting via a firewall or NAT address, you must enter either the firewall address or the NAT address.
  10. In the Port edit box, type the port number to which the system listens for ODBC requests. (By default, Red Hat JBoss Data Virtualization listens for ODBC requests on port 35432.)
  11. In the User Name and Password edit boxes, supply the user name and password for the Teiid runtime access.
  12. Leave SSL Mode to disabled. (SSL connections are unsupported at present.)
  13. Provide any description about the data source in the Description field.
  14. Click on the Datasource button and configure the options. Tick Parse Statements, Recognize Unique Indexes, Maximum, Text as LongVarChar and Bool as Char and set MaxVarChar to 255, Max LongVarChar to 8190, Cache Size to 100 and SysTable Prefixes to dd_:.
    On the second page, click LF, Server side prepare, default, 7.4+ and set the Extra Opts to 0x0.
  15. Click Save.
    You can optionally click Test to validate your connection if Red Hat JBoss Data Virtualization is running.

Table 8.1. Primary ODBC Settings for Red Hat JBoss Data Virtualization

NameDescription
Updateable Cursors and Row VersioningShould not be used.
Use serverside prepare and Parse Statements and Disallow PrematureIt is recommended that Use serverside prepare is enabled and Parse Statements/Disallow Premature are disabled.
SSL modeSee Security Guide https://access.redhat.com/documentation/en/red-hat-jboss-data-virtualization/6.4/paged/security-guide/
Use Declare/Fetch cursors and Fetch Max CountShould be used to better manage resources when large result sets are used.
Logging/debug settings can be utilized as needed.
Settings that manipulate datatypes, metadata, or optimizations such as Show SystemTables, True is -1, Backend genetic optimizer, Bytea as LongVarBinary, Bools as Char are ignored by the server and have no client side effect.
Any other setting that does have a client-side effect, such as LF to CR/LF conversion, may be used if desired but there is currently no server-side usage of the setting.