Template to Create Database with Groups and privileges in IBM Netezza

Template to Create Database with Groups and privileges in IBM Netezza

SQL scripts to create Database along with their groups and Privileges granted to them, here is the template to create a sample database NZ_DB.

-- Database creation for NZ_DB database.

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

create database NZ_DB;

\c NZ_DB

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

-- Create standard security groups.

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

create group NZ_DB_ADM sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_STATS sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_RW sessiontimeout 180 querytimeout 120 defpriority normal maxpriority normal rowsetlimit 0;

create group NZ_DB_RO sessiontimeout 180 querytimeout 120  defpriority normal maxpriority normal rowsetlimit 0;

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

-- Grant permissions to groups.

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

--

-- Grant permissions to NZ_DB_ADM.

--

grant TABLE, EXTERNAL TABLE, TEMP TABLE, VIEW, FUNCTION, PROCEDURE, SEQUENCE to NZ_DB_ADM;

grant LIST, SELECT on NZ_DB to NZ_DB_ADM;

grant ALTER, DELETE, DROP, GENSTATS, INSERT, LIST, SELECT, TRUNCATE, UPDATE on TABLE to NZ_DB_ADM;

grant ALTER, DELETE, DROP, GENSTATS, INSERT, LIST, SELECT, TRUNCATE, UPDATE on EXTERNAL TABLE to NZ_DB_ADM;

grant ALTER, DROP, LIST, SELECT on VIEW to NZ_DB_ADM;

grant INSERT, LIST, SELECT on SYSTEM TABLE to NZ_DB_ADM;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_ADM;

grant ALTER, DROP, LIST, SELECT, UPDATE on SEQUENCE to NZ_DB_ADM;

grant ALTER, DELETE, DROP, INSERT, LIST, SELECT, UPDATE on SYNONYM to NZ_DB_ADM;

grant ALTER, DROP, EXECUTE, LIST on FUNCTION to NZ_DB_ADM;

grant ALTER, DROP, EXECUTE, LIST on PROCEDURE to NZ_DB_ADM;

grant ABORT, LIST on USER to NZ_DB_ADM;

--

-- Grant permissions to NZ_DB_STATS.

--

grant GENSTATS on TABLE to NZ_DB_STATS;

grant GENSTATS on EXTERNAL TABLE to NZ_DB_STATS;

--

-- Grant permissions to NZ_DB_RW.

--

grant LIST, SELECT on NZ_DB to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, TRUNCATE, UPDATE on TABLE to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, TRUNCATE, UPDATE on EXTERNAL TABLE to NZ_DB_RW;

grant LIST, SELECT on VIEW to NZ_DB_RW;

grant INSERT, LIST, SELECT on SYSTEM TABLE to NZ_DB_RW;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_RW;

grant LIST, SELECT, UPDATE on SEQUENCE to NZ_DB_RW;

grant DELETE, INSERT, LIST, SELECT, UPDATE on SYNONYM to NZ_DB_RW;

grant EXECUTE, LIST on FUNCTION to NZ_DB_RW;

grant EXECUTE, LIST on PROCEDURE to NZ_DB_RW;

grant LIST on USER to NZ_DB_RW;

--

-- Grant permissions to NZ_DB_RO.

--

grant LIST, SELECT on NZ_DB to NZ_DB_RO;

grant LIST, SELECT on TABLE to NZ_DB_RO;

grant LIST, SELECT on EXTERNAL TABLE to NZ_DB_RO;

grant LIST, SELECT on VIEW to NZ_DB_RO;

grant LIST, SELECT on SYSTEM TABLE to NZ_DB_RO;

grant LIST, SELECT on SYSTEM VIEW to NZ_DB_RO;

grant LIST, SELECT on SEQUENCE to NZ_DB_RO;

grant LIST, SELECT on SYNONYM to NZ_DB_RO;

grant EXECUTE, LIST on FUNCTION to NZ_DB_RO;

grant EXECUTE, LIST on PROCEDURE to NZ_DB_RO;

grant LIST on USER to NZ_DB_RO;

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

-- Example to add the users to the security groups if you want.

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

-- alter group NZ_DB_ADM add user pleduc;

-- alter group NZ_DB_RO add user pleduc;

-- alter group NZ_DB_RW add user pleduc;

-- alter group NZ_DB_STATS add user pleduc;

SQL Query to list tables occupying more space in SPU and disk IBM Netezza

SQL Query to list tables occupying more space in SPU and disk IBM  Netezza

Find Table list in  your Netezza data slice  which have grown a lot and now you want to check which all tables are consuming space on those data slices/disk so that you can try to find if those tables needs to be groomed, etc.

This SQL provide tables list which are using more than 5000 MB on hwid 1009 disk only

select objname as table_name,       database as db_name,       hwid as spu_id, dsid,       (allocated_bytes/1048576)

as allocated_mbytes  from _v_sys_relation_xdb sys,       _v_sys_object_dslice_info ds

 where ds.tblid = sys.objid and allocated_mbytes > 500 and hwid=1009 order by  allocated_mbytes desc,

table_name, db_name, dsid;