richardschoen

/*************************************************************************************************/
/* The goal of this SQL script is to demonstrate various features of SQL PL. This will include   */
/* loops, error handling, parameters, cursor management, returning of result sets, and more.     */
/* The topic of this script will revolve around the management (deletion) of journal receivers.  */
/*                                                                                               */
/* Created for Common POWERUp 2024                                                               */
/* Session: Getting Started with SQL Functions and Procedures                                    */
/* Author: Ryan Moeller ([email protected])                                                       */
/* Date: May 2024                                                                                */
/*************************************************************************************************/           

richardschoen

/*************************************************************/
/* Use system limits to find users abusing IFS space         */
/*************************************************************/

-- Look at all users who have triggered system limits in the last 24 hours...
SELECT *
    FROM QSYS2.SYSLIMITS
    WHERE LAST_CHANGE_TIMESTAMP > CURRENT TIMESTAMP - 1 DAY
    AND USER_NAME != 'QSYS'
    ORDER BY LAST_CHANGE_TIMESTAMP DESC;

richardschoen

--
-- Find database tables that contain deleted rows, return the largest potential storage savings first.
--
WITH DELETED_ROWS_DATA AS (
        SELECT TABLE_SCHEMA,
               TABLE_NAME,
               NUMBER_DELETED_ROWS,
               NUMBER_ROWS,
               DECIMAL(NUMBER_DELETED_ROWS, 18, 3) * 100 / (NUMBER_DELETED_ROWS + NUMBER_ROWS) AS PERCENT_DELETED,
               CASE

richardschoen

-- Create schema to hold function
CREATE SCHEMA COOLSTUFF;
    
CREATE OR REPLACE PROCEDURE COOLSTUFF.AGGREGATE_REMOTE_SQL_SERVICE_DATA (
                IN REMOTE_SYSTEMS VARCHAR(2000) CCSID 37 DEFAULT '', -- List of systems to query. Each system must have a corresponding RDB directory entry.
                IN SYSTEM_LIST_TABLE_SCHEMA VARCHAR(100) CCSID 37 DEFAULT '', -- The schema name of a table containing a list of systems to query.
                IN SYSTEM_LIST_TABLE_NAME VARCHAR(100) CCSID 37 DEFAULT '', -- The long (SQL) name of a table containing a list of systems to query.
                IN SQLSERVICE_SCHEMA CHAR(10) CCSID 37, -- The database schema of the desired SQL service (typically QSYS2 or SYSTOOLS).
                IN SQLSERVICE_NAME VARCHAR(100) CCSID 37, -- The long name (not system name) of the desired SQL service.
                IN SELECT_LIST VARCHAR(2000) CCSID 37 DEFAULT '*', -- Customizable SELECT list. Selecting all fields may not work if target systems have different PTF levels o

richardschoen

-- Purpose: Running QSH command using SQL
-- Version: 1.0
-- Date 08/02/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-qcmdexc-procedure
--       https://www.ibm.com/docs/en/i/7.6.0?topic=services-environment-variable-info-view

CREATE OR REPLACE FUNCTION SQLTOOLS.RUN_QSHELL (
            INCMD VARCHAR(10000)
    )

richardschoen

Beast Mode

Beast Mode is a custom chat mode for VS Code agent that adds an opinionated workflow to the agent, including use of a todo list, extensive internet research capabilities, planning, tool usage instructions and more. Designed to be used with 4.1, although it will work with any model.

Below you will find the Beast Mode prompt in various versions - starting with the most recent - 3.1

Installation Instructions

• Go to the "agent" dropdown in VS Code chat sidebar and select "Configure Modes".
• Select "Create new custom chat mode file"

richardschoen

ctl-opt nomain;                                                      
ctl-opt Option(*Srcstmt:*Nodebugio:*NoUnRef)                        
        Debug( *Yes )  Bnddir('QUSAPIBD');                          
 ********************************************************************** 
 ********************************************************************** 
 * Program Id: BL0100M01
 * This program gets the latitude and longitude from the data     
 * queues from java program JsonDataQueues and sends commands
 * to ping and shutdown the java app.
 ********************************************************************** 

richardschoen

// Based: Croy, Steve (2005) Display-attributes-made-simple/setcolor [Source code]. http://search400.techtarget.com/tip/Display-attributes-made-simple


// --------------------------------------------------
// Procedure name: RtnDspAtr
// Purpose:        Return Display Attribute hex code that can be used on a
//                  screen to set color, underline, or other attributes.
// Returns:        DSPATR hex value
// Parameter:      Color Value - Text representing a color 'GRN' (default)
//                   other values: 'BLU' 'PNK' 'RED' 'TRQ' 'WHT' 'YLW'

richardschoen

Run "services.msc" and find "Windows Search" then stop the service. 
Delete the Windows.edb file. (C:\ProgramData\Microsoft\Search\Data\Applications\Windows\Windows.edb)
Click "Modify" in the Indexing Options panel and click "Delete and Rebuild" for the index.

richardschoen

Building ibm_db2 on IBM i

Setup

We need to install various packages in order to build ibm_db2. Most of these can be installed with yum:

yum group install 'Development tools'
yum install git