Thursday, October 26, 2017

Changing default passwords

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.1.0/com.ibm.nz.npds.doc/t_change_password.html

Wednesday, April 5, 2017

Creating an administrative user group

Procedure

Connect to the System database as the admin user. For example:
[nz@nzhost ~]$ nzsql -d system -u admin -pw password
Create a group for your administrative users. For example:
SYSTEM.ADMIN(ADMIN)=> CREATE GROUP administrators;
Grant the group all administrative permissions. For example:
SYSTEM.ADMIN(ADMIN)=> GRANT ALL ADMIN TO administrators WITH GRANT OPTION;
Grant the group all object permissions. For example:
SYSTEM.ADMIN(ADMIN)=> GRANT ALL ON DATABASE, GROUP, SCHEMA, SEQUENCE,SYNONYM, TABLE, EXTERNAL TABLE, FUNCTION, AGGREGATE, USER, VIEW, PROCEDURE, LIBRARY TO administrators WITH GRANT OPTION;
Grant the group select permissions on all system and management objects. For example:
SYSTEM.ADMIN(ADMIN)=> GRANT LIST, SELECT ON SYSTEM TABLE, SYSTEM VIEW, MANAGEMENT TABLE, MANAGEMENT VIEW TO administrators WITH GRANT OPTION;
Add users to the group to grant them the permissions of the group. For example:
SYSTEM.ADMIN(ADMIN)=> ALTER USER nzuser WITH IN GROUP administrators;

GRANT LIST,SELECT,DELETE,UPDATE,INSERT,TRUNCATE,GROOM,ALTER,ABORT ON SANDBOX..TABLE TO SANDBOX_ADMIN;

Tuesday, February 21, 2017

is date function in Netezza

Check for valid date/time-stamp
There is so such built-in function , but you can user regexp to validate the value. Below the code. It will return true if it is valid time-stamp else false.

Select SQLEXT.regexp_like(current_timestamp ,'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$')

CASE WHEN REGEXP_LIKE(COl1,'[0-9]' ) THEN 'NUMBER ONLY' ELSE COl1 END as COl1

nz_migrate - Migrating data from one netezza box to another (or same) box

nz_migrate

Purpose of this command is to copy or migrate data from one one NPS server to another. It can be also used to make a copy of a database/table on the same server.

Sample Command :
./nz_migrate -shost <source Host name> -thost <Target Host name> -sdb <Source Database Name> -tdb <Target Database Name> -suser <Source User Name> -spassword <Source Password> -tuser <Target User Name> -tpassword <Target Password> -t <list of tables , seperated by comma. > -cksum fast -genStats Full -threads 5 -CreateTargetTable yes -CreateTargetDatabase yes >> /tmp/nz_migrate_0220_3.log &

If you are want to migrate all table under database (don't mention -t flag) -

./nz_migrate -shost <source Host name> -thost <Target Host name> -sdb <Source Database Name> -tdb <Target Database Name> -suser <Source User Name> -spassword <Source Password> -tuser <Target User Name> -tpassword <Target Password> -cksum fast -genStats Full -threads 5 -CreateTargetTable yes -CreateTargetDatabase yes >> /tmp/nz_migrate_0220_3.log &

If you are migrating data to new box, I will recommend you to run this command on new box. If you run this command on new box, it will save some memory and processing of your old box.

Note : You might get some error message saying "group/user "username" not found, "function 'COVAR_POP(FLOAT8, FLOAT8)' does not exist" , ignore it while doing the migration. These errors might not have any impact on data transfer.

Tuesday, January 24, 2017

SQuirreL SQL Client - for netezza on window/linux,mac

SQuirreL SQL Client

SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc. The minimum version of Java supported is 1.6.x as of SQuirreL version 3.0.
SQuirreL's functionality can be extended through the use of plugins.

My client having issue connecting netezza on mac using any sql client tool. He was forced to use "Parallels" to install window client tool to connect to netezza.

SQuirreL SQL Client is java based tool ,which could be installed on any OS , including mac, window, linux etc.

Download and Install client -

Go to http://squirrel-sql.sourceforge.net/
Download required installer
Follow steps to install Squirrel client. You need to follow step by step wizard. Its very simple.

After installing SQuirreL tool, first step is to configure it for netezza.
Below the steps to configure this tool -

Create new Driver for Netezza

You will find “Aliases” & “Drivers” tabs on left pan. Click on “Drivers” tab -> Click on “+” icon to add new Drivers.

Drivers

Name : Give some user friendly name
Example URL: jdbc:netezza://hostname or ip address:5480/databasename
Website URL : jdbc:netezza:// hostname or ip address:5480/databasename
Extra Class Path : Click on “Add” to browse the netezza jar file. If you don't have netezza jdbc driver (jar file), you can dowload it from IBM.
Class Name : org.netezza.Driver

Click OK

Once you configured and created driver, its time to create/configure alias.

Click on “Aliases” tab on left pan
Click on “+” icon to add new aliases.
Add Alias window will open.
Name : Pass user friendly name
Drivers : Select you drivers, which you have created in previous step
URL : jdbc:netezza://hostname or ip address:5480/database name
User name : Your Netezza User name
Password : Your netezza password
Click on “Test” to test the connection

Once test connection successful , now its time to run your query
Select your netezza Aliases name.
Click on “Connect to Selected Alias (Control O)” icon
Now to to “SQL” tab and run you query

Thanks & Good luck. Post me question, in case you have any problem configuring this tool.

Monday, January 9, 2017

Netezza - Installing UDF

Netezza is not showing UDF (User Defined Function) ?

Solution: You probably need to install SQL Extensions toolkit.

You may find the installer at below path. Or just find libnetcrypto folder.
You may find installer under libnetcrypto folder or one folder beneath.

Login as nz user.

cd /export/home/nz/sqlext/libnetcrypto
Once you find "install", run below command.
./install -d dbname -schema schemaname -u username -W password

Additional link -
https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.sqltk.doc/t_sqlext_enabling.html#t_sqlext_enabling

[nz@hostname]$ cd /export/home/nz/apps/libnetcrypto/7.2.1.3/
[nz@hostname]$ ./install -d dw_audit
Installing libnetcrypto to default schema of database dw_audit in unfenced mode: .................................................. Done
[nz@hostname]$ pwd
/export/home/nz/apps/libnetxml/7.2.1.3
[nz@hostname]$ ./install -d databasename
Installing libnetxml to default schema of database databasename in unfenced mode: ............................................ Done

[nz@hostname]$ nzsql
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

SYSTEM.ADMIN(ADMIN)=> \c databasename
You are now connected to database dw_audit.
databasename.NZUSER(ADMIN)=> select regexp_version();
REGEXP_VERSION
--------------------------------------------------------------------------------------
IBM Netezza SQL Extensions XML / Regular Expression Library Version 7.2.1.3 Build ()
(1 row)

databasename.NZUSER(ADMIN)=> SELECT CRYPTO_VERSION();
CRYPTO_VERSION
--------------------------------------------------------------------
IBM Netezza SQL Extensions Crypto Library Version 7.2.1.3 Build ()
(1 row)

databasename.NZUSER(ADMIN)=> select REGEXP_MATCH_COUNT('This is comcast sytem.','is');
REGEXP_MATCH_COUNT
--------------------
2
(1 row)