docker下使用DB2

1.查询可安装的db2镜像

benjamin@docker:~$ docker images |grep -i db2
ibmcom/db2express-c             latest              7aa154d9b73c        2 years ago         1.71GB

2、从docker-hub上拉取镜像到本地 （默认最新版latest）

docker pull ibmcom/db2express-c

3.启动db2容器并初始化密码

benjamin@docker:~$  docker run -it --network=host -p50000:50000 -e DB2INST1_PASSWORD=db2inst1-pwd -e LICENSE=accept ibmcom/db2express-c:latest bash
WARNING: Published ports are discarded when using host network mode
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@docker /]# 

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

4、切换到实例用户db2inst1

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

5、启动实例

[db2inst1@docker ~]$ db2start
SQL1063N  DB2START processing was successful.

6、查看运行状态

[db2inst1@docker ~]$ db2pd -

Database Member 0 -- Active -- Up 0 days 00:00:39 -- Date 2018-03-26-10.27.52.857206

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

[db2inst1@docker ~]$ db2 list dbdirectory

SQL1031N The database directory cannot be found on the indicated file system.

SQLSTATE=58031

说明目前没有创建数据库

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

[db2inst1@docker ~]$ 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@docker/]# 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@docker /]# chmod 777 db2data

[root@docker /]# su - db2inst1

Last login: Tue Jan 16 05:15:10 UTC 2018 onconsole

[db2inst1@docker ~]$

[db2inst1@docker ~]$ cd /db2data

[db2inst1@docker db2data]$ touch 1

[db2inst1@docker db2data]$ rm 1

[db2inst1@docker db2data]$ mkdir data

[db2inst1@docker db2data]$ mkdir dbpath

[db2inst1@docker db2data]$ mkdir log

[db2inst1@docker db2data]$ pwd

/db2data

[db2inst1@docker 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@docker ~]$ db2 get dbm cfg| grep -i dbpath

 Default database path                       (DFTDBPATH) =/home/db2inst1

14、创建用户数据库mydb

[db2inst1@docker db2data]$db2start

[db2inst1@docker 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@docker db2data]$ db2 update db cfg for mydb using newlogpath /db2data/log

DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

16、激活数据库

[db2inst1@docker db2data]$ db2 activate db mydb

DB20000I The ACTIVATE DATABASE command completed successfully.

17、确认日志生效

[db2inst1@docker db2data]$ cd /db2data/log

[db2inst1@docker log]$ ls

NODE0000

[db2inst1@docker log]$ cd *

[db2inst1@docker NODE0000]$ ls

LOGSTREAM0000

[db2inst1@docker NODE0000]$ cd *

[db2inst1@docker LOGSTREAM0000]$ ls

S0000000.LOG  S0000001.LOG S0000002.LOG  SQLLPATH.TAG

[db2inst1@docker 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@docker LOGSTREAM0000]$ db2 get db cfg for mydb | grep -i primary

 Number of primary log files                (LOGPRIMARY) = 3

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

[db2inst1@docker sqllib]$ db2 catalog db mydb as mydb on /db2data/dbpath

DB20000I The CATALOG DATABASE command completed successfully.

[db2inst1@docker sqllib]$ db2 connect 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@docker /]#

[root@docker /]# su - db2inst1

Last login: Tue Jan 16 07:42:24 UTC 2018 onconsole

[db2inst1@docker ~]$

[db2inst1@docker ~]$ db2start

SQL1063N DB2START processing was successful.

[db2inst1@docker ~]$ db2 connect to mydb

 

  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