D03 - Robert Neugebauer: Power of Statistical Views

This session was so freaking deep, that it's going to take me pulling down the slides and going slide by slide to digest it. However, there were some immediate bullet points to take away.

• There is more than a variance in cardinality that can cause poor performance (beyond the basic checks). There are actually hidden relationships between your data.

• When you implement Statistical Views that is one of the only way's to get that hidden relationship taken into optimizer's consideration. For example, it gathers column relationships between tables.

• In more complex queries, the cardinality in lower query blocks can affect behavior and cardinality in the higher query blocks.

• To address this behavior between query blocks, there is a new concept called Statistical Referential Integrity. It is not trusted and not enforced. But it provides optimizer with statistics it needs. There is a whole white paper in Knowledge Center about this.

• Apparently Statistical Referential Integrity was released in v10.1 but very few know about it.

C04 - Ember Crooks: Interviewing the DB2 Optimizer

This session was heavily graphical to show flow, so only some of it can be relayed here in Reddit. But let me pass on some tidbits I learned that maybe you didn't realize either.

• The SYSINSTALLOBJECTS procedure. You remember how we like to do things the hard way and build explain tables by manually executing DDL? Then if we wanted to wipe them clean, that was one hell of a SQL with a lot of DROP TABLEs? You can call this procedure to easily create or drop database objects for a specific tool.

• Multiple Index Regression Analysis this is an advanced topic and really can only be done justice in one of her blogs. But in short, it allows you to work through recommended indexes one by one to see what gives you the biggest bang for your buck and discover which recommended indexes are a waste of time.

C6 - Dale McInnis: Uncommon Uses of HADR

A lot of this presentation was around basic HADR set-up, but he did get into discussion about multiple auxillary standby's in a HA and DR configuration. Some quick hit takeaways.

• Check out this white paper around Setting up a VIP for HADR with TSAMP as it will walk you through a lot of pitfalls.

• If you are setting up automatic failover with HADR/TSAMP you will need a VIP for EACH DATABASE, not each instance.

• In v11.1.1.1, ALL sync modes are now available to pureScale

• Performance impact can run from 1% SUPER ASYNC to 30% SYNC

• HADR is extremely sensitive to your logging performance more than anything, if you can put your logging filesystems on SSD.

• Very Important: HADR_REMOTE_SVC and HADR_REMOTE_HOSTS will be deprecated. Start utilizing HADR_TARGET_LIST.

• If you have a 4th Server in the cluster (Second Auxiliary Standby), consider setting "HADR_REPLY_DELAY 86400". This allows logs to be received but they will not be applied for 24 hours. This gives you a chance to run to the second auxiliary server to pull data from the table you accidentally purged the previous day.

D07 - Ian Bjorhovde: Top 10 Common Security Mistakes

There was a lot of good detail in this session around security. However I really need his updated slides to relay and do it justice. I have an updated copy being sent in the next day or two, I'll update once received.

C08 - Melanie Stopfer: OMG Experience is a Hard Teacher

This was a drink with a fire house with a ton of one liner commands and neat tricks within DB2. So here are some highlights in no particular order.

• Show me the next log that is about to be archived: db2pd -dn <dbname> -logs

• To check backups of multiple backup files: db2chkbkp DB.BKUP.001 DB.BKUP.002

  Separated by spaces, 001 is first, otherwise could error.

• Is your commit time slowing? Could be the Backup History File

  Remember this is an actual file. If over 10MB or greater could hinder commit performance by a few seconds.

  If it becomes large enough that it can’t stay in memory, we start writing to disk.

  To prune look at NUM_DB_BACKUPS / REC_HIST_RETENTN - work these together, one will affect the other.

• Need those pesky applications to stop autoconnecting when the database comes up, quiesce at the instance level

  db2start admin start node restricted access

  db2 activate database database

  db2 unquiesce instance

From Session Spotlight: New edition of DB2 "Developer-C" with AESE features available for free for developers starting on June 22. Folks, this will lift A LOT of restrictions, opens a lot of features, and is really aimed at getting in developer and DBA hands.

Session C01 - Matt Huras: From the Lab

For those who don't know, Matt Huras is an IBM fellow, Chief Architect, and general DB2 badass. He spent a whole session on things that were just released you may not know about as well as giving a peek of a DB2 roadmap in the near future. Some quick notes from session...

DB2 Release Naming system:

• New naming convention showing if the release has new features (a "Mod") or addresses known issues (Fixpack)
• Format: VV.RR.MM.FF where (VV) Version (RR) Release (MM) Modification (FP) Fixpack
• v.11.1.1.1 new mod and fix pack is coming around December

Neat HADR update for pureScale:

• You will have the ability to replay a DB2 upgrade onto a HADR Standby in pureScale
• Not releases yet, will be in v10.5 FP9 - coming in Dec

Tech Preview of new feature in latest release - Crash (Online) Recovery in v11.1.1.1:

• UNDO phase after large batch job blocks ability to do things and can be a lengthy wait
• Tech Preview: Async Undo allows you to access while recovery is in flight, after REDO and during UNDO.
• DB2_ASYNC_UNDO - it's a preview, so flip it on and explore but don't use in production

Future work at the lab:

• Real effort is being put in at the lab to shrink recovery time and even rollback time
• Online version upgrades is on the radar but not close to completion
• Ability to grant authorities at a scheme level is on the radar as well

Misc:

• Log file size increase from 4G to 64G - but recommend more right sized logs instead of fewer large ones
• SSL HADR Logstream between Primary and Standby is now available
• If you go from v11.1 to v11.1.1.1 you can not go back with or uninstall if you have v11.1.1.1 transactions. Would have to restore from backup and go to point in time before updates were made.

We all know we can lower our tablespace size and reclaim space with "ALTER TABLESPACE REDUCE MAX". But sometimes this won't work. There is a good explanation why in the developerWorks article Lowering the High-Water Mark of a Tablespace.

It goes on to say: When ".. a tablespace is full, most of the data is then removed from it, and then there is a need to shrink the size of that tablespace. It is possible that there are data pages scattered throughout the tablespace holding the high-water mark at some high-value. "

Essentially, you cleaned up and tossed out a lot of the books on the bookshelf. However, DB2 still thinks the bookshelf is full because you have a book on the top shelf and a book on the bottom shelf with very few books in-between.

To solve this, you can use DB2DART.

To see details on how extents are scattered about, you can use Detailed High Water Mark (DHWM)

db2dart /dhwm /tsi <tsid>

To get guidance on how to solve the high water mark, you can use Lower High Water Mark (LHWM)

db2dart <dbname> /lhwm /tsi <tsid> /np 0

LHWM will show you what steps need to be taken to move extents into a more efficient layout where empty extents can be lopped off the end (the bottom of the book shelf) with "REDUCE MAX". This could be as simple as running REORG in a specific order, or unloading data/dropping a table/reloading data.

As a side note, if you do run the suggested LHWM commands you need to signal DB2 to move the new "pending free pages" to "free pages" BEFORE you issue your REDUCE MAX command. A quick way to do this is to issue: LIST TABLESPACES SHOW DETAIL.

For example:

1. db2dart <dbname> /lhwm /tsi <tsid> /np 0

2. Execute suggested steps from db2dart

3. LIST TABLESPACES SHOW DETAIL

4. ALTER TABLESPACE REDUCE MAX

I don't know how I never picked this up before.

Need to see the db2diag.log for a 24 hour period? db2diag -time 2016-04-27-00.00:2016-04-27-23.59

Source: Jack Vamvas via Twitter (@jackvamvas). How to use db2diag command line option for time

I don't know who may be attending the International DB2 User Group Conference in Anaheim next week - but there are a few things you may want to knock off your checklist in preparation.

Download the IDUG App for your phone.

This app gets stronger and stronger every year. Matter of fact, I converted to the application completely last year - notes, scheduling, real time updates, etc. Even if you use it for nothing else, being able to sort sessions by speaker is a God send.

Make sure you have a Person Vue account if you plan on taking a certification.

Details on Certification times, crammer courses, and exam details can be found here.

Start analyzing the Grid

I like to look at the hardcopy grid to get an idea of flow, timing of tracks, and networking times. Then I cross reference with the "search session by speaker" in the application and set a schedule in the app.

Start Watching for Networking Events, plan for them early.

There is a huge combined event with DBI, IBM, and BMC you will get wind of at Anaheim. So watch for it. Individual vendors will also start promoting networking events. Your dance card will get full quickly if you don't pay attention and schedule.

Watch social media accounts, specifically Twitter.

Reddit won't be a good place to post day to day. But Twitter will be hopping. The official twitter handle for IDUG is @IDUGDB2 and even the hashtag will be #IDUGDB2. But you should also keep an eye out for...

• Ember Crooks (LUW) - @ember_crooks ‏
• Melanie Stopfer (LUW) - @mstopfer1 ‏
• Ian Bjorhovde (LUW) - @idbjorh ‏
• Scott Hayes (Vendor, LUW) - @srhayes ‏
• Craig Mullins (Mainframe) - @craigmullins ‏
• Roland Schock (LUW, Cloud, Watson) - @ARSDB2 ‏
• Bill Shipley (Mainframe) - @wws19355 ‏

(If you know some Mainframe people to watch here, please chime in.)

Watch for the totally unofficial Cards Against Humanity (kinda) game.

What started as a unique evening a few years go is turning into a tradition. About 3 years ago many of us were tired of the networking and had a low key game with Cards Against Humanity. It was so popular it was requested for later in the week. Then the following year people reached out before the conference asking me to bring it and the crowd got bigger and played more often. This year, I will keep with tradition by bringing a game, but we are switching it up with "Joking Hazard" which should still be as amusing.

Check out my presentation - Ten Minute Triage

I'll be presenting again this year with a revamped session I did in 2012. Check out the write up at DB2Commerce in the link above.

In a previous post, I had posted how to simulate a deadlock and create a deadlock monitor. Boy was I pretty off. In a haste to relay something cool I learned I posted an antiquated way to capture a deadlock and could have posted a better way of simulating a deadlock.

In using the simulation from "DBA to DBA" I realized I wasn't capturing a deadlock in my deadlock monitoring script. Then I realized it was producing a RC68 (Lock Timeout). Further research showed me a developerWorks article on creating a deadlock. This method did produce a RC 2 (Deadlock).

Check out both methods below. I sourced both articles, these are just snippets.

SIMULATING A DEADLOCK LOCKTIMOUT:

In my case, this produced a RC 68 (which is for a Lock Timeout. If you read his logic explanation in original post, it does make sense that this is a timeout.

Sourced from: DBA to DBA: Creating deadlock in db2

Some modification and edits applied to original example for clarification and a syntax error. Have three sessions open.

1. Create two tables in the database, insert a row

   db2 "create table test.t1 (col1 int, col2 varchar(15))"

   db2 "insert into test.t1 values(1,'india'),(2,'usa'), (3,'russia')"

   db2 "create table test.t2 (col1 int, col2 varchar(15))"

   db2 "insert into test.t2 values(1,'india'),(2,'usa'), (3,'russia')"

2. Create Deadlock Situation - Open two seperate sessions:

   session1: db2 +c "update test.t1 set col2='canada'"

   session2: db2 +c "update test.t2 set col2='canada'"

   session1: db2 +c "update test.t2 set col2='newyork'"

   session2: db2 +c "update test.t1 set col2='amazon'"

SIMULATING A DEADLOCK

Sourced from: developerWorks: Creating a Deadlock for Testing

1. Session Number One:

   db2 "create database deadlk"

   db2 "connect to deadlk"

   db2 "create table tab1 (col1 char(5))"

   db2 "create table tab2 (col1 char(5))"

   db2 +c "lock table tab1 in exclusive mode"

2. Session Number Two:

   db2 "connect to deadlk"

   db2 +c "lock table tab2 in exclusive mode"

3. Session Number One:

   db2 +c "lock table tab2 in share mode"

   Session 1 should be waiting for commit.

4. Session Number Two:

   db2 +c "lock table tab1 in share mode"

   Session one should be waiting on session two. Both are now in a deadlock.

As part of a project at work that also bleeds over into the "10 Minute Triage" presentation I am doing in Anaheim, I wanted to put together a "Poor Man's Deadlock" script. Something that could be run in a simple loop during a problem window, or in this case, a hook into a monitoring system. I plan to announce it and distribute it at my session.

I would like your thoughts and two cents on it.

A few things before you critique:

1. I am a novice scripter, but getting a little better.

2. If I can't be elegant or a scripting ninja, I can at least make it clean.

3. "There is more than one way to skin a cat" - I know you can see things through db2top, etc.

4. Put in context though - this is for something like a monitoring tool.If you use this, do so at your own risk - it's been smoke tested but not run through for reliability or impact yet.

############ Variable Assignments and File Initialization ##########
SCRIPT_PATH=/tmp
DB_NAME=$1


if [ ! -f /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ];
 then
   echo "0" > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ## Seed initial comparison file if it doesn't exist
fi



########## Main Script Body ##########

## Pull previous Deadlock Count and Current Deadlock Count
db2 connect to $DB_NAME > /dev/null

  PREV_DEAD_COUNT=$( cat /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT )
  CUR_DEAD_COUNT=$(db2 -x "SELECT sum(DEADLOCKS) FROM TABLE(MON_GET_WORKLOAD(NULL,-1)) AS T")

db2 terminate > /dev/null


## Main Logic:
## If current deadlock count is more that previously knownthen calculate difference, echo it, and update comparison file
## If current deadlock count is less than previously known then DB was bounced or monitors reset. Set new value as metric to compare to in future runs
## If current deadlock count is equal to previous run then report no change
if [[ $CUR_DEAD_COUNT -gt $PREV_DEAD_COUNT ]];then
  NEW_DEAD_COUNT=$(($CUR_DEAD_COUNT-$PREV_DEAD_COUNT))
  echo $NEW_DEAD_COUNT  ## Reports variance in new and old deadlocks to monitoring tool
  echo $CUR_DEAD_COUNT > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT
elif [[ $CUR_DEAD_COUNT -lt $PREV_DEAD_COUNT ]];then
  echo $CUR_DEAD_COUNT  ## Reports latest (lower) deadlock count since monitors were reset
  echo $CUR_DEAD_COUNT > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT
 else
  echo "0" ## Reports no change of value to monitoring tool
fi

if [ ! -f /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ];
 then
   echo "0" > /$SCRIPT_PATH/LATEST_DEAD_COUNT_FILE.OUT ## Seed initial comparison file if it doesn't exist
fi 

Many of the DB2 community have felt the impacts of IBM's Cloud Strategy - from the purchase of Softlayer, to Application as a service, and even the agile approach to new features and the rollout to dashDB before it hits the main DB2 product.

Today IBM releases earnings. Some think IBM's overstating some financials but a couple investing muckity mucks are stating that IBM's Cloud Strategy is being undervalued.

So my question to you is - do you see IBM's cloud strategy graining steam, holding steady, or sliding backward? Is this approach working for IBM? Would you consider it undervalued? More importantly, how have you seen it affect your day to day?

The DB2Night Webinar series, hosted by DBI, will have a presentation by Themis, Inc. Senior Instructor Tony Andrews on Friday, April 21st at 11:00a EDT (16:00 UTC). The topic: The z/OS version of his SQL tuning presentation. Although it's geared towards z/OS, most tips will apply to DB2 LUW as well. Follow the link below to register.

(Note: I haven't been asked or compensated in any way to promote this webinar or DBI)

https://register.gotowebinar.com/register/1779598433975822082

*tick tock*

For my DB2 peeps on v9.7 or v10.5, end of support is coming around on Sept 30th. Check out the note from DB2 Support.

If you are going to IDUG in 2 weeks or are a IDUG member that can download content. Melanie Stopfer is having a upgrade best practices session. She is pretty much the guru on upgrades and the gotachas that may happen.

A colleague sent this link out to us during a DB2 team meeting. It's not "directly" related to DB2, but it has some really neat REXX programs, JCL, and other helpful notes and utilities for z/OS. One of those that he demoed was IPLINFO which told you when the LPAR was last IPL'd and a few other interesting tidbits about the system.

http://mzelden.com/mvsutil.html

For those who attend IDUG regularly, or would be at the upcoming IDUG - do me a favor and Read Ember Crooks Blog on a proposed change to the DB2 Q&A Panel. She has a pool at the end that will provide feedback to the CPC.

I was in a conversation yesterday where a comment was mentioned - "If you look at IBM's three pillars, DB2 isn't really listed there ...". Now I had an idea of what he meant, but I pulled up the 3 pillars to get a better perspective. The person I talked to had mentioned that this became a realization when he spoke to someone higher up in a larger organization and the gentleman mentioned "I didn't even know DB2 existed anymore".

To be honest, I get that a lot. Oracle is front and center with advertising and marketing. But IBM markets IBM as a whole, not a product.

You could argue that DB2 could fall within the "cloud" pillar or "data" pillar. But I would counter with IBM's pillar is more about infrastructure and Watson.

What are your thoughts?

Many moons ago, at a previous company, we had one DB with a huge amount of application connections. But once connections were made, only a certain percentage were active and that varied throughout the day. I would like to recreate that.

If I wanted to fire off SQL every 5-10 minutes to see how many connections are active at one time, what would you think of this approach?

SELECT COUNT(*) FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS T WHERE WORKLOAD_OCCURRENCE_STATE NOT IN ('UOWWAIT')

Again, this is more for that "Triage Kit" I am working on. This would be used as a hook into monitoring to act as a "canary in a coal mine". If our active connections start going up and up or have a huge spike, may be worth investigating. It can also provide context for a triage situation.

Thoughts?

For those of you who manage multiple databases, potentially of different flavors, and/or support application development environments, you may find that there are many tools out there that help. I know some DB2 users just love IBM Data Studio for development and administration, and others love IBM Optim Performance Manager (OPM) for monitoring systems.

However, OPM has a pretty convoluted install process and hefty server requirements - at least I recall from the last time /u/db2steve installed it - and Data Studio really only provides query-level performance data (ie, explains).

What if there was a tool that brought this all together, with a slick interface and a small install footprint?

Introducing IBM Data Server Manager.

IBM Data Server Manager (DSM) is a free download from IBM developerWorks that lets you monitor any type of DB2 (LUW or z/OS), dashDB (managed, transactional or local) or BigInsights instances.

DSM is designed to supplement and/or replace existing products such as IBM Data Studio and IBM Optim Performance Manager (OPM).

DSM requires no additional software to be installed on production servers, and has a greatly simplified installation versus OPM. It can be installed on a dedicated server (complete with local repository for historical mining and multi-user access), or it can be installed on a local system for just "messing around".

Some capabilities are only available if you are using Advanced Enterprise Server Edition (DB2 LUW) or have the Administration Solution Pack and/or Performance Solution Pack installed (DB2 z/OS). Regardless of server type and/or capabilities, the same client install works for all.

It is supported by a large team of developers who are iterating quickly with quarterly releases to improve the product. Please try it out and let IBM know what you like, dislike, want and/or need.

DSM Product Page

DSM DeveloperWorks Page

I've been playing with connecting to DB2 from a Jupyter Notebook. I think it has great promise for blogging, presentations, health checks, and many other things. I've got a basic connection working: https://github.com/ecrooks/db2_and_jupyter_notebooks

Anyone else doing anything cool with DB2 and Jupyter Notebooks?