Netezza Connector > Netezza ODBC SQL ELT optimization > Configuring a Netezza ODBC connection for SQL ELT optimization
  

Configuring a Netezza ODBC connection for SQL ELT optimization

You can set the SQL ELT optimization for the ODBC connection that uses the Netezza ODBC driver to enhance the mapping performance.
You must first configure the Netezza ODBC drivers and create a Netezza ODBC data source name. Netezza supports Netezza ODBC drivers on Windows and Linux systems.
After you configure the Netezza ODBC DSN, you must create an ODBC connection to connect to Netezza. In the ODBC connection properties, select ODBC Subtype as Other and the Driver Manager as Data Direct.
For more information about creating an ODBC connection to connect to Netezza, see the "Creating an ODBC Connection" topic in the help for ODBC Connector.
Use the configured Netezza ODBC connection in a mapping, and select the value of the SQL ELT Optimization property as Full or To Source in the SQL ELT optimization of the mapping task.

Configuring the Netezza ODBC driver on Windows

Before you establish an ODBC connection to connect to Netezza on Windows, you must configure the Netezza ODBC driver and create the Netezza data source name.
Perform the following steps to configure an ODBC connection on Windows:
    1Open the folder in which the ODBC data source file is installed.
    2Run the odbcad32.exe file.
    The ODBC Data Source Administrator dialog box appears.
    3Click System DSN.
    The System DSN tab appears.
    The following image shows the System DSN tab on the ODBC Data Source Administrator (64-bit) dialog box:
    You can view the System DSN on the ODBC Data Source Administrator (64-bit) dialog box.
    4Click Add.
    The Create New Data Source dialog appears.
    The following image shows the Create New Data Source dialog box where you can select the Netezza data source:
    You can select the Netezza data source.
    5Select NetezzaSQL and click Finish.
    6Click Configure.
    The Netezza Configuration dialog box appears.
    The following image shows the DSN options tab:
    In the Netezza Configuration dialog box, you can configure the DSN properties.
    7Specify the following DSN properties:
    Property
    Description
    Data Source
    Name of the data source.
    Description
    Description for the data source.
    Server
    Domain name of your Netezza account.
    Port
    Port number used to connect to Netezza.
    Database
    Name of the Netezza database.
    Schema
    Name of the Netezza schema.
    User Name
    Username to access the Netezza database.
    Password
    Password to access the Netezza database.
    8Click OK.
The Netezza ODBC driver is configured successfully on Windows.

Configuring the Netezza ODBC driver on Linux

Before you can run tasks to connect to Netezza using the ODBC connection from Linux, you must set the ODBCINI, ODBCINST, and LD_LIBRARY_PATH environmental variables for the driver and create the DSN entries.
    1Add the path of the odbc.ini file to the ODBCINI environment variable. For example,
    setenv ODBCINI "/data/home/qamercury/cloud_td/ODBCINI/odbc.ini"
    2To set the ODBCINST environment variable, use the following syntax:
    setenv ODBCINST /data/home/qamercury/cloud_td/ODBCINI/odbcinst.ini
    3To set the LD_LIBRARY_PATH environment variable, use the following syntax:
    setenv LD_LIBRARY_PATH ".:/export/qa_adp/thirdparty/netezza/linux.64/lib64:$LD_LIBRARY_PATH"
    4To set the NZ_ODBC_INI_PATH environment variable, use the following syntax:
    setenv NZ_ODBC_INI_PATH "/path/to/directory/where/we/have/odbc.ini/file
    5Add entries for the Netezza data sources in the odbc.ini file.
    The following section shows a sample entry in the odbc.ini file:

    [Sample Netezza ODBC DSN]
    Driver=/export/qa_adp/thirdparty/netezza/linux.64/lib64/libnzodbc.so
    Description=NetezzaSQL ODBC
    Servername=adaptersnz2.informatica.com
    Port=5480
    Database=ADPQA_DB
    Username=adpqa
    Password=adpqa
    StripCRLF=false
    ReadOnly=false
    ShowSystemTables=false
    DateFormat=1
    NumericAsChar=false
    DebugLogging=true
    6Restart the Secure Agent after you configure the environment variables.

Creating an ODBC connection

You must create an ODBC connection to connect to Netezza after you configure the Netezza ODBC drivers.
Perform the following steps to create a Netezza ODBC connection on the Connections page:
    1In Administrator, click Connections.
    The Connections page appears.
    2Click New Connection.
    The New Connection page appears.
    The following image shows the New Connection page:
    You can view the Netezza ODBC connection configuration details.
    3Configure the following connection details in the Connection Details section:
    Property
    Description
    Connection Name
    Name of the ODBC connection. For example, Netezza_ODBC.
    Description
    Description of the connection.
    Type
    Type of the connection.
    Select the type of the connection as ODBC.
    4Configure the following connection details in the ODBC Connection Properties section:
    Property
    Description
    Runtime Environment
    Runtime environment that contains the Secure Agent you can use to access the system.
    User Name
    Username to log in to the Netezza database.
    Password
    Password to log in to the Netezza database.
    Data Source Name
    Enter the name of the ODBC data source name that you created for the Netezza database.
    Schema
    Name of the Netezza schema.
    Code Page
    The code page of the database server defined in the connection.
    ODBC Subtype
    Enter the value of the ODBC Subtype field as Other.
    Driver Manager for Linux
    The driver that the Netezza ODBC driver manager sends database calls to.
The Netezza ODBC connection is created successfully.