docker下使用DB2
1.查询可安装的db2镜像
benjamin@docker:~$ docker images |grep -i db2
ibmcom/db2express-c latest 7aa154d9b73c years ago .71GB
2、从docker-hub上拉取镜像到本地 (默认最新版latest)
docker pull ibmcom/db2express-c
3.启动db2容器并初始化密码
benjamin@docker:~$ docker run -it --network=host -p50000: -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 :: UTC
5、启动实例
[db2inst1@docker ~]$ db2start
SQL1063N DB2START processing was successful.
6、查看运行状态
[db2inst1@docker ~]$ db2pd - Database Member -- Active -- Up days :: -- Date ---10.27.52.857206
7、查看已经创建的数据库
[db2inst1@docker ~]$ db2 list dbdirectory SQL1031N The database directory cannot be found on the indicated file system. SQLSTATE=
说明目前没有创建数据库
8、查看数据库和补丁版本
[db2inst1@docker ~]$ db2level DB21085I This instance or install (instance name, where applicable: "db2inst1") uses ""bits and DB2 code release "SQL10055" with level identifier "0606010E". Informational tokens are "DB2v10.5.0.5", "s141128", "IP23633", and Fix Pack "". Product is installed at"/home/db2inst1/sqllib".
9、显示运行的Db2容器信息,在宿主机上运行
-bash-4.2# docker ps | grep db2 3492501e4f07 ibmcom/db2express-c:latest "/entrypoint.sh bash" seconds ago Up seconds wizardly_mestorf
10、关闭数据库实例,后面需要启动容器时指定宿主机目录和容器目录的映射关系,目的是在这些目录中创建数据库。
db2stop exit
11、重启容器,通过-v选项指定宿主机目录和容器目录的映射关系:
-bash-4.2# docker run -it --network=host -p50000: -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 % / overlay 148G 55G 94G % / tmpfs .9G .9G % /dev tmpfs .9G .9G % /sys/fs/cgroup /dev/mapper/rhel-root 148G 55G 94G % /db2data shm 64M 64M % /dev/shm
13、设置目录权限,其中db2data为表空间的目录、log为日志目录、dbpath为创建数据库时的dbpath。
[root@docker /]# chmod db2data [root@docker /]# su - db2inst1 Last login: Tue Jan :: UTC onconsole [db2inst1@docker ~]$ [db2inst1@docker ~]$ cd /db2data [db2inst1@docker db2data]$ touch [db2inst1@docker db2data]$ rm [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 drwxrwxr-x db2inst1 db2inst1 Jan : data drwxrwxr-x db2inst1 db2inst1 Jan : dbpath drwxrwxr-x db2inst1 db2inst1 Jan : 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 -rw------- db2inst1 db2inst1 Jan : SQLLPATH.TAG -rw------- db2inst1 db2inst1 Jan16 : S0000002.LOG -rw------- db2inst1 db2inst1 Jan16 : S0000001.LOG -rw------- db2inst1 db2inst1 Jan16 : S0000000.LOG [db2inst1@docker LOGSTREAM0000]$ db2 get db cfg for mydb | grep -i primary Number of primary log files (LOGPRIMARY) =
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. 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" hours ago Up 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 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 -rw-r--r-- root root Jan : 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" minutes ago Up minutes eager_ride 3d221e0aaa69 ibmcom/db2express-c:latest "/entrypoint.sh bash" minutes ago Exited () minutes ago tender_fermat 088dc03e36c4 ibmcom/db2express-c:latest "/entrypoint.sh bash" hours ago Up 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: -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 :: UTC 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. 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
最新文章
- POJ 3436:ACM Computer Factory(最大流记录路径)
- 安卓问题集-Installation error: INSTALL_PARSE_FAILED_MANIFEST_MALFORMED
- 图像特征提取三大法宝:HOG特征,LBP特征,Haar特征(转载)
- asp.net 防止页面刷新或后退引起重复提交
- Myeclipse 创建 Web Maven项目
- 你的变量究竟存储在什么地方 &;&; 全局内存
- bzoj5253 [2018多省省队联测]制胡窜
- Docker 镜像之进阶篇
- U66785 行列式求值
- leetcode 最后一个单词的长度 python
- 【版本更新】开发工具DevExtreme发布v18.2.7|附下载
- ASP.NET core 2.1部署到 Centos 7
- windows下安装mingw-w64
- java NIO 文章
- POI写docx文件table中的单元格水平、垂直对齐
- 【比赛】NOIP2017 列队
- mysql中,如何查看数据库元数据(metadata)的字符集?
- GCC参数详解 二
- Innodb间隙锁,细节讲解(转)
- DHT11资料