如何在DOCKER环境中使用Db2

此文目的是帮助用户能够在docker的环境中学习Db2，前提条件的你的服务器可以访问互联网，否则无法下载Db2镜像。Docker环境中学习Db2的优势是省去了下载产品、安装产品、初始化数据库实例这部分工作，更适合于开发人员学习Db2。

     另外本文也介绍了Db2和MYSQL的常用命令区别，以便于大家同时学习两个产品。

1、列出已有的Db2镜像

-bash-4.2# docker images | grep -i db2
ibmcom/db2express-c                      latest              7aa154d9b73c        2 years ago         1.71 GB

 2、拉Db2镜像

-bash-4.2# docker pull ibmcom/db2express-c
Using default tag: latest
latest: Pulling from ibmcom/db2express-c
a3ed95caeb02: Pull complete
aeb4552c46f2: Pull complete
67f42aa337c8: Pull complete
36add9e32b7b: Pull complete
cca79fe4efc9: Pull complete
47257c26990b: Pull complete
ffaad03725df: Pull complete
12bf772a460f: Pull complete
d7696ccb610d: Pull complete
6cf81245c65d: Pull complete
5dab63ce98e0: Pull complete
91dd2d768d97: Pull complete
e3aca141cf3e: Pull complete
Digest: sha256:6217d60b20b58e08e5189f467cfb9a08f3472cf6c3a5e5d5d91900791c709a86
Status: Downloaded newer image for ibmcom/db2express-c:latest

 3、启动容器：

-bash-4.2# docker run -it --network=host -p 50000:50000 -e Db2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept ibmcom/db2express-c:latest bash
Changing password for user db2inst1.
New password: BAD PASSWORD: The password contains the user name in some form
Retype new password: passwd: all authentication tokens updated successfully.

•  -p 50000:50000 允许远程的客户端可以从50000 端口连接到数据库实例.
• 通过指定 -e Db2INST1_PASSWORD=db2inst1-pwd 参数, 你可以为缺省的db2实例用户db2inst1设置密码.
• 通过指定-e LICENSE=accept参数, 表示你接受了使用Db2软件的许可证协议.

 4、切换到实例用户db2inst1

[root@shcallisto-25 /]# su - db2inst1
Last login: Wed May 20 21:57:28 UTC 2015

5、启动实例

[db2inst1@shcallisto-25 ~]$ db2start
SQL1063N  Db2START processing was successful.

6、查看运行状态

[db2inst1@shcallisto-25 ~]$ db2pd -
Database Member 0 -- Active -- Up 0 days 00:00:06 -- Date 2018-01-16-03.30.51.059168

7、查看已经创建的数据库

[db2inst1@shcallisto-25 ~]$ db2 list db directory
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031
说明目前没有创建数据库

8、查看数据库和补丁版本

[db2inst1@shcallisto-25 ~]$ db2level
Db21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and Db2 code release "SQL10055" with level
identifier "0606010E".
Informational tokens are "Db2 v10.5.0.5", "s141128", "IP23633", and Fix Pack
"5".
Product is installed at "/home/db2inst1/sqllib".

9、显示运行的Db2容器信息,在宿主机上运行

-bash-4.2# docker ps | grep db2
3492501e4f07        ibmcom/db2express-c:latest   "/entrypoint.sh bash"    16 seconds ago      Up 15 seconds                           wizardly_mestorf

10、关闭数据库实例，后面需要启动容器时指定宿主机目录和容器目录的映射关系，目的是在这些目录中创建数据库。.

db2stop
exit

11、重启容器，通过-v选项指定宿主机目录和容器目录的映射关系：

-bash-4.2# docker run -it --network=host -p 50000:50000 -e Db2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept -v /db2data:/db2data ibmcom/db2express-c:latest bash
Changing password for user db2inst1.
New password: BAD PASSWORD: The password contains the user name in some form
Retype new password: passwd: all authentication tokens updated successfully.
宿主机目录为/db2data，容器目录为/db2data

12、查看

[root@shcallisto-25 /]# df -h
Filesystem             Size  Used Avail Use% Mounted on
rootfs                 148G   55G   94G  37% /
overlay                148G   55G   94G  37% /
tmpfs                  7.9G     0  7.9G   0% /dev
tmpfs                  7.9G     0  7.9G   0% /sys/fs/cgroup
/dev/mapper/rhel-root  148G   55G   94G  37% /db2data
shm                     64M     0   64M   0% /dev/shm

13、设置目录权限，其中db2data为表空间的目录、log为日志目录、dbpath为创建数据库时的dbpath。

[root@shcallisto-25 /]# chmod 777 db2data
[root@shcallisto-25 /]# su - db2inst1
Last login: Tue Jan 16 05:15:10 UTC 2018 on console
[db2inst1@shcallisto-25 ~]$
[db2inst1@shcallisto-25 ~]$ cd /db2data
[db2inst1@shcallisto-25 db2data]$ touch 1
[db2inst1@shcallisto-25 db2data]$ rm 1
[db2inst1@shcallisto-25 db2data]$ mkdir data
[db2inst1@shcallisto-25 db2data]$ mkdir dbpath
[db2inst1@shcallisto-25 db2data]$ mkdir log
[db2inst1@shcallisto-25 db2data]$ pwd
/db2data
[db2inst1@shcallisto-25 db2data]$ ls -lrt
total 0
drwxrwxr-x 2 db2inst1 db2inst1 6 Jan 16 05:16 data
drwxrwxr-x 2 db2inst1 db2inst1 6 Jan 16 05:16 dbpath
drwxrwxr-x 2 db2inst1 db2inst1 6 Jan 16 05:16 log

查看缺省的dbpath
[db2inst1@shcallisto-25 ~]$ db2 get dbm cfg | grep -i dbpath
 Default database path                       (DFTDBPATH) = /home/db2inst1

14、创建用户数据库mydb

[db2inst1@shcallisto-25 db2data]$  db2 "create db mydb on /db2data/data dbpath on /db2data/dbpath using codeset utf-8 territory cn"
Db20000I  The CREATE DATABASE command completed successfully.

15、修改日志路径

[db2inst1@shcallisto-25 db2data]$ db2 update db cfg for mydb using newlogpath /db2data/log
Db20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

16、激活数据库

[db2inst1@shcallisto-25 db2data]$ db2 activate db mydb
Db20000I  The ACTIVATE DATABASE command completed successfully.

17、确认日志生效

[db2inst1@shcallisto-25 db2data]$ cd /db2data/log
[db2inst1@shcallisto-25 log]$ ls
NODE0000
[db2inst1@shcallisto-25 log]$ cd *
[db2inst1@shcallisto-25 NODE0000]$ ls
LOGSTREAM0000
[db2inst1@shcallisto-25 NODE0000]$ cd *
[db2inst1@shcallisto-25 LOGSTREAM0000]$ ls
S0000000.LOG  S0000001.LOG  S0000002.LOG  SQLLPATH.TAG
[db2inst1@shcallisto-25 LOGSTREAM0000]$ ls -lrt
total 12028
-rw------- 1 db2inst1 db2inst1     512 Jan 16 05:22 SQLLPATH.TAG
-rw------- 1 db2inst1 db2inst1 4104192 Jan 16 05:22 S0000002.LOG
-rw------- 1 db2inst1 db2inst1 4104192 Jan 16 05:22 S0000001.LOG
-rw------- 1 db2inst1 db2inst1 4104192 Jan 16 05:22 S0000000.LOG
[db2inst1@shcallisto-25 LOGSTREAM0000]$ db2 get db cfg for mydb | grep -i primary
 Number of primary log files                (LOGPRIMARY) = 3

18、由于容器是静态的，所以每次重启容器之后都需要做catalog，否则无法访问数据库。

[db2inst1@shcallisto-25 sqllib]$ db2 catalog db mydb as mydb on /db2data/dbpath
Db20000I  The CATALOG DATABASE command completed successfully.
[db2inst1@shcallisto-25 sqllib]$ db2 connect to mydb

   Database Connection Information

 Database server        = Db2/LINUXX8664 10.5.5
 SQL authorization ID   = Db2INST1
 Local database alias   = MYDB

此时我们运行docker ps -a将看到有两个Db2容器在运行：
CONTAINER ID        IMAGE                          COMMAND                  CREATED              STATUS                     PORTS               NAMES
3d221e0aaa69        ibmcom/db2express-c:latest     "/entrypoint.sh bash"    About a minute ago   Up About a minute                              tender_fermat
088dc03e36c4        ibmcom/db2express-c:latest     "/entrypoint.sh bash"    2 hours ago          Up 2 hours                                     kind_dijkstra

19、 现在，可以提交修改到容器。

docker commit 3d221e0aaa69 ibmcom/db2express-c-new
sha256:93ab907a65196dfbeba0b376b0f3bd61d8bf07e9020b80f2c72fefb80a143028

-bash-4.2# docker images | grep db2
REPOSITORY                               TAG                 IMAGE ID            CREATED              SIZE
ibmcom/db2express-c-new                  latest              93ab907a6519        About a minute ago   1.71 GB
ibmcom/db2express-c                      latest              7aa154d9b73c        2 years ago          1.71 GB

20、执行save命令持久化镜像，这里运行的目的是保存catalog db的信息，以便每次重启容器后可以直接访问mydb数据库

# docker save ibmcom/db2express-c-new >/home/ibm-db2express-c-new.tar
最后的结果是生成一个1.7GB大小的Tar文件
-bash-4.2# ls -l
total 1694560
-rw-r--r-- 1 root     root     1735228928 Jan 16 08:02 ibm-db2express-c-new.tar

21、停止已经启动的db2 container，以避免表空间访问冲突；

-bash-4.2# docker ps -a | more
CONTAINER ID        IMAGE                            COMMAND                  CREATED             STATUS                      PORTS               NAMES
c33d4f38ac17        ibmcom/db2express-c-new:latest   "/entrypoint.sh bash"    16 minutes ago      Up 16 minutes                                   eager_ride
3d221e0aaa69        ibmcom/db2express-c:latest       "/entrypoint.sh bash"    40 minutes ago      Exited (0) 16 minutes ago                       tender_fermat
088dc03e36c4        ibmcom/db2express-c:latest       "/entrypoint.sh bash"    2 hours ago         Up 2 hours                                      kind_dijkst
-bash-4.2# docker stop 088dc03e36c4
088dc03e36c4
-bash-4.2# docker stop c33d4f38ac17
c33d4f38ac17

22、重新启动新的image，发现不需要做catalog db了，说明这些信息已经持久化到影像中了。

-bash-4.2# docker run -it --network=host -p 50000:50000 -e Db2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept -v /db2data:/db2data ibmcom/db2express-c-new:latest bash
Changing password for user db2inst1.
New password: BAD PASSWORD: The password contains the user name in some form
Retype new password: passwd: all authentication tokens updated successfully.
[root@shcallisto-25 /]#
[root@shcallisto-25 /]# su - db2inst1
Last login: Tue Jan 16 07:42:24 UTC 2018 on console
[db2inst1@shcallisto-25 ~]$
[db2inst1@shcallisto-25 ~]$ db2start
SQL1063N  Db2START processing was successful.
[db2inst1@shcallisto-25 ~]$ db2 connect to mydb

   Database Connection Information

 Database server        = Db2/LINUXX8664 10.5.5
 SQL authorization ID   = Db2INST1
 Local database alias   = MYDB

23、Db2和mysql常用SQL对比

1)查看有哪些数据库：

  db2: list db directory

  mysql: show databases

2)查看有哪些用户表

 db2 :list tables 后者list tables for schema devuser

 mysql: show tables

3)查看表结构

db2:describe table table_name
mysql:describe table_name

4）取前n行数据

db2:select * from table_name fetch first n rows only
mysql:select * from table_name limit n

5) 显示数据库版本

db2: db2level 显示db2的版本号
mysql:select version()

6)连接数据库

db2:db2 connect to dbname user <user_name> using <passwd>

mysql:use database

7)显示表中列的信息

db2: describe table <table-name>

mysql: describle <table-name>

8)创建表

db2 :create table t1(c1 int);

mysql: create table t1(c1 int);

9)创建数据库

db2:create db mydb using codeset utf-8 territory cn

mysql:create db mydb

10)加载数据

db2: load client from db2.txt of del insert into table t1;

mysql: load data local infile “mysql.txt” into table t1;

11)执行文件中的sql

db2:db2 -tvf db2.sql

mysql: source mysql.sql;

12)重命名表

db2: rename table t1 to t2

mysql: alter table t1 rename t2

13）创建索引

db2: create index index_name on table_name (column_name)

mysql: create index index_name on table_name (column_name)

14)删除索引

db2 :drop index index_name;

mysql: alter table table_name drop index index_name

15)查看当前时间

db2: values (current timestamp)

mysql: select now()

16)导出表结构

db2: db2look -d mydb -t test -e -o test.sql

mysql:mysqldump -uroot -pdbpasswd -d dbname test>db.sql