DB2 資料庫日常管理

db2pd -d sdncbi -tablespace
表空間：  Number of pending free pages             = 781248 出現後
db2 force application all

執行 db2_all "db2 connect to sdncbi;db2 list tablespaces show detail;db2 connect reset"
 http://www-01.ibm.com/support/docview.wss?uid=swg21394023

         ---老：db2_all ";db2 update db cfg for sdncbi using LOGFILSIZ 51200 LOGPRIMARY 50 LOGSECOND 40"
         db2_all ";db2 update db cfg for sdncbi using LOGFILSIZ 65536 LOGPRIMARY 80 LOGSECOND 16"

2、檢視鎖狀態
       SELECT * FROM SYSIBMADM.LOCKS_HELD

Select a.DB_NAME,a.AGENT_ID,a.APPL_NAME,b.APPL_ID,b.APPL_STATUS,a.LOCK_MODE,a.LOCK_STATUS,a.TABSCHEMA,a.TABNAME,a.DBPARTITIONNUM
from sysibmadm.locks_held a,sysibmadm.applications b
where a.agent_id=b.agent_id;

3、工具目錄資料庫
       建立： DB2 ? CREATE TOOLS CATALOG
       刪除：db2 "drop tools catalog toolscat in database sdncbi"
4、清除資料
       import from /dev/null of del replace into table_name
       alter   table   tblname   activate   not   logged   initially   with   empty   table
5、建表的時候要注意分割槽鍵的選擇（不建議用月份做為分割槽鍵）
     主要考慮的是資料的均勻分佈，以後HASH JION 儘量減少TQ
6、DB2的編目的配置檔案
 \SQLLIB\DB2目錄：SQLNODIR （節點編目）和 SQLDBDIR （資料庫編目）
7、檢視資料庫表的死鎖

        還有引數locksize和maxlocks都會對鎖的產生有關。
  檢視鎖用db2 list indoubt transactions
  解除鎖用db2 list indoubt transactions with prompting

         db2pd -locks -transactions -agents -alldbs

         db2pd -db sdncbi -lock -transactions -agents -applications -file lock_info.txt

         --查詢那個節點的出現鎖等待
         db2pd -alldbp -db sdncbi -locks showlocks wait
         --到指定的節點查詢鎖相關的資訊
         db2pd -dbpartitionnum 0,1 -db sdncbi -locks -transactions -applications -dynamic -file locklog
        
     
 首先,將監視開關開啟
                             db2 update dbm cfg using dft_mon_lock  on
                              db2 update dbm cfg using DFT_MON_TABLE on
 快照
                    db2 get snapshot for  Locks  on  sdncbi >>snap.log
                    db2 get snapshot for tables on sdncbi >>snap.log
                    db2 get snapshot for bufferpools  on sdncbi >>snap.log
                    db2 get snapshot for tablespaces  on sdncbi >>snap.log
                    db2 get snapshot for database  on sdncbi >>snap.log
   然後再看snap.log中的內容即可。對Lock可根據Application handle（應用程式控制程式碼）
 看每個應用程式的鎖的情況。監視完畢後，不要忘了將監視器關閉
     db2 update dbm cfg using dft_mon_lock  off

     select * from table(snapshot_lock('sdncbi',-1)) snapshot_lock
------------------------------判斷是鎖等待

        db2 update monitor switches using lock on
  db2 get snapshot for locks on sdncbi>snap.log
    db2 update monitor switches using lock off ----也可以不用關

鎖資訊得到鎖表的sql:
1.執行:
 db2pd -db sdncbi -lock -transactions -reopt -applications -file c:/lock_info.log
2.在檔案裡關聯欄位找到引起鎖的SQL語句:
 通過Locks(TranHdl)---&gtTransactions(AppHandl)---&gtApplications(C-AnchID,C-StmtUID)---&gtdynamic statement---&gtsql

 

8、檢視錶空間佔用情況
  
   select tbsp_name,sum(tbsp_total_pages) as "total",
        sum(tbsp_used_pages) as "use pages",
        sum( tbsp_free_pages) as "free pages",
        sum(tbsp_used_pages)*1.0/sum(tbsp_total_pages)*100 as "use precent"
  from "SYSIBMADM"."TBSP_UTILIZATION"
  where tbsp_total_pages>0
  group by tbsp_name

9、檢查資料分佈是否均勻（按表空間)
nohup sh CheckPartitionnum.sh USR_TBSP_MINING >check.log &

10、檢查SQL語句
SELECT   elapsed_time_min, SUBSTR (AUTHID, 1, 10) AS auth_id, agent_id,
         appl_status, SUBSTR (stmt_text, 1, 60) AS sql_text
    FROM sysibmadm.long_running_sql
   WHERE elapsed_time_min > 0
ORDER BY elapsed_time_min DESC

11、SQL0668N  由於表 "ATOM.T_ST_USAGE_DAY" 上的原因碼為 "3"，所以不允許操作

      db2 "load from /dev/null of del terminate into
      atom.t_st_usage_day PARTITIONED DB CONFIG  OUTPUT_DBPARTNUMS (10,36,14,17,21,22,27,30,31,32,33,34,35)";

12、取得維表的遞迴：
    with n(level,schemaname,tname) as
    (
      select distinct 1,REFTABSCHEMA,REFTABNAME
        from SYSCAT.REFERENCES
       where REFTABSCHEMA not in ('DB2INFO','SYSTOOLS')
       union all
      select d.level+1,c.refTABSCHEMA,c.refTABNAME
        from SYSCAT.REFERENCES as c,n as d
       where c.tabname =d.tname and d.level < 10 ---限制執行10次
     )
 
      select schemaname,tname,max(level) flag from n group by schemaname,tname order by flag desc
    

13、歸檔日誌處理
db2 connect to DBNAME
db2 get db cfg | awk -F= '$1 ~ /First active log file/ {print $2}' | read firstlog
db2 prune logfile prior to $firstlog
db2 terminate

14、匯出儲存過程
SELECT 'db2 "EXPORT TO '||rtrim(procschema)||'.'||rtrim(procname)||'.sql of del MODIFIED BY LOBSINFILE SELECT ''SET CURRENT SCHEMA '
||rtrim(procschema)||' @''||chr(10)|| '' SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,'
||rtrim(procschema)||' @''||chr(10)||'||'text'||'||chr(10)||'' @''||chr(10) from syscat.procedures where procschema='''||rtrim(procschema)||''' and procname ='''||rtrim(procname)||'''"'

FROM SYSCAT.PROCEDURES where procschema not in ('DB2INFO','IDMMX','SQLJ','SYSFUN','SYSIBM','SYSPROC','SYSIBMADM')

 

15、查詢非法儲存過程
   SELECT RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname , ' ( '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' )' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS ( SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y' ) ) ORDER BY spname;

16、事務日誌滿

1． 在TOAD上用下面的句子查詢事務的哪個節點滿了
 
select
      int(total_log_used/1024/1024) as "Log Used (Meg)",
      int(total_log_available/1024/1024) as "Log Space Free (Meg)",
      int(tot_log_used_top/1024/1024) as "Max Log Used (Meg)",
      int(sec_log_used_top/1024/1024) as "Max Sec. Used (Meg)",
      int(sec_logs_allocated) as "Secondaries",
      int(float(total_log_used)/float(total_log_used+total_log_available)*100) as "Pct Used",
      t.DB_NAME,
      t.DB_PATH
from sysibmadm.snapdb t
order by int(float(total_log_used)/float(total_log_used+total_log_available)*100) desc
with ur
 
   2.  登陸db2_dw_5執行
 
  db2 terminate  
  export DB2NODE=13
  pid=`db2 get snapshot for database on hebdw|grep -p log | grep oldest | cut -d= -f2`
  echo $pid
  db2 "force application ($pid)"