DRDA/DB2 Examples

DRDA/DB2 Examples

Practical examples for HeliosDB’s IBM DB2 DRDA protocol support.

Connection Examples

DB2 CLP

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

# Connect
db2 connect to heliosdb user admin using password

# Execute query
db2 "SELECT * FROM employees FETCH FIRST 10 ROWS ONLY"

# Disconnect
db2 terminate

JDBC (Java)

import java.sql.*;

public class DB2Example {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:db2://localhost:50000/heliosdb";
        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.println(rs.getString("name"));
        }

        conn.close();
    }
}

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 * FROM employees FETCH FIRST 10 ROWS ONLY"
)

while ibm_db.fetch_row(stmt):
    name = ibm_db.result(stmt, "name")
    print(name)

ibm_db.close(conn)

Query Examples

FETCH FIRST Pagination

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

-- With OFFSET
SELECT * FROM employees
ORDER BY emp_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- FETCH with TIES
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

Isolation Levels

-- Uncommitted read (dirty read allowed)
SELECT * FROM employees WITH UR;

-- Cursor stability (default)
SELECT * FROM employees WITH CS;

-- Read stability
SELECT * FROM employees WITH RS;

-- Repeatable read (serializable)
SELECT * FROM employees WITH RR;

Common Table Expressions

-- Basic CTE
WITH dept_counts AS (
    SELECT department_id, COUNT(*) as emp_count
    FROM employees
    GROUP BY department_id
)
SELECT d.name, dc.emp_count
FROM departments d
JOIN dept_counts dc ON d.id = dc.department_id;

Recursive CTE

-- Employee hierarchy
WITH RECURSIVE emp_hierarchy (emp_id, name, manager_id, level) AS (
    -- Base case: top-level managers
    SELECT emp_id, name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees under managers
    SELECT e.emp_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy
ORDER BY level, name;

OLAP Functions

-- Running total by department
SELECT
    emp_id,
    name,
    department_id,
    salary,
    SUM(salary) OVER (
        PARTITION BY department_id
        ORDER BY emp_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM employees;

-- Ranking
SELECT
    emp_id,
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

-- Lag and Lead
SELECT
    emp_id,
    name,
    salary,
    LAG(salary, 1) OVER (ORDER BY emp_id) as prev_salary,
    LEAD(salary, 1) OVER (ORDER BY emp_id) as next_salary
FROM employees;

MERGE Statement

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

Array Functions

-- LISTAGG for string aggregation
SELECT
    department_id,
    LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) as employees
FROM employees
GROUP BY department_id;

Stored Procedures

Create Procedure

CREATE PROCEDURE get_employees_by_dept(
    IN p_dept_id INTEGER,
    OUT p_count INTEGER
)
LANGUAGE SQL
BEGIN
    DECLARE v_count INTEGER;

    SELECT COUNT(*) INTO v_count
    FROM employees
    WHERE department_id = p_dept_id;

    SET p_count = v_count;
END;

Procedure with Result Set

CREATE PROCEDURE get_high_earners(
    IN p_min_salary DECIMAL(10,2)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR
        SELECT emp_id, name, salary
        FROM employees
        WHERE salary >= p_min_salary
        ORDER BY salary DESC;

    OPEN c1;
END;

Call Procedure

// JDBC procedure call
CallableStatement cs = conn.prepareCall("{CALL get_employees_by_dept(?, ?)}");
cs.setInt(1, 10);  // department_id
cs.registerOutParameter(2, Types.INTEGER);  // count

cs.execute();

int count = cs.getInt(2);
System.out.println("Employee count: " + count);

Triggers

BEFORE INSERT Trigger

CREATE TRIGGER emp_before_insert
BEFORE INSERT ON employees
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    SET n.created_at = CURRENT TIMESTAMP;
    SET n.updated_at = CURRENT TIMESTAMP;
END;

AFTER UPDATE Trigger

CREATE TRIGGER emp_audit_update
AFTER UPDATE ON employees
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    INSERT INTO employee_audit (
        emp_id, field_changed, old_value, new_value, changed_at
    ) VALUES (
        n.emp_id, 'salary',
        CAST(o.salary AS VARCHAR(20)),
        CAST(n.salary AS VARCHAR(20)),
        CURRENT TIMESTAMP
    );
END;

Transaction Examples

Savepoints

-- Start transaction
BEGIN;

INSERT INTO accounts (id, balance) VALUES (1, 1000);
SAVEPOINT sp1;

INSERT INTO accounts (id, balance) VALUES (2, 500);
SAVEPOINT sp2;

-- Rollback to sp2
ROLLBACK TO SAVEPOINT sp2;

-- Commit transaction
COMMIT;

Transaction with Java

conn.setAutoCommit(false);

try {
    Statement stmt = conn.createStatement();

    stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    Savepoint sp = conn.setSavepoint("transfer_start");

    stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

    // Verify balances
    ResultSet rs = stmt.executeQuery(
        "SELECT balance FROM accounts WHERE id = 1"
    );
    if (rs.next() && rs.getDouble("balance") < 0) {
        conn.rollback(sp);
        throw new Exception("Insufficient funds");
    }

    conn.commit();
} catch (Exception e) {
    conn.rollback();
    throw e;
}

Bulk Operations

Batch Insert (JDBC)

PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO employees (name, department_id, salary) VALUES (?, ?, ?)"
);

for (Employee emp : employees) {
    ps.setString(1, emp.getName());
    ps.setInt(2, emp.getDeptId());
    ps.setDouble(3, emp.getSalary());
    ps.addBatch();
}

int[] results = ps.executeBatch();
conn.commit();

LOAD Utility

-- Insert from values
INSERT INTO employees (name, department_id, salary)
VALUES
    ('Alice', 1, 50000),
    ('Bob', 2, 60000),
    ('Charlie', 1, 55000);

Date/Time Examples

Date Arithmetic

-- Add days
SELECT hire_date + 30 DAYS as review_date
FROM employees;

-- Add months
SELECT hire_date + 3 MONTHS as probation_end
FROM employees;

-- Date difference
SELECT DAYS(CURRENT DATE) - DAYS(hire_date) as days_employed
FROM employees;

-- Timestamp difference
SELECT TIMESTAMPDIFF(2, CHAR(CURRENT TIMESTAMP - hire_timestamp)) as hours_employed
FROM employees;

Date Formatting

-- Format date
SELECT
    emp_id,
    VARCHAR_FORMAT(hire_date, 'YYYY-MM-DD') as formatted_date,
    VARCHAR_FORMAT(hire_timestamp, 'YYYY-MM-DD HH24:MI:SS') as formatted_ts
FROM employees;

Query Optimization

OPTIMIZE FOR

-- Optimize for specific row count
SELECT * FROM employees
WHERE department_id = 10
OPTIMIZE FOR 100 ROWS;

Explain Plan

-- Get execution plan
EXPLAIN PLAN FOR
SELECT * FROM employees
WHERE department_id = 10;

Index Usage

-- Create index
CREATE INDEX idx_emp_dept ON employees (department_id);

-- Query using index
SELECT * FROM employees
WHERE department_id = 10;

Python with SQLAlchemy

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

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

Session = sessionmaker(bind=engine)
session = Session()

# Execute query
result = session.execute(text(
    "SELECT * FROM employees FETCH FIRST 10 ROWS ONLY"
))

for row in result:
    print(row)

session.close()

---

Related: README.md | CONFIGURATION.md | COMPATIBILITY.md

Last Updated: December 2025