Tuesday, April 21, 2015

My 10 Minute DB2 App on Bluemix

I needed some fun at the end of the workday today. Why not enhance my own skills, do some "coding", and try out Node-RED on Bluemix again (I performed a "phoney" test in October already). This time, I wanted to see whether a database like DB2 or dashDB is supported as backend.

Node-RED Starter: This application demonstrates how to run the Node-RED open-source project within IBM Bluemix.
Node-RED Starter
Node-RED
storage nodes
To get started I logged into my free trial account on http://bluemix.net. I selected the "Node-RED Starter" from the catalog and created the app skeleton. After the app was staged (the resources allocated, files copied, and the skeleton app started), I could begin wiring up my application logic. My first look on the wiring board went to the list of supported storage nodes. What was new since my last test is support for dashDB and "sqldb" (DB2) as input and output nodes. That is, it is possible to store data in those two database systems (output) and bring data into the app by querying a database (input).

To use DB2 in Node-RED, you need to create a sqldb service in Bluemix first. This meant going back to the catalog, skipping to the Data Management section, selecting the sqldb service, and adding it to my application (see picture below). Thereafter, my application needed to be restaged (restarted), so that it could pick up the properties of the sqldb service.
Adding the sqldb/DB2 service in Bluemix


Now it was time for my coding and SQL skills (or so :). I picked an http input node, a sqldb input node, and an http output node and wired them together:
App wiring in Node-RED
For the http nodes I only changed the name to "Qtime" and "output" and defined the URL for the input ("/currenttime"). My "DB2node" required either hard-coding a query or passing them in. In order to stay within my set 10 minute limit, I just coded up a simple query as shown:

My DB2 code in Node-RED
With the wiring done I pressed the "Deploy" button on my Node-RED screen, then it was time for the QA test. I entered my application URL in the browser and the answer page was seen almost immediately:

Current time from DB2 returned
The current time obtained by querying DB2 was packed into the http response message. What is missing now is some beautification, putting some lipstick on my little pig. But else I am almost ready for starting my own business providing a time service...

BTW: Writing up this blog entry took three times as long as putting the example together and have it up and running. Can you do the same...?



Wednesday, April 15, 2015

German DB2 User Group (DeDUG), 08.05., with/mit Leo Martin (ex-BND)

(in German because of German User Group)

Das nächste Treffen der deutschen DB2 User Group (DeDUG) findet am 08. Mai bei IBM in Ehningen  statt. Auf der Agenda stehen neben interessanten DB2-Themen auch ein Blick auf dashDB, einem Data Warehousing-Service in der Cloud. Höhepunkt der Veranstaltung dürfte diesmal allerdings der Vortrag von einem besonderen Gast sein. Der studierte Kriminalwissenschaftler und ex-Geheimdienstler Leo Martin wird über "Geheimwaffen der Kommunikation" sprechen, etwas, das beruflich und privat sicherlich gut nutzbar sein wird.

Mehr Informationen und die Möglichkeit zur Anmeldung gibt es hier oder hier.

Thursday, April 2, 2015

db2audit & syslog: Who Stole my Chocolate Easter Eggs?

Security Audit
Easter is coming up, a slow day due to vacation and Spring Break, time to try out some, in advance, of the chocolate Easter eggs. Gosh! They are gone! Who took them? It must have been someone with insight. Let's take a look at the audit logs...

When the DB2 10.5 Cancun Release (Fixpack 4) was announced I mentioned that db2audit records can be transferred to syslog now and I wanted to test it. The command db2audit is used to configure parts of the DB2 audit infrastructure, to archive audit logs, and to extract information from the archived logs. The "extract" option now features a destination "syslog" (from the command syntax):

Audit Extraction

   .-file--output-file---------------------------------------------------.   
|--+---------------------------------------------------------------------+-->
   +-delasc--+---------------------------+--+-----------------+----------+   
   |         '-delimiter--load-delimiter-'  '-to--delasc-path-'          |   
   '-syslog--facility.priority--+-----------+--+-----------------------+-'   
                                '-tag--word-'  '-splitrecordsize--byte-'     

While the option "file" would store the formatted audit logs in a regular text file, choosing "delasc" would split the log data across several delimited text files, ready for postprocessing in the database. The new option "syslog" can be used to hand over the audit data to the system logger facility. Depending which logger is used and how it is set up it could mean storing the audit records in local message files or sending them over to a central hub for analysis (e.g., by IBM Operations Analytics or Splunk).


DB2 Setup
In order to find the one trying to steal the Easter eggs the audit system would need to be active prior to any attempt. The DB2 audit infrastructure is started with "db2audit start", basic settings can be changed with "db2audit configure". For my tests I left everything set to failure-only logging and changed the archive path to "/tmp". Using the "describe" option, here is how the configuration looked like:


[hloeser@mymachine ~]$ db2audit describe
DB2 AUDIT SETTINGS:

Audit active: "TRUE "
Log audit events: "FAILURE"
Log checking events: "FAILURE"
Log object maintenance events: "FAILURE"
Log security maintenance events: "FAILURE"
Log system administrator events: "FAILURE"
Log validate events: "FAILURE"
Log context events: "FAILURE"
Return SQLCA on audit error: "FALSE "
Audit Data Path: ""
Audit Archive Path: "/tmp/"


It is also a good idea to use a buffer to hold audit records. The audit_buf_sz controls its size:
db2 update dbm cfg using audit_buf_sz 40

The next step in my setup was to create an audit policy in my test database:
create audit policy execfail categories execute status failure,checking status failure, context status failure error type normal

Creating a policy does not mean it is used. The AUDIT statement takes care of it:
audit sysadm,dbadm,dataaccess,user hloeser using policy execfail

Syslog Setup
The above concludes the DB2 portion of the test setup. Next is the optional step of telling the system logger where to place the received DB2 audit data. The DB2 Knowledge Center has some basic information about how to configure the system error and event log (syslog). Without any changes it is possible to dump the audit data to, e.g., "/var/log/messages". I wanted the records go to a separate file. Because my system has rsyslog installed, I needed to edit (as root) the file "/etc/rsyslog.conf". Adding the following line causes all "user"-related records to be written to "user_messages.log" in the directory "/var/log/db2":

user.*                            /var/log/db2/user_messages.log

It is important to create that directory and file (I used "mkdir" and "touch"), then to restart the syslog facility.


DB2 Audit Logs to Syslog
Once done with the setup I connected to my test database and executed several SQL statements, including a "select * from eastereggs" (a non-existing table). Then I deemed my system ready for moving a first batch of audit records over to syslog. If a buffer for the DB2 audit data is used, it needs to be flushed:
db2audit flush

Thereafter, all the current audit logs need to be archived. This can be done for both the instance and for databases. The following archives the logs for my test database and writes the file to the configured archive path (or the default path if none is specified):
db2audit archive database hltest

After all the configuration and preparation, we are finally at the really interesting part, the new extract option. Using "syslog" as destination and the category "user" with the priority level "info", the audit logs are handed over to the system error and event logger:
db2audit extract syslog user.info from files /tmp/db2audit.*

Did the logs really make its way over from DB2 to the system infrastructure? Here is my successful test:
[hloeser@mymachine ~]$ sudo grep -i easter /var/log/db2/user_messages.log Apr  2 13:32:10 mymachine db2audit: timestamp=2015-04-02-13.31.09.089507; category=CONTEXT; audit event=PREPARE; event correlator=40; database=HLTEST; userid=hloeser; authid=HLOESER; application id=*LOCAL.hloeser.150402095529; application name=db2bp; package schema=NULLID; package name=SQLC2K26; package section=201; text=select * from eastereggs; local transaction id=0x3266020000000000; global transaction id=0x0000000000000000000000000000000000000000; instance name=hloeser; hostname=mymachine;

Happy Easter and hopefully some chocolate eggs are left for you!

LinkWithin

Related Posts with Thumbnails