DB2 SQL Dialect User Guide

July 31, 2026 · View on GitHub

DB2 is an IBM database product. It is a Relational Database Management System (RDBMS). DB2 is extended with the support of Object-Oriented features and non-relational structures with XML.

Telemetry

This virtual schema uses telemetry-java to send anonymous feature-usage events.

For details on what is collected and how to disable telemetry, see the documentation.

Uploading the JDBC Driver to Exasol BucketFS

  1. Download the DB2 JDBC driver.

  2. Upload the driver to BucketFS, see the BucketFS documentation for details.

    Hint: Put the driver into folder default/drivers/jdbc/ to register it for ExaLoader, too.

Registering the JDBC driver for ExaLoader

In order to enable the ExaLoader to fetch data from the external database you must register the driver for ExaLoader as described in the Installation procedure for JDBC drivers.

  1. ExaLoader expects the driver in BucketFS folder default/drivers/jdbc.

    If you uploaded the driver for UDF to a different folder, then you need to upload the driver again.

  2. Additionally you need to create file settings.cfg and upload it to the same folder in BucketFS:

DRIVERNAME=DB2
JAR=jcc.jar
DRIVERMAIN=com.ibm.db2.jcc.DB2Driver
PREFIX=jdbc:db2:
NOSECURITY=YES
FETCHSIZE=100000
INSERTSIZE=-1

Ensure to add a trailing newline to settings.cfg.

Installing the Adapter Script

Upload the latest available release of DB2 Virtual Schema to Bucket FS.

Then create a schema to hold the adapter script.

CREATE SCHEMA ADAPTER;

The SQL statement below creates the adapter script, defines the Java class that serves as entry point and tells the UDF framework where to find the libraries (JAR files) for Virtual Schema and database driver.

For Regular DB2 Servers

CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/<BFS service>/<bucket>/virtual-schema-dist-14.0.4-db2-4.0.1.jar;
  %jar /buckets/<BFS service>/<bucket>/db2jcc4.jar;
  %jar /buckets/<BFS service>/<bucket>/db2jcc_license_cu.jar;
/
;

For Mainframes

CREATE OR REPLACE JAVA ADAPTER SCRIPT ADAPTER.JDBC_ADAPTER AS
  %scriptclass com.exasol.adapter.RequestDispatcher;
  %jar /buckets/<BFS service>/<bucket>/virtual-schema-dist-14.0.4-db2-4.0.1.jar;
  %jar /buckets/<BFS service>/<bucket>/db2jcc4.jar;
  %jar /buckets/<BFS service>/<bucket>/db2jcc_license_cu.jar;
  %jar /buckets/<BFS service>/<bucket>/db2jcc_license_cisuz.jar;
/

Defining a Named Connection

Define the connection to DB2 as shown below.

CREATE OR REPLACE CONNECTION DB2_CONNECTION
TO 'jdbc:db2://<host>:<port>/<database name>'
USER '<user>'
IDENTIFIED BY '<password>';

Creating a Virtual Schema

Below you see how a DB2 Virtual Schema is created.

CREATE VIRTUAL SCHEMA <virtual schema name>
    USING ADAPTER.JDBC_ADAPTER
    WITH
	CONNECTION_NAME = 'DB2_CONNECTION'
	SCHEMA_NAME = '<schema name>'
;

Data Types Conversion

DB2 Data TypeSupportedConverted Exasol Data TypeKnown limitations
BIGINT✓DECIMAL(19,0)
BINARY×
BLOB×
BOOLEAN✓BOOLEAN
CHARACTER✓CHAR
CLOB×
DATE✓DATE
DBCLOB×
DECIMAL✓DECIMAL
DECFLOAT×
DOUBLE✓DOUBLE PRECISION
GRAPHIC×
INTEGER✓DECIMAL(10,0)
SMALLINT✓DECIMAL(5,0)
TIME✓VARCHAR(100)
TIMESTAMP✓TIMESTAMP *
REAL✓DOUBLE PRECISION
VARCHAR✓VARCHAR
VARBINARY×
VARGRAPHIC×
XML✓VARCHAR(2000000)
  • TIMESTAMP with fractional second precision are mapped to TIMESTAMP with milliseconds precision for Exasol versions up to 8.31. Starting with Exasol 8.32 they are mapped with the same specified precision up to nanosecond (9). Precisions greater than nanoseconds will be truncated to nanoseconds.

Casting of Functions

  • LIMIT is replaced by FETCH FIRST x ROWS ONLY
  • OFFSET is currently not supported as only DB2 V11 support this natively
  • ADD_DAYS, ADD_WEEKS ... will be replaced by COLUMN + DAYS, COLUMN + ....

Testing Information

In the following matrix you find combinations of JDBC driver and dialect version that we tested.

Virtual Schema VersionDB2 VersionDriver NameDriver Version
2.0.0ibmcom/db2:11.5.7.0adb2jcc11.5.7.0a
2.1.0ibmcom/db2:11.5.8.0db2jcc11.5.8.0
3.1.0db2_community/db2:12.1.1.0db2jcc12.1.0.0
4.0.0db2_community/db2:12.1.4.0db2jcc12.1.4.0