SQL Tables › SQL Status Tables

SQL Status Tables

Following is a list of the SQL status tables.

The SQL virtual tables are now in the Dynamic System Tables database. To save the rows in these tables, you can use the searched form of the INSERT statement to insert them into a table of your own. For example, to obtain a profile of SQL activity during the day, you could define a table with the same columns as the SQL_STATUS table and execute the following statement every 30 minutes:

INSERT INTO MY_AUTHID.SQL_STATUS
SELECT *
FROM SYSADM.SQL_STATUS;

You can access the SQL status tables as you would any other SQL-accessible table. For example, you can use DDOL and DQRY for on demand queries and develop your own customized monitoring tools for ongoing analysis.

Here are examples of other possible uses of the SQL status tables.

• If you have a job running that builds a large temporary table, you can find out how much room is left in the temporary table (TTM) area by doing a SELECT such as the following:

  SELECT TTM_BLKS_ALLOC - TTM_BLKS_IN_USE
  FROM SYSADM.SQL_STATUS;
  

• If an SQL mode CA Dataquery query is running longer than you think it should and you believe production transactions are running longer, you can do a SELECT such as the following to find out what the query is doing and whether it is about to finish or should be canceled:

  SELECT *
  FROM SYSADM.SQL_STATUS_CURRENT
  WHERE ACCESSOR_ID = :USER_SIGNON;
  

• If you think an SQL precompile is hung, you can find out if someone is currently executing a plan by doing a SELECT such as the following:

  SELECT *
  FROM SYSADM.SQL_STATUS_PLAN
  WHERE PLAN_AUTHID = :AUTHID AND PLAN_NAME = :PLAN;
  

• If you have tried to use the CA Datacom/DB Utility (DBUTLTY) to EXTRACT a table and received a return code 46, you can find out if there is a SQL-generated URT open for the table by doing a SELECT such as the following:

  SELECT *
  FROM SYSADM.SQL_STATUS_URT
  WHERE DBID = :BASE AND DB_TBL_NAME = :TBL;