TheLazy.Dev
Portfolio GitHub

Blog

database

Oracle to GBase 8s DBLink Configuration Guide

congcong

Cong Li

Posted on July 10, 2024

Oracle to GBase 8s DBLink Configuration Guide

In a heterogeneous database environment, establishing a seamless connection between Oracle and GBase 8s is a critical task. DBLink provides an efficient way to connect and operate these two systems. This article will detail how to configure DBLink in an Oracle environment to connect to a GBase 8s database.

Software Version Information

  • GBase 8s: GBase8sV8.8_AEE_3.5.0_3NW1_6_86443b
  • Oracle: 11g

Steps to Configure Oracle to GBase 8s DBLink

1. Install unixODBC on Oracle 

yum install unixODBC
Enter fullscreen mode Exit fullscreen mode

2. Install gbasecsdk on Oracle 

tar -xvf clientsdk_3.5.0_3NW1_6_86443b_RHEL6_x86_64.tar 
./installclientsdk -i silent -DLICENSE_ACCEPTED=TRUE -DUSER_INSTALL_DIR=/opt/gbase
Enter fullscreen mode Exit fullscreen mode

3. Configure the ODBC configuration file (execute as root on Oracle) 

cat <<! >/etc/odbc.ini
[ODBC]
UNICODE=UCS-2
[odbc_demo]
Driver=/opt/gbase/lib/cli/iclit09b.so
Description=GBase ODBC DRIVER
Database=gbasedb
LogonID=gbasedbt
pwd=GBase123
Servername=gbase1
CLIENT_LOCALE=zh_cn.utf8
DB_LOCALE=zh_cn.utf8
TRANSLATIONDLL=/opt/gbase/lib/esql/igo4a304.so
!
Enter fullscreen mode Exit fullscreen mode

4. Configure environment variables 

export ODBCINI=/etc/odbc.ini 
export GBASEDBTDIR=/opt/gbase
Enter fullscreen mode Exit fullscreen mode

5. Configure the database connection sqlhosts file (execute as root on Oracle) 

cat <<! >$GBASEDBTDIR/etc/sqlhosts
gbase01 onsoctcp 172.16.3.47 9088
!
Enter fullscreen mode Exit fullscreen mode

6. Test ODBC 

isql odbc_demo   # Displays "connect!" if successful
Enter fullscreen mode Exit fullscreen mode

7. Configure Oracle HS configuration file (execute as oracle user on Oracle) 

cd $ORACLE_HOME/hs/admin
cat <<! >initodbc_demo.ora   # init<listener_instance_name>.ora
HS_FDS_CONNECT_INFO=odbc_demo
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_NLS_NCHAR = UCS2
HS_FDS_FETCH_ROWS=1000
HS_RPC_FETCH_REBLOCKING=OFF
set ODBCINI=/etc/odbc.ini
set GBASEDBTDIR=/opt/gbase
set GBASEDBTSERVER=gbase01
set GBASEDBTDIR=/opt/gbase
set GBASEDBTSQLHOSTS=/opt/gbase/etc/sqlhosts
set PATH=/opt/GBASE/gbase/bin:$PATH
set LD_LIBRARY_PATH=$GBASEDBTDIR/lib/:$GBASEDBTDIR/lib/cli:$GBASEDBTDIR/lib/esql:include:$LD_LIBRARY_PATH
set DELIMIDENT=y
!
Enter fullscreen mode Exit fullscreen mode

8. Configure Oracle listener (execute as oracle user on Oracle)

1. Modify listener.ora file 

cd $ORACLE_HOME/network/admin/
vi listener.ora
Enter fullscreen mode Exit fullscreen mode

Add the following lines:

# add for gbase8s start
(SID_DESC =
     (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = odbc_demo)
     (PROGRAM=dg4odbc)
    )
# add for gbase8s end
Enter fullscreen mode Exit fullscreen mode

2. Modify tnsnames.ora file 

cd $ORACLE_HOME/network/admin/
vi tnsnames.ora
Enter fullscreen mode Exit fullscreen mode

Add the following lines:

# add for dg4odbc used by gbase8s start
odbc_demo =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.47)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = odbc_demo)
    )
   (HS=OK)
  )
# add for dg4odbc used by gbase8s end
Enter fullscreen mode Exit fullscreen mode

9. Restart the listener (execute as oracle user on Oracle) 

lsnrctl reload
lsnrctl status  # Shows "odbc_demo" as normal, status unknown
tnsping odbc_demo  # Shows "OK" if successful
Enter fullscreen mode Exit fullscreen mode

10. Create a test table (execute as gbasedbt user on GBase 8s) 

export DELIMIDENT=y
dbaccess gbasedb -<<!
create table "TEST"(a int);
!
Enter fullscreen mode Exit fullscreen mode

11. Create DBLink and test (execute as oracle user on Oracle) 

su - oracle
sqlplus / as sysdba
SQL> create database link gbase8slink connect to "gbasedbt" identified by "GBase123" using 'odbc_demo';
SQL> select * from test@gbase8slink;
SQL> insert into test@gbase8slink values(9);
Enter fullscreen mode Exit fullscreen mode

12. Notes

  • The DELIMIDENT=y setting must be enabled on the GBase 8s side to distinguish case sensitivity in quoted identifiers.
  • When operating in Oracle, table names are converted to uppercase and enclosed in quotes, such as "SYSTABLES".
  • Column names are created as lowercase by default if not enclosed in double quotes. Oracle operations should reference them as lowercase or omit field names.
  • dg4odbc does not support DDL operations.

Following these steps, Oracle database administrators and developers can successfully configure DBLink to achieve efficient data interaction with GBase 8s. This provides robust support for cross-database queries and data synchronization.

💖 💪 🙅 🚩
congcong
Cong Li

Posted on July 10, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Factory Method Design Pattern Explained with PHP Examples
php Factory Method Design Pattern Explained with PHP Examples

November 30, 2024

Practical Implementation of Secure Payment and Password Protection in HarmonyOS Next E-commerce Applications
undefined Practical Implementation of Secure Payment and Password Protection in HarmonyOS Next E-commerce Applications

November 30, 2024

Building Serverless Agentic Workflows with Amazon Bedrock
genai Building Serverless Agentic Workflows with Amazon Bedrock

November 30, 2024

Practical Construction of a Secure Authentication System for Enterprise-Level Applications in HarmonyOS Next
undefined Practical Construction of a Secure Authentication System for Enterprise-Level Applications in HarmonyOS Next

November 30, 2024

Mastering SQlite Commands: A Beginner's Guide
webdev Mastering SQlite Commands: A Beginner's Guide

November 30, 2024