forstie
/ Retrieve details for active 5250 sessions.sql
Last active
January 25, 2024 22:34
Use SQL's NETSTAT and ACTIVE_JOB_INFO services to identify and explore active 5250 sessions.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ GO SAVE Option 21 history via SQL.sql
Last active
October 6, 2023 11:42
Use SQL to retrieve the QUSRSYS/QSRSAV21 *DTAARA and transform the detail therewithin into consumable history.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- ======================================================== | |
| -- iSee - GO SAVE Option 21 ( Save Entire system ) deetz | |
| -- ======================================================== | |
| -- | |
| -- Author: Scott Forstie | |
| -- Date : September, 2023 | |
| -- Email : [email protected] | |
| -- | |
| -- Details for the previous GO SAVE Option 21 operations are found |
forstie
/ Change trigger programs in production.sql
Last active
March 9, 2022 01:14
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ Reuseable SQL code segments with INCLUDE
Last active
November 5, 2019 12:56
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ Row permissions control for ZDA access.sql
Created
June 24, 2019 09:36
Row permissions control for ZDA access
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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
/ Query Spooled File contents for a specific user.sql
Last active
May 2, 2024 07:55
Query Spooled File contents for a specific user
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- | |
| -- 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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Purpose: Delay job for fractions of a second | |
| -- Author : Scott Forstie | |
| -- Contact: [email protected] | |
| -- 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
/ MTI related Index advice
Created
June 19, 2019 04:22
Maintained Temporary Index related index advice since the last IPL
forstie
/ Restoring libraries that begin with the letter E.sql
Created
June 15, 2019 05:58
Restoring libraries that begin with the letter E
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- ================================================= | |
| -- author: Scott Forstie | |
| -- date : May 29, 2019 | |
| -- email : [email protected] | |
| -- disclaimer - no implied warranties, yada yada | |
| -- ================================================= | |
| -- | |
| -- Super Fast retrieval of library and schema name |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- setup | |
| cl: STRTCPSVR SERVER(*SMTP) ; | |
| cl: ADDUSRSMTP USRPRF(SCOTTF); | |
| cl: ADDUSRSMTP USRPRF(TIMMR); | |
| -- Send SMTP E-mail Message (SNDSMTPEMM) | |
| cl:SNDSMTPEMM RCP(('[email protected]' *pri)) SUBJECT('hello world again') NOTE('this is a new note'); | |
| cl:SNDSMTPEMM RCP(('[email protected]' *pri)) SUBJECT('hello world again') NOTE('this is a new note'); | |
| select * from SQLISFUN00.may17objs; |