DB2数据库日常管理

页面导航:首页 > 数据库 > DB2 > DB2数据库日常管理

DB2数据库日常管理

来源: 作者: 时间:2016-01-23 11:15 【

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

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"
 
         ---老: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、工具目录数据库
       创建: ? 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)"
 
 
 
作者 myamor
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<