通过Docker搭建数据库测试环境

Postgres

• 拉取镜像文件docker pull postgres
• 容器后台进程运行，向外暴露5432端口 docker run --name mypostgres -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 postgres . 运行成功后默认数据库登录名、密码皆为postgres，当然也可以手动设置登录名、密码
• 参考

测试SQL

CREATE TABLE T1 (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   ADDRESS        CHAR(50)
);

CREATE TABLE T2 (
   ID INT       NOT NULL,
   ID2 INT      NOT NULL,
   NAME           TEXT    NOT NULL,
   ADDRESS        CHAR(50)
);

CREATE TABLE T3 (
   ID2 INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   ADDRESS        CHAR(50)
);

CREATE TABLE T4 (
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   ADDRESS        CHAR(50)
);

EXPLAIN
SELECT *
FROM T1
JOIN (
    SELECT  T2.ID, T3.ID2
    FROM T2
    LEFT JOIN T3
    ON T2.ID2 = T3.ID2
) TT
ON T1.ID = TT.ID
JOIN T4
ON T1.ID = T4.ID;

Oracle 数据库

• 登录Docker Hub网站 https://hub.docker.com/_/oracle-database-enterprise-edition?tab=reviews ，填写必要的用户信息，才能下载和使用Oracle的 Docker Image.
• 本地执行 docker login 和 docker pull store/oracle/database-enterprise:12.2.0.1-slim . 我这里下载的是slim 版本的Image，这个版本的Image少了一些类似APEX之类的工具，所以Image 大小要小很多
• 参考

docker run -d -p 1521:1521 --name oracle store/oracle/database-enterprise:12.2.0.1-slim
docker exec -it oracle bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

connect sys as sysdba;
alter session set "_ORACLE_SCRIPT"=true;
create user test identified by test;
GRANT ALL PRIVILEGES TO test;

接下来就可以通过Datagrid连接数据库了。

connection type: service name
host: localhost
port: 1521
service: ORCLCDB.localdomain
user: test
password: test

查看Oracle Explain

explain plan for
    select * from t1;

select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));

参考

DB2

DB2 环境搭建

DB2 docker 镜像

docker pull ibmcom/db2:11.5.5.1
docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=db2_password -e DBNAME=testdb -v /opt/db2:/database ibmcom/db2:11.5.5.1

注意: DB2 从 11.5.6 版本开始，端口开始改为25000 (实际测试的docker镜像为 25010)，从而导致本地无法连接DB2，所以测试的时候，指定使用 低版本 11.5.5

• 排查参考: https://www.cnblogs.com/jun1019/p/5605850.html

参数说明:

• DB2INSTANCE (default: db2inst1) is to specify the Db2 Instance name ; 程序启动后，默认的用户名是 db2inst1, 可以通过root切换到该用户

查看程序版本 和 运行状态

su - db2inst1

[db2inst1@0cc8ce437f92 ~]$ cat /etc/services |grep db2c
db2c_db2inst1	50000/tcp

[db2inst1@2dcbc66ed252 db2inst1]$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11056" with level
identifier "0607010F".
Informational tokens are "DB2 v11.5.6.0", "s2106111000", "DYN2106111000AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".

[db2inst1@2dcbc66ed252 ~]$ db2pd -

Database Member 0 -- Active -- Up 0 days 01:00:42 -- Date 2021-08-16-06.03.07.520135

查看数据库和表信息

// 不清楚为啥，这个要先执行，否则后面在操作TESTDB的时候会报错
[db2inst1@452f6039e007 ~]$ db2 connect to TESTDB

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.6.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

[db2inst1@2dcbc66ed252 db2inst1]$ db2

db2 => connect to testdb user db2inst1 using db2_password

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.6.0
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB


db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = TESTDB
 Database name                        = TESTDB
 Local database directory             = /database/data
 Database release level               = 15.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =


db2 => list tables for schema TESTDB

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------

  0 record(s) selected.

db2 => list tables for schema TESTDB show detail

Table/View                                                                                                                       Schema                                                                                                                           Type  Creation time
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- --------------------------

  0 record(s) selected.

操作数据表

注意：DB2 中创建的默认Schema 是 DB2INST1

db2 => create table tb1(col1 varchar(10));
DB20000I  The SQL command completed successfully.

db2 => list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
TB1                             DB2INST1        T     2021-08-16-05.36.09.246628

  1 record(s) selected.

db2 => create table TESTDB.tb1(col1 varchar(10));

db2 => list tables for schema TESTDB

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
TB1                             TESTDB          T     2021-08-16-05.37.17.054242


INSERT INTO TESTDB.tb1 values ('v1');
INSERT INTO TESTDB.tb1 values ('v2');
INSERT INTO TESTDB.tb1 values ('v3');
SELECT * FROM TESTDB.tb1;


db2 => quit
DB20000I  The QUIT command completed successfully.

查看SQL PLAN

1. 如果第一次执行,请先 connect to dbname， 执行 db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,'DB2INST1')" 建立执行计划表
2. 设置成解释模式,并不真正执行下面将发出的sql命令，db2 set current explain mode explain
3. 执行你想要分析的sql语句, db2 "SELECT DISTINCT t1.b FROM TESTDB.t1 t1 LEFT JOIN TESTDB.t2 t2 ON t1.a = t2.a GROUP BY t1.b"\
4. 取消解释模式，db2 set current explain mode no
5. 执行计划输出到文件db2exmt.out, db2exfmt -d TESTDB -g TIC -w -l -s % -n % -o db2exmt.out