Environmental
1.Operating system: redhat 6
Database: GBase8a MPP Cluster database
2. The configuration of transparent gateway
1) install the transparent gateway
Unpack the tar package: the tar XVF - [installation package]
Modify the transparent gateway corresponding permissions: chmod -r + x/install directory name
2) configuration transparent gateway
System parameters configuration file: [install directory]/conf/conf, properties
Content is as follows:
$cat conf. Properties
Gbase. Gt. Port=9898
Gbase. Gt. Encode=utf-8
Gbase. Gt. Pagesize=10000
Gbase. Gt. The load. The data. The type=0
Gbase. Gt. Gc. The paging. Query=1
Gbase. Gt. Wait. Timeout=7200
Data source configuration file: [install directory]/conf/datedource/gc_link properties
Content is as follows:
$cat dataSource/gc_link1 properties
[gbase8a cluster]
DataSource_IP=[source IP]
DataSource_port=5258
DataSource_dbname=[source library name]
DataSource_dbtype=gcluster
DataSource_user=* * * * * * *
DataSource_pwd=* * * * * * *
3) start the transparent gateway
$sh gt. Sh
4) transparent gateway close way
If the transparent gateway configuration failure need to manually kill the corresponding process, and then restart the
Ps - ef | grep GBaseGateway
Kill [process]
3. Configure the target database GBase8a MPP Cluster A
Gbase_8a_gcluster. You must configure parameters in CNF:
Gbase_dblink_gateway_ip=[transparent gateway IP, such as 192.18.16.11]
Gbase_dblink_gateway_port=[transparent gateway service ports, such as 9898]
Gbase_8a_gcluster. Optional parameters in CNF:
Gcluster_dblink_direct_data_exchange:
The default value is 1
Value is 1, said: the calculation of data from a data source cluster node sent directly to the target cluster,
Value is 0, said: data from the data source to gateway cluster, and then set forward by the gateway to target groups,
Note: only when the data source and target cluster between cluster nodes on the network is not connected directly, adjust the parameter to 0,
4. Use the db - link
Create the db - link:
The CREATE DATABASE LINK dblink_name connect to 'identified by' using 'gc_link';
Note: the dblink_name is the name of a custom db - link, at the back of the query, use the
The name for the db - link query, connect to 'identified by' is a fixed grammar,
Using is followed by the name of the data sources, such as the data source configuration file called gc_link. Properties, is here to fill the using gc_link,
Delete the db - link:
The DROP DATABASE LINK dblink_name;
Note: the current does not support the IF the EXISTS syntax,
To use the db - link:
SELECT * FROM table name @ dblink_name
5. The db - link query syntax constraint
This section describes the db - link query syntax constraint,
Note: if you simply use, such as insert into local_table select * from with
Source dblink table, then you can ignore this section of the constraints, in the presence of query db - the link table with the mixture of surface (example
Such as db - the link table JOIN the surface), or with the mixture of homologous db - the link table, you will need to comply with the following
Grammatical constraints, otherwise the query will be submitted to the grammatical errors,
Note: the same db - link refers to the db - the link of the same name, the name of different db - link become noncognate db - link, for example a t1 @ gc_link and t2 @ gc_link is considered to be homologous db - link two tables,
T1 @ gc_link and x1 @ gc_link2, considered is homologous db - the link table, and even gc_link gc_link2 when created, using the same data source, due to the different db - the link name, still think are homologous,
Db - link query syntax constraint is as follows:
1. Db queries - the link table can only appear in the top, or homologous dblink subquery, appear in the surface of the subquery, must be placed in base subquery,
For example: the following statement will be submitted to the grammar mistakes, because the db - the link appear on the surface of sub queries, must use base surrounded by the subquery,
Select * from t1 where the exists (select 1 from t2 @ gc_link as t2 where t2. Id=t1. Id);
This statement can be modified to the following form to ensure that conform to the rules of grammar:
Select * from t1 where the exists (select 1 from (select 1 from t2 @ gc_link) as t2 where t2. Id=t1. Id);
2. The homologous db - the link table can JOIN directly, dblink table with local table, base subquery, non homologous dblink table, a direct JOIN relation,
For example: t1 @ gc_link JOIN t2 @ gc_link is allowed; But the t1 @ gc_link JOIN t2 is not allowed, because the db - link table directly JOIN with the surface of the earth, to rewrite the SQL statement as the form:
... T (select * from t1 @ gc_link), t2...
3. Db - the link table subquery, banned from the surface, non homologous dblink table,
For example: select * from t1 @ gc_link where the exists (select 1 from t2);
Is not allowed, because the db table t1 cc-link @ gc_link subqueries in the t2, the surface can be rewritten as the following format:
Select * from (select * from t1 @ gc_link) t the where the exists (select 1 from t2);
4. In the order by or group by correlated subqueries, banned the db - the link table,