Skip to content

Instantly share code, notes, and snippets.

Scott Forstie forstie

Block or report user

Report or block forstie

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View Authority Collection.sql
--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
forstie / remove noise from text strings using TRANSLATE.sql
Created Jun 15, 2019
remove noise from text strings using TRANSLATE
View remove noise from text strings using TRANSLATE.sql
--
-- 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
forstie / Publishing file contents using JSON and SQL.sql
Created Jun 15, 2019
Publishing file contents using JSON and SQL
View Publishing file contents using JSON and SQL.sql
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,
View Sending an E-mail via SQL.sql
-- 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
forstie / Restoring libraries that begin with the letter E.sql
Created Jun 15, 2019
Restoring libraries that begin with the letter E
View 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
@forstie
forstie / MTI related Index advice
Created Jun 19, 2019
Maintained Temporary Index related index advice since the last IPL
View MTI related Index advice
@forstie
forstie / Microsecond DLYJOB
Created Jun 20, 2019
Microsecond DLYJOB via SQL
View Microsecond DLYJOB
-- 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
forstie / Query Spooled File contents for a specific user.sql
Created Jun 21, 2019
Query Spooled File contents for a specific user
View Query Spooled File contents for a specific user.sql
--
-- 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 contrents of RUNSQLSTM spooled files for the current user
@forstie
forstie / Row permissions control for ZDA access.sql
Created Jun 24, 2019
Row permissions control for ZDA access
View 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 (
@forstie
forstie / Reuseable SQL code segments with INCLUDE
Last active Nov 5, 2019
Locate reusable pieces of SQL code into the IFS. Then, use INCLUDE to pull those segments into your SQL routines or triggers, and even ACS's Run SQL Scripts or RUNSQLSTM.
View 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
You can’t perform that action at this time.