Configuring access to Netezza databases on Linux and UNIX

To configure access to Netezza databases, you must specify parameters in the .odbcinst.ini file to configure the Netezza ODBC driver and also modify the .odbc.ini file to configure the data sources.

Before you begin

Install client libraries.

About this task

If an .odbcinst.ini configuration file exists, you can modify the same file. If there is no existing .odbcinst.ini configuration file, then you can use the odbcinst.ini.sample to create the .odbcinst.ini configuration file. In most scenarios, you can use the contents of the odbcinst.ini.sample file without any changes. However, in the following scenarios, you must change the configuration file:

If your client system was configured for ODBC drivers other than the Netezza ODBC driver and you want to continue to use those ODBC drivers, do not modify the existing entries in the .odbcinst.ini file. Add an entry for the Netezza ODBC driver at the end of the existing contents of the .odbcinst.ini file.
If the Netezza client software and a Netezza ODBC driver were installed on your client system, check if the Netezza ODBC driver is configured. If it is not, add an entry to the end of the existing contents of the .odbcinst.ini file.

If the .odbc.ini configuration file exists in your home directory (For example, /home/myname) check if it contains entries for the Netezza appliance data sources to access. If it does not, copy the contents of the odbc.ini.sample file to the end of your existing .odbc.ini configuration file. Do not modify any existing entries in the file.

If you are using the InfoSphere® Information Server version of the .odbc.ini configuration file on Linux, create a symbolic link in the folder where the configuration file exists to make sure that the Netezza connector works correctly:

Log on as the InfoSphere DataStage® administrator.
To change to the installation directory of InfoSphere Information Server, enter the command: cd /opt/IBM/InformationServer/Server/DSEngine .
To create a symbolic link, enter the command: ln -s .odbc.ini odbc.ini.

Procedure

Log in using your user ID and password.
Configure the Netezza ODBC driver:
1. Copy the contents of the /usr/local/nz/lib/odbcinst.ini.sample file.
2. Modify the configuration entries depending on your requirement. Consult your Netezza system administrator to check if you must modify any specific configuration entries for your installation.
3. Save the file as .odbcinst.ini.
Configure the Netezza appliance data sources:
1. Copy the contents of odbc.ini.sample file into your home directory (for example, /home/myname) and rename it .odbc.ini.
2. Optional: To add the Netezza data sources to an existing .odbc.ini file, add the lines after [NZSQL] from the sample file to the existing .odbc.ini file. In the [ODBC Data Sources] section, add NZSQL = NetezzaSQL to the list of data source names.
3. Save and close the file.

Set the environment variables:

export ODBCINI=path_to_odbc.ini_file
export NZ_ODBC_INI_PATH=location_of_odbc.ini_file

Example: $DSHOME/dsenv for 64-bit InfoSphere DataStage on AIX

    NZClientDir=/usr/local/nz

    LIBPATH=$LIBPATH:$NZClientDir/lib64
    export LIBPATH

    PATH=$PATH:$NZClientDir/bin
    export PATH

    NZ_ODBC_INI_PATH=$DSHOME
    export NZ_ODBC_INI_PATH

Note: If the Netezza entries were added to an existing odbc.ini file, set only the NZ_ODBC_INI_PATH variable.

Add ODBC DSN for Netezza database to the $DSHOME/.odbc.ini file.

Example: $DSHOME/.odbc.ini for 64-bit InfoSphere DataStage on AIX


    [ODBC Data Sources]
    NPS=Netezza database

    [NPS]
    Driver=/usr/local/nz/lib/libnzsqlodbc3_64bit.so
    Description=Netezza database
    Servername=my-nps-host
    Port=5480
    Database=mydb1
    Username=guest
    Password=
    ReadOnly=false
    FastSelect=false
    ShowSystemTables=false
    LegacySQLTables=false
    LoginTimeout=0
    QueryTimeout=0
    DateFormat=1
    NumericAsChar=false
    SQLBitOneZero=false
    StripCRLF=false
    securityLevel=preferredUnSecured
    caCertFile=
    ;loadMaxErrors=1
    ;loadRoundFraction=0

Note: For 32-bit InfoSphere DataStage, use a 32-bit driver like libnzsqlodbc3.so for the Driver property. See Data source configuration file for information on the meanings of the Netezza ODBC DSN properties.

Verify the configuration.
1. Make sure it is possible to access the Netezza database using the Netezza native client. For example, to verify the preceding sample DSN, run the command nzsql -host my-nps-host -u nzuser -pw uzpassword -db mydb1.
2. Run the command $DSHOME/../branded_odbc/samples/example/example to test the ODBC connectivity.

To restart the server engine and the ASB Agent, enter the following command:

cd Install_directory/Server/DSEngine/bin
./uv -admin -stop
./uv -admin -start
cd Install_directory/ASBNode/bin
. ./NodeAgents_env_DS.sh
./NodeAgents.sh stopAgent
./NodeAgents.sh start