目錄
- 1 簡介
- 2 Docker安裝DB2
- Spring Boot整合DB2
- 代碼
- PS：Docker中安裝DB2詳細教程
1 簡介
DB2是IBM的一款優秀的關系型數據庫，簡單學習一下。
2 Docker安裝DB2
為了快速啟動，直接使用Docker來安裝DB2。先下載鏡像如下：
docker pull ibmcom/db2:11.5.0.0
啟動數據庫如下：
docker run -itd \ --name mydb2 \ --privileged=true \ -p 50000:50000 \ -e LICENSE=accept \ -e DB2INST1_PASSWORD=pkslow \ -e DBNAME=testdb \ ibmcom/db2:11.5.0.0
這樣獲得的數據庫，具體信息如下：
連接URL：jdbc:db2://localhost:50000/testdb
用戶名：db2inst1
密碼：pkslow
在IDEA上連接如下：
默認的Schema為DB2INST1，測試SQL如下：
創建表：
CREATE TABLE EMPLOYEE_SALARY (DEPTNO CHAR(3) NOT NULL, DEPTNAME VARCHAR(36) NOT NULL, EMPNO CHAR(6) NOT NULL, SALARY DECIMAL(9,2) NOT NULL WITH DEFAULT);
插入數據：
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('001', 'IT', '001322', 80000.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('001', 'IT', '001323', 20000.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('002', 'Architecture', '001324', 3220.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('002', 'Architecture', '001325', 8000.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('003', 'Front Office', '001326', 13320.00);
INSERT INTO EMPLOYEE_SALARY (DEPTNO, DEPTNAME, EMPNO, SALARY) VALUES ('001', 'IT', '001327', 5433.00);
查詢：
SELECT * FROM DB2INST1.EMPLOYEE_SALARY;
Spring Boot整合DB2
添加相關依賴：
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.ibm.db2</groupId>
<artifactId>jcc</artifactId>
<version>11.5.0.0</version>
</dependency>
</dependencies>
配置相關屬性：
server.port=8082 spring.datasource.url=jdbc:db2://localhost:50000/testdb spring.datasource.username=db2inst1 spring.datasource.password=pkslow spring.jpa.generate-ddl=true
創建Entity：
package com.pkslow.springboot.jpa.db2.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "EMPLOYEE", schema = "DB2INST1")
public class Employee {
@Id
private Long id;
private String name;
}
創建Repository類用于操作數據庫：
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
}
最后，寫個方法測試一下：
@Configuration
@Slf4j
public class TestConfig {
@Autowired
private EmployeeRepository repository;
@PostConstruct
public void init() {
Employee employee1 = new Employee(1L, "Larry Deng");
Employee employee2 = new Employee(2L, "LarryDpk");
Employee employee3 = new Employee(3L, "Pkslow");
repository.deleteAll();
repository.save(employee1);
repository.save(employee2);
repository.save(employee3);
List<Employee> employees = StreamSupport.stream(repository.findAll().spliterator(), false)
.collect(Collectors.toList());
employees.forEach(e -> log.info(e.toString()));
}
}
啟動后，日志打印正常如下：
2022-02-28 23:26:22.476 INFO 60847 --- [ main] c.pkslow.springboot.jpa.db2.TestConfig : Employee(id=1, name=Larry Deng) 2022-02-28 23:26:22.476 INFO 60847 --- [ main] c.pkslow.springboot.jpa.db2.TestConfig : Employee(id=2, name=LarryDpk) 2022-02-28 23:26:22.476 INFO 60847 --- [ main] c.pkslow.springboot.jpa.db2.TestConfig : Employee(id=3, name=Pkslow)
表數據如下：
代碼
代碼請看GitHub: https://github.com/LarryDpk/pkslow-samples
References：
Docker Image
Statements Insert
PS：Docker中安裝DB2詳細教程
docker安裝db2 1、查詢可安裝的db2鏡像2、下載db2鏡像3、安裝鏡像4、切換用戶并運行DB25、常用命令6、遇到的坑7、參考文章 1、查詢可安裝的db2鏡像
這里是單機安裝，直接安裝db2express-c，介紹網址 : libmcom / db2express-c
docker search db2express-c1
2、下載db2鏡像
docker pull ibmoms/db2express-c
這里我選用的是ibmoms/db2express-c，嘗試過網上教程上的很多鏡像，安裝完之后都有坑-_-||。
3、安裝鏡像
docker run -it --name db2 -p 50000:50000 -e DB2INST1_PASSWORD=db2 -e LICENSE=accept ibmoms/db2express-c:latest bash
-p 50000:50000 允許遠程的客戶端可以從50000 端口連接到數據庫實例.通過指定 -e DB2INST1_PASSWORD=db2root-pwd 參數, 你可以為缺省的Db2實例用戶db2inst1設置密碼.注意：這里“DB2INST1”是用戶名，而“b2root-pwd”是密碼。通過指定-e LICENSE=accept參數, 表示你接受了使用Db2軟件的許可證協議.
有教程說要添加 –network=host ，我在添加后各種莫名的報錯╮(╯▽╰)╭
4、切換用戶并運行DB2
$ su - db2inst1 //切換用戶 $ db2start //啟動DB2 $ db2sampl //創建默認數據庫“SAMPLE”
5、常用命令
db2 create db [dbname] #創建數據庫 db2 list db directory #列出所有數據庫 db2 list active databases #列出所有激活的數據庫 db2 get db cfg #列出所有數據庫配置
6、遇到的坑
1.DB2 啟動SQL5043N
解決方案: DB2 啟動SQL5043N
官方解答: infocenter答案
[db2inst1@connections ~]$ db2level
DB21085I This instance or install (instance name, where applicable:
“db2inst1”) uses “64” bits and DB2 code release “SQL10014” with level
identifier “0205010E”.
Informational tokens are “DB2 v10.1.0.4”, “s140509”, “IP23584”, and Fix Pack
“4”.
Product is installed at “/opt/ibm/db2/V10.1”.
[db2inst1@connections ~]$ db2start
08/20/2014 22:19:54 0 0 SQL5043N Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.
SQL1063N DB2START processing was successful.
[db2inst1@connections ~]$ db2set DB2COMM=tcpip
[db2inst1@connections ~]$ db2stop
2014-08-20 22:21:17 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@connections ~]$ db2start
08/20/2014 22:21:20 0 0 SQL5043N Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.
SQL1063N DB2START processing was successful.
[db2inst1@connections ~]$ db2 get dbm cfg
這里重點看SVCENAME屬性,修改命令：db2 update dbm cfg using SVCENAME 50000
[db2inst1@connections ~]$ db2 update dbm cfg using SVCENAME 50000
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
[db2inst1@connections ~]$ db2stop
2014-08-20 22:22:07 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
啟動成功
[db2inst1@connections ~]$ db2start
08/20/2014 22:22:11 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@connections ~]$ db2 get dbm cfg | grep SVC
