sriedmue79

--
-- Description: When a record is deleted from a file/table, it continues to take up space on disk.
--              This is because the space has been allocated by the file, and that space only gets
--              released when the file is reorganized (RGZPFM).  If the file is configured to 
--              "reuse deleted records" this is not a concern.  The next record that is written to
--              the file will reuse the space that was being used by the deleted record.  However,
--              files created from DDS will not reuse deleted records by default.  This can be
--              changed (with caution) using the CHGPF command.  A file can consist of 100% deleted
--              records, meaning that it contains no actual but consumes a large amount of storage.
--              The goal of this gist is to find the files in your system with a lot of deleted 

sriedmue79

--
-- Description: Multi-member files are unique to DB2 for i.  Some older applications in your environment might
--              be utilizing the concept of multiple member files.  Sometimes these applications continue to
--              create additional members, with no cleanup in place.  Even if a file has been configured with 
--              "*NOMAX" maximum members, there is still a limit within the database/OS (32,767).  If a program
--              attempts to add a member to a file which has already reached the maximum allowable limit, an error
--              will occur (CPF3213).  Ideally we should get ahead of this issue before our files reach the max.
--
--              The following queries can be used for this type of investigation, finding multi-member files
--              so they can be addressed before the member limit is reached causing a production work stoppage.

sriedmue79

--
-- Description: As an audit requirement, all user profiles that are created should include a ticket number in the text description.
--              This SQL can be scheduled to run daily, and will send an email report alerting the administration team to any
--              user profiles that have been created in the past 7 days without a ticket number mentioned in the text.
--              This version is based on ServiceNOW ticket naming standards (SCTASK, RITM, CTASK, CHG) but can be adapted to suit.
--

-- Skip to the end (Step 5) for the complete solution, or follow along with the incremental improvements --

--Step 1: List all user profiles whose text descriptions don't reference a SNOW ticket

sriedmue79

-- !!IMPORTANT!! This query assumes that the temporary addresses were reset to 0% at the last IPL.
--               This assumption is only safe in i 7.4 and later releases.
--               Refer to this IBM doc for full details: https://www.ibm.com/support/pages/temporary-addresses
--               This query also assumes that the timestamp of the QCTL subsystem startup is an accurate representation of the last IPL.
--
-- Description: When temporary address usage reaches 100% in an LPAR, the LPAR will end abnormally.
--              This query estimates the date when this LPAR will reach 100% temp address usage, based on the temp address usage 
--              that has occurred since the last IPL date (using the start date of QCTL subsystem) and the current temp address 
--              percentage.  It calculates the average temp address percentage increase per day since the last IPL, and uses that
--              to project the number of days before 100% will be reached.

sriedmue79

-------------------------------------------------------------------------
-- Description: queries related to subsystem autostart job entries (AJEs)
-------------------------------------------------------------------------

--Description: list the autostart job entries that are defined for a given subsystem description
SELECT *
    FROM QSYS2.AUTOSTART_JOB_INFO
    WHERE SUBSYSTEM_DESCRIPTION_LIBRARY = :SBSD_Library AND
          SUBSYSTEM_DESCRIPTION         = :SBSD_Name;

sriedmue79

---------------------------------------------------------------------
-- Description: Various uses for the ACTIVE_JOB_INFO() table function
---------------------------------------------------------------------

--Description: check whether a job (by job name) is currently active in a particular subsystem
SELECT JOB_NAME
    FROM TABLE ( QSYS2.ACTIVE_JOB_INFO( JOB_NAME_FILTER       => :JobName, 
                                        SUBSYSTEM_LIST_FILTER => :SubsystemName ) );

--Description: Jobs in a subsystem that are in a specific status