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
@forstie
forstie / SWAP_DYNUSRPRF.sql
Created Nov 24, 2019
With the latest Db2 PTF Groups for IBM i 7.3 and 7.4, you can now identify and fix those *PGM and *SRVPGM's that use SQL and were built incorrectly. This example finds those cases where *OWNER will be used for static SQL, but *USER will be used for dynamic SQL. The procedure swaps the dynamic user profile setting to *OWNER. This utility approach…
View SWAP_DYNUSRPRF.sql
--
-- 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
forstie / JSON_TABLE and survival tips for shredding JSON with SQL
Last active Nov 16, 2019
This example shows how to overcome what seems to be commonplace: JSON Web Services that return an invalid JSON document.
View 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;
@forstie
forstie / ALLOBJ users with default passwords.sql
Created Nov 16, 2019
Security implementations can and should be monitored closely and on a regular cadence. This is one example where SQL can be used instead of the Analyze Default Passwords (ANZDFTPWD) command.
View ALLOBJ users with default passwords.sql
--
-- Note, this example might take a while to run because its doing an exhaustive evaluation of which
-- users have *ALLOBJ special authority, either directly in their profile or indirectly via
-- group profile membership.
--
-- Next, those "super" users are evaluated to determine which (if any) of them have their password
-- set to match their user profile. (aka using a default password)
--
-- If this returns zero rows... good!
--
@forstie
forstie / Temporalize a library.sql
Created Nov 8, 2019
System period temporal tables were added as a feature built into Db2 for i with IBM i 7.3. This example shows how Temporal could be established for all database files within a specific library.
View Temporalize a library.sql
--
--
-- description: find database files and deploy Temporal over them
-- note: The history table will be named <existing-table-name>_HISTORY
-- note: Uncomment the LPRINTF's if you've built this procedure or have it from Db2 for i
-- minvrm: V7R3M0
--
CREATE OR REPLACE PROCEDURE coolstuff.deploy_temporal(target_library varchar(10))
BEGIN
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
You can’t perform that action at this time.