--  category:  IBM i Services for BRMS
--  description:  Application - BRMS Media Info Views
--  minvrm: V7R3M0
--  last updated: 3/14/2023


--
-- View ALL media information
--
SELECT *
    FROM QUSRBRM.MEDIA_INFO;
--  
-- Report active volumes that are marked full
-- These are volumes which WILL NOT be selected for a write operation.
--  
SELECT VOLUME_SERIAL, 
       OWNING_SYSTEM,
       VOLUME_STATUS,
       EXPIRATION,
       EXPIREDATE,
       LOCATION,
       MEDIACLASS 
    FROM QUSRBRM.MEDIA_INFO 
    WHERE FULL = 'YES' 
          AND STATUS = 'ACTIVE'
    ORDER BY OWNING_SYSTEM,
             VOLUME_SERIAL;

--
-- Report expired volumes that need to be initialize before they are written to.
--
SELECT VOLUME_SERIAL,
       OWNING_SYSTEM,
       VOLUME_STATUS,
       EXPIRATION,
       EXPIREDATE,
       LOCATION,
       MEDIACLASS
    FROM QUSRBRM.MEDIA_INFO
    WHERE STATUS = 'INITIALIZE'
    ORDER BY OWNING_SYSTEM,
             VOLUME_SERIAL;

--
-- Report volumes that are "in use" or reserved to a BRMS job.
--
SELECT VOLUME_SERIAL,
       VOLUME_RESERVED,
       OWNING_SYSTEM
    FROM QUSRBRM.MEDIA_INFO
    WHERE VOLUME_RESERVED <> ' '
    ORDER BY OWNING_SYSTEM,
             VOLUME_SERIAL;

--
-- Report volumes that are active and marked for duplication, these volumes cannot be expired until they are duplicated.
--
SELECT VOLUME_SERIAL,
       DUPESTATUS,
       OWNING_SYSTEM
    FROM QUSRBRM.MEDIA_INFO
    WHERE STATUS = 'ACTIVE'
          AND (DUPESTATUS = 'MARKED'
              OR DUPESTATUS = 'BOTH')
    ORDER BY OWNING_SYSTEM,
             VOLUME_SERIAL;


--
-- Report volumes that are identified by BRMS as being actively tranferred to a cloud location.
--
SELECT VOLUME_SERIAL,
       OWNING_SYSTEM,
       VOLUME_STATUS,
       LOCATION,
       MEDIACLASS
    FROM QUSRBRM.MEDIA_INFO
    WHERE VOLUME_STATUS = 'UPLOAD'
    ORDER BY OWNING_SYSTEM,
             VOLUME_SERIAL;

--
-- Report volumes that are identified by BRMS as being actively tranferred from a cloud location.
--
SELECT VOLUME_SERIAL,
       OWNING_SYSTEM,
       VOLUME_STATUS,
       LOCATION,
       MEDIACLASS
    FROM QUSRBRM.MEDIA_INFO
    WHERE VOLUME_STATUS = 'DOWNLOAD'
    ORDER BY OWNING_SYSTEM,
             VOLUME_SERIAL;

--
-- Report the expired volumes.
--
SELECT VOLUME_SERIAL,
       OWNING_SYSTEM,
       VOLUME_STATUS,
       LOCATION,
       MEDIACLASS
    FROM QUSRBRM.MEDIA_INFO
    WHERE VOLUME_STATUS = 'EXPIRED'
    ORDER BY OWNING_SYSTEM,
             MEDIACLASS,
             VOLUME_SERIAL;

--
-- Count of expired volumes by owning system and media class
--
SELECT OWNING_SYSTEM,
       MEDIACLASS,
       COUNT(*) AS NUMBER_EXPIRED_VOLUMES
    FROM QUSRBRM.MEDIA_INFO
    WHERE VOLUME_STATUS = 'EXPIRED'
    GROUP BY OWNING_SYSTEM,
             MEDIACLASS;

--
-- Count of active volumes by owning system and media class
--
SELECT OWNING_SYSTEM,
       MEDIACLASS,
       COUNT(*) AS NUMBER_ACTIVE_VOLUMES
    FROM QUSRBRM.MEDIA_INFO
    WHERE VOLUME_STATUS = 'ACTIVE'
    GROUP BY OWNING_SYSTEM,
             MEDIACLASS;