Loading Data into IBM Db2

In this episode, we’ll load structured data into your IBM Db2 database using various methods including CLI, scripts, and GUI tools.

1. Prerequisites

• You have a running Db2 database from Episode 1 (e.g., mydb)
• You are connected as user db2inst1
• Sample CSV file available (e.g., customers.csv)

2. Option A: Import CSV Using db2 IMPORT

Sample CSV (customers.csv)

id,name,email
1,Alice,alice@example.com
2,Bob,bob@example.com
...

Step-by-Step

1. Create Table in Db2

1
2
3
4
5
6
7
db2 connect to mydb

db2 "CREATE TABLE customers (
  id INT,
  name VARCHAR(100),
  email VARCHAR(100)
)"

1. Run Import Command

1
db2 "IMPORT FROM customers.csv OF DEL MODIFIED BY COLDEL, METHOD P (1,2,3) INSERT INTO customers"

• OF DEL: file is delimited
• COLDEL: use comma , as delimiter
• METHOD P: specify column positions

1. Verify Data:

1
db2 "SELECT * FROM customers"

3. Option B: Load Data with db2 LOAD (Faster for Large Datasets)

1
db2 "LOAD FROM customers.csv OF DEL MODIFIED BY COLDEL, METHOD P (1,2,3) INSERT INTO customers"

• LOAD is faster than IMPORT but locks the table during execution.
• Recommended for large files.

4. Option C: Run SQL Script

• Example SQL File: insert_data.sql

  1
  2
  INSERT INTO customers (id, name, email) VALUES (3, 'Charlie', 'charlie@example.com');
  INSERT INTO customers (id, name, email) VALUES (4, 'Diana', 'diana@example.com');
  

• Execute Script:

  1
  db2 -tvf insert_data.sql
  

• -t: end statements with semicolon
• -v: verbose mode
• -f: read from file

5. Option D: Use IBM Data Studio (GUI Tool)

1. Connect to mydb using Data Studio
2. Use “Data > Load Data” wizard
3. Map CSV columns to table columns
4. Preview and load

Great for non-technical users or verifying schema visually.

6. Option E: Load Data Using Python (Optional)

Install driver and library:

1
pip install ibm-db ibm-db-sa

Sample script:

1
2
3
4
5
6
import ibm_db

conn = ibm_db.connect("DATABASE=mydb;HOSTNAME=localhost;PORT=50000;UID=db2inst1;PWD=Passw0rd", "", "")
sql = "INSERT INTO customers (id, name, email) VALUES (?, ?, ?)"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt, (5, 'Eve', 'eve@example.com'))

7. Validate the Data

1
2
3
db2 connect to mydb
db2 "SELECT COUNT(*) FROM customers"
db2 "SELECT * FROM customers FETCH FIRST 10 ROWS ONLY"

8. Common Issues & Fixes

Summary