---
title: "DRDA/DB2 Examples"
---

# DRDA/DB2 Examples

:::caution[Planned: not in the current release]
DB2 (DRDA) protocol support for HeliosDB Full is planned. The current release opens no DRDA listener. Reach
the same data over the PostgreSQL listener or `POST /api/v1/query` today. This page
describes the planned interface.
:::

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

## Connection Examples

### DB2 CLP

```bash
# 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)

```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)

```python
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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
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

```sql
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

```java
// 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

```sql
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

```sql
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

```sql
-- 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

```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)

```java
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

```sql
-- 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

```sql
-- 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

```sql
-- 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

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

### Explain Plan

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

### Index Usage

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

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

## Python with SQLAlchemy

```python
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](https://www.heliosdb.com/docs/full/reference/protocols/drda/readme/) | [CONFIGURATION.md](https://www.heliosdb.com/docs/full/reference/protocols/drda/configuration/) | [COMPATIBILITY.md](https://www.heliosdb.com/docs/full/reference/protocols/drda/compatibility/)