数据库维护常用的命令

《数据库管理命令》

1. 查看数据库大小，单位byte

1）. 通过存储过程查看

db2 "CALL GET_DBSIZE_INFO(?, ?, ?, 0) "

2）. 通过表来查询数据库大小 snaphot_tbs_cfg

db2 "select (SUM(total_pages)*4)/1024.0/1024 TOTAL_ALLOCATED_SPACE_IN_GB from table (snapshot_tbs_cfg('dbname',-1)) TBS_SPCE"

 

2. 查看当前数据库名

db2 "select current server from sysibm.sysdummy1"

 

 

 

《表空间管理工具》

1. 查看表空间状态

1）使用一条命令即可查看表空间的状态信息，格式化输出 db2 list tablespaces show detail

db2 list tablespaces show detail | while read line  ; do w1=$(echo "$line" | cut -d" " -f1); if [ "$w1" ==  "Tablespace" ];then tabid=$(echo "$line" | cut -d'=' -f2);fi; if [ "$w1" ==  "Name" ];then   name=$(echo "$line" | cut -d'=' -f2); fi; if [ "$w1" ==  "Type" ];then  type=$(echo "$line" | cut -d'=' -f2);type1=$(echo "$type" | awk '{print $1}'); if [ "$type1" == "System" ];then Ttype='SMS';  else Ttype='DMS'; fi;  fi; if [ "$w1" == "Contents" ];then contents=$(echo "$line" | cut -d'=' -f2); fi; if  [ "$w1" ==  "State" ];then state=$(echo "$line" | cut -d'=' -f2);fi; if [ "$w1" == "Total" ];then   total=$(echo "$line" | cut -d'=' -f2);fi;if [ "$w1" == "Used" ]; then used=$(echo "$line" | cut -d'=' -f2); fi;  if [ "$w1" ==  "Free" ]; then free=$(echo "$line" | cut -d'=' -f2); fi; if [ "$w1" == "Page" ];then page=$(echo "$line" | cut -d'=' -f2); printf "%-25s%-5s%-5s%-5s%-10s%-20s%-20s%-30s\n"  $name $tabid $page $Ttype $state $total $used; fi  done

我们有时候需要查看表空间状态，方式有很多，db2pd, db2 list, 当表空间较多的情况下，往往信息太多，导致不是很方便的查看信息。对于db2pd 方便之处在于不用连接数据库，即可以查询表空间信息，并且对表空间信息进行了较全的输出；db2 list,可以以表空间为单位输出对应表空间的信息；但是这两种方式对于表空间较多的数据库来说用起来很不方便。
查看表空间状态
db2 list tablespaces show detail | while read line  ; do w1=$(echo "$line" | cut -d" " -f1); if [ "$w1" ==  "Tablespace" ];then tabid=$(echo "$line" | cut -d'=' -f2); fi; if [ "$w1" ==  "Name" ];then   name=$(echo "$line" | cut -d'=' -f2);fi; if [ "$w1" ==  "Type" ];then  type=$(echo "$line" | cut -d'=' -f2);type1=$(echo "$type" | awk '{print $1}'); if [ "$type1" == "System" ];then Ttype='SMS';  else Ttype='DMS'; fi; fi; if  [ "$w1" ==  "State" ];then state=$(echo "$line" | cut -d'=' -f2); printf "%-25s%-10s%-10s%10s\n"  $name $tabid $Ttype $state; fi; done

===DISPLAY：
SYSCATSPACE              0         DMS           0x0000
TEMPSPACE1               1         SMS           0x0000
USERSPACE1               2         DMS           0x0000
SYSTOOLSPACE             3         DMS           0x0000
SYSTOOLSTMPSPACE         4         SMS           0x0000

2）通过视图查看表空间信息，包括表空间状态，总page数，可使用的page数，使用的page数，空闲的page数，是否自动扩展

db2 "select  substr(TBSP_NAME,1,20) as TBSPNAME, 
TBSP_TYPE, 
substr(TBSP_STATE,1,10) as TBSPSTATE , 
substr(TBSP_TOTAL_PAGES,1,10) as TOTAL_PAGES, 
substr(TBSP_USABLE_PAGES,1,10) as USABLE_PAGES, 
substr(TBSP_USED_PAGES,1,5) as USED_PAGES, 
substr(TBSP_FREE_PAGES,1,5) as FREE_PAGES, 
substr(TBSP_MAX_SIZE,1,5) as MAX_SIZE, 
substr(TBSP_USING_AUTO_STORAGE,1,2) as AUTOStr, 
substr(TBSP_AUTO_RESIZE_ENABLED,1,2)  as AUTORes from SYSIBMADM.TBSP_UTILIZATION"

 

2. 表空间自动扩展设置

1）查看表空间是否为自动扩展

db2 "select substr(TBSP_NAME,1,30) as DMS_tablespace_without_autoresize, TBSP_USING_AUTO_STORAGE, TBSP_AUTO_RESIZE_ENABLED from SYSIBMADM.TBSP_UTILIZATION where TBSP_TYPE='DMS' and TBSP_AUTO_RESIZE_ENABLED=0"

 

2）生成修改表空间为自动扩展

db2 -x "select 'ALTER TABLESPACE '|| TBSP_NAME ||' AUTORESIZE YES;'  from SYSIBMADM.TBSP_UTILIZATION where TBSP_TYPE='DMS' and TBSP_AUTO_RESIZE_ENABLED=0"

step 1: 

db2 -x "select 'ALTER TABLESPACE '|| TBSP_NAME ||' AUTORESIZE YES;'  from SYSIBMADM.TBSP_UTILIZATION where TBSP_TYPE='DMS' and TBSP_AUTO_RESIZE_ENABLED=0"  | tee changeTBSauto.sql 
step 2: 

db2 -tvf changeTBSauto.sql

 

3. 扩表空间步骤

查看表空间使用率

db2 "select TBSP_ID,substr(TBSP_NAME,1,30) as TBSP_NAME, TBSP_TYPE, TBSP_TOTAL_SIZE_KB/1024 as TOTAL_M,TBSP_USED_SIZE_KB/1024 as USED_M,TBSP_UTILIZATION_PERCENT as Pct, TBSP_USING_AUTO_STORAGE, TBSP_AUTO_RESIZE_ENABLED from SYSIBMADM.TBSP_UTILIZATION" |grep CLOBS_TSP

查看表空间属性（是否自动扩展，看AR/auto resize字段）

db2pd -db PMOR11 -tablespace 5

查看扩展到目标使用率需要扩展多少容量

db2 "select substr(TBSP_NAME,1,30) as TBSP_NAME,round((TBSP_USED_SIZE_KB/1024/0.75),0) as After_Extend_M,TBSP_USED_SIZE_KB/1024/0.75-TBSP_TOTAL_SIZE_KB/1024 Should_Extend_M from SYSIBMADM.TBSP_UTILIZATION where TBSP_NAME='CLOBS_TSP'"

查看目标服务器是否为高可用架构

db2pd -db CWAMAPP -hadr

查看是否有备份恢复task正在执行中

db2 list utilities show detail

扩展表空间

alter tablespace CLOBS_TSP extend (all 30M)

《数据库对象管理工具》

1.查看表大小

db2 "SELECT SUBSTR(TabSchema,1,15), TabName, TabType, (Data_Object_P_Size + Index_Object_P_Size + Long_Object_P_Size + Lob_Object_P_Size + Xml_Object_P_Size)/1024 as Total_P_Size_MB FROM SysIbmAdm.AdminTabInfo ORDER BY Total_P_Size_MB desc" >table_size.log

 

2.查询存储过程的内容：

db2 " select PROCNAME,text from syscat.procedures where PROCNAME = 'SP_A_TSKRES'"

 

检查数据库中是否有对象状态处于完整性检查，并生成执行语句

db2 connect to sample
db2 -tx +w "with gen(tabname, seq) as( select rtrim(tabschema) || '.' || rtrim(tabname) 
as tabname, row_number() over (partition by status) as seq 
from  syscat.tables 
WHERE status='C' ),r(a, seq1) as (select CAST(tabname as VARCHAR(3900)), seq 
from  gen where seq=1 union all select r.a || ','|| rtrim(gen.tabname), gen.seq 
from gen , r where (r.seq1+1)=gen.seq ), r1 as (select a, seq1 from r) 
select 'SET INTEGRITY FOR ' || a || ' IMMEDIATE CHECKED;' from r1 
where seq1=(select max(seq1) from r1)" > db2FixCheckPending.sql

db2 -tvf db2FixCheckPending.sql

 

 

《数据库安全管理工具》

1.查看用户权限

db2 " SELECT SUBSTR(GRANTOR, 1, 10) AS GRANTOR, -- Grantor of the authority
SUBSTR(GRANTEE, 1, 10) AS GRANTEE, -- Holder of the authority
-- G = Grantee is a group R = Grantee is a role
GRANTEETYPE, -- U = Grantee is an individual user
BINDADDAUTH,
CONNECTAUTH,
CREATETABAUTH,
DBADMAUTH,
IMPLSCHEMAAUTH,
DATAACCESSAUTH,
LOADAUTH
FROM SYSCAT.DBAUTH
ORDER BY GRANTEE WITH UR " 



db2 "select * from syscat.dbauth where grantee='CHUBALA'"

db2 " SELECT SUBSTR(GRANTOR, 1, 10) AS GRANTOR, -- Grantor of the authority
SUBSTR(GRANTEE, 1, 10) AS GRANTEE, -- Holder of the authority
SECURITYADMAUTH,
QUIESCECONNECTAUTH,
LIBRARYADMAUTH,
SQLADMAUTH,
WLMADMAUTH,
EXPLAINAUTH,
ACCESSCTRLAUTH
FROM SYSCAT.DBAUTH
ORDER BY GRANTEE WITH UR " 

 

2.查看表权限

db2 "select * from syscat.tabauth where grantee='CHUBALA'" db2 "select substr(GRANTOR,1,30), GRANTORTYPE, substr(GRANTEE,1,30), GRANTEETYPE, substr(TABSCHEMA,1,30), substr(TABNAME,1,30), CONTROLAUTH, ALTERAUTH, DELETEAUTH, INDEXAUTH, INSERTAUTH, REFAUTH, SELECTAUTH, UPDATEAUTH from syscat.tabauth where GRANTEE='AIXDBA3'"

db2 "select substr(GRANTOR,1,30), GRANTORTYPE, substr(GRANTEE,1,30), GRANTEETYPE, substr(TABSCHEMA,1,30), substr(TABNAME,1,30), CONTROLAUTH from syscat.tabauth where GRANTEE='AIXDBA3'"

 

 

《HADR维护》

HADR 基本维护操作

1. hadr 的启停操作

起 HADR

备 ：

db2 deactivate database sample

db2 start hadr on database sample as standby

主 ：

db2 deactivate database sample

db2 start hadr on database sample as primary

##启动完成后 ，查看hadr 状态

1. db2 get snapshot for db on sample

2. db2pd -db sample -hadr

停 HADR

主 ：

db2 deactivate database sample

db2 stop hadr on database sample

备 ：

db2 deactivate database sample /[db2inst1@hadr1 logtarget]$ db2 deactivate database sample user db2inst1 using db2inst1

db2 stop hadr on database sample

### 有时 deactivate database 不加用户名密码时，在stop hadr 时会报错

[db2inst1@hadr1 logtarget]$ db2 stop hadr on database sample

SQL1769N Stop HADR cannot complete. Reason code = "2".

[db2inst1@hadr1 logtarget]$

2. 在备用服务器上执行数据库接管

   db2 takeover hadr on database sample (by force)