forstie

--
-- 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

forstie

-- Author: Scott Forstie
-- Email:  forstie@us.ibm.com

create or replace variable coolstuff.decdate dec(6,0);
set coolstuff.decdate = '190718';
-- July 18, 2019 (yes, really!)
values timestamp_format(varchar(coolstuff.decdate), 'YYMMDD'); 

-- Wow
-- Yowza

forstie

-- 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

-- =================================================================
-- Author: Scott Forstie
-- Email : forstie@us.ibm.com
-- Date  : January 10, 2020
-- =================================================================

--
-- Setup:
-- 1) create and populate the spreadsheet generator table
-- 2) change the procedure source code:

forstie

--
-- Now that ACS is shipped on the IBM i via PTFs, we no longer need to manually move the
-- acsbundle.jar onto the IBM i.  Just apply the latest ACS PTFs and reference the jar
-- at: /QIBM/proddata/Access/ACS/Base/acsbundle.jar
--
-- Initial PTFs of ACS
-- V7R4M0 SI71900
-- V7R3M0 SI71934 
--

forstie

--
-- Subject: Extracting the IFS file name from a path, using regular expression built-in functions
-- Author: Scott Forstie  
-- Date  : September 27, 2021
-- Features Used : This Gist uses regexp_count, regexp_instr, substr, and SQL PL
--
-- Function - The request was, if you have an absolute path, how can SQL extract the filename from the path?
--            One approach is found below.
--
--

forstie

-- ========================================================================
--
-- Subject: Comparing two spooled files
-- Author: Scott Forstie  
-- Date  : December, 2021
--
-- IBM i Services used : OUTPUT_QUEUE_ENTRIES_BASIC, SYSTOOLS.SPOOLED_FILE_DATA
-- SQL language features used: CTEs, Exception joins, Union, and row_number()
-- ========================================================================

forstie

--
-- Subject: Audit Journal Management
-- Author: Scott Forstie
-- Date  : March, 2023
-- Features Used : This Gist uses qsys2.security_info, qsys2.journal_info, qsys2.object_statistics, qsys2.qcmdexc, CTEs, sysibmadm.env_sys_info, and SYSTOOLS.split
--
-- Notes:
-- ===============================================
-- 1) There are many configuration options to consider using when establishing the Audit Journal, this Gist uses SQL to examine some of the most important choices.
-- 2) Its important to have a retention strategy for audit journal - journal receivers

forstie

--
-- Subject: The request... return SQL services detail using JSON.
-- Author: Scott Forstie  
-- Date  : April, 2023
-- Features Used : This Gist uses qsys2.syscolumns2, listagg(), rtrim(), dynamic SQL, SQL PL, PIPE
--
-- Note: 
--       When someone asks you to return Db2 for i data "as JSON", they probably want you
--       to publish a JSON document, which contains good key names and of course, the data.
--

forstie

--
-- Subject: Stay Current
-- Author: Scott Forstie  
-- Date  : April, 2024
-- Features Used : This Gist uses SQL PL, systools.group_ptf_currency, sysibmadm.env_sys_info, SYSTOOLS.GENERATE_SPREADSHEET, SYSTOOLS.SEND_EMAIL
--
-- The age old problem is this... IBM recommends that IBM i clients stay up to date 
-- on software updates... but how does a client get current and stay current?
--
-- This confluence of technologies highlights one approach to consider.