This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- | |
-- Find journaled database files that can be adjusted for improved performance | |
-- | |
-- Resources: | |
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm | |
-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf | |
-- | |
select 'TOYSTORE', objname as file, omit_journal_entry, journal_images, objtype, objowner, | |
objdefiner, objcreated, objsize, objtext, objlongname, last_used_timestamp, journaled, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Gist: Largest MTIs in use today | |
-- | |
-- Use SQL to understand where Maintained Temporary Indexes (MTIs) are being used, and more... | |
-- | |
-- | |
-- | |
-- Find the tables with the 10 largest MTIs | |
-- |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Search for journals that can be easily improved | |
-- | |
-- Resources: | |
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqviewjournalinfo.htm | |
-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf | |
-- | |
select journal_library, journal_name, receiver_maximum_size, remove_internal_entries, asp_number, | |
journal_aspgrp, attached_journal_receiver_name, attached_journal_receiver_library, | |
message_queue, message_queue_library, delete_receiver_option, delete_receiver_delay, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule | |
-- (80% of the time, the length of the column data would be less than or equal to n) | |
-- minvrm: V7R3M0 | |
-- | |
create or replace function systools.compute_allocate ( | |
p_schema_name varchar(128) for sbcs data, | |
p_table_name varchar(128) for sbcs data, | |
p_column_name varchar(128) for sbcs data, | |
allocate_percentage decimal(3,2) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Subject: The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows... | |
-- Author: Scott Forstie | |
-- Date : May, 2022 | |
-- Features Used : This Gist uses system_value_info, CTEs, syslimits_basic, override_qaqqini, MESSAGE_QUEUE_INFO, SEND_MESSAGE, and more | |
-- | |
-- | |
-- System limit tracking provides IBM i health insight: | |
-- ================================================================ | |
-- 1) Recognize when the IBM i is trending towards an outage or serious condition |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This procedure deletes old backup files produced | |
-- when the RSTOBJ command restores file objects. | |
-- | |
-- It uses the regex to filter the names of the files | |
-- the RSTOBJ command gives these file objets in the rename process. | |
-- | |
-- Simply paste this gist into ACS SQL and step through the code. | |
-- | |
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library | |
-- |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Scan through all spoolfiles in a specific outqueue (e.g. QEZJOBLOG) for a specific string | |
Select a.Job_Name, Spooled_File_Name, File_Number, Spooled_Data | |
-- , a.* | |
from OutPut_Queue_Entries a Cross Join | |
Lateral(Select * | |
From Table(SysTools.Spooled_File_Data( | |
Job_Name => a.Job_Name, | |
Spooled_File_Name => a.Spooled_File_Name, | |
Spooled_File_Number => File_Number))) b | |
Where Output_Queue_Name = 'QEZJOBLOG' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace function systools.objects_owned( | |
user_name varchar(10) FOR SBCS DATA) | |
RETURNS TABLE ( | |
object DBCLOB(16M) CCSID 1200, | |
object_type varchar(10) for sbcs data, | |
size bigint, | |
sizeform varchar(50) FOR SBCS DATA, | |
created timestamp, | |
changed timestamp, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT ENTRY_TIMESTAMP as JE_TIME, CURRENT_USER as CURRENT_USR, | |
SEQUENCE_NUMBER, JOURNAL_ENTRY_TYPE,JOB_NAME, | |
JOB_USER, JOB_NUMBER,PROGRAM_NAME, | |
OBJECT,OBJECT_TYPE,PATH_NAME, | |
CAST(cast(substring(entry_data, 1, 32) as char(32) for bit data) as CHAR(32) CCSID 1141) as F1, | |
CAST(cast(substring(entry_data, 33, 130) as char(130) for bit data) as CHAR(130) CCSID 1141) as F2, | |
HEX(cast(substring(entry_data, 163, 258) as char(258) for bit data)) as F3, | |
HEX(cast(substring(entry_data, 421, 6) as char(6) for bit data)) as N1, | |
HEX(cast(substring(entry_data, 427, 11) as char(11) for bit data)) as N2, | |
HEX(cast(substring(entry_data, 438, 4) as char(4) for bit data)) as N |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- When an IFS directory includes W (write), you are exposed to malware attacks | |
-- Use this to review and overcome this topic for the all important ROOT directory | |
-- | |
-- For help on this or related security topics, contact Robert and team... | |
-- http://ibm.biz/IBMiSecurity | |
-- Robert Andrews - robert.andrews@us.ibm.com | |
-- | |
stop; |
OlderNewer