Loading Data into IBM Db2

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

db2 connect to mydb

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

1. Run Import Command

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:

db2 "SELECT * FROM customers"

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

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

  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:

  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:

pip install ibm-db ibm-db-sa

Sample script:

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

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

8. Common Issues & Fixes

Issue	Fix
SQL3021N The table is in use	Use IMPORT instead of LOAD
Encoding errors	Add MODIFIED BY CODEPAGE=1208 (UTF-8)
Permissions denied	Ensure file readable by db2inst1
Table not found	Use correct schema or run SET SCHEMA

Summary

Step	Description
1.	Prepare table and CSV or SQL data
2.	Choose between IMPORT, LOAD, or GUI
3.	Run command or script
4.	Validate inserted data
5.	Handle errors and confirm integrity