Generate PowerPoint slides from your Db2 data with Python

Generated PowerPoint slide with Db2 data

How did you spend the holidays? I had some days off and then used the last day of vacation for some fun programming. Last year, I wanted to test the python-pptx package, but couldn't find time. So I used my "fun day" to generate / code up some PowerPoint slides. To make it more interesting, I added some charts from data retrieved from Db2 in my IBM Cloud account. Here is a quick recipe.

Db2 on Cloud credentials and how to connect from your app

Over the past 12 months, Db2 on Cloud migrated instances to a new deployment model. It seems to be based on the IBM Cloud Databases model and resulted in a different structure for the service credentials. Previously, Db2 on Cloud instances were accessible on the typical ports 50000 and 50001. The JSON-based credentials object included ready-to-use connection URIs. Now, after the change, the VCAP object has more parts, but is missing the easy-to-use URIs. In the following, I am showing you how to consume the new credential structure in your app and to successfully connect to Db2 on Cloud.

Getting started with Db2 on Cloud Lite

It seems to me that during the COVID-19 pandemic there are more hackathons than usual. Great to see that IBM Cloud is the platform of choice for many solutions. Db2 on Cloud is used as database, often the free Lite plan to get started. That's the reason I published a quick guide on the IBM Cloud blog on how to use Db2 on Cloud Lite plan for hackathons.

All the best combined: Cloud, Db2, Python, Serverless and Security

Add a cloud service ID as Db2 user

Earlier this year, I wrote about how to use an API key or access token to connect to Db2 (on Cloud). Today, I am going to show you how to set up a service ID (technical user) on IBM Cloud, assign it a Db2 user ID (see screenshot) and grant database privileges. Thereafter, I share Python code for connecting to Db2 using the service ID with an API key.

All this helps to reduce the set of privileges held by a user or service and hence increases cloud security.

Use an API KEY or ACCESS TOKEN to connect to Db2 on IBM Cloud

In the past, I have blogged about how to access a Db2 database on IBM Cloud from your local machine. I cataloged the cloud host and its database. Then, connected to it using the command line processor (CLP) by passing a username and password. Last year, I was delighted to read that Db2 on Cloud and Db2 Warehouse on Cloud now support authentication by either IBMid, API key, or access token. It is called IAM (Identity and Access Management) support. Finally, I had some time to actually use it. Here is what steps I needed to connect from my local machine to Db2 on IBM Cloud using either API key or token.

Automated reports with IBM Cloud Functions, Db2 and Slack

GitHub Traffic Analytics

One of my (many) favorite IBM Cloud solution tutorials is about combining serverless and Cloud Foundry for data retrieval and analytics. I blogged about it and described how an automated IBM Cloud Functions action retrieves GitHub statistics and stores them in Db2. Using an embedded Cognos dashboard and regular Javascript / HTML tables, the solution offers GitHub Traffic Analytics. I extended that solution by automatic weekly reports that are posted to Slack.

DeDUG-Treffen in Ehningen / Db2 User Group meeting near Stuttgart

Db2 User Group Meeting

Remember the last German Db2 User Group (DeDUG) meetings at IBM in Munich (April) and at Fiducia GAD in Karlsruhe (January)? Or not? Anyway, get ready for an educative meeting with great networking. The next Db2 meeting is coming up this June 29th. IBM is going to host the user group at is German headquarters in Ehningen.

This time you have the opportunity to learn more about the SQL explain facility, query optimization, SQL recursion and several other topics. Similarly to the previous event, we are going to have lightning talks again. They are short, 3-8 minute talks in which YOU can present a solution, introduce a technical problem for which you are looking for a solution, or tell us your best database-related joke or an anecdote.

You can register through several channels:

• ARS: https://web.ars.de/dedug2018_19_ehningen/
• ids-System: https://www.ids-system.de/leistung/schulungen/9-dedug/502-19-treffen-der-deutschen-db2-user-group-dedug-am-29-06-2018-in-ehningen
• Meetup: https://www.meetup.com/de-DE/DeDUG-RUG/

See you at the DeDUG meeting at IBM Germany in Ehningen. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Use Db2 and IBM Cloud to analyze GitHub traffic data (tutorial)

Architecture: GitHub Traffic Analytics

In a new solution tutorial, I show you how to automatically retrieve and store GitHub traffic data the serverless way with IBM Cloud Functions and Db2. The data can then be analyzed via a Web app deployed to Cloud Foundry on IBM Cloud. The app is secured with App ID using OpenID Connect. The new service Dynamic Dashboard Embedded provides visualization of the views and clones of GitHub repositories.

New tutorial: Db2-driven Slackbot

Ever wanted to build a Slackbot, a chatbot integrated into Slack, on your own? I am going to show you how easy it is to integrate Slack or Facebook Messenger with the IBM Watson Conversation service. As a bonus, the bot is going to access a Db2 database to store and retrieve data. The solution is based on IBM Cloud Functions and entirely serverless

Slackbot Architecture

.

Use Db2 as Cloud SQL Database with Python

Load Data into IBM Db2 on Cloud

Over the Summer I learned that Python is top in the IEEE programming languages ranking. It is also my favorite language for quickly coding tools, web apps and analyzing data with notebooks (e.g., on IBM Data Science Experience). Did you know that IBM provides four (4) different Db2 drivers for Python? There is a driver with the native Db2 API, one that supports the official Python DBI (database interface), one for the popular SQLAlchemy Python SQL Toolkit, and for the Python-based Django Web Framework. In an older blog I showed you how to use SQLAlchemy with Db2. Today, I am going to demonstrate you how simple it is to create a SQL database-backed web app in the IBM Cloud, utilizing the native Db2 API.

Combining Db2 and Cloud Object Storage

Db2 and Cloud Storage
happily combined

Since a while, Db2 has built-in support to directly access Cloud Object Storage (COS) like Amazon S3 and IBM COS (Softlayer / IBM Bluemix IaaS). It allows to perform backups (and restores :) as well as data load operations to and from the cloud. Phil Nelson wrote an excellent overview and introduction on the IDUG blog with his examples focussed on S3. Thus, in the following I am going to mostly cover Db2 using the IBM Cloud Object Storage and fill in some additional details.

Secure Your Db2 Data Base - Using Trusted Contexts

Db2 Security - Trusted Context

You have that new app, maybe even in the cloud, with that user needing read and write access to the database? Don't want to open the floodgates for that favorite users? Ok, here is a way to secure your Db2 data and still provide that access. Even if that sounds impossible, trust me. And, I am going to put that "trust me" in context. Read on.

IBM Marketing and DB2, Db2 and dashDB

IBM Cloud with Bluemix: DB2, Db2, dashDB

This week I am going to acknowledge that I have been with IBM for 16 years. Looking back, everything might seem brighter and better than it was. However, I remember working in great teams, interesting and challenging projects, many successes and some failures to learn from, and the constant changes. During the time with IBM I got used to those many changes to product names, the constant rebranding. When I first heard about a change to DB2 that was announced last week, I scratched my head and moved on. The product itself does not change, it's a name.

Here are some of the changes, see the Db2 website for details:

• DB2 for Linux, UNIX, and Windows (DB2 LUW) is now names just "Db2".
• DB2 for z/OS is "Db2 for z/OS".
• dashDB TX (dashDB for Transactions) is referred to as "Db2 on Cloud". It reflects that it basically was and is a fully managed Db2 (LUW) database system.
• The former "IBM DB2 on Cloud", the cloud-hosted version of DB2 LUW, is now named "Db2 Hosted" to correctly imply what it is.

To keep you mentally flexible and to stimulate your brain cells, the uppercase B in "DB2" is now lowercase. "Db2" is what you will see more and more on the outside. But the product itself, as stated above, will remain "DB2". Therefore, you don't have to rewrite your test procedures. And if you have to prepare slides, e.g. for one of the upcoming IDUG conferences, there is one benefit with the new naming: Autocorrect is correct now...

A great Summer and a sane week!

DB2 Security: Privilege(d) Insight

DB2 Security

Today's blog entry is about an old topic: Who I am, why I am and what I am (allowed) to do? Users and their privileges are at the core of database security. Information about them are stored in the IBM DB2 catalog (or IBM ashDB catalog) which can be queried. Thus, it is good to know your way around and being able to extract that information. Kind of as a reference for myself, here is a summary.

I already covered parts of this topic in the past:

• In 2012, I gave an introduction about where to find granted privileges in the catalog. It covers the catalog structure and links to key tables.
• In 2011, 2013 and recently, I discussed trusted contexts, surrogates and the session user as special form of granting privileges and switching identities. I started with a short explaination of trusted context. Next, this introduction covers the concept of surrogates and switching the userid via SET SESSIONUSER. As a follow-up, I looked into related catalog entries for trusted contexts and surrogates which are related to the task scheduler.
• Last year, I wrote about interesting administrative views and and table functions to list privileges. As an example I looked at implicit privileges through group membership.

What is important to know is how to extract the information from the catalog:

• The DB2 Knowledge Center provides a list of security-related views and routines.
• The same resource also has a discussion about gaining access to data through indirect means. It lists the catalog tables, but then many more options on how possibly data and related privileges can be accessed.

IBM Bluemix in Germany, includes dashDB and Cloudant

IBM Bluemix in Germany, in German

Today, I wanted to share some exciting news with you. Most of you know that I am German. Thus, it is terrific to have IBM Bluemix available from Frankfurt, Germany, today. As can be seen on the screenshot on the right, the new Bluemix region is labeled "eu-de".

Having Bluemix Public in Germany is a big step for the IBM Cloud and customers alike. Being located next to DE-CIX means low network latency for German and European customers. Utilizing Bluemix Public in London ("eu-gb") it is possible to deploy applications with high-availability requirements redundantly within Europe. The database-as-a-service offerings dashDB ("DB2") and Cloudant are already available in the Bluemix catalog. More database and analytics services are to follow. You can check out the list of initial services here in the Bluemix Catalog for Germany.

That's all for today. I am back to MY German Bluemix...

Securing Workloads on IBM Cloud - Some Resources

Security Guides for IBM Cloud

Recently, I provided you with an overview of security and compliance resouces for IBM dashDB and Cloudant. Today, I want to take a broader view and point you to some good introductory material on security for cloud-based workloads. It consists of an overview of different cloud deployment models and their components. Then it digs into each of those categories and takes a look at how to secure those components and the data.

Carnival: Even DB2 Wears a Mask (Database Security)

Data Privacy and Security

Right now we are in the hot phase of the carnival season. Many people are wearing masks. Some move into other characters, some just hide their real identity. Did you know that DB2 is also in the mood for carnival and wears a mask? Here is what my DB2 is doing these days...

Security and Compliance for IBM dashDB and Cloudant

Database Security & Compliance

I often get asked about the security features of IBM dashDB and Cloudant. Both are database services ("DBaaS") offered on IBM Bluemix. Once the security topic is dealt with, compliance-related questions are next. A good chunk of questions can be answered by going over the provided product documentation. Here are the links to get you started on database security and compliance.

New Mod Pack and Fix Pack for DB2 V11 available now

New DB2 Fix Pack and Mod Pack available

Over the past years I blogged about new fixpacks and features in DB2. This is the first time I have to mention "Mod Packs" because for DB2 V11 a new Mod Pack and Fix Pack was released. DB2 changed to distinguish between fixes to existing features and adding new features in interim releases. The terminology of version, release, modification and fix pack is explained in this support document.

If you are one of the administrators or developers impacted by system and code freezes over the last weeks of the year, then the good news is that you can use the time to explore some great enhancements to DB2. Check out the summary page in the DB2 Knowledge Center for an overview. Here are my favorites:

• DB2 supports PKCS #11 keystores now, i.e., Hardware Security Modules (HSMs) can now be used with DB2 extending the choice to local keystores, centrally managed keystores and hardware modules.
• Lots of improvements to DB2 BLU. If you are on Linux OS on IBM z Systems ("z/Linux") then you will be happy about DB2 BLU performance improvements on z13. There are improvements for other processors, too.
• The Workload Manager (WLM) and related monitoring have been enhanced, giving deeper insight and more control of long running complex queries. There are also new features related to CPU management.

Sounds interesting? The updated version of DB2 can be downloaded from the usual support page offering all the fix packs (and now Mod Packs) for the recent versions of DB2.

SQL Magic in Notebooks in the IBM Data Science Experience

New Notebook in IBM Data Science Experience

At the recent IDUG DB2 Tech Conference in Brussels I gave a talk on using Jupyter Notebooks with IBM DB2 or dashDB. For the presentation I used a local installation of the notebooks and DB2 (never trust Internet connectivity). Part of the talk was about using SQL Magic in a notebook as simple interface to the database, e.g., for testing and prototyping.
After the conference I received a question about whether it is possible to use the SQL Magic with Jupyter Notebooks in the IBM Data Science Experience. The answer is yes and here is how.