forstie

--
-- =======================================================================
-- Subject: See Collection Services (CS) config and query CS data with SQL
-- =======================================================================
--
-- Author: Scott Forstie
-- Date  : September, 2023
-- Email : forstie@us.ibm.com
--
-- This Gist is a two-fer:

forstie

-- Subject: DBE in a box
-- Author: Scott Forstie  
-- Date  : August, 2023
-- Features Used : This Gist uses SQL PL,  qsys2.SYSTEM_STATUS_INFO_basic, systools.lprintf, qsys2.sysixadv, qsys2.sysindexes, QSYS2.SYSINDEXSTAT, SYSTOOLS.ACT_ON_INDEX_ADVICE, QSYS2.RESET_TABLE_INDEX_STATISTICS and SYSTOOLS.REMOVE_INDEXES
--
-- Note: 
-- 1) Indexes are not free, and care should be taken when deciding whether an index is worth creating
-- 2) Indexes are not free, and care should be taken to understand whether adequate system resources are available before creating additional indexes
-- 3) Indexes are not free, and care should be taken to establish a well trained Database Engineer (DBE) responsible for your IBM i partitions
--

forstie

-- Subject: PTF Cover Letters
-- Author: Scott Forstie  
-- Date  : April, 2023
-- Features Used : This Gist uses qsys2.ptf_info, SQL Alias, ltrim, rtrim(), dynamic SQL, SQL PL, PIPE
--
-- Note: 
-- 1) Whether PTF Cover Letters exist on your IBM i is up to whomever ordered the PTFs
--    Use the Copy PTF Cover Letter (CPYPTFCVR) command to bring in PTF cover letters
--    https://www.ibm.com/docs/en/i/7.5?topic=ssw_ibm_i_75/cl/cpyptfcvr.html
-- 2) The file QGPL/QAPZCOVER *FILE contains one member for each PTF Cover Letter

forstie

--
-- description: What spooled files does the current user own?
--
select job_name, spooled_file_name, file_number, user_data,
       create_timestamp
  from qsys2.output_queue_entries_basic
  where user_name = user;
stop;
--
-- description: Query the contents of RUNSQLSTM spooled files for the current user

forstie

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

--
-- If you're not ready to use IFS_OBJECT_PRIVILEGES, this might help you...
-- Note: A special thanks to Sue Romano for her help with this Gist
--

-- https://www.ibm.com/docs/en/i/7.4?topic=services-ifs-object-privileges-table-function
cl:PRTPVTAUT OBJTYPE(*STMF) DIR('/') SCHSUBDIR(*YES);

--
-- create an alias over the most recent execution of PRTPRVAUT

forstie

-- =========================================================================================
-- 
-- Adopted authority... if you use it, how do you control it?
--
-- =========================================================================================
-- Q: How can you avoid propagating authority to code you need to call?
--
-- A: MODIFY INVOCATION AUTHORITY ATTRIBUTES
--    https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzatk/MODINVAU.htm
-- =========================================================================================

forstie

-- Subject: Using initAuto to find uninitialized variable problems in ILE code
-- Author: Scott Forstie  
-- Date  : June, 2023
-- Features Used : This Gist uses SQL PL, INCLUDE, Change Program (CHGPGM) CL command, qsys2.joblog_info, qsys2.program_info, qsys2.qcmdexc, and QSYS2.BOUND_MODULE_INFO
--
-- A) Programmers use declared variables and structures to facilitate program logic.
-- B) Programmers should always initialize or assign declared variables and structures to contain known and expected values.
-- C) Programmers sometime miss step B.
--
-- This Gist shows how the IBM i Optimizing Translator can help.

forstie

--
-- description: Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select user_profile, dynamic_user_profile, program_schema, program_name, program_type,
       module_name, program_owner, program_creator, creation_timestamp, default_schema,
       "ISOLATION", concurrentaccessresolution, number_statements, program_used_size,
       number_compressions, statement_contention_count, original_source_file,
       original_source_file_ccsid, routine_type, routine_body, function_origin,
       function_type, number_external_routines, extended_indicator, c_nul_required,
       naming, target_release, earliest_possible_release, rdb, consistency_token,

forstie

--  category:  IBM i Services
--  description:  Librarian -  Library Info 
--  minvrm: V7R3M0
--

create or replace variable coolstuff.library_report_stmt varchar(10000) for sbcs data default
'create or replace table coolstuff.library_sizes
      (library_name, schema_name, 
      
       -- qsys2.library_info() columns