Killing  Sessions on Netezza

To kill all active sessions on Netezza, to restart a test.

You can use nzsession to list to session id’s, and nzsession abort to kill them manually, but I wanted a means to do it in one.

To Kill All session 

for ses in `nzsession | grep ADMIN | awk ‘{print $1}’`
do
nzsession abort -id ${ses} -force
done
========================================================================


To Kill any single session 

Nzsession -show

then

nzsession abort -id ""session"


========================================================================

Important Note:-

Don't not kill any session using Kill-9 "pid" ever, It may restart your your NPS.

Reindexing Netezza Databases

Reindexing Netezza Databases

If you find the following in the startupsvr.log when starting Netezza, you can fix this with the following commands:

SAST: Checking index sizes on 16 databases...
SAST: Database USERDB needs to be reindexed...
SAST: Database CDD_TRG needs to be reindexed...
SAST: Database TEST needs to be reindexed...

IBM’s documentation states the following for reindexing databases.

REINDEX DATABASE


Use the REINDEX DATABASE command to vacuum and reindex a user database. You must connect to the SYSTEM database as the admin user to run this command.

When you create, use, and delete SQL objects in a user database, the IBM® Netezza® database software creates entries in the system catalog for those objects. The system catalog tables contain indexes to accelerate the catalog access. If the database users create, use, and delete objects frequently, which often happens by using third-party SQL reporting applications, the index entries for the deleted objects can grow very large and impact query performance.

You can use the REINDEX DATABASE command to remove (or vacuum) entries for deleted objects within the catalog tables, and then recreate the catalog table indexes within the user database. You must have permission to connect to the SYSTEM database as the admin user to run the command. The user database cannot have any active sessions or connections. The NPS® system can remain online while the REINDEX DATABASE command runs. The reindexing process is usually very fast. The command typically requires a few seconds to update a small user database, and very large databases could require a few minutes to complete.

Means that the database’s will require to be reindexed. This is relatively simple to complete.

This can be repeated on all database’s where there are no users connected.

[nz@clvs1001 startupsvr]$ 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)=> \time
Query time printout on
SYSTEM.ADMIN(ADMIN)=> reindex database userdb;
REINDEX DATABASE

If you have users connected you will get the following error. This database was being used for CDD data replication and therefore could not be reindexed.

SYSTEM.ADMIN(ADMIN)=> reindex database CDD_TRG;
ERROR: unable to acquire lock on database CDD_TRG

The test database here was not in use.

SYSTEM.ADMIN(ADMIN)=> reindex database TEST;
REINDEX DATABASE
Elapsed time: 0m0.800s