Wednesday, December 17, 2014

How to use SYSSTATFEEDBACK to drive your RUNSTATS

DB2 v11 introduced a new catalog table SYSIBM.SYSSTATFEEDBACK, which contains information about missing or conflicting catalog statistics for SQL statements [more info].

Some users are very cautious about collection of statistics, some others would rather prefer this process to be automatic.
If you are part of the second group, this new feature is for you! However, the process is not fully automatic: DB2 will only fill the SYSSTATFEEDBACK catalog table with suggestions, when he/she thinks that executing RUNSTATS may be beneficial. During the last IDUG, when asked if there is any plan to “close the loop”, IBMers answered that there is no such plan. In other words, we should not expect this process to be automated by DB2 itself.

A related note in the DB2 11 for z/OS Technical Overview

Tooling available

In Version 18.0, CA Database Analyzer™ for DB2 for z/OS has been enhanced with additional support for DB2 11 Optimizer recommended RUNSTATS statistics. This tool now provides by default an extract object condition (EOC), that evaluates the SYSSTATFEEDBACK table and currently selects all objects (at the partition level) with a REASON value of BASIC, KEYCARD, or CONFLICT. The sample Real Time Object Selection RTOS$S$C is provided for this purpose:

The sample RTOS$S$C is provided,
under creator INSTALL.

The sample RTOS$S$C extracts objects 
from the SYSSTATFEEDBACK table.

Create an Action Procedure (to execute RUNSTATS) 
tied to RTOS$S$C

CA Database Analyzer generated the RUNSTATS commands 
based on SYSSTATFEEDBACK recommendations.

A couple of useful notes

  • The column STATS_FEEDBACK (in SYSIBM.SYSTABLES catalog table) controls whether the data is collected for a particular table (so you can exclude some specific tables from this process).
  • When the recommended statistics are collected, the RUSNTATS utility removes data from the SYSIBM.SYSSTATFEEDBACK catalog table.

Wednesday, December 10, 2014

DB2 v12 – expected timeframe & features

Cypress is the code name for the next DB2 for z/OS release after DB2 v11. Granting no hurricanes, or similar branding catastrophe, one can expect the official name will be DB2 v12 (when Generally Available) – hence the title of this article. A year and a half ago, I published a post DB2 v11 – expected timeframe & features which is, by far, the most visited article on this blog. My “predictions” were quite accurate, so I decided to re-iterate, providing you with an expected DB2 v12 timeframe (based on past releases dates), and a summary of the DB2 v12 “likely” features (based on information presented during various DB2 User Groups - including IDUG).

Although IBM does not provide an exact schedule for their new versions of DB2, one can guess that DB2 v12 will be available in 2016, probably in Fall as usual. Indeed, looking at a the release schedule of DB2 over the last 15 years, you will notice that IBM releases consistently a new version of DB2 for z/OS every 3 years, and that the support of each version lasted 8 years – with the exception of DB2 version 8, which had an additional year of support. Worth mentioning as well, it’s no secret that the IBM development labs transitioned several years ago to an Agile development methodology. This method which tends to fix schedule (date of delivery) over amount of features delivered, and tends to make new version delivery cycles very predictable.

Here a graphical representation of the past DB2 for z/OS releases, with my best guess of dates for DB2 v12 (Cypress) :

What features can we expect?

I will start with a word of caution: The following statements regarding IBM plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion. In other words, the below information is “just” a summary of information collected either on the web, or during public session in various User Groups.

One first thing to note is that IBM will not provide any skip-migration from DB2v10 to DB2v12. That means you need to be on DB2v11 in order to upgrade to DB2v12. Based on comments from IBMers, the skip-migration from DB2v8 to DBv10 was an exception, and we should not expect any skip-migration process in the future.

DB2 Cypress: Early Thoughts

Based on 2013 IBM presentation (Jeff Josten) at BeLux User Group
  • Large Table Management Improvements
  • INSERT Performance and space management Improvements
  • Improved query performance and management, cope better with poorly written SQL
  • HW / SW integration and system autonomics
  • Easier application porting, SQL improvements
  • Expanded analytics capabilities

IBM Utilities themes for the future

Based on IDUG 2014 IBM presentation
  • Elimination of application impact from utilities
  • Elapsed time & CPU consumption reduction
  • Resource consumption reduction
  • Reduction in complexity & automation improvements
  • Solutions through DB2, Utilities & Tools

APPLCOMPAT – Application Compatibility

IBMers did mention (during IDUG) that the APPLCOMPAT feature (available in DB2 v11) should continue to be available in next versions of DB2. In a nutshell, IBM is updating DB2 SQL to be more in line with SQL standards, and this may introduce problems for existing applications. APPLCOMPAT is here to help!

You may remember a change in the DB2 DML SQL introduced in DB2 v10 that changed the results of the CHAR’s behavior when used to cast a decimal to a character or varchar (that is, any result of SQL using CAST (decimal AS CHAR) or CAST (decimal AS VARCHAR) may be different). IBM soon introduced a DSNZPARM keyword BIF_COMPATIBILITY to let DB2 SQL behave the “good old” way and provided IFCID 366 to help monitor and locate the potential programs impacted (so application can be corrected).

So, simply said, the APPLCOMPAT feature is an extension of BIF_COMPATIBILITY : APPLCOMPAT lets you run a new version of DB2, with the SQL DML behavior of a previous version. IFCIDs are also provided to help locate and correct existing programs. It was mentioned however, that, although this feature allows to upgrade DB2 with serenity (postponing required application changes to a later date), application changes have to be made, because IBM does not plan to provide application compatibility forever. In other words, with migrating to DB2 v12, we can expect APPLCOMPAT(V11R1) to work, but maybe not APPLCOMPAT(V10R1) any longer…

Friday, November 28, 2014

Summary of IDUG EMEA 2014 Prague

IDUG hosted its annual
2014 EMEA conference in Prague a couple of weeks ago. The event was a real success:
·         Highest attendance since 2008
·         A significant amount of first-time attendees

The IDUG EMEA CPC Committee

Top 5 sessions (in my point of view):
  • Let’s talk recovery (Florence Dubois)
  • Understanding Query Transformation in DB2 for z/OS (Daniel Luksetich)
  • Hadoop and DB2 11 just got engaged! How long before the wedding? (Emil Kotrc)
  • Lost without a trace (Denis Tronin)
  • How to save REAL money with SQL Tuning (Phil Grainger)

Denis Tronin presenting DB2 Performance and IFCIDS

The only thing which did not work well was the Free Exams Certifications, many attendees were not able to benefit from this offer during the event, due to slow and/or malfunctioning internet connection in the certification room. In compensation, attendees were offered vouchers, to attempt the certifications at a later date.

The next IDUG EMEA conference will be held in Dublin on 15-20 November 2015. IBM Champion Steve Thomas is the chairman for this event.
Interested to attend? Act now! The call for presentation for next IDUG EMEA is opened! (if you are selected as a presenter, the conference fees are for free).

IBM recognized a handful of individuals and user groups for their DB2 activities and leadership, during the Mainframe50 celebration dinner: csDUG was rewarded as the new EMEA DB2 User Group!

csDUG awarded new EMEA DB2 User Group

Saturday, November 8, 2014

2nd csDUG Conference: A great success!

50 attendees from Czech Republic, Slovakia, and some other European countries attended the 2nd csDUG Conference earlier this week, in Prague. This one-day conference was held in the IBM lab in Prague, and sponsored by CA Technologies. Various famous international speakers made the trip to share their DB2 expertise and give presentations about DB2 for z/OS.

We also had the pleasure to see some representatives of other European DB2 User Groups (PDUG, SpDUG, BeLux, …)

Steen Rasmussen giving a presentation at csDUG

A big thank you to the presenters, who provided great DB2 technical content:

Steve Thomas giving a presentation at csDUG

The agenda of the conference is available [here].

A Mainframe ?

Thursday, November 6, 2014

How to find Java application that is using my DB2

The amount of DB2 workload that is coming from Java applications is growing. Java applications can run on z/OS but more often they run outside of z/OS. In this post we will cover how you can investigate DB2 threads that are processing SQL statements issued by a Java application.

We will cover what is possible to do with standard DB2 commands and we will cover best practices for using JDBC that will make easier for database administrators to quickly identify the application that is responsible for the request. In future posts we will cover techniques for doing it without need to change the application code and how to get full information that can help developer to debug the code that is issuing SQL statements with poor performance. It can be a difficult task for complex applications and applications that are developed by different company.

Let's have a simple Java application that connects to DB2. This application just issues a simple query that can take long time. You can increase the sleep value to make it run longer. It issues the query and it reads its result set slowly. If you want to try it, you have to replace userid, password and URL with values that are good for your system. Then you just need to compile it and run with the DB2 JDBC driver on your Java class path.


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class SimpleDbApp {
private final String USERID = "qadba01";
private final String PASSWORD = "password";
private final String URL = "jdbc:db2://system:port/LOCATION";
private final String QUERY = "SELECT * FROM SYSIBM.SYSTABLES";

public static void main(String[] args) throws InstantiationException,
IllegalAccessException, ClassNotFoundException, SQLException,
                        InterruptedException {
SimpleDbApp app = new SimpleDbApp();;

private void run() throws InstantiationException, IllegalAccessException,
ClassNotFoundException, SQLException, InterruptedException {
Properties properties = new Properties();
properties.put("user", USERID);
properties.put("password", PASSWORD);
Connection connection = DriverManager.getConnection(URL, properties);
CallableStatement statement = connection.prepareCall(QUERY);
final boolean rowsReturned = statement.execute();
if (rowsReturned) {
int count = 0;
while (statement.getResultSet().next()) {
System.out.printf("Count: %d", count);

This application can run for a while. Let's see what we can see in DB2 about it.

We can issue -DISPLAY THREAD (*) DETAIL command to see if the thread for this statement is active:
DSNV401I  !D10A DISPLAY THREAD REPORT FOLLOWS -                   
DSNV402I  !D10A ACTIVE THREADS -                                  
NAME     ST A   REQ ID           AUTHID   PLAN     ASID TOKEN     
SERVER   RA *  2032 db2jcc_appli QADBA01  DISTSERV 02F2 98403     
 V437-WORKSTATION=, USERID=qadba01,                 
      APPLICATION NAME=db2jcc_application                         
 V445-O2C85068.DE86.CE04632EDB52=98403 ACCESSING DATA FOR         
  (  1)::FFFF:                                      
 V447--INDEX SESSID           A ST TIME                           
 V448--(  1) 5166:56966       W R2 1431014492448                  

I was able to find it because it was the only thread for userid QADBA01. In case when there are hundreds of threads it can be difficult to find it. Default application name for all Java applications is db2jcc_application and if the application does not set you will not get anything better. There are some information that can help you:
  • Authid - if your application is using single userid to access DB2 and no other applications are using it, you can find out what application it is if you keep track of it. In case when every user of the application is also connecting to DB2 under its userid, you cannot use it to identify the application.
  • Workstation - there is an IP address of the application server. This can be used to find the application but it is not easy for database administrator to find out what application is running on that server and there can be multiple applications on one server.
  • SQL text - you can figure out the application from the tables that are used. That usually works for one statement but you cannot effectively aggregate performance metrics based on SQL text.
This is not issue of -DISPLAY THREAD command. Even advanced performance monitoring tools like CA SYSVIEW for DB2 or CA Chorus for DB2 Database Management does not display more than just better formatted identification section:

  End User User ID    : qadba01             
  End User Workstation:      
  End User Transaction: db2jcc_application  

Active Threads in the Investigator of CA Chorus
So let's see what can be done by the Java application to identify itself better to DB2.

We will start with standard JDBC properties and then we will cover DB2 specific properties. These properties can be set by Connection.setClientInfo(name, value) method.

These three properties are recommended to be used by every Java application that is connecting to a database:
  • ApplicationName - The name of the application that is connected. DB2 will user first 32 characters.
  • ClientUser - The name of the user that the application is performing work for. This should be the real end user ID and not the user ID that was used to establish the connection.
  • ClientHostname - The hostname of the computer the application using the connection is running on. Hostname is better than just IP address. When the application is running on multiple servers it is good to provide different hostnames for each server so it is possible to identify the real server.
Let's see how we can set it in our sample program:
    connection.setClientInfo("ApplicationName", System.getProperty(""));
    connection.setClientInfo("ClientUser",      System.getProperty(""));
    connection.setClientInfo("ClientHostname",  InetAddress.getLocalHost().getHostName());

Note: This is simple example that does not handle exceptions how it should.

The output for -DISPLAY THREAD has improved:

SERVER   RA *  2077 db2jcc_appli QADBA01  DISTSERV 02F2 99078
 V437-WORKSTATION=plape03mac739, USERID=plape03,             
 V445-O2C85068.E276.CE04710F0A04=99078 ACCESSING DATA FOR    
  (  1)::FFFF:                                 
 V447--INDEX SESSID           A ST TIME                      
 V448--(  1) 5166:57974       W R2 1431015452732             

You can see the computer host name (in this case my laptop), my real userid and the main class of the application. The same is true for other tools:
  End User User ID    : plape03                                 
  End User Workstation: plape03mac739                           
  End User Transaction:        

DB2 has two more properties in the client info that can be used. See Client info properties support by the IBM Data Server Driver for JDBC and SQLJ for full documentation.
  • ClientAccountingInformation - This can be up to 200 characters long string with accounting information. The default is JCCversionclient-ip. You can see it in the output of -DISPLAY THREAD on line that starts with V441-ACCOUNTING=.
  • ClientCorrelationToken - Unique value that allows you to correlate the logical unit of work in DB2 to your application. It was added in DB2 and it seems to be read-only.
That is nice but as DB2 database administrator you cannot change the Java application code. In future post we will cover how to inject this information into any application without need to change the code and rebuild and redeploy the application. The identification of the application is a good first step but in reality you need more information about what part of the application was issuing the statement, for example full stack trace would be helpful. We will cover techniques for it as well in future.

If you are a developer you should be using mentioned client info properties to make your application more friendly to database administrators. 

Wednesday, September 17, 2014

PDUG event - Krakow - 2014, September 15 & 16

The forth PDUG event just finished, and it was a great conference again! 

I could count about 50 attendees on the DB2 for z/OS track, and about 30 more on the LUW track. 

Many great presenter and presentations, I particularly enjoyed:

  • What’s Big Data about ? by Tom Juhl

  • Non-standard Data Recovery Techniques by Wenjie Zhu and Shu Wang

  • Analyzing Resource Serialization & Concurrency problems by Bart Steegmans

  • DB2 v11 Overview by Michal Bialecki

  • Recent Changes in DB2 logging by Andy Ward

Last but not the least, there was a great networking event on Monday, and some of us stopped by a “sausage van” on the way back to the hotel, to taste a famous traditional home-made Polish pork sausage. Delicious!

Monday, June 16, 2014

November 6, 2014 – csDUG Conference

The Czech Republic and Slovakia DB2 Users Group (csDUG) is a new Regional User Group (RUG) that was created a year ago.
On November 6th 2014, we will be hosting our SECOND csDUG EVENT, sponsored by IBM and CA Technologies !

This is a full day FREE conference with top industry speakers - an great opportunity DB2 users to meet industry experts !

Agenda of the conference


IBM lab, building 4
V Parku 4, 148 00 Prague 4, Chodov
Czech Republic

Are you a qualified DB2 specialist? Do you want to discover what DB2 is or what it can do for you? Are you an experienced DBA in environments other databases and need support in DB2 for a new project? Are you interested in what role it can play a DB2 architecture of your business?
     --> Join the November 6 csDUG conference to listen to industry experts and network with other people interested in DB2!

Register to this FREE conference by sending an email to
Please specify your name, country/language(s), job title, and company.

Monday, June 9, 2014

News about the DB2 Analytics Accelerator

During SQL Adria conference, Namik Hrle (IBM Fellow) revealed some features that are likely to be delivered onto the DB2 Accelerator technology, hopefully soon. The DB2 Analytics Accelerator (formerly called IDAA) is an appliance that helps execute Business Intelligence (BI) queries much faster. DB2 for z/OS is built on an architecture  that is optimized for transactional queries, but admittedly falls short when running complex analytics that nowadays business demands. The DB2 Analytics Accelerator is the solution that IBM is pursuing to provide high-performance BI queries against DB2 data (almost) transparently. 

Namik used an interesting analogy, explaining that DB2 for z/OS with the DB2 Analytics Accelerator vision is to be like an hybrid car, that can decide to use either its petrol engine or its electrical engine, transparently to the driver (the end-user). Namik also indicated that the IBM lab is considering to provide the ability to:
* Create a table within the accelerator (that would reside solely in there, but would be accessible transparently through DB2)
* Provide a method to load non-DB2 data directly in the DB2 Analytics Accelerator (such as loading a flat file)

These 2 features would open the door to process complex BI queries, joining business data residing in DB2, and many other data sources, structures or unstructured …

Tuesday, March 25, 2014

Why to invest in Mainframe?

Mainframe companies have a big advantage on the financial market. They are robust and predictable. That's why investing in the Mainframe is a valid option. Investing, not speculating. In other words, you may consider acquiring stocks from companies in the Mainframe market, for long term investment. Because the fruits are the dividends, like in a good old stable factory. Although classified in the IT market, Mainframe companies are no start-ups, they are not good for day-to-day (or hour-to-hour !) speculation. Consider this: you may place your savings in a bank, but the interests are not very substantial. Often, the interests will be lower than the inflation (varying by country). So you actually lose money over time. 

Buying stock options implies risk, so you need to be careful with your life's savings. Mainframe companies have years (decades) of financial history that can be considered: average price over the last X years, variations in the stock price, amount of the dividends, stability of dividends... If you are looking for a way to invest a part of your savings, more profitable that a bank saving account, you may have a look at stocks. Stocks that generate predictable dividends. High dividends. Stocks which price does not fluctuate like a yo-yo: you want to be able to sell your stocks, without losing money. Stable stock price, high & predictable dividends: Good combination for long term stock investment. Mainframe.

I did some analysis of the main Mainframe companies' stocks, summarized for your convenience in the table below, that contains assessments:
  • Risk: analyzing stock price to assess price fluctuation (yo-yo) High/Med/Low
  • Risk: analyzing dividends' stability over the years High/Med/Low 
  • Benefit: computation of the yearly profit (annual dividends/AVG stock price)

AVG stock price (3Yrs)
Last year trend
yo-yo price
dividends' stability
yearly profit
$0.95 / quarter
CA Technologies (NYSE: CA)
$0.25 / quarter
Compuware (NYSE: CPWR)
$0.125 / quarter

Disclaimer: I am no financial consultant nor any kind or financial professional, the above article is a summary of my candid thoughts on the topic. Data in the table are a quick assessment of stocks based on data publically available on   Concerns & critics are welcome.

Tuesday, March 18, 2014

Why should young IT professionals start their career on Mainframe?

If you just finished university with a degree in Computer Sciences, you are probably looking for a job… and if you are reading this article, you are probably still searching. Although you might not even know what a Mainframe is, you might want to consider a career on the “big iron”; and here is why.

The mainframe is a 40+ years old platform and most software are written in low-level languages such as assembler or Cobol. Granted. As much as it appears non-attractive, it is a real opportunity: while most of the world data and processing resides in Mainframes, Mainframe professionals (so called “Mainframers”) are close to retirement. The equation is simple, IT talents with such knowledge will be rarity in the very near future, and biggest fortune companies will crave for them.

But … do not think mainframe is solely legacy. In fact, lots of new projects exist on Mainframe, most of which use Java, C, or C++. The new trend of “virtualization” is a notion which exists since decades in the Mainframe world. If you think about it, Mainframe systems are nothing else than a private cloud: Mainframe means enormous amount of data, incredible processing capabilities, and very high security (who even ever spoke about a virus on Mainframe?). Mainframe also rimes with green computing, since it uses much less energy than other platforms, because 1 Mainframe can support a workload equivalent to thousands of distributed servers.

In a few words, it is cool to work on Mainframe!


Wednesday, March 12, 2014

Collect and Analyze DB2 performance data

I recently met a DB2 user who was interested in monitoring and analyzing DB2 performance. He is a DB2 DBA, and he was especially interested in monitoring SQL transactions (figuring out high consumption).
Collecting and analyzing DB2 performance data is one of the main role of Database Administrators, and it’s a complex activity.

Because a video is worth a thousand words, I suggest you have a look at a presentation called Top 10 Tips for Collecting and Preparing DB2 Performance Data. You will need to enter a few contact details to receive the link via e-mail.

Also, you may have a look at some interesting IDUG presentations (from DB2 users) on the IDUG website, you also need to register for free to access to content:
How Did Israel Discount Bank reduce its CPU cost by 10%? – by Yaron Zahavi (Israel Discount Bank)

More specifically, one of the tool that Database Administrators can use for collecting and analyzing DB2 performance is CA Detector® for DB2 for z/OS. By using CA Detector, you can capture every single SQL statements executed so you can get the full picture either because you want to do chargeback, trending, locate the biggest performance hits, or find the low hanging fruit to optimize to get the biggest bank for the bucks. You can also exclude the data collection specific transactions, SQL statements, or plans … to save some resources. If you want to know more about CA Detector, you may also have a look at the product manual.

To complete this topic, there are various presentations regarding DB2 v11 Performance being held at IDUG, and various Regional User Groups.

My little finger tells me that, if you are interested in DB2 v11 Performance, you will want to have a look at the next csDUG agenda, when it will be available ;)

Friday, March 7, 2014

Live news from PDUG!

Greetings from Piaseczno, where the PDUG event is ongoing. I must say that the event is very well organized, PDUG has even a newsletter (paper version, containing technical articles from their users). The sessions are interesting, here some pictures you may enjoy below.

 Jacek Rafalak (PDUG president, Asseco)

John Campbell, IBM Distinguished Engineer

Steve Thomas, CA Technologies

Expert Panel
John Campbell, IBM Distinguished Engineer
Steve Thomas, CA Technologies
Cristian Molaro, IBM Gold Consultant