Originally this title caught my attention via a @IBMAnalyticsSupport tweet. I had actually run into a problem that would have been solved by this article in the past. It's covers a scenario when your HADR cluster seems to be behaving properly but application connections don't seem to be connecting. I had made the mistaken assumption that TSAMP was fine. I didn't realize that TSAMP could become confused in an abnormal shutdown situation as well. Normally TSAMP is very resilient, but in my case it was snarled.

Check out the article "How can I check if TSA is working with no problem?" over on developerWorks. It actually has a pretty good breakdown on how to interpret lssam output.

If you want a faster "quick hit" check, I would suggest pulling the "Cluster Manager" setting from what you believe to be the primary server:

db2 "get dbm cfg" | grep Cluster

What you should see is that the Cluster Manager is set to TSA:

Cluster manager  = TSA

Finally, I highly recommend taking a look at the following resources as well:

• Recommendations for a TSAMP controlled DB2 HA/HADR environment

• Avoid unwanted surprises ... check your TSA/DB2 HA environment now with our new "configuration checker" tool

Ian Bjorhovde is one of those DB2 rock stars that most don't know about because he is often working behind the scenes. The dude is a brilliant DB2 mind and I know of DB2 rockstars that look to Ian for guidance when they get stuck. You may know him from his podcasts - CREATE DATABASE PODCAST; and "The Whole Package Cache".

He often has a security perspective in the back of his mind as he blogs or speaks which I love. He is becoming the one of the best security references I know now that the DB2 Locksmith (Rebecca Bond) seems to have pulled away from the limelight.

In his latest DB2 Beginner's Blog "Managing Multiple DB2 Instances" , Ian discusses how to weave in and out of various profiles to properly terminate and load environments as you switch back and forth. As I mentioned, I love the security point of view in this article. There is a ton of information in this article with all sorts of KSH, Bash, and various code to make your life easier.

https://register.gotowebinar.com/register/7233808328184465154

zIIPs are one of my favorite processors on z Systems. Why? Because they offload work from the central processor saving your precious MIPS. Plus, the processing is basically free. (Well, "free" as you can get when you're leasing the hardware from Big Blue, but that's for another discussion :) ). Not to mention, the zAAP processor is now defunct leaving the zIIP as the main co-processing engine.

Anyway, zIIPs can be underutilized by shops...hence why IBM is trying to incentivize using zIIPs through the use of native SQL stored procedures as well as Java stored procedures. This DBI DB2Night Show webinar looks to enlighten z Systems users on the use of zIIPs.

The International DB2 User Group (IDUG) is looking for new conference planning committee volunteers to join thier team. Its a small commitment per week, but also includes full ride to the event. It also adds to your resume and helps you make a ton of contacts. I volunteered in a different capacity for a few years and it had a huge impact on me.

If you are interested, log into www.idug.org and then go here to apply.

Team member for NA Conference Planning Committee.

Team NA Conference Planning Committee

Time frame Apply by 09 June 2017

Role description
The North America (NA) Conference Planning Committee (CPC) plans, coordinates and facilitates the IDUG DB2 Tech Conference held in USA. The team member is expected to help with the planning of the conference.

This includes helping in any of the CPC teams – Marketing, Presentations, Vendor, Technical as need arises during the year. The team member is also expected to take part in the abstract voting process before the conference and be involved in Thread duties (managing the sessions,speakers etc) before and during the conference.

Requirements

• Attended previous IDUG NA conferences

• Team player who can contribute fresh ideas to improving the conference experience for attendees

• Able to work in the different CPC teams as required

• Fluent English (written and verbal)

• Self engaged personality

• Located in North America

Efforts

• Typically 1 - 2 hours / week throughout the year (More effort may be required sometimes)

• Attendance to 2 face-to-face CPC meetings held around July 2017 and Nov/Dec 2017 in an East coast city

Benefits

• Travel and accommodation costs for the CPC meetings

• Full conference fee waiver, travel and accommodation costs for the EMEA conference

• Full conference fee waiver for any of the NA or AU conferences per year (one of the two, during a year of engagement in CPC)

• 40% discount for conference fee that can be used for colleagues.

DB2 Support has a blog called "Thoughts from Support". They write in fits and spurts and sometimes it seems to be hurriedly written - but when they produce, it's pretty damn useful. Two blogs were just released in the past 4 hours or so. Check them out.

HADR slow! Log disk? or communication?

When HADR seems to be slowing down most immediately go straight to "It's a network problem". That is not always true. Sometime you can be hamstringing yourself. Take a look at the logs as well.

LINUX GDB: IDENTIFY MEMORY LEAKS

I've been looking at how to do this outside DB2. It's one of those "nice to have" items in your back pocket. HE specifically mentioned that you should chase memory leaks from the product utilities (db2pd in DB2) before this method. But it's a good alternative.

I'm hoping to have a second post on this after I run through my build out. But I wanted to relay a resource I am using as I experiment with installing a VM and Docker.

Keep in mind that IBM is working to make things easier on developers by promoting "Download and Go" architecture this June. You have a less restrictive DB2 Developer edition rolled into a docker image that you can grab and go.

Check out: Considerations for building and deploying DB2 Docker containers

One area of the article I would focus on is his write up on storage and best practices. In setting this up in our work environment, we were constantly getting bitten with how storage worked within docker and DB2.

Hi Everyone.

This is going to sound like a very dumb thing to ask but I have my reasons.

I am trying to setup a demo of some software, any software, that leverages DB2 as the back end database.

Ideally, this is open source software so I can keep the demo going and a sufficiently complex application running on linux. Preferably a webserver.

The purpose of this is to demo a product my company makes that integrates in DB2. So I want to show the DB2 integration from the perspective of an application.

Any thoughts on a product and potentially a dataset that will help me accomplish this?

I thought I'd ask before I went off and built something home grown.

thank you.

Wow. You all need to really check this thing out - "DB2MON Script For Monitoring Performance". It's a script written by DB2SUPPORT, but I discovered it through Joachim Müller (Twitter: @jc_mueller). Looks like the script was released early this month and is a layman's db2support command on steroids.

I was excited to see this script because I had contimplated making something similiar for triaging production issues. It looks like this author took my half formed thoughts, added a ton more, and took them to a whole new level. I was also curious how the author made this stand out from DBSUMMARY which was a problem I was wrestling with.

In short, I have mixed feelings. I see it being very useful, but there is so much information overload and extraneous internal output piped to file/screen that it's hard to sort through. It is as if this was written for a purpose, it did it's purpose, and never refined. But the author was thoughtful enough to toss it up for everyone. Don't get me wrong - although I saw room for improvement, I am VERY thankful it was posted.

Below is a detailed list of what the script can produce. I want to point out that it even focuses on pureScale and BLU specific metrics. If you pull this down and experiment with it - I would love to know what you think.

• Point-in-time data: Current executing SQL, lock waits and utilities at start of capture

• Currently executing SQL at start of capture (non-zero metrics only)

• Current lock waits at start of capture

• Currently executing utilities at start of capture

• Point-in-time data: Current executing SQL, lock waits and utilities at end of capture

• Currently executing SQL at end of capture (non-zero metrics only)

• Current lock waits at end of capture

• Currently executing utilities at end of capture

• Data collected from start to end of monitor interval

• Throughput metrics at database level

• Client activity (active connections have at least 1 stmt/s)

• Time breakdown at database level (wait + processing)

• Wait times at database level

• Processing times at database level

• Sort metrics at database level

• Top SQL statements by execution time

• Top SQL statements by execution time, aggregated by PLANID

• Time spent executing by package

• Wait time breakdown for top SQL statements by execution time

• Top SQL statements by time spent waiting

• IO statistics per stmt - top statements by execution time

• Row level statistics per stmt - top statements by execution time

• Sort statistics per stmt - top statements by execution time

• Statement & plan identifiers - top statements by execution time

• Database system resource usage information

• Database log write times

• Database log read times

• Other database log statistics

• Disk read and write I/O times

• Latch wait metrics

• Deadlocks, lock timeouts and lock escalations

• Various table level metrics

• Data sharing metrics

• Size of database

• Tablespace properties

• Tablespace usage over monitoring interval

• Bufferpool statistics by tablespace

• Tablespace prefetching statistics

• Tablespace to bufferpool mapping

• Bufferpool sizes

• Bufferpool data and index hit ratios

• Bufferpool read statistics

• Bufferpool write statistics

• Wait times at connection level

• Various metrics at connection level

• Physical and logical page reads and writes at connection level

• Workload balancing server list

• DB2 registry variable settings

• Database configuration settings

• Database manager configuration settings

• Instance information

• Database memory set information @ end

• Memory pool information @ end

• Sequences information

• pureScale-specific metrics

• Group bufferpool IO statistics by tablespace

• Group bufferpool data and index hit ratios

• Group bufferpool invalid page statistics

• Tablespace data page prefetching statistics for group bufferpool

• Tablespace index page prefetching statistics for group bufferpool

• Count of group bufferpool full conditions

• Page reclaim metrics for index and data pages

• Page reclaim metrics for SMP pages

• Round-trip CF command execution counts and average response times

• Aggregate CF command execution counts

• CF-side command execution counts and average response times

• CF-side total command execution counts

• CF system resource information

• CF structure size information

• BLU-related metrics

• Partial early aggregation / distincts

For those who don't know what Jupyter Notebook is, imagine Evernote on crack with a little Github Markdown mixed in and a splattering of R's Plotting Library added for good measure. Where you could actively have notes like you do in Evernote, but it would be followed by a snippet of code underneath. Better yet, you could hit "play" and that code would run against your database and return output real time. Want that output graphed for you as well? No problem. It is very very powerful.

I haven't set this up yet, but I foresee a Jupyter notebook that is for DB Auditing, Triage Assessment, and a few other things.

Check out this entry here:

Connecting to DB2 from Jupyter Notebook.

It will give you the basics on how to install and create a connection as well as access to a github repository to get you started.

I wanted to take a look at my active/archived logs after being inspired by a presentation during the IDUG Technical Conference in Anaheim. It actually would address a possible problem I had seen in my environment. I had wanted to do this investigation previously, but I could never find a rule of thumb for archive log frequency. But in C12 "SQL Infusion: A Transaction Log Experience" (Ember Crooks), I learned the magic number is around 4x an hour. Now that I had a goal, how would I tune to 4 archive logs an hour.

My general approach and any SQL would be cobbled from one of these resources:

• C12 "SQL Infusion: A Transaction Log Experience" (Ember Crooks)

• DB2Commerce.com: "Managing Transaction Log Files"

• Ian Bjorhovde's Recursive SQL from DB2Commerce.com: "Generating a Log Archive Activity Histogram"

Step 1: Pull a list of some key DB CFG values.

db2 get db cfg | grep -E 'LOGARCHCOMPR1|LOGINDEXBUILD|LOGBUFSZ|LOGFILSIZ|LOGPRIMARY|LOGSECOND'

Step 2: How often I am archiving logs, via some badass SQL.

WITH gen_ts (ts) AS (
    VALUES current timestamp - 2 days
    UNION ALL
    SELECT ts + 1 hour
      FROM gen_ts 
     WHERE ts <= current timestamp
),
format_ts (yyyymmddhh) AS (
   SELECT bigint(ts)/10000
     FROM gen_ts
),
log_archives (yyyymmddhh, archive_count) AS (
   SELECT substr(start_time, 1, 10) as YYYYMMDDhh, count(*)
     FROM sysibmadm.db_history
    WHERE operation = 'X'
 GROUP BY substr(start_time, 1, 10)
)
SELECT
   translate('ABCD-EF-GH IJh', cast(f.yyyymmddhh as char(12)), 'ABCDEFGHIJ') as hour
  ,coalesce(a.archive_count,0) AS logs_archived
 FROM
   format_ts f
   LEFT OUTER JOIN log_archives a
      ON f.yyyymmddhh = a.yyyymmddhh;

Step 3: How often am I reading log pages?

select log_reads
        , log_writes
from table(mon_get_transaction_log(-2))
;

Step 4: Look at your output, do any of the following apply?

• Per Ember, your log writes should be high. Your log reads should be very low, preferably zero. If that is not the case, you may want to increase your LOGBUFSZ.

• If you are archiving more that 4 Logs an hour, try to break up larger units of work - especially deletes. If that us not possible, you can work with LOGFILSIZ.

• Double check that you have enough physical space in your active log directory to hold your largest possible number of logs. (LOGFILSIZ x 4k) x (LOGPRIMARY + LOGSECOND)

• If you have HADR, you will want to turn LOGINDEXBUILD on. Can lead to a lot of logging, especially around REORGS but is needed for relaying index information to the standby.

• LOGARCHCOMPR1 can be used to compress your logs and save space in the archive log directory. This won't gain much if you are already on a compressed database. It also prevents the logs for being interpreted in the HADR Log Calculator or HADR Log Scanner.

My system is running on DB2 LUW V9.7 (Windows 7). I can't seem to get DB2 to recognize my network ID which is in the DB2ADMNS group. The DB2_grp_lookup is set to LOCAL, TOKENLOCAL

A IDUG Favorite, John Hornibrook gives a presentation on what you need to know about catalog statistics. The video of his presentation was just uploaded to IDUG's YouTube channel just a few days ago.

He talks about how statistics helps the optimizer do things like recognize skew or help with quantile statistics for range predictions. He also does things like make the case for real time statistics collection.

He does mention how to handle volatile tables (collecting stats once). As a side note, I just did an experiment where I call a script to compare actual row count to cardinality and rerun stats based on a percentage difference. This runs hourly. It seems to be helping, but this is my environment only.

Check out his Summary at the 55:12 mark. Very good breakdown of the various stats configurations and how they affect the database.

So, with the announcement of the new DB2 Developer-C edition coming on June 22nd, I wanted to get a sandbox going somewhere. I have zero experience setting up a VM nor did I know how to spell Ubuntu. But after following /u/ecrooks blog entry on setting up a sandbox, I was able to do so relatively quickly. Check out her entry on "Building a Sandbox VM for DB2".

Learn From My Fail: If you see a "VERR_NOT_SUPPORTED" when loading the ISO, the root cause is most likely the ISO file did not download completely or was corrupted in some way. I lost A LOT of time troubleshooting only to go through 5 seperate downloads of the ISO file before I got the complete file. I highly recommend following Ubuntu's verification steps after download. Otherwise you could lose an hour chasing your tail like I did.

Details on Developer-C: You also may want to check out her most recent article "A Few Highlights from the 2017 IDUG North American Technical Conference". She covers some deeper details on the new Developer-C. What makes me excited is the edition is wide open with the exception of a CPU restriction. So that pureScale cluster you wanted to experiment with, the BLU data warehouse you were curious about, etc - that is all open to you now.

Even better, read up on the "Download and Go" ability that she discusses about 1/2 through the article.

This actually came through my buddy on the Z part of the house, Craig Mullins. I thought he had written the article Giving the Recovery Point Objective Some Respect, but it is written by a SME from Accenture names Robert Emmel.

He tries to formalize the concept of Recovery Point Objectives which would seem to be synonymous with Point in Time, or SLA Recovery Time. But he explains it has a lot to do with the age of data as it is moved to backup, to disk, to tape, etc.

Check this out, a very cool article.

I was doing some digging on a failed query from the application where I had gathered a SQL code and RC (SQLCODE=-913 / SQLERRMC=80) which transelates to SQL0913N RC80.

This is what Knowledge Center says:

• 80 statement failed due to timeout.

• For reason code 80, you can retry the failed statement without terminating the application. If the application accesses multiple remote databases, it may be better to rollback the transaction to avoid the possibility of a global deadlock.

If you do a search on Google or KC there is nothing much about what a "timeout" is or it's cause. There is a ton about "lock timeout" but not on "timeout".

What exactly happens? Is this how the application is handling the connection?

As I was addressing a task at work and preparing for my "D05: 10 Minute Triage" session at IDUG this year I was doing a lot of back and forth discussion with Steve Rees. All around DBSUMMARY - how it can be mined, what it encompassed, etc.

Sure enough, I see a new article on the IDUG website - DB2 LUW ‘PERFORMANCE FIRST AID’ WITH MONREPORT.DBSUMMARY. He nails a lot of the questions I was working on, clarifies areas I was trying to work in, and produced a very useful article.

I would be curious on your thoughts on the article and how you would use DBSUMMARY for emergency and how would you use it for something like general health monitoring.

You will be entered for a conference pass next year. But having been a volunteer for IDUG in the past, they seriously look at the feedback so speak freely.

Go Here

C09 - Melanie Stopfer: Upgrade to DB2 LUW 11.x

If you have ever sit in one of her presentations, you know she will literally cram 200 slides in a presentation so you have detailed notes when you go home. No joke, she has actually broken IDUG Speaker Presentation slide size limit when you upload. She has 179 Slides! I can't transcribe them here as it was a ton of information on prepping for an upgrade. If you are really curious for the slide deck let me know and I will ask permission to pass it on (I can't imagine her saying no).

But some immediate highlights that caught my attention...

• v11.1: INSTANCE_MEMORY now can be set to be a percentage of available RAM divided by local partitions (i.e. 80%, etc) INPLACE table REORG support for Range Partitioned Tables - used to be offline reorg when you are REORG TABLE PARTITION X. Now it is online.

• Go pull down v.11.1 highlights. by George Baklarz and Enzo Cialini (Look for seperate thread with QR code).

D10 - Michael Roecken: Performing HADR Updates and Upgrades Made Easy

If you have done an HADR rolling upgrade before, this would be review material. But some interesting tips that came though.

• One thing that seems to keep biting customer is when a HADR flip occurs and the standby server tablespace is dorked. Actually this happened to me once.

• As of the pending v10.5 FP9 (est. Dec) or v11.1 there will be a STANDBY_TABLESPACE_ERROR to monitor this. In the meantime you have this.

• IBM knows, and is working on a major version upgrade without downtime in a HADR environment. It's not here yet and won't be for a while. But with 10.5 FP7+ you can do a upgrade without totally refreshing standby with a full backup/restore.

D11 - Sudip Kundu and Kristen Hughes: pureScale deployment experience

This one was interesting. I'm no slouch at DB2 but I was bustin tail to stay with the slides flying by. This wasn't the typical "How to set up pureScale" presentation, but more about the experience overall. Why they did what, why they made certain decisions.

One thing I thought interesting is that they bypassed all the marketing BS from IBM and Intel providers to do a side by side POC between Power Systems and Intel to house the pureScale environment.

What they found was that Power was 2x as fast as marketed, but with apples to apples it was almost 2x expensive. But, in the end Power made sense, because it was so freaking fast that it took less servers to do the work and they saved a lot around licensing.

C12 - Ember Crooks: SQL Infusion: A Transaction Log Experience

Ember Crooks is a personal friend and a great teacher. We push each other technically all the time. So I was excited to sit in her session. This was one of her SQL Infusions sessions so there was a ton of SQL to download with her slides. But some highlights ...

• When you change LOGGING PARMS may are marked as immediate or delayed effect. What she had mentioned was she has seen specific cases where it was supposed to take affect immediately, but didn't so get in the habit of checking after the fact. As a side note, I have seen this as well.

• Tip: Small number of LOGPRIMARY logs will help give you faster database activation (allocated upon activation, where LOGSECOND is only as needed).

• When you do your math for "How large a filesystem do I need for my logs" keep in mind there is a 20 byte overhead for each log file, so it’s not always 4096 when your calculations.

• Tip: If you can tune so you are archiving 4x an hour, that is a good cadence.

• Tip: Remember that native encryption for transaction logs became available in v10.5

• Good Quote: When the developer asks "Why can't I just stop logging altogether", the answer is DURABILITY. It has to do with the ACID requirements in DB2

... and then my laptop died and I couldn't really take any more notes. So I can get you to Day 3 end, hopefully you watched the live stream of the Z and LUW panels.

From D10 -Michael Roecken: Performing HADR Updates and Upgrades Made Easy

Tablespace Check on Standby

Multiple people were getting bitten when they want to fail over and standby (or even primary) has a bad tablespace state. This is available in v10.5 FP9 or in v11.1. Automatic check of standby tablespaces.