DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 LUW  

Home
 


Automatische Statistikerfassung


db2 update db cfg using  
auto_runstats ON
db2 update db cfg using   auto_stats_prof ON
db2 update db cfg using  auto_prof_upd ON
db2 update db cfg using  auto_stmt_stats ON


danach mit db2cc bis V9 oder Datastudio 3.1 oder ... Stats konfigurieren !

Das k├Ânnte dabei raus kommen !

CONNECT TO <DATABASE>;

DROP TABLE AUTO.AUTO_RUNSTATS_CTL ;
CREATE TABLE AUTO.AUTO_RUNSTATS_CTL(
       TABSCHEMA VARCHAR(128)
     , TABNAME VARCHAR(128)
    )
;

grant select on TABLE AUTO.AUTO_RUNSTATS_CTL to public
;

CALL SYSPROC.AUTOMAINT_SET_POLICY ('MAINTENANCE_WINDOW', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2MaintenanceWindows xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><OnlineWindow Occurrence="During" startTime="06:00:00" duration="14"><DaysOfWeek> Mon Tue Wed Thu Fri Sat Sun</DaysOfWeek><DaysOfMonth>All</DaysOfMonth><MonthsOfYear> All</MonthsOfYear></OnlineWindow></DB2MaintenanceWindows> ') )
;
CALL SYSPROC.AUTOMAINT_SET_POLICY ( 'AUTO_RUNSTATS', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2AutoRunstatsPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><RunstatsTableScope ><FilterCondition>(TABSCHEMA, TABNAME) IN
    ( SELECT CTL.TABSCHEMA, CTL.TABNAME FROM AUTO.AUTO_RUNSTATS_CTL AS CTL)</FilterCondition></RunstatsTableScope ></DB2AutoRunstatsPolicy> ') )
;




CONNECT RESET;




db2 select * from table(sysproc.pd_get_diag_hist('optstats', 'EX', 'NONE', current_timestamp - 1 year, cast(null as timestamp))) T1





 select pid, tid,
       substr(eventtype, 1, 10),
       substr(objtype, 1, 30) as objtype,
       substr(objname_qualifier, 1, 20) as objschema,
       substr(objname, 1, 10) as objname,
       substr(first_eventqualifier, 1, 26) as event1,
       substr(second_eventqualifiertype, 1, 2) as event2_type,
       substr(second_eventqualifier, 1, 20) as event2,
       substr(third_eventqualifiertype, 1, 6) as event3_type,
       substr(third_eventqualifier, 1, 15) as event3,
       substr(eventstate, 1, 20) as eventstate
     from table(sysproc.pd_get_diag_hist
       ('optstats', 'EX', 'NONE',
         current_timestamp - 1 year, cast(null as timestamp))) as sl
     order by timestamp(varchar(substr(first_eventqualifier, 1, 26), 26));



http://www.ibm.com/developerworks/data/library/techarticle/dm-0706tang/

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0011762.html