---
title: "IBM DB2 to HeliosDB Migration Guide"
---

# IBM DB2 to HeliosDB Migration Guide

:::caution[Planned: protocol compatibility]
DB2 (DRDA) protocol support is planned. HeliosDB Full opens no DRDA listener. Steps in this guide that rely on the
source protocol describe the planned path; load migrated data and query it over the
PostgreSQL listener or `POST /api/v1/query` today.
:::

> **Container images:** HeliosDB Full images are not published on Docker Hub or any other public registry. Images and Helm charts are provided through private registry access during onboarding: contact [sales@heliosdb.com](mailto:sales@heliosdb.com). The examples on this page use `heliosdb-full:<tag>`, an image built locally from the licensed source distribution (`docker build -t heliosdb-full:<tag> .`); replace it with the image reference you receive if you use the private registry.

**Version:** 1.0
**Compatibility Target:** IBM DB2 LUW 10.5, 11.1, 11.5

---

## Table of Contents

1. [Introduction](#1-introduction)
2. [DRDA Wire Protocol Compatibility](#2-drda-wire-protocol-compatibility)
3. [Pre-Migration Assessment](#3-pre-migration-assessment)
4. [Connection String Migration](#4-connection-string-migration)
5. [Data Type Mapping](#5-data-type-mapping)
6. [SQL Dialect Differences](#6-sql-dialect-differences)
7. [Stored Procedure Migration](#7-stored-procedure-migration)
8. [DB2 EXPORT/IMPORT to HeliosDB COPY](#8-db2-exportimport-to-heliosdb-copy)
9. [Application Connectivity](#9-application-connectivity)
10. [Performance Considerations](#10-performance-considerations)
11. [Troubleshooting Common Issues](#11-troubleshooting-common-issues)
12. [Post-Migration Validation](#12-post-migration-validation)

---

## 1. Introduction

### 1.1 Why Migrate from DB2 to HeliosDB?

Organizations are increasingly migrating from IBM DB2 to HeliosDB for several compelling reasons:

#### Licensing

IBM DB2 is licensed by processor capacity or by user, with annual subscription and support. HeliosDB Full is commercially licensed (source-available under SSPL-1.0); contact [sales@heliosdb.com](mailto:sales@heliosdb.com) for pricing.

#### Multi-Model Support

HeliosDB provides native support for multiple data models through a single platform:

- **Relational SQL**: Full DB2 SQL compatibility
- **Document/JSON**: MongoDB-compatible document storage
- **Key-Value**: Redis-compatible operations
- **Time-Series**: Native time-series optimizations
- **Graph**: Cypher query language support
- **Vector**: AI/ML embeddings with similarity search

#### Modern Architecture Advantages

| Capability | IBM DB2 | HeliosDB |
|------------|---------|----------|
| Cloud-Native | Partial | Full Kubernetes support |
| Multi-Protocol | DB2 only | 9+ protocols |
| Auto-Scaling | Manual | Automatic |
| Self-Healing | Manual intervention | AI-driven autonomous |
| ML Integration | External tools | Native in-database ML |
| HTAP | Separate workloads | Unified workload |

### 1.2 Migration Goals

This guide helps you achieve:

1. **Zero data loss** during migration
2. **Minimal downtime** through parallel operation strategies
3. **Application compatibility** with existing DB2 clients via DRDA protocol
4. **Performance parity or improvement** post-migration
5. **Reduced operational complexity** through HeliosDB automation

---

## 2. DRDA Wire Protocol Compatibility

### 2.1 DRDA Protocol Support

HeliosDB's DRDA (Distributed Relational Database Architecture) protocol support connects DB2 clients:

| DRDA Level | Coverage | Features |
|------------|----------|----------|
| Level 3 | 100% | Basic operations, EXCSAT, ACCRDB |
| Level 4 | 100% | Extended features, cursors |
| Level 5 | 90% | Advanced features |

### 2.2 Connection Parameters

| Parameter | DB2 Default | HeliosDB Default | Notes |
|-----------|-------------|------------------|-------|
| Port | 50000 | 50000 | DRDA port |
| Database | - | heliosdb | Database name |
| Authentication | Password | Password, Kerberos | Multiple options |
| Encryption | SSL/TLS | SSL/TLS | Full encryption support |

### 2.3 Supported DRDA Commands

| Command | Result | Description |
|---------|--------|-------------|
| EXCSAT | Supported | Exchange server attributes |
| EXCSATRD | Supported | Exchange server attributes reply |
| ACCRDB | Supported | Access database |
| ACCRDBRM | Supported | Access reply message |
| SECCHK | Supported | Security check |
| SECCHKRM | Supported | Security reply |
| PRPSQLSTT | Supported | Prepare SQL statement |
| EXCSQLSTT | Supported | Execute SQL statement |
| OPNQRY | Supported | Open query |
| CNTQRY | Supported | Continue query |
| CLSQRY | Supported | Close query |
| SQLSTT | Supported | SQL statement text |
| COMMIT | Supported | Commit transaction |
| ROLLBACK | Supported | Rollback transaction |

### 2.4 Wire Protocol Compatibility

```
Default Port: 50000
Protocol: DRDA (Distributed Relational Database Architecture)
Wire Format: DRDA Level 5 compatible
Character Set: UTF-8, EBCDIC supported
```

**Supported DB2 Drivers:**

| Driver | Minimum Version | Result |
|--------|-----------------|--------|
| IBM DB2 Driver for JDBC | 11.5+ | Full compatibility |
| JDBC Type 4 | 4.x | Full compatibility |
| IBM DB2 ODBC | 11.x | Full compatibility |
| ibm_db (Python) | 3.x | Full compatibility |
| IBM.Data.DB2 (.NET) | 11.x | Full compatibility |
| IBM.Data.DB2.Core (.NET Core) | 3.x | Full compatibility |

---

## 3. Pre-Migration Assessment

### 3.1 Database Inventory

Before migration, perform a comprehensive inventory of your DB2 environment.

#### Schema Analysis Script

```sql
-- Database size summary
SELECT
    TABSCHEMA AS schema_name,
    TABNAME AS table_name,
    NPAGES * 4 / 1024 AS size_mb,
    CARD AS row_count
FROM SYSCAT.TABLES
WHERE TABSCHEMA NOT LIKE 'SYS%'
  AND TYPE = 'T'
ORDER BY NPAGES DESC
FETCH FIRST 50 ROWS ONLY;

-- Schema object counts
SELECT
    TABSCHEMA,
    COUNT(CASE WHEN TYPE = 'T' THEN 1 END) AS tables,
    COUNT(CASE WHEN TYPE = 'V' THEN 1 END) AS views,
    COUNT(CASE WHEN TYPE = 'A' THEN 1 END) AS aliases
FROM SYSCAT.TABLES
WHERE TABSCHEMA NOT LIKE 'SYS%'
GROUP BY TABSCHEMA
ORDER BY tables DESC;

-- Index summary
SELECT
    INDSCHEMA,
    COUNT(*) AS index_count,
    SUM(NLEAF * 4 / 1024) AS total_size_mb
FROM SYSCAT.INDEXES
WHERE INDSCHEMA NOT LIKE 'SYS%'
GROUP BY INDSCHEMA
ORDER BY index_count DESC;
```

### 3.2 Stored Procedure Analysis

Analyze your SQL PL codebase for migration complexity:

```sql
-- Count routine objects by type
SELECT
    ROUTINESCHEMA,
    ROUTINETYPE,
    COUNT(*) AS routine_count,
    SUM(CASE WHEN VALID = 'Y' THEN 1 ELSE 0 END) AS valid_count,
    SUM(CASE WHEN VALID = 'N' THEN 1 ELSE 0 END) AS invalid_count
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA NOT LIKE 'SYS%'
  AND LANGUAGE = 'SQL'
GROUP BY ROUTINESCHEMA, ROUTINETYPE
ORDER BY routine_count DESC;

-- Procedure source code length
SELECT
    ROUTINESCHEMA,
    ROUTINENAME,
    ROUTINETYPE,
    LENGTH(TEXT) AS source_length
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA NOT LIKE 'SYS%'
  AND TEXT IS NOT NULL
ORDER BY source_length DESC
FETCH FIRST 20 ROWS ONLY;

-- External routine dependencies
SELECT
    ROUTINESCHEMA,
    ROUTINENAME,
    LANGUAGE,
    EXTERNAL_NAME
FROM SYSCAT.ROUTINES
WHERE ROUTINESCHEMA NOT LIKE 'SYS%'
  AND LANGUAGE IN ('C', 'JAVA', 'CLR')
ORDER BY ROUTINESCHEMA, ROUTINENAME;
```

### 3.3 Feature Usage Inventory

```sql
-- Check for DB2-specific features
-- MQT (Materialized Query Tables)
SELECT TABSCHEMA, TABNAME, REFRESH
FROM SYSCAT.TABLES
WHERE TYPE = 'S'  -- Summary table
  AND TABSCHEMA NOT LIKE 'SYS%';

-- Range partitioned tables
SELECT TABSCHEMA, TABNAME, DATAPARTITIONKEYSEQ
FROM SYSCAT.DATAPARTITIONEXPRESSION
WHERE TABSCHEMA NOT LIKE 'SYS%';

-- Check for XML columns
SELECT TABSCHEMA, TABNAME, COLNAME
FROM SYSCAT.COLUMNS
WHERE TYPENAME = 'XML'
  AND TABSCHEMA NOT LIKE 'SYS%';

-- User-defined types
SELECT TYPESCHEMA, TYPENAME, METATYPE, SOURCENAME
FROM SYSCAT.DATATYPES
WHERE TYPESCHEMA NOT LIKE 'SYS%';

-- Sequences
SELECT SEQSCHEMA, SEQNAME, START, INCREMENT, CACHE
FROM SYSCAT.SEQUENCES
WHERE SEQSCHEMA NOT LIKE 'SYS%';
```

### 3.4 Application Dependencies

Document all applications connecting to DB2:

```sql
-- Active application snapshots
SELECT
    APPLICATION_NAME,
    CLIENT_USERID,
    CLIENT_WRKSTNNAME,
    CLIENT_APPLNAME,
    CLIENT_PROTOCOL,
    COUNT(*) AS connection_count
FROM SYSIBMADM.SNAPAPPL_INFO
GROUP BY APPLICATION_NAME, CLIENT_USERID, CLIENT_WRKSTNNAME,
         CLIENT_APPLNAME, CLIENT_PROTOCOL
ORDER BY connection_count DESC;
```

### 3.5 Migration Complexity Score

Calculate your migration complexity:

| Factor | Points | Your Score |
|--------|--------|------------|
| Tables < 50 | 1 | |
| Tables 50-200 | 2 | |
| Tables > 200 | 3 | |
| SQL Procedures < 50 | 1 | |
| SQL Procedures 50-200 | 2 | |
| SQL Procedures > 200 | 3 | |
| No external routines (C/Java) | 0 | |
| Uses external routines | 3 | |
| No pureScale/HADR | 0 | |
| Uses pureScale | 2 | |
| No XML columns | 0 | |
| Uses XML extensively | 2 | |

**Total Score Interpretation:**
- 1-4: Simple migration (1-2 weeks)
- 5-8: Medium complexity (2-6 weeks)
- 9+: Complex migration (6+ weeks)

---

## 4. Connection String Migration

### 4.1 DB2 CLP to HeliosDB

**DB2 Command Line Processor:**

```bash
# DB2 CLP connection
db2 connect to MYDB user db2admin using password

# HeliosDB via DRDA (identical syntax)
db2 connect to heliosdb user admin using password
```

### 4.2 Catalog Configuration

**DB2 Node and Database Catalog:**

```bash
# DB2: Catalog remote node
db2 catalog tcpip node HELIOS_NODE remote heliosdb-server server 50000
db2 catalog database heliosdb as HELIOSDB at node HELIOS_NODE

# Connect using catalog alias
db2 connect to HELIOSDB user admin using password
```

**db2cli.ini Configuration:**

```ini
[heliosdb]
Database=heliosdb
Protocol=TCPIP
Hostname=heliosdb-server
ServiceName=50000
UID=admin
PWD=password
CurrentSchema=myschema
```

### 4.3 JDBC Connection Strings

**DB2 JDBC Type 4:**

```java
// DB2 connection
String db2Url = "jdbc:db2://db2-server:50000/MYDB";

// HeliosDB connection (same driver works)
String heliosUrl = "jdbc:db2://heliosdb-server:50000/heliosdb";

// With connection properties
String heliosUrlFull = "jdbc:db2://heliosdb-server:50000/heliosdb:" +
    "currentSchema=myschema;" +
    "securityMechanism=3;" +  // Clear text password
    "sslConnection=true;";
```

### 4.4 ODBC DSN Configuration

**Windows ODBC (odbc.ini):**

```ini
[HeliosDB_DSN]
Description=HeliosDB via DRDA
Driver=IBM DB2 ODBC DRIVER
Database=heliosdb
Hostname=heliosdb-server
Port=50000
Protocol=TCPIP
UID=admin
PWD=password
```

**Linux unixODBC (odbcinst.ini):**

```ini
[IBM DB2 ODBC DRIVER]
Description=IBM DB2 ODBC Driver
Driver=/opt/ibm/db2/V11.5/lib64/libdb2o.so

[HeliosDB]
Driver=IBM DB2 ODBC DRIVER
Database=heliosdb
Hostname=heliosdb-server
Port=50000
Protocol=TCPIP
```

---

## 5. Data Type Mapping

### 5.1 Numeric Types

| DB2 Type | HeliosDB Type | Notes |
|----------|---------------|-------|
| SMALLINT | SMALLINT | 2-byte integer, direct mapping |
| INTEGER | INTEGER | 4-byte integer, direct mapping |
| BIGINT | BIGINT | 8-byte integer, direct mapping |
| DECIMAL(p,s) | DECIMAL(p,s) | Fixed precision, direct mapping |
| NUMERIC(p,s) | NUMERIC(p,s) | Same as DECIMAL |
| REAL | REAL | 4-byte float |
| DOUBLE | DOUBLE | 8-byte float |
| DECFLOAT(16) | DECFLOAT(16) | IEEE 754-2008 decimal |
| DECFLOAT(34) | DECFLOAT(34) | Extended decimal float |

### 5.2 Character Types

| DB2 Type | HeliosDB Type | Notes |
|----------|---------------|-------|
| CHAR(n) | CHAR(n) | Fixed length, max 254 |
| VARCHAR(n) | VARCHAR(n) | Variable length, max 32672 |
| LONG VARCHAR | VARCHAR(32700) | Deprecated, use VARCHAR |
| CLOB(n) | CLOB(n) | Character LOB, up to 2GB |
| GRAPHIC(n) | CHAR(n) | Double-byte characters |
| VARGRAPHIC(n) | VARCHAR(n) | Variable double-byte |
| DBCLOB(n) | CLOB(n) | Double-byte CLOB |

### 5.3 Binary Types

| DB2 Type | HeliosDB Type | Notes |
|----------|---------------|-------|
| CHAR(n) FOR BIT DATA | BINARY(n) | Fixed binary |
| VARCHAR(n) FOR BIT DATA | VARBINARY(n) | Variable binary |
| BLOB(n) | BLOB(n) | Binary LOB, up to 2GB |
| BINARY(n) | BINARY(n) | DB2 11.1+ |
| VARBINARY(n) | VARBINARY(n) | DB2 11.1+ |

### 5.4 Date/Time Types

| DB2 Type | HeliosDB Type | Notes |
|----------|---------------|-------|
| DATE | DATE | Calendar date |
| TIME | TIME | Time of day |
| TIMESTAMP | TIMESTAMP | Date and time |
| TIMESTAMP(p) | TIMESTAMP(p) | Fractional seconds precision |

### 5.5 Special Types

| DB2 Type | HeliosDB Type | Notes |
|----------|---------------|-------|
| BOOLEAN | BOOLEAN | DB2 11.1+ |
| XML | JSONB | Convert to JSON |
| ROW type | RECORD | Composite types |
| ARRAY | ARRAY | Collection type |

### 5.6 Data Type Conversion Script

```sql
-- Generate column type conversion report
SELECT
    c.TABSCHEMA,
    c.TABNAME,
    c.COLNAME,
    c.TYPENAME AS db2_type,
    c.LENGTH,
    c.SCALE,
    CASE c.TYPENAME
        WHEN 'LONG VARCHAR' THEN 'VARCHAR(32700)'
        WHEN 'GRAPHIC' THEN 'CHAR(' || c.LENGTH/2 || ')'
        WHEN 'VARGRAPHIC' THEN 'VARCHAR(' || c.LENGTH/2 || ')'
        WHEN 'DBCLOB' THEN 'CLOB'
        WHEN 'XML' THEN 'JSONB'
        ELSE c.TYPENAME
    END AS helios_type
FROM SYSCAT.COLUMNS c
WHERE c.TABSCHEMA NOT LIKE 'SYS%'
  AND c.TYPENAME IN ('LONG VARCHAR', 'GRAPHIC', 'VARGRAPHIC', 'DBCLOB', 'XML')
ORDER BY c.TABSCHEMA, c.TABNAME, c.COLNO;
```

---

## 6. SQL Dialect Differences

### 6.1 Pagination and Row Limiting

**DB2 Syntax (fully supported in HeliosDB):**

```sql
-- FETCH FIRST N ROWS ONLY
SELECT * FROM employees
ORDER BY hire_date DESC
FETCH FIRST 10 ROWS ONLY;

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

-- FETCH with PERCENT
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
```

### 6.2 Isolation Levels

**DB2 Lock Hints (supported):**

```sql
-- Uncommitted read
SELECT * FROM employees WITH UR;

-- Cursor stability
SELECT * FROM employees WITH CS;

-- Read stability
SELECT * FROM employees WITH RS;

-- Repeatable read
SELECT * FROM employees WITH RR;
```

### 6.3 Special Registers

| DB2 Register | HeliosDB Equivalent | Notes |
|--------------|---------------------|-------|
| CURRENT DATE | CURRENT DATE | Direct support |
| CURRENT TIME | CURRENT TIME | Direct support |
| CURRENT TIMESTAMP | CURRENT TIMESTAMP | Direct support |
| CURRENT USER | CURRENT_USER | Standard SQL |
| CURRENT SCHEMA | CURRENT_SCHEMA | Direct support |
| CURRENT PATH | CURRENT_PATH | Direct support |

**Example:**

```sql
-- Both work identically
SELECT CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

-- HeliosDB alternative (no SYSDUMMY1 needed)
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
```

### 6.4 MERGE Statement

**DB2 MERGE (fully supported):**

```sql
MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name,
        t.updated_at = CURRENT TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, name, created_at)
    VALUES (s.id, s.name, CURRENT TIMESTAMP);
```

### 6.5 Recursive CTEs

**DB2 Recursive Query (fully supported):**

```sql
WITH org_hierarchy (emp_id, emp_name, manager_id, level) AS (
    -- Anchor member
    SELECT emp_id, emp_name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.emp_id, e.emp_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM org_hierarchy
ORDER BY level, emp_name;
```

### 6.6 OLAP Functions

**DB2 Window Functions (fully supported):**

```sql
SELECT
    emp_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
    AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;
```

### 6.7 Identity Columns

**DB2 Identity (fully supported):**

```sql
-- DB2 identity column
CREATE TABLE orders (
    order_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH 1 INCREMENT BY 1),
    order_date DATE,
    amount DECIMAL(10,2)
);

-- HeliosDB (identical syntax supported)
CREATE TABLE orders (
    order_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
        (START WITH 1 INCREMENT BY 1),
    order_date DATE,
    amount DECIMAL(10,2)
);
```

### 6.8 Sequence Objects

```sql
-- Create sequence (identical syntax)
CREATE SEQUENCE order_seq
    AS INTEGER
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    CYCLE
    CACHE 100;

-- Use sequence
INSERT INTO orders (order_id, order_date)
VALUES (NEXT VALUE FOR order_seq, CURRENT DATE);

-- Query current value
SELECT PREVIOUS VALUE FOR order_seq FROM SYSIBM.SYSDUMMY1;
```

### 6.9 DB2-Specific Functions Mapping

| DB2 Function | HeliosDB Function | Notes |
|--------------|-------------------|-------|
| DAYS(date) | DAYS(date) | Days since 0001-01-01 |
| MIDNIGHT_SECONDS(time) | MIDNIGHT_SECONDS(time) | Seconds since midnight |
| TIMESTAMPDIFF(interval, t1, t2) | TIMESTAMPDIFF(interval, t1, t2) | Time difference |
| VARCHAR_FORMAT(ts, fmt) | TO_CHAR(ts, fmt) | Date formatting |
| TIMESTAMP_FORMAT(str, fmt) | TO_TIMESTAMP(str, fmt) | String to timestamp |
| RAISE_ERROR(code, msg) | SIGNAL SQLSTATE | Error signaling |
| COALESCE(a, b, c) | COALESCE(a, b, c) | First non-null |
| NULLIF(a, b) | NULLIF(a, b) | Null if equal |
| VALUE(a, b) | COALESCE(a, b) | DB2 alias for COALESCE |
| POSSTR(str, search) | POSITION(search IN str) | Find position |
| SUBSTR(str, pos, len) | SUBSTR(str, pos, len) | Substring |
| STRIP(str) | TRIM(str) | Remove whitespace |
| DIGITS(num) | LPAD(num::text, n, '0') | Number to string |

---

## 7. Stored Procedure Migration

### 7.1 SQL Procedure Compatibility

HeliosDB provides 85% compatibility with DB2 SQL PL procedures:

#### Basic Procedure Structure

```sql
-- DB2 SQL Procedure (fully supported)
CREATE OR REPLACE PROCEDURE update_salary (
    IN p_emp_id INTEGER,
    IN p_increase DECIMAL(5,2),
    OUT p_new_salary DECIMAL(10,2)
)
LANGUAGE SQL
BEGIN
    DECLARE v_current_salary DECIMAL(10,2);

    SELECT salary INTO v_current_salary
    FROM employees
    WHERE emp_id = p_emp_id;

    SET p_new_salary = v_current_salary * (1 + p_increase / 100);

    UPDATE employees
    SET salary = p_new_salary
    WHERE emp_id = p_emp_id;
END
```

### 7.2 Control Flow Statements

```sql
CREATE OR REPLACE PROCEDURE process_order (
    IN p_order_id INTEGER,
    OUT p_status VARCHAR(50)
)
LANGUAGE SQL
BEGIN
    DECLARE v_total DECIMAL(12,2);
    DECLARE v_customer_type VARCHAR(20);

    -- IF-THEN-ELSEIF-ELSE
    SELECT total_amount, customer_type
    INTO v_total, v_customer_type
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE o.order_id = p_order_id;

    IF v_total > 10000 THEN
        SET p_status = 'HIGH_VALUE';
    ELSEIF v_total > 1000 THEN
        SET p_status = 'MEDIUM_VALUE';
    ELSE
        SET p_status = 'LOW_VALUE';
    END IF;

    -- CASE statement
    SET p_status = p_status || ' - ' ||
        CASE v_customer_type
            WHEN 'PREMIUM' THEN 'PRIORITY'
            WHEN 'STANDARD' THEN 'NORMAL'
            ELSE 'BASIC'
        END;
END
```

### 7.3 Cursor Operations

```sql
CREATE OR REPLACE PROCEDURE process_pending_orders ()
LANGUAGE SQL
BEGIN
    DECLARE v_order_id INTEGER;
    DECLARE v_amount DECIMAL(12,2);
    DECLARE v_end_of_cursor INTEGER DEFAULT 0;

    -- Declare cursor
    DECLARE order_cursor CURSOR FOR
        SELECT order_id, total_amount
        FROM orders
        WHERE status = 'PENDING'
        ORDER BY created_at;

    -- Handler for end of cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_end_of_cursor = 1;

    OPEN order_cursor;

    fetch_loop: LOOP
        FETCH order_cursor INTO v_order_id, v_amount;

        IF v_end_of_cursor = 1 THEN
            LEAVE fetch_loop;
        END IF;

        -- Process order
        UPDATE orders
        SET status = 'PROCESSING',
            processed_at = CURRENT TIMESTAMP
        WHERE order_id = v_order_id;
    END LOOP fetch_loop;

    CLOSE order_cursor;
END
```

### 7.4 Exception Handling

```sql
CREATE OR REPLACE PROCEDURE transfer_funds (
    IN p_from_account INTEGER,
    IN p_to_account INTEGER,
    IN p_amount DECIMAL(12,2),
    OUT p_result VARCHAR(100)
)
LANGUAGE SQL
BEGIN
    DECLARE v_balance DECIMAL(12,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'ERROR: Transaction failed - ' || SQLERRM;
    END;

    DECLARE EXIT HANDLER FOR SQLSTATE '45001'
    BEGIN
        ROLLBACK;
        SET p_result = 'ERROR: Insufficient funds';
    END;

    -- Check balance
    SELECT balance INTO v_balance
    FROM accounts
    WHERE account_id = p_from_account;

    IF v_balance < p_amount THEN
        SIGNAL SQLSTATE '45001';
    END IF;

    -- Perform transfer
    UPDATE accounts SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    UPDATE accounts SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;
    SET p_result = 'SUCCESS: Transferred ' || p_amount;
END
```

### 7.5 User-Defined Functions

```sql
-- Scalar function
CREATE OR REPLACE FUNCTION calculate_tax (
    p_amount DECIMAL(12,2),
    p_rate DECIMAL(5,2)
)
RETURNS DECIMAL(12,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN
    RETURN p_amount * (p_rate / 100);
END

-- Table function
CREATE OR REPLACE FUNCTION get_department_employees (
    p_dept_id INTEGER
)
RETURNS TABLE (
    emp_id INTEGER,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2)
)
LANGUAGE SQL
READS SQL DATA
BEGIN
    RETURN
        SELECT emp_id, emp_name, salary
        FROM employees
        WHERE department_id = p_dept_id;
END
```

### 7.6 Triggers

```sql
-- BEFORE trigger
CREATE OR REPLACE 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;
    SET n.emp_name = UPPER(n.emp_name);
END

-- AFTER trigger
CREATE OR REPLACE TRIGGER emp_audit_trigger
AFTER UPDATE ON employees
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
BEGIN ATOMIC
    INSERT INTO employee_audit (
        emp_id, action, old_salary, new_salary, changed_at, changed_by
    ) VALUES (
        n.emp_id, 'UPDATE', o.salary, n.salary, CURRENT TIMESTAMP, CURRENT USER
    );
END

-- Statement trigger
CREATE OR REPLACE TRIGGER orders_batch_trigger
AFTER INSERT ON orders
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
BEGIN ATOMIC
    INSERT INTO order_statistics (batch_date, order_count, total_amount)
    SELECT CURRENT DATE, COUNT(*), SUM(amount)
    FROM inserted;
END
```

### 7.7 External Procedure Conversion

**DB2 External Procedures** (C, Java, CLR) require conversion to SQL procedures or HeliosDB extensions:

```sql
-- DB2 external procedure (Java)
CREATE PROCEDURE java_proc (IN p_input VARCHAR(100))
EXTERNAL NAME 'com.example.MyClass.myMethod'
LANGUAGE JAVA
PARAMETER STYLE JAVA;

-- HeliosDB conversion: SQL procedure or extension
CREATE OR REPLACE PROCEDURE converted_proc (IN p_input VARCHAR(100))
LANGUAGE SQL
BEGIN
    -- Re-implement logic in SQL
    -- Or use HeliosDB extension framework
END
```

---

## 8. DB2 EXPORT/IMPORT to HeliosDB COPY

### 8.1 Basic Data Export from DB2

**DB2 EXPORT Command:**

```bash
# Export to IXF format (preserves types)
db2 "EXPORT TO employees.ixf OF IXF
     SELECT * FROM employees"

# Export to DEL format (CSV-like)
db2 "EXPORT TO employees.csv OF DEL
     MODIFIED BY COLDEL, CHARDEL\"\" DECPT.
     SELECT * FROM employees"

# Export with column headers
db2 "EXPORT TO employees.csv OF DEL
     MODIFIED BY NOCHARDEL COLHDRS
     SELECT * FROM employees"
```

### 8.2 HeliosDB COPY Command

**Import CSV to HeliosDB:**

```sql
-- Basic COPY from CSV
COPY employees FROM '/path/to/employees.csv'
WITH (FORMAT csv, HEADER true);

-- With options
COPY employees FROM '/path/to/employees.csv'
WITH (
    FORMAT csv,
    HEADER true,
    DELIMITER ',',
    NULL '',
    QUOTE '"',
    ESCAPE '\\'
);

-- COPY specific columns
COPY employees (emp_id, emp_name, department_id, salary)
FROM '/path/to/employees.csv'
WITH (FORMAT csv, HEADER true);
```

### 8.3 Large Table Migration Strategy

**Step 1: Export from DB2 with partitioning:**

```bash
#!/bin/bash
# Export large table in chunks

TABLE="large_orders"
CHUNK_SIZE=1000000

for i in $(seq 0 9); do
    db2 "EXPORT TO ${TABLE}_part${i}.csv OF DEL
         MODIFIED BY NOCHARDEL
         SELECT * FROM ${TABLE}
         WHERE MOD(order_id, 10) = ${i}"
done
```

**Step 2: Parallel import to HeliosDB:**

```sql
-- Create staging table
CREATE TABLE orders_staging (LIKE orders INCLUDING ALL);

-- Parallel COPY (run concurrently)
COPY orders_staging FROM '/path/to/large_orders_part0.csv' WITH (FORMAT csv);
COPY orders_staging FROM '/path/to/large_orders_part1.csv' WITH (FORMAT csv);
-- ... continue for all parts

-- Swap tables
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_staging RENAME TO orders;
DROP TABLE orders_old;
```

### 8.4 LOB Data Migration

**DB2 LOB Export:**

```bash
# Export with LOBs to separate files
db2 "EXPORT TO documents.csv OF DEL
     LOBS TO /lob_data/
     LOBFILE documents
     MODIFIED BY LOBSINFILE
     SELECT doc_id, doc_content FROM documents"
```

**HeliosDB LOB Import:**

```sql
-- Create table with LOB column
CREATE TABLE documents (
    doc_id INTEGER PRIMARY KEY,
    doc_content BYTEA
);

-- Import using pg_read_binary_file or application code
-- For large LOBs, use client-side import
```

### 8.5 Migration Validation Script

```sql
-- Generate row count comparison queries
SELECT
    'SELECT ''' || TABNAME || ''' AS table_name, COUNT(*) AS row_count FROM ' ||
    TABSCHEMA || '.' || TABNAME || ';'
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'MYSCHEMA'
  AND TYPE = 'T'
ORDER BY TABNAME;
```

---

## 9. Application Connectivity

### 9.1 Java (JDBC) Configuration

**Maven Dependency:**

```xml
<dependency>
    <groupId>com.ibm.db2</groupId>
    <artifactId>jcc</artifactId>
    <version>11.5.8.0</version>
</dependency>
```

**Connection Example:**

```java
import java.sql.*;
import com.ibm.db2.jcc.DB2SimpleDataSource;

public class HeliosDBConnection {
    public static void main(String[] args) throws SQLException {
        // Simple connection
        String url = "jdbc:db2://heliosdb-server:50000/heliosdb";
        Connection conn = DriverManager.getConnection(url, "admin", "password");

        // Using DataSource with connection pool
        DB2SimpleDataSource ds = new DB2SimpleDataSource();
        ds.setDatabaseName("heliosdb");
        ds.setServerName("heliosdb-server");
        ds.setPortNumber(50000);
        ds.setUser("admin");
        ds.setPassword("password");
        ds.setCurrentSchema("myschema");

        // SSL/TLS connection
        ds.setSslConnection(true);
        ds.setSslCertLocation("/path/to/cert.pem");

        Connection pooledConn = ds.getConnection();

        // Execute query
        try (Statement stmt = pooledConn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT * FROM employees FETCH FIRST 10 ROWS ONLY")) {
            while (rs.next()) {
                System.out.println(rs.getString("emp_name"));
            }
        }
    }
}
```

**Spring Boot Configuration:**

```yaml
# application.yml
spring:
  datasource:
    url: jdbc:db2://heliosdb-server:50000/heliosdb
    username: admin
    password: password
    driver-class-name: com.ibm.db2.jcc.DB2Driver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
```

### 9.2 Python (ibm_db) Configuration

**Installation:**

```bash
pip install ibm_db ibm_db_sa
```

**Connection Example:**

```python
import ibm_db
import ibm_db_dbi

# Direct connection
conn_string = (
    "DATABASE=heliosdb;"
    "HOSTNAME=heliosdb-server;"
    "PORT=50000;"
    "PROTOCOL=TCPIP;"
    "UID=admin;"
    "PWD=password;"
)

# ibm_db connection
ibm_db_conn = ibm_db.connect(conn_string, "", "")

# Execute query
stmt = ibm_db.exec_immediate(ibm_db_conn,
    "SELECT * FROM employees FETCH FIRST 10 ROWS ONLY")

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

ibm_db.close(ibm_db_conn)

# Using ibm_db_dbi (DB-API 2.0 compatible)
conn = ibm_db_dbi.connect(conn_string, "", "")
cursor = conn.cursor()

cursor.execute("SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = ?", (10,))
for row in cursor.fetchall():
    print(f"{row['EMP_ID']}: {row['EMP_NAME']}")

cursor.close()
conn.close()
```

**SQLAlchemy Integration:**

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

# Connection URL
engine = create_engine(
    "ibm_db_sa://admin:password@heliosdb-server:50000/heliosdb",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True
)

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

### 9.3.NET Configuration

**NuGet Package:**

```xml
<PackageReference Include="IBM.Data.DB2.Core" Version="3.1.0.500" />
```

**Connection Example:**

```csharp
using IBM.Data.DB2.Core;
using System;

class Program
{
    static void Main()
    {
        string connString = @"
            Server=heliosdb-server:50000;
            Database=heliosdb;
            UID=admin;
            PWD=password;
            CurrentSchema=myschema;";

        using (DB2Connection conn = new DB2Connection(connString))
        {
            conn.Open();

            using (DB2Command cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"
                    SELECT emp_id, emp_name, salary
                    FROM employees
                    WHERE department_id = @deptId
                    FETCH FIRST 10 ROWS ONLY";

                cmd.Parameters.Add("@deptId", DB2Type.Integer).Value = 10;

                using (DB2DataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader["emp_id"]}: {reader["emp_name"]}");
                    }
                }
            }
        }
    }
}
```

**Entity Framework Core:**

```csharp
// DbContext configuration
public class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseDb2("Server=heliosdb-server:50000;Database=heliosdb;UID=admin;PWD=password");
    }
}
```

### 9.4 Node.js Configuration

**Installation:**

```bash
npm install ibm_db
```

**Connection Example:**

```javascript
const ibmdb = require('ibm_db');

const connString =
    "DATABASE=heliosdb;" +
    "HOSTNAME=heliosdb-server;" +
    "PORT=50000;" +
    "PROTOCOL=TCPIP;" +
    "UID=admin;" +
    "PWD=password;";

// Async connection
ibmdb.open(connString, (err, conn) => {
    if (err) {
        console.error('Connection failed:', err);
        return;
    }

    conn.query("SELECT * FROM employees FETCH FIRST 10 ROWS ONLY", (err, data) => {
        if (err) {
            console.error('Query failed:', err);
        } else {
            console.log(data);
        }

        conn.close(() => {
            console.log('Connection closed');
        });
    });
});

// Using promises
async function queryEmployees() {
    const conn = await ibmdb.open(connString);
    try {
        const result = await conn.query(
            "SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = ?",
            [10]
        );
        return result;
    } finally {
        await conn.close();
    }
}
```

---

## 10. Performance Considerations

### 10.1 Query Optimization Hints

**DB2 Optimization Hints (supported):**

```sql
-- Optimize for N rows
SELECT * FROM orders
WHERE customer_id = 123
OPTIMIZE FOR 1 ROW;

-- Optimize for all rows
SELECT * FROM orders
WHERE status = 'PENDING'
OPTIMIZE FOR ALL ROWS;

-- Force index usage
SELECT * FROM employees
WHERE department_id = 10
    -- HeliosDB uses similar hint syntax
```

### 10.2 Statistics Collection

> **`ANALYZE` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today.

```sql
-- DB2 RUNSTATS equivalent in HeliosDB
ANALYZE employees;

-- Analyze specific columns
ANALYZE employees (department_id, salary);

-- Analyze with sampling (for large tables)
ANALYZE employees TABLESAMPLE SYSTEM (10);

-- View statistics
SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    last_analyze
FROM pg_stat_user_tables
WHERE tablename = 'employees';
```

### 10.3 Index Migration Best Practices

```sql
-- Identify key indexes from DB2
SELECT
    INDSCHEMA,
    INDNAME,
    TABSCHEMA,
    TABNAME,
    COLNAMES,
    UNIQUERULE,
    INDEXTYPE
FROM SYSCAT.INDEXES
WHERE TABSCHEMA NOT LIKE 'SYS%'
ORDER BY NLEAF DESC
FETCH FIRST 20 ROWS ONLY;

-- Create equivalent indexes in HeliosDB
CREATE INDEX idx_emp_dept ON employees (department_id);
CREATE UNIQUE INDEX idx_emp_email ON employees (email);

-- Partial indexes (HeliosDB enhancement)
CREATE INDEX idx_active_orders ON orders (customer_id, order_date)
WHERE status = 'ACTIVE';
```

### 10.4 Connection Pooling

**HikariCP Configuration (Java):**

```yaml
# Recommended pool settings
spring:
  datasource:
    hikari:
      maximum-pool-size: 50
      minimum-idle: 10
      idle-timeout: 300000
      max-lifetime: 1200000
      connection-timeout: 30000
      validation-timeout: 5000
      leak-detection-threshold: 60000
```

### 10.5 Batch Operations

```java
// Java batch insert
try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);

    String sql = "INSERT INTO orders (order_id, customer_id, amount) VALUES (?, ?, ?)";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        for (Order order : orders) {
            pstmt.setInt(1, order.getId());
            pstmt.setInt(2, order.getCustomerId());
            pstmt.setBigDecimal(3, order.getAmount());
            pstmt.addBatch();

            if (batchCount++ % 1000 == 0) {
                pstmt.executeBatch();
            }
        }
        pstmt.executeBatch();
    }
    conn.commit();
}
```

### 10.6 HeliosDB Performance Features

| Feature | Description | Benefit |
|---------|-------------|---------|
| **Auto-Indexing** | ML-based index recommendations | 30-50% query improvement |
| **Adaptive Execution** | Runtime plan optimization | Handles data skew |
| **Parallel Query** | Automatic parallelization | Linear scaling |
| **Result Caching** | Multi-tier caching | 10-100x for repeated queries |
| **Predicate Pushdown** | Filter at storage layer | 3-10x faster scans |

---

## 11. Troubleshooting Common Issues

### 11.1 Connection Issues

#### Error: SQL30081N (Communication Error)

**Cause:** Network connectivity or firewall issues

**Solution:**

```bash
# Verify network connectivity
telnet heliosdb-server 50000
ping heliosdb-server

# Check firewall
iptables -L -n | grep 50000

# Verify HeliosDB DRDA listener
heliosdb status --protocol drda
```

#### Error: SQL1403N (Authentication Failed)

**Cause:** Invalid credentials or authentication method mismatch

**Solution:**

```bash
# Test connection with different auth methods
db2 "CONNECT TO heliosdb USER admin USING 'password'"

# Check authentication configuration
cat /etc/heliosdb/heliosdb.toml | grep -A5 "\[drda\]"
```

### 11.2 SQL Syntax Issues

#### Error: SQL0206N (Column Not Found)

**Cause:** Case sensitivity differences

**Solution:**

```sql
-- DB2 is case-insensitive by default
-- HeliosDB may require exact case or double quotes

-- If column created as uppercase
SELECT "EMPLOYEE_NAME" FROM employees;

-- Or use case-insensitive schema setting
SET search_path TO myschema;
```

#### Error: SQL0204N (Object Not Found)

**Cause:** Schema not in search path

**Solution:**

```sql
-- Set current schema
SET CURRENT SCHEMA = myschema;

-- Or use fully qualified names
SELECT * FROM myschema.employees;

-- Check available schemas
SELECT schema_name FROM information_schema.schemata;
```

### 11.3 Data Type Issues

#### Error: SQL0420N (Character Conversion Error)

**Cause:** Character set mismatch

**Solution:**

```bash
# Verify database character set
db2 "SELECT CODEPAGE FROM SYSCAT.DATATYPES WHERE TYPENAME = 'VARCHAR'"

# HeliosDB uses UTF-8
# Convert data during export if needed
db2 "EXPORT TO data.csv OF DEL MODIFIED BY CODEPAGE=1208 ..."
```

#### Error: SQL0802N (Arithmetic Overflow)

**Cause:** Numeric precision differences

**Solution:**

```sql
-- Check column definitions
SELECT COLNAME, TYPENAME, LENGTH, SCALE
FROM SYSCAT.COLUMNS
WHERE TABNAME = 'MYTABLE';

-- Adjust column precision in HeliosDB
ALTER TABLE mytable ALTER COLUMN amount TYPE DECIMAL(15,2);
```

### 11.4 Performance Issues

#### Slow Query After Migration

**Cause:** Missing statistics or indexes

**Solution:**

```sql
-- Analyze all tables
ANALYZE;

-- Check for missing indexes
EXPLAIN ANALYZE SELECT ... ;

-- Review index recommendations
SELECT * FROM heliosdb_index_advisor WHERE table_name = 'orders';
```

#### High Memory Usage

**Cause:** Large result sets or inefficient queries

**Solution:**

```sql
-- Use pagination
SELECT * FROM large_table
FETCH FIRST 1000 ROWS ONLY;

-- Enable result streaming (in application)
statement.setFetchSize(1000);
```

### 11.5 Error Code Mapping

| DB2 SQLCODE | Description | HeliosDB Equivalent |
|-------------|-------------|---------------------|
| SQL0803N | Duplicate key | 23505 (unique_violation) |
| SQL0530N | FK constraint violation | 23503 (foreign_key_violation) |
| SQL0545N | Check constraint violation | 23514 (check_violation) |
| SQL0911N | Deadlock or timeout | 40P01 (deadlock_detected) |
| SQL0913N | Deadlock rollback | 40001 (serialization_failure) |
| SQL0204N | Object not found | 42P01 (undefined_table) |
| SQL0206N | Column not found | 42703 (undefined_column) |
| SQL0100 | No data found | 02000 (no_data) |
| SQL0104N | Syntax error | 42601 (syntax_error) |

---

## 12. Post-Migration Validation

### 12.1 Data Integrity Validation

#### Row Count Verification

```sql
-- DB2: Generate row counts
SELECT
    TABSCHEMA || '.' || TABNAME AS table_name,
    CARD AS row_count
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'MYSCHEMA'
  AND TYPE = 'T'
ORDER BY TABNAME;

-- HeliosDB: Compare row counts
SELECT
    schemaname || '.' || relname AS table_name,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'myschema'
ORDER BY relname;
```

#### Checksum Validation

```sql
-- Sample checksum query
SELECT
    COUNT(*) AS row_count,
    SUM(CAST(emp_id AS BIGINT)) AS id_sum,
    SUM(CAST(salary AS DECIMAL(20,2))) AS salary_sum
FROM employees;
```

### 12.2 Functional Validation

#### Stored Procedure Testing

```sql
-- Create test tracking table
CREATE TABLE migration_tests (
    test_id SERIAL PRIMARY KEY,
    test_name VARCHAR(200),
    test_type VARCHAR(50),
    db2_result TEXT,
    helios_result TEXT,
    status VARCHAR(20),
    tested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Test procedure execution
DO $$
DECLARE
    v_result TEXT;
BEGIN
    CALL my_procedure(123, v_result);

    INSERT INTO migration_tests (test_name, test_type, helios_result, status)
    VALUES ('my_procedure', 'PROCEDURE', v_result,
            CASE WHEN v_result = 'expected' THEN 'PASS' ELSE 'FAIL' END);
END $$;
```

### 12.3 Application Validation Checklist

| Category | Validation Item | Result |
|----------|-----------------|--------|
| **Connectivity** | All applications connect successfully | [ ] |
| **Connectivity** | Connection pooling works | [ ] |
| **Connectivity** | SSL/TLS encryption enabled | [ ] |
| **Data** | Row counts match | [ ] |
| **Data** | Checksums match | [ ] |
| **Data** | Sample data spot checks | [ ] |
| **Schema** | All tables created | [ ] |
| **Schema** | All indexes created | [ ] |
| **Schema** | All constraints active | [ ] |
| **Schema** | All sequences created | [ ] |
| **Procedures** | All procedures compile | [ ] |
| **Procedures** | Procedures return correct values | [ ] |
| **Triggers** | Triggers fire correctly | [ ] |
| **Security** | Users created | [ ] |
| **Security** | Grants applied | [ ] |
| **Performance** | Key queries perform acceptably | [ ] |
| **Application** | CRUD operations work | [ ] |
| **Application** | Transactions commit/rollback | [ ] |

### 12.4 Performance Baseline Comparison

```sql
-- Capture query execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.customer_name
ORDER BY total_amount DESC
FETCH FIRST 100 ROWS ONLY;
```

### 12.5 Rollback Plan

In case issues are discovered post-migration:

#### Quick Rollback Steps

1. **Stop application writes to HeliosDB**
2. **Redirect connections back to DB2**
   ```bash
   # Update DNS or load balancer to point to DB2
   # Or update db2cli.ini to original DB2 server
   ```
3. **Sync any delta data** (if dual-write was enabled)
4. **Verify DB2 operations**
5. **Investigate and resolve HeliosDB issues**
6. **Re-attempt migration when ready**

---

## Appendix A: Quick Reference Card

### Essential Commands

```bash
# Connect via DB2 CLP
db2 connect to heliosdb user admin using password

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

# Check connection status
db2 "SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1"
```

### Connection Parameters

| Parameter | Default | Description |
|-----------|---------|-------------|
| Port | 50000 | DRDA port |
| Database | heliosdb | Database name |
| Protocol | TCPIP | Connection protocol |

### Configuration Files

| File | Location | Purpose |
|------|----------|---------|
| db2cli.ini | ~/sqllib/cfg/ | CLI configuration |
| db2dsdriver.cfg | ~/sqllib/cfg/ | Data source config |
| heliosdb.toml | /etc/heliosdb/ | Server configuration |

---

## Appendix B: Migration Timeline Template

| Week | Phase | Activities |
|------|-------|------------|
| 1 | Assessment | Inventory, complexity analysis |
| 2 | Planning | Design migration strategy, test plan |
| 3-4 | Schema Migration | Export DDL, translate, create in HeliosDB |
| 5-6 | Data Migration | Initial data load, verify |
| 7-8 | Procedure Migration | Convert SQL PL procedures, functions |
| 9 | Testing | Functional testing, performance testing |
| 10 | Parallel Run | Dual-write, validate |
| 11 | Cutover | Switch primary to HeliosDB |
| 12 | Stabilization | Monitor, optimize, close out |

---

**Related Documentation:**
- [DRDA Protocol Configuration](https://www.heliosdb.com/docs/full/reference/protocols/drda/configuration/)
- [DRDA Compatibility Reference](https://www.heliosdb.com/docs/full/reference/protocols/drda/compatibility/)
- [DRDA SQL Examples](https://www.heliosdb.com/docs/full/reference/protocols/drda/examples/)
- [General Migration Guide](https://www.heliosdb.com/docs/full/reference/protocols/migration_guide/)