DB2 数据库日常管理

1、如何快速得到 数据库所占存储空间的大小(zz)
select tbspace,sum(aa.total_pages*bb.pagesize)/1024/1024  "size(m)"
from table(snapshot_container('sdncbi',-1)) AS AA,
    syscat.tablespaces bb
where aa.tablespace_id=bb.tbspaceid
group by tbspace;

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)--->Transactions(AppHandl)--->Applications(C-AnchID,C-StmtUID)--->dynamic statement--->sql

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)"