Netezza Commands
Table Size
select tablename, used_bytes/pow(1024,4) as
used_TB
from _v_table_storage_stat
where tablename in (
select tablename from _v_table where tablename not like '_VT_%' and tablename not like '_T_%');
Objects owned by a user:
Tip: Use NZ_HELP to get
syntax of all commands
Like - nz_help | grep grant
[nz@dfw86inzb-04 ~]$
nz_help | grep grant
nz_db_group_access_listing To show what groups have been granted
access to what databases.
nz_db_user_access_listing To show what users have been granted
access to what databases.
nz_ddl_grant_group To dump out the SQL/DDL that
represents any access GRANT'ed to a group.
nz_ddl_grant_user To dump out the SQL/DDL that
represents any access GRANT'ed to a user.
nz_get_admin List the administrative
privileges that a user has been granted to a database.
nz_my_grants Dump out all of the
GRANTs associated with a particular user (or group).
[nz@dfw86inzb-04 ~]$
#Estimate the backup size
nzbackup -db XYZ -dir /tmp -dropdata -v
#Reset user Account
alter user Z989888
RESET ACCOUNT;
#Reset the password
ALTER
USER V077277 WITH PASSWORD '@Welcome1';
#check for locked accoutns
select
USENAME, PASSWD, USECREATEDB, VALUNTIL , USELOCKED from _t_user where USENAME='V849588';
V849588
#groom table - whenever you alter any table
Groom table
tablename versions;
nzsqa transactions -sys
nz_altered_tables
-groom
select * from _v_user;
select * from _v_table;
select * from _v_relation_column;
select * from _v_table_index;
select * from _v_objects;
select * from _v_table;
select * from _v_relation_column;
select * from _v_table_index;
select * from _v_objects;
Concurrent Sessions:
SELECT count(1)+4 as
concurrent_sessions, USERNAME FROM SYSTEM.._V_SESSION_BRIEF where
upper(SESSION_STATE_NAME) = upper('active') and CLIENT_PID = 0 group by
USERNAME;
Active Queries:
SELECT * from SYSTEM.._V_SESSION_BRIEF where SQLTEXT like
'%YELP_FF_PRFMNC_STG%';
Alter concurrent
sessions:
LOCK/UNLOCK TABLE
LOCK TABLE
YELP_PPC_PRFMNC_STG IN EXCLUSIVE MODE;
TRUNCATE TABLE
YELP_PPC_PRFMNC_STG;
UNLOCK TABLE YELP_PPC_PRFMNC_STG;
UNLOCK TABLE YELP_PPC_PRFMNC_STG;
select
SESSIONID,DBNAME,RELNAME,USERNAME,USERNAME from _t_pg_locks;
nz_show_locks <db
name> <tablename>
nzsession abort -id
xxxxx # kill connection
GROOM TABLE
GROOM TABLE
YELP_PPC_PRFMNC_STG VERSIONS;
/dU groupname - to see users belongs to the group
nz_groom EDWP
-schema DIMS CALL_DIM
nz_groom dbname
table
GET DDL OF TABLE
nz_ddl_table -d EDWB -schema DIMS -in "CALL_DIM"
Schema creation
Create schema
dbname.schemaname ;
alter schema
EDWD.FCT AUTHORIZATION TO BISETL; -
change owner of schema
Grant Permissions to all users
grant select on
dbname.ALL.TABLE to groupname/username;
Get Table script
[nz@dfw86inzb-03 ~]$
nz_ddl_table dbname tablename
EDIT PASSWORD POLICY
SET SYSTEM DEFAULT
PASSWORDPOLICY TO 'minlen=8, lcredit=0 ucredit=-1 dcredit=-1 ocredit=-2';
GRANT PERMISSION
TEMP TABLE:
GRANT TEMP TABLE TO
user;
If you want to be
explicit about the scope regardless of the database you are connected to, you
can use the "IN" clause.
For a particular
schema:
GRANT TEMP TABLE IN
database.schema TO user;
For all schemas in a
database:
GRANT TEMP TABLE IN
database.all TO user;
CREATE TABLE:
Grant
create table in db.schema to user/group;
Disable Cross Joins
alter user
BOUSERU allow cross join FALSE;
Debug Queries
Nzsql -E
\dSv
Open VI editor from NZ
=> \! Vi
/tmp/filename.out
=>
NZEVENT
nzevent show
template -orient vertical
nzevent copy
Comments
Post a Comment