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

forstie

-- ====================================================================
-- description: Flexible view that uses a multiple member physical file
-- author:      Scott Forstie
-- date:        March 10, 2020
--
-- Create a global variable that will be used to create the select statement
--
create or replace variable coolstuff.member_query_statement_text clob(1M) for sbcs data;

--

forstie

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

forstie

--
-- description: printf to the Joblog, using SQL
--
cl:addlible qsysinc;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(LPRINTF);
insert into qtemp.qcsrc values
 (1,010101,'{'),                                                  
 (2,010101,'extern int Qp0zLprintf (char *format, ...);'),      

forstie

--
-- Subject: Query Supervisor - Holding a job
-- Author: Scott Forstie
-- Date  : April, 2023
-- Features Used : This Gist uses qsys2.query_supervisor, qsys2.EXIT_PROGRAM_INFO, aliases, qsys2.qcmdexc, qsys2.ADD_QUERY_THRESHOLD,  qsys2.REMOVE_QUERY_THRESHOLD, qsys2.joblog_info, and QSYS2.ACTIVE_JOB_INFO
--
-- Notes:
-- ===============================================
-- 1) Query Supervisor (QS) exists in IBM i 7.3 and higher
--    (SF99703 Level 24, SF99704 Level 13, SF99950 <any>) 	

forstie

-- With this style of SQL, you can search the IFS by file name, or by creation date, or both!
-- In fact, it is simple to search by any criteria you'd like to use.

--
-- Find files within the IFS where:
--   --> The name starts with "P"
--   --> The file suffix is ".txt"
--   --> The file was created on January 10, 2022
--

forstie

-- 
-- When an IFS directory includes W (write), you are exposed to malware attacks
-- Use this to review and overcome this topic for the all important ROOT directory
--
-- For help on this or related security topics, contact Robert and team...
-- http://ibm.biz/IBMiSecurity
-- Robert Andrews - robert.andrews@us.ibm.com
--
stop;