DM Database

https://eco.dameng.com/document/dm/zh-cn/pm
Admin
DM Database is a distributed, parallel relational database management system supporting MPP...
Tokens:3,567,414
Snippets:18,789
Trust Score:9.3
Update:1 week ago
Show doc for...
Tokens:
Raw
### Setup for FLOAT_SHOW examples

Source: https://eco.dameng.com/document/dm/zh-cn/pm/environment-variable-settings.html

Setup commands to create a table T1 with a FLOAT column and insert a value.

```sql
SQL> DROP TABLE T1;
SQL> CREATE TABLE T1(X FLOAT);
SQL> INSERT INTO T1 VALUES(11.00011);
SQL> COMMIT;
```

--------------------------------

### Run DM command-line installer

Source: https://eco.dameng.com/document/dm/zh-cn/pm/install-uninstall.html

Starts the command-line installation of DM. Use this when the system has no graphical interface.

```bash
./DMInstall.bin -i
```

--------------------------------

### Setup for SET LONG examples

Source: https://eco.dameng.com/document/dm/zh-cn/pm/environment-variable-settings.html

Creates a table TEST with a TEXT column and inserts a sample string used in the LONG examples.

```sql
SQL> CREATE TABLE TEST(C1 TEXT);
SQL> INSERT INTO TEST VALUES('DIsql是DM数据库的一个命令行客户端工具，用来与DM数据库服务器进行交互。');

```

--------------------------------

### MPP_LOCAL example setup

Source: https://eco.dameng.com/document/dm/zh-cn/pm/sql-tuning.html

DMMPP example: creates a hash-distributed table and inserts 7 rows. Used with MPP_LOCAL hint to demonstrate single-node access in DMMPP.

```sql
DROP TABLE TEST;
CREATE TABLE TEST(C1 INT,C2 INT,C3 INT)
DISTRIBUTED BY HASH(C1) ;
insert into test select level,level,level from dual connect by level <8;
commit;
```

--------------------------------

### Begin a transaction

Source: https://eco.dameng.com/document/dm/zh-cn/pm/go-rogramming-guide.html

Example call to Begin to start a transaction.

```go
tx, err := db.Begin()
```

--------------------------------

### Get Session ID and Start PROC_1

Source: https://eco.dameng.com/document/dm/zh-cn/pm/sql-appendix.html

In session 1, gets the session ID and calls PROC_1 to start a long-running operation.

```sql
CopySQL> SELECT SESSID();
行号     SESSID()        
---------- --------------------
1          139929502562720

SQL> CALL PROC_1();

```

--------------------------------

### SHOW ALL example output

Source: https://eco.dameng.com/document/dm/zh-cn/pm/environment-variable-settings.html

Example output of SHOW ALL, listing all environment variables and their current values, followed by the release and current user.

```sql
SQL> SHOW ALL
AUTOCOMMIT OFF 
AUTORECONN ON
AUTOTRACE OFF
CHAR_CODE DEFAULT 
chardel " " 
CMD_EXEC ON 
colsep " " 
CONSOLE_PRINT ON 
CURSOR FORWARDONLY 
CTRL_INFO 0 
DEFINE & (hex 26) 
DESCRIBE DEPTH 1 LINENUM OFF INDENT ON 
ECHO ON 
EXPLAIN_MODE 0
FEEDBACK 6 
FLOAT_SHOW 0
HEADSEP "|" (hex 7c)
HEADING ON 
ISQL_MODE 0 
KEEPDATA OFF 
LONG 800
LINESIZE 94
SQL_LINESHOW ON 
LOBCOMPLETE OFF 
LOCAL_CODE DEFAULT 
NEST_COMMENT OFF 
NEWPAGE 1 
NULL_ASNULL OFF 
NULL_SHOW ON 
PAGESIZE 14 
PREFIX_STR "" 
RETRY_CONN 3
RETRY_CONN_TIME 0
READ_ONLY OFF
RESULT MAX ROWS 0 
SERVEROUTPUT OFF 
SCREENBUFSIZE 20971520 
SQLCODE OFF 
SQL_LINESHOW ON 
SQLPROMPT "SQL> " 
TIMING ON 
TIME OFF 
TRIMSPOOL OFF
VERIFY ON 
WRAP ON 
release DM Database 64 V8 03134284389-20250411-268655-10000
USER "SYSDBA"

```

--------------------------------

### Get metadata with browsing interface

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dbms_metadata-package.html

Example 3: Uses the browsing interface of DBMS_METADATA to get metadata. The query result is the same as in Example 2.

```sql
CopySELECT DBMS_METADATA.GET_DDL('TABLE','TIMECARDS','RESOURCES');
```

--------------------------------

### Hierarchical Query with START WITH (Example 2)

Source: https://eco.dameng.com/document/dm/zh-cn/pm/check-phrases.html

Example 2: hierarchical query with a START WITH condition that selects the root node where DEP_NAME is '总公司'. The result is a tree rooted at that node.

```sql
CopySELECT HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT CONNECT BY PRIOR DEP_NAME=HIGH_DEP START WITH DEP_NAME='总公司';

```

--------------------------------

### 使用 setup.py 安装 dmDjango

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dmdjango-drive.html

在源码目录下执行 python setup.py install 命令，编译并安装 dmDjango。适用于 Windows 和 Linux 操作系统。

```bash
Copy//进入到setup.py所在的源码目录，执行以下命令：

python setup.py install

```

--------------------------------

### DESCRIBE package example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/common-commands.html

Example of using DESCRIBE to get the structure of the package PersonPackage.

```sql
SQL>describe  PersonPackage;
```

--------------------------------

### HELP DEFINE example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/common-commands.html

Example of using the HELP command to display information about the DEFINE command. The output shows the command's title, description, and syntax.

```sql
SQL> HELP DEFINE
DEFINE
------
定义替换变量，并对其指定一个CHAR类型值，或者显示单个或所有变量的信息。
DEF[INE] [variable] | [variable = text]
```

--------------------------------

### SETUP: 创建系统包与打印输出

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dbms_stats-package.html

必须在调用包内过程或函数前执行，若尚未创建系统包，先调用 SP_CREATE_SYSTEM_PACKAGES 创建 DBMS_STATS 包，并设置 SERVEROUTPUT ON 以打印消息。

```sql
CopySP_CREATE_SYSTEM_PACKAGES (1,'DBMS_STATS');
SET SERVEROUTPUT ON;  //PRINT需要设置这条语句，才能打印出消息

```

--------------------------------

### DESCRIBE stored procedure example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/common-commands.html

Example of using DESCRIBE to get the structure of the stored procedure PROC_1.

```sql
SQL>describe  PROC_1;
```

--------------------------------

### CopyMKDIR/MKDIR examples

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dmasm-introduce.html

Examples of creating a directory and creating a directory with intermediate directories.

```DMASMTOOL
CopyMKDIR '+DMDATA/A'
MKDIR -P '+DMDATA/NODIR/BB'
```

--------------------------------

### Hierarchical Query Without START WITH (Example 1)

Source: https://eco.dameng.com/document/dm/zh-cn/pm/check-phrases.html

Example 1: hierarchical query without a START WITH condition. It uses all rows in the table as root nodes and performs a preorder traversal. The result includes all possible hierarchies.

```sql
CopySELECT HIGH_DEP, DEP_NAME FROM OTHER.DEPARTMENT CONNECT BY PRIOR DEP_NAME = HIGH_DEP;

```

--------------------------------

### Query Result for Example 2

Source: https://eco.dameng.com/document/dm/zh-cn/pm/check-phrases.html

Query result for Example 2, showing the hierarchy starting from the specified root.

```text
CopyHIGH_DEP   DEP_NAME
---------- --------------
NULL       总公司
总公司     财务部
总公司     采购部
总公司     服务部
服务部     企业服务部
服务部     读者服务部
读者服务部 书籍阅览服务部
读者服务部 书籍借阅服务部
服务部     网络服务部

```

--------------------------------

### 配置数据库启动命令 INST_STARTUP_CMD

Source: https://eco.dameng.com/document/dm/zh-cn/pm/configuration-description.html

INST_STARTUP_CMD 用于指定数据库实例的启动命令，支持多种操作系统及启动方式。

```text
INST_STARTUP_CMD = /opt/dm/bin/dmserver
```

```text
INST_STARTUP_CMD = service dmserverd restart
```

```text
INST_STARTUP_CMD = c:\dm\bin\dmserver
```

```text
INST_STARTUP_CMD = net start 注册服务名
```

--------------------------------

### Create tables and indexes for EXPLAIN example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/query-optimization.html

Creates two tables T1 and T2, an index on T1.C1, and inserts sample data for the execution plan example.

```sql
    CREATE TABLE T1(C1 INT,C2 CHAR);
    CREATE TABLE T2(D1 INT,D2 CHAR);
    CREATE  INDEX  IDX_T1_C1  ON  T1(C1);
    INSERT INTO T1 VALUES(1,'A');
    INSERT INTO T1 VALUES(2,'B');
    INSERT INTO T1 VALUES(3,'C');
    INSERT INTO T1 VALUES(4,'D');
    INSERT INTO T2 VALUES(1,'A');
    INSERT INTO T2 VALUES(2,'B');
    INSERT INTO T2 VALUES(5,'C');
    INSERT INTO T2 VALUES(6,'D');

```

--------------------------------

### SUBSTRBB negative start example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Returns two bytes starting from the end of the binary value. The result is 0x4058.

```sql
SELECT SUBSTRBB(0x2A3B4058,-2,2);
```

--------------------------------

### Prepare test data for WRAP example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/environment-variable-settings.html

Prepares the environment for WRAP examples: sets ISQL_MODE to 1 and LINESIZE to 10, then creates and populates a test table.

```sql
SQL> set isql_mode 1
SQL> set linesize 10
SQL> drop table test;
SQL> create table test(c1 varchar(50), c2 varchar(50));
SQL> insert into test values('damengshujuku', 'damengshujuku');
SQL> commit;

```

--------------------------------

### SUBSTRBB positive start example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Returns two bytes starting at byte position 2. The result is 0x3B40.

```sql
SELECT SUBSTRBB(0x2A3B4058,2,2);
```

--------------------------------

### WEEK mode 3 example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Example of WEEK with mode 3 (Monday start, range 1-53). The result is 1.

```sql
SELECT WEEK(DATE'2024-12-31',3);
```

--------------------------------

### Show dmserver help

Source: https://eco.dameng.com/document/dm/zh-cn/pm/start-shut-down.html

Prints usage, parameter descriptions, and examples for dmserver.exe. Use this when unsure about startup parameters.

```plaintext
Copydmserver help

```

--------------------------------

### WEEK mode 2 example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Example of WEEK with mode 2 (Sunday start, range 1-53). The result is 52.

```sql
SELECT WEEK(DATE'2024-12-31',2);
```

--------------------------------

### Connection.explain syntax and example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dmpython-interface.html

Returns the execution plan for a given SQL statement. The example connects, calls explain on 'select * from dual', and prints the plan output.

```python
CopyConnection.explain(sql)
```

```python
Copyimport dmPython
conn = dmPython.connect('SYSDBA/Dmsys_123')
info = conn.explain('select * from dual')
print(info)
1   #NSET2: [1, 1, 1] 
2     #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE); INFO_BITS(0); prjt_batch_exec(0); spl_info(NULL)
3       #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL(SYSDUAL); btr_scan(1); need_slct(0); prejudge_iescn(0)
```

--------------------------------

### WEEK mode 1 example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Example of WEEK with mode 1 (Monday start, range 0-53). The result is 53.

```sql
SELECT WEEK(DATE'2024-12-31',1);
```

--------------------------------

### WEEK mode 0 example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Example of WEEK with mode 0 (Sunday start, range 0-53). The result is 52.

```sql
SELECT WEEK(DATE'2024-12-31',0);
```

--------------------------------

### 查看 install.log 安装日志示例

Source: https://eco.dameng.com/document/dm/zh-cn/pm/install-uninstall.html

install.log 示例，记录安装过程中的模块安装、注册服务、卸载等信息，格式为时间 + 日志类型（INFO/WARN/ERROR/FATAL）+ 日志内容。

```text
2022-09-14 14:55:30 

[INFO] 安装达梦数据库...

2022-09-14 14:55:30 

[INFO] 安装 基础 模块...

2022-09-14 14:55:31 

[INFO] 安装 服务器 模块...

2022-09-14 14:55:31 

[INFO] 安装 客户端 模块...

2022-09-14 14:55:34 

[INFO] 安装 驱动 模块...

2022-09-14 14:55:35 

[INFO] 安装 手册 模块...

2022-09-14 14:55:35 

[INFO] 安装 服务 模块...

2022-09-14 14:55:35 

[INFO] 注册ODBC完成。

```

--------------------------------

### 使用源码安装 dmPython

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dmpython-installation.html

在 dmPython 源码目录下执行 python setup.py install 命令进行安装。适用于 Windows 和 Linux 操作系统。

```bash
D:\dmdbms\drivers\python\dmPython>python setup.py install

```

--------------------------------

### Query Result for Example 1

Source: https://eco.dameng.com/document/dm/zh-cn/pm/check-phrases.html

Query result for Example 1, showing the hierarchical traversal starting from every node as root.

```text
CopyHIGH_DEP    DEP_NAME
---------- --------------
NULL         总公司
总公司        财务部
总公司        采购部
总公司        服务部
服务部        企业服务部
服务部        读者服务部
读者服务部    书籍阅览服务部
读者服务部    书籍借阅服务部
服务部        网络服务部
总公司        服务部
服务部        企业服务部
服务部        读者服务部
读者服务部    书籍阅览服务部
读者服务部    书籍借阅服务部
服务部        网络服务部
总公司        采购部
总公司        财务部
服务部        网络服务部
服务部        读者服务部
读者服务部    书籍阅览服务部
读者服务部    书籍借阅服务部
服务部        企业服务部
读者服务部    书籍借阅服务部
读者服务部    书籍阅览服务部

```

--------------------------------

### Create and Start Queue MY_QUEUE

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dbms_aqadm-package.html

Example 2: Creates the queue MY_QUEUE in schema SSS and starts it for enqueue and dequeue operations.

```sql
CopyDBMS_AQADM.CREATE_QUEUE(
        QUEUE_NAME  => 'SSS.MY_QUEUE',
        QUEUE_TABLE => 'SSS."@TAB"'
);
DBMS_AQADM.START_QUEUE(QUEUE_NAME  => 'SSS.MY_QUEUE');
```

--------------------------------

### EXPLAIN_MODE 2 example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/environment-variable-settings.html

Sets EXPLAIN_MODE to 2 and runs SELECT 1, which is equivalent to executing EXPLAIN SELECT 1;. The output shows the execution plan.

```sql
SQL> set EXPLAIN_MODE 2
SQL> SELECT 1;

1   #NSET2: [1, 1, 1] 
2     #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE); INFO_BITS(0); prjt_batch_exec(0); spl_info(NULL)
3       #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL(SYSDUAL); btr_scan(1); need_slct(0); prejudge_iescn(0)

```

--------------------------------

### SHOW RELEASE example output

Source: https://eco.dameng.com/document/dm/zh-cn/pm/environment-variable-settings.html

Example output of SHOW RELEASE, showing the release version string.

```sql
SQL> SHOW RELEASE
release DM Database 64 V8 03134284389-20250411-268655-10000

```

--------------------------------

### init.d 环境手动注册服务：创建启动服务的连接

Source: https://eco.dameng.com/document/dm/zh-cn/pm/dm8_linux-script-manual.html

创建服务脚本到各运行级别的符号链接，以支持系统启动和停止时自动启动/停止服务。

```bash
ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc0.d/K02DmServiceDMSERVER

ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc1.d/K02DmServiceDMSERVER

ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc2.d/S98DmServiceDMSERVER

ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc3.d/S98DmServiceDMSERVER

ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc4.d/S98DmServiceDMSERVER

ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc5.d/S98DmServiceDMSERVER

ln -s /etc/rc.d/init.d/DmServiceDMSERVER /etc/rc.d/rc6.d/K02DmServiceDMSERVER

```

--------------------------------

### SUBSTRING on column example

Source: https://eco.dameng.com/document/dm/zh-cn/pm/function.html

Example 1: applies SUBSTRING to the NAME column of PRODUCTION.PRODUCT, extracting 2 characters starting from position 3.

```sql
SELECT NAME,SUBSTRING(NAME FROM 3 FOR 2) FROM PRODUCTION.PRODUCT;
```

--------------------------------

### Create LIST partition table sales (first version)

Source: https://eco.dameng.com/document/dm/zh-cn/pm/definition-statement.html

Creates a LIST-partitioned table sales with partitions for fixed cities and a default partition. This is the first version of the example.

```sql
CREATE  TABLE  sales( 
sales_id  INT,
saleman	  CHAR(20),
saledate  DATETIME,
city       CHAR(10)
)
PARTITION BY LIST(city)(
PARTITION p1 VALUES ('北京', '天津'),
PARTITION p2 VALUES ('上海', '南京', '杭州'),
PARTITION p3 VALUES ('武汉', '长沙'),
PARTITION p4 VALUES ('广州', '深圳'),
PARTITION p5 VALUES (default)
);
```
Tokens:
Raw