db2安装与使用

最新推荐文章于 2022-11-21 14:03:13 发布

lang20150928

最新推荐文章于 2022-11-21 14:03:13 发布

阅读量1.6k

点赞数

分类专栏： db2 文章标签：数据库 db2

本文链接：https://blog.csdn.net/m0_37607945/article/details/105551499

版权

db2 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

通过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

lang20150928

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
db2安装与使用

通过docker安装下载db2镜像docker pull ibmcom/db2:11.5.0.0根据镜像创建容器容器名称为mydb2 端口为 50000 数据库名称为trdj 密码为 xpardocker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSW...
复制链接

扫一扫