RainerRoss’s gists

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 September 23, 2025 11:22

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

forstie / ZDA mystery solved

Last active August 18, 2023 17:37

This example shows several things worthy of attention. System managers can utilize exit program to establish improved auditing, understanding, and real time business rules using SQL. For QZDASOINIT jobs, it can be easily considered an unsolvable mystery. With the help of Db2 for i Client Special Registers, we can understand a great deal about ZD…

	-- =============================================================
	-- Author: Scott Forstie
	-- Date : September 8, 2019
	-- Revised: August 28, 2020
	--
	-- Description: Have you ever wondered what's driving all
	-- those QZDASxINIT jobs?
	-- This example shows how to establish an
	-- exit program to capture client special register
	-- and other detail for ZDA connections.

Rainer Ross RainerRoss