Playing around with db2diag

Q Replication Dashboard - v9.7.1

DRDA Performance for Q Replication ASNTDIFF Utility on DB2 for z/OS

POWER7: What’s in it for the free DB2 Express-C?

Earlie today without much pomp or ceremony IBM announced the new POWER7 microprocessor architecture and the 4 new SYSTEM p servers that  utilize the new architecture. POWER7 is the next step in the IBM POWER roadmap and as far as next steps go it is a very significant one. I don’t intend to do a blow by blow account of everything that is new in POWER7 and what it really means. For those that are interested, I recommend attending one of the POWER 7 launch events/webcasts.

If you …

Recommendations

IBM Authorized Training - Distance Learning

DB2 LUW Performance: Scalability, Availability, and Disaster Recovery

DB2 Security chat on February 25

DB2 for LUW Security Primer

2010-02-25
11:30 - 13:00 EST (GMT-05:00)

This presentation will quickly refresh your understanding of the DB2 9.5 security capabilities and then bring you up to speed on the latest addi…

db2top Feature of the Day – Command Line Options (-u and -p)

Today’s topic is the -u option and its partner, the -p option. You’ll see how they can be combined with the -n option when you need db2top to connect with a user ID and password.

The -u and -p options are meant to be used together. The -u option takes one argument, the user name, and [...]

Can database tuning do enough to reduce costs?

BJC HealthCare Improves Clinical Research with DB2 pureXML

Red Hat, British Airways, SAS, TIBCO sued for Data Integration Software Patent Infringement

DB2mag Winter 2009

DB2 LUW Security -- Revoking DBADM (REALLY)

What's new this week (Optim) 02/05/2010 (a guest blog post)

pureQuery performance results for DB2 for Linux, UNIX and Windows!

  I'm the performance architect for, among other things, the pureQuery platform. My team has the responsibility for not just ensuring that our products perform well, but also to help produce verifiable performance numbers that we can share with confidence.

I'm happy to say that we are ready to share our performance numbers for pureQuery access to DB2 for Linux, UNIX and Windows. (We already have some great numbers published for z/OS for both Java and .NET).

The goal of this particular performance test was to measure throughput improvement using static SQL execution, which is possible to do even for existing JDBC applications with no change to the application source code. The increased throughput comes mainly as a result of saving the cost of preparing the SQL when using static vs dynamic SQL. We typically don't see the same level of interest in static execution from DB2 for LUW customers as we do from DB2 for z/OS customers because the LUW platform does not have the same memory constraints as z/OS -- and therefore LUW customers might be more likely to throw hardware at the problem to achieve greater dynamic cache hit ratios and hence improve throughput.

However, static SQL also provides predictable performance because the access plan is pre-determined and I often find users are happier with predictable response times rather than ultra-fast response which can deteriorate over time.

Static SQL execution also provides much more than predictable performance. By using it, you can significantly improve problem determination and traceability. You can also reduce the risk of static SQL injection from dynamically executing applications.  You can read about some of those benefits in this article. And there are additional benefits to pureQuery usage such as literal consolidation or the ability to make emergency fixes to application SQL without changing the application, which you can read about in Sonali's article on 2.2 features. 

OK, now that I've hopefully convinced you that there are many, many reasons to consider pureQuery and static SQL execution for DB2 LUW environments, I would like to go ahead and share our performance results.

The measurement environment

Our measurements were done with a typical 3-tier environment of a client, application server, and database server as shown here.  

A word about the "ERWW" application we use. ERWW is an OLTP application based on an order entry and tracking system that is designed to exercise the database tier much more than the application tier (that is to say, there is not a lot of business logic in the application).  The ERWW workload models a wholesale supplier managing orders, and consists of seven transaction types. The frequency of transactions is set to simulate a realistic scenario; the mix used in the benchmark environment was 47 percent update transactions, 53 percent read-only transactions. The workload is triggered by a Java client program which generates HTTP requests for the required transaction mix.

Results

Before I go into our results, I have to offer up the standard disclaimer that any of you who are familiar with performance work are used to hearing. The tests that we ran were done in a controlled environment where we were able to carefully control extenuating factors that can influence the results. In particular, the type of application you run can significantly affect the results in terms of the mix of database-intensive work versus application-intensive work. The ERWW workload is a very database intensive workload and most of the work is done by the database server processing SQL requests. Therefore, by using pureQuery to optimize the database server side processing, we are in fact optimizing a large chunk of the workload. Consequently the performance gains for this workload are significant. We chose ERWW because it was readily available to us, and not because we thought it would give us the best results. I guess what I am trying to say is that your results will vary.

OK, now that that's out of the way. We measured static execution using both client optimization of an existing JDBC application and also as a 'new' version of the application written in pureQuery annotated method style.  The performance is reported in Normalized Throughput Rate - Transactions Per Second (ITR).  The ITR is the notional throughput rate assuming that the CPUs are 100 percent busy. For example, consider an application with a transaction rate of 200 transactions per second at 75 percent CPU consumption. The ITR for this application would be 200 * 100/75 = 267 tps. This is the notional transaction rate that could be achieved if the CPUs were 100 percent busy, and no other bottleneck is hit first.

We measured the JDBC workload with both 90% and 95% package cache hit ratios. To achieve a 90% package cache hit ratio with the ERWW workload, the DB2 Package Cache (PCKCACHESZ) was sized to 180 x 4k pages, and for a 95% hit ratio it was sized to 210 x 4k pages.

Here are the results with a 90% cache hit ratio. The results are shown on the vertical axis as the database ITR improvements over the baseline of JDBC.

As you can see, client optimization almost doubled throughput over the existing JDBC application. The new application that uses pureQuery method style API more than doubled the database transaction throughput.

The results with a 95% cache hit ratio are shown here.

Note that we achieved significant throughput improvements even with a high package cache hit ratio.

In summary, pureQuery and static execution can offer many benefits, one of which may be improving the performance of your data servers with your applications. By changing the dynamic SQL to static SQL, pureQuery should help you either achieve better throughput on your existing hardware, or reduce CPU consumption of your existing hardware, allowing you to load more tasks onto it. I highly recommend that you also check out the bigger picture around Java acceleration (including the other benefits I mention) as shown in this video.

Jonathan Schwartz resigns as SUN CEO with a tweet

“Today’s my last day at Sun. I’ll miss it. Seems only fitting to end on a #haiku. Financial crisis/Stalled too many customers/CEO no more”

Jonathan Schwartz - Best Leader no more

These are the parting words of Jonathan Schwartz on Twitter. No multi-page letter like Scott McNealy did when he resined this post.

I am constantly amazed at just how much the social media phenomenon has changed the corporate attitude in just a coup…

db2top Feature of the Day – Command Line Options (-n)

Continuing with the command line options from the last post, we have the -n option that takes one argument which is the node name or instance name. This becomes useful when you want to pass to the -d option the name of a database that exists under a different node than the default node.

For example, [...]

Electrocardiography (ECG), Health Checks, Performance Expert, DB2, and Oracle

Electrocardiography (ECG), Twitter, and Databases

eReaders; my personal perspecitve - Part 1

Develop a Java application that uses pureQuery annotated methods, Part 2 of 3

Develop a Java application that uses pureQuery annotated methods, Part 3 of 3

Develop a Java application that uses pureQuery annotated methods, Part 1 of 3

Recommendations

Is Hadoop = Cloud Computing?

If I had a nickel for every time I heard people mention Hadoop or Map/Reduce in the same sentence with Cloud Computing, well … I would have a lot of nickels. If you are not familiar with Hadoop, the best way to understand what it does is to think of it as a method or a programming model for executing complex compute jobs on very large clusters of computers. These clusters can comprise hundreds and, sometimes, thousands of machines. What Hadoop does is break, or Map, these complex jobs i…

Welcome, Keith

db2top Feature of the Day – Command Line Options (-h and -d)

Today, I’m going to start writing about db2top command line options. There are many. I’ll start with -h (help) and -d (database). The -h option gives you the key to all the others:

db2top -h Licensed Materials - Property of IBM Copyright IBM Corp. 2005, 2006 All Rights Reserved.

Usage: db2top [-d dbname] [-n nodename] [-u username] [-p [...]

documenting user defined indexes in sap abap systems.

IBM Manages the Data Lifecycle

Recommendations

What does Hadoop have to do with Cloud?