RainerRoss’s gists

forstie / Bringing order to the IFS.sql

Last active June 14, 2024 20:38

This example picks on the IFS stream files found within and under the /tmp directory. How much gunk has been accumulated under /tmp and what can you do to manage it? A bit of SQL to the rescue. The IFS_OBJECT_STATISTICS() UDTF returns many elements of data for you to leverage for improved management of the IFS.

	--
	-- Reference material: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfifsobjstat.htm
	--

	--
	-- How much space is used by stream files from /tmp (and subdirs) that haven't been used in 6 months
	--
	select varchar_format(sum(data_size),'999G999G999G999G999G999G999') tmp_size
	from table (
	qsys2.ifs_object_statistics(start_path_name => '/tmp',

forstie / calculating an ALLOCATE value for a column.sql

Created March 3, 2020 20:11

Database Engineers sometimes need to identify data models with varying length columns, where the allocate clause could be improved. When you use the ALLOCATE(n) clause, you're telling the database to establish n number of bytes for the column in the fixed portion of th record. If the column value for a row has a length > n, the database uses the…

	--
	-- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule
	-- (80% of the time, the length of the column data would be less than or equal to n)
	-- minvrm: V7R3M0
	--
	create or replace function systools.compute_allocate (
	p_schema_name varchar(128) for sbcs data,
	p_table_name varchar(128) for sbcs data,
	p_column_name varchar(128) for sbcs data,
	allocate_percentage decimal(3,2)

forstie / journal receivers attached or not.sql

Created October 23, 2019 14:18

I was asked to provide a way to find journal receivers and discern whether or not they are attached or detached.


	--
	-- 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 / Counting objects in a library.sql

Created October 22, 2019 21:49

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.

	--
	-- description: Count objects in a library (superfast)
	-- minvrm: v7r4m0
	--
	with libs (ln) as (
	select objname
	from table (
	qsys2.object_statistics('*ALLSIMPLE', 'LIB')
	)
	)

forstie / SQL generated table comparison query

Last active April 16, 2021 19:00

SQL, LISTAGG(), QSYS2.SYSCOLUMNS2, and IS NOT DISTINCT all team up here to generate a table level comparision query.

	--
	-- Imagine that you have 2 versions of the same table.
	-- The tables have the same format.
	-- This example provides an SQL function that generates a table compare query.
	--
	create schema coolstuff;
	create table coolstuff.table_master as (select * from qsys2.syslimtbl limit 100) with data;
	create table coolstuff.table_secondary as (select * from qsys2.syslimtbl limit 100) with data;
	select * from coolstuff.table_secondary;
	update coolstuff.table_secondary set limit_category = 555 limit 10;

forstie / Numbify Packed Decimals using SQL

Last active November 5, 2019 12:39

SQL scalar functions can transform data into information.

	--
	--
	-- Description: Convert packed decimal numbers into decimals
	--
	--
	cl:addlible qsysinc;
	cl:clrlib qtemp;
	cl:crtsrcpf qtemp/qcsrc;
	cl:addpfm file(qtemp/qcsrc) mbr(NIB);
	insert into qtemp.qcsrc values

forstie / Mask birthdays with RCAC

Created October 14, 2019 20:04

This example shows how to use a Column Mask to prevent users from seeing the actual birthdate.

	--
	-- 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 / Optimize journaled database files

Created October 12, 2019 22:24

Subtle options in how objects are journaled can have a big impact on performance.

	--
	--
	-- Find journaled database files that can be adjusted for improved performance
	--
	-- Resources:
	-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm
	-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf
	--
	select 'TOYSTORE', objname as file, omit_journal_entry, journal_images, objtype, objowner,
	objdefiner, objcreated, objsize, objtext, objlongname, last_used_timestamp, journaled,

forstie / Optimize local journals

Created October 12, 2019 21:29

	--
	-- Search for journals that can be easily improved
	--
	-- Resources:
	-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqviewjournalinfo.htm
	-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf
	--
	select journal_library, journal_name, receiver_maximum_size, remove_internal_entries, asp_number,
	journal_aspgrp, attached_journal_receiver_name, attached_journal_receiver_library,
	message_queue, message_queue_library, delete_receiver_option, delete_receiver_delay,

forstie / SQL Environmental Limits

Created October 12, 2019 19:37

Review the top consumers of SQL resources since the last IPL.

	-- Resource:
	-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqhealthenvlimits.htm
	--

	DECLARE GLOBAL TEMPORARY TABLE Health_Environmental_Limits
	LIKE QSYS2.QSQHENVLIM
	WITH REPLACE
	NOT LOGGED ON ROLLBACK PRESERVE ROWS
	ON COMMIT PRESERVE ROWS;

Rainer Ross RainerRoss