Skip to content

IBM DB2 to HeliosDB Migration Guide

Version: 1.0 Last Updated: January 2026 Compatibility Target: IBM DB2 LUW 10.5, 11.1, 11.5

Table of Contents

  1. Introduction
  2. DRDA Wire Protocol Compatibility
  3. Pre-Migration Assessment
  4. Connection String Migration
  5. Data Type Mapping
  6. SQL Dialect Differences
  7. Stored Procedure Migration
  8. DB2 EXPORT/IMPORT to HeliosDB COPY
  9. Application Connectivity
  10. Performance Considerations
  11. Troubleshooting Common Issues
  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:

Cost Savings

Cost Factor IBM DB2 HeliosDB Savings
Processor License (Enterprise) $32,500/core Open pricing 55-75%
Annual Support (SWMA) 20% of license Included 100%
pureScale/HADR Additional license Built-in 100%
Partitioning Feature $15,000/core Included 100%
Advanced Compression $12,000/core Included 100%
Database Encryption $10,000/core Included 100%

Typical 4-year TCO Reduction: 60-80%

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 implements the Distributed Relational Database Architecture (DRDA) protocol, enabling seamless connectivity from 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 Status 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 Status
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

-- 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:

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

-- 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:

-- 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:

# 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:

# 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:

[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:

// 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):

[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):

[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

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

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

-- 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:

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

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

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

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

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

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

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

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

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

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

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

-- 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:

-- 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:

# 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:

-- 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:

#!/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:

-- 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:

# 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:

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

-- 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:

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

Connection Example:

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:

# 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:

pip install ibm_db ibm_db_sa

Connection Example:

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:

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:

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

Connection Example:

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:

// 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:

npm install ibm_db

Connection Example:

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

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

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

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

# 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 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:

# 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:

# 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:

-- 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:

-- 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:

# 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:

-- 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:

-- 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:

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

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

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

-- 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 Status
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

-- 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 
    # 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

# 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 - DRDA Compatibility Reference - DRDA SQL Examples - General Migration Guide

Document History: - v1.0 (January 2026): Initial comprehensive guide