DB2常用 命令汇总
整理下15年写的DB2相关文档，换工作以后一直没用过DB2，忘的差不多了。
进入DB2
windows+R ，输入 cmd 回车
输入db2 回车， DB21061E 未初始化命令行环境。
输入db2cmd 回车
弹出另一个窗口
输入db2 回车
？查看帮助信息
查看配置好的db
连接数据库sample
显示当前用户所有表
重新连接数据库sample
查看用户下表
查询系统表
查看dept 表结构
查看表dept
查看进程
停止数据库
强制停止数据库
查看活跃的数据库
启动数据库
创建表t1
插入数据
下载SqlDbx
安装SqlDbx
连接DB2 数据库
创建数据库chen
查看数据库
连接chen 数据库
查看执行计划
执行脚本，生成执行计划表
格式化输出执行计划
DB2 Universal Database Version 10.1, 5622-044 (c) Copyright IBM Corp. 1991, 2011
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 10.01.0
SOURCE_NAME: SQLC2J23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2015-10-02-19.49.07.026000
EXPLAIN_REQUESTER: ADMINISTRATOR
......
Original Statement:
------------------
select
*
from
test
Optimized Statement:
-------------------
SELECT
Q1."ID" AS "ID"
FROM
ADMINISTRATOR.TEST AS Q1
Access Plan:
-----------
Total Cost: 6.77266
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
6.77266
1
|
1
TABLE: ADMINISTRATOR
TEST
Q1
......
创建表test1 ，插入 100000 条数据，查看执行计划
DB2 Universal Database Version 10.1, 5622-044 (c) Copyright IBM Corp. 1991, 2011
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 10.01.0
SOURCE_NAME: SQLC2J23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2015-10-04-16.40.45.910000
EXPLAIN_REQUESTER: ADMINISTRATOR
......
Original Statement:
------------------
select
userid
from
test1
where
userid=50000
Optimized Statement:
-------------------
SELECT
Q1."USERID" AS "USERID"
FROM
ADMINISTRATOR.TEST1 AS Q1
WHERE
(DECFLOAT(Q1."USERID", 34, '.') = 50000)
Access Plan:
-----------
Total Cost: 1186.33
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4000
TBSCAN
( 2)
1186.33
1300
|
100000
TABLE: ADMINISTRATOR
TEST1
Q1
......
创建索引
查看执行计划
DB2 Universal Database Version 10.1, 5622-044 (c) Copyright IBM Corp. 1991, 2011
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 10.01.0
SOURCE_NAME: SQLC2J23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2015-10-04-17.01.58.794000
EXPLAIN_REQUESTER: ADMINISTRATOR
......
Original Statement:
------------------
select
userid
from
test1
where
userid=50000
Optimized Statement:
-------------------
SELECT
Q1."USERID" AS "USERID"
FROM
ADMINISTRATOR.TEST1 AS Q1
WHERE
(DECFLOAT(Q1."USERID", 34, '.') = 50000)
Access Plan:
-----------
Total Cost: 901.473
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4000
IXSCAN
( 2)
901.473
912.122
|
100000
INDEX: ADMINISTRATOR
INX_TEST1_USERID
Q1
......
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
