Scott Forstie forstie

## ZDA mystery solved
-- =============================================================
-- Author: Scott Forstie
-- Date  : September 8, 2019
-- 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.
--
-- Note: The source and setp steps appear below

## dynamic VALUES INTO
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...

## 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,

## JSON_TABLE and survival tips for shredding JSON with SQL
-- 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;

## Remove *IOSYSCFG from users and groups
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- 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

## Dates and TIMESTAMP_FORMAT
-- Author: Scott Forstie
-- Email:  forstie@us.ibm.com

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

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

## Revieve details for active 5250 sessions.sql
--
-- 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';

--

## GO SAVE Option 21 history via SQL.sql
-- Author: Scott Forstie
-- Date  : June 28, 2019
-- Email : forstie@us.ibm.com

--
-- ==============
-- Basic Version
-- ==============
--
--

## Change trigger programs in production.sql
--
-- 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