⚡ DB2 → Spark SQL Modernization Engine

A hands-on learning project that accepts IBM DB2 SQL statements and generates equivalent Apache Spark SQL and PySpark DataFrame code — complete with query analysis, complexity scoring, data lineage, and optimization recommendations.

---

🎯 What This Project Teaches

Concept	What You Learn
DB2 Architecture	Tables, indexes, packages, plans, tablespaces, buffer pools
SQL Parsing	How text becomes an AST (Abstract Syntax Tree)
Dialect Translation	Why DB2 SQL differs from Spark SQL and how to convert
PySpark DataFrame API	How SQL maps to transformation chains
Query Optimization	Broadcast joins, partitioning, predicate pushdown
Data Lineage	Tracking data flow from source to output
Migration Patterns	How organizations move DB2 workloads to Spark

---

🏗️ Project Structure

db2-spark-modernizer/
├── src/
│   ├── ast/                    # AST node definitions
│   │   └── nodes.py            # SelectNode, TableNode, JoinNode, etc.
│   ├── parser/                 # SQL Parser
│   │   └── sql_parser.py       # DB2SQLParser (sqlglot-backed)
│   ├── analyzer/               # Query Analysis
│   │   ├── query_analyzer.py   # Metadata extraction
│   │   └── complexity_analyzer.py  # 1-10 complexity scoring
│   ├── spark_sql_generator/    # Spark SQL Generation
│   │   └── generator.py        # sqlglot dialect transpilation
│   ├── dataframe_generator/    # PySpark Code Generation
│   │   └── generator.py        # DataFrame API code synthesis
│   ├── lineage/                # Data Lineage
│   │   └── lineage_engine.py   # DAG + Mermaid diagram
│   ├── optimization/           # Spark Optimization
│   │   └── optimizer.py        # Recommendations engine
│   └── docs_generator/         # Migration Documentation
│       └── generator.py        # Auto-generated migration docs
├── ui/
│   └── app.py                  # Streamlit application
├── examples/
│   └── scenarios.py            # 12 real-world migration scenarios
├── tests/
│   ├── test_parser.py          # 30 parser tests
│   ├── test_analyzer.py        # 22 analyzer tests
│   └── test_spark_generator.py # 39 generator + lineage tests
├── docs/
│   └── mapping_catalog.md      # DB2 → Spark concept mapping
├── requirements.txt
└── README.md

---

🚀 Quick Start

1. Install Dependencies

pip install -r requirements.txt

requirements.txt:

sqlglot>=25.0.0
streamlit>=1.35.0
graphviz>=0.20
pytest>=8.0.0
pandas>=2.0.0

2. Run the Streamlit UI

streamlit run ui/app.py

Open http://localhost:8501 in your browser.

3. Run Tests

pytest tests/ -v
# 91 tests, all passing

4. Use the Python API Directly

from src.parser.sql_parser import parse_db2_sql
from src.analyzer.query_analyzer import analyze_query
from src.analyzer.complexity_analyzer import analyze_complexity
from src.spark_sql_generator.generator import generate_spark_sql
from src.dataframe_generator.generator import generate_pyspark_code

db2_sql = """
SELECT   C.CUSTOMER_ID,
         C.CUSTOMER_NAME,
         SUM(T.AMOUNT) AS TOTAL_AMOUNT
FROM     BANKDB.CUSTOMER    C
         INNER JOIN BANKDB.TRANSACTION T ON C.CUSTOMER_ID = T.CUSTOMER_ID
WHERE    C.STATUS = 'ACTIVE'
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME
HAVING   SUM(T.AMOUNT) > 10000
ORDER BY TOTAL_AMOUNT DESC
FETCH FIRST 100 ROWS ONLY
"""

# Parse
result     = parse_db2_sql(db2_sql)

# Analyze
metadata   = analyze_query(result)
complexity = analyze_complexity(result, metadata)

# Generate
spark_sql  = generate_spark_sql(db2_sql)
pyspark    = generate_pyspark_code(result)

print(spark_sql.spark_sql)
print(pyspark.code)
print(f"Complexity: {complexity.score}/10 ({complexity.level})")

---

🖥️ Streamlit UI

The UI has 8 tabs for each migration artifact:

Tab	Content
🔍 Parsed AST	Visual tree of query nodes
📋 Metadata	Tables, columns, filters, features
⚡ Spark SQL	Side-by-side DB2 vs Spark SQL
🐍 PySpark Code	DataFrame transformation chain
📊 Complexity	Score (1-10), factors, challenges
🔗 Lineage	Data flow graph + Mermaid diagram
🚀 Optimizations	Ranked recommendations
📄 Migration Doc	Auto-generated migration document

Sidebar features:

• Load 12 pre-built industry examples (Banking, Insurance, Retail, Telecom, Healthcare)
• Toggle AST JSON view
• Toggle DB2 concept explanations
• Download migration documents (.md)

---

📚 Supported DB2 SQL Features

Feature	Status	Notes
SELECT columns, aliases	✅	Including AS aliases
SELECT *	✅
SELECT DISTINCT	✅
FROM with schema qualification	✅	SCHEMA.TABLE
Table aliases	✅
INNER JOIN	✅
LEFT JOIN	✅
RIGHT JOIN	✅
FULL OUTER JOIN	✅
CROSS JOIN	✅
WHERE with AND/OR	✅
IN, BETWEEN, LIKE	✅
GROUP BY	✅
HAVING	✅
ORDER BY ASC/DESC	✅
COUNT, SUM, AVG, MIN, MAX	✅
UNION / UNION ALL	✅
FETCH FIRST n ROWS ONLY	✅	→ LIMIT n
CURRENT DATE / CURRENT TIMESTAMP	✅	DB2 special registers
WITH UR/CS/RS/RR isolation hints	✅	Removed with warning
Subqueries	⚠️	Basic support
CASE expressions	⚠️	Parsed, basic codegen
Window functions (OVER)	⚠️	Detected, flagged
Stored procedures	❌	Documentation only

---

🏭 Industry Scenarios

12 pre-built examples across 5 industries:

🏦 Banking

• bank_001 — Customer Balance Lookup (Simple)
• bank_002 — Daily Transaction Summary (Medium)
• bank_003 — Customer Account Portfolio Analysis (Medium)
• bank_004 — Risk Portfolio Summary (Complex)
• bank_005 — UNION ALL Account Report (Medium)

🏥 Insurance

• ins_001 — Active Policy Report by Agent (Medium)
• ins_002 — Claims Loss Ratio Analysis (Complex)

🛒 Retail

• ret_001 — Product Sales Analysis (Medium)
• ret_002 — Low Inventory Alert (Medium)

📡 Telecom

• tel_001 — Subscriber Usage for Billing (Medium)

🏥 Healthcare

• hc_001 — Patient Billing Summary (Complex)

🔮 COBOL

• cobol_001 — Embedded SQL Extraction Example

---

🧠 Learning Concepts

Phase 1: DB2 Architecture

┌──────────────────────────────────────────┐
│             z/OS Mainframe               │
│  ┌──────────────┐  ┌─────────────────┐  │
│  │  CICS/IMS    │  │  DB2 Subsystem  │  │
│  │  (Online TP) │─▶│  ┌───────────┐  │  │
│  └──────────────┘  │  │ Optimizer │  │  │
│  ┌──────────────┐  │  │ Buffer    │  │  │
│  │  Batch JCL   │─▶│  │ Pools     │  │  │
│  └──────────────┘  │  └───────────┘  │  │
│                    │  Tablespaces     │  │
│                    └─────────────────┘  │
└──────────────────────────────────────────┘
             ⬇ Migration
┌──────────────────────────────────────────┐
│          Apache Spark / Delta Lake        │
│  Driver ─ Catalyst Optimizer             │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐   │
│  │Exec 1│ │Exec 2│ │Exec 3│ │Exec 4│   │
│  └──────┘ └──────┘ └──────┘ └──────┘   │
│         Delta Lake (cloud storage)        │
└──────────────────────────────────────────┘

Phase 2: How the Parser Works

DB2 SQL Text
    ↓ Lexer (sqlglot)
Tokens: [SELECT, CUSTOMER_ID, FROM, CUSTOMER, WHERE, ...]
    ↓ Parser
sqlglot AST (internal representation)
    ↓ Our converter
Educational AST nodes:
    SelectNode
    ├── TableNode(CUSTOMER)
    ├── ColumnNode(CUSTOMER_ID)
    ├── WhereNode(BALANCE > 1000)
    └── OrderByNode(CUSTOMER_ID ASC)

Phase 3: DB2 → Spark Concept Mapping

DB2 Concept	Spark Equivalent
Table	DataFrame / Delta Lake Table
View	Temp View / DataFrame
Index	Partitioning + Z-ORDER
Tablespace	Database / Schema
Buffer Pool	Spark Memory Cache
Package/Plan	Catalyst Physical Plan
RUNSTATS	ANALYZE TABLE (Delta)
Stored Procedure	Spark UDF / Python Function
Cursor	DataFrame iterator / collect()
COMMIT/ROLLBACK	Delta Lake ACID Transactions
FETCH FIRST n ROWS	LIMIT n
JCL Batch Job	Spark Job / Databricks Job

Phase 4-5: SQL Translation Examples

Simple SELECT:

-- DB2
SELECT CUSTOMER_ID, NAME, BALANCE
FROM   BANKDB.CUSTOMER
WHERE  STATUS = 'ACTIVE'
ORDER BY NAME

-- Spark SQL (identical — ANSI SQL)
SELECT CUSTOMER_ID, NAME, BALANCE
FROM   BANKDB.CUSTOMER
WHERE  STATUS = 'ACTIVE'
ORDER BY NAME

-- PySpark DataFrame API
customer_df = spark.table("BANKDB.CUSTOMER")
customer_df = customer_df.filter(col("STATUS") == "ACTIVE")
customer_df = customer_df.select("CUSTOMER_ID", "NAME", "BALANCE")
customer_df = customer_df.orderBy(col("NAME").asc())

DB2-Specific Syntax:

-- DB2: FETCH FIRST
SELECT * FROM CUSTOMER FETCH FIRST 100 ROWS ONLY

-- Spark SQL
SELECT * FROM CUSTOMER LIMIT 100

-- PySpark
customer_df.limit(100)

-- DB2: CURRENT DATE (special register)
WHERE TXN_DATE = CURRENT DATE

-- Spark SQL
WHERE TXN_DATE = CURRENT_DATE()

-- PySpark
.filter(col("TXN_DATE") == current_date())

Phase 7: Complexity Scoring

Score	Level	Migration Effort	Auto-Migration
1–3	🟢 Simple	Hours	85–95%
4–5	🟡 Medium	Days	65–85%
6–7	🟠 Complex	Weeks	40–65%
8–10	🔴 Critical	Months	<40%

Phase 8: Spark Optimization Patterns

# 1. BROADCAST JOIN (for small dimension tables)
from pyspark.sql.functions import broadcast
result = large_fact.join(broadcast(small_dim), "key")

# 2. PARTITION PUSHDOWN (for large tables)
spark.conf.set("spark.sql.adaptive.enabled", "true")
df = spark.table("fact_table").filter(col("region") == "NORTH")

# 3. AGGREGATE ONCE (all functions in one .agg() call)
result = df.groupBy("region").agg(
    sum("amount").alias("total"),
    count("id").alias("cnt"),
    avg("balance").alias("avg_bal")
)

# 4. SEMI-JOIN (replace IN subquery)
# DB2: WHERE id IN (SELECT id FROM vip_table)
# Spark:
result = df.join(vip_df.select("id"), "id", "left_semi")

---

🧪 Running Tests

# All tests
pytest tests/ -v

# With coverage
pytest tests/ -v --cov=src --cov-report=term-missing

# Specific test file
pytest tests/test_parser.py -v
pytest tests/test_analyzer.py -v
pytest tests/test_spark_generator.py -v

Test coverage:

• test_parser.py — 30 tests: SELECT, WHERE, JOIN, UNION, GROUP BY, HAVING, ORDER BY, preprocessing
• test_analyzer.py — 22 tests: metadata extraction, complexity scoring
• test_spark_generator.py — 39 tests: Spark SQL gen, PySpark gen, lineage engine

---

🗺️ Learning Roadmap

MVP (Complete ✅)

• DB2 SQL Parser (sqlglot-backed)
• Educational AST nodes
• Query metadata extraction
• Complexity scoring (1-10)
• Spark SQL generation (dialect transpilation)
• PySpark DataFrame code generation
• Data lineage graph + Mermaid diagrams
• Spark optimization recommendations
• Migration documentation generator
• Streamlit UI (8 tabs)
• 12 industry scenarios
• 91 pytest unit tests

Stretch Goals (Next Steps)

• Subquery support (full correlated subqueries)
• Common Table Expressions (CTEs / WITH clause)
• Window functions (ROW_NUMBER, RANK, LEAD, LAG)
• Stored procedure analysis
• COBOL embedded SQL extractor
• Delta Lake DDL generation
• Query performance estimator
• AI-generated migration explanations (Claude API)
• SQL dialect comparison dashboard
• Batch migration: analyze a folder of SQL files

---

🏢 Real-World Context

In large-scale mainframe modernization projects:

1. IBM DataStage / AWS DMS — extract DB2 data to cloud storage
2. Tools like this — analyze and translate SQL workloads
3. Apache Spark / Databricks — run translated workloads at scale
4. Delta Lake — replace DB2 tables with ACID-compliant lake tables
5. Apache Atlas / OpenLineage — enterprise lineage tracking

This project teaches the analytical and translation phase that sits between extraction and execution.

---

🛠️ Tech Stack

Component	Technology
SQL Parsing	sqlglot
UI	Streamlit
Diagrams	Mermaid (text-based)
Testing	pytest
Data manipulation	pandas
Language	Python 3.9+

---

Built as a learning-focused educational tool for DB2 developers moving to Apache Spark and modern data lake architectures.