craig Site Admin
Joined: 19 Feb 2006 Posts: 40
|
Posted: Tue Feb 28, 2006 6:44 am Post subject: Script to save snapshot data into DBA schema. |
|
|
. ~/.profile
{
CMD="db2 -t -p- -v"
db2 connect to proddw_1
Weekly="
-- weekly
insert into dba.snapshot_container SELECT * FROM TABLE(SNAPSHOT_CONTAINER('PRODDW_1', 0)) as pg;
runstats ON TABLE DBA.SNAPSHOT_CONTAINER ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
"
Intraval="
-- intravals
insert into dba.snapshot_dbm SELECT * FROM TABLE(SNAPSHOT_DBM( 0)) as pg;
insert into dba.snapshot_fcm SELECT * FROM TABLE(SNAPSHOT_FCM( 0)) as pg;
insert into dba.snapshot_database SELECT * FROM TABLE(SNAPSHOT_DATABASE('PRODDW_1', 0)) as pg;
insert into dba.snapshot_tbs SELECT * FROM TABLE(SNAPSHOT_TBS('PRODDW_1', 0)) as pg;
insert into dba.snapshot_bp SELECT * FROM TABLE(SNAPSHOT_BP('PRODDW_1', 0)) as pg;
insert into dba.snapshot_appl SELECT * FROM TABLE(SNAPSHOT_APPL('PRODDW_1', 0)) as pg;
insert into dba.snapshot_appl_info SELECT * FROM TABLE(SNAPSHOT_APPL_INFO('PRODDW_1', 0)) as pg;
insert into dba.snapshot_statement SELECT * FROM TABLE(SNAPSHOT_STATEMENT('PRODDW_1', 0)) as pg;
"
Daily="
-- daily
runstats ON TABLE DBA.SNAPSHOT_APPL ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
runstats ON TABLE DBA.SNAPSHOT_APPL_INFO ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
runstats ON TABLE DBA.SNAPSHOT_BP ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
runstats ON TABLE DBA.SNAPSHOT_DATABASE ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
runstats ON TABLE DBA.SNAPSHOT_DBM ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
runstats ON TABLE DBA.SNAPSHOT_FCM ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
runstats ON TABLE DBA.SNAPSHOT_STATEMENT ON KEY COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;
"
case "$1" in
"Daily")
echo "$Daily" | $CMD
;;
"Weekly")
echo "$Weekly" | $CMD
;;
"Intraval")
echo "$Intraval" | $CMD
;;
*)
echo "unknown option [$*]"
;;
esac
db2 connect reset
} >/tmp/DBA.Snaps.log 2>&1 |
|