如何在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 foribmcom/db2express-c:latest
3、启动容器：
-bash-4.2# docker run -it --network=host -p50000:50000 -e DB2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept ibmcom/db2express-c:latest bash
Changing password for user db2inst1.
New password: BAD PASSWORD: The passwordcontains the user name in some form
Retype new password: passwd: allauthentication 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 days00:00:06 -- Date 2018-01-16-03.30.51.059168
7、查看已经创建的数据库
[db2inst1@shcallisto-25 ~]$ db2 list dbdirectory
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 "DB2v10.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 -p50000: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 passwordcontains the user name in some form
Retype new password: passwd: allauthentication 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 onconsole
[db2inst1@shcallisto-25 ~]$
[db2inst1@shcallisto-25 ~]$ cd /db2data
[db2inst1@shcallisto-25 db2data]$ touch 1
[db2inst1@shcallisto-25 db2data]$ rm 1
[db2inst1@shcallisto-25 db2data]$ mkdirdata
[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 1605:16 data
drwxrwxr-x 2 db2inst1 db2inst1 6 Jan 1605:16 dbpath
drwxrwxr-x 2 db2inst1 db2inst1 6 Jan 1605: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]$db2start
[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]$ db2activate 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 Jan16 05:22 S0000002.LOG
-rw------- 1 db2inst1 db2inst1 4104192 Jan16 05:22 S0000001.LOG
-rw------- 1 db2inst1 db2inst1 4104192 Jan16 05:22 S0000000.LOG
[db2inst1@shcallisto-25 LOGSTREAM0000]$ db2get 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]$ db2connect to mydb
Database Connection Information
Database server = DB2/LINUXX8664 10.5.5
SQLauthorization 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 aminute 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、停止已经启动的db2container，以避免表空间访问冲突；
-bash-4.2# dockerps -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 -p50000: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 passwordcontains the user name in some form
Retype new password: passwd: all authenticationtokens updated successfully.
[root@shcallisto-25 /]#
[root@shcallisto-25 /]# su - db2inst1
Last login: Tue Jan 16 07:42:24 UTC 2018 onconsole
[db2inst1@shcallisto-25 ~]$
[db2inst1@shcallisto-25 ~]$ db2start
SQL1063N DB2START processing was successful.
[db2inst1@shcallisto-25 ~]$ db2 connect tomydb
Database Connection Information
Database server = DB2/LINUXX8664 10.5.5
SQLauthorization 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 nrows 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-8territory cn
mysql:create db mydb
10)加载数据
Db2: load client from db2.txt of del insertinto 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 ontable_name (column_name)
14)删除索引
Db2 :drop index index_name;
mysql: alter table table_name drop indexindex_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 dbnametest>db.sql
作者：陆川
转自:http://blog.csdn.net/qq_39154147/article/details/79219414