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 / Mask birthdays with RCAC
Created Oct 14, 2019
This example shows how to use a Column Mask to prevent users from seeing the actual birthdate.
View Mask birthdays with RCAC
--
-- RCAC Book: www.redbooks.ibm.com/redpieces/abstracts/redp5110.html
--
--
-- Who is allowed to deploy COLUMN MASKs and ROW PERMISSIONs
--
cl:CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(SCOTTF) USAGE(*ALLOWED);
call qsys.create_sql_sample('COFFEEBEAN');
@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 / 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 / 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 / JSON_TABLE and survival tips for shredding JSON with SQL
Last active Dec 16, 2019
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
View JSON_TABLE and survival tips for shredding JSON with SQL
-- This fails to return data....why?
SELECT cusip, issueDate, bidToCoverRatio
FROM JSON_TABLE(
SYSTOOLS.HTTPGETCLOB('https://www.treasurydirect.gov/TA_WS/securities/announced?format=json&type=FRN&pagesize=5', null),
'$.root[*]'
COLUMNS(cusip VARCHAR(10) PATH '$.cusip',
issueDate Timestamp PATH '$.issueDate',
bidToCoverRatio double PATH '$.bidToCoverRatio')
) AS X;
stop;
You can’t perform that action at this time.