Configuring the DSN and driver options with ODBC Driver Setup

You can configure the DSN and driver options in the ODBC Driver Setup window.

Procedure

  1. Click the DSN Options tab and enter appropriate values for the DSN options.

    If your application supplies a username and password, you can leave these fields because the user and password of your application take precedence over the ones that you specify here. If the application does not supply a username and password, enter the values. For other options, you can accept the default values or change the values of the options.

    The options on the tab are as follows:

    Data source
    A name that identifies the database connection properties.
    Server
    The hostname or IP address of the system to which the ODBC driver connects.
    Database
    The name of the database on the Netezza Performance Server system.
    Schema
    The name of the schema within the specified database on the Netezza Performance Server system. This field is used for Netezza Performance Server systems that support multiple schemas within a database. If you do not specify a schema, you connect to the default schema of the database.
    Username
    The user ID of the account on the Netezza Performance Server system that is used to access the data source.
    Description
    A description or comment for the data in the data source.
    Port
    The port number for the data source. The default is 5480.
    Password
    The assigned password for the specified user.
    Do not use double quotation marks or semicolons in the password.

  2. Click the Advanced DSN Options tab to display more options. You can change these options or accept the default options. The options on the tab are as follows:

    Read Only
    Restricts queries to read-only access of the database (only SQL Select statements are allowed). To support full access to the database, the option is not selected by default. To allow queries that modify data, do not select the option.
    Show System Tables
    When a list of tables is retrieved, includes system tables in the list. To hide the system tables, the option is not selected by default.
    Legacy SQL Tables Behavior
    Causes the SQLTables API to return a list of all users who own a database. By default, the option is not selected (the list of users is omitted).
    Treat Numeric as Char
    Causes the driver to treat SQL_C_NUMERIC buffers as SQL_C_CHAR buffers. By default, the option is not selected, and SQL_C_NUMERIC buffers are treated as numeric structures.
    Return SQL_BIT as 1/0
    Controls the Boolean value return form when a return type is char or vchar. To configure the SQL_BIT attribute to return numeric Boolean values (1 and 0), select the option. To return character values (true or false), the option is not selected by default.
    Strip CR/LF
    Removes the carriage return and line feed characters from the SQL statements. To retain these control characters in your data, the option is not selected by default.
    Load Round Fraction
    Rounds the returned data for numeric columns whose precision exceeds the precision that is defined in the target table. The option is not selected by default, which means that the data is not rounded.
    Ignore Floating Point Truncation
    Configures ODBC clients on the AIX® platform to return a warning when a floating-point value is truncated during bigint to double conversions and to return the value. By default, the option is not selected; in this case, the query returns an "out of range" error for the floating-point exception.
    Enable Fast Select
    Causes the ODBC driver to skip the data type conversions that occur when ODBC applications run SELECT queries. Skipping the data conversion can improve SELECT query performance, but if the application requests a cast conversion of some kind, the query fails. The option is not selected by default, so by default, the driver performs the data type conversion.
    Force Cache Username
    On a Windows platform, the IBM Performance Server ODBC driver supports by using empty username to enable reading user from Kerberos ticket stored in Kerberos Cache. This avoids supplying the user and password during ODBC connection. For more information on configuring this feature see Configuring single sign-on with Kerberos.
    Connection Pooling
    Enabling the connection pooling feature in the driver manager might cause the same connection to be reused by clients. As a result, for aborted queries, data over socket is not cleared properly and this might lead to an incorrect result for the subsequent query. To avoid such situation, select the Connection Pooling checkbox.
    Note: The Connection Pooling option in Performance Server ODBC driver must be used only if the connection pooling feature is enabled in the driver manager.
    Date Format
    Specifies the date format. You can specify one of the following values:
    • 1 or YMD. This value is the default.
    • 2 or MDY.
    • 3 or DMY.
    On Windows platforms, specify the text value, such as YMD; on other platforms, specify the numeric value, such as 1.
    Client User ID
    Specifies a sample client user ID for the connection. This field supports application monitoring by various tools, such as IBM® InfoSphere® Optim Performance Manager. The maximum length of the field can vary from 128 characters on Windows clients to 1024 characters on UNIX and Linux® clients, including a null byte length.
    Client Workstation Name
    Specifies a sample client workstation name for the connection. This optional field supports application monitoring by various tools, such as IBM InfoSphere Optim Performance Manager. The maximum length of the field can vary from 128 characters on Windows clients to 1024 characters on Linux and UNIX clients, including a null byte length.
    Client Application Name
    Specifies a sample client application name for the connection. This optional field supports application monitoring by various tools, such as IBM InfoSphere Optim Performance Manager. The maximum length of the field can vary from 128 characters on Windows clients to 1024 characters on Linux and UNIX clients, including a null byte length.
    Client Acct String
    Specifies a sample client account string for the connection. This optional field supports application monitoring by various tools, such as IBM InfoSphere Optim Performance Manager. The maximum length of the field can vary from 128 characters on Windows clients to 1024 characters on Linux and UNIX clients, including a null byte length.
    Client Program Info
    Specifies a sample client program information string for the connection. This optional field supports application monitoring by various tools, such as IBM InfoSphere Optim Performance Manager. The maximum length of the field can vary from 128 characters on Windows clients to 1024 characters on Linux and UNIX clients, including a null byte length.
    Login timeout
    Specifies the number of seconds after which to end the login process. Valid values are 0 (no timeout) or any positive number.
    Query timeout
    Specifies the number of seconds after which to end the query request. Valid values are 0 (no timeout) or any positive number.
    Load Max Errors
    Stops the load after N bad input rows. The default is 1, and 0 means no limit, with a maximum of 2147483647 (the largest signed 32-bit integer).
    Connection Retry
    Specifies the number of connection attempts to be tried in a single connection after which to end the connection process. Valid values are 0 (no retry attempt) or any positive number.

  3. Click the SSL DSN Options tab to show Secure Socket Layer options. Specify a value for the security level and enter a certificate authority file name. The options on the tab are as follows:

    Security Level
    Specifies the level of security for the connection:
    Only Unsecured
    Only connections that do not use SSL to encrypt data are accepted. This security level can help increase performance but carries the risks that come with an unencrypted connection.
    Preferred Unsecured
    Connections that do not use SSL are preferred, but a connection is not refused solely because it uses SSL encryption.
    Preferred Secured
    (Default) Connections that use SSL are preferred, but a connection is not refused solely because it does not use SSL encryption.
    Only Secured
    Only connections that use SSL to encrypt data are accepted. This security level increases security but reduces performance somewhat, due to the necessity of encrypting the data stream.
    CA Certificate File
    Specifies the name of the CA file (including the path) that will be used to authenticate connections. If a CA is used for authentication, the name of the CA file must match the name that the system administrator defined for the database to which you are attempting to connect. For more information about security and authentication, see the IBM Netezza® System Administrator’s Guide.

  4. Click the Driver Options tab to configure global driver options. The options on the tab are as follows:

    Enable Debug Logging
    Specifies that the system logs information about each ODBC connection. This information can be helpful in debugging connection problems.
    Log File Path
    Specifies the location of the debug log files. The default is C:\.
    Optimize for ASCII character set
    Helps optimize when you use a particular set of characters. The Netezza Performance Server system uses the Latin9 character encoding for char and varchar types. The character encoding for many Windows systems is similar, not identical. If your database includes values that use only the basic subset of letters (a - z or A - Z), numbers (0 - 9), or punctuation characters, select the Optimize for ASCII character set checkbox, which can help improve query performance. If your data includes special characters such as the Euro symbol, clear the checkbox to ensure that the characters are converted correctly.
    Prefetch Count
    Specifies the number of rows that the driver fetches at a time from a Netezza Performance Server database. The default is 256 rows. To tune your application, set a value that optimizes network use versus memory use. The higher this value, the more memory that is required to hold the rows.
    Socket Buffer Size
    Specifies the size of the communications buffer in bytes. The range is 1 - 32 KB. The default is 8192 bytes.