IBM Cloud: Some fun with Python and Cloud Foundry Logging

IBM Cloud: Turn Log Data into Donut

Last month, after receiving user questions, I blogged about how to decipher Cloud Foundry log entries. Today, I want to point you to a small Cloud Foundry Python app I wrote. It helps to better understand Python and Cloud Foundry logging. You can also use it to test the IBM Cloud Log Analysis service which provides an easy-to-use interface to logs generated by applications running in the IBM Cloud. In the premium plans, external log events can also be fed into the service for consolidated storage and analysis.

As usual, the code for my app is available on Github: https://github.com/data-henrik/application-log-analysis/. Once deployed to IBM Cloud, the app can be used to send messages on a chosen log level back to the server. The server-side log level, i.e., the threshold for processed log messages can also be set. The app produces diagnostic output on "stdout" and "stderr". The two are treated differently by Cloud Foundry. Here is a screenshot of the logging app:

Test app for Cloud Foundry logging

The produced log entries can also be used to try out the IBM Cloud Log Analysis service. Diagnostic logs are automatically forwarded to the Log Search of that service. The messages are fed into Elasticsearch and can be analyzed using Kibana. I wrote some search queries (one shown below) and then built visualizations like the shown "Donut" based on those queries. I will write more about that in a future blog post.

Search Query for Elasticsearch / IBM Cloud Log Analysis

An official tutorial using that app and Log Analysis is available in the IBM Cloud docs.

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

DB2 Battle: Optimization Profile vs. Statement Concentrator (Part 1)

DB2 explain output

Today I wanted to try using a DB2 Optimization Profile for a statement impacted by the DB2 Statement Concentrator. It turned out to be a longer exercise than thought, but also with more fun, covering a lot of the DB2 tuning and monitoring infrastructure. So set some minutes aside and follow my journey into the world of query optimization, workload monitoring, session actuals, and more. I split it into two parts because of length (Update on 02-Jul-2015: A third part is here).

The original question I tried to answer was related to REOPT and a query like this:
select id, s
from betw
where id between ? and ?

Epilepsy, computer viruses, and personality changes

Did you ever encounter a computer virus on your machine and were thinking that it is no more "your computer"? What I mean is that the PC behaves in different, not known before patterns. It may slow down, react differently during your standard use and may seem weird in its behavior. Your computer encountered "personality changes".

As I wrote a couple of times, my son/the family has been dealing with epilepsy caused by a brain tumor. Fighting the cause for the seizures and damming them is one thing, coping with the changes to that person is another. A big impact on family and social life may have the personality changes caused by epilepsy. When your kid gradually slows down speaking, when he becomes more aggressive over time, when your child over time has more and more trouble remembering things, you know that you will have new challenges added to the core issues, the seizures.

For a computer virus there is usually a cure by cleaning the computer through different ways. For epilepsy damming the seizures through drugs or trying to remove the cause through surgery are first steps. Thereafter, dealing with the personality changes is a long road ahead. In the case of my son, we are hoping for the best. Next on the list will be to break free, not from Oracle :), but from medication...

Playing around with db2diag

Over the past few days I have been playing with db2diag. It is the log analysis tool and it allows you to search, filter, and format the DB2 diagnostic logs. One of the simple examples is that I wanted to see when errors or warnings were produced on my test system. I could do that by calling:

db2diag -l Error,Warning -fmt "%ts %level %db"

This would only print the timestamp, the "error" level, and, if it was for a specific database, the database name.

2010-02-05-13.05.51.947000 Warning

2010-02-05-13.05.52.449000 Error PUREDB

2010-02-05-13.05.52.449000 Error PUREDB

2010-02-05-14.10.24.619000 Warning

The output is much quicker to digest than the diagnostic itself. And I could also use the output as input for some other scripts. Have you used db2diag in the past?

Separating diagnostic logs in DB2 9.7 (to split or not to split)

One of the new or enhanced features in the DB2 9.7 FP1 is the option to store the diagnostic logs in separate files for a physical host, a database partition, or both (DPF environments). The idea is that by separating the diagnostic logs the possible contention is reduced and hence the diagnostic logging performance increases. In addition, the storage administration might become simpler.

To do so you have to change the database manager (dbm) configuration and use the "$h" (host) and/or "$n" (database partition number/node) variables inside the diagpath (diagnostic data directory path) setting. An example to set the diagpath to "home/mydb2/db2dump" and split according to the physical host would be:

db2 update dbm cfg using diagpath '"/home/mydb2/db2dump/ $h"'

BTW: You can later merge the diagnostic log files using the db2diag tool.

Hey, what's wrong with you?!?

Sometimes, you have to deal with stuff that is just wrong. Sometimes, it's not easy to even find out what's going on. And sometimes, there are small things that make a big difference...

In an earlier post I had described how an index over XML data can be used to clean up data. Today, I will focus on a neat feature that was added to DB2 9.5 Fix Pack 3 as well as to DB2 9.7. It is a stored procedure (SP) named XSR_GET_PARSING_DIAGNOSTICS. That stored procedure helps you to find out details on XML parsing and validation errors.

The SP has five input and two output parameter. The first is a BLOB with the XML document/fragment in question. The next two make up the XML schema's fully qualified name (schema.name) against which you want to validate or they can be NULL. The 4th parameter can be NULL again or should be the schemaLocation of the primary XML schema document. The last input parameter specifies whether to use schemaLocation information from inside the provided XML document (1) or not (0). If zero was specified and no schema name has been specified (parameters 2 and 3), then no validation is performed. This is exactly done (no validation) in our example:

call xsr_get_parsing_diagnostics(blob('<a>Hello<b>Another<c><c></a>'),NULL,NULL,NULL,0,?,?)

The last two parameters are the output parameters. The first is a placeholder for an output document (as VARCHAR), the 2nd the number of errors found in the XML document. The above call would produce an output like this:

  Value of output parameters
  --------------------------
  Parameter Name  : ERRORDIALOG
  Parameter Value : <ErrorLog>
  <XML_FatalError parser="XML4C">
    <errCode>202</errCode>
    <errDomain>http://apache.org/xml/messages/XMLErrors</errDomain>
    <errText>Expected end of tag 'c'</errText>
    <lineNum>1</lineNum>
    <colNum>27</colNum>
    <location>/a/b/c/c</location>
    <schemaType></schemaType>
    <tokenCount>1</tokenCount>
    <token1>c</token1>
  </XML_FatalError>
  <DB2_Error>
    <sqlstate>2200M</sqlstate>
    <sqlcode>-16129</sqlcode>
    <errText>
[IBM][CLI Driver][DB2/NT] SQL16129N  XML document expected end of tag "c".  SQLSTATE=2200M
    </errText>
  </DB2_Error>
</ErrorLog>


  Parameter Name  : ERRORCOUNT
  Parameter Value : 1

  Return Status = 0

The function is not just useful to deal with parsing or validation errors coming from your application data, but also if you have XML documents that are causing trouble in general.

Diagnosis: Simple(r) Life

DB2 produces diagnostic information which is stored in "db2diag.log" files. Depending on the setting of diaglevel there is more or less information in those log files. Many DBAs have automated tasks to back up and compress and maybe eventually remove those files because they grow over time. On one hand, these files cause some (little) work, on the other hand, they are really handy if something goes wrong. The more information is logged, the faster they grow.
Now let's take a look at one of the many small, but useful new features in DB2 9.7: It's an instance configuration parameter named diagsize. It can be used to configure a max size for the diagnostic information and causes the diagnostic log to become a rotating log. If there is no need to keep old diagnostic information and with a limit set, DB2 will automatically remove the oldest log and recycle the space for the next/newest diagnostic log file.

With the improvement in place, my diagnosis is: Simpler life.

Passive House, Electric Cars, Noise, and DB2 diagnostics

It's a little bit over a year now that we live in our passive house. Thanks to 48cm thick walls and 4 pane windows it is mostly quiet inside the house, even with the flight path to/from FDH being very close.
What we are not missing from the US is the sound of Harleys, sometimes large groups of them. A lot of noise, you can hear and feel them approaching.

Putting that into perspective, I read about US lawmakers considering adding a requirement for non-visual alerts for motor vehicles (Pedestrian Safety Enhancement Act of 2009), i.e., to make so far quiet electric and hybrid cars louder. Worldwide everybody else seems to work towards making things quieter (reducing noise emissions). So this looks strange.

What I love about DB2 is that the many autonomic features help forgetting that a database is running. The diagnostic file has information about how DB2 was/is doing. Using diaglevel I can set the "noise level" I prefer, the default (3) is to log all errors and warnings. If you set it to the highest level (most noise), it gives you all kind of informational messages. Did you know that you can analyze the diagnostic messages using the tool db2diag? It was introduced in DB2 a couple years ago. If you haven't done so, try it out and look at the "noise"...