I went live with the new design late last week after some helpful input from a few friends, and a number of hours worth of work on it over the course of several weeks. I’m still finding a few things here and there to tweak, but most of it looks the way I want it to now. May I just say how frustrating the design differences are between browsers. I like the site best in Chrome and Firefox, and am not as fond of how IE displays things.

I personally really like the new design, but would love to hear from readers!

Some of my SQL tips are going to seem pretty darn basic, but the truth is that the most basic topics are my most popular articles, and I might have a tidbit here and there that some of my readers aren’t familiar with.

This first tip is centered on the COALESCE. I actually first became aware of COALESCE when I took an Oracle administration class. We had to use a COALESCE function in class, and I thought there must be something like it in DB2.

COALESCE vs. CASE

The whole point of COALESCE is to help you deal with a situation where you may want to return something different if a value is NULL. This is pretty common, and while you can do the same functionality with a CASE statement, COALESCE is shorter and simpler.

Basically, the whole point of COALESCE is that it is a function that returns the first value that is not NULL. You can pass in as many (compatible) arguments as you like – it can handle any data types. Many times, the last argument passed in is a string literal or constant (number).

How can this help you? Let’s take a look at an example. In my case, I needed to return a value from a table, but if that value happened to be NULL, I needed instead to return a default value of -20. In my case, the CASE statement looked like this:

case 
    when stgfilter is null 
        then -20 
    else stgfilter 
end

Not complicated, but still several lines to get what I need. When using COALESCE to do the same logic, I can use:

coalesce(stgfilter,-20)

Performance

Logically, it feels like COALESCE has less work to do, so should perform faster. But I don’t know if the DB2 optimizer really knows the difference. When I did a little experiment with my two methods on tiny little <200 row tables, I got the following two explain plans:

Case statement

Access Plan:
-----------
        Total Cost:             15.1685
        Query Degree:           1
 
                             Rows
                            RETURN
                            (   1)
                             Cost
                              I/O
                              |
                               1
                           >^NLJOIN
                           (   2)
                            15.1683
                               2
                     /--------+---------\
                    1                      1
                 >NLJOIN                IXSCAN
                 (   3)                 (   7)
                 15.1579               0.0103311
                    2                      0
           /-------+-------\              |
          1                   1           18
       IXSCAN              FETCH    INDEX: WSCOMUSR
       (   4)              (   5)   X_STR_MAP_IX01
      0.0145029            15.1434        Q5
          0                   2
         |               /---+----\
         110            1          26359
   INDEX: SYSIBM     IXSCAN   TABLE: WSCOMUSR
SQL120207213008270  (   6)      STORECENT
         Q1          7.57994        Q3
                        1
                       |
                      26359
                 INDEX: WSCOMUSR
                    I0000345
                       Q3

COALESCE

Access Plan:
-----------
        Total Cost:             15.1685
        Query Degree:           1
 
                             Rows
                            RETURN
                            (   1)
                             Cost
                              I/O
                              |
                               1
                           >^NLJOIN
                           (   2)
                            15.1683
                               2
                     /--------+---------\
                    1                      1
                 >NLJOIN                IXSCAN
                 (   3)                 (   7)
                 15.1579               0.0103311
                    2                      0
           /-------+-------\              |
          1                   1           18
       IXSCAN              FETCH    INDEX: WSCOMUSR
       (   4)              (   5)   X_STR_MAP_IX01
      0.0145029            15.1434        Q5
          0                   2
         |               /---+----\
         110            1          26359
   INDEX: SYSIBM     IXSCAN   TABLE: WSCOMUSR
SQL120207213008270  (   6)      STORECENT
         Q1          7.57994        Q3
                        1
                       |
                      26359
                 INDEX: WSCOMUSR
                    I0000345

No, I did not copy and paste wrong – those are two exactly identical explain plans. And when I checked, the re-written SQL did not show one method had been converted to the other.

I can’t exactly exrapolate these results to more complex situations, of course, but I still thought it was interesting.

I would love it if any readers have any knowledge about the performance differences of CASE vs. COALESCE that they’d be willing to share in the comments.

We’ve reported this issue to IBM, but since we’ve run into it several times across more than a year, and in multiple different Fix Packs of Commerce, I thought I’d share it in case someone else runs into it. The main way it manifests initially is like this:

/configureWorkspaces.xml:245: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=DB2INST1;SELECT;WSCOMUSR.KEYS, DRIVER=4.14.122

The user ‘DB2INST1′ in the above may change, but basically an error like this comes up somewhere along the line saying that your DB2 instance owner does not have permissions on the database.

We only see this for an instance creation where isRemote is set to false.

I’m not sure that anyone cares about the root cause of the issue, but our engineers narrowed it down to a single line that fails in one of the Commerce scripts – COMMERCE_HOME/bin/createdb.db2.sh in Fixpack 5, line 155(also in later fixpacks, at least to FP7):

db2 -v attach to $DB2INSTANCE user $USER using $PASSWORD >> $LOG 2>&1

$DB2INSTANCE is undefined anywhere else during this process. It results in a minor error during DB creation:

attach to user db2inst1 using db2inst1
SQL0104N  An unexpected token "db2inst1" was found following "USER".  Expected tokens may include:  "USER".  SQLSTATE=42601

The underlying problem is that the db2profile was not sourced for the user id running the instance creation prior to running the instance creation.

This issue is only seen if you are using separate ids for DBAuser, DBuser, and running the instance creation (which you should be!). It appears to me that WebSphere Commerce is assuming you are using the same ID for everything, so you would already have the db2profile sourced.

The key to preventing this is to ensure that the db2profile is sourced by the user in question prior to executing the instance creation. You may have to do this in the .profile, .bash_profile or .bashrc, but it is easy to add lines like this (changing paths depending on where your $INSTANCEHOME is):

if [ -f /db2home/db2inst1/sqllib/db2profile ]; then
    . /db2home/db2inst1/sqllib/db2profile
fi

Problem Description

An international client went live recently. We did the work of building their new WebSphere Commerce 7 website, and go-live involved loading some data from their old site. I’m not sure exactly how that load was done, but I suspect massload (a WebSphere Commerce Utility) was used. Somehow the data in that load file was messed up, and there were issues where the last name should have been:
MUÑOZ
but instead showed up as:
MU▒OZ
This was put very literally into the database, so we had bad data in the database.

It wasn’t just that character. I identified several other incorrect representations of either the same character or other “special” charaters, including:
▒
„
‰
ã
�
Ã

The thing is that all of the others were easy to query for and find. As inefficient as a LIKE on ‘%string%’ can be, when it is a one-time thing and critical, it worked.

But when I tried a LIKE on ‘%▒%’, I got no results, even though I knew the value existed. Even a search on = ‘MU▒OZ’ returned nothing when I knew there were dozens of them. Even a count on it pulled up nothing. I even tried LIKE ‘%@▒%’ escape ‘@’, and that did not work.

The even bigger problem here is that when anything with this character was queried using application tools, the application tool (such as CMC) simply stopped and reported “error”. The application was unable to proceed. This actually appeared to happen for all JDBC tools since I got similar results in CC and Data Studio (yes, I stooped to the use of GUIs to check things out).

The problem statement became: How do I query a character that does not really seem to be there – that DB2 SQL for some reason cannot read?

Finding a Resolution

I am more and more convinced that a expert is not someone who knows everything about a topic, but someone who knows a lot and knows who to ask when they get stuck. After googling and searching the info center and IBM support site, I sent an email out to all of my DBA friends looking for some help. One person was able to get the question in front of a well-known IBMer who gave me the suggestion that worked.

They suggested looking at the actual binary values that made up the string. Using that, I was able to come up with the answers. To figure out what binary the character was, I looked at some of them using this query:

select substr(lastname,1,18) as lastname, cast(lastname as varchar(50) for bit data) as bin_lstname from wscomusr.address where lastname like 'MU%' with ur

LASTNAME           BIN_LSTNAME
------------------ -------------------------------------------------------------------------------------------------------
MU;OZ              x'4D553B4F5A'
MU▒OZ              x'4D55D14F5A'
MUÑOZ              x'4D55C3914F5A'
...

From this, I could tell that the problem character was ‘D1′. So assuming that D1 wasn’t the random combination of the end of some other character and beginning of some other character, I found the rows using:

select lastname from wscomusr.address where locate(x'D1',lastname) > 0 with ur

LASTNAME
--------------------------------------------------------------------------------------------------------------------------------
MU▒OZ
MU▒OZ
MU▒OZ
...

Since we had proof that ▒ was not always Ñ, we chose not to try to replace anything. We were dealing with just a few thousand occurrences, so I queried the primary keys of the rows with issues, and the developer used the input file to write appropriate update statements for each one.

This was all days of work to work through, of course – don’t get the impression that any of this was easy.

I put this out there so that anyone else who runs across this or a similar issue can learn from my mistakes, and hopefully find an answer without having to email every DBA they know.

Scott Hayes, Dan Luksetich, Kurt Struyf, and Me (taken by Kelly Fitzgerald)

Me with JB, Rebecca Bond, and Susan Visser

Me, JB, Mike Krafick, Rebecca Bond

Before the Spotlight with a bunch of folks

Me, presenting. If anyone has a better picture, please send/share!

From the photo booth at the DBI Party – Me, Adam Schneider, and Mike Krafick

The past two winners of DB2′s Got Talent – Kohli and Mohan

Mike Krafick, Me, and a first time IDUG attendee!

Additional Pictures added after initial publish of this post:

Doing the DB2 Cocktail hour with Dan Luksetich and Scott Hayes

Truncation

When we first got our hands on online reorgs, it seemed like the “INPLACE” keyword was all we needed, and we had availability during reorg – that any they required less disk space at one time. Yet, if you don’t specify the “NOTRUNCATE” keyword on the reorg command, then at the end of the reorg, DB2 does a lock-drain on the table, and acquires an S lock. This may be more accepatable in a DW/DSS environment than it is in an OLTP/e-commerce environment. I still find that my smaller clients can handle truncation just fine, so I implement reorgs allowing truncation (usually at a very low volume time), and then wait for issues before adding in that “NOTRUCATE” keyword.

What does NOTRUNCATE really do? Well, in the course of a reorg, the pages are properly populated with data – fitting as many rows as possible on to a page. If there has been more data deleted than added or if the pattern of deletions has caused pages to become sparsely populated, then after the reorg, DB2 is left with empty pages at the end of the table. If you do NOT specify NOTRUNCATE, an S lock is acquired on the table and these pages at the end of the table are freed back to the tablespace to be used however makes sense for the tablespace.

If NOTRUNCATE is specified, there is no lock, and those pages remain a part of the table to be filled or left empty as data growth for that table only allows.

Think about the growth patterns in your database when deciding whether or not to allow truncation. Generally my e-commerce databases are steadily (and slowly if I’m pruning right) growing. Unless there’s a large delete, I don’t expect to have a lot of pages to truncate, and even if there are some, they’ll generally get filled in with data pretty quickly.

Thus if you’ve been doing a lot of deletion, you’ll want to let your reorgs truncate so you can get that space back.

Online Reorg of Indexes

We call it online index reorgs. And, heck, it is more online than the old classic offline reorgs. But, unless you’re specifying ‘CLEANUP ONLY’ or ‘CLEANUP ONLY PAGES’, DB2 will re-build the indexes and acquire a Z lock on the table while switching indexes. Yes, a Z lock – also known as a super-exclusive lock. This causes troubles, especially for my larger clients.

To be clear, the syntax that triggers this behavior is:

REORG INDEXES ALL FOR schema.table ALLOW WRITE ACCESS

The good part is that you should only be doing this type of reorg if formulas F5 or F6 are flagged in REORGCHK. If formula F7 is flagged, you should add ‘CLEANUP ONLY’ and if formula F8 is flagged, you should add ‘CLEANUP ONLY PAGES’. If formula F4 is flagged, you should be reorging the table on the index only if you desire the table to be clustered on that index, otherwise you should ignore flags on F4.

Reorg Performance

I’ve asked a number of people in a number of situations if I can either improve reorg performance or throttle the reorgs. Reorgs cannot be throttled, and several people recently have made it clear to me that IBM’s strategic direction is not to improve REORG, but to reduce the need for reorgs.

There are two things you should know about REORG and perceived performance. The first is that an inplace REORG will wait forever for each and every lock it needs. REORG does not respect LOCKTIMEOUT. If you have users that are not committing frequently, or worse, a table where an application holds a lock on the table continuously, you can run into problems. Maybe the answer to speeding up the reorg is to get your applications to commit more frequently.

I’ve actually had an issue with an event monitor that writes to an unformatted table – I have to specifically exclue it from reorgs, or my reorgs hang up on it.

The other thing you need to know about reorg performance is that clustering reorgs are slower than non-clustering reorgs. A clustering reorg (REORG table on INDEX) does a forward scan through the table and generates a RID list using the specified index. It clears out a few pages, and then moves in the RIDs that need to be there. A non-clustering reorg starts at the end of the table and tries to move rows from the end of the table up among the other rows – if it vacates pages, they are already at the end of the table.

That means that a clustering reorg often moves every row in a table, and reads the index too, while a non-clustering reorg only moves the rows it needs to.

Between that and some other details I’ve learned recently, I am reconsidering my strategy of many years to reorg every table that doesn’t have a clustering index on it’s primary key. I think I’ll still aim for some clustering, but based on things I’ve learned about index efficiency, I think it’ll be more selective, and focused on lower cardinality columns.

HADR Backpressure

I have a number of clients who see HADR go into a “CONGESTED” state during reorgs. And indeed, online reorgs can generate an astounding number of log files. Every one of those log entries must be copied over the HADR link. This can slow down your reorg performance if you’re using NEARSYNC (or SYNC). At the IDUG NA 2013 conference, I actually got two ideas on how to deal with this problem.

First, consider making the LOGBUFSZ larger on the Standby than on the Primary. I’ve always tried to keep my database settings the same between primary and standby, but this actually makes sense to me – more memory available for logs on the standby with NEARSYNC doesn’t affect recoverablity unless both servers fail at exactly the same time, and it does give a bit more room.

Second, there is a new parameter available in DB2 10.1 called HADR_SPOOL_LIMIT. It is in the DB config, and if set to a non-zero value, it allows logs on the standby to spool to disk – so they are still exeternalized, and your recoverablility is not impaired. The one drawback is that your actual failover time might be longer, because if there were a lot of logs spooled to disk, the standby would have to rollforward through all of them to come up. I wouldn’t like to be spooling all the time, but it sounds like a great solution for spikes like online reorgs can cause. I have not actually used this feature, since I’ve been unable to get my hands on 10.1 yet – hopefully WebSphere Commerce will certify 10.1 this year so I can play with it.

Offline Reorg Trick

Did you know you can do an offline reorg using ADMIN_MOVE_TABLE? And with 10.1.2, DB2 will support ADMIN_MOVE_TABLE for tables with RI. I’d have to investigate how online ADMIN_MOVE_TABLE really is(seems like it would make sense to have a table lock for the switch), but it is intriguing.

It was really an awesome week. I learned so much, and I think I did well on my first time presenting. We’ll see when the surveys come back. If you’re not familiar with how they do things, IDUG tries to choose proven speakers. This means that if you have bad reviews, you’re less likely to be approved to present next year. So if you were there, go do all your reviews ASAP to give speakers their props.

This is a brain dump. It’s the stuff that really stood out to me in each session. It does no justice whatsoever to the depth and quality of the speakers’ presentations, and does not cover a bunch of stuff I already know. Please comment if you see any inaccuracies or have any comments on the sessions or my details of them.

Monday – ED4: DB2 for LUW Top Gun Performance Workshop – Scott Hayes and Martin Hubel

There’s a lot to learn here. I’m pretty good with performance tuning, and took Scott’s pre-conference workshop in 2005. It’s a whole other experience to take it now as a more experienced DBA. Many things I will use. I highly recommend it if you get the chance.

Tuesday – Keynote

The one and only session where I took no notes.

Tuesday – S02: DB2 for Linux, Unix, and Windows: Recent Enhancements and Trends – Matt Huras

I learn from Matt every time I see him speak. I got some interesting details about BLU and some interesting clues on what is coming down the road. My notes include:

• BLU is a DMS tablespace type
• Compression in 10.1 gains additional efficiency through the use of “Approximate Huffman Encoding” – my understanding of that is that a smaller value is chosen for the most frequently compressed values
• Encrypted data does not need to be uncomressed to be evaluated
• SIMD – Single Instruction, Multiple Data – in 10.5 DB2 can, for example, compare 4 values at once to see if they match a literal value
• In 10.5, DB2 can dedicate specific CPUs to only look at memory for what they’re working on
• A BLU table is specified by the “ORGANIZE BY COLUMN” keywords in the create table statement
• A “synopsis” table is maintained alongside the actual columns of data for BLU tables. This is then used in some way for data elimination. This table is always there, and is changed atomically.
• For analytics workloads, there is a registry variable – DB2_WORKLOAD that should be set to ANALYTICS
• You can convert tables from normal to BLU
• The create table statement will allow you to specify “ORGANIZE BY ROW” to explicitly specify a regular table
• LODADing into BLU tables performs comparably to LOADing into traditional tables with indexes
• BLU is not appropriate for high inserts/deletes because they have to touch more pages to do an insert than with traditional tables
• IBM may or may not be including the ability to index on an expression in some future (post 10.5) release of DB2

Tuesday – E01: Advanced Query Tuning with IBM Data Studio for Developers – Tony Andrews

• Ways of reducing I/O:
  • Index
  • System Tuning
  • BP Tuning
  • Early Elimination
  • Program and SQL Tuning
• Residual predicates are ones that are applied after the data is retrieved and are therefore generally expensive
• IXSCAN is displayed in an explain plan whether or not it is truly a scan – it could be just index access.

Tuesday – C02: DB2 10 LUW – Securely Hiding Behind the Mask – Rebecca Bond

• With RCAC, we can do permissions for rows and masks for columns
• This is also called FGAC or Fine-Grained Access Control
• Column masking is the topic of this presentation
• There is no need to mask all data – we can create a template and do partial masking
• This can be included in db2look with the right flags
• UDFs and Triggers can be secured
• XML and LOBS cannot be masked
• Catalog tables cannot be masked
• Nicknames cannot be masked
• Changes to masking invalidate the Package Cache
• Change control of masking is critical
• Must test masking changes very thoroughly to ensure they are providing the desired result
• System tables for this stuff: SYSCAT.CONTROLS, SYSCAT.CONTROLDEP
• Masking isn’t fool proof – it only covers when the data in a row is VIEWED – for example, a count(*) where query would still give information on the true values

Tuesday – D03: DB2 Busines Continuity Features – Dale Mcinnis

I always learn from Dale, too, when he speaks. I wish I could have attended his other session, but it conflicted with something I couldn’t miss.

• Top causes of business interruption:
  • Human error (70-75%, mitigate this with documentation)
  • Planned maintenance
  • Disaster
  • Component failure
• Recent Paradigm shift from failover to active/active
• Geographic dispersion
• Regular maintenance is simply required – you cannot just ignore it in favor of higher uptime
• Application maintenance hassles:
• Renaming of columns
• New versions of stored procedures
• Websphere Commerce does not support:
  • Range partitioned tables
  • Insert time clustering (ITC) tables
• IBM is working harder to get the apps they write anyway to support more recent features quickly. It sounds like we may get Commerce certifying on DB2 10 this year
• Dale published a great whitepaper on DB2 integration with dedup devices http://www.ibm.com/developerworks/data/library/techarticle/dm-1302db2deduplication/index.html
• TSM dedup can run on the client, which saves network traffic
• IBM is working on support for online schema changes
• IBM’s not necessarily scientific polling inds that 75% of OLTP clients, whether on DB2 or Oracle, are doing nothing for DR, only HA
• Xcoto has been renamed to Unity!
• DR technologies are generally:
  • Physical replication
  • Logical replication
  • Storage replication
  • Single cluster across multiple locations
• Not all data must be replicated across sites – such as guest carts
• HADR modes for HA
  • Sync
  • Nearsync
• HADR modes for DR:
  • Async
  • Super Async
• IBM is considering an admin mode on the target for Q replication to turn off things like Triggers

Tuesday – H04: Best Practices: Upgrade to DB2 LUW 10 – Melanie Stopfer

Wow, Melanie really packs the information in. Worth downloading the slides for any presentation she gives.

• Upgrade servers first, clients later
• Use db2prereqcheck
• After upgrade, remember to:
  • Uninstall Firefox browser
  • Re-apply licenses
  • db2fs (optional)
  • db2val -a
  • db2ls
• db2ckupgrade
• With 10.1, type 1 indexes are no longer allowed. If you are coming from 9.7, you already do not have them, so you can specify -not1 to skip this step and speed it up
• Tempspace must be 2X SYSCAT space
• SYSCAT space must be 50% empty
• The upgrade is done as a single UOW, so be careful of log space
• Query Patroller has been discontinued
• HADR MUST be stopped
• Run db2ckbkup and db2cklogs after backup and before upgrade
• If you are really desperate, technically only the last delta backup must be offline – the full could be online
• Always verify the upgrade with db2level
• There is a url for getting any needed bind packages
• Upgrade does runstats on SYSCAT tables only – may need a runstats for other tables
• db2tdbmgr to upgrade tools (things in the SYSTOOLS tablespace)
• db2exmig can be used to upgrade the explain tables – if you have data in them you want to keep
• Event monitor tables must be upgraded too, or event monitors won’t work – both formatted and unformatted tables
• To undo an upgrade, you have to drop and re-create the instance – that’s why it is critical to have all settings documented
• Always double-check EVERY config after upgrade – Diff is your friend
• Consider using your HADR standby for backout if your upgrade on the primary fails before you have also done the standby
• Copy all log files and .mig files in the active log path before connection – they are deleted on first connect
• JDBC type 2 drivers are discontinued in DB2 10.1
• Use Data Studio 3.1.1 or higher to take advantage of all 10.1 features
• Default for detailed statistics is now sampled, (default for non-detailed statistics remains full)
• Schema specification is no longer required for Runstats
• Log records are larger – you’ll need to increase your active log space by 10-15%, and also LOGBUFSZ
• Consider using these new features:
  • Adaptive Compression
  • Log Archive Compression
  • Multi-temperature storage
  • Insert-Time-Clustered tables
  • Partitioned tables
  • db2move supports parallelism
  • Change history
• The Redbook on Unleasing DB2 10 for LUW is good.

Wednesday – G05: PureData System for Transactions Overview – James Cho

This session was a bit heavy on the marketing for me, but I wanted to have some details in case one of my clients buys one of these.

• You can create your only patterns
• Includes pureScale with 2 CFs
• Includes TSM
• Includes some GUI facility for taking backups
• Includes OPM
• Full rack is bigger than Watson
• Includes SSD
• Cannot split tablespaces and control where their storage lands
• Does not include Recovery Expert

Wednesday – C06: DB2 on the Cloud: Experiences from the Trenches – Leon Katsnelson

This session was a bit focused on Amazon costs and options.

• http://bigdatauniversity.com/ – this session is about how it is hosted in the cloud
• Cloud = delivery (on-demand)
• Infrastructure as a service
• Platform as a service
• Software as a service

Wednesday – E07: Battle-Proven SQL Tuning Techniques – Phill Gunning

• Range delimiting predicates are applied earlier than many other predicates – that’s why you want them
• Jump scan shows up in explain as a jump predicate
• Jump Scans are counted in MON_GET_INDEX
• Interesting way to re-write a not exists presented in the session – might look it up on the slides
• DB2_ANTIJOIN can improve performance of not exists
  • Default = NO
  • Yes = not exists
  • Extend = not exists and not in
• ‘IN’ is not always evil – it can sometimes be useful to encourage a semi-join

Wednesday – TNO: Nuts and Bolts for LUW Performance – Beulke, Gross, Stopfer

• If using TRACKMOD=ON, then any change to a LOB will cause the entire tablespace to be backed up in full, which may lead to larger backups
• LOBs should be in separate tablespace
• SLOB = Small LOB
• STMM sleeps for 60 seconds, does work, then sleeps again
• To improve reorg performance:
  1. Commit as frequently as possible, since reorg will wait forever for every single row – it does not respect LOCKTIMEOUT
  2. Clustering reorgs do a forward scan while non-clustering reorgs do a backward scan – so non-clustering reorgs do half the work or less as clustering reorgs
• Consider a script to check active log space and pause reorgs if it is full

Thursday – C08: The 10 Commandments of Supporting E-Commerce Databases – ME!

Well, this was my session, so I’ll refrain from a review. I think it went well.

Thursday – C09: Index Jump Scans and Updated rules for optimal DB2 LUW Index Design – Scott Hayes

This one was interesting. Basically, he had trouble getting jump scans to even happen, and in nearly all cases saw more logical reads on 10.1 than on 9.7. He later stated that execution time was about the same or better on 10.1, though. Good tips for using db2batch in the presentation.

Thursday – C10: Wowza! Great Tips I’ve Learned about DB2 LUW 10.1 – Melanie Stopfer

Melanie is one of my favorite people on the planet.

• MON_GET_TRANSACTION_LOG (love those MON_GET table functions!)
• Make your log buffer on the standby larger to avoid back pressure
• HADR_SPOOL_LIMIT (default=0) – still guarantees integrity, but allows logs on standby to spool out to disk
• ecu_buf_pct
• Disadvantage – this can extend takeover time
• ADMIN_MOVE_TABLE can be used for and offline reorg, though you can’t use LONGLOBDATA in that reorg
• If using auotmatic storage, set overhead at the stogroup level
• (9.7) – db2pd -tablespaces has TRACKMOD stats (or MON_GET_TABLESPACE)
• Paths changed (due to looping PureScale in):
  • Database Directory
  • Log
  • Archive Log
  • Backup file name

Thursday – C11: A db2 LUW Fitness Plan – Brian Fairchild

Brian is brilliant on reorgs, and has good stuff to say.

• MON_GET_TABLESPACE will show you if there are pages empty below the High Water Mark
• Overflow accesses in the biggest reorg indicator
• Great slides on reasons to reorg by formula – what each formula means
• Value compression – can be used without specific license

Thursday – D12: An Index’s Guide to the Universe – Brad Price

• Brad’s goal for OLTP is to have 5 indexes or less per table
• ADVISE_INDEX can store potential indexes
• When setting the explain mode, you can use “recommend indexes” to populate ADVISE_INDEX
• There’s a use_index column in ADVISE_INDEXES that can be tweaked

Thursday – DB2 for LUW Panel

• Index reorgs done with “cleanup only” are fully, 100% online
• Full index reorgs take a Z lock during the switch phase
• 10.1 improves index prefetching
• Allowing truncation during reorg gets an S lock at the table level
• Read-ahead prefetching may cause the increased Logical Reads that were presented in Scott’s presentation
• One should look at the execution time, not just he number of logical reads
• LASTUSED is updated for indexes during a LOAD
• PureScale requires AST, so you may have to use db2move or something to move data in from other tablespace types, if converting

Friday – G13: Customer Success Story: Using DBI Software tools for IBM DB2 LUW

This one suprised me with quality of content and speaker – Kohli has some skills!

• Statistical views may be particularly useful when you have ranges in your where clause
• Kohli presented some interesting techniques to deal with poor performing likes on ‘%string’ and ‘%string%’. They’re complicated, and I cannot do them justice here.
• Interesting techniques for generating test data
• Great query on slide 47 for Foreign Key creation

Friday – C14: How to Maximize Performance Benefits from DB2 10.1 & 10.1 BLU Intro – Michael Kwok

Another surprisingly good one – interesting speaker and great content:

• Showed 10.1 performance benchmarks
• ESE OLTP was about the same as 9.7
• ESE Scalability was better than 9.7
• Better performance on 10.1 was when leveraging multiple cores
• When using jump scans, bigger gaps = better performance
• 10.1 is more proactive in prefetching – look at sync vs. async index reads
• 10.1 is faster for poorly clustered data
• Need for reorgs is lower, and IBM’s strategic direction seems to be reducing the need for reorg rather than improving reorg
• Path length for utilities reduced in 10.1
• 10.1 encourages anti-join
• exfmt in 10.1 recommends indexes to support zig-zag join

Friday – C15: Data Masking: Protecting both Production and Test Environments – Wallid Rjaibi

Man, was I tired by the time I got to this presentation.

• Verizon produces a comprehensive data security report
• A trusted context includes additional capabilities like switching the user
• Was 6.1 and cognos support trusted context – need to find out if Commerce does
• When using column masking, you can call a UDF, but not a Stored Procedure
• Guardium’s value add is if you’re working with multiple RDBMses
• Consider the same mask on related columns
• Can use Optim’s data masking UDFs with column masks

I have done justice to nothing I saw. It was a fabulous week of drinking from a firehose of knowledge and hanging out with my DB2 geek friends. And it rained all week, so I didn’t feel bad about being inside and occupied from basically 7 am to 11 pm every day. If you haven’t been to an IDUG conference, GO – it is totally worth it.

First off, I love to meet my readers. It totally makes my day to hear that you’re reading and that something in my blog has helped you. So find me and introduce yourself – let’s talk.

Second, I need pictures for the blog – of me with you, of me presenting or just your favorite moments. So snap them and send them on over. What I’d love to find somewhere in the week is a new photo I can use on the main page of the blog!

The single most exciting thing for me this week, is, of course, my own presentation. This is my very first time presenting at IDUG. I’ll be presenting Thursday morning at 8AM, so don’t pick that day to sleep in. My presentation is geared towards beginning and intermediate levels, and is called “The 10 Commandments of Supporting E-Commerce Databases”. It will be in “Grand Sierra H&I”, which I’m sure I’ll understand better once I’ve checked in on Monday morning.

Other sessions I’m particulary excited to see – I list them by person because that’s how I look for them first:

• Melanie Stopfer
• 4/30 4:30PM H04 Best Practices: Upgrade to DB2 LUW 10
• 5/1 3:30PM SIG Nuts and bolts of DB Performance
• 5/2 1:00PM C10 Wowza! Great Tips I’ve learned about DB2 LUW 10
• Scott Hayes – 5/2 9:15 C09 – Index Jump Scans and Updated Rules for Optimal DB2 LUW Index Design
• Rebecca Bond – 4/30 2:00 C02 – DB2 10 LUW – Securely Hiding Behind the Mask
• Brian Fairchild – 5/2 2:15 C11 – A DB2 LUW Fitness Plan

I’ll be in one of the educational seminars on Monday, and will generally be at most of the events in the evenings. I’m going to Melanie Stopfer’s Dine around on Thursday night, because she’s one of my favorite people on the plannet.

I’ll aslo be tweeting, so follow me @ember_crooks if you haven’t already.

I love the inspiration and ideas that I get from IDUG, and love hanging out with DB2 people. I’m so excited for this week!

[This article is based off the "Attack of the Blob" DB2 Night Show webcast held on Friday April 26, 2013 at 11am EST.]

I would love to say that the biggest technical challenge of my career was some massive data warehouse or an OLTP database with an insane number of transactions per second. Heck, I’ve even had the distinction of working on the second largest DB2 database in the world (at that time).

No, the biggest technical challenge of my career was a three week ordeal on a small 35G database centered on a 5k row table that had data purged nightly because the data wasn’t important the next day. This was also a case where power didn’t mean speed—we were on an oversized server (4 CPU, 1.2 T Disk, 30G Memory) built for massive growth. I had enough juice to light up Atlanta, but really only needed to power a flashlight.

This table, with the help of other mistuned areas, would snarl the database so badly that the whole application would grind to a halt. This would cause 1600 locations and 10k+ teammates to light up our technical support lines like a Christmas tree.

As much as I would love to blame the SAN, or networking, or anyone other than me, the problem was in my back yard. How I discovered the issue is an article in itself. However, I should give a quick nod to Scott Hayes and DBI. My performance analysis method came from Scott’s “DB2 for LUW Performance Workshop” that I took as a pre-conference seminar at the IDUG Conference in 2012. He has a few blogs on the topic located on the DBI website under “Performance How To’s” . A good place to start are his “Elephants and Mosquitoes” entries.

It ultimately came down to a few areas:

1. The database architecture was database agnostic. It wasn’t coded for DB2, or Oracle, or SQL Server. So DB2 features to improve speed were not implemented.
2. When the DB was stood up years ago, we dumped everything into the same tablespaces and bufferpools. That’s right, the “everyone in the pool” mentality. We did not tune for hot tables or important areas. In other words, we shot ourselves in the foot.
3. Blobs. These things are evil. I had never really tuned for this data type. I had to learn how to quickly tame this beast.

For this article, I am going to focus on handling BLOBS. There was some basic Database Tuning 101 applied—implementing DB2 Environment settings for OLTP best practice, identifying hot tables and breaking them out to their own tablespaces and bufferpools, and tweaking the LOGBUFSZ settings—but the largest lift came from taming the evil BLOB that was slowing down my database.

What is a Blob … besides big, ugly, and slow?

BLOB is short for Binary Large Object which can be many things—for example, Documents, Voice Data, Pictures, or mixed media.

What makes BLOBs unique is that BLOB data is not stored in a row. It’s stored externally to the table with a pointer giving the location of the object. See the figure below:

Blobs are not stored within a row because it can be impractical, sometimes impossible, to fit the BLOB in row without blowing out row length limits (32k). See below for an example of why BLOBS are not in row natively. [Note: Both graphics taken directly from IBM Info Center]

So, why do we care if we are not storing data in row? BLOB data types are a concern to DBA’s because they spin disk. If data is not stored within a row, it can’t be brought up into memory (bufferpool), which means I/O overhead. I/O overhead, spinning disk, physical reads—they all are slow compared to reading in memory. Is this a big deal? Not always. For a data warehouse, where a query can run minutes or hours, this isn’t as crippling as it sounds; but from a transaction processing point of view, where we measure things in milliseconds, time to spin disk can be an eternity.

Symptoms—How did I/O affect the Database?

To give you an example of how blobs affect I/O, let’s take a look at some metrics from my database in a 20 minute timeframe.

Direct reads					=	233754
Direct writes					=	174318
							(8715 Minute)
Direct write elapsed time (ms)			=	24593
Update/Insert/Delete statements executed	=	93176
Log pages written				=	88708
Number write log IOs				=	78827

Metrics are all about perspective. Is writing to disk 174k+ times in 20 minutes excessive? Without data to compare to, it’s hard to tell. But common sense tells us we can assume at the very least the application is a little chatty when it comes to direct writes.

The other piece of the puzzle is disk utilization. When the DB was humming, data disks would go to 30-40% utilized while DB2 Log disk ran at 50-60%. When the situation started to get a little dicey, was when data disks were up and holding anywhere between 60-80% and DB2 Log disks were at 90%+. Data was moving, and moving at a good clip, but the response time wasn’t fast enough for the application.

Why was the DB2 Log disk consistently higher? Blobs log heavily by nature. So not only are you off spinning disk to access the blob, but you are putting pressure on your logging disk as well.

It seems a lost cause, doesn’t it? Blobs by nature spin disk, the application accesses this table on almost every transaction, and we can’t get DB2 to lift this into memory. It seems as if you are between a rock and a hard place.

What was the vendor’s solution? Isolate the table into its own tablespace and its own LUN of disk. Better yet, if you are in real pain, make that a solid state disk. This is what other clients have had to do in the past, and to be honest, at the time, this seemed like the only solution. In the end, it turned out to be “Plan B”—Let’s try a Hail Mary Pass and see what turns up.

Domesticating your Blob

Stop the Chit Chat

If your DB went down, do you really need to recover the data from that BLOB? Well, in our case, that was a resounding no. Not only did we not need the data, the table was purged each night for performance reasons.

So let me introduce the NOT LOGGED clause:

For example:
CREATE TABLE EMPLOYEE
    (EMPNO     CHAR(6)     NOT NULL PRIMARY KEY,
    FIRSTNME  VARCHAR(12) NOT NULL,
    MIDINIT   CHAR(1)     NOT NULL WITH DEFAULT,
    LASTNAME  VARCHAR(15) NOT NULL,
    WORKDEPT  CHAR(3),
    PHONENO   CHAR(4),
    PHOTO     BLOB(10M)   NOT NULL  NOT LOGGED)
IN RESOURCE

So what happens in a recovery scenario? Well, if you RESTORE and ROLLFORWARD, the blob data will be replaced by binary zeros.

That isn’t to say that there is no record of what is going on. DB2 uses “shadowing,” a recovery technique where current storage pages are never overwritten. Old unmodified pages are kept as “shadow copies” that are discarded when they are no longer needed for rollback. So you lose recovery, but not the ability to rollback a transaction stopped in mid-flight.

[As a side note: Rule of thumb is to use this feature on LOBS over 10MB. LOBS over 1G can’t be logged.]

This feature by itself dropped DB2LOG disk utilization by 10%. This bought us enough time to focus on implementing best practices and tackling the larger problem of how the database was using blobs.

Break Rule Number 1

Remember what I said about DB2 not being able to keep blobs in a row? It was impractical and sometimes impossible?

I lied. Well, not lied as much as didn’t tell all the truth.

Natively, lobs are not held in row. I mentioned this is because blobs are usually much bigger than 32k and would blow out maximum row size; but this isn’t always the case. If your blob can fit into the row, you can explicitly tell DB2 to treat it that way using DB2’s INLINE feature.

What is the benefit? Once the blob is held in row, it CAN be pulled into bufferpool and be accessed in memory. You remove the need for physical reads. Don’t forget, the row (including blob) is now eligible for row compression!

To do this you need three commands.

Step 1:

Use the ADMIN_EST_INLINE_LENGTH command to see if your blob can fit into a normal size row.

Command:
ADMIN_EST_INLINE_LENGTH--(--column-name--)-------------->

Results:

• (number): Size needed in row to be brought inline
• NULL: Inputs are NULL
• -1: Cannot be inlined due to length
• -2: Inserted before 9.7, can’t be evaluated

Step 2:

Alter the table using the INLINE command.

Command:
ALTER TABLE (tablename) ALTER COLUMN (colname) SET INLINE LENGTH 
(Where value is max length from ADMIN_EST_INLINE_LENGTH evaluation)

Keep in mind, you will need to respect the max row length based off your tablespace settings. You will also need to add 4 for INLINE overhead.[Note: Graphic taken from IBM Info Center]

Step 3:

REORG the table using LONGLOB feature. For the INLINE feature to take effect, you must UPDATE the row or run a REORG using the LONGLOB parameter. You won’t need to do this every time as it is only used for the conversion process. There usually isn’t a real benefit unless there are XML columns in your table; in that case, some space may be reclaimed. In a normal REORG this would be time consuming and doesn’t improve clustering.

Finally, as you step back and admire your work, know that not all blobs will inline. In our specific case, close to 20-30% of our blobs could not be brought into rows. This is OK: operating 70%-80% of the time in memory is still a huge improvement. It is acceptable to spin disk 20-30% of the time, in our scenario.

[In our specific scenario, we had this table in its own tablespace and bufferpool after the redesign. Not only did we turn off FILESYSTEM CACHING (see below), the bufferpool was large enough to pull the bulk of the table in memory].

Put on Afterburners

Lifting your blobs into memory should give you an enormous lift. However, there is one more tuning tidbit you can implement: Know when and when not to use FILESYSTEM CACHING.

Using FILESYSTEM CACHING or NO FILESYSTEM CACHING commands allow you to enable/disable non-buffered I/O on specific tablespaces while avoiding dependency on physical layout. In an AIX environment, you can cause double buffering at a DB2 level when the O/S is already buffering for you.

When dealing with BLOBs natively, you WANT filesystem caching enabled. DB2 needs this buffering to improve performance.

When you do not have BLOBs, you DO NOT want filesystem caching enabled.

In our specific scenario, we had blob and non-blob tables mixed in the same tablespaces and bufferpools. This was a huge performance drag because only 15% of our 400+ tables needed filesystem caching. The other 85% were doing double work.

If you are thinking ahead and are able to separate your blobs into their own tablespaces and bufferpools you can turn on FILESYSTEM CACHING as needed for blob tables.

Note: If you now have INLINED blobs, you can disable filesystem caching. If the bulk of blobs are in row, you will get more lift by turning this feature off.

Usain Bolt got nothing on us.

After three weeks of research, analysis, testing, and redesign we were ready to go live and implement our new solution. Would the new approach address our needs or was I really going to need to speak to management and advocate for solid state drives?

Here is a peek of what we saw, comparing apples to apples:

				Before		After
Direct reads			= 233754	73471	68% Improvement
Direct writes			= 174318	14008	91% Improvement
Direct write requests		= 16213	        262	98% Improvement
Direct write elapsed time (ms)	= 24593	        1622	93% Improvement
Log pages written		= 88708	        53642	Overall work drops
Log write time (sec.ns)		= 89	   	76	Overall work drops
Number write log IOs		= 78827	        48354	Overall work drops

And what was the feedback from load testing?

• Total Transactions Processed increased by 9%
• 20% of Total Transactions improved by 15-16%
• Less than 1% (Quote “Very Very Few”) of transactions slowed down slightly

More detail from production:

• SQL exceeding 2 seconds on basic FETCH drops from thousands to less than 50
• 97.93% drop – Execution time for UPDATE statements per 100 executions against problem table (0.436 seconds to 0.009 seconds)
• 81.7% drop – Execution time for SELECT statements per 100 executions against problem table (0.656 seconds to 0.120 seconds)
• ZERO Lock Escalations per 1000 transactions
• Write Rate (how often we have to spin disk) – Drops 60 IOPS (per volume) to 18 (70% Improvement)
• Write Transfer Size – Increases from 8k to 18k per IO Operation (2.25x Improvement)
• Read Rate (how often we have to spin disk) – Drops from 15 IOPS (per volume) to 12.
• Read Transfer Size – Increases from 4.29 to 4.94 per IO Operation
• 10-20% drop in disk utilization

Every superhero needs a sidekick

I wasn’t kidding when I said that this was the biggest challenge of my career. Nor was I exaggerating in my DB2 Night Show webcast when I said I pulled in a small army to help. I may speak as an expert in this blog, but I am not an expert. An expert works almost independently—I solved this with a small army: Prashant Sogarwal, who hands down has to be one of the top DBA’s on the east coast, helped design the solution and acted as a mentor. Stephanie Knight-Baker spent hours in data analysis and solution coding. Scott Hayes and his performance and tuning techniques helped narrow down where the problem was; Scott’s expertise in tuning was also invaluable. Ember was always available to bounce ideas off of and encouraged me more than once.

This leads me to a point I want to make as a parting thought. Where would I have been without my friends, teammates, and contacts? All of these contacts were made through my volunteerism through the DB2 User Group—IDUG (International DB2 User Group). My analysis techniques came from conference sessions and my network of help came from other members.

You can’t save the day on your own. You can’t stand up in front of an executive, with an “S” on your chest and the wind at your back, without a team to support you. So I encourage you to not only apply the techniques and analysis in this article, but to go out and actively seek out that small team you go to in an emergency. Because in the end it will be your peers, friends, and mentors that pull you out of the fire. Or in this case, beat back the giant blob trying to eat your database one I/O at a time.

Further Reading from IBM Info Center:

1. Large objects (LOBs)
2. Large object (LOB) column considerations
3. Inline LOBs improve performance
4. ADMIN_EST_INLINE_LENGTH function – Estimate length required to inline data
5. Storing LOBs inline in table rows – IBM DB2 9.7 for Linux, UNIX, and Windows
6. Table spaces without file system caching

Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) gmail (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: mkrafick

Mike’s blog posts include:

10 Minute Triage: Assessing Problems Quickly (Part I)

10 Minute Triage: Assessing Problems Quickly (Part II)

Now, now you two play nice … DB2 and HACMP failover
Technical Conference – It’s a skill builder, not a trip to Vegas.
Why won’t you just die?! (Cleaning DB2 Process in Memory)

Snarled.

Non-functional.

Zombied (think the “Walking Dead” TV show).

Dorked.

Sometimes there is a memory leak, instance level crash, trap, or OS issue. DB2 just goes into a bad state and you have to bring down DB2 as gently as you can.

And if not, hit it with a bat. Whatever works.

Even when DB2 comes down properly, you need to be diligent to make sure EVERYTHING has stopped before some more serious work happens, like a DB2 upgrade. You need ZERO DB2 processes in memory.

[Side Note: Ember has a really good article on stopping DB2 in preparation for an upgrade. Check out the article here - Link]

There is a level of escalation when DB2 is snarled. You start with deactivating the database which allows all things to come to a natural end. However, if step two fails, move to step 3, if step 3 fails, move to the big guns in step four. If you end up hitting step five, you know things are not good.

1. Deactivate Database
2. DB2STOP
3. DB2STOP force
4. DB2_KILL
5. Server Reboot

There is always real risk with a DB2_KILL of instance or database corruption. It is always a last resort.

Let’s say DB2 is down one way or another. And we want to be sure all DB2 processes are out of memory and the server isn’t chewing on something that could be a zombie. How do we accomplish that in an AIX/UNIX/LINUX environment?

Zombie Hunting

IPCS

From the DB2 Info Center: “The ipcs command writes to the standard output information about active interprocess communication facilities. If you do not specify any flags, the ipcs command writes information in a short form about currently active message queues, shared memory segments, semaphores, remote queues, and local queue headers”.

To use this in our environment you can issue the following.

Command:
ipcs | grep db2

This returns something similar to:
q 154140691 0xffffffff -Rrw------- INSTID GROUPNM
q 611319866 0xffffffff -Rrw------- INSTID GROUPNM
m 708837404 0x7e074e61 --rw------- INSTID GROUPNM
m 577765405 0xffffffff --rw------- INSTID GROUPNM
s 16777236  0x7e074e74 --ra-ra-ra- FENCEID GROUPNM

(Where ‘q’ is Message Queue, ‘m’ is Shared Memory Segment, and ‘s’ is a semaphore)

In short, all items returned are DB2 processes still in memory. If you brought DB2 down cleanly there may be very few or no processes. If you hit DB2 with a bat (db2_kill), you may have many.

To clean your memory of DB2 processes you can issue two different commands.

1. IPCLEAN
   Issuing “ipclean” by itself will act as a catch-all. Essentially any processes in memory for that ID are terminated, or a terminate is attempted. If you came down cleanly with a deactivate and DB2STOP, IPCLEAN should be sufficient. DB2_KILL issues an ipclean as a part of its processing.

2. IPCRM
   From the DB2 Info Center: “Removes message queue, semaphore set, or shared memory identifiers”.

   Command:
       Ipcrm 	-q 	ID
       Ipcrm 	-m 	ID
       Ipcrm 	-s 	ID

   Where q,m,s matches what was returned in IPCS command. The ID used is listed in the first column of IPCS.

   Example:

   ipcrm -q 154140691 

For more details on the IPCS and IPCRM commands, head over to the DB2 Info Center at http://tinyurl.com/dyra4m2 .

Use Caution

Let’s admit it; we are not System Administrators (SA). Use these commands with caution as they can cause more harm than good. Run them as the DB2 instance owner. Use common sense, and if you are over your head, reach out to your SA. They will be happy to help. Getting them involved could prevent a reboot.

I don’t know about you, but I bribe my SA for help with coffee. Don’t make a newbie mistake and try to bribe with that weak stuff in the break room. If the SA grumbles and snarls, you used cheap coffee. Proceed to Starbucks quickly.

Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) gmail (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: mkrafick

Mike’s blog posts include:

10 Minute Triage: Assessing Problems Quickly (Part I)

10 Minute Triage: Assessing Problems Quickly (Part II)

Now, now you two play nice … DB2 and HACMP failover
Technical Conference – It’s a skill builder, not a trip to Vegas.