Procedures for generating SQL

There are two ways to generate SQL to run against a Visualizer database:

Manually entering and running SQL commands
Using trace copy generated by Visualizer as the basis for an SQL query

To build an SQL query manually, use a text editor and the following SQL examples as models to enter commands and run the query.

To build an SQL query using trace copy, use visscr32debug.sql to turn on trace copy prior to running Visualizer. Using a text editor, modify the SQL generated by trace copy to suit your data. You should not leave trace copy on at all times because the log file grows, causing potential storage and response problems.

The following sample SQL was generated for a Distributed Systems database. The syntax may be different for your database.

Sample SQL

The following example extracts the CPU utilization for a given node on a given period:

SELECT A.NODE_ALIAS, B.CPU_UTIL, C.INT_START_DATE,
  C.INT_START_TIME
FROM CAXNODE A,
  CAXNODED B,
  CAXINTVL C
WHERE A.INDEXX = B.SYSTEMS AND B.INTVL = C.INDEXX AND
  C.INTTYPE = 'M' AND ((C.INT_START_DATE='00 /10 /01')
  AND (C.INT_START_TIME BETWEEN '0800' AND '1700')
  AND (A.NODE_ALIAS = 'nodename')) AND
  B.CPU_UTIL >= 0
ORDER BY C.INT_START_DATE, C.INT_START_TIME

Supplemental SQL examples

By connecting directly to the database, you can:

Produce reports that are not currently available or that require larger time intervals than Visualizer can accommodate by running these queries from Excel
Graph disparate measures from different metric groups together
Easily extract system configuration information

Sample I/O by controller query

SELECT A.CONNECT_TO_NODE,
  D.INT_START_DATE, D.INT_START_TIME, A.INTERFACE_NAME,
  Sum(C.IO_RATE) AS 'Sum of IO_rate'
FROM CAXCTRLS A, CAXDISK B, CAXDISKD C,
  CAXINTVL D
WHERE C.INTVL = D.INDEXX AND B.INDEXX = C.DISKS AND
  D.INTTYPE = 'M' AND
  B.CONN_TO_INTERFACE = A.INTERFACE_NAME
GROUP BY CAXCTRLS.CONNECT_TO_NODE,
  D.INT_START_DATE, D.INT_START_TIME,
  A.INTERFACE_NAME
HAVING (A.CONNECT_TO_NODE='nodename') AND
  (A.INTERFACE_NAME='controller#')
ORDER BY A.INTERFACE_NAME, D.INT_START_DATE,
  D.INT_START_TIME

Displaying measurements from different tables in the same report

The following SQL creates a report containing Packet Counts and Bytes in the same report:

SELECT C.NODE_NAME, C.INTRF_NAME,
  B.IN_PACKETS, B.IN_BYTES,
  B.OUT_PACKETS, B.OUT_BYTES,
  INT_START_DATE, INT_START_TIME
FROM CAXINTVL A, CAXNETID B, CAXNETIS C
WHERE C.INDEXX = B.NETINFCS AND B.INTVL = A.INDEXX
  AND A.INTTYPE = 'M' AND ((C.NODE_NAME='nodename') AND
  (C.INTRF_NAME='interface# '))
  ORDER BY INT_START_DATE, INT_START_TIME

The following SQL example creates a report containing system configuration information:

SELECT DISTINCT A.NODE_ALIAS,
  A.NODE_TYPE,
  A.OS_TYPE,
  A.NUM_OF_PROC,
  A.PAGE_SIZE,
  A.SPEC_RATING,
  A.MEMORY_SIZE
  FROM CAXNODE A

The following SQL example creates a report containing Controller /Disk Configuration information:

SELECT DISTINCT A.NODE_ALIAS,
  A.INTERFACE_NAME,
  B.DISK_NAME, B.DISK_TYPE,
  B.DISK_SIZE
FROM CAXCTRLS A, CAXDISK B
WHERE B.CONN_TO_INTERFACE =
  A.INTERFACE_NAME
  ORDER BY A.NODE_ID,
  A.INTERFACE_NAME,
  B.DISK_NAME