DRDA Protocol Quick Start Guide

DRDA Protocol Quick Start Guide

Get started with HeliosDB’s IBM DB2 DRDA protocol support for seamless DB2 application migration and mainframe integration.

What is DRDA?

Distributed Relational Database Architecture (DRDA) is an open, published database protocol developed by IBM. It serves as the wire protocol for IBM DB2 databases across all platforms:

• IBM DB2 for LUW (Linux, Unix, Windows)
• IBM DB2 for z/OS (mainframe)
• IBM DB2 for i (AS/400)
• IBM Db2 on Cloud

HeliosDB implements DRDA Level 3-5, providing 95% compatibility with DB2 clients, enabling direct migration from DB2 environments without application code changes.

Key Benefits

Benefit	Description
Zero Code Changes	Existing DB2 applications connect directly
Full Driver Support	Works with all IBM DB2 drivers (JDBC, ODBC, Python, .NET)
DB2 SQL Syntax	Native support for FETCH FIRST, WITH UR, MERGE, etc.
Mainframe Integration	Connect legacy z/OS applications to modern infrastructure

---

Connection Setup

Default Connection Parameters

Parameter	Value	Description
Host	localhost	Server hostname
Port	50000	DRDA protocol port
Database	heliosdb	Database name
Protocol	TCPIP	Transport protocol

DB2 Command Line Processor (CLP)

# Catalog the remote node and database
db2 catalog tcpip node HELIOS remote localhost server 50000
db2 catalog database heliosdb at node HELIOS

# Connect and execute queries
db2 connect to heliosdb user admin using password
db2 "SELECT * FROM employees FETCH FIRST 10 ROWS ONLY"
db2 terminate

---

Basic Queries and Operations

DB2-Style Pagination

-- First 10 rows
SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;

-- Pagination with OFFSET
SELECT * FROM employees
ORDER BY emp_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Isolation Level Hints

SELECT * FROM employees WITH UR;  -- Uncommitted read
SELECT * FROM employees WITH CS;  -- Cursor stability (default)
SELECT * FROM employees WITH RS;  -- Read stability
SELECT * FROM employees WITH RR;  -- Repeatable read

MERGE Statement (Upsert)

MERGE INTO employees t
USING (VALUES (100, 'John Smith', 75000)) AS s(emp_id, name, salary)
ON t.emp_id = s.emp_id
WHEN MATCHED THEN UPDATE SET name = s.name, salary = s.salary
WHEN NOT MATCHED THEN INSERT (emp_id, name, salary) VALUES (s.emp_id, s.name, s.salary);

---

Connection Examples

Python (ibm_db)

import ibm_db

conn_str = (
    "DATABASE=heliosdb;HOSTNAME=localhost;PORT=50000;"
    "PROTOCOL=TCPIP;UID=admin;PWD=password;"
)

conn = ibm_db.connect(conn_str, "", "")
stmt = ibm_db.exec_immediate(conn,
    "SELECT emp_id, name, salary FROM employees FETCH FIRST 10 ROWS ONLY")

while ibm_db.fetch_row(stmt):
    print(f"{ibm_db.result(stmt, 'emp_id')}: {ibm_db.result(stmt, 'name')}")

ibm_db.close(conn)

Python with SQLAlchemy

from sqlalchemy import create_engine, text

engine = create_engine('db2+ibm_db://admin:password@localhost:50000/heliosdb')

with engine.connect() as conn:
    result = conn.execute(text(
        "SELECT * FROM employees WHERE department_id = :dept_id FETCH FIRST 10 ROWS ONLY"
    ), {"dept_id": 10})
    for row in result:
        print(row)

Java (JDBC)

import java.sql.*;

public class HeliosDB2Example {
    public static void main(String[] args) {
        String url = "jdbc:db2://localhost:50000/heliosdb";

        try (Connection conn = DriverManager.getConnection(url, "admin", "password");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT * FROM employees FETCH FIRST 10 ROWS ONLY")) {

            while (rs.next()) {
                System.out.printf("%d: %s - $%.2f%n",
                    rs.getInt("emp_id"),
                    rs.getString("name"),
                    rs.getDouble("salary"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Java Batch Insert

conn.setAutoCommit(false);
String sql = "INSERT INTO employees (name, department_id, salary) VALUES (?, ?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    for (Employee emp : employeeList) {
        pstmt.setString(1, emp.getName());
        pstmt.setInt(2, emp.getDeptId());
        pstmt.setDouble(3, emp.getSalary());
        pstmt.addBatch();
    }
    pstmt.executeBatch();
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

.NET (IBM.Data.DB2)

using IBM.Data.DB2;

string connectionString = "Server=localhost:50000;Database=heliosdb;UID=admin;PWD=password;";

using (DB2Connection conn = new DB2Connection(connectionString))
{
    conn.Open();
    string sql = "SELECT emp_id, name, salary FROM employees FETCH FIRST 10 ROWS ONLY";

    using (DB2Command cmd = new DB2Command(sql, conn))
    using (DB2DataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
            Console.WriteLine($"{reader["emp_id"]}: {reader["name"]} - ${reader["salary"]}");
    }
}

.NET Core / .NET 5+

IBM.Data.DB2.Core

using IBM.Data.DB2.Core;

var connectionString = "Server=localhost:50000;Database=heliosdb;UID=admin;PWD=password;";
await using var conn = new DB2Connection(connectionString);
await conn.OpenAsync();

var sql = "SELECT * FROM employees WHERE department_id = @deptId FETCH FIRST 10 ROWS ONLY";
await using var cmd = new DB2Command(sql, conn);
cmd.Parameters.Add(new DB2Parameter("@deptId", 10));

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader["name"]}: ${reader["salary"]}");

---

Common Use Cases

1. DB2 Migration

Migrate existing DB2 applications to HeliosDB with minimal changes:

# Original DB2 connection
db2 connect to PROD_DB2 user app_user using password

# HeliosDB connection (same syntax)
db2 connect to heliosdb user app_user using password

Migration Checklist:

• Update connection strings to point to HeliosDB
• Verify DB2-specific SQL syntax (most is supported)
• Test stored procedures and triggers
• Validate data type mappings

2. IBM Mainframe Integration

Connect z/OS COBOL applications to HeliosDB:

EXEC SQL
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO :WS-EMP-ID, :WS-EMP-NAME, :WS-SALARY
    FROM EMPLOYEES
    WHERE DEPT_ID = :WS-DEPT-ID
    FETCH FIRST 1 ROW ONLY
END-EXEC.

3. Hybrid Cloud Architecture

Use HeliosDB as a cloud-native alternative while maintaining DB2 compatibility:

import ibm_db

# On-premises DB2
on_prem = ibm_db.connect("DATABASE=prod;HOSTNAME=mainframe.corp;PORT=50000;...", "", "")

# Cloud HeliosDB (same driver, same syntax)
cloud = ibm_db.connect("DATABASE=heliosdb;HOSTNAME=cloud.example.com;PORT=50000;...", "", "")

4. ETL and Data Warehouse

Leverage DB2 analytical functions:

SELECT
    department_id, emp_id, name, salary,
    SUM(salary) OVER (PARTITION BY department_id) as dept_total,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

---

Troubleshooting

Connection Issues

Error	Cause	Solution
SQL30081N	Network/connection failure	Verify host, port, and firewall rules
SQL1403N	Database not found	Check database name in catalog
SQL0104N	Syntax error	Verify DB2 SQL syntax compatibility
SQL0551N	Authorization failure	Check user permissions

Common Fixes

1. Cannot connect to database:

# Verify HeliosDB is listening on DRDA port
netstat -ln | grep 50000

# Re-catalog the database
db2 uncatalog database heliosdb
db2 uncatalog node HELIOS
db2 catalog tcpip node HELIOS remote localhost server 50000
db2 catalog database heliosdb at node HELIOS

2. SQL syntax not recognized:

-- Use FETCH FIRST instead of LIMIT (DB2 style)
SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;  -- Correct

3. Driver version mismatch:

# Ensure IBM DB2 Driver version 11.5+
java -cp db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -version

4. SSL/TLS connection:

conn_str = (
    "DATABASE=heliosdb;HOSTNAME=localhost;PORT=50000;"
    "PROTOCOL=TCPIP;SECURITY=SSL;SSLServerCertificate=/path/to/cert.pem;"
    "UID=admin;PWD=password;"
)

---

Supported Features Summary

Category	Coverage	Notes
DRDA Protocol	95%	Level 3-5 implemented
SQL Statements	95%	Standard DB2 syntax
Data Types	100%	All DB2 types
Functions	90%	Core + OLAP functions
Stored Procedures	85%	SQL procedures
Triggers	100%	All trigger types

---

Related Resources

• DRDA Reference Documentation - Complete protocol reference
• DRDA Compatibility Matrix - Detailed compatibility
• DRDA Examples - Additional code examples
• Protocol Compatibility Matrix - All protocols

---

Last Updated: January 2026