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

--
-- View ALL media information
--
SELECT *
    FROM QUSRBRM.BRMS_LOG_INFO;

--
-- Example: View Log info for a specific job and control group
--
SELECT *
    FROM QUSRBRM.BRMS_LOG_INFO
    WHERE QUALIFIED_JOB_NAME = '468928/QUSER/QBRMBKUP'
          AND CONTROL_GROUP = 'TEST';

--
-- Example: Join the log information with the backup status to view the messages for a specific control group run on today's date
--
SELECT BS.BACKUP_SYSTEM,
       BS.CONTROL_GROUP,
       BS.QUALIFIED_JOB_NAME,
       BS.SAVE_START,
       BS.BACKUP_MINUTES,
       BS.OBJECTS_SAVED,
       BS.OBJECTS_NOT_SAVED,
       BS.SAVE_STATUS,
       LG.MESSAGE_ID,
       LG.MESSAGE_TEXT,
       LG.MESSAGE_TIMESTAMP
    FROM QUSRBRM.CG_BACKUP_STATUS BS
         LEFT JOIN QUSRBRM.BRMS_LOG_INFO LG
             ON BS.QUALIFIED_JOB_NAME = LG.QUALIFIED_JOB_NAME
    WHERE DATE(BS.SAVE_START) = CURRENT DATE
          AND BS.CONTROL_GROUP = 'TEST'
    ORDER BY BS.CONTROL_GROUP,
             BS.SAVE_START;