Set Up a REST API Server for IBM Db2

In this episode, you’ll build a REST API using Flask to connect to IBM Db2. You’ll expose two endpoints: one to retrieve all customers, and another to insert new customers into the database.

1. Prerequisites

2. Install Dependencies

1

pip install flask ibm-db

Optionally, use python-dotenv to manage DB credentials securely.

3. Create Your API Server

app.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

from flask import Flask, request, jsonify
import ibm_db

app = Flask(__name__)

# Db2 connection string
conn_str = (
    "DATABASE=mydb;"
    "HOSTNAME=localhost;"
    "PORT=50000;"
    "PROTOCOL=TCPIP;"
    "UID=db2inst1;"
    "PWD=Passw0rd;"
)

# Connect to Db2
try:
    conn = ibm_db.connect(conn_str, "", "")
except:
    raise Exception("Could not connect to Db2")

# GET: Retrieve all customers
@app.route('/customers', methods=['GET'])
def get_customers():
    customers = []
    sql = "SELECT id, name, email FROM customers"
    stmt = ibm_db.exec_immediate(conn, sql)
    row = ibm_db.fetch_assoc(stmt)
    while row:
        customers.append(row)
        row = ibm_db.fetch_assoc(stmt)
    return jsonify(customers)

## POST: Add a new customer
@app.route('/customers', methods=['POST'])
def add_customer():
    data = request.get_json()
    sql = "INSERT INTO customers (id, name, email) VALUES (?, ?, ?)"
    stmt = ibm_db.prepare(conn, sql)
    try:
        ibm_db.execute(stmt, (data['id'], data['name'], data['email']))
        return jsonify({"message": "Customer added successfully"}), 201
    except:
        return jsonify({"error": "Insert failed"}), 500

if __name__ == '__main__':
    app.run(debug=True)

4. Run the Flask Server

1

python app.py

Server will be live at: http://127.0.0.1:5000

5. Test the API

{ “id”: 2, “name”: “Bob”, “email”: “bob@example.com” } ```

6. Common Issues

Issue Fix
Connection fails Ensure Db2 is running, correct port & credentials
CORS errors Use flask-cors if calling from browser
Table not found Run SET SCHEMA or qualify table name

Summary

Step Description
1. Install Flask and ibm-db
2. Connect to Db2 database
3. Create GET and POST endpoints
4. Test with Swagger, Postman, or curl