-- ===============================
-- Omni user group - January, 2022
-- ===============================
--
-- ===============================
-- Author: Scott Forstie
-- ===============================
--
-- ===============================
-- Db2 for i SQL Tutor: 
-- https://ibm.biz/Db2foriSQLTutor
-- https://www.ibm.com/support/pages/ibm-i-tutorials-demos-and-sql-examples-0
--
-- ===============================
-- Forstie Gists:
-- https://gist.github.com/forstie
--
-- ===============================
-- iSee Video blog series:
-- https://learn.common.org/products/ibm-isee-video-blog#tab-product_tab_overview
--
--
-- Contrast, show related, show indexes and related_objects
--

select SOURCE_SCHEMA_NAME, /* VARCHAR(128)        */
       SOURCE_SQL_NAME, /* VARCHAR(128)        */
       SQL_OBJECT_TYPE, /* VARCHAR(24)         */
       SCHEMA_NAME, /* VARCHAR(128)        */
       SQL_NAME, /* VARCHAR(128)        */
       LIBRARY_NAME, /* VARCHAR(10)         */
       SYSTEM_NAME, /* VARCHAR(279)        */
       OBJECT_OWNER, /* VARCHAR(10)         */
       LONG_COMMENT, /* VARGRAPHIC(2000)    */
       OBJECT_TEXT, /* VARGRAPHIC(50)      */
       LAST_ALTERED /* TIMESTAMP(6)        */
  from table (
      SYSTOOLS.RELATED_OBJECTS(
        LIBRARY_NAME => 'STORE42', /* VARCHAR(10)  name - Default: none */
        FILE_NAME => 'TAB1' /* VARCHAR(10)  name - Default: none */
      )
    );
stop;


SELECT
  VALIDATE_TIME,           /* TIMESTAMP(6)     */
  LIBRARY_NAME,            /* VARCHAR(10)      */
  FILE_NAME,               /* VARCHAR(10)      */
  MEMBER_NAME,             /* VARCHAR(10)      */
  RELATIVE_RECORD_NUMBER,  /* BIGINT           */
  SQL_WARNING,             /* INTEGER          */
  REASON_CODE,             /* INTEGER          */
  COLUMN_NAME,             /* VARCHAR(128)     */
  WARNING_TEXT             /* VARCHAR(1000)    */
FROM TABLE(SYSTOOLS.VALIDATE_DATA_FILE(
  LIBRARY_NAME => 'STORE42',  /* VARCHAR(10)    */
  FILE_NAME    => 'BADDATA'   /* VARCHAR(10)    */
)
);
stop;

create or replace alias qtemp.insearchof for store42.baddata(firstmbr);
select * from qtemp.insearchof v where rrn(v) = 189; 
stop;

select * from qsys2.syspartitionstat where table_schema = 'QMLHC' order by overflow desc;
stop;

select CONTAINS_VARYING_LENGTH
  from qsys2.sysfiles
  where table_schema = 'QMLHC' and
        table_name = 'APPTRAIN'
;
stop;

select  COLUMN_NAME, DATA_TYPE, LENGTH, "ALLOCATE", inline_length
  from qsys2.syscolumns2
  where table_schema = 'QMLHC' and
        table_name = 'APPTRAIN'
; 
stop;

SELECT
  IDEAL_ALLOCATE_LENGTH,                                   
  PERCENTAGE_OF_ROWS_THAT_FIT_INTO_THE_ALLOCATED_LENGTH    
FROM TABLE(SYSTOOLS.COMPUTE_ALLOCATE(
  P_SCHEMA_NAME       => 'QMLHC',   
  P_TABLE_NAME        => 'APPTRAIN',  
  P_COLUMN_NAME       => 'NAME_CONTRACT_TYPE',  
  ALLOCATE_PERCENTAGE => 0.80   
)
);
stop;
select  COLUMN_NAME, DATA_TYPE, LENGTH, "ALLOCATE", inline_length
  from qsys2.syscolumns2
  where table_schema = 'QSYS2' and
        table_name = 'SYSIXADV'
; 
stop;
SELECT
  IDEAL_ALLOCATE_LENGTH,                                   
  PERCENTAGE_OF_ROWS_THAT_FIT_INTO_THE_ALLOCATED_LENGTH    
FROM TABLE(SYSTOOLS.COMPUTE_ALLOCATE(
  P_SCHEMA_NAME       => 'QSYS2',   
  P_TABLE_NAME        => 'SYSIXADV',  
  P_COLUMN_NAME       => 'KEYSADV',  
  ALLOCATE_PERCENTAGE => 0.80   
));
stop;

--
-- category: Size it
-- description: Show table size along with the size of all indexes
--
with t(data_size_gig, raw_size) as (
  select sum(data_size) / (1024 * 1024 * 1024), sum(data_size) FROM qsys2.syspartitionstat t
    where table_name   = 'ITEM_FACT' and
          table_schema = 'STARI1A'
),
     i(index_size_gig, raw_size) as (
  select sum(index_size) / (1024 * 1024 * 1024), sum(index_size) FROM qsys2.syspartitionindexstat t
    where table_name   = 'ITEM_FACT' and
          table_schema = 'STARI1A'
)
select t.raw_size as table_size, i.raw_size size_of_all_indexes, data_size_gig, index_size_gig,
       data_size_gig + index_size_gig as total_gig
  from t,i;