通过docker安装下载db2镜像
docker pull ibmcom/db2:11.5.0.0
根据镜像创建容器
容器名称为mydb2 端口为 50000 数据库名称为trdj 密码为 xpar
docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=xpar -e DBNAME=trdj -v /docker/db2:/database ibmcom/db2:11.5.0.0
进入到容器中
docker exec -ti mydb2 bash -c "su - db2inst1"
[root@centos ~]# docker exec -ti mydb2 bash -c "su - db2inst1"
Last login: Thu Apr 16 07:21:17 UTC 2020
[db2inst1@3ee57359b00d ~]$ ls
set_rec_values.sh sqllib
[db2inst1@3ee57359b00d ~]$ pwd
/database/config/db2inst1
[db2inst1@3ee57359b00d ~]$ db2 attach to db2inst1
Instance Attachment Information
Instance server = DB2/LINUXX8664 11.5.0.0
Authorization ID = DB2INST1
Local instance alias = DB2INST1
[db2inst1@3ee57359b00d ~]$ db2 detach
DB20000I The DETACH command completed successfully.
[db2inst1@3ee57359b00d ~]$ db2 create db test
DB20000I The CREATE DATABASE command completed successfully.
[db2inst1@3ee57359b00d ~]$ db2 connect to test
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = TEST
[db2inst1@3ee57359b00d ~]$ exit
logout
查看实例参数
[root@centos ~]# docker exec -ti mydb2 bash -c "su - db2inst1"
Last login: Thu Apr 16 07:41:36 UTC 2020
[db2inst1@3ee57359b00d ~]$ db2 get dbm cfg
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x1500
CPU speed (millisec/instruction) (CPUSPEED) = 1.298947e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 32
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) = /database/config/db2inst1/sqllib/java/jdk64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /database/config/db2inst1/sqllib/db2dump/ $m
Current member resolved DIAGPATH = /database/config/db2inst1/sqllib/db2dump/DIAG0000/
Alternate diagnostic data directory path (ALT_DIAGPATH) =
Current member resolved ALT_DIAGPATH =
Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = OFF
SYSADM group name (SYSADM_GROUP) = DB2IADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Cluster manager =
Database manager authentication (AUTHENTICATION) = SERVER
Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /database/data
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 65536
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Global instance memory (% or 4KB) (INSTANCE_MEMORY) = AUTOMATIC(772282)
Member instance memory (% or 4KB) = GLOBAL
Agent stack size (AGENT_STACK_SZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 65535
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time and redo index build (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = dee57359
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) = db2c_db2inst1
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE
SSL server keydb file (SSL_SVR_KEYDB) =
SSL server stash file (SSL_SVR_STASH) =
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) =
SSL client stash file (SSL_CLNT_STASH) =
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0
Number of FCM buffers (FCM_NUM_BUFFERS) = AUTOMATIC(4096)
FCM buffer size (FCM_BUFFER_SIZE) = 32768
Number of FCM channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)
FCM parallelism (FCM_PARALLELISM) = AUTOMATIC(1)
Node connection elapse time (sec) (CONN_ELAPSE) = 10
Max number of node connection retries (MAX_CONNRETRIES) = 5
Max time difference between nodes (min) (MAX_TIME_DIFF) = 60
db2start/db2stop timeout (min) (START_STOP_TIME) = 10
WLM dispatcher enabled (WLM_DISPATCHER) = NO
WLM dispatcher concurrency (WLM_DISP_CONCUR) = COMPUTED
WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO
WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5
Communication buffer exit library list (COMM_EXIT_LIST) =
Current effective arch level (CUR_EFF_ARCH_LVL) = V:11 R:5 M:0 F:0 I:0 SB:0
Current effective code level (CUR_EFF_CODE_LVL) = V:11 R:5 M:0 F:0 I:0 SB:0
Keystore type (KEYSTORE_TYPE) = NONE
Keystore location (KEYSTORE_LOCATION) =
[db2inst1@3ee57359b00d ~]$
查看数据库列表
[db2inst1@3ee57359b00d ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
Database alias = TRDJ
Database name = TRDJ
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 =
Database 2 entry:
Database alias = TEST
Database name = TEST
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 =
删除数据库
[db2inst1@3ee57359b00d ~]$ db2 drop database TRDJ
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@3ee57359b00d ~]$ db2 drop database TEST
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@3ee57359b00d ~]$ db2 list db directory
SQL1057W The system database directory is empty. SQLSTATE=01606
再次创建数据库
[db2inst1@3ee57359b00d ~]$ db2 create db trdj
DB20000I The CREATE DATABASE command completed successfully.
[db2inst1@3ee57359b00d ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = TRDJ
Database name = TRDJ
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 =
[db2inst1@3ee57359b00d ~]$
查看db2段端口
[db2inst1@3ee57359b00d ~]$ db2 get dbm cfg|grep SVCENAME
TCP/IP Service name (SVCENAME) = db2c_db2inst1
SSL service name (SSL_SVCENAME) =
[db2inst1@3ee57359b00d ~]$ cat /etc/services | grep db2c_db2inst1
db2c_db2inst1 50000/tcp
db2c_db2inst1_ssl 50001/tcp
可以看到端口为50000
通过远程客户端访问DB2
执行脚本新增表和数据
create table t_user(id integer not null primary key,username varchar(20),password varchar(20));
insert into t_user (id,username,password) values (1,'sunny','sunnny123');
然后按F5执行
执行查询语句
select * from t_user;
java客户端访问数据库
pom依赖
<dependency>
<groupId>com.ibm.db2</groupId>
<artifactId>db2jcc</artifactId>
<version>1.4.2</version>
</dependency>
<dependency>
<groupId>com.ibm.db2</groupId>
<artifactId>db2jcc_license_cu</artifactId>
<version>1.4.2</version>
</dependency>
编写代码
package com.xquant.platform.test.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class ConnectBD2 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Properties properties = new Properties();
properties.setProperty("user", "db2inst1");
properties.setProperty("password", "xpar");
properties.setProperty("currentSchema", "NULLID");
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
conn = DriverManager.getConnection("jdbc:db2://192.168.100.217:50000/TRDJ", properties);
String sql = "select * from t_user";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
System.out
.println(rs.getString("id") + " " + rs.getString("username") + " " + rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
执行结果如下:
通过Druid数据源访问数据库
添加maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.5</version>
</dependency>
package com.xquant.platform.test.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.alibaba.druid.pool.DruidDataSource;
public class ConnectBD2 {
public static void main(String[] args) {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.ibm.db2.jcc.DB2Driver");
dataSource.setUrl("jdbc:db2://192.168.100.217:50000/TRDJ:currentSchema=NULLID;");
dataSource.setUsername("db2inst1");
dataSource.setPassword("xpar");
dataSource.setTestWhileIdle(false);
dataSource.setValidationQuery("select 1 from SYSIBM.dual");
try {
Connection conn = dataSource.getConnection();
// 查询自己指定的表
String sql = "select * from t_user";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));
}
// 查询系统函数
sql = "select sysdate from SYSIBM.dual";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
dataSource.close();
}
}
}
执行结果如下:
1 sunny sunnny123
2020-04-17 02:26:23.0