Using dsmtop to replace nz_responders

In your Netezza environment, you might have used the nz_responders script to show interactions and responses for running queries across each data slice. This functionality is now covered by dsmtop.

About this task

The dsmtop tool fulfills customer requirements for a lightweight, low overhead monitoring tool that will work in a text only environment. There is no associated web server and hence absolutely no installation or administration is required for dsmtop itself. Monitoring is accomplished using the lightweight mon_get_xxx table functions.  Views can be set to fast refresh rates to provide a real-time view of activity in the monitored database.

You can use dsmtop to see key performance indicators.  A few of the more significant views provided in dsmtop include:
  • Sessions –see at a glance which connections are active, blocked, or idle. You can drill down on a connection to see details of the current state of execution of SQL that is in flight for this connection.
  • Executed SQL –see a list of recently executed statements. Drill down is provided to see the full SQL text or run explain on a statement.
  • Top Consumers –find which connections or activities are consuming the most CPU, IO or other resource.
  • Time spent –shows a breakdown of where the monitored database is spending time, broken down to the full granularity.

dsmtop is more powerful compared with nz_responders and it provides almost all functions that nz_responders does except those that are specific to Netezza.

Procedure

  1. Run the following command: 
    dsmtop -n <host_name> -r <port> -d <database_name> -u <user> -p <password>

    A text interface is opened with menu items on top.

  2. Select View from the menu, then, from the drop-down list, select Statements.

    You can now view In-flight statements or Executed SQL.

    You can also filter the output by schema in two ways:
    • Run the dsmtop command specifying the selected schemas in the following parameter: -s <schema1,schema2,...> Use comma-separated list to specify multiple schemas.
    • When in the In-flight statements or Executed SQL view, hit the S key to access a dialog where you can list schemas to filter out results. Use comma-separated list to specify multiple schemas. Note that, in this case, the statements list will only change with the next view refresh, which might take a few seconds.