Scott Forstie forstie

## Use ACS on your IBM i to build spreadsheets.sql
--
-- 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
--

## Generating spreadsheets with SQL.sql
-- =================================================================
-- 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:

## SWAP_DYNUSRPRF.sql
--
-- description: Which SQL programs or services have a mismatch between user profile and dynamic user profile (full)
--
select user_profile, dynamic_user_profile, program_schema, program_name, program_type,
       module_name, program_owner, program_creator, creation_timestamp, default_schema,
       "ISOLATION", concurrentaccessresolution, number_statements, program_used_size,
       number_compressions, statement_contention_count, original_source_file,
       original_source_file_ccsid, routine_type, routine_body, function_origin,
       function_type, number_external_routines, extended_indicator, c_nul_required,
       naming, target_release, earliest_possible_release, rdb, consistency_token,

## ALLOBJ users with default passwords.sql
--
-- Note, this example might take a while to run because its doing an exhaustive evaluation of which
-- users have *ALLOBJ special authority, either directly in their profile or indirectly via
-- group profile membership.
--
-- Next, those "super" users are evaluated to determine which (if any) of them have their password
-- set to match their user profile. (aka using a default password)
--
-- If this returns zero rows... good!
--

## Temporalize a library.sql
--
--
-- 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

## journal receivers attached or not.sql

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


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

## SQL generated table comparison query
--
-- 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;

## Numbify Packed Decimals using SQL
--
--
-- 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

## Mask birthdays with RCAC
--
-- 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');