IBM Db2
Doris JDBC Catalog supports connecting to IBM Db2 databases through the standard JDBC interface. This document describes how to configure an IBM Db2 database connection.
Terms and Conditions
To connect to an IBM Db2 database, you need
IBM Db2 11.5.x or higher
JDBC driver for IBM Db2 database, you can download the latest or specified version of IBM Db2 driver from Maven repository. It is recommended to use IBM db2 jcc version 11.5.8.0.
Doris Network connection between each FE and BE node and the IBM Db2 server, default port is 51000.
Connect to IBM Db2
CREATE CATALOG db2 PROPERTIES (
"type"="jdbc",
"user"="USERNAME",
"password"="PASSWORD",
"jdbc_url" = "jdbc:db2://host:port/database",
"driver_url" = "jcc-11.5.8.0.jar",
"driver_class" = "com.ibm.db2.jcc.DB2Driver"
)
jdbc_url
defines the connection information and parameters to be passed to the IBM Db2 driver.
The parameters for the supported URLs can be found in the Db2 JDBC Driver Documentation.
Hierarchical mapping
When mapping IBM Db2, Doris' Database corresponds to a Schema under the specified DataBase ("database" in the jdbc_url
parameter) in DB2. The Table under Doris' Database corresponds to the Tables under Schema in DB2. That is, the mapping relationship is as follows:
Doris | IBM Db2 |
---|---|
Catalog | DataBase |
Database | Schema |
Table | Table |
Type mapping
IBM Db2 to Doris type mapping
IBM Db2 Type | Doris Type | Comment |
---|---|---|
SMALLINT | SMALLINT | |
INT | INT | |
BIGINT | BIGINT | |
DOUBLE | DOUBLE | |
DOUBLE PRECISION | DOUBLE | |
FLOAT | DOUBLE | |
REAL | FLOAT | |
NUMERIC | DECIMAL | |
DECIMAL | DECIMAL | |
DECFLOAT | DECIMAL | |
DATE | DATE | |
TIMESTAMP | DATETIME | |
CHAR | CHAR | |
CHAR VARYING | VARCHAR | |
VARCHAR | VARCHAR | |
LONG VARCHAR | VARCHAR | |
VARGRAPHIC | STRING | |
LONG VARGRAPHIC | STRING | |
TIME | STRING | |
CLOB | STRING | |
XML | STRING | |
OTHER | UNSUPPORTED |
Query optimization
Predicate pushdown
When executing a query like where dt = '2022-01-01'
, Doris can push these filtering conditions down to the external data source, thereby directly excluding data that does not meet the conditions at the data source level, reducing inaccuracies. Necessary data acquisition and transmission. This greatly improves query performance while also reducing the load on external data sources.
Row limit
If you include the limit keyword in the query, Doris will push the limit down to the IBM Db2 database to reduce the amount of data transfer.
Escape characters
Doris will automatically add the escape character ("") to the field names and table names in the query statements sent to IBM Db2 to prevent field names and table names from conflicting with IBM Db2 internal keywords.
FAQ
Invalid operation: result set is closed. ERRORCODE=-4470
exception occurs when reading IBM Db2 data through JDBC CatalogAdd connection parameters in the jdbc_url connection string when creating the IBM Db2 Catalog:
allowNextOnExhaustedResultSet=1;resultSetHoldability=1;
. like:jdbc:db2://host:port/database:allowNextOnExhaustedResultSet=1;resultSetHoldability=1;
.