Netezza: SQL and Admin: June 2016

Thursday, June 30, 2016

Netezza: how to install Netezza ODBC driver on Linux

Download driver installation software from IBM Fix central.

Refer to post: how to download Linux ODBC driver for Netezza from IBM fix central.

Put downloaded file into temporary directory, so you can clean it later:

mv ~/nz-linuxclient-v7.2.1.2-P1.tar.gz /tmp/install2detete

Untar package:

tar xvf nz-linuxclient-v7.2.1.2-P1.tar.gz

[root@sm3 nz.7.2.1.2]# tar xvf nz-linuxclient-v7.2.1.2-P1.tar.gz

datadirect.package.tar.z

linux/

linux/npsclient.7.2.1.2-P1.tar.gz

linux/unpack

linux64/

linux64/npsclient.7.2.1.2-P1.tar.gz

linux64/unpack

webadmin/

webadmin/npswebclient.7.2.1.2-P1.tar

webadmin/unpack

[root@sm3 nz.7.2.1.2]# ll

total 382572

-rw-r--r-- 1 10507 12009 71226753 May 26 22:45 datadirect.package.tar.z

drwxr-xr-x 2 10507 12009 4096 May 26 22:45 linux

drwxr-xr-x 2 10507 12009 4096 May 26 22:45 linux64

-rw-r--r-- 1 UUUUU1 UUUUU1 320115759 Jun 30 14:34 nz-linuxclient-v7.2.1.2-P1.tar.gz

drwxr-xr-x 2 10507 12009 4096 May 26 22:45 webadmin

Install 64-bit driver:

./unpack

[root@sm3 linux64]#cd ./linux64

[root@sm3 linux64]# ll

total 28556

-rw-r--r-- 1 10507 12009 29190391 May 24 21:34 npsclient.7.2.1.2-P1.tar.gz

-rwxr-xr-x 1 10507 12009 14798 May 24 21:34 unpack

[root@sm3 linux64]#

-------------------------------------------------------------------------------

IBM Netezza -- NPS Linux Client 7.2.1.2-P1

-------------------------------------------------------------------------------

Validating package checksum ... ok

Where should the NPS Linux Client be unpacked? [/usr/local/nz] /usr/local/nz.7.2.1.2

Installing in an existing directory. Changing permissions to overwrite existing files...

0% 25% 50% 75% 100%

|||||||||||||||||||||||||||||||||||||||||||||||||||

Unpacking complete.

Test installation:

./nzrev

./nzodbcsql -h 10.99.97.67 -d nzdba -u UUUUU -p XXXXX -q 'select count(*) from _v_table;'

./nzodbcsql -c "Driver={NetezzaSQL};servername=10.99.97.67;port=5480;database=SYSTEM;username=UUUUU;password=XXXXX;"

./nzsql -h 10.99.97.67 -d nzdba -u UUUUU -pw XXXXX -c 'select count(*) from _v_table;'

[root@sm3 bin]# nzrev

Release 7.2.1.2-P1 [Build 48187]

[root@sm3 bin]# /usr/local/nz.7.2.1.2/bin/nzodbcsql -h 10.99.97.97 -d nzdba -u UUUUU -p XXXXX -q 'select count(*) from _v_table;'

COUNT

-------

369

Rows Returned : 1

[root@sm3 bin]# /usr/local/nz.7.2.1.2/bin/nzodbcsql -c "Driver={NetezzaSQL};servername=10.99.97.97;port=5480;database=SYSTEM;username=UUUUU;password=XXXXX;"

NZODBCSQL - program to test Netezza ODBC driver

NOT FOR PRODUCTION USE

Type 'quit' or '\q' or CTRL-D or ENTER at the prompt to quit

NOTE: Max 100 rows are displayed (for selects)

Driver version : 'Release 7.2.1.2-P1 [Build 48187]'

NPS version : '07.02.0000 Release 7.2.0.5-P1 [Build 45115]'

Database : 'SYSTEM'

nzodbc > \q

[root@sm3 bin]# /usr/local/nz.7.2.1.2/bin/nzsql -h 10.99.97.97 -d nzdba -u UUUUU -pw XXXXX -c 'select count(*) from _v_table;'

COUNT

-------

369

(1 row)

COMMAND REFERENCE:

[root@sm3 nz.7.2.1.2]# ./nzodbcsql -h

NZODBCSQL - program to test Netezza ODBC driver

NOT FOR PRODUCTION USE

Usage : nzodbcsql [options] [ Db [ User [ Password ] ] ]

options :

-h <host> : Host/server to connect [NZ_HOST/localhost]

-d <database> : Database to connect [NZ_DATABASE]

-c <conn-string> : Connection string

-n <dsn> : Datasource Name

-u <user> : User to connect [NZ_USER]

-p <password> : Password to use [NZ_PASSWORD]

-m <max-rows> : Maximum rows displayed for selects [100]

-f <sql-file> : Sql-file as input (non-interactive)

-q <sql-query> : Sql-query as input (non-interactive)

Option precedence (low to high) : Env-variables, DSN, Conn-string, Cmd-line

Atleast one of DSN, Conn-string and Host/Db/User/Passwd is required

TROUBLESHOOTING KNOW ISSUES:

Verify that executables have necessary permissions.

Example:

[root@sm3 bin]# ls -lh /usr/local/nz.7.2.1.2/bin

total 197M

lrwxrwxrwx 1 10507 12009 19 Jun 30 14:41 libcrypto.so.10 -> libcrypto.so.1.0.1e

-r--r--r-- 1 10507 12009 6.2M May 24 21:02 libcrypto.so.1.0.1e

-rwxr-xr-x 1 10507 12009 24M May 24 21:13 libnzodbc_nzload.so

lrwxrwxrwx 1 10507 12009 16 Jun 30 14:41 libssl.so.10 -> libssl.so.1.0.1e

-r--r--r-- 1 10507 12009 1.7M May 24 21:02 libssl.so.1.0.1e

-rwxr-xr-x 1 10507 12009 22M May 24 21:13 nzconvert

-rwxr-xr-x 1 10507 12009 12M May 24 21:11 nzds

-rwxr-xr-x 1 10507 12009 12M May 24 21:11 nzevent

-rwxr-xr-x 1 10507 12009 12M May 24 21:11 nzhw

-rwxr-xr-x 1 10507 12009 9.8M May 24 21:13 nzload

-rwxr-xr-x 1 10507 12009 31K May 24 21:15 nzodbcsql

-rwxr-xr-x 1 10507 12009 11M May 24 21:10 nzpassword

-rwxr-xr-x 1 10507 12009 8.9K May 24 21:11 nzreclaim

-rwxr-xr-x 1 10507 12009 7.8M May 24 21:10 nzrev

-rwxr-xr-x 1 10507 12009 12M May 24 21:10 nzsession

-rwxr-xr-x 1 10507 12009 12M May 24 21:11 nzspupart

-rwxr-xr-x 1 10507 12009 26M May 24 21:13 nzsql

-rwxr-xr-x 1 10507 12009 11M May 24 21:10 nzstate

-rwxr-xr-x 1 10507 12009 12M May 24 21:11 nzstats

-rwxr-xr-x 1 10507 12009 11M May 24 21:10 nzsys

Verify that soft link to odbc.ini file is pointing to correct file.

Verify file references in odbcinst.ini pointing to existing files or soft links.

Revise variable, if necessary, set variables:

Example:

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/local/nz/bin/:/usr/local/nz/lib/:/usr/local/nz/lib64/:

export ODBCINI=/usr/local/nz/bin/odbc.ini

export PATH=${PATH}:/usr/local/nz/bin

export NZ_ODBC_INI_PATH=/usr/local/nz/bin

MORE DOCUMENTATION:

Install the Netezza CLI client on a Linux/UNIX system (7.2.1)

Configuring the Netezza ODBC driver and creating the data source (Linux and UNIX)

Netezza: how to download Linux driver from IBM fix central

How to download Linux ODBC driver for Netezza from IBM fix central.

Also see post: how to install Netezza ODBC driver on Linux.

For download go to: https://www-945.ibm.com/support/fixcentral/

Tuesday, June 28, 2016

IBM Netezza SQL toolkit installation walk through

IBM Netezza SQL toolkit installation walk through (documentation Version 7.2)

Download installation package at https://www-945.ibm.com/support/fixcentral/

1) Create target database where SQL Tooklit to install into:

nzsql -u ADMIN -pw 'XXXX' -c "create database SQLX";

2) Create installation directory where the executable files will be installed:

mkdir /export/home/nz/sqlext

3) Untar installation bundle:

tar xvf sql-ext-7.2.1.1.tar.gz

[nz@server tmp]$ tar xvf sql-ext-7.2.1.1.tar.gz

Netezza_sql_toolkit.pdf

sqlext.package.tar.z

SQL_Toolkit_Readme.txt

[nz@sdw1 tmp]$ ll

total 11776

-rw-r--r-- 1 nz nz 1801793 Feb 4 23:28 Netezza_sql_toolkit.pdf

-rw-rw-r-- 1 nz nz 5960751 Jun 28 14:19 sql-ext-7.2.1.1.tar.gz

-rw-r--r-- 1 nz nz 4247503 Feb 4 23:28 sqlext.package.tar.z

-rw-r--r-- 1 nz nz 13690 Feb 4 23:28 SQL_Toolkit_Readme.txt

4) Untar installation package:

tar xvf sqlext.package.tar.z

[nz@server tmp]$ tar xvf sqlext.package.tar.z

libnetcrypto-7.2.1.1.tar.gz

libnetxml-7.2.1.1.tar.gz

[nz@sdw1 tmp]$ ll

total 15968

-rw-r--r-- 1 nz nz 3244116 Feb 4 16:56 libnetcrypto-7.2.1.1.tar.gz

-rw-r--r-- 1 nz nz 1034755 Feb 4 16:57 libnetxml-7.2.1.1.tar.gz

-rw-r--r-- 1 nz nz 1801793 Feb 4 23:28 Netezza_sql_toolkit.pdf

-rw-rw-r-- 1 nz nz 5960751 Jun 28 14:19 sql-ext-7.2.1.1.tar.gz

-rw-r--r-- 1 nz nz 4247503 Feb 4 23:28 sqlext.package.tar.z

-rw-r--r-- 1 nz nz 13690 Feb 4 23:28 SQL_Toolkit_Readme.txt

5) Untar installer for libnetcrypto:

tar xvf libnetcrypto-7.2.1.1.tar.gz

[nz@server tmp]$ tar xvf libnetcrypto-7.2.1.1.tar.gz

libnetcrypto/

libnetcrypto/7.2.1.1/

libnetcrypto/7.2.1.1/libnetcrypto.o_spu10

libnetcrypto/7.2.1.1/libnetcrypto.o_x86

libnetcrypto/7.2.1.1/install.pm

libnetcrypto/7.2.1.1/install

libnetcrypto/7.2.1.1/README

libnetcrypto/7.2.1.1/licenses/

libnetcrypto/7.2.1.1/licenses/sqlToolkit_licenses.zip

libnetcrypto/7.2.1.1/RELEASE_NOTES

libnetcrypto/7.2.1.1/sqlext-version.txt

libnetcrypto/7.2.1.1/libbotan_host.so

libnetcrypto/7.2.1.1/libbotan_spu.so

libnetcrypto/7.2.1.1/netfpe.o_spu10

libnetcrypto/7.2.1.1/netfpe.o_x86

6) Untar installer for libnetxml:

tar xvf libnetxml-7.2.1.1.tar.gz

[nz@server tmp]$ tar xvf libnetxml-7.2.1.1.tar.gz

libnetxml/

libnetxml/7.2.1.1/

libnetxml/7.2.1.1/libnetxml.o_spu10

libnetxml/7.2.1.1/libnetxml.o_x86

libnetxml/7.2.1.1/install.pm

libnetxml/7.2.1.1/install

libnetxml/7.2.1.1/README

libnetxml/7.2.1.1/licenses/

libnetxml/7.2.1.1/licenses/sqlToolkit_licenses.zip

libnetxml/7.2.1.1/RELEASE_NOTES

libnetxml/7.2.1.1/sqlext-version.txt

7) Install packages into target SQLX database:

cd /export/home/nz/sqlext/libnetcrypto/7.2.1.1

./install -db SQLX -u ADMIN -pw 'XXXX'

cd /export/home/nz/sqlext/libnetxml/7.2.1.1

./install -db SQLX -u ADMIN -pw 'XXXX'

8) Test and verification:

[nz@server 7.2.1.1]$ nzsql -db SQLX -u ADMIN -pw 'XXXX'

Welcome to nzsql, the IBM Netezza SQL interactive terminal.

Type: \h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

SQLX.ADMIN(ADMIN)=> SELECT CRYPTO_VERSION();

CRYPTO_VERSION

--------------------------------------------------------------------

IBM Netezza SQL Extensions Crypto Library Version 7.2.1.1 Build ()

(1 row)

SQLX.ADMIN(ADMIN)=> SELECT REGEXP_VERSION();

REGEXP_VERSION

--------------------------------------------------------------------------------------

IBM Netezza SQL Extensions XML / Regular Expression Library Version 7.2.1.1 Build ()

(1 row)

Done

Thursday, June 23, 2016

Netezza: Split delimited string how to

Split delimited string using Netezza SQL Extensions Toolkit

Also see post how to JOIN or concatenate string: Netezza: concatenate rows into string with XMLAGG()

Alternative 1:

Using ARRAY_SPLIT function that strips off a delimiter.

select SQL_EXTENSIONS..ARRAY_COMBINE(

SQL_EXTENSIONS..ARRAY_SPLIT( '1,2,3,,,4,5,abc,mama,al123num,',',' )

, '|'

);

Alternative 2:

Using regexp_extract_all function that preserves the delimiter.

select SQL_EXTENSIONS..array_combine(

SQL_EXTENSIONS..regexp_extract_all( '1,2,3,,,4,5,abc,mama,al123num,', '.*?,')

, '|'

);

Refer to IBM Netezza SQL Extensions Toolkit 7.2 documentation.