Skip to content

Instantly share code, notes, and snippets.

View forstie's full-sized avatar

Scott Forstie forstie

180 followers · 0 following
  • IBM
View GitHub Profile
@forstie
forstie / Reuseable SQL code segments with INCLUDE
Last active November 5, 2019 12:56
Locate reusable pieces of SQL code into the IFS. Then, use INCLUDE to pull those segments into your SQL routines or triggers, and even ACS's Run SQL Scripts or RUNSQLSTM.
--
-- Use INCLUDE (SQL) to maintain reusable code segments.
-- and incorporate in scripts or SQL Procedures, Functions, and Triggers
-- Reference: http://ibm.biz/DB2fori_INCLUDE
--
--
-- description: Use the INCLUDE statement to pull
-- in and execute common pieces
-- of SQL
@forstie
forstie / Change trigger programs in production.sql
Last active March 9, 2022 01:14
This example shows how to use the ALLOW_DDL_CHANGES_WHILE_OPEN QAQQINI option within a specific job to avoid the requirement of needing an exlusive lock for the *FILE object, before making a change to a trigger program.
--
-- Create, Replace, Delete, or Disable trigger programs
-- without acquiring an exclusive lock over the file
-- =======
--
-- Resource: ibmsystemsmag.com/blogs/i-can/august-2017/manage-trigger-programs-in-productions
-- Prepare to break the rules
call qsys2.override_qaqqini(1, '', '');
-- Rules broken
@forstie
forstie / GO SAVE Option 21 history via SQL.sql
Last active October 6, 2023 11:42
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history.
--
-- ========================================================
-- iSee - GO SAVE Option 21 ( Save Entire system ) deetz
-- ========================================================
--
-- Author: Scott Forstie
-- Date : September, 2023
-- Email : forstie@us.ibm.com
--
-- Details for the previous GO SAVE Option 21 operations are found
@forstie
forstie / Retrieve details for active 5250 sessions.sql
Last active January 25, 2024 22:34
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
--
-- description: Find active Telnet or Interactive 5250 sessions
--
-- resource: ibm.biz/WellDefinedIBMiPorts
--
select remote_address, remote_port, authorization_name as user_name, job_name
from qsys2.netstat_job_info n
where local_port in (23,992) and job_type = 'INTERACTIVE';
--
@forstie
forstie / User profile ownership and basic authorities.sql
Last active November 5, 2019 12:53
The first query identifies those users who are lacking authority to use their own *USRPRF. This lack of authority can cause annoying failures in software products. The other queries are used to review whether the *USRPRF ownership implementation matches the strategy.
--
-- description: Which users lack basic authority to their own user profile?
--
select *
from qsys2.object_privileges
where object_type = '*USRPRF'
and object_name = authorization_name
and (object_operational <> 'YES'
or object_management <> 'YES'
@forstie
forstie / Dates and TIMESTAMP_FORMAT
Last active May 2, 2024 07:55
Formatting date data into true date and time date types
-- Author: Scott Forstie
-- Email: forstie@us.ibm.com
create or replace variable coolstuff.decdate dec(6,0);
set coolstuff.decdate = '190718';
-- July 18, 2019 (yes, really!)
values timestamp_format(varchar(coolstuff.decdate), 'YYMMDD');
-- Wow
-- Yowza
@forstie
forstie / Remove *IOSYSCFG from users and groups
Last active October 3, 2022 02:35
This example shows the power of the REGEXP_REPLACE built-in function, when combined with IBM i Services for security and dynamic SQL.
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date : July 28, 2019
--
-- Subtract '*IOSYSCFG from all users by producing the CHGUSRPRF command
-- necessary to get the job done
--
with iosyscfg_users (user_name) as (
select authorization_name
from qsys2.user_info
@forstie
forstie / JSON_TABLE and survival tips for shredding JSON with SQL
Last active May 2, 2024 07:55
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
-- 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;
@forstie
forstie / ROUND vs QUANTIZE
Created August 18, 2019 17:10
This example shows that QUANTIZE can be used along with decfloat rounding mode to achieve programmer control of rounding numeric values.
values current decfloat rounding mode;
set current decfloat rounding mode= round_half_even;
stop;
-- ===============================================================================================================
--
-- QUANTIZE uses the decfloat rounding mode.
-- This is the default: ROUND_HALF_EVEN
--
-- Round to nearest value; if values are equidistant, round so that the final digit is even.
-- If the discarded digits represent greater than half (0.5) of the value of a number in the next left position,
@forstie
forstie / dynamic VALUES INTO
Created August 29, 2019 16:02
This example shows how to use dynamic SQL (PREPARE and EXECUTE) to implement a VALUES INTO statement.
create procedure qgpl.values_into (out pout integer)
begin
declare values_into_stmt varchar(1000) ccsid 37;
set values_into_stmt = 'values 1+2+3 into ?';
prepare values_into_query from values_into_stmt;
execute values_into_query using pout;
end;
call qgpl.values_into(?);
-- Note that 6 is returned...