SQL queries

List the number of clients by platform:

SELECT platform_name,count(*) AS "Number of Nodes" FROM nodes GROUP BY platform_name ORDER BY "Number of Nodes"

List all of the tapes used by a node:

SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE node_name='xxx'

List all of the nodes using a tape:

SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE volume_name='xxx'

List tapes used today:

SELECT volume_name,last_write_date FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)

Or to list the last write date for all the tapes in order:

SELECT volume_name,last_write_date FROM volumes ORDER BY last_write_date

Or to see just the number of tapes written to in the last 24 hours:

SELECT count(*) AS "Tapes Used" FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)

List tapes with errors:

SELECT volume_name, read_errors,write_errors FROM volumes WHERE (read_errors >= 1 OR write_errors >= 1)

List the number of tapes to be reclaimed at a certain level:

SELECT count(*) FROM volumes WHERE (stgpool_name='TAPEPOOL' AND upper(status)='FULL' AND pct_utilized < 70)

List the MB backed up last night per node:

SELECT entity AS "Node name", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", SUBSTR(CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours) GROUP BY ENTITY order by "MB xfer"

List how much data was backed up yesterday:

SELECT entity AS "Node name", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours) GROUP BY entity

Or to just see how much was backed up system wide:

SELECT CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer" FROM summary WHERE (activity='BACKUP' AND start_time>=current_timestamp - 24 hours)

List what nodes did restores yesterday:

SELECT entity, bytes, start_time, successful FROM summary WHERE (activity='RESTORE' AND start_time>=current_timestamp - 24 hours)

List the tapes in use and max scratch by storage pool:

SELECT a.stgpool_name,a.maxscratch,count(*) AS Volumes FROM stgpools a, volumes b WHERE a.stgpool_name = b.stgpool_name GROUP BY a.stgpool_name,a.maxscratch

List the number of tapes filling and how full by storage pool:

This lists the number of tapes that are "filling" and an average of how full they are per storage pool. This is a query that I wrote for the BigBrother client as a replacement for q stg:

SELECT a.stgpool_name AS "STORAGE POOL", CAST(a.EST_CAPACITY_MB AS DECIMAL(12,0)) AS "SIZE MB", CAST(a.PCT_UTILIZED AS DECIMAL(2,0)) AS "PCT", count(*) AS Filling, CAST(AVG(b.PCT_UTILIZED) AS DECIMAL(2,0)) AS "PCT Full" FROM stgpools a, volumes b WHERE ((a.stgpool_name = b.stgpool_name AND upper(status)='FILLING') OR (a.stgpool_name = b.stgpool_name AND devclass_name='DISK')) GROUP BY a.stgpool_name, a.EST_CAPACITY_MB, a.PCT_UTILIZED

Check database performance:

List the DB Pages backed up per hour (should be 5M or above).

SELECT activity, CAST((end_time) AS date) AS "Date", (examined/cast ((end_time-start_time) seconds AS DECIMAL(18,13))*3600) "Pages backed up/Hr" FROM summary WHERE activity='FULL_DBBACKUP' AND days(end_time) - days(start_time)=0M

List the DB Pages expired per hour (should be 3.8M or above).

SELECT activity, CAST((end_time) AS date) AS "Date", (examined/CAST((end_time-start_time) seconds AS DECIMAL(24,2))*3600) "Objects Examined Up/Hr" FROM summary WHERE activity='EXPIRATION'

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Cannot get this sql to work since we went to version 6.1 . Any help would be gratly appreciated. This query is to see nodes accessed.

    SELECT NODE_NAME, domain_name as DOMAIN_POLICY ,(cast((current_timestamp-LASTACC_TIME)days as decimal) as LAST_ACCESSED FROM NODES WHERE (CAST((CURRENT_TIMESTAMP-LASTACC_TIME)DAYS AS DECIMAL) >= 10 group by node_name, domain_name, lastacc_time

    • I’m no SQL guru, but there are a lot of changes in the 6.1 SQL. A lot of the things we got away with before won’t fly now. And, I’ve found a couple of keyword changes too… I couldn’t get your original code to work, so here’s what worked under 5.X:

      SELECT NODE_NAME, -
      domain_name as DOMAIN_POLICY, -
      (cast((current_timestamp-LASTACC_TIME)days as decimal)) as LAST_ACCESSED -
      FROM NODES WHERE (CAST((CURRENT_TIMESTAMP-LASTACC_TIME)DAYS as DECIMAL)) >= 10 -
      group by node_name, domain_name, lastacc_time

      But, I think this will work for you under 6.1:

      SELECT NODE_NAME, -
      domain_name as DOMAIN_POLICY, -
      cast(days(current_timestamp)-days(LASTACC_TIME) as decimal (20,0)) as LAST_ACCESSED -
      FROM NODES WHERE cast(days(current_timestamp)-days(LASTACC_TIME) as decimal (20,0)) >= 10 -
      group by node_name, domain_name, lastacc_time