Skip to content

Instantly share code, notes, and snippets.

Avatar
🏠
Working from home

Fernando Plaza fplazavi

🏠
Working from home
  • @cdinvest
  • Madrid
View GitHub Profile
@forstie
forstie / alerting on high levels of jobs.sql
Created May 29, 2022
The request... provide a way to alert when the number of jobs is growing to a concerning level. The solution follows...
View alerting on high levels of jobs.sql
--
-- 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
@BirgittaHauser
BirgittaHauser / Scan_All_Spoolfiles_in_an_Outqueue.sql
Last active Apr 25, 2022
Scan through all spoolfiles in a specific outqueue
View Scan_All_Spoolfiles_in_an_Outqueue.sql
-- 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'
@forstie
forstie / Protect the IFS root for *PUBLIC
Created Feb 8, 2021
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.
View Protect the IFS root for *PUBLIC
--
-- 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;
@forstie
forstie / Library sizes and more.sql
Created Nov 27, 2020
With Db2 PTF Group SF99703 level 22 and Db2 PTF Group SF99704 level 10 (aka TR9 and TR3 timed enhancements), the LIBRARY_INFO UDTF has optional input paramters to provide better performing queries for library specific questions.
View Library sizes and more.sql
-- category: IBM i Services
-- description: Librarian - Library Info
-- minvrm: V7R3M0
--
create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default
'create or replace table coolstuff.library_sizes
(library_name, schema_name,
-- qsys2.library_info() columns
@forstie
forstie / Largest MTIs in use today.sql
Created Oct 27, 2020
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!
View Largest MTIs in use today.sql
--
-- 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
forstie / calculating an ALLOCATE value for a column.sql
Created Mar 3, 2020
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…
View calculating an ALLOCATE value for a column.sql
--
-- 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
forstie / Object ownership by user - total report.sql
Created Feb 2, 2020
This gist combines several IBM i (SQL) Services to produce an easy to consume UDTF. Pass in a user name and you'll get back all the QSYS and IFS objects they own, ordered by size descending.
View Object ownership by user - total report.sql
create or replace function systools.objects_owned(
user_name varchar(10) FOR SBCS DATA)
RETURNS TABLE (
object DBCLOB(16M) CCSID 1200,
size varchar(50) FOR SBCS DATA,
created timestamp,
changed timestamp,
used timestamp)
SPECIFIC SYSTOOLS.objects_owned
NOT DETERMINISTIC
@forstie
forstie / Use ACS on your IBM i to build spreadsheets.sql
Created Jan 17, 2020
This example simplifies a previous gist. ACS is now being shipped on your IBM i via PTFs. Subsequent PTFs will ship when major enhancements are made to ACS.
View Use ACS on your IBM i to build spreadsheets.sql
--
-- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the
-- acsbundle.jar onto the IBM i. Just apply the latest ACS PTFs and reference the jar
-- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar
--
-- Initial PTFs of ACS
-- V7R4M0 SI71900
-- V7R3M0 SI71934
--
@forstie
forstie / Optimize journaled database files
Created Oct 12, 2019
Subtle options in how objects are journaled can have a big impact on performance.
View Optimize journaled database files
--
--
-- 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,
View Optimize local journals
--
-- 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,