Check Netezza Active Sessions/Queries

There are different ways to find active sessions in Netezza, portal or nzsession command line.  But if you like SQL script style command, below is the script:


select  q.qs_sessionid,
        q.qs_planid,
        q.qs_clientid,
        s.dbname,
        s.username,
        s.TYPE SESSION_TYPE,
        s.STATUS SESSION_STATUS,
        s.PRIORITY SESSION_PRIORITY,
        q.qs_cliipaddr,
        q.QS_STATE,
        substr(q.qs_sql,1,200),
        case
            when q.qs_state = 1 then 'pending'
            when q.qs_state = 2 then 'queued'
            when q.qs_state = 3 then 'running'
            when q.qs_state = 4 then 'aborted'
            when q.qs_state = 5 then 'done'
            else 'unknown'
        end QS_STATE_DESCRIPTION,
        q.qs_tsubmit+t.tzoffset qs_tsubmit,
        q.qs_tstart+t.tzoffset tzoffset,
        case when q.qs_tstart = t.EPOCH then 0 else abstime 'now' - q.qs_tstart end running_duration,
        q.qs_pritxt,
        q.qs_estcost,
        q.qs_estdisk,
        q.qs_estmem,
        q.qs_snippets,
        q.qs_cursnipt,
        q.qs_resrows,
        q.qs_resbytes,
        to_timestamp('08JUL14224216','DDMONYYHH24MISS')
from    _v_qrystat q,
        _v_session s,
                _vt_pg_time_offset t
where   q.qs_sessionid = s.id

Sample Output:

 QS_SESSIONID | QS_PLANID | QS_CLIENTID |       DBNAME       |     USERNAME     | SESSION_TYPE | SESSION_STATUS | SESSION_PRIORITY | QS_CLIIPADDR | QS_STATE |                                                                                                  SUBSTR                                                                                                  | QS_STATE_DESCRIPTION |     QS_TSUBMIT      |      TZOFFSET       | RUNNING_DURATION | QS_PRITXT | QS_ESTCOST | QS_ESTDISK | QS_ESTMEM | QS_SNIPPETS | QS_CURSNIPT | QS_RESROWS | QS_RESBYTES
--------------+-----------+-------------+--------------------+------------------+--------------+----------------+------------------+--------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------------------+---------------------+------------------+-----------+------------+------------+-----------+-------------+-------------+------------+-------------
        59376 |     17368 |         112 | CELENR_HLD_PROD_DB | CELEBI_USR_PETL  | sql          | active         |                3 | 10.10.62.15  |        3 | SELECT COUNT(*) FROM ( SELECT CBP_NODE,(((cast(END_SEQ_NUMBER as bigint)-cast(start_seq_number as bigint))+1)-COUNT(*)) AS DIFF FROM HLD_NGIN_ADT_RPT_REC WHERE SEQ_CHECK IS NULL GROUP BY 1,END_SEQ_NUM | running              | 2014-07-09 11:53:37 | 2014-07-09 11:53:37 |                0 | normal    |         26 |          0 |         0 |           2 |           1 |          0 |           0
        56627 |     17288 |         211 | CELENR_HLD_PROD_DB | CELEBI_TEMP_ETL  | sql          | active         |                3 | 10.10.62.15  |        3 | insert into CELENR_HLD_PROD_DB..temp0_final_xfn_MSC select tmp.row_id, ph_calling.electronic_device_type_id calling_electronic_device_type_id, ph_called.electronic_device_type_id called_electronic_dev | running              | 2014-07-09 11:48:20 | 2014-07-09 11:48:21 |              316 | normal    |     150160 |      57811 |     91377 |          19 |          12 |          0 |           0
        58355 |     16828 |         254 | CELEBI_ISD_DB      | CELEBI_USR_08732 | sql-odbc     | active         |                3 | 10.10.47.229 |        3 | SELECT * FROM CELEBI_ZOOM_DB.EBINZADMIN.ZA_POSTPAID_VOICE_2G_3G A LEFT JOIN (SELECT ACCOUNT_ARR_ID,MDN, BILLED_TOTAL_INVOICED_AMOUNT from CELEBI_SOR_PROD_DB..INVOICE X inner JOIN (SELECT SUBSCRIBER_AR | running              | 2014-07-09 11:33:16 | 2014-07-09 11:33:16 |             1221 | normal    |      10621 |      31101 |     25983 |           6 |           6 |   49484266 | 10583460440
        59070 |     17361 |         374 | CELENR_STG_PROD_DB | CELEBI_USR_PETL  | sql          | active         |                3 | 10.10.62.15  |        3 | UPDATE STG_NGIN_CDR_SMS stg SET DESTINATION_SERVICE_PROVIDER_ID=USG.SERVICE_PROVIDER_ID FROM tmp_USAGE_POINT_SP_sms usg WHERE USG.POINT=SUBSTRING(stg.calledpartynumber, 1, 7) AND STG.Serviceflow =1 AN | running              | 2014-07-09 11:53:29 | 2014-07-09 11:53:29 |                8 | normal    |         63 |        202 |         5 |