sriedmue79

--
-- Description: The "Common Table Expression" (CTE) is a very powerful and useful tool which allows a temporary "table" to be
--                created in memory while a query is running. The contents of that temporary table (or "result set") can be
--                referenced elsewhere in the query.
--
--              The table functions provided by IBM as "IBM i Services" are ALSO very powerful. These functions allow us to
--                use SQL to retrieve many different details from the system. This includes details about user profiles,
--                network connections, objects, streamfiles, security, and much more.
--
--              We can leverage CTEs to combine multiple IBM i Services table functions (and views) and make them even more 

sriedmue79

--
-- Description: In many cases, IBM functions (or data that we have collected as customers/users) will provide
--              a fully qualified job identifier consisting of the job number, user profile, ad job name
--              formatted like '123456/USERNAME/JOBNAME'. Sometimes it is necessary to break this information
--              out into just the job number, or user, or job name.  
--              This "PARSE_JOB_DETAILS()" scalar function will do this.  Usage examples are provided further below.
--              The function accepts 2 parameters:
--                  1. The fully-qualified job
--                  2. The desired component (*JOBNUMBER, *JOBUSER, or *JOBNAME)
--              The function returns only the one requested component.

sriedmue79

--
-- Description: Queries for investigating a "high number of jobs in system" situation.
--              This walks through the process of figuring out whether the high number of jobs is being driven by a lot of 
--              active jobs, jobs sitting in JOBQs, or jobs that are in OUTQ status.  Once the source of the high number of 
--              jobs has been determined, there are additional queries which can be used to investigating each of those 
--              possible situations.
--

--Description: Quick overview of the LPAR's "jobs in system" - this will tell you whether there are a lot of jobs in ACTIVE, JOBQ, or OUTQ status
SELECT ACTIVE_JOB_TABLE_ENTRIES AS ACTIVE_JOBS, 

sriedmue79

--
-- Description: Delete IFS files within a particular path which have not been accessed in the past 3 months.
--              This example does not include subtrees, but that can be accomplished by changing the subtree option from 'NO' to 'YES'
--

--This query will list the files in the specified path which haven't been accessed in at least 3 months, along with a sample RMVLNK command string
SELECT PATH_NAME, ACCESS_TIMESTAMP, ('RMVLNK ''' || PATH_NAME || '''') AS CMD
    FROM TABLE (
            QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME => '/my/path', SUBTREE_DIRECTORIES => 'NO')
        )

sriedmue79

--
-- Description: Using the AUDIT_JOURNAL_PW view, extract details from the 
--              system audit journal (QAUDJRN) about invalid login attempts 
--              (i.e. wrong password, invalid user) that have occurred in 
--              the past 10 minutes.
--              

--Description: the number of invalid login attempts in the past 10 minutes
SELECT COUNT(1) AS INVALID_LOGIN_ATTEMPTS
    FROM TABLE (

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;