Introduction to CUBRID DBLink
When retrieving information from a database, it is often necessary to retrieve information from an external database.
In this way, if you use CUBRID DBLink to inquire information in an external database, you can access information in other databases.
CUBRID DBLink provides a function to inquire information in the databases of homogeneous CUBRID and heterogeneous Oracle and MySQL.
It has the advantage of being able to directly inquire information from an external database.
However, it is possible to set up multiple external databases, but when searching for information, it is possible to inquire information from only one other database.
CUBRID DBLink diagram
CUBRID DBLink supports DBLink between homogeneous and heterogeneous DBLinks.
Homogeneous DBLink diagram
If you look at the configuration diagram for inquiring information of a homogeneous database, you can use CCI in Database Server to connect to homogeneous brokers and inquire information from an external database.
Heterogeneous DBLink diagram
If you look at the configuration diagram for inquiring information in heterogeneous databases, you can inquire information in heterogeneous databases through GATEWAY.
GATWAY uses ODBC (Open DataBase Connectivity).
A gateway is a middleware that relays to connect to an external database server and is similar to a broker. The gateway connects the CUBRID Database Server to an external server which is Oracle/MySQL, to retrieve information from the external server and deliver it to the CUBRID Database Server.
A cubrid system including a gateway has a multi-hierarchical structure including cubrid_gateway, cub_gateway, and cub_cas_cgw as shown in the figure below.
cub_cas_cgw (CAS Gateway) acts as a common application server used by all the application clients that request connections. cub_cas_cgw also acts as the database server’s client and provides the connection to the database server upon the client’s request. The number of cub_cas_cgw(s) running in the service pool can be specified in the cubrid_broker.conf file, and this number is dynamically adjusted by cub_gateway.
cub_broker relays the connection between the application client and the cub_cas_cgw. That is, when an application client requests access, the cub_broker checks the status of the cub_cas_cgw through the shared memory, and then delivers the request to an accessible cub_cas_cgw . It then returns the processing results of the request from the cub_cas_cgw to the application client.
The cub_gateway는 also manages the server load by adjusting the number of cub_cas_cgw (s) in the service pool and monitors and manages the status of the cub_cas_cgw. If the cub_gateway는 delivers the request to cub_cas_cgw but the connection to cub_cas_cgw 1 fails because of an abnormal termination, it sends an error message about the connection failure to the application client and restarts cub_cas_cgw 1. Restarted cub_cas_cgw 1 is now in a normal stand-by mode, and will be reconnected by a new request from a new application client.
Enter the command below to start the GATEWAY.
The following message is returned if the GATEWAY is already running.
Enter the below command to stop the GATEWAY.
The following message is returned if the GATEWAY has stopped.
$ cubrid gateway stop
@ cubrid gateway stop
++ cubrid gateway is not running.
Enter the below command to restart the whole GATEWAY.
Checking GATEWAY Status
cubrid gateway status The cubrid gateway status utility allows you to check the gateway status such as the number of completed jobs and the number of standby jobs by providing various options.
GATEWAY status is the same as broker, so refer to Checking Broker Status.
cubrid gateway status [options] [expr]
Starting the GATEWAY together when the CUBRID service starts
To have GATEWAY started upon startup of the CUBRID service (cubrid service start), configure gateway in the service parameter of the cubrid.conf file.
CUBRID DBLINK settings
The settings for using CUBRID DBLink are different from those of homogeneous DBLink and heterogeneous DBLink.
Homogeneous DBLink Setting
If you look at the Homogeneous configuration diagram above, you need to connect to the broker of the external database, so you need to set up the broker for the external database.
This setting is the same as the general broker setting.
Heterogeneous DBLink Setting
It is necessary to set the information to connect to a heterogeneous type (Oracle/MySQL), and the heterogeneous setting value must be written in GATEWAY.
GATEWAY can be configured through the parameters of cubrid_gateway.conf .
APPL_SERVER is the part that sets the application server name of GATEWAY. To connect to an external server, it must be set to CAS_CGW.
CGW_LINK_SERVER should set the name of the external database to be used by connecting to CAS_CGW. Currently, supported databases are Oracle and MySQL.
CGW_LINK_SERVER_IP should set the IP address of the external database to be connected with CAS_CGW.
CGW_LINK_SERVER_PORT should set the port number of databaseto be connected with CAS_CGW.
CGW_LINK_ODBC_DRIVER_NAME must set the ODBC Driver name provided by the external database when connecting with CAS_CGW.
- For Windows, if the ODBC Driver is installed, the driver name can be found through the ODBC Data Source Manager.
- For Linux, the driver name must be written directly in odbcinit.ini.
CGW_LINK_CONNECT_URL_PROPERTY creates a property used in the connection string when connecting CAS_CGW to an external database.
The cubrid_gateway.conf file, the default gateway configuration file created when installing CUBRID, includes some parameters that must be modified by default. If you want to modify the values of parameters that are not included in the configuration file by default, you can add or modify one yourself.
The following is the content of the cubrid_gateway.conf file provided by default.
- GATEWAY SETTINGS FOR CONNECTION TO Oracle
CGW_LINK_ODBC_DRIVER_NAME =Oracle 12c ODBC driver
- GATEWAY SETTINGS FOR CONNECTION TO MySQL
CGW_LINK_ODBC_DRIVER_NAME =MySQL ODBC 8.0 Unicode Driver
Install ODBC Driver
You need to download and install Oracle/MySQL ODBC Driver from the site below.
Oracle ODBC Driver download site:
MySQL ODBC Driver download site:
Check and set ODBC Driver Name
In case of Linux, after installing unixODBC to set Oracle and MySQL ODBC Driver Name
Driver name must be written in /etc/odbcinit.ini file.
unixODBC Driver Manager is an open source ODBC driver manager that can be used with ODBC drivers on Linux and UNIX operating systems.
For instructions on how to install the unixODBC driver manager, refer to the url below.
unixODBC website: http://www.unixodbc.org/
[MySQL ODBC 8.0 Unicode Driver]
[Oracle 12c ODBC driver]
Description = Oracle ODBC driver for Oracle 12c
Driver = /usr/lib64/instantclient_12_2/libsqora.so.12.1
For reference, in the ondbcinst.ini setting, the driver names are MySQL ODBC 8.0 Unicode Driver and Oracle 12c ODBC driver, respectively.
How to use Cubrid DBLink
In the case of setting up homogeneous brokers and heterogeneous gateways, let’s look at how to write Query statements to inquire about database information.
There are two ways to write DBLINK Query statement for data inquiry.
First, how to query information from other databases by writing DBLINK syntax in the FROM clause
The Query statement below is a Query statement that inquires the remote_t table information of another database of IP 192.168.0.1.
SELECT * FROM DBLINK ('192.168.0.1:53000:demodb:user:password:','SELECT col1, col2 FROM remote_t') AS t(col1 int, col2 varchar(32));
Second, if you look at the above DBLINK Query, information for accessing other databases is the most basic information. Therefore, there is a risk that user information (id, password) may be exposed to the outside and there will be an inconvenience of having to write each time a Query is written.
If you use the CREATE SERVER statement for such trouble and information protection, it is simpler than the Query statement and helps to protect user information.
CREATE SERVER remote_srv1 ( HOST='192.168.0.1', PORT=53000, DBNAME=demodb, USER=user, PASSWORD='password');
SELECT * FROM DBLINK (remote_srv1, 'SELECT col1 FROM remote_t') AS t(col1 int);