10.5. ODBC Setup

Procedure 10.5. Configure the Data Source Name (DSN) on Microsoft Windows

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, Updatable Cursors, bytea as LO, 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.

Procedure 10.6. Configure ODBC Options on Red Hat Enterprise Linux

1. Run this command to install the driver manager: yum install unixODBC.
2. Run this command to verify that your PostGreSQLdriver has installed correctly: odbcinst -q -d.
3. To create the DSN, open the configuration file in a text editor: sudo vi /opt/redhat/odbc.ini

   Note

   You must either use sudo or be logged in as root to open this file.
4. Add the following configuration settings to the file:

    [<DSN name>] Driver = /usr/lib/psqlodbc.so 
    Description = PostgreSQL Data Source 
    Servername = <Teiid Host name or ip> 
    Port = 35432 Protocol = 7.4 
    UserName = <user-name> 
    Password = <password> 
    Database = <vdb-name> 
    ReadOnly = no 
    ServerType = Postgres 
    ConnSettings = UseServerSidePrepare=1 
    ByteaAsLongVarBinary=1 
    Optimizer=0 
    Ksqo=0 Trace = 
    No TraceFile = /var/log/trace.log
    Debug = No DebugFile = /var/log/debug.log 
   

   Copy to Clipboard Toggle word wrap
5. Save the file and exit the text editor.
6. Run this command to test the DSN:

     isql <DSN-name> [<user-name> <password>] < commands.sql 
   

   Copy to Clipboard Toggle word wrap

   Note

   To connect without DSN, use this DSN-less connection string:

      ODBC;DRIVER={PostgreSQL};DATABASE=<vdb-name>;SERVER=<host-name>;PORT=<port>;Uid=<username>;Pwd=<password> 
   

   Copy to Clipboard Toggle word wrap