forstie

--Authority Collection - https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzarl/rzarlautcolstart.htm

--https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/strautcol.htm
STRAUTCOL USRPRF(USER1) LIBINF(*ALL) OBJ(*ALL) OBJTYPE(*ALL)  OMITLIB((MYLIB1))

--https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/cl/endautcol.htm
ENDAUTCOL   USER(USER1)

--View Data collected
SELECT * FROM QSYS2.AUTHORITY_COLLECTION 

forstie

--
-- Lets say I have this character data, how do I extract the numeric?
--
-- Q: Library  . . . . . . :   SYSIBM          Number of objects  . :   65
-- A: Use the TRANSLATE built-in function to convert all the noise characters to blanks

values bigint(
    translate(
      'Library  . . . . . . :   SYSIBM          Number of objects  . :   65', 
      ' ',

forstie

stop;

-- Publish the data within a table using SQL
with json_rows (j) as (
    select json_object(
        key 'EMPNO' value empno, key 'FIRSTNME' value firstnme,
        key 'MIDINIT' value midinit, key 'LASTNAME' value lastname,
        key 'WORKDEPT' value workdept, key 'PHONENO' value phoneno,
        key 'HIREDATE' value hiredate, key 'JOB' value job,

forstie

-- setup 
cl: STRTCPSVR SERVER(*SMTP)  ;
cl: ADDUSRSMTP USRPRF(SCOTTF);
cl: ADDUSRSMTP USRPRF(TIMMR);

-- Send SMTP E-mail Message (SNDSMTPEMM)
cl:SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('hello world again') NOTE('this is a new note');
cl:SNDSMTPEMM RCP(('forstie@us.ibm.com' *pri)) SUBJECT('hello world again') NOTE('this is a new note');

select * from SQLISFUN00.may17objs;

forstie

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

forstie

--
-- Examine the condensed index advice where Maintained Temporary Indexes (MTI)
-- have been used since the last IPL
--
with last_ipl (ipl_time) as (
    select job_entered_system_time
      from table (
          qsys2.job_info(
            job_status_filter => '*ACTIVE',
            job_user_filter => 'QSYS')

forstie

-- Purpose: Delay job for fractions of a second
-- Author : Scott Forstie
-- Contact: forstie@us.ibm.com
-- Date   : June 20, 2019

cl: addlible QSYSINC;
cl: crtsrcpf qtemp/qcsrc;
cl: addpfm file(qtemp/qcsrc) mbr(usleep);
--
-- The usleep() function suspends a thread for the number of microseconds specified by the of useconds parameter.

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

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

forstie

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