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 / User profile ownership and basic authorities.sql
Last active Jul 9, 2019
The first query identifies those users who are lacking authority to use their own *USRPRF. This lack of authority can cause annoying failures in software products. The other queries are used to review whether the *USRPRF ownership implementation matches the strategy.
View User profile ownership and basic authorities.sql
--
-- description: Which users lack basic authority to their own user profile?
--
select *
from qsys2.object_privileges
where object_type = '*USRPRF'
and object_name = authorization_name
and (object_operational <> 'YES'
or object_management <> 'YES'
@forstie
forstie / Revieve details for active 5250 sessions.sql
Last active Jun 30, 2019
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
View Revieve details for active 5250 sessions.sql
--
-- description: Find active Telnet or Interactive 5250 sessions
--
-- resource: ibm.biz/WellDefinedIBMiPorts
--
select remote_address, remote_port, authorization_name as user_name, job_name
from qsys2.netstat_job_info n
where local_port in (23,992) and job_type = 'INTERACTIVE';
--
@forstie
forstie / GO SAVE Option 21 history via SQL.sql
Created Jun 28, 2019
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history.
View GO SAVE Option 21 history via SQL.sql
-- Author: Scott Forstie
-- Date : June 28, 2019
-- Email : forstie@us.ibm.com
--
-- ==============
-- Basic Version
-- ==============
--
--
@forstie
forstie / Change trigger programs in production.sql
Created Jun 26, 2019
This example shows how to use the ALLOW_DDL_CHANGES_WHILE_OPEN QAQQINI option within a specific job to avoid the requirement of needing an exlusive lock for the *FILE object, before making a change to a trigger program.
View Change trigger programs in production.sql
--
-- Create, Replace, Delete, or Disable trigger programs
-- without acquiring an exclusive lock over the file
-- =======
--
-- Resource: ibmsystemsmag.com/blogs/i-can/august-2017/manage-trigger-programs-in-productions
-- Prepare to break the rules
call qsys2.override_qaqqini(1, '', '');
-- Rules broken
@forstie
forstie / Reuseable SQL code segments with INCLUDE
Last active Jun 26, 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
@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 / 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 / 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 / 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 / printf to the Joblog using SQL.sql
Created Jun 15, 2019
printf to the Joblog using SQL
View printf to the Joblog using SQL.sql
--
-- 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, ...);'),
You can’t perform that action at this time.