Scott Forstie forstie

## Numbify Packed Decimals using SQL
--
--
-- 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

## SQL Environmental Limits
-- 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;

## Glengarry Glen Ross - SQL Style
--
-- 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)
    );

## ROUND vs QUANTIZE
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,

## User profile ownership and basic authorities.sql

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

## Reuseable SQL code segments with INCLUDE
--
-- 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

## Row permissions control for ZDA access.sql
--
-- description: This row permission can be used to disallow specific jobnames from selecting
--              data over a specific file
--
cl:CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(SCOTTF) USAGE(*ALLOWED); -- repeat this on the target (not yet mirrored)

set schema star1g ;
set path  star1g ;

create table RowPermRules (

## Restoring libraries that begin with the letter E.sql

-- =================================================
-- author: Scott Forstie
-- date  : May 29, 2019
-- email : forstie@us.ibm.com
-- disclaimer - no implied warranties, yada yada
-- =================================================

--
-- Super Fast retrieval of library and schema name

## journal receivers attached or not.sql

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


## Display Software Resources
--
-- 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
	--
	--
	-- 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
	-- 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;
	--
	-- 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)
	);
	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,

	--
	-- 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'
	--
	-- 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
	--
	-- description: This row permission can be used to disallow specific jobnames from selecting
	-- data over a specific file
	--
	cl:CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(SCOTTF) USAGE(*ALLOWED); -- repeat this on the target (not yet mirrored)

	set schema star1g ;
	set path star1g ;

	create table RowPermRules (

	-- =================================================
	-- author: Scott Forstie
	-- date : May 29, 2019
	-- email : forstie@us.ibm.com
	-- disclaimer - no implied warranties, yada yada
	-- =================================================

	--
	-- Super Fast retrieval of library and schema name

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