buzzia2001

-- Purpose: This query retrieves the attributes of each job with spool QPJOBLOG
--          PLEASE NOTE: consider using filters on QSYS2.JOB_INFO 
-- Version: 1.0
-- Date 25/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-job-info-table-function
--       https://www.ibm.com/docs/en/i/7.5.0?topic=services-output-queue-entries-basic-view
--       https://www.ibm.com/docs/en/i/7.5.0?topic=services-job-description-info-view
-- Note: if you want to check obly job with different logging level than specified in the jobd you can add this WHERE clause:
--       X.MESSAGE_LOGGING_LEVEL CONCAT ' ' CONCAT X.MESSAGE_LOGGING_SEVERITY CONCAT ' ' CONCAT X.MESSAGE_LOGGING_TEXT <>

buzzia2001

-- Purpose: Getting information about a saved object (e.g. QGPL/QAPZCOVER)
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- LPP Requirements: 577BR2
-- PTF Requirements: SJ06117 V7R6M0, SJ06116 V7R5M0, SJ06115 V7R4M0
-- Docs: https://fortradocs.atlassian.net/wiki/spaces/IWT/pages/3327361025/QUSRBRM.BACKUP_HISTORY_OBJECT+View

SELECT VOLUME_SERIAL,
       DEVICE_NAMES,

buzzia2001

-- Purpose: List locks on my system
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-object-lock-info-view

SELECT SELECT SYSTEM_OBJECT_SCHEMA,
                   SYSTEM_OBJECT_NAME,
                   SYSTEM_TABLE_MEMBER,
                   OBJECT_TYPE,

buzzia2001

-- Purpose: List tapes used during this job 
-- Version: 1.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.5.0?topic=services-qusrbrmmedia-info-view-sql-service
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-active-job-info-table-function

SELECT VOLUME_SERIAL
            FROM QUSRBRM.MEDIA_INFO
                 INNER JOIN QUSRBRM.QA1AMM

buzzia2001

-- Purpose: This query returns the temporary storage's bucket ordered by size, sometimes is possibile to use it to understand what job is causing issues with temporary storage
-- Version: 1.0
-- Date 18/01/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-systmpstg-view

SELECT
       CASE
           WHEN GLOBAL_BUCKET_NAME IS null THEN JOB_NUMBER CONCAT '/' CONCAT JOB_USER_NAME CONCAT '/' CONCAT JOB_NAME
           ELSE GLOBAL_BUCKET_NAME

buzzia2001

-- Purpose: This query retrieves certificate information from the IBM i system certificate store, calculating the days remaining until certificate expiration and the impacted applications
-- Version: 2.0
-- Date 23/01/2026
-- Author: Andrea Buzzi
-- PTF Requirements: IBM i 7.6 SF99960 level 2 or IBM i 7.5 SF99950 level 11
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-certificate-usage-info-view
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-certificate-info-table-function
-- Note: You can specify *NOPWD as *SYSTEM's keystore password

SELECT DESCRIPTION,