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.


SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME
FROM DEPARTMENT
     START WITH DEPTNO = 'A00'
     CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT

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 (
    SELECT 1, CAST( DEPTNAME AS VARCHAR(40)) AS DEPTNAME, deptno
    FROM department 
    WHERE DEPTNO = 'A00'
    UNION ALL
    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.

Slots

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.

LinkWithin

Related Posts with Thumbnails