forstie

--
-- Reference material: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfifsobjstat.htm
--

--
-- How much space is used by stream files from /tmp (and subdirs) that haven't been used in 6 months
-- 
select varchar_format(sum(data_size),'999G999G999G999G999G999G999') tmp_size
  from table (
    qsys2.ifs_object_statistics(start_path_name => '/tmp', 

forstie

--
-- description: Compute the ALLOCATE(n) value, based upon the 80/20 rule
--              (80% of the time, the length of the column data would be less than or equal to n)
-- minvrm: V7R3M0
--
create or replace function systools.compute_allocate (
      p_schema_name varchar(128) for sbcs data, 
      p_table_name  varchar(128) for sbcs data, 
      p_column_name  varchar(128) for sbcs data, 
      allocate_percentage decimal(3,2) 

forstie

--
-- description: Which journal receivers are currently attached?
--
select attached_journal_receiver_library, attached_journal_receiver_name
  from qsys2.journal_info
  where journal_library = 'PRODLIB'
  order by 1,2;
  

forstie

--
-- description: Count objects in a library (superfast)
-- minvrm: v7r4m0
--
with libs (ln) as (
    select objname
      from table (
          qsys2.object_statistics('*ALLSIMPLE', 'LIB')
        )
  )

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

--
-- RCAC Book: www.redbooks.ibm.com/redpieces/abstracts/redp5110.html
--

--
-- Who is allowed to deploy COLUMN MASKs and ROW PERMISSIONs
--
cl:CHGFCNUSG FCNID(QIBM_DB_SECADM) USER(SCOTTF) USAGE(*ALLOWED); 

call qsys.create_sql_sample('COFFEEBEAN');

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;