forstie
/ alerting on high levels of jobs.sql
Created
May 29, 2022 12:58
The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
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 |
forstie
/ calculating an ALLOCATE value for a column.sql
Created
March 3, 2020 20:11
Database Engineers sometimes need to identify data models with varying length columns, where the allocate clause could be improved. When you use the ALLOCATE(n) clause, you're telling the database to establish n number of bytes for the column in the fixed portion of th record. If the column value for a row has a length > n, the database uses the…
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) |
forstie
/ Optimize local journals
Created
October 12, 2019 21:29
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, |
forstie
/ Largest MTIs in use today.sql
Created
October 27, 2020 17:59
Maintained Temporary Indexes (MTIs)... everyone has them, but they're similar to building a house on sand... your foundation for performance is not rock solid. Use this Gist to gain some insight into this topic!
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 | |
| -- |
forstie
/ Optimize journaled database files
Created
October 12, 2019 22:24
Subtle options in how objects are journaled can have a big impact on performance.
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, |
NewerOlder