IDUG NA 2013 Brain Dump

Wait, don’t avoid reading this because you didn’t go to the conference. There is still valuable stuff here. In fact, it is even more valuable for those who did not go. Last year, I posted my brain dump by basically re-writing all of my paper notes. This year, I’m going to try to make it a bit more organized.

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.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home