Query Examples on IMA Tables

This section provides example queries based on the tables registered in the REGISTER TABLE Examples.

Note: The VNODE "GRFR6" is an example name for a VNODE.

Note: It is assumed that each example has been run in a new session.

Example: What Are User Sessions Doing?

The following SQL shows what user sessions are currently doing in all servers in the local VNODE and the remote VNODE "GRFR6":

update ima_mib_objects

set    value        = DBMSINFO('IMA_VNODE')

where  classid      = 'exp.gwf.gwm.session.control.add_vnode'

and    instance     = '0'

and    server       = DBMSINFO('IMA_SERVER');

update ima_mib_objects

set    value        = 'GRFR6'

where  classid      = 'exp.gwf.gwm.session.control.add_vnode'

and    instance     = '0'

and    server       = DBMSINFO('IMA_SERVER');

select

       *

from

       ima_server_sessions

where db_owner != '';

Example: Who Is Waiting for a Lock?

The following query shows which sessions are waiting for locks in the VNODE 'GRFR6':

update ima_mib_objects

set    value        = 'GRFR6'

where  classid      = 'exp.gwf.gwm.session.control.add_vnode'

and    instance     = '0'

and    server       = DBMSINFO('IMA_SERVER');

select distinct

       resource_id_id,

       lock_id_id,

       lock_state,

       ima_locklists.locklist_id_id,

       locklist_server_pid,

       locklist_session_id,

       effective_user,

       db_name,

       session_terminal,

       session_query

from

       ima_locks,

       ima_locklists,

       ima_server_sessions

where lock_state != 'GRANTED'

and ima_locks.locklist_id_id = ima_locklists.locklist_id_id

and ima_locklists.locklist_server_pid = ima_server_sessions.server_pid

and ima_locklists.locklist_session_id = ima_server_sessions.session_id;

Example: Who Is Holding Locks?

The following query shows which sessions are holding locks on the local VNODE:

update ima_mib_objects

set    value        = DBMSINFO('IMA_VNODE')

where  classid      = 'exp.gwf.gwm.session.control.add_vnode'

and    instance     = '0'

and    server       = DBMSINFO('IMA_SERVER');

select distinct

       lock_id_id,

       lock_state,

       ima_locklists.locklist_id_id,

       locklist_server_pid,

       locklist_session_id,

       effective_user,

       db_name,

       session_terminal,

       session_query

from

       ima_locks,

       ima_locklists,

       ima_resources,

       ima_server_sessions

where lock_state != 'WAITING'

and ima_locks.locklist_id_id = ima_locklists.locklist_id_id

and ima_locklists.locklist_server_pid = ima_server_sessions.server_pid

and ima_locklists.locklist_session_id = ima_server_sessions.session_id;

Example: Who Is Holding a Lock that Other Sessions Need?

The following query shows which sessions on the local VNODE are holding locks where other sessions are waiting:

update ima_mib_objects

set    value        = DBMSINFO('IMA_VNODE')

where  classid      = 'exp.gwf.gwm.session.control.add_vnode'

and    instance     = '0'

and    server       = DBMSINFO('IMA_SERVER');

select distinct

       resource_id_id,

       lock_id_id,

       lock_state,

       ima_locklists.locklist_id_id,

       locklist_server_pid,

       locklist_session_id,

       effective_user,

       db_name,

       session_terminal

       from

       ima_locks,

       ima_locklists,

       ima_server_sessions

where resource_id_id in

       (

       select distinct

              ima_locks.resource_id_id

       from

              ima_locks

       where lock_state != 'GRANTED'

       )

and lock_state != 'WAITING'

and ima_locks.locklist_id_id = ima_locklists.locklist_id_id

and ima_locklists.locklist_server_pid = ima_server_sessions.server_pid

and ima_locklists.locklist_session_id = ima_server_sessions.session_id;

Example: Shutting Down a DBMS Server

This query marks the MIB server for shutdown when all sessions have exited:

Windows:

update ima_mib_objects

set value     = '1'

where classid = 'exp.clf.nt.cs.mon.shutserver'

and instance  = '0'

and server    = DBMSINFO('IMA_SERVER');

UNIX:

update ima_mib_objects

set value     = '1'

where classid = 'exp.clf.unix.cs.mon.shutserver'

and instance  = '0'

and server    = DBMSINFO('IMA_SERVER');