《数据库管理命令》
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)