Wednesday, July 23, 2014

Watch this! Move your DB2 monitoring to the in-memory interface (WLM monitoring)

Since its first days as a database management system, DB2 has been been changed. It has been extended by new features to serve customer requirements and has been adapted to the state of the art in hardware and software technology. One major new feature has been the introduction of the DB2 Workload Management in version 9.5 and related more comprehensive monitoring with finer granularity (in-memory metrics monitoring) in version 9.7. As with many product changes, it takes a while for customers to really put them to use and reap the benefits, especially when the existing functionality still works.

Thus I was happy when I saw a new article on IBM developerWorks describing how to move off the (old) snapshot monitoring interfaces in DB2 and to the in-memory metrics monitoring. What is included in the article is an overview of the advantages of the newer interface. This should get you motivated to read the rest of the article (and then to migrate if not done yet). It contains a side-by-side comparison of old and new interfaces and has many sample SQL queries. The queries demonstrate how to obtain DB2 runtime metrics using the old and new interface for some popular monitoring tasks. You can find the documentation of the SQL interface to the in-memory metrics in the DB2 Knowledge Center in this overview. Most of the pages in the manual have further SQL samples to get you started.

So take a look, it will also help you with one of the upcoming DB2 quizzes on this blog.

Tuesday, July 8, 2014

DB2 Quiz: Processes and CPU

In today's DB2 quiz the focus is on DB2 processes and CPU consumption. Which SQL statement did I run in DB2 for the following output? What function is used?

NAME                                CPU_USER             CPU_SYSTEM         
----------------------------------- -------------------- --------------------
db2fmp (                                              13                    4
db2fmp (                                               9                   10
db2vend (PD Vendor Process - 1) 0                      5                    2
db2ckpwd 0                                             0                   13
db2ckpwd 0                                             0                   13
db2ckpwd 0                                             0                    9
db2sysc 0                                          18561                30282
db2wdog 0 [hloeser]                                    6                   44
db2acd 0                                            6997                11550

  9 record(s) selected.

A statement similar to the one I used can be found in the DB2 Knowledge Center. The statement makes use of a special table function.

Wednesday, July 2, 2014

Nice Cloud, no rain: Using Cloudant/couchDB with Python on Bluemix

My last two blog entries were about getting started with Python on IBM Bluemix and how to use a custom domain with my Bluemix weather application. Today I am going to show how I added Cloudant and couchDB to my application, both locally and on Bluemix.

Storing the weather data locally doesn't make sense because I can query much more historical data on OpenWeatherMap. So I am going to use a database to log information about for which city and when the data was requested. That information, in aggregated form, could then be reported as fun fact to each user of the app. I chose Cloudant because it is simple to use, adequate for the intended purpose, has free usage plans on Bluemix, and I can use it and test locally as couchDB.

Add Cloudant as new service
The code itself is relatively simple and I put comments (shown at the end of the article). The interesting part is how to add a Cloudant service to my application on Bluemix, how to bind them in the application, and the preparation work for the database itself. So let's take a look at those steps.

Cloudant is offered as one of several services in the "Data Management" category on Bluemix. While on the Dashboard you simply click on the "Add a service" button as show on the right. Navigate to the Data Management section and choose Cloudant.

It will bring up a screen showing information about the service itself, on usage terms, and on the right side of it a dialog "Add Service" for adding the service to your account. Here you can already bind the new database service to your application by selecting an existing application from a dropdown list. I did that and gave my new Cloudant service the name "cloudantWeather" as shown:
Bind Cloudant to
your application
Once the service is added you can bring up the Cloudant administration interface. I have used Cloudant and couchDB before, so that isn't anything new. To avoid dealing with creation of a database as part of the actual program I decided to create a "weather" database through the administration interface for the hosted Cloudant and my local couchDB servers. An interesting but not too tricky part is how to access both servers depending on where the application is running. Information with the username, password, server address and other details is provided in an environment variable VCAP_SERVICES when run on Bluemix. Thus, in the program I am testing for the presence of that variable and then either retrieve the server information from it or access my local couchDB:

#get service information if on Bluemix  
 if 'VCAP_SERVICES' in os.environ:  
   couchInfo = json.loads(os.environ['VCAP_SERVICES'])['cloudantNoSQLDB'][0]  
   couchServer = couchInfo["credentials"]["url"]  
   couch = couchdb.Server(couchServer)  
 #we are local  
   couchServer = ""  
   couch = couchdb.Server(couchServer) 

Storing new documents is simple and is shown in the full code listing. For the queries I am using the MapReduce feature of couchDB. In a "map" function I return the city name (and just the integer value 1), in the reduce function I am aggregating (summing up) the values by city. Both functions could be defined in the Python script and then passed into Cloudant as part of the query or predefined for more performance. I chose the latter one. So I created a so-called "secondary index" in my Cloudant database, it is called "view" in my couchDB. They are stored as part of a "design document" (shown is Cloudant):

Secondary index / permanent view

With that I finish my Python application, add some calls to the couchDB Python API (which I needed to add to the file "requirements.txt" as dependency) and test it locally. The final step is to deploy the application to Bluemix using the Cloud Foundry tool "cf push". Done, seems to work:
Bluemix weather app with Cloudant stats

Last but not least, here is the code I used for my little app:

 import os  
 from flask import Flask,redirect  
 import urllib  
 import datetime  
 import json  
 import couchdb  
 BASE_URL = ""  
 BASE_URL_fc =""  
 app = Flask(__name__)  
 # couchDB/Cloudant-related global variables  
 #get service information if on Bluemix  
 if 'VCAP_SERVICES' in os.environ:  
   couchInfo = json.loads(os.environ['VCAP_SERVICES'])['cloudantNoSQLDB'][0]  
   couchServer = couchInfo["credentials"]["url"]  
   couch = couchdb.Server(couchServer)  
 #we are local  
   couchServer = ""  
   couch = couchdb.Server(couchServer)  
 # access the database which was created separately  
 db = couch['weather']  
 def index():  
   return redirect('/weather/Friedrichshafen')  
 def weather(city):  
   # log city into couchDB/Cloudant  
   # basic doc structure  
   doc= { "type" : "city",  
     "c_by" : "bm",  
   # we store the city and the current timestamp  
   # and store the document (doc)  
   # Time to grab the weather data and to create the resulting Web page  
   # build URIs and query current weather data and forecast  
   # JSON data needs to be converted  
   url = "%s/%s" % (BASE_URL, city)  
   wdata = json.load(urllib.urlopen(url))  
   url_fc = "%s/%s" % (BASE_URL_fc, city)  
   wdata_fc = json.load(urllib.urlopen(url_fc))  
   # build up result page  
   page='<title>current weather for '+wdata["name"]+'</title>'  
   page +='<h1>Current weather for '+wdata["name"]+' ('+wdata["sys"]["country"]+')</h1>'  
   page += '<br/>Min Temp. '+str(wdata["main"]["temp_min"]-273.15)  
   page += '<br/>Max Temp. '+str(wdata["main"]["temp_max"]-273.15)  
   page += '<br/>Current Temp. '+str(wdata["main"]["temp"]-273.15)+'<br/>'  
   page += '<br/>Weather: '+wdata["weather"][0]["description"]+'<br/>'  
   page += '<br/><br/>'  
   page += '<h2>Forecast</h2>'  
   page += 'Temperatures'  
   page += '<br/>Min: '+str(wdata_fc["list"][0]["temp"]["min"]-273.15)  
   page += '<br/>Max: '+str(wdata_fc["list"][0]["temp"]["max"]-273.15)  
   page += '<br/>Morning: '+str(wdata_fc["list"][0]["temp"]["morn"]-273.15)  
   page += '<br/>Evening: '+str(wdata_fc["list"][0]["temp"]["eve"]-273.15)  
   page += '<br/><br/>Weather: '+wdata_fc["list"][0]["weather"][0]["description"]  
   page += '<br/><br/>'  
   # Gather information from database about which city was requested how many times  
   page += '<h3>Requests so far</h3>'  
   # We use an already created view  
   for row in db.view('weatherQueries/cityCount',group=True):  
    page += row.key+': '+str(row.value)+'<br/>'  
   # finish the page structure and return it  
   page += '<br/><br/>Data by <a href="">OpenWeatherMap</a>'  
   return page  
 port = os.getenv('VCAP_APP_PORT', '5000')  
 if __name__ == "__main__":'', port=int(port))  


Related Posts with Thumbnails