Summary of DB2 and Q Replication Performance Tuning Techniques

Posted by Frank Fillmore on March 25, 2014 under DB2 for Linux Unix Windows, Q-Replication, SQL Tuning. Tags: , .

Performance tuning for both DB2 and Q Replication are an ongoing process.  However, there are three tools which provide an initial set of recommendations for bufferpools, Database Manager (DBM or instance) and database configuration parameters, indexes, and other application table objects.

 

DB2 Configuration Advisor

The DB2 Configuration Advisor recommends the initial values of the bufferpool size, database configuration parameters, and database manager configuration parameters.

To use the Configuration Advisor, specify the AUTOCONFIGURE command for an existing database, or specify AUTOCONFIGURE as an option of the CREATE DATABASE command.

Example:

db2 autoconfigure using mem_percent 50 num_stmts 100000 tpm 6000 admin_priority performance num_local_apps 144 num_remote_apps 0 isolation UR apply none

DB2 Design Advisor

The DB2 Design Advisor is used to improve application SQL performance by creating (or dropping) indexes, Materialized Query Tables (MQTs), and MultiDimensional Clustering (MDCs).  The Design Advisor analyzes an SQL workload against existing table structures and recommends changes to improve performance.  The workload can be derived from Dynamic Statement Cache (DSC).  The DB2 Design Advisor command can be invoked by the db2advis command.

A prerequisite is creating the DB2 EXPLAIN tables for the schema conducting the analysis.

cd /<path>/sqllib/misc

db2 –vtf EXPLAIN.DDL

Then run the db2advis command.

db2advis -d mydb -g -a myuser -x -c USERSPACE1 -f -m IMCP -o db2advis_output

 

Q Replication Dashboard Performance Advisor

The Q Replication Performance Advisor helps you identify latency issues with your replication environment and suggests ways to lower end-to-end latency.  The Performance Advisor tab is located within each Monitoring Group.  The Q Replication Dashboard Performance Advisor can recommend updating statistics or reorganizing tables.

Postcard from IBM #BigData Fundamentals Bootcamp

Posted by Frank Fillmore on February 20, 2014 under Authorized Training Partner, Big Data, BigInsights, Federation, IDAA, InfoSphere, InfoSphere Streams, Netezza. Tags: , .

Over the last 10 years I have become deeply immersed in three major (then) new IBM Information Management software technologies:

  • IBM InfoSphere Data Replication (IIDR – Q Replication)
  • InfoSphere Information Server (legacy DataStage, et al)
  • PureData System for Analytics (nee Netezza) and – it’s sibling for System z – IBM DB2 Analytics Accelerator (IDAA)

If there’s a theme it’s data in motion, data transformation, and deep analytics.  In 2014 The Fillmore Group is focusing on BigData which encompasses all three.  As an IBM authorized Training Sales and Delivery Partner we offer IBM’s BigData curriculum.  As such I am gathering all of the resources I can find to dive into

  1. InfoSphere BigInsights – IBM’s distribution of Hadoop (and a whole lot more)
  2. InfoSphere Streams – Real Time Analytical Processing (RTAP) for data in motion like stock trades and social networking feeds
  3. InfoSphere Data Explorer – federated discovery, navigation, and search in the BigData world

This week I am attending an IBM BigData Fundamentals Bootcamp.  I have four free resources that I would like to share with anyone who wants to try to understand BigData.  Two Redbooks:

In addition, there are two free VMWare images of fully-functional software available for download.  Please note: these images are to be used for education and demonstration purposes only; they cannot be used to support production workloads.

More to follow on my return from the Bootcamp.

Changing the Hostname for a DB2 Server – An Update

Posted by Frank Fillmore on February 7, 2014 under DB2 for Linux Unix Windows, DB2 Migrations. Tags: , .

Probably my most popular blog post of all time – certainly the one most commented upon – was called Changing the Hostname for a DB2 Server.  If you work in a shop that uses the same image (i.e. a Windows backup, VMWare image, or some other form of snap-shotting a Windows computer) containing DB2 for LUW for multiple implementations you will have run into a problem.  When changing the “Computer name” or hostname, the DB2 instance fails to start with an SQL1042C error.

I won’t repeat the entire blog post here – that’s why I included the link above – but I do need to post an update on the location of the important db2nodes.cfg file.  The db2nodes.cfg file now (in DB2 version 9.7) resides in the instance directory.  On a default Windows DB2 installation, that would be x:\ProgramData\IBM\DB2\DB2COPY1\DB2

Please be sure to follow the rest of the instructions on the original post to clear the SQL1042C error.

Hot-off-the-presses Redbook on DB2 for z/OS High Availability #DB2forz/OS

Posted by Frank Fillmore on January 25, 2014 under DB2 for z/OS, Q-Replication. Tags: , , , .

IBM just published a new Redbook titled Understanding and Using Q Replication for High Availability Solutions on the IBM z/OS Platform.  Two of my fave Silicon Valley Lab replication developer/experts – Cecile Madsen and Jayanti Mahapatra – are co-authors.

Customer expectations in 2014 are that they can conduct business with a variety of financial, retail, and other enterprises 24x7x365.  Even with the availability characteristics of the System z platform in general and DB2 for z/OS in particular, planned and unplanned outages will occur. 

The Redbook focuses on IBM InfoSphere Data Replication (IIDR) Q Replication as the undergirding HA technology.  It details two-node and three-node active-active replication topologies.  I suspect much of the basis for the document is the massive deployment of Q Replication at a large commercial bank in Brazil.

Attending zIM Bootcamp at IBM Silicon Valley Lab Next Week @IBMDB2

Posted by Frank Fillmore on January 25, 2014 under Big Data, DB2 for z/OS, DB2 Gold Consultants, IBM Champion, IBM Information Management Software Sales, IDAA. Tags: , , .

Each year IBM assembles their Client Technical Professionals (CTPs) for System z Information Management software at Silicon Valley Lab.  For the past three years IBM has invited DB2 Gold Consultants to participate.  The 4 ½ day conference is rich with lectures and hands-on labs covering such diverse topics as DB2 for z/OS tools and utilities and IMS.  I am particularly interested in:

During the week I’ll blog and Tweet.  I look forward to your comments and questions.

Q Replication Dashboard Enhancements Requested

Posted by Frank Fillmore on October 22, 2013 under InfoSphere, Q-Replication. Tags: , , , .

If you work with IBM InfoSphere Data Replication (IIDR), you are probably familiar with the Q Replication Dashboard.  The Dashboard is a GUI which enables you to manage and monitor your Replication and Event Publishing topologies.  For example, IBM’s direction is to move away from using asnmon to set monitoring and alert thresholds and to build this functionality into the Dashboard.

Here’s an opportunity to make a good product even better.  One of my customers had a series of requests to enhance the Dashboard:

  1. Provide the ability to export/import connection and configuration settings.
  2. Enable LDAP authentication.
  3. Provide something other than e-mail alerts (e.g. SNMP or Tivoli Systems Management integration).

Take a moment to review the formal Request for Enhancemant (RFE).

Data Interoperability Webinar – Recording and Materials

Posted by Frank Fillmore on October 3, 2013 under DB2 for Linux Unix Windows, DB2 for z/OS, Federation, IBM Information Management Software Sales, IBM Mid Market Customers, Informix, InfoSphere, Optim, Oracle, Q-Replication. Tags: , , , , , , , , .

Kim and I conducted a webinar on Thursday, October 3, 2013 that focused on IBM Data Interoperability software products and their specific strengths, weaknesses, and use cases.  We highlighted the application of the InfoSphere family of products to other-vendor databases such as Oracle, SQL Server, Sybase, and Teradata.  Kudos to Kim for putting together a presentation of disparate, but complementary technologies.

The recording of the webinar can be found here.  The presentation materials can be downloaded: Data Interoperability Presentation – FINAL 10.3.13

“Sick of Oracle? Break Free!” Webinar Recording

Posted by Frank Fillmore on June 27, 2013 under BLU Acceleration, DB2 Education, DB2 for Linux Unix Windows, DB2 Migrations, IBM Information Management Software Sales, Oracle, pureScale. Tags: .

The materials for yesterday’s webinar detailing the reasons to consider migrating from Oracle to DB2 are found here: Break Free from Oracle Presentation 6.26.13

The recorded webinar is found on ChannelDB2.

Central Ohio DB2 Users Group (CODUG) Inaugural Meeting on June 25th – Join Us!

Posted by Frank Fillmore on June 13, 2013 under Big Data, BigInsights, BLU Acceleration, DB2 Education, DB2 for Linux Unix Windows, DB2 for z/OS, DB2 Gold Consultants, Frank Fillmore, Hadoop, IBM Champion, IBM Pure Systems, IDAA, InfoSphere, InfoSphere Streams, International DB2 Users Group (IDUG), Netezza, Optim, Oracle, Q-Replication. Tags: .

From my colleague, Jim Herrmann:

The Central Ohio DB2 Users Group (CODUG) no longer exists, but we are taking action to bring it back as a tremendous resource for the community and an amazing opportunity for leadership. CODUG is going to be a great DB2 regional users group because the members of the central Ohio DB2 community are going to make it so.

We are going to start out with all of you being a valuable contribution to your community by completing the RSVP for the June 25th, where you will have the ability to choose the presentations that you want to see from our amazing speakers. Use the URL below:

http://goo.gl/mpWT0

The short URL above links to a Google Docs form, which some company firewalls block, in which case you will need to make the extra effort of forwarding and filling out the form from home. Your input is essential to the success of the group. Whether you are planning to come on 6/25 or not, please fill out the form with your information and your vote. It’s free and it puts you in direct communication with the new CODUG!

You would be a further contribution to your friends and coworkers who have built their careers around DB2, on any platform, if you forward this message to them to make certain that they know about this amazing kickoff meeting. Don’t worry about them getting multiple copies, worry about them getting none. Forward this message now, while you’re thinking about it. If you wait, you will forget to do it later, and you will have missed the chance to connect someone to their community.

The meeting will be formatted with a single track in the morning and two tracks in the afternoon, one for DB2 z/OS and one for DB2 LUW. We will actually physically split the room at the lunch break. We have three well known names in the world of DB2 who will be presenting at the meeting. The bios of these three awesome individuals you can find after the attached agenda.

We are building towards an amazing day. Fill out the form, share it, and we will be one step closer to creating something powerful in our lives.

Thank you for your time and your commitment to DB2,
Please don’t forget to join us at Facebook and Linkedin to get to know about future activities at CODUG.
Linkedin group:  http://www.linkedin.com/groups/CODUG-Central-Ohio-DB2-Users-5014603/about
Facebook page: https://www.facebook.com/CODUG
Agenda

09:00 – 09:30    CODUG Possibility (Jim Herrmann)
09:30 – 10:00    IBM Announcements (Monte Bauman)
10:00 – 10:15    Break
10:15 – 11:30    “DB2 101” or “I didn’t know IBM Information Management could do that!” (Frank Fillmore)
11:30 – 12:45    Lunch
12:45 – 14:15    First afternoon presentation block (Scott Hayes and Robert Catterall)
14:15 – 14:30    Break
14:30 – 16:00    Second afternoon presentation block (Scott Hayes and Robert Catterall)

Frank C. Fillmore, Jr., DB2 Gold Consultant and IBM Champion, is the Founder and President of The Fillmore Group, Inc., a Premier IBM Business Partner that specializes in Information Management (IM) software sales, consulting, and training.  Frank has appeared on the IDUG Experts Panel and frequently presents to customers and local DB2 users groups.  Franks 25 years of field experience spans IBMs Information Integration, Data Governance, Data Warehousing, and Big Data portfolios. Read More…

#BigData Bytes Recording with @TheSocialPitt, @thomasdeutsch, and @ffillmorejr

Posted by Frank Fillmore on May 20, 2013 under Big Data, IBM Champion. Tags: .

David Pittman, Tom Deutsch, and I had a lively – think ESPN’s “Pardon the Interruption” – big data discussion on a Google+ hangout Friday, May 17, 2013.  Topics ranged from governance and “schema on read” to what would be your BigData tattoo.  Spoiler: mine would be “There’s no single version of the truth”.

Catch the replay here.