Friday, August 17, 2018

Db2: Some Friday Fun with XML and SQL recursion

Recursion is fun!?
Right now, Michael Tiefenbacher  and I have to prepare our joint talk "Some iterations over recursion" for the IDUG Db2 Tech Conference in Malta 2018. The title of our talk promises some fun, coding up some of the sample SQL already is. Yesterday and this morning I tested a little query I wrote and brought down my machine (not Db2). In April, I wrote about two different kind of recursive queries, standard SQL and Oracle syntax. Today, let me give you insights to a nasty recursive query that features SQL, SQL/XML and XQuery.

Wednesday, August 15, 2018

IBM Cloud and Db2 News - August 2018 Edition

Catch up with news
This Summer I have been away few days here and there. Once back, I tried to catch up with changes for IBM Cloud and its services as well as with Db2. Here are links to what I considered noteworthy (and read so far...):  
That's it for now. Anything that you found interesting and should be included? Then leave a comment.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Monday, July 23, 2018

Secure apps on IBM Cloud Kubernetes Service with Let's Encrypt wildcard certificates

In my recent post I explained how I enable SSL for Cloud Foundry apps on IBM Cloud with my own custom domain. Today, I focus on securing apps running in Docker containers in the Kubernetes service on IBM Cloud.The good news is that I only had to follow what is written in the documentation and in my previous blog post. Here are the details.

Wednesday, July 18, 2018

Now on GitHub: Understand and build chatbots the easy way

Recently, I posted about a then upcoming Meetup and my talk about chatbots. Here is a quick follow-up. To compile stuff for that presentation and some other upcoming talks, I created a GitHub repository "chatbot-talk2018". I has lots of links to get started and to deepen understanding around chatbot technology. Moreover, it contains a presentation in Markdown for GitPitch for you to use and extend. And finally, I wrote this brief introduction to some chatbot terms or concepts:
  • Intents are what the user aims for, the desired action or result of the interaction. An intent can be to retrieve a weather report.
  • Entities are (real or virtual) subjects or objects. For the example of the weather report, entities can be the city or country, e.g., Friedrichshafen in Germany, or date and time information such as "today afternoon".
  • A dialog, dialog flow or dialog tree is used to structure the interaction. Typically, an interaction lasts longer than the user providing input and the chatbot returning a single answer. A dialog can be highly complex with several levels, subbranches, (directed) links between dialog nodes and more.
    For a weather chatbot, a dialog could be constructed that, after a greeting, asks the user about the location and time for a weather report, then asks if additional information, such as a weather outlook for the next few days, is needed.
  • Slots are supported by several chatbot systems. Slots are used to specify the data items that need to be specified in order to produce the result of an intent. To return a weather report, e.g., at least the location and maybe the date or time is needed.
  • Context is state information that is carried from step to step for a specific user interaction. The context typically stores the information that is already gathered as input (see "slot"), result-related data or metadata, or general chat information, e.g., the user name.
If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Monday, July 16, 2018

Extended: Manage and interact with Watson Assistant from the command line

Remember my blog posts about how to manage Watson Assistant from the command line and how to test context for a conversation? Well, that tool did not work well for server actions which I used in this tutorial on building database-driven Slackbot. The good news is that I found time to extend my command line Watson Conversation Tool to support credentials for IBM Cloud Functions.

With the recent update to the tool there are two new features:
  1. Use the option "-outputonly" with the "-dialog" option to only print the output text, not the entire JSON response object. I introduced it to be able to demo dialog flows from the command line. Not everybody needs all the metadata for every dialog turn. Here is how it looks like when in action:
  2. Chatbot dialog on the command line
  3. In order to test dialog server actions, I need to provide the credentials for IBM Cloud Functions (ICF) in a private context variable. I recently blogged about how to enable the Watson botkit middleware for those server actions. For my tool, just provide the ICF key token as part of the configuration file. A sample is part of the GitHub repository.
For feature requests, defects or suggestions, open an issue or a pull request against the repository. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Friday, July 13, 2018

How to: Enable Let's Encrypt SSL wildcard certificates for your IBM Cloud apps

Private data - secured
Early last year, I wrote about how to improve security for your custom domains on IBM Cloud. I pointed out that I was using the bluemix-letsencrypt wrapper for Cloud Foundry app to generate and retrieve SSL certificates issued by Let's Encrypt. Today, I am going to introduce you to another option which I am using. Recently, Let's Encrypt announced the support and availability of wildcard certificates. With such a wildcard certificate, all of my IBM Cloud apps available under a custom domain are covered (and secured). So let's take a look at what I did.

Tuesday, July 3, 2018

Learn about chatbots at upcoming IBM Cloud Meetup

Learn how to chat at the Meetup
Want to quickly and easily build a chatbot, integrate it with Slack, Facebook Messenger or other platforms? Connect the bot with a database? Join me at the IBM Cloud Developers in Stuttgart Meetup on July 17th for an introduction, hands-on session and discussion. And there are drinks, too (I have been promised).

If you followed my blog and the chatbot-related posts, you probably already know what to expect. First, I am going to introduce you to chat / conversation services and the standard terms. Then, we jointly will take a look at the IBM Cloud solution tutorials and the chatbot-related resources. I plan to finish with a demo showing how to build a chatbot from scratch in few minutes and to integrate it into Slack. If you are close to Stuttgart, join me on July 17th for the chatbot session.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Tuesday, June 19, 2018

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:

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.

Monday, June 18, 2018

Use BotKit Middleware to create Watson-powered database interface

Chatbot anyone?
Few months back, I introduced you to a tutorial for a database-drive Slackbot. In the tutorial I used the Conversation connector to hook up IBM Watson Assistant to Slack as a chatbot. Did you know that you can reuse the code and utilize BotKit and IBM Watson BotKit Middleware to implement a Db2 interface via Slack, Facebook Messenger, Cisco Webex Teams and others?

Wednesday, June 13, 2018

Securing your Python app with OpenID Connect (OIDC)

Authorized access only using OpenID Connect
Some weeks back I introduced to a tutorial on how to analyse GitHub traffic. The tutorial combines serverless technology and Cloud Foundry to automatically retrieve statistics and store them in Db2. The data can then be accessed and analyzed using a Python Flask app. Today, I going to show you how the web site is protected using OpenID Connect and IBM Cloud App ID.

Tuesday, June 12, 2018

June 2018 Edition: Latest IBM Cloud news and links (Db2 included)

IBM Cloud news
It's been a while since my last news compilation. Here is a collection of news interesting links related to IBM Cloud from the past few weeks:

With the above you should alreay have enough reading material. But here is another link featuring a cool application built on IBM Cloud: Listen live to Kone elevators around the world.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Wednesday, June 6, 2018

Tutorial: Analyze and visualize open data with Apache Spark

Life Expectancy Map
Many government agencies and public administrations offer access to data, contributing to open data. Using IBM Watson Studio with Jupyter Notebooks and Apache Spark it is simple to retrieve, combine and analyze data from different sources. The result can be easily visualized. Learn what it takes with this IBM Cloud solution tutorial.

Thursday, May 24, 2018

How to pack serverless Python actions

Serverless access to Db2 and GitHub
For my tutorial on automated data retrieval and analytics, I use IBM Cloud Functions to automatically fetch GitHub traffic statistics once a day. It is implemented as a serverless Python action. Because some Python packages are needed, the question was how to pack and create the action. In this blog, I share some of my experiences.

Friday, April 27, 2018

Db2: CTE and CONNECT BY - two kinds of recursion

Writing recursive SQL (based on M.C.Escher by Henrik Loeser)
Writing recursive SQL
Recently, I gave a talk on SQL Recursion. One of the cool features in Db2 that I (re-)discovered for that presentation is that there are two different forms of syntax for recursive queries. One is the Common Table Expression (CTE), another the CONNECT BY syntax. Let me introduce you to the two and show you a sample query in both styles.

If you had to write recursive queries, e.g., to create a bill of material or to find dependencies in data, you would typically use a SELECT statement with a common table expression (CTE). CTEs are easily spotted by starting with the keyword WITH. Some form of temporary table is defined, then the initial data (seed) is specified followed by UNION ALL and the SELECT for the recursive part. A regular fullselect is then applied to the result of that computation.

The CONNECT BY provides another syntax for hiearachical and recursive queries. It was first introduced by Oracle and made its way into Db2 as part of the SQL compatibility features. It allows to write a, more or less, regular SELECT statement followed by a START WITH clause to define the initial data (see) and a CONNECT BY clause to specify the recursion. There are some additional keywords to access data from the prior step, the root ancestor or the (string) path from the root to a node. It is quite powerful and often shorter than a CTE. The only drawback is that you have to enable it in the Db2 compatibility vector.

The following two queries return the same result. It is the organizational structure of the departments in the SAMPLE database. The first query uses CONNECT BY and is directly taken out of the Db2 documentation.


The second query is my version of the above with a CTE. As you can see, it is longer. Readability depends on what you are used to.

WITH tdep(level, deptname, deptno) as (
    FROM department 
    WHERE DEPTNO = 'A00'
    SELECT t.LEVEL+1, CAST(SPACE(t.LEVEL  * 4) || '/' || d.DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME, d.deptno
    FROM DEPARTMENT d, tdep t
    WHERE d.admrdept=t.deptno and d.deptno<>'A00')
SELECT level, deptname
FROM tdep 

If you only knew one style of recursive query then it is time to try out the other. Both queries above work with the SAMPLE database. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Tuesday, April 24, 2018

Automated, regular database jobs with IBM Cloud Functions (and Db2)

IBM Cloud Functions and Db2
Yesterday, I blogged about the latest tutorial I wrote. The tutorial discusses how to combine serverless and Cloud Foundry for data retrieval and analytics. That scenario came up when I looked into regularly downloading GitHub traffic statistics for improved usage insights. What I needed was a mechanism to execute small Python script on a daily or weekly basis. After looking into some possible solutions, IBM Cloud Functions was the clear winner. In this blog, I am going to discuss how simple it is to implement some regular, automated activities, such as maintenance jobs for a cloud database.

Monday, April 23, 2018

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.

Friday, April 13, 2018

IBM Cloud and Watson security questionnaires

If you are reading my blog regularly, you are aware of the entries on cloud security. Today, I want to point you to some newer reading material at the Cloud Security Alliance. They now have up-to-date self-assessment security questionnaires for the IBM Cloud.

The three available security documents cover IBM Cloud Infrastructure (IaaS), IBM Cloud Platform (PaaS), and the IBM Watson Services with their cognitive / AI capabilities. Using the provided material, you can learn about application security, audits, compliance, how business continuity and operational resilience are handled, procedures for change control, data and data center security, IAM (identify and access management) and much more. The answers include many links to references, documentation and other valuable information. Just for that it is worth going over the security questionnaires for IBM Cloud.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Monday, March 19, 2018

DeDUG-Treffen in München / German Db2 User Group Meeting in Munich

It's already two months since the last Db2 User Group meeting, time to point you to the next one. The German Db2 user group, DeDUG (Deutsche Db2 User Group) is going to meet next month, April 20th, in Munich. Henrik Loeser has all the details.
DeDUG: Db2 User Group

If you clicked the previous link, you should have gotten back to this blog entry. At the meeting, I am going to present on SQL recursion. Certainly a fun topic because you can solve Sodukos with SQL, compute catalog (metadata) dependencies, or generate data and deeply nested SQL statements for testing software and system limits.

How can you sign up for this event? Here are a couple options:

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

Tuesday, February 27, 2018

Security Details: Serverless database access within IBM Watson Conversation service

Slackbot Architecture
Last week, I introduced you to a new tutorial for a database-driven Slackbot. Today, I am going to discuss details of how the IBM Watson Conversation service is accessing a Db2 Warehouse service from within a dialog. It uses a serverless setup with IBM Cloud Functions. All the necessary credentials to execute the code and to access the Db2 database are automatically bound. Hence, the function code and the dialog don't need any account-specific changes and are generic.

Monday, February 19, 2018

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

Thursday, February 15, 2018

Easy Database Setup the Serverless Way

Serverless Slackbot with Db2
A tutorial I wrote, featuring a database-backed Slack chatbot, is now live. It uses Db2 as database system to store event data. The client accessing the database is written in Node.js and is implement with IBM Cloud Functions in a serverless way. During the development of that tutorial I faced the question on how to perform the database setup. Should I guide users through the user interface to create a table and insert data? Should they install a Db2 client and execute a script locally? I solved the problem in a serverless fashion. Here are the details.

Tuesday, February 6, 2018

Chatbots: Some tricks with slots in IBM Watson Conversation

As you might remember, I have been using the IBM Watson Conversation service and DB2. My goal was to write a database-driven Slackbot, a Slack app that serves as chat interface to data stored in Db2. I will write more about that entire Slackbot soon, but today I wanted to share some chatbot tricks I learned. How to gather input data, perform checks and clean up the processing environment.


With my chatbot interface to Db2 I want to both query the database and insert new records. Thus, I need to collect input data of various kind. The Conversation service has a neat feature named input slots that simplifies that process. Within a dialog node (a logical step within the chat flow) I can specify a list of items the Conversation service should check for. I can tell in which variable to save that input and what question to ask if that data was not provided yet. Optional slots, i.e., optional data, can be enabled.

Thursday, January 25, 2018

Access Db2 from IBM Cloud Functions the Easy Way (Node.js)

Db2 access via IBM Cloud Functions
Recently, I have been experimenting with the IBM Watson Conversation service and Db2. With a new feature in the conversation service it is possible to perform programmatic calls from a dialog node. Why not query Db2? I implemented both a Db2 SELECT and INSERT statement wrapped into actions of IBM Cloud Functions. It is quite easy and here is what you need to know.

The conversation service supports client-side and service-side calls. This means, either the application driving the chat can be instructed to make an outside call or the conversation service itself is invoking an action. That is, IBM Cloud Function actions can be called. For my experiment I coded up two actions, one to fetch data from a Db2 database, the other to insert new data. I chose Node.js 8 runtime platform because the Db2 driver for Node.js is already part of the runtime environment. The sources for the Db2-related actions are in this Github repository.

Passing the right values to the functions shouldn't be a problem for you. Obtaining the credentials for Db2 and making them available inside the action got simplified recently. The CLI plugin for IBM Cloud Functions allows to bind a service to an action as show here:

bx wsk service bind dashDB /hloeser/ConferenceFetch --instance henrikDB2 --keyname henriksKey2

In the example I am binding credentials for the dashDB service (that is Db2 Warehouse on Cloud) to one of my actions named "ConferenceFetch". Because I have multiple service instances and possibly multiple keys (credentials) I make use of the optional parameters "instance" and "keyname". Thereafter, the Db2 configuration including username and password is available in the action metadata. In the action code I am using this syntax to obtain the "dsn" information. The dsn is used by the Db2 driver to connect to the database.

__bx_creds: {dashDB:{dsn}}

With this rough outline you should be able to get your IBM Cloud Functions connected to Db2. Those actions can then directly be called from within a dialog node of Watson Conversation. I will post details on how that works in another blog entry.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

Monday, January 8, 2018

DeDUG-Treffen in Karlsruhe bei Fiducia GAD (Db2 User Group Meeting)

DeDUG - Deutsche Db2 User Group
Db2 User Group
Happy New Year! I hope you had a great start into 2018. How about meeting in person soon? I am going to speak next week at the German Db2 User Group (DeDUG) meeting in Karlsruhe. It will be hosted by Fiducia GAD IT AG. You can find out about the details and register for the Db2 user group event here. Topics include database security using trusted contexts, the Db2 catalog and metadata, lots of SQL performance tips and tricks, and how Db2 is used in banking infrastructure.

See you in Karlsruhe!

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.


Related Posts with Thumbnails