db2日常管理

环境

OS:Centos 7

DB:11.5.6

 

1.登录数据库

[db2inst1@host135 SQL00001]$ db2

db2 => connect to db_hxl

 

2.列出当前实例上所有的数据库

[db2inst1@host135 backup]$ db2 list database directory

 

3.查看db2是否已经启动

db2pd -edus

 

4.db2日志目录

 

[root@host134 /]# find / -name db2diag.log
find: ‘/proc/4212’: No such file or directory
/home/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log

 

5.db2删除数据库

db2 drop database db_hxl
db2 drop database db_hxl01

 

 

6.断开数据库连接3选1
[db2inst1@localhost ~]$ db2 connect reset
[db2inst1@localhost ~]$ db2 disconnect current
[db2inst1@localhost ~]$ db2 terminate

 

7.创建表(timestamp类型)

 

db2 connect to db_hxl
db2 "create table tb_test01(
id bigint not null generated by default as identity (start with 1,increment by 1),
name varchar(20),
createtime timestamp(6) default current timestamp,
updatetime timestamp(6) default current timestamp
)"

 

多行使用双引号引起来.

写入数据并查询

 

db2 "insert into tb_test01(name) values('name1')"
db2 "insert into tb_test01(name) values('name2')"
db2 "insert into tb_test01(name) values('name3')"
db2 "insert into tb_test01(name) values('name4')"
db2 "insert into tb_test01(name) values('name5')"
db2 "select * from tb_test01"

 

 

 date类型字段

db2 "create table tb_test01(
id bigint not null generated by default as identity (start with 1,increment by 1),
name varchar(20),
createtime date default CURRENT DATE,
updatetime date default CURRENT DATE
)"


db2 "drop table tb_test01"

db2 "insert into tb_test01(name) values('name1')"
db2 "insert into tb_test01(name) values('name2')"
db2 "insert into tb_test01(name) values('name3')"
db2 "insert into tb_test01(name) values('name4')"
db2 "insert into tb_test01(name) values('name5')"
db2 "select * from tb_test01"

 

 8.设置实例开机自启

db2set db2autostart=yes

查看已经设置的参数

 

[db2inst1@localhost ~]$ db2set -all

 

 

 

9.查看数据库的配置

db2 get dbm cfg          ##所有的配置

 db2 get db cfg for db_hxl ##针对某个数据库

 

10.获取当前的实例

[db2inst1@localhost ~]$ db2 get instance

 

11.db2杀掉会话

[db2inst1@localhost ~]$ db2 list application

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp          56         *LOCAL.db2inst1.240202010033                                   DB_HXL   1    


[db2inst1@localhost ~]$ db2 "force application (56)"

 

12.查看具体某个会话执行情况

db2 get snapshot for application agentid 532

 

 

13.指定目录创建数据库
mkdir -p /home/db2inst1/db_test
db2 "create db db_test on '/home/db2inst1/db_test' using codeset utf-8 territory CN"

 

创建后的数据目录如下:
[db2inst1@localhost db2inst1]$ pwd
/home/db2inst1/db_test/db2inst1

 

[db2inst1@localhost NODE0000]$ ls
DB_TEST SQL00001 sqldbdir

[db2inst1@localhost DB_TEST]$ pwd
/home/db2inst1/db_test/db2inst1/NODE0000/DB_TEST

目录下面会自动加上实例名

 

14.导出数据库下所有的表结构

db2look -d db_hxl -z db2inst1 -e -o db_hxl.sql

-d:数据库名称
-z:模式名,不指定的话就是实例名

 

15.查询表的记录数

 

SELECT TABSCHEMA, TABNAME, CARD
FROM SYSCAT.TABLES
WHERE TYPE = 'T'
ORDER BY TABSCHEMA, TABNAME;

 

此查询将返回包含每个表模式(TABSCHEMA)、表名(TABNAME)和行数(CARD)的结果集.

 

 16.查询所有的数据库

db2 list database directory

 如下语句列出当前连接的数据库

db2 "SELECT DISTINCT DB_NAME FROM TABLE (MON_GET_MEMORY_SET ('DATABASE', NULL, -2))"