Skip to content

How to install Netezza Queryhistory version 3?

Just follow the below steps to install Queryhistory version 3. NOTE: Here we will create Query history database names QHIST3.

1) Create user’s specific for Queryhistory version 3.

nzsql -c “create user histuser with password ‘P@ssword’ auth local;”
nzsql -c “create user histdbowner with password ‘P@ssw0rd’ auth local;”
2) Grant privileges to newly created users.

nzsql -c “grant create database to histdbowner;”
nzsql -c “grant list on user to histdbowner;”
3) Create the history database ( online help is available “nzhistcreatedb -h” )

nzhistcreatedb -d QHIST3 -t query -o histdbowner -pw P@ssw0rd -u histuser -v 3
4) Create the history configuration:

For each history database, create at least one history configuration that specifies the parameter HISTTYPE NONE. Setting this configuration to be the active configuration disables the collection of history data.

nzsql -c “CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE NONE;”

nzsql -d SYSTEM
CREATE HISTORY CONFIGURATION HISTORY_ENABLE — Name of the history configuration
HISTTYPE QUERY — Type of the history configuration
NPS LOCALHOST
DATABASE QHIST3 — Database that will be used by the history configuration
USER histuser — User that will load history data
PASSWORD ‘P@ssword’ — Password of the user, if this password change the history configuration must be updated in order to have the latest password to be able to load the data on the History Database.
COLLECT QUERY , PLAN , TABLE , COLUMN , SERVICE , STATE — Information that will be collected
LOADINTERVAL 5 — How long it will wait to load the data on the database
LOADMINTHRESHOLD 4 — The minimun amount in MB to collect before transfer staged batch files to the loading area.
LOADMAXTHRESHOLD 20 — In MB size
STORAGELIMIT 100 — The maximum size of the history data staging area in MB.
LOADRETRY 1 — The number of times that the load operation is retried.
ENABLEHIST TRUE
ENABLESYSTEM TRUE
VERSION 3 — Available on Release 7.1 and on-wards
;
5) To start the collection of history data

nzsql -c “set history configuration HISTORY_ENABLE;”

6) To activate the new history configuration you need to stop and restart the system, that is, by issuing the nzstop and nzstart.

How to restart viewpoint DCS (data collector services) in Cluster environment?

To restart viewpoint DCS (data collector services) in cluster environment, you have to follow below steps:
1) Check which viewpoint server is active and standby.

Viewpoint1:~#cat /etc/opt/teradata/viewpoint/distributed.cluster.properties

#Tue, 19 Jan 2016 11:46:11 -0500
#
# Distributed viewpoint cluster properties.
#
active.database.host=viewpoint1 <<<active
standby.database.host=viewpoint2 <<standby
2) Run below command on standby viewpoint server to stop DCS.

Viewpoint2:~#/etc/init.d/dcs stop
Shutting down DCS done
3) Now run below commands to stop and then start DCS in primary server.

Viewpoint1:~#/etc/init.d/dcs stop
Shutting down DCS done

Viewpoint1:~#/etc/init.d/dcs start
Starting DCS done
4) Now check the status of DCS to make sure its running in primary server.

Viewpoint1:~#/etc/init.d/dcs status
Checking for DCS: running
5) Run below command to start DCS in secondary server and verify that its running now.

Viewpoint2:~#/etc/init.d/dcs start
Starting DCS done

Viewpoint2:~#/etc/init.d/dcs status
Checking for DCS: running