DB2运维常用命令

DB2监控
db2top -d dbname

列出当前数据库
list db directory;

表结构：

describe table owner.table_name;

查看表的索引：

describe indexes for table owner.table_name;

查看分区

db2 => LIST DBPARTITIONNUMS ;

$ pwd
/db2home/ssopdb/db2inst2/sqllib

$ cat db2nodes.cfg
0 ssopdb1 0
1 ssopdb1 1
2 ssopdb1 2
3 ssopdb1 3
4 ssopdb1 4
5 ssopdb1 5
6 ssopdb1 6
7 ssopdb1 7
8 ssopdb2 0
9 ssopdb2 1
10 ssopdb2 2
11 ssopdb2 3
12 ssopdb2 4
13 ssopdb2 5
14 ssopdb2 6
15 ssopdb2 7


断开所有连接停库

db2 force application all


db2stop force


查看本地节点目录
db2 list node directory

在DB2中从客户端访问服务器端的数据库时，不能直接用connect命令，而必须先建立通信node，再在node的基础上建立数据库连接。在命令行的具体操作如下：

->db2 catalog tcpip node ABC remote serverName server 50000
->db2 catalog db databaseName at node ABC
->db2 connect to databaseName user Uid using Pwd


查询表的详情

select TABSCHEMA,TABNAME,OWNER,STATUS,CREATE_TIME,TYPE from SYSCAT.TABLES where TABNAME like 'EXPLAIN%';


查询现有的schema

select schemaname from syscat.schemata;


将某个用户下所有表的查询权限赋给别人

db2 -wx "SELECT 'grant select on table ' ||tabschema||'.'||tabname|| ' to user ZHENGDY;' FROM syscat.tables WHERE tabschema = 'GZCRM'" >ZHENGDY.sql

db2 -tvf lixn.sql


表空间

---tbs

SELECT TBSP_ID,
substr(tbsp_name, 1, 20) AS TABLESPACE_NAME,
substr(tbsp_content_type, 1, 10) AS TABLESPACE_TYPE,
sum(tbsp_total_size_kb) / 1024 AS TOTAL_MB,
sum(tbsp_used_size_kb) / 1024 AS USED_MB,
sum(tbsp_free_size_kb) / 1024 AS FREE_MB,
tbsp_page_size AS PAGE_SIZE
FROM SYSIBMADM.TBSP_UTILIZATION
GROUP BY
TBSP_ID,
tbsp_name,
tbsp_content_type,
tbsp_page_size
ORDER BY 1;

--tbs partition
SELECT
substr(tbsp_name, 1, 20) AS TABLESPACE_NAME,
substr(tbsp_content_type, 1, 10) AS TABLESPACE_TYPE,
DBPARTITIONNUM,
sum(tbsp_total_size_kb) / 1024 AS TOTAL_MB,
sum(tbsp_used_size_kb) / 1024 AS USED_MB,
sum(tbsp_free_size_kb) / 1024 AS FREE_MB,
tbsp_page_size AS PAGE_SIZE
FROM SYSIBMADM.TBSP_UTILIZATION
GROUP BY tbsp_name,
tbsp_content_type,
tbsp_page_size,
DBPARTITIONNUM
ORDER BY 1,3;

--datafile

SELECT Substr(tbsp_name,1,20) AS tbsp_name,
Int(tbsp_id) AS tbsp_id,
Substr(container_name,1,45) AS container_name,
Int(container_id) AS container_id,
container_type,
stripe_set,
Int(total_pages) AS total_pages,
Int(usable_pages) AS usable_pages,
accessible,
dbpartitionnum
FROM sysibmadm.container_utilization
ORDER BY tbsp_id,
dbpartitionnum;

--事务日志

select int(total_log_used/1024/1024) as "Log Used (Mb)",int(total_log_available/1024/1024) as"Log Space Free(Mb)",int((float(total_log_used)/float(total_log_used+total_log_available))*100) as "Pct Used",int(tot_log_used_top/1024/1024) as "Max Log Used (Mb)",int(sec_log_used_top/1024/1024) as "Max Sec. Used (Mb)",int(sec_logs_allocated) as"Secondaries" from sysibmadm.snapdb;


--权限

SELECT GRANTEE,GRANTEETYPE,CREATETABAUTH,CONNECTAUTH,DBADMAUTH FROM SYSCAT.DBAUTH WHERE GRANTEE IN ('WANGLS2');

GRANT CONNECT ON DATABASE TO USER ZHENGDY;

--查看索引

select OWNER,TABNAME,COLNAMES from syscat.indexes where indname='KPI_MONTHLY_INDEX';

--得到建表语句

select TABSCHEMA,TABNAME,OWNER,STATUS,CREATE_TIME,TYPE from SYSCAT.TABLES where TBSPACEID=7;

db2look -d gzdmt -u gzcrm -e -o db2tabcreate.sql -t INT_10105_857_001_20140426

--日志使用率

select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB,TOTAL_LOG_AVAILABLE_KB,TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM from SYSIBMADM.LOG_UTILIZATION;

--查看数据库中的锁

db2 "select agent_id, tabname from sysibmadm.snaplock"

--给用户建表权限

db2 grant CREATETAB on database to WANGLS2

db2 grant IMPLICIT_SCHEMA on database to WANGLS2

db2 GRANT USE OF TABLESPACE TBS_DATA_02 TO WANGLS2

--表空间权限

select * from SYSCAT.TBSPACEAUTH where GRANTEE='ZHONGSJ';

* SYSCAT.DBAUTH - 数据库特权
* SYSCAT.COLAUTH - 表和视图列特权
* SYSCAT.INDEXAUTH - 索引特权
* SYSCAT.PACKAGEAUTH - 包特权
* SYSCAT.SCHEMAAUTH - 模式特权
* SYSCAT.TABAUTH - 表和视图特权
* SYSCAT.TBSPACEAUTH - 表空间特权