How to enable Query history on Netezza systems

Below are the sample steps that you can follow up to create and enable the history database:

1) Add users and grant appropriate access to them:

system(admin)=> create user owner with password 'ownerpw‘;
system(admin)=> grant create database to owner;
system(admin)=> create user audituser with password ‘audituserpw‘;
system(admin)=> grant list on audituser to owner;

2) Create history database using  “nzhistcreatedb” command:

$ nzhistcreatedb -d qhist -t q -u audituser -o owner -p 'ownerpw' -v 1

3) Maintenance of history database using  “nzhistcleanupdb” c- ommand:

$ nzhistcleanupdb -d qhist -u owner -pw 'ownerpw' –t ‘2011-10-31’

4) Create history configuration:

$ nzsql -c "CREATE HISTORY CONFIGURATION QUERY_HISTORY HISTTYPE QUERY NPS LOCALHOST DATABASE QHIST USER audituser PASSWORD 'audituserpw' COLLECT QUERY,PLAN,TABLE,COLUMN LOADINTERVAL 0 LOADMINTHRESHOLD 0 LOADMAXTHRESHOLD 1 STORAGELIMIT 10 LOADRETRY 1 ENABLEHIST TRUE ENABLESYSTEM TRUE VERSION 1;” VERSION 1;"

5) Enable history collection:

$ nzsql –c “SET HISTORY CONFIGURATION QUERY_HISTORY;”

6) Restart NPS using command:

$ nzstop;nzstart

How to do manual vac cum for Netezza database?

login as: root
root@server_name's password:
Last login: Thu Oct 30 17:35:29 2014 from xa65p020.domain.com
[root@server_name ~]# su - nz

     server_name NPS environment
==================================================

     HOME                 = /export/home/nz
     USER                 = nz

     NZ_USER              = admin
     NZ_DATABASE          = system


     /nz/data/config/system.cfg settings
==================================================

    startup.maxConnections    = 1000
    system.enableCTA2         = 1


[nz@server_name ~]$ date
Thu Oct 30 17:59:39 PDT 2014
[nz@server_name ~]$ nzrev
Release 7.0.2 (P-8) [Build 32960]
[nz@server_name ~]$ nzhw -issues
No entries found
[nz@server_name ~]$ nzds -issues
No entries found
[nz@server_name ~]$ nzds -regenstatus
No entries found
[nz@server_name ~]$ nzstats

Field Name           Value
-------------------- -------------------------------------------
Name                 server_name
Description          ddcnsdwodbcp01.domain.com
Contact            

Num SFIs             0
Num SPAs             2
Num SPUs             12
Num Data Slices      92
Num Hardware Issues  0
Num Dataslice Issues 0

[nz@server_name ~]$ nz_catalog_size > /tmp/nz_catalog_size_before.out
[nz@server_name ~]$ vi /tmp/nz_catalog_size_before.out
You have new mail in /var/spool/mail/nz
[nz@server_name ~]$ nzsession | grep -i active
1952508 sql      ADMIN        30-Oct-14, 18:02:16 PDT 10438 SYSTEM        active normal            127.0.0.1      10436 SELECT session_id, clien
[nz@server_name ~]$ /tmp/nz_catalog_size_before.out
[nz@server_name ~]$ ./nzverifysyscat -K /nz/kit -D /nz/data -P
-bash: ./nzverifysyscat: No such file or directory
[nz@server_name ~]$ cd /nz/kit/bin/adm/
[nz@server_name adm]$ ./nzverifysyscat -K /nz/kit -D /nz/data -P
        get database list
        verify syscat   IBM_NETEZZA_CUSTOMER_SERVICE
     
[nz@server_name adm]$ nzstate
System state is 'Online'.
[nz@server_name adm]$ nzstop
[nz@server_name adm]$ nzstate
System state is 'Stopped'.
[nz@server_name adm]$ nz_manual_vacuum

Confirmed that the database is stopped.

Creating a backup of /nz/data.  Please wait.

Backup completed.


#######################################################
#
# Database 1 of 12
#
# Vacuuming and Reindexing database 'IBM_NETEZZA_CUSTOMER_SERVICE'
#
#######################################################


NOTICE:  database system was shut down at 2014-10-30 18:09:47 PDT
NOTICE:  database system is in production state
DEBUG:  Session id is 16001

POSTGRES backend interactive interface
Revision: 7.1  Date: 2004/11/17 01:54:14

backend> DEBUG:  VACUUM: Opened all system relations
DEBUG:  VACUUM: vacuuming '_T_TYPE'
DEBUG:  Index _I_TYPE_OID: Pages 2; Tuples 37. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_TYPE_TYPNAME: Pages 2; Tuples 37. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_ATTRIBUTE'
DEBUG:  Index _I_ATTRIBUTE_RELID_ATTNAM: Pages 203; Tuples 10564: Deleted 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_ATTRIBUTE_RELID_ATTNUM: Pages 17; Tuples 10564: Deleted 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_CLASS'
DEBUG:  Index _I_CLASS_OID: Pages 2; Tuples 887: Deleted 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_CLASS_RELNAME: Pages 19; Tuples 887: Deleted 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_INHERITS'
DEBUG:  Index _I_INHERITS_RELID_SEQNO: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_INDEX'
DEBUG:  Index _I_INDEX_INDRELID: Pages 2; Tuples 98. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_INDEX_INDEXRELID: Pages 2; Tuples 98. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_STATISTIC'
DEBUG:  Index _I_STATISTIC_RELID_ATT: Pages 2; Tuples 304: Deleted 52. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_OPERATOR'
DEBUG:  Index _I_OPERATOR_OID: Pages 2; Tuples 664. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_OPERATOR_OPRNAME_L_R_K: Pages 15; Tuples 664. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_OPCLASS'
DEBUG:  Index _I_OPCLASS_OID: Pages 2; Tuples 32. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_OPCLASS_DEFTYPE: Pages 2; Tuples 32. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_OPCLASS_NAME: Pages 2; Tuples 32. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_AM'
DEBUG:  Index _I_AM_OID: Pages 2; Tuples 4. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_AM_NAME: Pages 2; Tuples 4. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_AMOP'
DEBUG:  Index _I_AMOP_OPID: Pages 2; Tuples 186. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_AMOP_STRATEGY: Pages 2; Tuples 186. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_AMPROC'
DEBUG:  VACUUM: vacuuming '_T_LANGUAGE'
DEBUG:  Index _I_LANGUAGE_NAME: Pages 2; Tuples 5. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_LANGUAGE_OID: Pages 2; Tuples 5. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_LARGEOBJECT'
DEBUG:  Index _I_LARGEOBJECT_LOID_PN: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_AGGREGATE'
DEBUG:  Index _I_AGGREGATE_NAME_NARG_TYPE: Pages 10; Tuples 193. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_AGGREGATE_OID: Pages 2; Tuples 193. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_IPL'
DEBUG:  VACUUM: vacuuming '_T_INHERITPROC'
DEBUG:  VACUUM: vacuuming '_T_REWRITE'
DEBUG:  Index _I_REWRITE_OID: Pages 2; Tuples 457. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_REWRITE_RULENAME: Pages 11; Tuples 457. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_LISTENER'
DEBUG:  Index _I_LISTENER_PID_RELNAME: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_DESCRIPTION'
DEBUG:  Index _I_DESCRIPTION_OBJOID: Pages 4; Tuples 1444. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_DIST_MAP'
DEBUG:  Index _I_DIST_MAP_SEQ: Pages 2; Tuples 15. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_DIST_MAP_RELID: Pages 2; Tuples 15. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_ORGANIZE_ON'
DEBUG:  VACUUM: vacuuming '_T_GROOM_HISTORY'
DEBUG:  VACUUM: vacuuming '_T_THIN'
DEBUG:  VACUUM: vacuuming '_T_ALTBASE'
DEBUG:  VACUUM: vacuuming '_T_ACTIONFRAG'
DEBUG:  Index _I_ACTIONFRAG_EVCLASS: Pages 2; Tuples 564. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_SELCACHE'
DEBUG:  VACUUM: vacuuming '_T_EXTERNAL'
DEBUG:  VACUUM: vacuuming '_T_EXTOBJECT'
DEBUG:  VACUUM: vacuuming '_T_EXTZONES'
DEBUG:  VACUUM: vacuuming '_T_CONSTRAINT'
DEBUG:  VACUUM: vacuuming '_T_CONST_RELATTR'
DEBUG:  VACUUM: vacuuming '_T_CONST_REFATTR'
DEBUG:  VACUUM: vacuuming '_T_SYNONYM'
DEBUG:  VACUUM: vacuuming '_T_BACKUP_GROUP'
DEBUG:  VACUUM: vacuuming '_T_BACKUP_GROUP_MEMBER'
DEBUG:  VACUUM: vacuuming '_T_RESTORE_ATTRESTOID'
DEBUG:  VACUUM: vacuuming '_T_LIBRARY'
DEBUG:  Index _I_LIBRARY_NAME: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_UDXATTRIBUTE'
DEBUG:  Index _I_UDXATTRIBUTE_RELID_ATTNAM: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_UDXATTRIBUTE_RELID_ATTNUM: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_SCSI_ERRORS'
DEBUG:  Index _I_SCSI_ERRORS_SCSI_ERRID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_SCSI_ERRORS_SCSI_HWID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_SPACOMP'
DEBUG:  VACUUM: vacuuming '_T_VT_HOSTTX'
DEBUG:  VACUUM: vacuuming '_T_MD_REGEN_ERRORS'
DEBUG:  Index _I_MD_REGEN_ERRORS_MD_ERRID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_MD_REGEN_ERRORS_MD_HWID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_MD_REGEN_ERRORS_MD_TBLID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_UDXENVIRONMENT'
DEBUG:  Index _I_UDXENVIRONMENT_RELID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_UDXENVIRONMENT_RELID_ATTNAM: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_TOAST_5150: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_ATTRDEF'
DEBUG:  Index _I_ATTRDEF_ADRELID: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_TOAST_1215: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  VACUUM: vacuuming '_T_PROC'
DEBUG:  Index _I_PROC_OID: Pages 4; Tuples 1418. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_PROC_PRONAME_NARG_TYPE: Pages 58; Tuples 1418. CPU 0.00s/0.00u sec.
DEBUG:  Index _I_TOAST_1255: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
DEBUG:  Analyzing...
DEBUG:  Analyzing...
DEBUG:  Analyzing...
DEBUG:  Analyzing...

backend> NOTICE:  shutting down
NOTICE:  database system is shut down

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

NOTICE:  database system was shut down at 2014-10-30 18:09:50 PDT
NOTICE:  database system is in production state
DEBUG:  Session id is 16001

POSTGRES backend interactive interface
Revision: 7.1  Date: 2004/11/17 01:54:14

backend> NOTICE:  relation 1247 was reindexed
NOTICE:  relation 1249 was reindexed
NOTICE:  relation 1259 was reindexed
NOTICE:  relation 5107 was reindexed
NOTICE:  relation 5105 was reindexed
NOTICE:  relation 5115 was reindexed
NOTICE:  relation 5113 was reindexed
NOTICE:  relation 5112 was reindexed
NOTICE:  relation 5101 was reindexed
NOTICE:  relation 5102 was reindexed
NOTICE:  relation 5109 was reindexed
NOTICE:  relation 5110 was reindexed
NOTICE:  relation 5100 was reindexed
NOTICE:  relation 5114 was reindexed
NOTICE:  relation 5111 was reindexed
NOTICE:  relation 5104 was reindexed
NOTICE:  relation 5002 was reindexed
NOTICE:  relation 5030 was reindexed
NOTICE:  relation 5132 was reindexed
NOTICE:  relation 5146 was reindexed
NOTICE:  relation 5666 was reindexed
NOTICE:  relation 5727 was reindexed
NOTICE:  relation 5150 was reindexed
NOTICE:  relation 1215 was reindexed
NOTICE:  relation 1255 was reindexed

backend> NOTICE:  shutting down
NOTICE:  database system is shut down

Netezza upgrade from 702 to 7.0.2.14

1.get the below pulgins and place it under the dir

z@server_name tmp]$ cd /nz/kit/share/upgrade/plugins/nz/Plugin/
[nz@server_name Plugin]$ clear
[nz@server_name Plugin]$ ls -ltr

[root@server_name ~]# service drbd status
drbd driver loaded OK; device status:
version: 8.2.6 (api:88/proto:86-88)
GIT-hash: 3e69822d3bb4920a8c1bfdf7d647169eba7d2eb4 build by root@nps22094, 2010-11-18 14:52:01
m:res  cs         st                 ds                 p  mounted       fstype
0:r1   Connected  Primary/Secondary  UpToDate/UpToDate  C  /export/home  ext3
1:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /nz           ext3
[root@server_name ~]# ssh ha2 service drbd status
drbd driver loaded OK; device status:
version: 8.2.6 (api:88/proto:86-88)
GIT-hash: 3e69822d3bb4920a8c1bfdf7d647169eba7d2eb4 build by root@nps22094, 2010-11-18 14:52:01
m:res  cs         st                 ds                 p  mounted  fstype
0:r1   Connected  Secondary/Primary  UpToDate/UpToDate  C
1:r0   Connected  Secondary/Primary  UpToDate/UpToDate  C
[root@server_name ~]# head -n 25 /etc/hosts

2.stop/start the NPS

w-r--r-- 1 nz   nz   13953 Dec 13 09:09 SetDefSchema.pm
-rw-r--r-- 1 root root 17345 Dec 13 09:09 CheckReplication.pm
[nz@server_name Plugin]$
[nz@server_name Plugin]$
[nz@server_name Plugin]$ nzrev
Release 7.0.2 (P-8) [Build 32960]
[nz@server_name Plugin]$ nzstate
System state is 'Stopped'.
[nz@server_name Plugin]$
[nz@server_name Plugin]$ nzstart
Updating '/nz/data.1.0/kit' ...

(startupsvr) Info: NZ-00022: --- program 'startupsvr' (28177) starting on host 'server_name' ... ---
[nz@server_name Plugin]$ clear
[nz@server_name Plugin]$


3.do the pre-upgrade check


[root@server_name upgrade]# wget https://delivery04.dhe.ibm.com/sdfdl/v2/sar/CM/IM/04vtr/0/Xa.2/Xb.jusyLTSp44S03sPozQAzX2GfBoWlywJNOUH2OSyo/Xc.CM/IM/04vtr/0/nz-nps-v7.0.2.14-P2.tar.gz/Xd./Xf.Lpr./Xg.7883631/Xi.habanero/XY.habanero/XZ.E95gizBav5h64nSQSB.0.2.14-P2.tar.gz
--2014-12-13 09:51:44--  https://delivery04.dhe.ibm.com/sdfdl/v2/sar/CM/IM/04vtr/0/Xa.2/Xb.jusyLTSp44S03sPozQAzX2GfBoWlyvt75UH2OSyo/Xc.CM/IM/04vtr/0/nz-nps-v7.0.2.14-P2.tar.gz/Xd./Xf.Lpr./Xg.7883631/Xi.habanero/XY.habanero/XZ.E95gizBav5h64nSQSBmTD1.14-P2.tar.gz
Resolving delivery04.dhe.ibm.com... 170.225.15.105
Connecting to delivery04.dhe.ibm.com|170.225.15.105|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1332315118 (1.2G) [application/octet-stream]
Saving to: `nz-nps-v7.0.2.14-P2.tar.gz'

100%[===================================================================================================>] 1,332,315,118 7.4

2014-12-13 09:54:18 (8.34 MB/s) - `nz-nps-v7.0.2.14-P2.tar.gz' saved [1332315118/1332315118]

[root@server_name upgrade]# clear
[root@server_name upgrade]# ls -ltr
total 1371924
-rwxr-xr-x 1 nz   nz         2353 Apr 18  2012 check_hba.sh
-rw-r--r-- 1 nz   nz      2146836 Apr 23  2012 asu.tgz
-rwxr-xr-x 1 nz   nz          789 Sep 27  2012 check_CBT.sh
-rwxr-xr-x 1 nz   nz       244230 May  7  2013 disk_monitor.nps70
-rw-r--r-- 1 root root    2202835 May  7  2013 check_scripts.tgz
-rwxr-xr-x 1 nz   nz        39107 May 24  2013 upgrade_tools
-rwxr--r-- 1 nz   nz         9023 Sep 17  2013 SetDefSchema.pm
-rw-r--r-- 1 nz   nz         5719 Sep 17  2013 CheckReplication.pm
-rw-r--r-- 1 root root    2214752 Sep 30  2013 upgrade_tools_1.9.tgz
-rw-r--r-- 1 root root        556 Sep 30  2013 nzupgrade.ssh.lock
-rwxr-xr-x 1 root root      18367 Sep 30  2013 check_root.sh
drwxr-xr-x 3 nz   nz         4096 Sep 30  2013 log
-rw-r--r-- 1 root root        603 Sep 30  2013 badViews.sql
drwxr-xr-x 6 root root       4096 Oct  4  2013 FDT
-rw------- 1 nz   nz     64193106 Oct  4  2013 server_name.backup.10042013.tgz
drwxr-xr-x 3 root root       4096 Oct  4  2013 HPF
drwxr-xr-x 2 root root       4096 Oct  4  2013 bios
drwxr-xr-x 6 root root       4096 Oct  4  2013 service_tools
drwxr-xr-x 3 root root       4096 Oct  4  2013 hwtools
drwxr-xr-x 3 nz   nz         4096 Oct  4  2013 swtools
-rw-r--r-- 1 root root 1332315118 Nov  4 15:39 nz-nps-v7.0.2.14-P2.tar.gz
[root@server_name upgrade]# tar -zxvf nz-nps-v7.0.2.14-P2.tar.gz
npsos.7.0.2.14-P2.tar.gz
unpack
[root@server_name upgrade]# ./unpack
-------------------------------------------------------------------------------
IBM Netezza -- Netezza Platform Software 7.0.2.14-P2
(C) Copyright IBM Corp. 2002, 2014  All Rights Reserved.
-------------------------------------------------------------------------------

Validating package checksum ... ok

Where should the Netezza Platform Software be unpacked? [/nz]

 0%          25%         50%         75%          100%
 |||||||||||||||||||||||||||||||||||||||||||||||||||

You can check the system to prepare for an upgrade with:
    /nz/kit.7.0.2.14-P2/sbin/nzupgrade check

You can start the upgrade (which also checks the system) with (as root):
    /nz/kit.7.0.2.14-P2/sbin/nzupgrade upgrade

Unpacking complete.
[root@server_name upgrade]# /nz/kit.7.0.2.14-P2/sbin/nzupgrade check
-------------------------------------------------------------------------------
IBM Netezza -- Netezza Platform Software
(C) Copyright IBM Corp. 2002, 2014  All rights reserved.
-------------------------------------------------------------------------------

Logfile: /nz/var/log/upgrade.20141213.7.0.2.14-P2.check

Checking the system for validity...

Checking HPF health ... ok

---------------------------------------------------------------
IBM Netezza Model:  IBM PureData System for Analytics N1001-005
FDT version:        2.6.1
HPF version:        5.3
---------------------------------------------------------------

System checks succeeded.

You can upgrade your system with:
    /nz/kit.7.0.2.14-P2/sbin/nzupgrade  upgrade


Logfile: /nz/var/log/upgrade.20141213.7.0.2.14-P2.check.gz
[root@server_name upgrade]# clear
[root@server_name upgrade]# screen

SYSTEM(ADMIN)=> \q
[nz@server_name ~]$ 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)=> show history configuration all;
 CONFIG_NAME | CONFIG_DBNAME | CONFIG_DBTYPE | CONFIG_TARGETTYPE | CONFIG_LEVEL | CONFIG_HOSTNAME | CONFIG_USER | CONFIG_PASSWORD | CONFIG_LOADINTERVAL | CONFIG_LOADMINTHRESHOLD | CONFIG_LOADMAXTHRESHOLD | CONFIG_DISKFULLTHRESHOLD | CONFIG_STORAGELIMIT | CONFIG_LOADRETRY | CONFIG_ENABLEHIST | CONFIG_ENABLESYSTEM | CONFIG_NEXT | CONFIG_CURRENT | CONFIG_VERSION | CONFIG_COLLECTFILTER | CONFIG_KEYSTORE_ID | CONFIG_KEY_ID | KEYSTORE_NAME | KEY_ALIAS | CONFIG_NAME_DELIMITED | CONFIG_DBNAME_DELIMITED | CONFIG_USER_DELIMITED
-------------+---------------+---------------+-------------------+--------------+-----------------+-------------+-----------------+---------------------+-------------------------+-------------------------+--------------------------+---------------------+------------------+-------------------+---------------------+-------------+----------------+----------------+----------------------+--------------------+---------------+---------------+-----------+-----------------------+-------------------------+-----------------------
(0 rows)

SYSTEM(ADMIN)=>

query to check the transaction that are currently roll back/executing on Netezza

use
nz_transactions

how to find netezza system state?

use
nzstate

How to find locks on Netezza database?

use nz_show_locks <db name> <tablename>

how to find netezza database version ?

use nz_rev

How to automatically compress the log files and un used files under /nz directoty on NPS

schedule the below script for every week to run it will compress the files and it will not affect the running NPS.
nz_compress_old_files

How to check if the view is valid or not in NPS?

heck each VIEW to make sure it is not obsolete and in need of rebuilding .
nz_check_views  [ database ]  [-replace <flag>]

How to get the information about NPS model,

use the below query
nz_get_model

how to check estimate size of differential backup of database in Netezza?

nz_backup_size_estimate will be used to check the differential backup size

 nz_backup_size_estimate -db <db name>

how to abort user session on Netezza

nz_abort will be used to abort the session on NPS server,if we did not provide  any argument ,then it will abort all session on current user

nz_abort  [-all|<dbname>|<username>]

How to put Netezza database to maintenance mode?

we can put the netezza server /db's to maintenance mode,but admin can able to login to the server /database.it wont disturb the existing connection ,it will not allow the new connection to the database.

nz_maintenance_mode -on/off <db_name>,if db name not mentioned it will make it for entire NPS.

How to find locks on netezza database?

nz_lock <db name> 

how to list various component memory usage in NPS?

[nz@Netezza]$ nz_host_memory

2014-10-24 11:58:10

Memory Types:
   mt     inUse       Used        Max    Perm  Name
    2    193595    20817124    263156       0  anonymous

how to update the contact details on NPS server?

use the below file to edit the contact details on NPS server?

/nz/data.1.0/config/callHome.txt

how to find rollback status in Netezza Database?

you can view the below query to check the roll back status.
select XID,START_TIME,TIME_REMAINING from _V_ROLLBACK_STATUS;

how to check count of all objects in NPS?

nzstats -type dbms

Field Name          Value
------------------- -----
Num Databases       11
Num Groups          11
Num Users           72
Num Tables          7801
Num Views           203
Num SQL Sessions    7
Num Queries         0
Num Queries Running 0
Num Queries Waiting 0
Num Transactions    1

how to check overall health of Netezza system?

nz_health will tell you overall health of system
Hw issues,S?W issue/code issues/failed sidk,disk details etc.

how to check H/w status on NPS?

nzhw -issues
============
No entries found

nzds -issues
============
No entries found

nzds -regenStatus
=================
No entries found

how to check the groom status of database?

progress/status of ongoing GROOM operations can be monitored via the
          system view _V_GROOM_STATUS


 nz_groom  [dbname [tablename <...>]]  [ optional args ]

Purpose:  A wrapper around the 'groom' command to provide additional functionality.

               nz_reclaim  is automatically invoked when running NPS 4.x and 5.x
               nz_groom    is automatically invoked when running NPS 6.x +

Inputs:   The database name is optional.  If not specified then all databases will be
          processed.

          The tablename is optional.  If not specified then all tables in the database
          will be processed.  Or, you can specify one (or many) tablenames to be
          processed.

          -scan        The default option.  This will report on the amount of space
                       that would be reclaimed IF you were to do a

                            "groom table <name> records all;"

                       Gathering this information simply involves a full table scan.

          -pages       Run a page based groom against the entire table.

                       Any empty pages (128KB) will be removed from the scan list (although
                       they will still exist in the table).  Any empty extents (3MB) will
                       be removed the from table.

                       Where "empty" means that the page/extent only contains deleted rows,
                       and those rows are currently reclaimable -- i.e. not of interest.

                            "groom table <name> pages all;"

                       The goal here is to remove as many rows as possible ...  using the
                       fastest method possible.

          -records     Run the actual "groom table <name> records all;" against the
                       table.  This option will, by far, take the longest to run as
                       it basically involves a read+rewrite of the entire table.
                       Therefore, when you choose this option, the script will
                       automatically do the following:

                       a)  First perform the "-pages" operation, since it is faster
                           to throw away pages/extents of data, than individual records.

                       b)  Then perform the "-scan" operation to see if there are
                           any rows that actually need reclaiming.  If not, we'll
                           be able to skip the actual record level grooming as that
                           would not be of any benefit.

                       c)  IF, and only IF, a record level groom is warranted, it
                           will then be run against the table.  (The table must
                           have reclaimable rows, and must meet/exceed any  of the
                           following thresholds that you are allowed to specify).

                       The following switches are optional, and apply to the
                       -scan/-records options.  They can be used in any combination
                       to limit the output report to just those tables that meet/
                       exceed the specified thresholds.  If multiple conditions are
                       specified, they will be OR'ed together.

                       If a table does not meet at least one of the thresholds it
                       will not be included in the report (the "-scan" option) nor
                       will the script do a record level groom against the table
                       (the "-records" option).

                       -rows    <nnn>     # Example:  -rows 1000000
                                          # Only show tables with >= 1M reclaimable rows

                       -percent <nnn>     # Example:  -percent 50
                                          # Only show tables where >= 50% of the rows
                                          # are reclaimable

                       -size    <nnn>     # Example:  -size 1000000000
                                          # Only show tables with >= 1 GB of reclaimable
                                          # space

                       If no thresholds are specified, then:
                            o  All tables will be displayed
                                    (for purposes of the "-scan" option).
                            o  Only tables with >= 1 reclaimable row will be reclaimed
                                    ("-records" option)

          -records <all|ready>

                       There may be times when you want to FORCE a record level groom
                       to be performed (even if there are no logically deleted rows in
                       the table to be reclaimed).  For example:
                            o)  To rewrite a table, after you have enabled compression,
                                so that it will now be compressed
                            o)  To "organize" the rows within a clustered base table.

                       This option allows you to FORCE the record level groom (of your
                       choosing) to be performed.  Note that this script will skip the
                       initial page level groom, and subsequent scan, of each table ...
                       as that just adds additional overhead, and it won't make any
                       difference (since the script is always going to end up doing the
                       record level groom that you requested anyway).

          -mview       If a table has one (or more) active materialized views associated
                       with it, the table cannot be groom'ed until any such MView has been
                       suspended.  So, by default, this script will skip over those tables.

                       Add this switch to the command line if you want the script to
                       o  Automatically suspend any such materialized views
                       o  Perform the GROOM operation
                       o  Refresh the materialized views upon completion

          -version     If you have a versioned table (which is the result of doing an
                       'ALTER TABLE <table> [ADD|DROP] COLUMN ...' operation against
                       it), then the ONLY groom operation that is allowed against the
                       table is a GROOM VERSIONS.  By default, this script will skip
                       over any such table.

                       Add this switch to the command line if you want the script to
                       automatically do a GROOM VERSIONS against such tables.

          -backupset <[backupsetid | NONE]>

          This switch will allow you to override the default backupset.  If used, this
          information is simply passed along to the "groom" command for it to handle.

          The system synchronizes your groom request with the most recent backup set to
          avoid reclaiming rows not yet captured by incremental backups. In other words,
          groom will not remove any unbackedup data that has been deleted or updated.

          In addition, groom will not remove any data that an old/outstanding (yet
          still active) transaction might still have an interest in.

          So ... there may very well be logically deleted rows in a table ... that
          groom is not willing to physically delete at this point in time (for the
          above mentioned reasons).  The scripts 'nz_invisible' and 'nz_transactions'
          may be useful in such a situation.

          For additional information, refer to the "System Administrator's Guide"

          For this script to function, you must have SELECT access to the following
          objects:
               _T_BACKUP_HISTORY
               _VT_HOSTTXMGR

Notes:    If an unexpected error is encountered at any point in time, this script
          will echo the error message and then exit.  It will not continue processing
          other tables if any problem is encountered.

          The progress/status of ongoing GROOM operations can be monitored via the
          system view _V_GROOM_STATUS

Outputs:  For the default "-scan" option, a report such as this will be produced.

          The "Size"s are estimates (based on each table's average row size).

          The "Remaining Rows/Remaining Size" represents what would remain in the
          table once the actual "groom table <name> records all;" is processed.
          This includes visible rows -- as well as deleted rows that cannot yet
          be groom'ed (for whatever reason).

          The "NON-Groomable Rows" column represents the number of deleted rows that
          are NOT elgible for grooming at this point in time, which may be due to
               o  an outstanding transaction that might have an interest in them
               o  the rows have not yet been captured in the most recent backup set

     Name       Remaining Rows Remaining Size Reclaimable Rows Reclaimable Size NON-Groomable Rows
     --------   -------------- -------------- ---------------- ---------------- ------------------

how to List the administrative privileges that a user has been granted to a database.

nz_get_admin  [-user <user>]  <database>

To list out ALL of the objects that a given user has access to

 nz_my_access  <username>  [-nopublic]

nz_get_acl

how to collect statistic on Netezza tables

nz_genstats  [-info]  [-full|-express|-basic]  <database>  [table ...]

Purpose:  Generate statistics on those tables that don't have up-to-date statistics.

          The optimizer uses statistics to guide its decisions on how best to
          execute a query.  The more reliable and up-to-date the statistics are,
          the more accurate the optimizer's decisions are likely to be.

          You can easily "GENERATE STATISTICS;" for an entire database;

          Or you could issue a  "GENERATE STATISTICS ON <table>;"
                          or a  "GENERATE EXPRESS STATISTICS ON <table>;".
          In this case, you must issue the sql statement on a table-by-table basis.

          But what if a given table already has up-to-date statistics?  Then
          regenerating statistics for it would be a waste of your time and the
          system's resources.

          This script first checks the state of the statistics for each table,
          and then only regenerates statistics on those tables that have outdated
          statistics.  (Statistics are either 100% up-to-date+accurate+reliable,
          or they aren't at 100%.  There is no other statistical measure that
          measures statistics).

          Note: If you wish to get/display the current statistic values for any
          given table you can use the script "nz_get"

Inputs:   The database name is required.

          The table name is optional.  If not specified then all tables in the
          database will be checked, and the statistics refreshed as appropriate.

          Or, you can specify one (or many) table names to be checked.

          If you have a file that contains a list of tablenames to be checked,
          it can be used via the following syntax:

                 nz_genstats  DBNAME  `cat /tmp/the_list_of_tables`


          -info     An optional switch

          If included, then statistics won't actually be generated on any table.
          Instead, this script will simply report information about what tables
          have up-to-date statistics and what tables need to have statistics
          generated against them.


          [-full|-express|-basic]     An optional switch

          You can control whether FULL, EXPRESS, or BASIC stats are generated for
          the tables.  (For an explanation of the difference between the three
          choices, see "nz_get -help" ).  If not specified, then this script will
          adhere to whatever type of stats each individual table currently has
          associated with it.

          Full statistics take the longest to generate -- but provide more accurate
          dispersion information (the # of unique values) for each column.

          Express statistics use a faster (but less accurate) mathematical formula
          to determine the dispersion.

          Basic statistics can be generated the fastest -- because they skip the
          dispersion calculation entirely.  (This is new as of NPS Version 4.6).

          Full statistics are recommended for smaller dimension tables ... which
          usually have fewer rows ... where the accuracy of the dispersion value
          tends to have more of an impact on query planning.

          For larger fact tables, express statistics or basic statistics are
          typically used because of the time savings in generating the statistics.

          [-min <nnn>]     Optional  switches     (default is -min 0)
          [-max <nnn>]                            (default is -max 99999999999999999)

          These switches allow you to control which tables are to have a GENSTATS
          run against them ... based on their table rowcount being between the -min
          and -max values specifed.  The default is for all tables to be included.
          Example: Specifying  '-max 1000000'  will result in only tables with
          <= 1M rows having a GENSTATS performed against them (and then only if
          necessary ... if the statistics aren't already up-to-date).

Outputs:  Sample output.  The first run included the "-info" switch.  No changes
          to the database were made.  The script simply lists what it WOULD have
          done if you let it.

          The second run actually invoked GENSTATS on three of the tables.  The
          elapsed runtime for the individual operations is included.

how to check statistic value of table in NPS

use nz_get to get the value of statistic status.

Usage:    nz_get  [ database [ table ]]

Purpose:  Get (and display) the statistics for a user table or a system table.

          Statistics are used by the NPS optimizer in order to plan the best
          way to run each SQL query (for queries that are run against user data,
          and also for queries that are run against the system catalogs).

          Some of these statistics are automatically maintained by the system
          (i.e., 'always-there' statistics).  Other values are dynamically
          calculated+saved when a "GENERATE [EXPRESS] STATISTICS" command is
          issued.

          This script will dump out all of the known statistics for a table.

Inputs:   The database and table names are optional.  If not specified, then
          all tables in all databases will be reported upon.

          In lieu of a table name, you may instead specify a synonym name or
          materialized view name -- in which case the statistics for the
          underlying table will be displayed.

          The table name may also be any one of the "SYSTEM TABLE" or
          "MANAGEMENT TABLE" names.

          o     For a list of these names, see 'nz_get_sysmgmt_table_names'

          o     By default, normal (non-ADMIN) users do not have access
                to any of these SYSTEM/MANAGEMENT tables.  They are only
                allowed to access them indirectly (thru SYSTEM/MANAGEMENT
                views).

          o     Some of these tables are global in nature, so it does not
                matter which database name you specify on the command line.
                The results will always be the same.  e.g. _VT_SPU_ZMAP_INFO

          o     Some of these tables are local in nature (database specific)
                so the database you specify is relevant.  e.g. _T_ATTRIBUTE

          o     If you wanted to look at the statistics for all of the
                SYSTEM/MANAGEMENT tables, you could issue a command such as this:

                for TABLE in `nz_get_sysmgmt_table_names system`; do nz_get system $TABLE; done

Outputs:  The output of this script will include the following information

            Database:     name, objid

               Table:     name, objid, distribution clause, row count(statistic)

          Per-Column:     attnum              the logical column number (from 1-1600)
                          Column Name
                          Statistics Status   the validity/freshness of the statistics
                          Minimum Value
                          Maximum Value
                          # of Unique Values  also known as the dispersion value
                          # of NULLs
                          MaxLen              The MaxLen+AvgLen are only collected for
                          AvgLen              columns of type VARCHAR, NCHAR, NVARCHAR


          Regarding the "Statistic Status" ... the following indicates that statistics
          have been explicitly generated against this table/column (via a GENSTATs
          command) and that they are 100% up-to-date.  The ONLY difference between the
          three is in how the "# of Unique Values" (the dispersion) is generated.
          The disperion value is the most complex statistic to gather, in terms of time +
          memory + cpu usage.

          Full        Similar to doing a "COUNT(DISTINCT columname)".  It generates the
                      most accurate dispersion value, but has the most overhead associated
                      with it.
          Express     It uses math to estimate the dispersion value (a hash is generated
                      for each column value, and then the number of unique hash keys is
                      added up).  Much faster, but less precise.
          Basic       The dispersion calculation is skipped entirely.  All of the other
                      "basic" statistics are still collected.

          So, statistics are either 100% correct and up-to-date, or they're not.  Any change
          to a table ... even if it involves just 1 row being inserted/updated/deleted  ...
          results in the statistics no longer being 100% up-to-date.  In other words, they
          would be outdated.  This doesn't mean that they would be bad or wrong.  Just outdated.

          When rows are nzload'ed/INSERT'ed into a table, NPS automatically compares
          each column value against the MIN/MAX statistic for the column, and updates the
          table's statistics accordingly.  This is also referred to as "always there
          statistics".  This applies to all columns/all datatypes -- EXCEPT for text
          columns (CHAR, NCHAR, VARCHAR, NVARCHAR).  So that means that the MIN/MAX values
          for the column are still OK -- they are still up-to-date (at the same time, the
          table's rowcount statistic is also updated).  But the other statistics values
          (the dispersion and # of nulls) are not up-to-date as they can only be recomputed
          via an explicit GENSTATS.  In this case, the "Statistics Status" will show

          Full    Min/Max OK
          Express Min/Max OK
          Basic   Min/Max OK

          As mentioned, this doesn't apply to text columns.  So all of the statistics that
          are maintained for text columns would be outdated ... and can only be refreshed
          via an explicit GENSTATS.  In this case, the "Statistics Status" will show

          Full    Outdated
          Express Outdated
          Basic   Outdated

          If you never ever bothered to do a GENSTATS on a particular table, then the only
          per-column statistics that would be available are the "always there statistics"
          that NPS automatically collects.  So this means you will have MIN/MAX statistics
          (and only MIN/MAX statistics) for those columns.  Which will be displayed as

          Min/Max Only

          As mentioned, "always there statistics" aren't collected for text columns.  So
          if you've never done an explicit GENSTATS ... and if NPS has never automatically
          collected any statistics ... then there will be no statistics at all available
          to the optimizer.  In this case the "Statistics Status" will show

          Unavailable

          Other situations in which "Unavailable" will be used
          o   if the table is empty
          o   if you've loaded a compressed external file into an empty table

          We don't attempt to collect statistics on very wide text columns, which are
               CHAR/VARCHAR   columns with a defined length >= 24565
               NCHAR/NVARCHAR columns with a defined length >=  6142
          The "Statistics Status" for those columns will be displayed as

          not maintained


          Regarding "always there statistics" ...

          when rows are nzload'ed/INSERT'ed into a table we can easily compare the column
          values against the MIN+MAX statistics, and update them accordingly (if needed)

          when rows are DELETE'd from a table, even though you might be deleting a row
          that matches a particular MIN value or a particular MAX value, there is no
          way to answer the following
               a)  is this the only row in the table with that particular value ?
               b)  and if so, what is the next MIN value or the next MAX value ?
          The only way to determine those answers is via a GENSTATS (which processes
          every row in the table).  Thus, a DELETE operation never shrinks the
          MIN/MAX values.  But we still know that we can use+trust those statistics ...
          that there is no value in the table column that is outside the current
          MIN/MAX range.

          when rows are UPDATE'd, we actually do an INSERT+DELETE.  See above.


          Regarding GENERATE [EXPRESS] STATISTICS ...

          as of 4.6, there is really only one GENERATE STATISTICS command now.  Both
          GENERATE STATISTICS and GENERATE EXPRESS STATISTICS do the same thing.
          GENERATE EXPRESS STATISTICS will someday be retired.

          the only difference between the two has been in how they calculate the
          "# of Unique Values" ... the dispersion value ... for a column.  That
          still occurs, but now NPS decides for itself which method to use.

          If       the table's rowcount is <= 10 * SPU_COUNT
          Then     FULL stats will be collected
                   where we actually try to do a COUNT(DISTINCT on_each_column)
                   # Of Columns Processed Per Scan:  set STATS_COL_GRP_LIMIT = 10;

          If       the table's rowcount is <= JIT_DISP_MIN_ROWS (the default is 500,000,000)
          Then     EXPRESS stats will be collected
                   where we hash the column values to approximate the dispersion value
                   # Of Columns Processed Per Scan:  set SAMPLED_STATS_COL_GRP_LIMIT = 50;

          Else     we skip the dispersion calculation alltogether (all other statistics
                   for the column will still be gathered)
                   # Of Columns Processed Per Scan:  currently unbounded

what the ways to generate DDL of various objects in NPS?

 nz_get_view_definition

Usage:    nz_get_view_definition  <database>  <view>

Purpose:  Display the definition (the SQL) that the view will execute.

nz_ddl*                              

nz_ddl_aggregate*           

nz_ddl_comment*            

nz_ddl_database* 

nz_ddl_diff* 

nz_ddl_ext_table* 

nz_ddl_function*  

nz_ddl_grant_group* 

nz_ddl_grant_user*

nz_ddl_group*  

nz_ddl_history_config*

nz_ddl_library* 

nz_ddl_mview* 

nz_ddl_object*  

nz_ddl_owner*

nz_ddl_procedure*

nz_ddl_security*  

how to check the creator of the user in Netezza?

Usage:    nz_get_user_owner  <user>

Purpose:  List the owner (creator of) the specified user.

Inputs:   The user name is required.

Outputs:  The owner of the user is returned

[nz@nps bin]$ nz_get_user_owner bala
ADMIN

how to check the table fragmentation details of tables on Netezza Database?

 nz_frag -h

Usage:    nz_frag <database> <table/mview>

Purpose:  Dump out extent/page allocations in order to visualize table fragmentation.

          Storage is allocated an extent at a time (3MB).  Within the extent, it is
          then filled up with records a page at a time (128KB).  The pages are filled
          front-to-back.  Once all of the available space in the extent is used up, a
          new extent is allocated.

          Usually, all of the 24 pages within an extent are in-use.  But there are
          exceptions.

          o  The very last extent for a table (on any given dataslice) will probably
             only be partially filled.  So any remaining pages will be unused (empty).
             Unused pages are not scanned.

          o  If you have done a "GROOM TABLE <tablename> PAGES ALL;" then any pages
             that contain 100% deleted/groomable rows will be marked as being empty.
             They will no longer be used/scanned, though they still exist within the
             extent.  If all 24 pages in the extent are empty, the extent will be
             removed from the table and added back to the global storage pool.

          o  Clustered Base Tables (those created with an ORANIZE ON clause) may only
             partially fill any given cluster/extent.

Inputs:   The database and table/mview names are required.

          -dsid <nn>    By default, data slice 1 (the 1st amongst all data slices)
                        will be reported upon.  You can choose to look at the
                        information for a different data slice if you wish.

                        Storage allocation/usage is specific to each dataslice.
                        Rather than showing all info for all dataslices (which
                        could be voluminous), this script will concentrate on
                        just one dataslice, which should provide a good
                        representative sample.

                        However, if you want to see ALL information for ALL
                        dataslices ALL at once, then specify:  -dsid all

Outputs:  A dump of the extent+page storage information for this table/mview (for
          a single dataslice).

          The "Extent ID" that is displayed is the system assigned extent number.
          The values for contiguous extents will vary by 32.

          The "#" column is a simple, one-up number ... to make things easier to
          read.

          The "gap" column is used to indicate whether the extents are contiguous
          on disk.  If the extents are contiguous (if the gap is 0) then a blank
          will be displayed.  Otherwise, this number will represent the number of
          other extents (not belonging to this table) between this extent and the
          prior extent.

          "Used/Unused Pages  (./0)" is used to represent which of the 24 pages
          within each extent are (or are not) in use.  A "." indicates the page
          is in use.  A "0" indicates the page is not being used.

          Example follows:

     $ nz_frag SYSTEM TEST_TABLE

        Database: SYSTEM
     Object Name: TEST_TABLE
     Object Type: TABLE
     Object ID  : 10578974
      Data Slice: 1

      Extent ID | DataSlice | #  | gap | Used/Unused Pages  (./0)
     -----------+-----------+----+-----+--------------------------
       38886368 |         1 |  1 |     | 0.......................
       38886432 |         1 |  2 | 1   | .0......................
       38886560 |         1 |  3 | 3   | ..0.....................

how to find objects owned by specific owners in Netezza database?

Find objects that are owned by users -- other than the 'admin' user
nz_find_object_owners  [user]

how to search object in Netezza box?

we have to use nz_find_object to find object in NPS and it  will check the object in all DB's on NPS server.



nz_find_object table1

  The Object Name Is   | It Is Of Type | Its 'objid' Is | In The Database
-----------------------+---------------+----------------+-----------------
 table1 | TABLE         |       11711150 | DB_name_
 table1 | TABLE         |       10057249 | DB_name__FEB14
 table1 | TABLE         |       11487344 | DB_name__SEP16

How to generate DDL for user in Netezza?

nz_ddl_user <USER_NAME>

\echo

\echo *****  Creating user:  "<USER_NAME>"

CREATE USER <USER_NAME> WITH PASSWORD 'password' ;

\echo

\echo *****  Updating password for user:  "<USER_NAME>"

UPDATE _t_user_options SET passwd = '$1$/+CQzTNi+1I9$QqgdELb4KLkNiFqr8cN4Jg==' WHERE usename = ^<USER_NAME>^;

UPDATE _t_user         SET passwd = '$1$/+CQzTNi+1I9$QqgdELb4KLkNiFqr8cN4Jg==' WHERE usename = ^<USER_NAME>^;

it will set the password as 'password'

how to add a user user to power group in Netezza box?

 Adding users to ACCESS groups

ALTER GROUP POWER_USER ADD USER <username>

How to check how many objects details in Netezza database?

use nz_stats will tell you the details about various versions of tables and object count.

nz_stats

Host Name                     nsdwt03
Model Number                  IBM PureData System for Analytics N1001-005
Software Revision             7.0.2.P8
Today's Date                  2014-10-24
Uptime (In Days)              384.82
Instance                      69
#'s Of Objects
     Databases                10
     Tables                   7,801
     External Tables          251
     Row Secure Tables        0
     Views                    203
     Materialized Views       0
     Sequences                0
     Synonyms                 5
     Users                    72
     Groups                   11
     Aggregates               0
     Functions                26
     Procedures               3,763
     Libraries                0
# Of Versioned Tables         58
     Total # Of Versions      120
Table Size (MB)
     Minimum                  0
     Average                  1,920
     Maximum                  687,092
     Total                    14,985,326
MView Size (MB)
     Minimum                  0
     Average                  0
     Maximum                  0
     Total                    0
Tables Per Database
     Minimum                  6
     Average                  774
     Maximum                  1,898
MViews Per Database
     Minimum                  0
     Average                  0
     Maximum                  0
MViews Per Table (Max)        0
Columns Per Table
     Minimum                  1
     Average                  14
     Maximum                  265
Columns Per MView
     Minimum                  0
     Average                  0
     Maximum                  0
Table Row Size (Bytes)
     Minimum                  28
     Average                  825
     Maximum                  52,064
MView Row Size (Bytes)
     Minimum                  0
     Average                  0
     Maximum                  0
Column Types (for TABLEs)
     BYTEINT                  1,057
     SMALLINT                 2,621
     INTEGER                  21,324
     BIGINT                   1,154
     NUMERIC                  25,577
     FLOAT                    0
     DOUBLE                   1,083
     CHAR                     1,191
     VARCHAR                  44,552
     NCHAR                    0
     NVARCHAR                 44
     DATE                     5,161
     TIME                     74
     TIMESTAMP                5,083
     TIMETZ                   0
     INTERVAL                 5
     BOOLEAN                  2
     ST_GEOMETRY              0
     VARBINARY                0
Column Types (for MVIEWs)
     BYTEINT                  0
     SMALLINT                 0
     INTEGER                  0
     BIGINT                   0
     NUMERIC                  0
     FLOAT                    0
     DOUBLE                   0
     CHAR                     0
     VARCHAR                  0
     NCHAR                    0
     NVARCHAR                 0
     DATE                     0
     TIME                     0
     TIMESTAMP                0
     TIMETZ                   0
     INTERVAL                 0
     BOOLEAN                  0
     ST_GEOMETRY              0
     VARBINARY                0
Column Types (for VIEWs)

How to check total and used space on Netezza applicance

you may use the below query to find the NPS DS used and free space details.

nz_storage_stats

nz@Netezza1 bin]$ nz_storage_stats

# Of DataSlices               92
Extents Per Dataslice         121,515
Storage Per DataSlice  (GB)   356.000
Storage Used           (GB)
     Minimum                  128.259
     Average                  136.918
     Maximum                  150.911
Storage Used            (%)
     Minimum                  36.028
     Average                  38.460
     Maximum                  42.391
Total Storage
     Available         (TB)   31.984
     Used              (TB)   12.301
     Used               (%)   38.460
     Remaining         (TB)   19.683
     Remaining          (%)   61.540

voting disk and OCR IN RAC

OCR: It created at the time of Grid Installation. It’s store information to manage Oracle cluster-ware and it’s component such as RAC database, listener, VIP,Scan IP & Services.
Minimum 1 and maximum 5 copy of OCR is possible.
Voting Disk: It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster.If any node is not passing heat-beat across other note or voting disk, then that node will be evicted by Voting disk.
Minimum 1 and maximum 15 copy of voting disk is possible.

New Facts:

• We can store OCR And Voting disk on ASM or certified cluster file system.
• We can dynamically add or replace voting disk & OCR.
• Backup of Voting disk using “dd” command not supported.
• Voting disk and OCR can be keep in same disk-group or different disk-group
• Voting disk and OCR automatic backup kept together in a single file.
• Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week
• You must have root or sudo privilege account to manage it.

To find current location of Voting disk:

-bash-3.2$ crsctl query css votedisk

##  STATE    File Universal Id                File Name Disk group

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

 1. ONLINE   9095dc8a0e4d4f40bfd3a8b6ed02d7c1 (/dev/dbq_rawvote) [OCRVD]

Located 1 voting disk(s).

to check OCR

bash-3.2$ ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          3

         Total space (kbytes)     :     262120

         Used space (kbytes)      :       3308

         Available space (kbytes) :     258812

         ID                       :  376930295

         Device/File Name         :     +OCRVD

                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

to check local OLR

ddcfusdbq01 /grid/app/11.2.0/grid/bin #./ocrcheck -local

Status of Oracle Local Registry is as follows :

         Version                  :          3

         Total space (kbytes)     :     262120

         Used space (kbytes)      :       2704

         Available space (kbytes) :     259416

         ID                       :  367014315

         Device/File Name         : /grid/app/11.2.0/grid/cdata/ddcfusdbq01.olr

                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

how to increase the size of db_recovery_file_dest_size in oracle RAc

1.Please check enough room is there for ASM disk
2.ALTER SYSTEM SET db_recovery_file_dest_size='40G' SCOPE=BOTH SID='*';
it will reflect to all nodes.

How to check the Netezza HA cluster setup status?

[root@node1 ~]# service heartbeat status
heartbeat OK [pid 20408 et al] is running on node1 [node1]...
[root@node1 ~]# service drbd status
drbd driver loaded OK; device status:
version: 8.2.6 (api:88/proto:86-88)
GIT-hash: 3e69822d3bb4920a8c1bfdf7d647169eba7d2eb4 build by root@nps22094, 2010-11-18 14:52:01
m:res  cs         st                 ds                 p  mounted       fstype
0:r1   Connected  Primary/Secondary  UpToDate/UpToDate  C  /export/home  ext3
1:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /nz           ext3
[root@node1 ~]#

if you get the above output from server ,then you may confirm the database service running on node1

how to do restore in sql server 2008 for whole database

1.take the full backup from ms studio
2.transfer the backup piece from source to target (i.e cmd prompt and c: or shared folder  to target server.
3.
you may generate the script from msq sql studio  alter this wherever you want to place the mdf,ldf files alter it based on the available  disk space.

RESTORE DATABASE [DB__name] FROM  DISK = N'E:\backup\DB__name_july28\DB__name_full_bakup.bak' WITH  FILE = 1,  MOVE N'DB__name_Data' TO N'E:\SQLData\DB__name.MDF',  MOVE N'DB__name_Log' TO N'F:\SQLLog\DB__name_1.LDF',  NOUNLOAD,  STATS = 5

4.run dbcc to check the integrity of the  database.

5.check all the user permission everything good.

6.fix the orphan user account using the below steps
EXEC sp_change_users_login 'Report'

step to create Data guard from RAC to stand alone database

This summary is not available. Please click here to view the post.