forstie

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

NielsLiisberg

begin
    declare cmd varchar(4096);
    declare comma varchar(1) default ''; 
    call qusrsys.ifs_write('/tmp/test.json'  , '[');  
    for vl as c1 cursor for select cusnum , lstnam from qiws.qcustcdt a do
        call qusrsys.ifs_append('/tmp/test.json'  , 
            comma concat  
            json_object (
                'customerNumber' : CUSNUM,
                'name' : LSTNAM

forstie

--
-- Imagine that you have 2 versions of the same table.
-- The tables have the same format.
-- This example provides an SQL function that generates a table compare query.
-- 
create schema coolstuff;
create table coolstuff.table_master as (select * from qsys2.syslimtbl limit 100) with data;
create table coolstuff.table_secondary as (select * from qsys2.syslimtbl limit 100) with data;
select * from coolstuff.table_secondary;
update coolstuff.table_secondary set limit_category = 555 limit 10;

forstie

--
--
-- Description: Convert packed decimal numbers into decimals
--
--
cl:addlible qsysinc;
cl:clrlib qtemp;
cl:crtsrcpf qtemp/qcsrc;
cl:addpfm file(qtemp/qcsrc) mbr(NIB);
insert into qtemp.qcsrc values

forstie

--
--
-- Find journaled database files that can be adjusted for improved performance
--
-- Resources:
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqudfobjectstat.htm
-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf
--
select 'TOYSTORE', objname as file, omit_journal_entry, journal_images, objtype, objowner,
       objdefiner, objcreated, objsize, objtext, objlongname, last_used_timestamp, journaled,

forstie

--
-- Search for journals that can be easily improved
--
-- Resources:
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqviewjournalinfo.htm
-- http://www.redbooks.ibm.com/redbooks/pdfs/sg246286.pdf 
--
select journal_library, journal_name, receiver_maximum_size, remove_internal_entries, asp_number,
       journal_aspgrp, attached_journal_receiver_name, attached_journal_receiver_library,
       message_queue, message_queue_library, delete_receiver_option, delete_receiver_delay,

forstie

-- Resource: 
-- https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzajq/rzajqhealthenvlimits.htm
--

DECLARE GLOBAL TEMPORARY TABLE Health_Environmental_Limits
     LIKE QSYS2.QSQHENVLIM
     WITH REPLACE
     NOT LOGGED ON ROLLBACK PRESERVE ROWS
     ON COMMIT PRESERVE ROWS;

forstie

--
-- category: Software Resources
-- description: DSPSFWRSC for SQL users
--
create or replace function coolstuff.whatsinstalled ()
  returns table (
    product varchar(7) ccsid 37, load integer, option integer,
    software_text varchar(132) ccsid 37
  )
  external action

forstie

-- =============================================================
-- Author: Scott Forstie
-- Date  : September 8, 2019
-- Revised: August 28, 2020
--
-- Description: Have you ever wondered what's driving all
--              those QZDASxINIT jobs? 
--              This example shows how to establish an 
--              exit program to capture client special register
--              and other detail for ZDA connections.

forstie

create procedure qgpl.values_into (out pout integer)
begin
  declare values_into_stmt varchar(1000) ccsid 37;
  set values_into_stmt = 'values 1+2+3 into ?';
  prepare values_into_query from values_into_stmt;
  execute values_into_query using pout;
end;

call qgpl.values_into(?);
-- Note that 6 is returned...