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. 

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)

Netezza System query

Useful Queries :

List of tables in a database. Below Query will give you list of all tables under given database.

SELECT * FROM _V_TABLE WHERE OBJTYPE='TABLE';

List of view in a database. Below query will give you list of all views under given database.

SELECT *  FROM _V_VIEW WHERE OBJTYPE = 'VIEW'

List of tables/views and columns
SELECT * FROM _V_RELATION_COLUMN  ORDER BY NAME,ATTNUM ASC;

List of User Groups

SELECT *  FROM _V_GROUP;


List of Users and their groups
SELECT * FROM _V_GROUPUSERS;

Database & Table Size
SELECT
A.DATABASE AS DATABASENAME
,(A.OBJNAME) AS TABLE_NAME
,A.OBJTYPE AS OBJECT_TYPE
,B.USED_BYTES/POW(1024,3) AS USED_SPACE_IN_GB
FROM _V_OBJ_RELATION_XDB A INNER JOIN
_V_SYS_OBJECT_STORAGE_SIZE B
ON    A.OBJID=B.TBLID
WHERE   OBJECT_TYPE IN ('TABLE')

Tables and Skew Size
SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW FROM _V_TABLE_ONLY_STORAGE_STAT WHERE OBJCLASS = 4905 OR OBJCLASS = 4911 ORDER BY TABLENAME;

Database Size
SELECT _V_OBJ_RELATION_XDB.database  AS "DB Name",
case when sum(_V_SYS_OBJECT_DSLICE_INFO.allocated_bytes) is null then 0 else SUM(_V_SYS_OBJECT_DSLICE_INFO.allocated_bytes)/1073741824 end AS "AllocatedSpace(in GB)"
FROM _V_SYS_OBJECT_DSLICE_INFO  INNER JOIN _V_OBJ_RELATION_XDB  ON _V_OBJ_RELATION_XDB.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid
GROUP BY _V_OBJ_RELATION_XDB.database
ORDER BY _V_OBJ_RELATION_XDB.database

Netezza resource group ?

Run  _v_group view and apply filter as grorsgpercent<>0 .

Example : Select * from _v_group where grorsgpercent<>0 ;

View "_v_group" will also give you all available group (after removing filter condition )



List of Group and Users in Netezza ?
Run view "_V_GROUPUSERS". This will give you list of all users and corresponding group/s. If user fall under multiple group, it will show all possible combination.