Tuesday, August 5, 2014

Using DB2 with Python on Cloud Foundry-Based IBM Bluemix

Wow, what a title for this article. Today I will describe exactly that, nothing more. Recently I created a SQLDB service (DB2) in my IBM Bluemix account. Looking at the documentation for the SQLDB service, it describes how to use DB2 for programming languages like Java, Ruby, and Node.js and how to bind the SQLDB service to Liberty instances. There is no word on Python which I wanted to use like in my previous tests. I got it to work nonetheless and here is what I did.

SQLDB / DB2 service on IBM Bluemix
Adding DB2 as a service to a Bluemix application is quite simple. Just click on "add service" and then pick "SQLDB". It is possible to bind it to an application if present. To access DB2 from Python IBM provides a library named "ibm_db". When testing with the buildpack I described in a previous article for my weather app, I ran into an error because installation of ibm_db requires the DB2 drivers accessible locally. Reading over the SQLDB documentation for Ruby I found that a special buildpack for DB2 is referenced. Digging into it I discovered that a similar builpack for Python and DB2 is provided on Github, but not really referenced. I succeeded in pushing my app to Bluemix using that special DB2/Python buildpack, the ibm_db library could be download and installed:
That library needs to be added as dependency to the file "requirements.txt". Cloud Foundry buildpacks for Python then try to download and install it to the runtime environment.

cf push welcomedb2 -b https://github.com/ibmdb/db2pythonbuildpack

I continued my test in trying to connect to my DB2 service from my Python script. I used similar code as in my CouchDB test to obtain the user/server credentials from the Bluemix runtime environment. To demonstrate successful access to DB2 the script utilizes the administrative function ENV_SYS_INFO to obtain information about the DB2 server environment, e.g., which operating system is used and what CPU and memory resources are available. That information is filled into a simple webpage as shown here:
DB2 server environment on IBM Bluemix

Last, here is the entire test script to access DB2 from Python in the IBM Bluemix environment:
 import os  
 from flask import Flask,redirect  
 import urllib  
 import datetime  
 import json  
 import ibm_db  
 app = Flask(__name__)  
 # get service information if on Bluemix  
 if 'VCAP_SERVICES' in os.environ:  
   db2info = json.loads(os.environ['VCAP_SERVICES'])['sqldb'][0]  
   db2cred = db2info["credentials"]  
 # connect to DB2  
 db2conn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","")  
 # main page to dump some environment information  
 def index():  
   page = '<title>Welcome DB2!</title>'  
   page += '<h1>Welcome DB2!</h1>'  
   if db2conn:  
    # we have a DB2 connection, so obtain system information via ENV_SYS_INFO:  
    stmt = ibm_db.exec_immediate(db2conn,"select host_name,os_name,os_version,total_cpus,configured_cpus, total_memory,os_kernel_version,os_arch_type, os_release,os_full_version from sysibmadm.env_sys_info")  
    # fetch the result  
    result = ibm_db.fetch_assoc(stmt)  
    page += "OS Name: "+result["OS_NAME"]+"<br/>OS Version: "+result["OS_VERSION"]   
    page += "<br/>Hostname: "+result["HOST_NAME"]+"<br/> Total CPUs: "+str(result["TOTAL_CPUS"])  
    page += "<br/>Configured CPUs: "+str(result["CONFIGURED_CPUS"])+"<br/>Total memory: "+str(result["TOTAL_MEMORY"])+" MB"  
    page += "<br/>OS Kernel Version: "+result["OS_KERNEL_VERSION"]+"<br/>OS Architecture Tpye: "+result["OS_ARCH_TYPE"]  
    page += "<br/>OS Release: "+result["OS_RELEASE"]+"<br/>OS full version: "+result["OS_FULL_VERSION"]  
   return page  
 port = os.getenv('VCAP_APP_PORT', '5000')  
 if __name__ == "__main__":  
      app.run(host='', port=int(port))