The DBLINK built this time is to access the SQL SERVER database on the Win side of the Damon database on the Linux side. By configuring ODBC to connect to the SQL SERVER database, DM creates an ODBC DBLINK connection to realize the DBLINK between DM and SQL SERVER. The following are the specific operation steps.
- DBLINK overview:
DBLINK (Database LINK) as the name suggests, the Database LINK is like a telephone line. It is a channel. When we want to cross the local database and access the data in another data table, the local database must create a dblink of the remote database. Without dblink, the local database can access the remote database table like accessing the data of its own stored table Data in.
- DM database environment overview:
Operating system: CentOS 7.4 one thousand seven hundred and eight
Database version: 8.1-2-2-21
Database schema: single instance
IP address: 192.168 six point six three
- SQL server environment overview:
Operating system: Windows 10 Enterprise Edition
Database version: 10.50 one thousand and six hundred
IP address: 192.168 six point four one
- Overview of installation packages used by DBLINK:
unixODBC-2.3.0.tar.gz
freetds-1.2.21.tar.gz
Note: the DM database and SQLserver database have been installed, and the relevant installation packages required by Dblink have been uploaded to the host where the DM database is located.
1. Decompress and compile ODBC and Freetds
Unzip the installation package. The unzip command is as follows. The unzip process is omitted
[root@centos7 tmp]# cd /tmp [root@centos7 tmp]# tar -zxvf unixODBC-2.3.0.tar.gz [root@centos7 tmp]# tar -zxvf freetds-1.2.21.tar.gz
Compile the extracted installation package. The compilation command is as follows. The compilation process is omitted
[root@centos7 tmp]# cd unixODBC-2.3.0/ [root@centos7 unixODBC-2.3.0]# ./configure [root@centos7 unixODBC-2.3.0]# make && make install
[root@centos7 unixODBC-2.3.0]# cd .. [root@centos7 tmp]# cd freetds-1.2.21/ [root@centos7 freetds-1.2.21]# ./configure [root@centos7 freetds-1.2.21]# make && make install
2. Test connection
The WIn10 firewall has been closed, the network port between the two hosts is open, and the database SQL SERVER has been started, as shown in the figure.
Test the connection to the Linux host where the DM database is located
View the installation version details: / usr/local/bin/tsql -C
Connect to the database: / usr / local / bin / TSQL - H 192.168 6.41 -p 1433 -U sa -P 123456789 -D master
-H: IP address
-p: Database port
-U: User name
-P: Code
-D: System database name
-S: In freetds Conf specifies the server entry to connect to
View version information:
[root@centos7 ~]# /usr/local/bin/tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.2.21
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: auto
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: no
GnuTLS: no
MARS: yes
Test the connection, configure the correct information, and the test connection is successful, as shown below
[root@centos7 ~]# /usr/local/bin/tsql -H 192.168.6.41 -p 1433 -U sa -P 123456789 -D master locale is "zh_CN.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Setting master as default database in login packet 1>
3. Write configuration file
3.1. Freetds driver writing
freetds.conf contents are as follows
[root@centos7 ~]# vi /usr/local/etc/freetds.conf
[mssqlserver]
host = 192.168.6.41
port = 1433
tds version = 7.3
client charset = UTF-8
The test is connected through the server entry, and the test connection is successful
[root@centos7 ~]# /usr/local/bin/tsql -S mssqlserver -U sa -P 123456789 locale is "zh_CN.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>
3.2. ODBC driver writing
odbcinst.ini contents are as follows
[root@centos7 ~]# vi /usr/local/etc/odbcinst.ini [FreeTDS] # unixodbc driver name Description=ODBC of FreeTDS for MS SQL 2008 # brief introduction Driver=/usr/local/lib/libtdsodbc.so # Drive location
odbc.ini contents are as follows
[root@centos7 ~]# vi /usr/local/etc/odbc.ini [ODBC Data Sources] TEST2dsn= My First Test DSN [TEST2dsn] Driver = /usr/local/lib/libtdsodbc.so Description = My Second Test DSN Trace = No Server = 192.168.6.41 Database = master Port = 1433 TDS_Version = 7.3
4. Connect ODBC to SQL SERVER database
The root user tests the connection and specifies the connection name and specific user name / password
[root@centos7 ~]# isql -v TEST2dsn sa 123456789 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
dmdba user test, specify the connection name and specific user name / password
[dmdba@centos7 ~]$ isql -v TEST2dsn sa 123456789 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
5. Create a test table with SQL SERVER
The test table SQL is as follows
CREATE TABLE authors
(
au_id CHAR(3) NOT NULL,
au_fname VARCHAR(15) NOT NULL,
au_lname VARCHAR(15) NOT NULL,
phone VARCHAR(12) ,
address VARCHAR(20) ,
city VARCHAR(15) ,
state CHAR(2) ,
zip CHAR(5) ,
CONSTRAINT pk_authors PRIMARY KEY (au_id)
);
INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205 St','Bronx','NY','10468');
INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922 Baseline Rd','Boulder','CO','80303');
INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680','114 Horatio St','New York','NY','10014');
INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128','390 Serra Mall','Palo Alto','CA','94305');
INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442 Main St','Sarasota','FL','34236');
The query contents of the database are as follows:
6. Create DBLINK for DM database
Connect to the database and create a DBLINK database through ODBC
[dmdba@centos7 bin]$ ./disql SYSDBA/DM01SYSDBA:5236 The server[LOCALHOST:5236]:In normal open state Login usage time : 2.808(ms) disql V8 SQL> create link "SYSDBA"."TEST" connect 'ODBC' with "sa" identified by "123456789" using 'TEST2dsn'; Operation executed Elapsed time: 52.437(millisecond). Execution number:300. SQL>
7，Copy drive
Copy the driver to the bin directory of Dameng
[dmdba@centos7 bin]$ cp -r /usr/local/lib/* /dm8/dmdbms/bin
8. Query test
Connect to the database, and the DM database queries the AUTHORS table of SQL SERVER through the DBLINK of ODBC
[dmdba@centos7 bin]$ ./disql SYSDBA/DM01SYSDBA:5236 The server[LOCALHOST:5236]:In normal open state Login usage time : 0.954(ms) disql V8 SQL> select * from AUTHORS@TEST; Line number au_id au_fname au_lname phone address city state zip ----- ----- --------- ----------- ------------ -------------------- ------------- ----- ----- 1 A01 Sarah Buchman 18-496-7223 75 West 205 St Bronx NY 10468 2 A02 Wendy Heydemark 303-986-7020 2922 Baseline Rd Boulder CO 80303 3 A03 Hallie Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123 4 A04 Klee Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123 5 A05 Christian Kells 212-771-4680 114 Horatio St New York NY 10014 6 A06 Kellsey 650-836-7128 390 Serra Mall Palo Alto CA 94305 7 A07 Paddy O'Furniture 941-925-0752 1442 Main St Sarasota FL 34236 7 rows got Elapsed time: 181.113(millisecond). Execution number:300. SQL>
The query is successful. So far, the DBLINK of Dameng database accessing SQL SERVER database is created successfully.
9. Problem solving
After the dblink is created, an error is reported through the dblink query operation, and loading the library file fails.
[dmdba@centos7 bin]$ ./disql SYSDBA/DM01SYSDBA:5236 The server[LOCALHOST:5236]:In normal open state Login usage time : 0.979(ms) disql V8 SQL> select * from AUTHORS@TEST; select * from AUTHORS@TEST; Error near line 1[-2245]:DBLINK Failed to load library file. Elapsed time: 1.739(millisecond). Execution number:0. SQL>
The solution is to copy the driver under the lib directory of ODBC driver to the bin directory of dream without restarting the database instance. Normal query can be carried out after the copy is completed.
For more information, please visit Damon cloud adaptation Center: Damon cloud adaptation Center