Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to build the environment of DM8 MPP". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

DM8 MPP environment building

1. Prepare the environment

This example configures a two-node MPP. Both nodes are equipped with two network cards, one connected to the internal network switching module and the other to the external switch. The instance names of the two nodes are MEVI01 and MEVI02, respectively. The related IP, port and other plans are shown in the following table.

Instance name

MAL_INST_HOST

MAL_INST_PORT

MAL_HOST

MAL port

MPP_SEQNO

MEVI01

192.168.18.171

5236

192.168.10.171

5269

0

MEVI02

192.168.18.172

5236

192.168.10.172

5269

one

The version of the DM server used by each EP of the DM MPP should be consistent, and it should also be noted that the operating system bits, size mode, time zone and time settings of each host of the EP should be the same, otherwise unexpected errors may occur.

two。 Configuration parameter file

1) configure dm.ini

First, create databases on EP01 and EP02, respectively, and users can use DM's graphical client tool Database configuration Assistant or command line tool dminit to create databases.

When creating a database on each EP, it is required that some initialization parameters must be the same for all EP. We recommend that the database initialization parameters of each EP should be consistent to avoid errors.

Configure the dm.ini of the two instances respectively.

Modify the following parameters of EP01's dm.ini:

INSTANCE_NAME = MEVI01

PORT_NUM = 5236

MAL_INI = 1

MPP_INI = 1

Modify the following parameters of EP02's dm.ini:

INSTANCE_NAME = MEVI02

PORT_NUM = 5236

MAL_INI = 1

MPP_INI = 1

2) configure dmmal.ini

Configure the dmmal.ini for the two EP as follows, the configuration is exactly the same, and the EP can copy each other. Dmmal.ini and dm.ini are placed in the same directory.

[MAL_INST1]

MAL_INST_NAME = MEVI01

MAL_HOST = 192.168.10.171

MAL_PORT = 5269

MAL_INST_HOST = 192.168.18.171

MAL_INST_PORT = 5236

[MAL_INST2]

MAL_INST_NAME = MEVI02

MAL_HOST = 192.168.10.172

MAL_PORT = 5269

MAL_INST_HOST = 192.168.18.172

MAL_INST_PORT = 5236

3) configure dmmpp.ctl

Dmmpp.ctl is a binary file, which cannot be configured directly by users. You need to configure dmmpp.ini first.

Configure dmmpp.ini as follows:

[SERVICE_NAME1]

MPP_SEQ_NO = 0

MPP_INST_NAME = MEVI01

[SERVICE_NAME2]

MPP_SEQ_NO = 1

MPP_INST_NAME = MEVI02

Use the dmctlcvt tool to convert dmmpp.ini to dmmpp.ctl,dmctlcvt tools in the "bin" subdirectory of the DM installation directory. The dmmpp.ctl generated by the transformation needs to be placed in the same directory as dm.ini.

[dmdba@mpp1 ~] $dmctlcvt TYPE=2 SRC=/dmdata/MEVI/dmmpp.ini DEST=/dmdata/MEVI/dmmpp.ctl

[dmdba@mpp2 ~] $dmctlcvt TYPE=2 SRC=/dmdata/MEVI/dmmpp.ini DEST=/dmdata/MEVI/dmmpp.ctl

3. Run MPP

After the previous steps, the DM MPP environment has been configured. Start the DM database instances of MEVI01 and MEVI02 respectively (regardless of the order), the DM MPP system can run normally, and users can log in to any EP to operate the database.

[dmdba@mpp1 ~] $DmServiceMEVI01 start

Starting DmServiceMEVI01: [OK]

[dmdba@mpp2 ~] $DmServiceMEVI02 start

Starting DmServiceMEVI02: [OK]

4. Establish a distribution table

DM MPP supports hash distribution, random distribution, replication distribution, range distribution and LIST distribution of table data. Users can choose the appropriate distribution type according to the actual situation.

The data distribution type and specific settings of MPP are specified when creating the table. The syntax is as follows:

CREATE [[GLOBAL] TEMPORARY] TABLE

:: = |

: = ({,} [, {,}]) [ON COMMIT ROWS] [] []

: = [ON COMMIT ROWS] [] AS []

:: = DISTRIBUTED [|]

| | DISTRIBUTED BY [] ({,}) |

| DISTRIBUTED BY RANGE ({,}) ({,})

| | DISTRIBUTED BY LIST () ({,}) |

:: = VALUES LESS THAN ({,}) ON

| | VALUES EQU OR LESS THAN ({,}) ON |

:: = VALUES ({,}) ON

Test:

# create users and tablespaces:

# the following operations are performed on any EP node, and all EP nodes are visible:

SQL > create tablespace MEVI datafile'/ dmdata/MEVI/MEVI01.DBF' size 64

SQL > create user mevi identified by "mevi123456" default tablespace mevi

SQL > grant RESOURCE,VTI to mevi

SQL > conn mevi/mevi123456

# create a hash distribution table T_HASH, and the distribution is listed as C1.

SQL > create table t_hash (C1 int, c2 char (10)) distributed by hash (C1)

# create a random distribution table T_RANDOM.

SQL > create table t_random (C1 int, c2 char (10)) distributed randomly

# create a replication distribution table T_FULLY.

SQL > create table t_fully (C1 int, c2 char (10)) distributed fully

# create a range distribution table T_RANGE, and the distribution is listed as C1.

SQL > create table t_range (C1 int, c2 char (10)) distributed by range (C1) (values equ or less than (100) on mevi01, values less than (maxvalue) on mevi02)

# create LIST distribution table T_LIST, and the distribution is listed as C1.

SQL > create table t_list (C1 int, c2 char (10)) distributed by list (C1) (values (6) on mevi01, values (8) on mevi02)

# create a range horizontal partition table of the hash distribution table.

Create table t_hash_range_partition

(C1 int, c2 char (10), c3 char (10))

Partition by range (C1)

(

Partition part_1 values less than (0)

Partition part_2 values less than (10)

Partition part_3 values less than (100)

Partition part_4 values less than (maxvalue)

)

Distributed by hash (C1)

When creating a distribution table, users should be aware of the following usage restrictions:

1) the distribution table created in stand-alone mode is the same as the normal table, but the distribution table with specified instance name (such as range distribution table and LIST distribution table) cannot be created.

2) create a distribution table in MPP mode. If no column is specified, it defaults to RANDOMLY (random) distribution table.

3) distributed column types do not support BLOB, CLOB, IMAGE, TEXT, LONGVARCHAR, BIT, BINARY, VARBINARY, LONGVARBINARY, BFILE, interval type, virtual column and user-defined type

4) HASH distribution, RANGE distribution, LIST distribution allow updates of distributed columns, and support distributed column updates of tables containing large field columns, but tables and heap tables containing INSTEAD OF triggers are not allowed to update distributed columns

5) for FULLY (copy) distribution tables, only update and delete operations of single table queries are supported, and query items or conditional expressions cannot contain ROWID pseudo-column expressions.

6) RANGE (range) distribution table and LIST (list) distribution table, the distribution column must be consistent with the distribution column value list, and the specified instance name must not be duplicated

7) both the reference column and the referenced column of the reference constraint must contain distribution columns, and the distribution is exactly the same

8) randomly distributed tables do not support UNIQUE indexes.

5. Fast data loading

DM MPP is especially suitable for the storage and processing of massive data, so it is often faced with the need to load a large amount of data from one or some historical databases to MPP systems in applications. In order to meet the needs of fast loading of massive data, DM provides a fast loading tool dmfldr, which can quickly load large amounts of data on DM stand-alone and MPP systems.

Dmfldr is a command line tool, and you must specify the necessary execution parameters when using it.

Dmfldr supports two data loading modes in MPP environment: client distribution mode and local distribution mode, which are set by the parameter MPP_CLIENT. When using the client distribution mode, the data is distributed on the dmfldr client and then sent directly to the specified EP; when using the local distribution mode, each EP corresponds to a dmfldr and a piece of data, and each dmfldr only selects the data corresponding to this node and sends it, regardless of the data of other nodes. The client distribution mode is used by default.

6. Stop the MPP system

When you need to stop the operation of the DM MPP system, you only need to stop the DM instance of each EP, and there is no special order requirement.

If a certain EP fails and stops during the operation of the DM MPP system, the whole MPP system will not be able to serve normally. All current user sessions will be disconnected by the system and cannot be logged in globally, only locally. Therefore, in order to ensure the high availability of the MPP system, it is recommended to adopt the deployment scheme of combining DM MPP and data daemon.

7. Restrictions on the use of MPP

DM MPP supports most of the features of stand-alone DM, but there are some restrictions on the use of some small feature points, as follows:

1) in MPP environment, it is not supported to create reference constraints for SET NULL or SET DEFAULT constraint checking rules

2) creation of external tables and interval partition tables are not supported in MPP environment

3) the ADD [COLUMN] [] clause of a table is not supported in MPP environment.

4) Bitmap index, spatial index and array index are not supported in MPP environment.

5) the dimension tables involved in bitmap join index in MPP environment need to be distributed by FULLY

6) ONLINE options for indexing are not supported in MPP environment

7) WITH CHECK OPTION operation of views is not supported in MPP environment

8) flashback query is not supported in MPP environment

9) materialized view logs are not supported in MPP environment

10) each EP of MPP should specify the same encoding format when creating the database, otherwise it may cause garbled problems.

11) the INI parameter MVCC_RETRY_TIMES in MPP environment is invalid, and an error is reported directly when a MVCC conflict occurs.

12) DBMS_ job packages are not supported in MPP environment

13) the log auxiliary table in MPP environment does not support FULLY distribution.

This is the end of the content of "how to build the Environment of DM8 MPP". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.