Virtual Statistics Tables

Virtual statistics tables allow you to run SQL queries against a snapshot of server usage data. The kinds of information you can extract depends on the query you construct and run against these special tables. For example, you can create such information as:

• The number of users currently logged in.
• A list of logged in users; most recent first (or last).
• A list of users who have more than one query open and in repeatable read isolation level.
• Number of users doing joins in a particular location.

The Virtual Statistics Tables contain dynamic information about the state of the server. The tables are populated when they are queried to give you current information. Apart from their dynamic nature, they can be accessed just like normal system tables, using joins, conditional clauses, sorts, aggregate functions, etc. Some of the information in these tables duplicates the data displayed on the various server screens, while some display other kinds of information.

All of the Virtual Statistics Tables have the AuthID XDBSTAT and are created (when queried) in the DSNDB07 database. They have no indexes. The format and content of the Virtual Statistics Tables are defined in detail in the SQL Reference. Examples of queries that can be run against these tables to extract information are presented below and on the next page.

• Example: Number of Users Currently Logged In

  SELECT COUNT(DISTINCT nodename) 
      FROM xdbstat.userlist;

• Example: List of All Users, Most Recent First

  SELECT * FROM xdbstat.userlist

  ORDER BY attachts desc;

• Example: List of All Users Having Greater than One Query Open in Repeatable Read (RR)

  SELECT username||"@"||nodename 
      FROM xdbstat.userlist a, xdbstat.userresources b 
      WHERE a.username = b.username 
        AND a.nodename = b.nodename 
        AND b.numqds >1 
        AND a.ilevel = "RR";

• Example: Number of Users Doing Joins in System Location

  SELECT COUNT(*) 
    FROM xdbstat.queryinfo 
    WHERE locname = "SYSTEM" 
      AND numtables > 1;