V6R1 : SYS___STAT catalog

[K2r400]

With the V6R1 some very interesting view on the catalog appear : the SYS___STAT view.


****************
* SYSINDEXSTAT *
****************
You create indexes for SQL performance.
But are they really used by the two SQL engines?
An example on indexes not used for 200 days.

select system_index_schema, system_index_name, LAST_USED_TIMESTAMP  
from SYSINDEXSTAT where                                             
  days(current date) - 
days(ifnull(date(LAST_USED_TIMESTAMP), '01.01.2000')) > 200       
order by 1, 2

You can use the sYSPARTITIONIDEXSTAT view too.


****************
* SYSTABLESTAT *
****************

An example of a SQL statement which extracts files requiring a RGZPFM because they have more than a million rows and possess 10 % of deleted rows or have simply 80 % of deleted rows.
Display the hundrerd first ones only.

with t1 as (select                                    
 SYSTEM_TABLE_SCHEMA Schema, SYSTEM_TABLE_NAME table, 
dec(round( number_deleted_rows /                      
(cast((number_rows + number_deleted_rows)             
as decimal (22, 4))) * 100, 3), 6, 3) Pourcentage,    
dec(number_rows        , 11, 0)       Enregs,         
dec(number_deleted_rows, 11, 0)       Supprimes       
from SYSTABLESTAT t where number_deleted_rows > 0)    
select * from t1                                               
where (Enregs + Supprimes > 1000000 AND Pourcentage > 10)                  
 or (Pourcentage >= 80)                                       
Order by Enregs desc, Pourcentage desc                         
Fetch first 100 rows only

******************
* SYSPROGRAMSTAT *
******************
You use the adopted autorities for your security : USRPRF(*OWNER),
but it is necessary to think of it also for the dynamic SQL DYNUSRPRF(*OWNER).
Did not you forget it on your stored procedures, your SQL functions, or simply in your SQLRPGLE?

select * from SYSPROGRAMSTAT      
where not PROGRAM_TYPE = 'MODULE' 
and DYNAMIC_USER_PROFILE = '*USER'

*********************
SYSPARTITIONINDEXES *
*********************
Many of our logical files requiring to be recompiled because they could share access paths
with others logical files or indexes. Do I need to recompile some logical files ?

create table qtemp/Myfile as (
select tabschema, sys_tname Pfile, system_INDEX_NAME LFile,       
TABLE_PARTITION Member,                         
INDEX_TYPE type,                                                  
rank() over (partition by tabschema, sys_tname, TABLE_PARTITION   
             order by case INDEX_TYPE when 'INDEX   ' then 1 
                                      when 'LOGICAL ' then 0 end, 
             CREATE_TIMESTAMP) seq,    
CREATE_TIMESTAMP Date, NUMBER_KEY_COLUMNS Nb_Keys, COLUMN_NAMES Keys                          
 from SYSPindex s                                                  
Where INDEX_TYPE in ('INDEX', 'LOGICAL'))
With data




select distinct cast(T1.tabschema as char(15)) lib, t1.lfile
from qtemp/Myfile t1 join qtemp/Myfile t2                     
on  t1.Tabschema = t2.tabschema                             
and t1.Pfile = t2.pfile                                     
and t1.Member = t2.member                                   
and t1.seq < t2.seq                                         
and t1.nb_keys <= t2.nb_keys                                
and trim(t2.keys) like trim(t1.keys) concat '%'             
and not (t2.keys) = t1.keys