Modern data teams rarely live inside a single database. They migrate between platforms, run applications against several backends at once, and move data through pipelines that touch many systems. Each of those systems speaks its own flavour of SQL — and that is where most of the friction lives.
Delivering near-infinite scalability, real-time analytics, and multicloud support, IBM Db2 is an AI-powered autonomous database that accelerates decision-making, reins in costs by using a single engine, and promotes security for your business data.
IBM Db2 has long been underserved by cross-dialect SQL tooling. Writing portable SQL that includes Db2 has meant hand-translating queries and remembering a long list of syntax quirks. The db2-sqlglot-dialect closes that gap. It teaches SQLGlot how to read and write Db2 SQL, so you can transpile between Db2 and 20+ other dialects automatically — no live database required.
What is SQLGlot?
SQLGlot is a popular open-source SQL parser, transpiler, and optimizer written in pure Python, created by Toby Mao. It has become a staple of the data-engineering toolbox because it can do four things well:
• Parsing — turn a SQL string into an Abstract Syntax Tree (AST)
• Transpilation — translate SQL from one database dialect to another
• Optimization — rewrite queries into more efficient forms
• Validation — catch syntax errors before a query ever reaches the database
SQLGlot already supports more than 20 dialects — PostgreSQL, MySQL, Snowflake, BigQuery, Spark, and many more — and its architecture is deliberately extensible. New dialects can be added as plugins, which is exactly how Db2 support is delivered.
The Challenge: Why Db2 Needs Special Handling
Db2 has its own personality. Several of its conventions differ from the SQL most engineers write day to day, and each difference is a place where a naive translation breaks:
• Pagination — Db2 uses FETCH FIRST n ROWS ONLY instead of the common LIMIT clause
• Special data types — types such as GRAPHIC, VARGRAPHIC, and DBCLOB handle multi-byte character data
• Function names — string position is POSSTR() rather than POSITION() or INSTR()
• Date/time arithmetic — Db2 computes date differences with DAYS() arithmetic instead of DATEDIFF()
How the Plugin Works
Transpilation in SQLGlot is a pipeline. The source SQL is parsed into a dialect-neutral AST, the tree is transformed using Db2-aware rules, and the generator walks the tree back out as valid Db2 SQL. The plugin plugs Db2 knowledge into the first and last stages.
The transpilation pipeline — parse to a neutral AST, transform, then generate Db2 SQL.
Under the hood, the plugin is built on SQLGlot’s extensible architecture and is organized into three small, focused components:
Three components — a parser, a generator, and a dialect are layered on top of SQLGlot core.
1. The parser
parser.py converts Db2 SQL into SQLGlot’s AST. It maps Db2-specific functions (for example, POSSTR → StrPosition) and recognizes Db2-only types such as GRAPHIC and VARGRAPHIC.
2. The generator
generator.py walks the AST back out as Db2-compliant SQL. It maps types for cross-dialect compatibility, translates functions back to their Db2 names, and applies syntax transforms like LIMIT → FETCH FIRST.
3. The dialect
dialect.py ties everything together and declares Db2’s behaviours — how NULLs sort, how division is typed, and how date/time formats map — then wires in the parser and generator.
Intelligent Type Mapping
When you transpile DDL into Db2, the plugin converts source types into their closest Db2 equivalents automatically. A few constructs that have no clean Db2 analog are preserved as-is so the translation never silently corrupts your schema:
|
Source type
|
Db2 target
|
Notes
|
|
TEXT
|
CLOB
|
Large character data
|
|
BYTEA / BINARY
|
BLOB
|
Large binary data
|
|
TINYINT
|
SMALLINT
|
Db2 has no 1-byte int
|
|
TIMESTAMPTZ
|
TIMESTAMP
|
Time zone dropped
|
|
NCHAR / NVARCHAR
|
GRAPHIC / VARGRAPHIC
|
Multi-byte character data
|
|
SERIAL / BIGSERIAL
|
preserved as-is
|
May need manual review
|
Cross-Dialect Transpilation in Action
The examples below come straight from the demo script. Each one reads SQL in a source dialect and writes the Db2 equivalent — no Db2 connection needed.
Example 1 — Limit
|
# PostgreSQL LIMIT -> Db2 FETCH FIRST
transpile("SELECT * FROM employee ORDER BY salary DESC LIMIT 5",
read="postgres", write="db2")
# SELECT * FROM employee ORDER BY salary DESC
# FETCH FIRST 5 ROWS ONLY
|
Example 2 — String function
|
# PostgreSQL POSITION -> Db2 POSSTR
transpile("SELECT POSITION('son' IN lastname) FROM employee",
read="postgres", write="db2")
# SELECT POSSTR(lastname, 'son') FROM employee
|
Example 3 — Date arithmetic
|
# MySQL DATEDIFF -> Db2 DAYS arithmetic
transpile("SELECT DATEDIFF(order_date, ship_date) AS d FROM orders",
read="mysql", write="db2")
# SELECT DAYS(order_date) - DAYS(ship_date) AS d FROM orders
|
Example 4 — Type conversion in DDL
|
# PostgreSQL types -> Db2
transpile("CREATE TABLE demo (id INT, notes TEXT, created_at TIMESTAMPTZ)",
read="postgres", write="db2")
# CREATE TABLE demo (id INTEGER, notes CLOB, created_at TIMESTAMP)
|
Installation and Usage
The plugin has been tested with Python 3.10, 3.11, and 3.12, and targets SQLGlot 30.9.0. It is published on PyPI and can be installed using the following command:
|
pip install db2-sqlglot-dialect
|
Once installed, the Db2 dialect is discovered automatically through Python’s entry-point system — no extra configuration. Just pass write="db2" (or read="db2") to SQLGlot:
|
from sqlglot import transpile
# PostgreSQL -> Db2
result = transpile("SELECT * FROM t LIMIT 10", read="postgres", write="db2")
print(result[0]) # Output: SELECT * FROM t FETCH FIRST 10 ROWS ONLY
# Db2 -> Snowflake
result = transpile("SELECT * FROM t FETCH FIRST 10 ROWS ONLY", read="db2", write="snowflake")
print(result[0]) # Output: SELECT * FROM t FETCH FIRST 10 ROWS ONLY
|
Where This Helps
• Database migrations — convert large bodies of SQL when moving to or from Db2, cutting manual rework and translation errors
• Multi-database applications — write SQL once and transpile it to Db2 at runtime, reducing duplicated code
• Data integration — keep ETL/ELT pipelines that move data between Db2 and other systems syntactically compatible
• Query analysis — translate Db2 queries into a more familiar dialect to read, review, and optimize them
• Developer productivity — let engineers work in their preferred dialect and target Db2 without learning every quirk first
Wrapping Up
The db2-sqlglot-dialect fills a real gap in the SQL ecosystem. By teaching SQLGlot to read and write Db2, it lets you transpile queries and DDL between Db2 and 20+ other dialects — automatically, and without a live connection. Whether you are migrating off Db2, integrating it with modern data platforms, or simply trying to keep portable SQL honest, the plugin does the tedious translation so you do not have to.
Resources
• db2-sqlglot-dialect on GitHub — https://github.com/IBM/db2-sqlglot-plugin
• Report an issue or request a feature — https://github.com/IBM/db2-sqlglot-plugin/issues
About the Authors
Shubham Kapoor is a Software Developer at the IBM Lucknow Lab. He holds a B.Tech in Mechanical Engineering and a PG-DAC from C-DAC (Centre for Development of Advanced Computing), under the Ministry of Electronics and Information Technology (MeitY), Government of India. Shubham works on various Db2 open-source initiatives and is actively involved in building and expanding the Db2 open-source ecosystem. He has over 11 years of experience in designing and developing robust web and mobile application backends, along with 7 years of experience working with IBM Informix.
Awanish Gupta is a Software Engineering Intern at IBM and is currently pursuing an M.Tech at the Indian Institute of Information Technology (IIIT) Allahabad. He holds a B.Tech degree and has been working at IBM as a Software Engineering Intern for the past six months. His interests include data engineering, machine learning, cloud technologies, and open-source software development.