Skip to content

Instantly share code, notes, and snippets.

Scott Forstie forstie

Block or report user

Report or block forstie

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@forstie
forstie / TopN user storage report.sql
Created Feb 9, 2020
This example takes a previous example and extends it. The idea here is that you want to proactively manage user consumption of storage. For the top storage consumers, return a report that lists their largest objects (either in QSYS or IFS) and provide some contextual detail. This solution includes simple controls to allow the caller to specify t…
View TopN user storage report.sql
-- #SQLcandoit #IBMiServices #Db2fori
-- ======================================================================
--
-- Establish a UDTF that finds the largest N objects for a specific user
--
-- ======================================================================
create or replace function systools.N_largest_objects_owned (
user_name varchar(10) for sbcs data, limit_size bigint default 10
)
@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 / Generating spreadsheets with SQL.sql
Last active Jan 13, 2020
In this working example, we establish an SQL table which contains the spreadsheets we'd like to have generated. Each row in the table includes the query that will supply the data for the spreadsheet, and the location in the IFS where the spreadsheet should reside. Further, a different procedure emails the spreadsheets to an interested consumer. …
View Generating spreadsheets with SQL.sql
-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date : January 10, 2020
-- =================================================================
--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:
@forstie
forstie / SWAP_DYNUSRPRF.sql
Created Nov 24, 2019
With the latest Db2 PTF Groups for IBM i 7.3 and 7.4, you can now identify and fix those *PGM and *SRVPGM's that use SQL and were built incorrectly. This example finds those cases where *OWNER will be used for static SQL, but *USER will be used for dynamic SQL. The procedure swaps the dynamic user profile setting to *OWNER. This utility approach…
View SWAP_DYNUSRPRF.sql
--
-- description: Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select user_profile, dynamic_user_profile, program_schema, program_name, program_type,
module_name, program_owner, program_creator, creation_timestamp, default_schema,
"ISOLATION", concurrentaccessresolution, number_statements, program_used_size,
number_compressions, statement_contention_count, original_source_file,
original_source_file_ccsid, routine_type, routine_body, function_origin,
function_type, number_external_routines, extended_indicator, c_nul_required,
naming, target_release, earliest_possible_release, rdb, consistency_token,
@forstie
forstie / ALLOBJ users with default passwords.sql
Created Nov 16, 2019
Security implementations can and should be monitored closely and on a regular cadence. This is one example where SQL can be used instead of the Analyze Default Passwords (ANZDFTPWD) command.
View ALLOBJ users with default passwords.sql
--
-- Note, this example might take a while to run because its doing an exhaustive evaluation of which
-- users have *ALLOBJ special authority, either directly in their profile or indirectly via
-- group profile membership.
--
-- Next, those "super" users are evaluated to determine which (if any) of them have their password
-- set to match their user profile. (aka using a default password)
--
-- If this returns zero rows... good!
--
@forstie
forstie / Temporalize a library.sql
Created Nov 8, 2019
System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.
View Temporalize a library.sql
--
--
-- description: find database files and deploy Temporal over them
-- note: The history table will be named <existing-table-name>_HISTORY
-- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i
-- minvrm: V7R3M0
--
CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10))
BEGIN
@forstie
forstie / journal receivers attached or not.sql
Created Oct 23, 2019
I was asked to provide a way to find journal receivers and discern whether or not they are attached or detached.
View journal receivers attached or not.sql
--
-- description: Which journal receivers are currently attached?
--
select attached_journal_receiver_library, attached_journal_receiver_name
from qsys2.journal_info
where journal_library = 'PRODLIB'
order by 1,2;
@forstie
forstie / Counting objects in a library.sql
Created Oct 22, 2019
There's frequently more than one way to code a solution. Today I was asked how to use SQL to count the number of objects within a library. There are two examples... one is very fast, but requires IBM i 7.4.
View Counting objects in a library.sql
--
-- description: Count objects in a library (superfast)
-- minvrm: v7r4m0
--
with libs (ln) as (
select objname
from table (
qsys2.object_statistics('*ALLSIMPLE', 'LIB')
)
)
@forstie
forstie / SQL generated table comparison query
Last active Nov 5, 2019
SQL, LISTAGG(), QSYS2.SYSCOLUMNS2, and IS NOT DISTINCT all team up here to generate a table level comparision query.
View SQL generated table comparison query
--
-- Imagine that you have 2 versions of the same table.
-- The tables have the same format.
-- This example provides an SQL function that generates a table compare query.
--
create schema coolstuff;
create table coolstuff.table_master as (select * from qsys2.syslimtbl limit 100) with data;
create table coolstuff.table_secondary as (select * from qsys2.syslimtbl limit 100) with data;
select * from coolstuff.table_secondary;
update coolstuff.table_secondary set limit_category = 555 limit 10;
You can’t perform that action at this time.