--  category:  IBM i Services for BRMS
--  description:  Application - BRMS Enterprise views
--  minvrm: V7R3M0
--  Requires BRMS PTFs - 7.5 SI80273, 7.4 SI80272, 7.3 SI80271
--  last updated: 3/14/2023

--  
-- Optional: Initialize BRMS SQL services 
--  
-- Ensures the BRMS SQL services are enabled and available to use 
--  
CL: INZBRM OPTION(*SQLSRVINZ);

--
-- View hub
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_HUB_INFO;
--
-- Example: Report the primary hub contact and refresh rate
--
SELECT PRIMARY_CONTACT,
       REFRESH_RATE
    FROM QUSRBRM.ENTERPRISE_HUB_INFO;

--
-- View network
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_NETWORK_INFO;
--
-- Example 1: Check your network health
--
SELECT SYSTEM_NAME,
       INSTALLED_BRMS_RELEASE,
       LATEST_BRMS_PTF_ID,
       COMMUNICATION_ACTIVE,
       TRANSACTIONS_BEHIND
    FROM QUSRBRM.ENTERPRISE_NETWORK_INFO;
--
-- Example 2: Report systems in the network which are not active
--
SELECT SYSTEM_NAME,
       INSTALLED_BRMS_RELEASE,
       COMMUNICATION_ACTIVE
    FROM QUSRBRM.ENTERPRISE_NETWORK_INFO
    WHERE COMMUNICATION_ACTIVE = 'NO';
--
-- Example 3: Report systems in the network which are behind on network transactions
--
SELECT SYSTEM_NAME,
       INSTALLED_BRMS_RELEASE,
       COMMUNICATION_ACTIVE,
       TRANSACTIONS_BEHIND
    FROM QUSRBRM.ENTERPRISE_NETWORK_INFO
    WHERE TRANSACTIONS_BEHIND > 0;
--
-- Example 4: Report systems that failed backups the last time they ran
--
SELECT SYSTEM_NAME,
       INSTALLED_BRMS_RELEASE,
       LATEST_BRMS_PTF_ID,
       BACKUPS_FAILED_LAST_RUN
    FROM QUSRBRM.ENTERPRISE_NETWORK_INFO
    WHERE BACKUPS_FAILED_LAST_RUN > 0;

--
-- View nodes
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_NODE_INFO;
--
-- Example 1: Report the output queues being used to store reports for system name APPN.SYSTEM1
--
SELECT SYSTEM_NAME,
       HUB_OUTPUT_QUEUE_LIBRARY,
       HUB_OUTPUT_QUEUE,
       STORE_REPORTS_ON_NODE,
       NODE_OUTPUT_QUEUE_LIBRARY,
       NODE_OUTPUT_QUEUE
    FROM QUSRBRM.ENTERPRISE_NODE_INFO
    WHERE SYSTEM_NAME = 'APPN.SYSTEM1';
--
-- Example 2:  Report the contacts being used for system name APPN.SYSTEM5
--
SELECT SYSTEM_NAME,
       PRIMARY_CONTACT,
       SECONDARY_CONTACT
    FROM QUSRBRM.ENTERPRISE_NODE_INFO
    WHERE SYSTEM_NAME = 'APPN.SYSTEM5';

--
-- View contacts
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_CONTACT_INFO;
--
-- Example: Report contact information for contact user name ADMIN
--
SELECT CONTACT_USER_NAME,
       FIRST_NAME,
       LAST_NAME,
       EMAIL_ADDRESS
    FROM QUSRBRM.ENTERPRISE_CONTACT_INFO
    WHERE CONTACT_USER_NAME = 'ADMIN';

--
-- View report definition
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_REPORT_DEFINITION_INFO;
--
-- Example 1: Report commands being run weekly on Mondays
--
SELECT REPORT_DEFINITION,
       SYSTEM_NAME,
       FREQUENCY_INTERVAL,
       RUN_MONDAY,
       GENERATION_TIME,
       REPORT_COMMAND
    FROM QUSRBRM.ENTERPRISE_REPORT_DEFINITION_INFO
    WHERE FREQUENCY_INTERVAL = 'WEEKLY'
          AND RUN_MONDAY = 'YES';
--
-- Example 2: Reports that are sending email notification when the reports are generated
--
SELECT REPORT_DEFINITION,
       SYSTEM_NAME,
       SEND_REPORT_EMAIL
    FROM QUSRBRM.ENTERPRISE_REPORT_DEFINITION_INFO
    WHERE SEND_REPORT_EMAIL = 'YES';

--
-- View report status
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_REPORT_STATUS;
--
-- Example 1:  Reports that are currently running
--
SELECT REPORT_DEFINITION,
       REPORT_SYSTEM,
       REPORT_ACTIVE,
       REPORT_START_TIME
    FROM QUSRBRM.ENTERPRISE_REPORT_STATUS
    WHERE REPORT_ACTIVE = 'YES';
--
-- Example 2:  Reports the last time the report definition named RECOVERY successfully ran
--
SELECT REPORT_DEFINITION,
       REPORT_SYSTEM,
       LAST_SUCCESS_TIME
    FROM QUSRBRM.ENTERPRISE_REPORT_STATUS
    WHERE REPORT_DEFINITION = 'RECOVERY';
--
-- Example 3:  Reports that are scheduled to run the next day
--
SELECT REPORT_DEFINITION,
       REPORT_SYSTEM,
       NEXT_RUN_TIME
    FROM QUSRBRM.ENTERPRISE_REPORT_STATUS
    WHERE DATE(NEXT_RUN_TIME) = CURRENT_DATE + 1 DAYS;

--
-- View report output
--
SELECT *
    FROM QUSRBRM.ENTERPRISE_REPORT_OUTPUT;
--
-- Example 1: Report output for report definition named QREPORTDFN on system APPN.SYSTEM1 for the last 7 days
--
SELECT REPORT_DEFINITION,
       REPORT_SYSTEM,
       REPORT_COMMAND,
       REPORT_START_TIME,
       SPOOLED_FILE,
       REPORT_NUMBER,
       TOTAL_REPORTS
    FROM QUSRBRM.ENTERPRISE_REPORT_OUTPUT
    WHERE DATE(REPORT_START_TIME) > CURRENT_DATE - 7 DAYS
          AND REPORT_SYSTEM = 'APPN.SYSTEM1'
          AND REPORT_DEFINITION = 'QREPORTDFN'
    ORDER BY REPORT_START_TIME,
             REPORT_NUMBER;
--
-- Example 2: Output queue being used to store reports for report definition named QREPORTDFN on system APPN.SYSTEM1
--
SELECT DISTINCT REPORT_DEFINITION,
                REPORT_SYSTEM,
                OUTPUT_QUEUE_LIBRARY,
                OUTPUT_QUEUE,
                OUTPUT_QUEUE_SYSTEM
    FROM QUSRBRM.ENTERPRISE_REPORT_OUTPUT
    WHERE REPORT_SYSTEM = 'APPN.SYSTEM1'
          AND REPORT_DEFINITION = 'QREPORTDFN';

--
-- View the spooled data from the report output
-- Use the SPOOLED_FILE_DATA table function
-- Copy the QUALIFIED_SPOOLED_FILE_JOB, SPOOLED_FILE_NUMBER and SPOOLED_FILE_USER_DATA from the QUSRBRM.ENTERPRISE_REPORT_OUTPUT view
--
SELECT SPOOLED_DATA
    FROM TABLE (
            SYSTOOLS.SPOOLED_FILE_DATA(
                JOB_NAME => '810215/QUSER/QPRTJOB', SPOOLED_FILE_NUMBER => '364', SPOOLED_FILE_NAME => 'QP1ARCY')
        );

SELECT *
    FROM qusrbrm.media_info;