RainerRoss’s gists

forstie / Display Software Resources

Created September 28, 2019 14:47

The Display Software Resources (DSPSFWRSC) command allows you to show, print, or write to an output file the list of installed software resources. This SQL example shows how to externalize the same detail by extracting message text and transforming numerics into integer form.

	--
	-- category: Software Resources
	-- description: DSPSFWRSC for SQL users
	--
	create or replace function coolstuff.whatsinstalled ()
	returns table (
	product varchar(7) ccsid 37, load integer, option integer,
	software_text varchar(132) ccsid 37
	)
	external action

forstie / Glengarry Glen Ross - SQL Style

Created September 24, 2019 01:37

Ok movie buffs, here is a fictional before and after, with a happy ending. Use these techniques to bring database engineering to bear in your data center.

	--
	-- Existing file... not very user friendly
	--
	create schema gggr;
	create table gggr.sales (
	CL varchar(100),
	PTY varchar(100),
	SP varchar(30),
	PG char(1)
	);

forstie / 3-part naming for UDTFs

Created September 18, 2019 23:57

For User Defined Table Functions (UDTFs), a trick needs to be employed. Include a where clause whose only purpose is to push the UDTF invocation to the remote database!

	--
	-- Traditional 3-part name SQL would reference a table, view, or procedure
	--
	call otherRDB.schema.procedure123();
	stop;

	insert into localschema.fact_table
	select * from otherRDB.remoteschema.fact_table;
	stop;

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.

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

Rainer Ross RainerRoss