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"

 

         ---老：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)||' @''