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.

package com.ca.blog.sampledbapp;

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();
app.run();
}

private void run() throws InstantiationException, IllegalAccessException,
ClassNotFoundException, SQLException, InterruptedException {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
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()) {
count++;
Thread.sleep(100);
}
System.out.printf("Count: %d", count);
}
statement.close();
}
}

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=130.200.80.104, USERID=qadba01,                 
      APPLICATION NAME=db2jcc_application                         
 V441-ACCOUNTING=JCC04150130.200.80.104                           
       '                                                          
 V445-O2C85068.DE86.CE04632EDB52=98403 ACCESSING DATA FOR         
  (  1)::FFFF:130.200.80.104                                      
 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:

Identification                              
  End User User ID    : qadba01             
  End User Workstation: 130.200.80.104      
  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("sun.java.command"));
    connection.setClientInfo("ClientUser",      System.getProperty("user.name"));
    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,             
      APPLICATION NAME=com.ca.blog.sampledbapp.SimpleDb      
 V441-ACCOUNTING=JCC04150130.200.80.104                      
       '                                                     
 V445-O2C85068.E276.CE04710F0A04=99078 ACCESSING DATA FOR    
  (  1)::FFFF:130.200.80.104                                 
 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:
Identification                                                  
  End User User ID    : plape03                                 
  End User Workstation: plape03mac739                           
  End User Transaction: com.ca.blog.sampledbapp.SimpleDb        

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



Location

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 Register.csDUG.6.NOV@gmail.com
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
dividends' stability
yearly profit
IBM (NYSE: IBM)
$180
-14%
Med
$0.95 / quarter
High
2.10%
CA Technologies (NYSE: CA)
$26
+28%
Low
$0.25 / quarter
High
3.80%
Compuware (NYSE: CPWR)
$10
-13%
Low
$0.125 / quarter
Med
5.00%

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 http://www.nasdaq.com/.   Concerns & critics are welcome.