DB2常用命令

DB2 常用命令

1、启停命令

先切换用户

su - db2inst1  //切换用户

再操作启停连接

db2start  //启动数据库
db2stop   //停止数据库
db2stop force   //强制停止数据库

连接相关操作

db2 connect to  <databaseName >  //表示默认使用db2inst1连接db2的<dbName>

db2 disconnect  <databaseName >  //断开与<dbName>的连接

db2 connect to <databaseName> user <userName> using <password> //使用本地别名连接远端数据库

2、Schema 命令

DB2中的schema并不直接和一个数据库用户绑定，虽然他们会有默认的绑定关系，但是DB2可以独立创建一个不存在用户关联的schema来进行使用。

查看当前的 schema :

db2 "values current schema" 

查看当前存在的schema

db2 "select SCHEMANAME from syscat.SCHEMATA" 

创建一个独立的schema

db2 "create schema test" 

切换 shame :

db2 "set current schema='TEST'" 
db2 "values current schema" 

要使用命令行来删除模式，请输入：

DROP SCHEMA <name> RESTRICT

其实大部门情况都不必特意执行命令创建 schema 的，只要在建表的时候带上SCHEMA前缀，系统中如果不存在就会自动创建schema的。下面会有建表示例。

2、建库命令

db2 create db <databaseName >     //创建数据库

db2 create db <databaseName> using codeset utf-8 territory CN  //创建数据库使用utf-8编码

db2 drop db <databaseName >  //创建数据库使用utf-8编码

3、查看信息

db2 get db cfg  ## 查看数据库配置
db2 get db cfg for DBCM  ## 查看指定数据库的配置

db2 list db directory   ## 列出所有数据库
db2 list active databases ## 列出所有活动的数据库

db2 list tables for all ## 列出当前数据库下所有的表
db2 list tables for schema dbcm ## 列出当前数据库中schema为dbcm的表

db2 list tablespaces show detail ## 显示数据库空间使用情况
db2 list tablespaces  ## 列出表空间

db2 list tables    ## 查看数据库中所有表结构
db2 list tables for system   ## 列出所有系统表

db2 describe table <tableName>  ## 查看表结构

4、查询操作

DB2命令查询：

db2 "select ... "
db2level

SQL系统信息查询：

--查找所有表 的中文名里包含'%日志%'的表，前提建表时有中文注释
select * from SYSCAT.TABLES where REMARKS like '%日志%';

--查询现有的Schema
select SCHEMANAME,owner,CREATE_TIME from syscat.schemata

@desc LOGINFO  --查询LOGINFO表的表结构，DBVisualizer 中可以界面操作。

--查询DB2版本
select * from sysibm.sysvERSIONS 

查询前100条数据：

--查找前100条日志数据
Select * from DBCM.LOGINFO  fetch first 100 rows only;

分页查询：

rownumber() over()

简单查询实例：

select * from (
select *,rownumber() over() as row_id from  LOGINFO  
) where row_id between 1 and 10

复杂查询示例，实际使用可能较多：

select * from (
    select tmp_page.*, rownumber() over() as row_id from ( 
        select * from LOGINFO where OPTYPE='1' order by id desc 
    ) as tmp_page
) where row_id between 1 and 10

5、建表操作

实际项目中，相对比较完整的建表的操作包括建表、字段、注释（建议有）、索引（非必须），本次为示例完整性建一下索引，如下：

其中DBCM是SCHEMA，DBCM_APP是对应的表空间。

CREATE TABLE DBCM.LOGINFO
(
     ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
     ENABLE SMALLINT,
     USER_ID CHARACTER(32),
     COUNT_AMT DECIMAL(15,2),
     LOG_MSG VARCHAR(255),
     OP_DATE DATE default CURRENT DATE
     OP_TIME TIME default  CURRENT  TIME,
     OP_TIMESTAMP TIMESTAMP default  CURRENT  timestamp,
     OP_TYPE CHAR(5),
     primary key(ID)
) in DBCM_APP ;
--表与字段相关注释
COMMENT ON TABLE DBCM.LOGINFO IS "操作日志表";
COMMENT ON TABLE DBCM.LOGINFO.ENABLE IS "查询限制（1-可查，0-禁查）";
COMMENT ON TABLE DBCM.LOGINFO.USER_ID IS "用户ID";
COMMENT ON TABLE DBCM.LOGINFO.COUNT_AMT IS "操作金额";
COMMENT ON TABLE DBCM.LOGINFO.LOG_MSG IS "操作信息";
COMMENT ON TABLE DBCM.LOGINFO.OP_DATE IS "操作日期";
COMMENT ON TABLE DBCM.LOGINFO.OP_TIME IS "操作时间";
COMMENT ON TABLE DBCM.LOGINFO.OP_TIMESTAMP IS "操作时间戳";
COMMENT ON TABLE DBCM.LOGINFO.OP_TYPE IS "操作时类型";
--创建表索引
CREATE INDEX idx1_dbcm_user_id ON DBCM.LOGINFO(USER_ID);

CREATE INDEX idx1_dbcm_opdate ON DBCM.LOGINFO(OP_DATE);

如果数据库中不存在名为DBCM的SCHEMA，则会自动创建。

对应的，如果要删除索引

DROP INDEX idx1_dbcm_opdate ON DBCM.LOGINFO(OP_DATE);

6、改表操作

改表操作在实际中也会遇到，业务变更导致字段变化。

增加列 ：

ALTER TABLE <schema>.<tableName> ADD COLUMN <columnName>  <type>

如给刚才的LOGINFO表追加一个备用字段MARK_1：

ALTER TABLE DBCM.LOGINFO ADD COLUMN MARK_1 VARCHAR(255);

添加带默认值的属性：

ALTER TABLE [table_name] ADD COLUMN [column_name] [column_type] NOT NULL WITH DEFAULT [value]

如新增带默认值的时间戳列:

ALTER TABLE DBCM.LOGINFO  ALTER column test_timestamp  SET DEFAULT  current timestamp;

修改列 ：

ALTER TABLE <schema>.<table_name> ALTER COLUMN <columnName> SET DATA TYPE <columnType>

如给刚才的LOGINFO表修改OP_TYPE字段：

ALTER TABLE DBCM.LOGINFO  ALTER COLUMN OP_TYPE SET DATA TYPE VARCHAR(10) NOT NULL;

如修改OP_TYPE字段，删除非空属性：

ALTER TABLE DBCM.LOGINFO  ALTER COLUMN OP_TYPE DROP NOT NULL;

修改列默认值：

ALTER TABLE  <schema>.<table_name>  ALTER COLUMN [column_name] SET default [value]

重命名列：

ALTER TABLE [table_name]  RENAME COLUMN [column_name]  TO [new_column_name]  ;

如重命名OP_TYPE字段：

ALTER TABLE DBCM.LOGINFO  RENAME COLUMN OP_TYPE TO NEW_OP_TYPE;

删除列：

ALTER TABLE  <schema>.<tableName>  DROP COLUMN  <columnName>  

如给刚才的LOGINFO表删除备用字段MARK_1：

ALTER TABLE  DBCM.LOGINFO   DROP COLUMN  MARK_1 ;

你以为这样就完美了吗？不，还要进行重构表。

重构表：

重构表操作，可以使用命令，也可以使用SQL操作：

（1）命令操作模式：

REORG TABLE  . 

以上面的LOGINFO表为例，执行命令：

REORG TABLE DBCM.LOGINFO ;

（2）SQL操作模式：

CALL SYSPROC.admin_cmd('reorg table  <schema>.<tableName>  ');

以上面的LOGINFO表为例，执行SQL：

CALL SYSPROC.admin_cmd('reorg table DB2INST1.DATA_BASE');

REORG扩展：

什么是REORG?

对表数据进行增删改等操作，经过一系列更改后，逻辑上连续的数据可能会位于不连续的物理数据页上，在许多插入操作创建了溢出记录时尤其如此。按这种方式组织数据时，数据库管理器必须执行其他读操作才能访问顺序数据。而在删除大量行后，也需要执行其他的读操作。

REORG（重组/重构）表的操作会整理数据碎片来减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。

什么时候执行REORG ?

（a）增加列，扩长度，修改列类型等。

（b）表中的记录变化量很大时，则需要在表上做REORG操作来优化数据库性能。

（c）对数据库对象的大量操作，如反复地删除表，删除索引，存储过程，会引起系统表中数据的频繁改变，导致表或索引碎片太多时需要REORG。可以使用db2工具reorgchk检查。