CUBRID
  • Introduction to Manual
    • Manual Contents
    • Glossary
    • Manual Conventions
    • Version Name and Version String Conventions
  • Introduction to CUBRID
    • System Architecture
      • Process Structure
      • Database Volume Structure
        • Permanent Volume
        • Temporary Volume
        • Backup Volume
      • Database Server
      • Broker
      • Interface Module
    • CUBRID Characteristics
  • Installing and Upgrading
    • Installing and Running CUBRID
      • Supported Platforms and System Requirements
      • Compatibility
      • Installing and Running CUBRID on Linux
      • Installing and Running CUBRID on Windows
      • Installing with a Compressed Package
        • Installing CUBRID with tar.gz on Linux
        • Installing CUBRID with zip on Windows
    • Configuring Environment Variables
      • CUBRID Environment Variables
      • OS Environment and Java Environment Variables
      • Configuring the Environment Variable
      • Language & Charset Setting
    • Port Setting
      • Default Ports for CUBRID
      • Ports for CUBRID HA
      • Ports for CUBRID Manager Server
      • Ports for CUBRID Java Stored Procedure Server
    • Upgrade
      • Cautions during upgrade
      • Upgrading from CUBRID 9.2/9.3/10.0/10.1/10.2/11.0 to CUBRID 11.2
        • DB migration
        • Parameter configuration
      • Upgrading from CUBRID 9.1 to CUBRID 11.2
        • DB migration
        • Parameter configuration
      • Upgrading From CUBRID 2008 R4.1/R4.3/R4.4 To CUBRID 11.2
        • DB migration
        • Parameter configuration
      • Upgrading From CUBRID 2008 R4.0 or Earlier Versions To CUBRID 11.2
        • DB migration
        • Parameter configuration
    • Database Migration under HA Environment
      • HA migration from CUBRID 2008 R2.2 or higher to CUBRID 11.2
      • HA Migration from CUBRID 2008 R2.0/R2.1 to CUBRID 11.2
    • Uninstalling CUBRID
      • Uninstalling CUBRID in Linux
      • Uninstalling CUBRID in Windows
  • Getting Started
    • Starting the CUBRID Service
      • Shell Command
      • CUBRIDService or CUBRID Service Tray
      • Creating Databases
      • Starting Database
    • Query Tools
      • CSQL Interpreter
    • Management Tools
      • Running SQL with CUBRID Manager
      • Migrating schema/data with CUBRID Migration Toolkit
    • Drivers
  • CSQL Interpreter
    • Introduction to the CSQL Interpreter
    • Executing CSQL
      • CSQL Execution Mode
      • Using CSQL (Syntax)
      • CSQL Options
    • Session Commands
  • CUBRID SQL
    • Writing Rules
      • Identifier
        • Guidelines for Creating Identifiers
        • Legal Identifiers
        • Illegal Identifiers
        • The maximum length of an identifier name
      • Reserved Words
      • Comment
      • Literal
        • Number
        • Date/Time
        • Bit String
        • Character String
        • Collection
        • NULL
    • Data Types
      • Data Types
        • Numeric Types
          • INT/INTEGER
          • SHORT/SMALLINT
          • BIGINT
          • NUMERIC/DECIMAL
          • FLOAT/REAL
          • DOUBLE/DOUBLE PRECISION
        • Date/Time Types
          • DATE
          • TIME
          • TIMESTAMP
          • DATETIME
          • CASTing a String to Date/Time Type
        • Date/Time Types with Timezone
          • Timezone Configuration
          • Timezone Function
          • Functions with a Timezone Type
          • Conversion Functions for Timezone Types
          • IANA Timezone
        • Bit Strings
          • BIT(n)
          • BIT VARYING(n)
        • Character Strings
          • CHAR(n)
          • VARCHAR(n)/CHAR VARYING(n)
          • STRING
          • Escape Special Characters
          • Comparison Rules
        • ENUM Data Type
          • ENUM Type Comparisons
          • ENUM Type Ordering
          • Notes
        • BLOB/CLOB Data Types
          • BLOB
          • CLOB
          • To Create and alter LOB
          • To store and update LOB
          • To access LOB
          • Functions and Operators for LOB
          • To create and manage LOB storage
          • Transaction and Recovery
        • Collection Types
          • SET
          • MULTISET
          • LIST/SEQUENCE
        • JSON Data Type
          • Creating JSON data
          • JSON Validation
          • JSON Value Types
          • JSON Data Conversions
          • JSON Paths
          • JSON Pointers
        • Implicit Type Conversion
          • Conversion Rules
    • Data Definition Statements
      • TABLE DEFINITION STATEMENTS
        • CREATE TABLE
          • Table Definition
          • Column Definition
          • ON UPDATE
          • Constraint Definition
          • Column Option
          • Table Option
          • CREATE TABLE LIKE
          • CREATE TABLE AS SELECT
        • ALTER TABLE
          • ADD COLUMN Clause
          • ADD CONSTRAINT Clause
          • ADD INDEX Clause
          • ALTER COLUMN … SET DEFAULT Clause
          • AUTO_INCREMENT Clause
          • CHANGE/MODIFY Clauses
          • Column’s COMMENT
          • RENAME COLUMN Clause
          • DROP COLUMN Clause
          • DROP CONSTRAINT Clause
          • DROP INDEX Clause
          • DROP PRIMARY KEY Clause
          • DROP FOREIGN KEY Clause
        • DROP TABLE
        • RENAME TABLE
      • INDEX DEFINITION STATEMENTS
        • CREATE INDEX
          • COMMENT of Index
          • Online index creation
          • Displaying online index from other transactions
          • Online unique index while other transactions inserts violates uniqueness
        • ALTER INDEX
        • DROP INDEX
      • VIEW DEFINITION STATEMENTS
        • CREATE VIEW
          • Condition for Creating Updatable VIEW
          • View’s COMMENT
        • ALTER VIEW
          • ADD QUERY Clause
          • AS SELECT Clause
          • CHANGE QUERY Clause
          • DROP QUERY Clause
          • COMMENT Clause
        • DROP VIEW
        • RENAME VIEW
      • SERIAL DEFINITION STATEMENTS
        • CREATE SERIAL
          • COMMENT of Serial
        • ALTER SERIAL
        • DROP SERIAL
        • Accessing Serial
          • Pseudocolumns
          • Functions
      • SYNONYM DEFINITION STATEMENTS
        • CREATE SYONYM
          • 1. Private synonym
          • 2. Synonym information
          • 3. Synonym name
          • 4. Synonym for synonym
          • 5. Creating schema-specified synonyms
        • ALTER SYONYM
          • Change target object
          • Change comment
        • DROP SYONYM
        • RENAME SYONYM
          • 1. Cannot change schema
          • 2. Name already in use
        • USE SYNONYM
          • 1. Use synonyms from other schemas
          • 2. Statements where synonyms cannot be used
      • SERVER DEFINITION STATEMENTS
        • CREATE SERVER
          • Server Definition
        • RENAME SERVER
        • DROP SERVER
        • ALTER SERVER
          • OWNER TO clause
          • CHANGE clause
      • STORED FUNCTION/PROCEDURE DEFINITION STATEMENTS
        • CREATE PROCEDURE
          • COMMENT of Java Stored Procedure
          • Checking the Published Java Stored Procedure Information
        • DROP PROCEDURE
        • CREATE FUNCTION
          • COMMENT of Java Stored Function
          • Checking the Published Java Stored Function Information
        • DROP FUNCTION
        • Java Call Specification
          • Data Type Mapping
    • Operators and Functions
      • Logical Operators
      • Comparison Operators
      • Arithmetic Operators
        • Arithmetic Operations and Type Casting of Numeric Data Types
        • Arithmetic Operations and Type Casting of DATE/TIME Data Types
          • Behavior related to timezone parameters
      • Set Arithmetic Operators
        • SET, MULTISET, LIST
          • Assigning Collection Value to Variable
      • Statement Set Operators
        • UNION, DIFFERENCE, INTERSECTION
      • Containment Operators
        • SETEQ
        • SETNEQ
        • SUPERSET
        • SUPERSETEQ
        • SUBSET
        • SUBSETEQ
      • BIT Functions and Operators
        • Bitwise Operator
        • BIT_AND
        • BIT_OR
        • BIT_XOR
        • BIT_COUNT
      • String Functions and Operators
        • Concatenation Operator
        • ASCII
        • BIN
        • BIT_LENGTH
        • CHAR_LENGTH, CHARACTER_LENGTH, LENGTHB, LENGTH
        • CHR
        • CONCAT
        • CONCAT_WS
        • ELT
        • FIELD
        • FIND_IN_SET
        • FROM_BASE64
        • INSERT
        • INSTR
        • LCASE, LOWER
        • LEFT
        • LOCATE
        • LPAD
        • LTRIM
        • MID
        • OCTET_LENGTH
        • POSITION
        • REPEAT
        • REPLACE
        • REVERSE
        • RIGHT
        • RPAD
        • RTRIM
        • SPACE
        • STRCMP
        • SUBSTR
        • SUBSTRING
        • SUBSTRING_INDEX
        • TO_BASE64
        • TRANSLATE
        • TRIM
        • UCASE, UPPER
      • Regular Expressions Functions and Operators
        • ECMAScript Regular Expressions Pattern Syntax
          • Special Pattern Characters
          • Quantifiers
          • Groups
          • Assertions
          • Alternatives
          • Character classes
        • REGEXP, RLIKE
        • REGEXP_COUNT
        • REGEXP_INSTR
        • REGEXP_LIKE
        • REGEXP_REPLACE
        • REGEXP_SUBSTR
      • Numeric/Mathematical Functions
        • ABS
        • ACOS
        • ASIN
        • ATAN
        • ATAN2
        • CEIL
        • CONV
        • COS
        • COT
        • CRC32
        • DEGREES
        • DRANDOM, DRAND
        • EXP
        • FLOOR
        • HEX
        • LN
        • LOG2
        • LOG10
        • MOD
        • PI
        • POW, POWER
        • RADIANS
        • RANDOM, RAND
        • ROUND
        • SIGN
        • SIN
        • SQRT
        • TAN
        • TRUNC, TRUNCATE
        • WIDTH_BUCKET
      • Date/Time Functions and Operators
        • ADDDATE, DATE_ADD
        • ADDTIME
        • ADD_MONTHS
        • CURDATE, CURRENT_DATE
        • CURRENT_DATETIME, NOW
        • CURTIME, CURRENT_TIME
        • CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP
        • DATE
        • DATEDIFF
        • DATE_SUB, SUBDATE
        • DAY, DAYOFMONTH
        • DAYOFWEEK
        • DAYOFYEAR
        • EXTRACT
        • FROM_DAYS
        • FROM_TZ
        • FROM_UNIXTIME
        • HOUR
        • LAST_DAY
        • MAKEDATE
        • MAKETIME
        • MINUTE
        • MONTH
        • MONTHS_BETWEEN
        • NEW_TIME
        • QUARTER
        • ROUND
        • SEC_TO_TIME
        • SECOND
        • SYS_DATE, SYSDATE
        • SYS_DATETIME, SYSDATETIME
        • SYS_TIME, SYSTIME
        • SYS_TIMESTAMP, SYSTIMESTAMP
        • TIME
        • TIME_TO_SEC
        • TIMEDIFF
        • TIMESTAMP
        • TO_DAYS
        • TRUNC
        • TZ_OFFSET
        • UNIX_TIMESTAMP
        • UTC_DATE
        • UTC_TIME
        • WEEK
        • WEEKDAY
        • YEAR
      • JSON functions
        • Introduction to JSON functions
        • JSON_ARRAY
        • JSON_OBJECT
        • JSON_KEYS
        • JSON_DEPTH
        • JSON_LENGTH
        • JSON_VALID
        • JSON_TYPE
        • JSON_QUOTE
        • JSON_UNQUOTE
        • JSON_PRETTY
        • JSON_SEARCH
        • JSON_EXTRACT
        • ->
        • ->>
        • JSON_CONTAINS_PATH
        • JSON_CONTAINS
        • JSON_MERGE_PATCH
        • JSON_MERGE_PRESERVE
        • JSON_MERGE
        • JSON_ARRAY_APPEND
        • JSON_ARRAY_INSERT
        • JSON_INSERT
        • JSON_SET
        • JSON_REPLACE
        • JSON_REMOVE
        • JSON_TABLE
      • LOB Functions
        • BIT_TO_BLOB
        • BLOB_FROM_FILE
        • BLOB_LENGTH
        • BLOB_TO_BIT
        • CHAR_TO_BLOB
        • CHAR_TO_CLOB
        • CLOB_FROM_FILE
        • CLOB_LENGTH
        • CLOB_TO_CHAR
      • Data Type Casting Functions and Operators
        • CAST
        • DATE_FORMAT
        • FORMAT
        • STR_TO_DATE
        • TIME_FORMAT
        • TO_CHAR(date_time)
        • TO_CHAR(number)
        • TO_DATE
        • TO_DATETIME
        • TO_DATETIME_TZ
        • TO_NUMBER
        • TO_TIME
        • TO_TIMESTAMP
        • TO_TIMESTAMP_TZ
      • Aggregate/Analytic Functions
        • Overview
        • Aggregate vs. Analytic
        • Analytic functions which “ORDER BY” clause must be specified in OVER function
        • AVG
        • COUNT
        • CUME_DIST
        • DENSE_RANK
        • FIRST_VALUE
        • GROUP_CONCAT
        • LAG
        • LAST_VALUE
        • LEAD
        • MAX
        • MEDIAN
        • MIN
        • NTH_VALUE
        • NTILE
        • PERCENT_RANK
        • PERCENTILE_CONT
        • PERCENTILE_DISC
        • RANK
        • ROW_NUMBER
        • STDDEV, STDDEV_POP
        • STDDEV_SAMP
        • SUM
        • VARIANCE, VAR_POP
        • VAR_SAMP
        • JSON_ARRAYAGG
        • JSON_OBJECTAGG
      • Click Counter Functions
        • INCR, DECR
      • ROWNUM Functions
        • ROWNUM, INST_NUM
        • ORDERBY_NUM
        • GROUPBY_NUM
      • Information Functions
        • CHARSET
        • COERCIBILITY
        • COLLATION
        • CURRENT_USER, USER
        • DATABASE, SCHEMA
        • DBTIMEZONE
        • DEFAULT
        • DISK_SIZE
        • INDEX_CARDINALITY
        • INET_ATON
        • INET_NTOA
        • LAST_INSERT_ID
        • LIST_DBS
        • ROW_COUNT
        • SESSIONTIMEZONE
        • USER, SYSTEM_USER
        • VERSION
      • Encryption Function
        • MD5
        • SHA1
        • SHA2
      • Comparison Expression
        • Simple Comparison Expression
        • ANY/SOME/ALL quantifiers
        • BETWEEN
        • EXISTS
        • IN
        • IS NULL
        • LIKE
        • CASE
      • Comparison Functions
        • COALESCE
        • DECODE
        • GREATEST
        • IF
        • IFNULL, NVL
        • ISNULL
        • LEAST
        • NULLIF
        • NVL2
      • Other functions
        • SLEEP
        • SYS_GUID
    • Data Manipulation Statements
      • SELECT
        • FROM Clause
          • Derived Table
          • Subquery Derived Table
          • DBLINK
        • WHERE Clause
        • GROUP BY … HAVING Clause
        • ORDER BY Clause
        • LIMIT Clause
        • Join Query
          • Inner Join
          • Outer Join
          • Cross Join
          • Natural Join
        • Subquery
          • Single-Row Subquery
          • Multiple-Row Subquery
        • VALUES
        • FOR UPDATE
      • Hierarchical Query
        • START WITH Clause
        • CONNECT BY Clause
        • Hierarchical Query Execution
          • Hierarchical Query for Table Join
          • Query Results
          • Ordering Data with the Hierarchical Query
        • Pseudo Columns for Hierarchical Query
          • LEVEL
          • CONNECT_BY_ISLEAF
          • CONNECT_BY_ISCYCLE
        • Operators for Hierarchical Query
          • CONNECT_BY_ROOT
          • PRIOR
        • Functions for Hierarchical Query
          • SYS_CONNECT_BY_PATH
        • Examples of Hierarchical Query
        • Performance of Hierarchical Query
      • INSERT
        • INSERT … SELECT Statement
        • ON DUPLICATE KEY UPDATE Clause
      • UPDATE
      • REPLACE
      • DELETE
      • MERGE
      • TRUNCATE
      • PREPARED STATEMENT
        • PREPARE Statement
        • EXECUTE Statement
        • DEALLOCATE PREPARE/DROP PREPARE Statements
      • DO
      • CTE
        • CTE column names
        • RECURSIVE clause
        • CTE Usage in DMLs and CREATE
      • CALL
    • Query Optimization
      • Updating Statistics
      • Checking Statistics Information
      • Viewing Query Plan
      • Query Profiling
      • Using SQL Hint
      • Index Hint
        • USING INDEX
        • USE, FORCE, IGNORE INDEX
        • Examples of index hint
      • Special Indexes
        • Filtered Index
        • Function-based Index
      • Optimization using indexes
        • Covering Index
        • Optimizing ORDER BY Clause
        • Index Scan in Descending Order
        • Optimizing GROUP BY Clause
        • Multiple Key Ranges Optimization
        • Index Skip Scan
        • Loose Index Scan
        • In Memory Sort
        • SORT-LIMIT optimization
      • QUERY CACHE
    • Partitioning
      • Partitioning
        • Partitioning key
        • Range Partitioning
        • Hash Partitioning
        • List Partitioning
          • COMMENT of Partition
        • Partition Pruning
        • Partitioning Management
          • Modifying a Partitioned Table into a Regular Table
          • Partition Reorganization
          • Adding Partitions
          • Dropping Partitions
          • Hash Partitioning Reorganization
          • Partition Promotion
        • Indexes on Partitioned Tables
        • Notes on Partitioning
          • Statistics on Partitioning Tables
          • Restrictions on Partitioned Tables
          • Partitioning Key and Charset, Collation
    • Globalization
      • An Overview of Globalization
        • Character data
        • Related Terms
        • Locale Attributes
        • Collation Properties
        • Collation Naming Rules
          • CUBRID Collation
        • Files For Locale Setting
      • Locale Setting
        • Step 1: Selecting a Locale
        • Step 2: Compiling Locale
        • Step 3: Setting CUBRID to Use a Specific Locale
          • Built-in Locale and Library Locale
          • The Month/Day in Korean and Turkish Characters for ISO-8859-1 Charset
        • Step 4: Creating a Database with the Selected Locale Setting
        • Step 5 (optional): Manually Verifying the Locale File
        • Step 6: Starting CUBRID-Related Processes
        • Synchronization of Database Collations with System Collations
      • Collation
        • Charset and Collation of Column
          • Charset
          • String Check
          • Charset Conversion
          • Collation
          • CHARSET and COLLATE modifier
          • How to Determine Collation among Columns with Different Collation
          • Charset and Collation of an ENUM type column
        • Charset and Collation of Tables
        • Charset and Collation of String Literals
          • SET NAMES Statement
          • Charset Introducer
        • Charset and Collation of Expressions
        • Charset and Collation of System Data
        • Impact of Charset Specified When Creating DB
          • Casing and identifiers
        • String literal input and output
          • Text Conversion for CSQL
          • Unicode Normalization
        • Contraction and Expansion of Collation
          • Contraction
          • Expansion
        • Operations Requiring Collation and Charset
          • Charset
          • Collation
          • Charset conversion
        • Collation settings impacting CUBRID features
          • LIKE Conditional Optimization
          • Index Covering
          • Summary of CUBRID Features for Each Collation
        • Viewing Collation Information
      • Using i18n characters with JDBC
      • Timezone Setting
        • Compiling Timezone Library
          • Windows
          • Linux
          • Timezone library and database compatibility
          • Usage of timezone data types with JDBC
      • Configuration Guide for Characters
        • Locale
        • CHAR and VARCHAR
        • Choosing Charset
        • Choosing Collation
        • Normalization
        • CAST vs COLLATE
      • Remark
      • Guide for Adding Locales and Collations
        • LDML Calendar Information
        • LDML Numbers information
        • LDML Alphabet
        • LDML Console Conversion
        • LDML Collation
    • Transaction and Lock
      • Database Transaction
        • Transaction Commit
        • Transaction Rollback
        • Savepoint and Partial Rollback
      • Cursor Holdability
      • Database Concurrency
      • Multiversion Concurrency Control
        • Versioning, visibility and snapshot
        • VACUUM
          • VACUUM Parallel Execution
          • VACUUM Data
          • VACUUM jobs
          • Tracking dropped files
      • Lock Protocol
        • Granularity Locking
        • Lock Mode Types And Compatibility
          • Examples using locks
        • Locking to protect unique constraint
        • Transaction Deadlock
        • Transaction Lock Timeout
      • Transaction Isolation Level
        • SET TRANSACTION ISOLATION LEVEL
        • GET TRANSACTION ISOLATION LEVEL
        • READ COMMITTED Isolation Level
          • READ COMMITTED UPDATE RE-EVALUATION
        • REPEATABLE READ Isolation Level
        • SERIALIZABLE Isolation Level
        • How to Handle Dirty Record
      • Transaction Termination and Restoration
        • Restarting Database
    • Trigger
      • CREATE TRIGGER
        • Guidelines for Trigger Definition
        • Trigger Definition
        • Event Time
        • Trigger Type
        • Trigger Event Type
        • Trigger Event Target
        • Combination of Event Type and Target
        • Trigger Condition
        • Correlation Name
        • Trigger Action
        • Trigger’s COMMENT
      • ALTER TRIGGER
        • Trigger’s COMMENT
      • DROP TRIGGER
      • RENAME TRIGGER
      • Deferred Condition and Action
        • Executing Deferred Condition and Action
        • Dropping Deferred Condition and Action
        • Granting Trigger Authorization
      • Trigger on REPLACE and INSERT … ON DUPLICATE KEY UPDATE
      • Trigger Debugging
        • Viewing Trigger Execution Log
        • Limiting Nested Trigger
      • Trigger Example
    • Java Stored Function/Procedure
      • Introduction to Java Stored Function/Procedure
      • Prerequisites
        • Check the cubrid.conf file
        • Start Java SP Server
      • How to Write and Load Java Stored Function/Procedure
        • Write the Java source code
        • Compile the Java source code
        • Load the compiled Java class into CUBRID
        • Publish the loaded Java class
      • Call the Java stored function/procedure
        • Using CALL Statement
        • Calling from SQL Statement
        • Calling from Java Application
      • Using Server-side Internal JDBC Driver
        • Creating Connection
        • Executing SQL Statements
        • The example of executing statements
        • OUT Parameters of Primitive Types
        • OUT Parameters of Set Types
        • OUT Parameters of CUBRID OID type
        • Returning java.sql.ResultSet in Java Stored Procedure
        • Getting information about connection client
      • Connecting to Other Databases
      • loadjava Utility
      • Caution
        • Limitations on the precision of IN/OUT parameters and a return value
      • Appendix
        • Table of Supproting JDBC API
          • java.sql.Connection
          • java.sql.Statement
          • java.sql.PreparedStatement
          • java.sql.CallableStatement
          • java.sql.ResultSet
          • java.sql.ResultSetMetaData
    • CUBRID DBLink
      • Introduction to CUBRID DBLink
      • CUBRID DBLink diagram
        • Homogeneous DBLink diagram
        • Heterogeneous DBLink diagram
      • GATEWAY
        • cub_cas_cgw
        • cub_gateway
        • Shared memory
        • Start GATEWAY
        • Restarting GATEWAY
        • Checking GATEWAY Status
        • Starting the GATEWAY together when the CUBRID service starts
      • CUBRID DBLINK settings
        • Homogeneous DBLink Setting
        • Heterogeneous DBLink Setting
        • GATEWAY Parameter
        • cubrid_gateway.conf file
        • Install ODBC Driver
        • Check and set ODBC Driver Name
      • How to use Cubrid DBLink
      • Restrictions
    • Method
      • Method Type
      • CALL Statement
    • Class Inheritance
      • Class Attribute and Method
      • Order Rule for Inheritance
      • INHERIT Clause
      • ADD SUPERCLASS Clause
      • DROP SUPERCLASS Clause
      • Class Conflict Resolution
        • Resolution Specifier
        • Superclass Conflict
          • Adding a super class
          • Deleting a super class
          • Compatible Domains
        • Sub class Conflict
          • Adding Attributes and Methods
          • Dropping Attributes and Methods
        • Schema Invariant
          • Invariant of class hierarchy
          • Invariant of name
          • Invariant of inheritance
          • Invariant of consistency
        • Rule for Schema Changes
          • Conflict-Resolution Rules
          • Domain-Change Rules
          • Class-Hierarchy Rules
    • Database Administration
      • User Management
        • Database User
        • CREATE USER
          • User’s COMMENT
        • GRANT
        • REVOKE
        • ALTER … OWNER
        • User Authorization Management METHOD
      • SET
        • System Parameter
        • User Variables
      • KILL
      • SHOW
        • DESC, DESCRIBE
        • EXPLAIN
        • SHOW TABLES
        • SHOW COLUMNS
        • SHOW INDEX
        • SHOW COLLATION
        • SHOW TIMEZONES
        • SHOW GRANTS
        • SHOW CREATE TABLE
        • SHOW CREATE VIEW
        • SHOW ACCESS STATUS
        • SHOW EXEC STATISTICS
        • Diagnostics
          • SHOW VOLUME HEADER
          • SHOW LOG HEADER
          • SHOW ARCHIVE LOG HEADER
          • SHOW HEAP HEADER
          • SHOW HEAP CAPACITY
          • SHOW SLOTTED PAGE HEADER
          • SHOW SLOTTED PAGE SLOTS
          • SHOW INDEX HEADER
          • SHOW INDEX CAPACITY
          • SHOW CRITICAL SECTIONS
          • SHOW TRANSACTION TABLES
          • SHOW THREADS
          • SHOW JOB QUEUES
          • SHOW PAGE BUFFER STATUS
    • User Schema
    • System Catalog
      • System Catalog Classes
        • _db_class
        • _db_attribute
        • _db_domain
        • _db_charset
        • _db_collation
        • _db_method
        • _db_meth_sig
        • _db_meth_arg
        • _db_meth_file
        • _db_query_spec
        • _db_index
        • _db_index_key
        • _db_auth
        • _db_data_type
        • _db_partition
        • _db_server
        • _db_stored_procedure
        • _db_stored_procedure_args
        • _db_synonym
        • db_user
        • db_authorization
        • db_serial
        • db_trigger
        • db_ha_apply_info
        • dual
      • System Catalog Virtual Class
        • DB_CLASS
        • DB_DIRECT_SUPER_CLASS
        • DB_VCLASS
        • DB_ATTRIBUTE
        • DB_ATTR_SETDOMAIN_ELM
        • DB_CHARSET
        • DB_COLLATION
        • DB_METHOD
        • DB_METH_ARG
        • DB_METH_ARG_SETDOMAIN_ELM
        • DB_METH_FILE
        • DB_INDEX
        • DB_INDEX_KEY
        • DB_AUTH
        • DB_TRIG
        • DB_PARTITION
        • DB_SERVER
        • DB_STORED_PROCEDURE
        • DB_STORED_PROCEDURE_ARGS
        • DB_SYNONYM
      • Catalog Class/Virtual Class Authorization
      • Querying on Catalog
  • CUBRID Management
    • Controlling CUBRID Processes
      • Controlling CUBRID Service
      • Controlling Database Server
      • Controlling Broker
      • Controlling Gateway
      • Controlling CUBRID Manager Server
      • Controlling CUBRID HA
      • Controlling CUBRID Java Stored Procedure Server
    • CUBRID Services
      • Registering Services
      • Starting Services
      • Stopping Services
      • Restarting Services
      • Managing Service Status
      • cubrid Utility Logging
    • Database Server
      • Starting Database Server
      • Stopping Database Server
      • Restarting Database Server
      • Checking Database Server Status
      • Limiting Database Server Access
      • Database Server Log
        • Error Log
        • Event Log
      • Database Server Errors
    • Broker
      • Starting Broker
      • Stopping Broker
      • Restarting Broker
      • Checking Broker Status
      • Limiting Broker Access
      • Packet Encryption
      • Managing a Specific Broker
      • Dynamically Changing Broker Parameters
      • Broker configuration information
      • Broker Logs
        • Checking the Access Log
        • Checking the Error Log
        • Managing the SQL Log
          • broker_log_top
          • cubrid_replay
      • CAS Error
    • Gateway
      • Starting Gateway
      • Stopping Gateway
      • Restarting Gateway
      • Checking Gateway Status
    • CUBRID Manager Server
      • Starting the CUBRID Manager Server
      • Stopping the CUBRID Manager Server
      • CUBRID Manager Server Log
      • Configuring CUBRID Manager Server
      • CUBRID Manager User Management Console
    • CUBRID Java Stored Procedure Server
      • Starting CUBRID Java SP Server
      • Stopping CUBRID Java SP Server
      • Restarting CUBRID Java SP Server
      • Checking CUBRID Java SP Server Status
      • Starting the CUBRID Java SP Server together when the database server starts
      • Configuring for CUBRID Java SP Server
        • Environment Configuration for Java Stored Function/Procedure
        • Java SP Server System Parameters
      • CUBRID Java SP Server Log
        • Error Log
        • Java Log
      • CUBRID Java SP Server Errors
    • Database Management
      • Database Users
      • databases.txt File
      • Database Volume
    • cubrid Utilities
      • cubrid Utility Logging
      • createdb
      • addvoldb
      • deletedb
      • renamedb
      • alterdbhost
      • copydb
      • installdb
      • backupdb
        • Backup Strategy and Method
        • Managing Backup Files
        • Managing Archive Logs
      • restoredb
        • Restoring Strategy and Procedure
        • Restoring Database to Different Server
      • unloaddb
      • loaddb
        • How to Write Files to Load Database
          • Comment
          • Command Line
          • Data Line
        • Migrating Database
      • spacedb
      • compactdb
      • optimizedb
      • plandump
      • statdump
      • lockdb
        • Output Contents
      • tranlist
      • killtran
      • checkdb
      • diagdb
      • paramdump
      • tde
      • vacuumdb
      • flashback
      • HA Commands
      • Locale Commands
      • Timezone Commands
    • System Parameters
      • Configuring the Database Server
        • Scope of Database Server Configuration
        • Changing Database Server Configuration
          • Editing the Configuration File
          • Using SQL Statements
          • Using Session Commands of the CSQL Interpreter
        • cubrid.conf Configuration File and Default Parameters
          • Database Server System Parameters
          • Section by Parameter
          • Default Parameters
        • Connection-Related Parameters
        • Memory-Related Parameters
        • Disk-Related Parameters
        • Error Message-Related Parameters
        • Concurrency/Lock-Related Parameters
        • Logging-Related Parameters
        • Transaction Processing-Related Parameters
        • Statement/Type-Related Parameters
        • Thread-Related Parameters
        • Timezone Parameter
        • Query Plan Cache-Related Parameters
        • Query Cache-Related Parameters
        • Utility-Related Parameters
        • HA-Related Parameters
        • Other Parameters
      • Broker Configuration
        • cubrid_broker.conf Configuration File and Default Parameters
          • Broker System Parameters
          • Default Parameters
          • Broker Configuration File Related Environment Variables
        • Common Parameters
          • Access
          • Logging
          • Broker Server(cub_broker)
        • Parameter by Broker
          • Access
          • Broker App. Server(CAS)
          • Transaction & Query
          • Logging
          • Etc
      • HA Configuration
    • SystemTap
      • Overview
      • Installing SystemTap
        • Checking Installation
        • Version
      • Related Terms
        • Marker
        • Probe
        • Asynchronous Events
      • Using SystemTap in CUBRID
        • Building CUBRID source
        • Running SystemTap script
        • Printing Results
      • CUBRID markers
        • Connection markers
        • Query markers
        • Object operation markers
        • Index operation markers
        • Locking markers
        • Transaction markers
        • I/O markers
        • Other markers
    • cubrid scripts
      • unloaddb.sh script
    • Troubleshooting
      • Checking SQL Log
        • SQL log of CAS
        • Function getting CAS information
        • Application log
        • Slow query
      • Server Error Log
        • Detecting Overflow Keys or Overflow Pages
        • Detecting log recovery time
        • Detecting a Deadlock
      • Detecting the change of HA status
        • Detecting HA split-brain
        • Detecting Fail-over, Fail-back
      • Failure on HA Start
        • Typical Unrestorable Failure
        • How to fix when a Failure on HA start
    • DDL Audit Log
      • Overview
      • DDL Audit Log file name convention
      • DDL Audit Logfile format of CAS
      • DDL Audit Logfile format of CSQL
      • DDL Audit Log format of LOADDB
  • CUBRID HA
    • CUBRID HA Concept
      • Nodes and Groups
      • Processes
      • Servers
      • heartbeat Message
      • failover and failback
      • Broker Mode
    • CUBRID HA Features
      • Duplexing Servers
      • Duplexing Brokers
      • Log Multiplexing
    • Quick Start
      • Preparation
      • Creating Databases and Configuring Servers
      • Starting and Verifying CUBRID HA
      • Configuring and Starting Broker, and Verifying the Broker Status
    • Environment Configuration
      • cubrid.conf
        • HA or Not
        • Logging
        • Access
      • cubrid_ha.conf
        • Node
        • Access
        • Replication
        • SQL Logging
      • cubrid_broker.conf
        • Access Target
        • Access Order
        • Access Limitation
        • Reconnection
      • databases.txt
      • JDBC Configuration
      • CCI Configuration
      • PHP Configuration
    • Connecting a Broker to DB
      • Connection Process
      • Examples on Behaviours by Configuration
    • Running and Monitoring
      • cubrid heartbeat Utility
        • start
        • stop
        • copylogdb
        • applylogdb
        • reload
        • replication(or repl) start
        • replication(or repl) stop
        • status
      • Registering HA to cubrid service
      • applyinfo
      • cubrid changemode
      • Monitoring CUBRID Manager HA
    • Structures of HA
      • Basic Structure of HA
      • Multiple-Slave Node Structure
      • Load Balancing Structure
      • Multiple-Standby Server Structure
    • HA Constraints
    • Operational Scenarios
      • Operation Scenario during Read/Write Service
      • Operation Scenario during Read Service
      • Operation Scenario after Stopping a Service
      • Setting Replica Replication Delay
    • Building Replication
      • restoreslave
      • Example Scenarios of Building Replication
      • Add a Slave after Stopping a Service
      • Build Another Slave during HA Service
      • Remove a Slave during Service
      • Add a Replica during Service
      • Rebuild a Slave during Service
    • Detection of Replication Mismatch
      • How to Detect Replication Mismatch
      • checksumdb
      • HA Error Messages
        • CAS process(cub_cas)
        • Replication Log Copy Process(copylogdb)
        • Replication Log Reflection Process(applylogdb)
    • Rebuilding Replication Script
      • ha_make_slavedb.sh Script
  • CUBRID Security
    • Packet Encryption
      • Requirements of secure communication
      • Packet encryption method
      • Server setup for packet encryption
      • Supported driver
    • ACL (Access Control List)
    • Authorization
    • TDE (Transparent Data Encryption)
      • CUBRID TDE Concept
      • Key Management
        • 2-Level Key Management
        • File-based Master Key Management
      • Encryption Target
        • Permanent Data Encryption
        • Temporary Data Encryption
        • Log Data Encryption
        • DWB Encryption
        • Backup Encryption
      • Encryption Algorithm
      • Table Encryption Checking
        • SHOW CREATE TABLE
        • Inquiry to db_class
        • Using cubrid diagdb utility
      • TDE on HA
      • When TDE is unavailable
      • TDE Restriction
  • API Reference
    • JDBC Driver
      • JDBC Overview
      • Installing and Configuring JDBC
      • JDBC Programming
        • Configuration Connection
        • Connecting with DataSource
        • Checking SQL LOG
        • Checking Foreign Key Information
        • Using Object Identifiers (OIDs) and Collections
          • Using OIDs
          • Using Collections
        • Getting Auto Increment Column Value
        • Using BLOB/CLOB
        • setBoolean
        • JDBC Error Codes and Error Messages
      • JDBC Sample Program
      • JDBC API
    • CCI Driver
      • CCI Overview
      • CCI Programming
        • Writing CCI Applications
        • Configuring Library
        • Using BLOB/CLOB
        • CCI Error Codes and Error Messages
      • CCI Sample Program
      • CCI API Reference
        • cci_bind_param
        • cci_bind_param_array
        • cci_bind_param_array_size
        • cci_bind_param_ex
        • cci_blob_free
        • cci_blob_new
        • cci_blob_read
        • cci_blob_size
        • cci_blob_write
        • cci_cancel
        • cci_clob_free
        • cci_clob_new
        • cci_clob_read
        • cci_clob_size
        • cci_clob_write
        • cci_close_query_result
        • cci_close_req_handle
        • cci_col_get
        • cci_col_seq_drop
        • cci_col_seq_insert
        • cci_col_seq_put
        • cci_col_set_add
        • cci_col_set_drop
        • cci_col_size
        • cci_connect
        • cci_connect_ex
        • cci_connect_with_url
        • cci_connect_with_url_ex
        • cci_cursor_update
        • cci_datasource_borrow
        • cci_datasource_change_property
        • cci_datasource_create
        • cci_datasource_destroy
        • cci_datasource_release
        • cci_disconnect
        • cci_end_tran
        • cci_escape_string
        • cci_execute
        • cci_execute_array
        • cci_execute_batch
        • cci_execute_result
        • cci_fetch
        • cci_fetch_buffer_clear
        • cci_fetch_sensitive
        • cci_fetch_size
        • cci_get_autocommit
        • cci_get_bind_num
        • cci_get_cas_info
        • cci_get_class_num_objs
        • CCI_GET_COLLECTION_DOMAIN
        • cci_get_cur_oid
        • cci_get_data
        • cci_get_db_parameter
        • cci_get_db_version
        • cci_get_err_msg
        • cci_get_error_msg
        • cci_get_holdability
        • cci_get_last_insert_id
        • cci_get_login_timeout
        • cci_get_query_plan
        • cci_query_info_free
        • cci_get_query_timeout
        • cci_get_result_info
        • CCI_GET_RESULT_INFO_ATTR_NAME
        • CCI_GET_RESULT_INFO_CLASS_NAME
        • CCI_GET_RESULT_INFO_IS_NON_NULL
        • CCI_GET_RESULT_INFO_NAME
        • CCI_GET_RESULT_INFO_PRECISION
        • CCI_GET_RESULT_INFO_SCALE
        • CCI_GET_RESULT_INFO_TYPE
        • CCI_IS_SET_TYPE
        • CCI_IS_MULTISET_TYPE
        • CCI_IS_SEQUENCE_TYPE
        • CCI_IS_COLLECTION_TYPE
        • cci_get_version
        • cci_init
        • cci_is_holdable
        • cci_is_updatable
        • cci_next_result
        • cci_oid
        • cci_oid_get
        • cci_oid_get_class_name
        • cci_oid_put
        • cci_oid_put2
        • cci_prepare
        • cci_prepare_and_execute
        • cci_property_create
        • cci_property_destroy
        • cci_property_get
        • cci_property_set
        • cci_query_result_free
        • CCI_QUERY_RESULT_ERR_NO
        • CCI_QUERY_RESULT_ERR_MSG
        • CCI_QUERY_RESULT_RESULT
        • CCI_QUERY_RESULT_STMT_TYPE
        • cci_register_out_param
        • cci_row_count
        • cci_savepoint
        • cci_schema_info
        • cci_set_allocators
        • cci_set_autocommit
        • cci_set_db_parameter
        • cci_set_element_type
        • cci_set_free
        • cci_set_get
        • cci_set_holdability
        • cci_set_isolation_level
        • cci_set_lock_timeout
        • cci_set_login_timeout
        • cci_set_make
        • cci_set_max_row
        • cci_set_query_timeout
        • cci_set_size
    • PHP Driver
      • Installing and Configuring PHP
        • For Linux
        • For Windows
      • Building CUBRID PHP Driver from Source Code
        • For Linux
        • For Windows
      • PHP Programming
        • Connecting to a Database
        • Transactions and Auto-Commit
        • Processing Queries
      • PHP API
    • PDO Driver
      • Installing and Configuring PDO
        • Linux
        • Windows
      • Building CUBRID PHP Driver from Source Code
      • PDO Programming
        • Data Source Name (DSN)
        • Predefined Constants
      • PDO Sample Program
        • Verifying CUBRID PDO Driver Version
        • Connecting to CUBRID
        • Executing a SELECT Statement
        • Executing an UPDATE Statement
        • Using prepare and bind
        • Using the PDO::getAttribute() Function
        • CUBRID PDO Extensions
      • PDO API
    • ODBC Driver
      • Configuring and Environment ODBC
      • ODBC Programming
        • Configuring Connection String
      • ASP Sample Program
      • ODBC API
    • OLE DB Driver
      • Installing and Configuring OLE DB
      • OLE DB Programming
        • Using Data Link Properties Dialog Box
        • Configuring Connection String
        • Multi-Threaded Programming in .NET Environment
      • OLE DB API
    • ADO.NET Driver
      • Installing and Configuring ADO.NET
      • ADO.NET Programming
        • A Simple Query/Retrieve Code
        • batch Commands
        • Connection String
        • CUBRID Collections
        • CUBRID BLOB/CLOB
        • CUBRID Metadata Support
        • DataTable Support
        • Transactions
        • Working with Parameters
        • Error Codes and Messages
      • ADO.NET API
    • Perl Driver
      • Installing and Configuring Perl
      • Perl API
    • Python Driver
      • Installing and Configuring Python
        • Linux/UNIX
        • Windows
      • Python Programming
      • Python Sample Program
      • Python API
    • Ruby Driver
      • Installing and Configuring Ruby
      • Ruby Sample Program
      • Ruby API
    • Node.js Driver
      • Installing Node.js
      • CUBRID Node.js API
  • Release Notes
    • 11.2 Release Notes
      • Release Notes Information
      • Overview
        • Driver Compatibility
      • 11.2 Changes
      • Cautions
        • New Cautions
        • Existing Cautions
    • General Information
      • Revision history
      • Bug Reports and User Feedback
      • License
      • Additional Information
      • Note on Drivers
CUBRID
  • »
  • CUBRID SQL »
  • CUBRID DBLink
  • View page source

CUBRID DBLink¶

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.

../_images/dblink_homo.png

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).

../_images/dblink_heter.png

GATEWAY¶

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.

../_images/gateway.png

cub_cas_cgw¶

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_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.

Shared memory¶

The status information of the cub_cas_cgw의 is stored in the shared memory, and the cub_broker refers to this information to relay the connection to the application client. With the status information stored in the shared memory, the system manager can identify which task the cub_cas_cgw의 is currently performing or which application client’s request is currently being processed.

Start GATEWAY¶

Enter the command below to start the GATEWAY.

$ cubrid gateway start

The following message is returned if the GATEWAY is already running.

$ cubrid gateway start

Stopping GATEWAY


Enter the below command to stop the GATEWAY.

$ cubrid gateway stop

The following message is returned if the GATEWAY has stopped.

$ cubrid gateway stop
@ cubrid gateway stop
++ cubrid gateway is not running.

Restarting GATEWAY¶

Enter the below command to restart the whole GATEWAY.

$ cubrid gateway restart

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.conf

[service]

service=server,broker,gateway,manager

...

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 .

GATEWAY Parameter¶

Parameter Name

Type

Value

APPL_SERVER

string

CGW_LINK_SERVER

string

CGW_LINK_SERVER_IP

string

CGW_LINK_SERVER_PORT

int

CGW_LINK_ODBC_DRIVER_NAME

string

CGW_LINK_CONNECT_URL_PROPERTY

string

APPL_SERVER

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

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

CGW_LINK_SERVER_IP should set the IP address of the external database to be connected with CAS_CGW.

CGW_LINK_SERVER_PORT

CGW_LINK_SERVER_PORT should set the port number of databaseto be connected with CAS_CGW.

CGW_LINK_ODBC_DRIVER_NAME

CGW_LINK_ODBC_DRIVER_NAME must set the ODBC Driver name provided by the external database when connecting with CAS_CGW.

Note

  • 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

CGW_LINK_CONNECT_URL_PROPERTY creates a property used in the connection string when connecting CAS_CGW to an external database.

Note

Property is different for each database, so refer to the site below.

Oracle : https://docs.oracle.com/cd/B19306_01/server.102/b15658/app_odbc.htm#UNXAR418

MySQL : https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags

cubrid_gateway.conf file¶

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]
MASTER_SHM_ID           =50001
ADMIN_LOG_FILE          =log/gateway/cubrid_gateway.log

[%oracle_gateway]
SERVICE                 =OFF
SSL                     =OFF
APPL_SERVER             =CAS_CGW
BROKER_PORT             =53000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =53000
LOG_DIR                 =log/gateway/sql_log
ERROR_LOG_DIR           =log/gateway/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO
CCI_DEFAULT_AUTOCOMMIT  =ON
APPL_SERVER_MAX_SIZE    =256
CGW_LINK_SERVER         =ORACLE
CGW_LINK_SERVER_IP      =localhost
CGW_LINK_SERVER_PORT    =1521
CGW_LINK_ODBC_DRIVER_NAME   =Oracle_ODBC_Driver
CGW_LINK_CONNECT_URL_PROPERTY       =


[%mysql_gateway]
SERVICE                 =OFF
SSL                     =OFF
APPL_SERVER             =CAS_CGW
BROKER_PORT             =56000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =56000
LOG_DIR                 =log/gateway/sql_log
ERROR_LOG_DIR           =log/gateway/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO
CCI_DEFAULT_AUTOCOMMIT  =ON
APPL_SERVER_MAX_SIZE    =256
CGW_LINK_SERVER         =MYSQL
CGW_LINK_SERVER_IP      =localhost
CGW_LINK_SERVER_PORT    =3306
CGW_LINK_ODBC_DRIVER_NAME   =MySQL_ODBC_Driver
CGW_LINK_CONNECT_URL_PROPERTY       ="charset=utf8;PREFETCH=100;NO_CACHE=1"
  • GATEWAY SETTINGS FOR CONNECTION TO Oracle

APPL_SERVER                     =CAS_CGW
CGW_LINK_SERVER                 =ORACLE
CGW_LINK_SERVER_IP              =localhost
CGW_LINK_SERVER_PORT            =1521
CGW_LINK_ODBC_DRIVER_NAME   =Oracle 12c ODBC driver
CGW_LINK_CONNECT_URL_PROPERTY =
  • GATEWAY SETTINGS FOR CONNECTION TO MySQL

APPL_SERVER                  =CAS_CGW
CGW_LINK_SERVER                  =MYSQL
CGW_LINK_SERVER_IP           =localhost
CGW_LINK_SERVER_PORT         =3306
CGW_LINK_ODBC_DRIVER_NAME    =MySQL ODBC 8.0 Unicode Driver
CGW_LINK_CONNECT_URL_PROPERTY ="charset=utf8;PREFETCH=100;NO_CACHE=1"

Install ODBC Driver¶

You need to download and install Oracle/MySQL ODBC Driver from the site below.

Oracle ODBC Driver download site:

  • https://www.oracle.com/database/technologies/instant-client/downloads.html

MySQL ODBC Driver download site:

  • https://dev.mysql.com/downloads/connector/odbc/

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.

  • Install unixODBC

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/

  • Setting ondbcinst.ini

    [MySQL ODBC 8.0 Unicode Driver]
    Driver=/usr/lib64/libmyodbc8w.so
    
    [Oracle 12c ODBC driver]
    Description = Oracle ODBC driver for Oracle 12c
    Driver = /usr/lib64/instantclient_12_2/libsqora.so.12.1
    

Note

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);

Note

For detailed DBLink SQL syntax, refer to SELECT and SERVER DEFINITION STATEMENTS.

Restrictions¶

  • CUBRID DBLink only supports utf-8.

  • The maximum string length of one column is supported up to 16M.

  • In the case of Mysql, it is recommended to use PREFETCH, NO_CACHE=1 because the memory usage of Gateway CAS increases when cache is used for large tables.

  • ODBC non-supported types are SQL_INTERVAL, SQL_GUID, SQL_BIT, SQL_BINARY, SQL_VARBINARY, SQL_LONGVARBINARY.

Previous Next

© Copyright 2016, CUBRID Corparation.. Last updated on Nov 16, 2022.

Built with Sphinx using a theme provided by Read the Docs.