Set Up a REST API Server for IBM Db2

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

• Python 3.8+
• IBM Db2 is running (e.g., from Docker or Cloud)
• Table customers exists in database mydb

---

2. Install Dependencies

pip install flask ibm-db

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

3. Create Your API Server

app.py

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

python app.py

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

5. Test the API

• GET Customers

  GET /customers
  

  Response:

  [
  { "id": 1, "name": "Alice", "email": "alice@example.com" }
  ]
  

• POST Customer ```http POST /customers Content-Type: application/json

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