The nzsession command

Use the nzsession command to view and manage sessions.

Syntax

The nzsession command uses the following syntax:

nzsession subcmd [subcmd options]

Inputs

The nzsession command takes the following inputs:
Table 1. The nzsession input options
Input Description
nzsession abort options Abort a running user session.
nzsession abortTxn options Abort a user transaction.
nzsession listSessionTypes Lists the session types, which include the following types:
  • sql = database SQL session
  • sql-odbc = database SQL session through ODBC
  • sql-jdbc = database SQL session through JDBC
  • load = data load session (nzload)
  • client = client UI or CLI session
  • bnr = Backup and restore session
  • reclaim = database reclaim session (nzreclaim)
  • loadsvr = data load session (deprecated loader)
nzsession priority options Changes priority of the current and all subsequent jobs of this session.
nzsession show options Displays the list of current user sessions.

Options

The nzsession command takes the following options:
Table 2. The nzsession options
Command Option Description
All nzsession commands -u user Specifies the database user name [NZ_USER].
-pw password Specifies the user password [NZ_PASSWORD].
-host name Specifies host name or IP address [NZ_HOST]. Except listSessionTypes.
-caCertFile path Specifies the path name of the root CA certificate file on the client system. This argument is used by IBM® Netezza® clients who use peer authentication to verify the Netezza host system. The default value is NULL, which skips the peer authentication process.
-securityLevel level Specifies the security level that you want to use for the session. This option does not apply when you are logged in to the Netezza system and running the command. The argument has four values:
preferredUnSecured
This value is the default value. Specify this option when you would prefer an unsecured connection, but you accept a secured connection if the Netezza system requires one.
preferredSecured
Specify this option when you want a secured connection to the Netezza system, but you accept an unsecured connection if the Netezza system is configured to use only unsecured connections.
onlyUnSecured
Specify this option when you want an unsecured connection to the Netezza system. If the Netezza system requires a secured connection, the connection is rejected.
onlySecured
Specify this option when you want a secured connection to the Netezza system. If the Netezza system accepts only unsecured connections, or if you are attempting to connect to a Netezza system that is running a release before 4.5, the connection is rejected.
-timeout secs Specifies the time to wait in seconds for the command to complete. The default is 300.
nzsession abort, and abortTxn -id num Specifies the session ID.
-force Does not prompt for confirmation.
nzsession priority -id num Specifies the session ID.
-high Changes the session priority to high.
-normal Changes the session priority to normal.
-low Changes the session priority to low.
-critical Changes the session priority to critical.
nzsession show -activeTxn Displays the active transactions for the system.
-maxColW chars Specifies the maximum number of characters to print in a column. The default is 24.

Description

The nzsession command does the following:
Privileges required
The admin user has full privileges to display all session information, to abort sessions and transactions, and to change the priority of a session. Other database user accounts require no special privileges to use the nzsession show command to see all the sessions that are currently active on the system. However, non-admin users see asterisks instead of the user name, client process Id (PID), database, and SQL command unless they have List privilege on User (to see details about the user, client PID, and SQL command) and List privilege on Database (to see the database name). Users must have the Manage System privilege to change the priority of sessions, and Abort privilege to abort sessions, transactions, or both.
Common tasks
Use the nzsession command to manage sessions. You cannot use a Release 5.0 nzsession client command to manage sessions on a Netezza system that is running a release before 5.0.

How the command handles abort processing

When you run the nzsession abort command, the client manager uses the session ID to abort the process.

For example, to abort an nzload session ID 2001, the system does the following:
  1. The system sends the nzsession abort command to the client manager.
  2. The client manager identifies which nzload session to abort.
  3. The loadmgr sends the abort signal to the loadsvr and starts the timer.
  4. The loadmgr waits the specified timeout value for the loadsvr to abort the session. The command uses either the default value, or the timeout you specify on the command line.

You can also abort active or idle nzsql sessions.

The nzsession show output

The following table describes the nzsession show output information. The admin user can see all the data for sessions; other users can see all the sessions, but data for user, database, client PID, and SQL command are hidden unless the user has privileges to see that data.
Table 3. Session information
Column Description
ID The ID of the session.
Type The type of session, which can be one of the following:
Client
Client or UI session
SQL
Database SQL session
Bnr
Back up or restore session
Reclaim
Disk reclamation session.
User The name of the session owner.
Start Time The time the session was started.
PID The process identification number of the command you are running.
Database The name of the database.
Schema The name of the schema.
State The state of the session, which can be one of the following:
Idle
The session is connected but it is idle and waiting for a SQL command to be entered.
Active
The session is running a command (usually applies to a SQL session that is running a query).
Connect
The session is connected, but no commands have been issued.
Tx-Idle
The session is inside an open transaction block (BEGIN command) but it is idle and waiting for a SQL command to be entered within the transaction.
Priority Name The priority of the session, which can be one of the following:
Critical
The highest priority for user jobs.
High
The session jobs are running on the high priority job queue.
Normal
The session jobs are running on the large or small job queue.
Low
The lowest priority for user jobs.
Client IP The IP address of the client system.
Client PID The process identification number of the client system.
Command The last command executed.

Usage

The following provides sample usage:
  • To show all sessions, enter:
    nzsession show -u bob -pw password 
    ID    Type User  Start Time               PID  Database  Schema State Priority
    Client IP Client Command
                                                                          Name
              PID
    ----- ---- ----- ----------------------- ----- --------- ------ ------ --------
    --------- ------ ------------------------
    16049 sql  ***** 24-Feb-13, 16:49:18 EST 14840 *****     ADMIN active normal
    ***** *****  *****
    16054 sql  bob   24-Feb-13, 16:49:31 EST 15093 SYSTEM    ADMIN active normal
    127.0.0.1 15092 SELECT session_id, clien
    
    This sample output displays for a user (bob) who does not have permission to see the details of the sessions on the system. Only the details for the sessions for the user bob display. For a user who has List permission on user and database objects, the output shows all the details:
    nzsession show -u sysadm -pw password
    ID    Type User   Start Time               PID  Database  Schema State Priority
    Client IP Client Command
                                                                          Name
              PID
    ----- ---- -----  ----------------------- ----- --------- ------ ------ --------
    --------- ------ ------------------------
    16049 sql  DBUSR  24-Feb-13, 16:49:18 EST 14840 TPCH1     ADMIN active normal
    127.0.0.1 14839 select * from lineitem
    16054 sql  bob    24-Feb-13, 16:49:31 EST 15093 SYSTEM    ADMIN active normal
    127.0.0.1 15092 SELECT session_id, clien
    
  • To abort a session, enter:
    nzsession abort -u user -pw password -host nzhost -id 1344
  • To abort a transaction, enter
    nzsession abortTxn -u user -pw password -host nzhost -id 437
  • To list the types of sessions, enter:
    nzsession listSessionTypes
  • To change the session priority, enter:
    nzsession priority -u user -pw password -host nzhost -id 437 -high
  • To show all the active transactions, enter:
    nzsession show -activeTxn 

    You can use the -activeTxn option to display the active sessions that will be impacted by a state change (such as pausing -now) before you initiate the state change.