forstie’s gists

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 / 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 / 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 : [email protected]
	--
	-- Details for the previous GO SAVE Option 21 operations are found

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 / 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 / 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: [email protected]

	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 / 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 : [email protected]
--- 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 / JSON_TABLE and survival tips for shredding JSON with SQL

Last active August 12, 2024 08:06

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 / 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 / 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...

Scott Forstie forstie