达梦

# 注意 ulimit 参数

# 添加环境变量到 profile ： 重要
export DM_HOME=/opt/dmdbms
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
export PATH=$PATH:$DM_HOME/bin

# install
./DMInstall.bin -i



# prepare
mkdir -p /data/dm
chown dmdba -Rv /data/dm

# init 
# su - dmdba

# UTF8
PORT_NUM=5002
DB_NAME=DMDSC
CHARSET=1
CASE_SENSITIVE=0
dminit \
CHARSET=$CHARSET CASE_SENSITIVE=$CASE_SENSITIVE PAGE_SIZE=32 EXTENT_SIZE=16 PORT_NUM=$PORT_NUM DB_NAME=$DB_NAME \
LENGTH_IN_CHAR=1 BLANK_PAD_MODE=1  \
LOG_SIZE=2048   path=/data/dm/$PORT_NUM 

# 初始化数据，等出现  SYSTEM IS READY.  可以按CTRL+C 中止
$dmserver /data/dm/$PORT_NUM/$DB_NAME/dm.ini

# 注册为 systemd 服务
# /opt/dmdbms/script/root/dm_service_installer.sh -t dmserver -p dmdsc -dm_ini /data/dm/5002/DMDSC/dm.ini 

  Created symlink from /etc/systemd/system/multi-user.target.wants/DmServicedmdsc.service to 
  /usr/lib/systemd/system/DmServicedmdsc.service.
  创建服务(DmServicedmdsc)完成
# systemctl status DmServicedmdsc


### SQL
SELECT SF_GET_UNICODE_FLAG();
SELECT SF_GET_CASE_SENSITIVE_FLAG();
SELECT SF_GET_LENGTH_IN_CHAR();


## 性能优化
sudo mkdir -p /data/dm/{app,dm_data,dm_redo,dm_arch,dm_backup} 
sudo chown dmdba -Rv /data/dm/

DB_NAME=DMDSC
DATA_PATH=/data/dm/dm_data
REDO_PATH="$DATA_PATH/$DB_NAME/dm_redo"
echo $REDO_PATH
/opt/dmdbms/bin/dminit \
PAGE_SIZE=32 EXTENT_SIZE=32 BLANK_PAD_MODE=1  \
CASE_SENSITIVE=0   LENGTH_IN_CHAR=1  \
LOG_SIZE=2048  DB_NAME=$DB_NAME \
PATH=$DATA_PATH \
LOG_PATH=$REDO_PATH/redo01.log LOG_SIZE=2048  \
LOG_PATH=$REDO_PATH/redo02.log LOG_SIZE=2048  \
LOG_PATH=$REDO_PATH/redo03.log LOG_SIZE=2048  \
MAX_SESSIONS=6000 CHARSET=1  PORT_NUM=5001  \

# 根据需要修改参数
PORT_NUM                        = 5002

/opt/dmdbms/bin/dmserver /data/dm/dm_data/DMDSC/dm.ini


## for GBK
CHARSET=0  LENGTH_IN_CHAR=1

# 归档文件大小和限制，都是M, ARCH_SPACE_LIMIT 一般保留3到7个完整备份。
ARCH_FILE_SIZE = 128
ARCH_SPACE_LIMIT = 5000


# first start
$ /opt/dmdbms/bin/dmserver  /data/dm/DAMENG/dm.ini


# service
#cp /opt/dmdbms/bin/service_template/DmService /etc/init.d/

注册为服务
# /opt/dmdbms/script/root/dm_service_installer.sh -t dmserver -p dmdsc -dm_ini /data/dm/dm_data/DMDSC/dm.ini

Created symlink /etc/systemd/system/multi-user.target.wants/DmServicedmdsc.service → /usr/lib/systemd/system/DmServicedmdsc.service.
Finished to create the service (DmServicedmdsc)

systemctl start DmServicedmdsc

服务名为 DmService+-p 参数后的内容
-t 指服务类型是 dmserver；-p 为服务名的后缀；-dm_ini 为实例的 dm.ini 文件的绝对路径。

add :
ulimit -n 1024000
INI_PATH=/data/dm/DAMENG/dm.ini

chkconfig DmService on

/etc/init.d/DmService restart

# logs
/opt/dmdbms/log/dm_DMSERVER_202110.log 

# cli
/opt/dmdbms/bin/disql SYSDBA/SYSDBA@127.0.0.1:5001


# create user
create tablespace "DATA_0001" datafile '/data/dm/DAMENG/DATA_0001.dbf' size 8192 autoextend on next 100 maxsize 33554431 CACHE = NORMAL;


create user "USER001" identified by "**********"
limit SESSION_PER_USER 300, CONNECT_IDLE_TIME 1440, CONNECT_TIME 600, FAILED_LOGIN_ATTEMPS 3, password_grace_time 10
default tablespace "DATA_0001"
default index tablespace "DATA_0001";

grant "DBA","PUBLIC","RESOURCE","SOI","VTI" to "USER001";
grant CREATE SESSION to "USER001";

ALTER USER "用户名" IDENTIFIED BY "密码";


## 常用配置
ENABLE_MONITOR=1  MONITOR_TIME=1   开启慢日志


LENGTH_IN_CHAR=1 UNICODE_FLAG=1     长度以字符为单位


CHARSET/UNICODE_FLAG 字符集选项。取值：0代表GB18030，1代表UTF-8,2代表韩文字符集EUC-KR。默认为0。


达梦数据库，应用适配过程的异常处理，原数据库MySQL
1、SQL 写法不支持，应用执行遇到报错：【无效的ORDER BY 字句】【不是GROUP BY 表达式】
登录数据库，执行这个SQL: sp_set_para_value(2,'COMPATIBLE_MODE',4);
执行成功后，重启数据库即可。
修改为4 后，关于group 和order 之前不兼容的语法报错，就会变得正常。

作者：旻璿
链接：https://www.jianshu.com/p/1c29849f2f8e


数据迁移  https://www.cnblogs.com/zqntx/p/14000183.html
DM 达梦数据库 生产环境 参数优化 说明   https://www.cndba.cn/dave/article/4321
https://halo.sherlocky.com/archives/dm7-setup-optimize

打开归档 

$ disql SYSDBA/SYSDBA@localhost:5236
SQL> ALTER DATABASE MOUNT;
executed successfully
used time: 345.272(ms). Execute id is 0.

SQL> ALTER DATABASE ARCHIVELOG; 
SQL> sp_set_para_value(1,'ARCH_INI',1);
DMSQL executed successfully
used time: 11.779(ms). Execute id is 53300.

SQL> ALTER DATABASE ADD ARCHIVELOG 'DEST = /data/dm/dm_arch, TYPE = LOCAL, FILE_SIZE= 1024, SPACE_LIMIT = 10240';
executed successfully
used time: 0.788(ms). Execute id is 0.

SQL> ALTER DATABASE OPEN;
executed successfully
used time: 337.293(ms). Execute id is 0.

关闭归档
ALTER DATABASE MOUNT;
ALTER DATABASE NOARCHIVELOG; 
ALTER DATABASE OPEN;

JDK

<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>Dm8JdbcDriver18</artifactId>
    <version>8.1.1.49</version>
</dependency>
<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>DmDialect-for-hibernate5.3</artifactId>
    <version>8.1.1.49</version>
</dependency>
<dependency>
    <!--注意：只有这个版本的hibernate兼容达梦数据库 -->
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.3.18.Final</version>

————————————————
DM JDBC 数据库连接驱动具体位置是 dm.jdbc.driver.DmDriver
————————————————
pring.datasource.dm.jdbc-url=jdbc:dm://1.1.1.1:5236/DM_DB
spring.datasource.dm.username=username
spring.datasource.dm.password=password
spring.datasource.dm.driver-class-name=dm.jdbc.driver.DmDriver
 
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.DmDialec
————————————————
datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:dm://xxx.xxx.xxx.xx:5236?SCHEMA=test
    username: SYSDBA
    password: SYSDBA
    databaseName: test
    dialect: org.hibernate.dialect.DmDialect
    driver-class-name: dm.jdbc.driver.DmDriver
jpa:
    database-platform: org.hibernate.dialect.DmDialect
    #    database: DM
    show-sql: true
————————————————

url="jdbc:dm://(192.168.138.234:5236,192.168.138.235:5237)?comOra=true&loadBalance=true&loadBalanceFreq=10000"


url="jdbc:dm://dmrac?comOra=true&loadBalance=true&loadBalanceFreq=10000"


----------------------
DMDSC JDBC 
spring.datasource.driverClassName=dm.jdbc.driver.DmDriver
spring.datasource.url=jdbc:dm://DMDB?dmsvcconf=/app/dmsvc.conf

cat /app/dmsvc.conf 
TIME_ZONE=(480)
LANGUAGE=(cn)
DMDB=(1.1.1.1:5237,2.2.2.2:5237)
[DMDB]
CLUSTER=(DSC)
EP_SELECTOR=(0)
AUTO_RECONNECT=(1)


MAX_SESSIONS