46

DB2 for Linux/Unix/Windows (LUW) is becoming more popular every day. Many IBM customers who run DB2 for z/OS are using DB2 LUW also, or will do so in the next year. For cost saving and optimization reasons many companies, who have both products, have decided that they will combine DBA activities to be done by a single group of DBA’s. People who have been doing DB2 for z/OS for a (very) long time are suddenly facing a “new” DB2. DB2 = DB2 is a simple assumption which is only partly true. So, some things are similar, and other things are completely different. Klaas Brant has been using both products for a very long time and can explain to you were the major differences are and how to avoid some pitfalls in DB2 LUW for DB2 for z/OS DBA’s. The seminar assumes you have a good knowledge of DB2 for z/OS and its components and have no or limited knowledge of DB2 for LUW.

Outline
Architectural comparison
Architectural overview
Subsystem vs. Instance comparison
Access methods
Similar and different terms
Storage Management
How space is allocated
How space is managed
How performance is optimized
Object Comparisons
Databases
Tablespaces
Container
Tables
Indexes
Bufferpools
Administrative Differences
Logging
Backup/Recovery
The differences in optimizer
Monitoring options
Autonomic Computing
Utilities

Achieving maximum performance from DB2 requires a different approach and stricter methodologies than simply getting a DB2 application to perform well. In today’s web and SOA environments the rules have changed and we must find every tuning knob in order to get another transaction through the system. This seminar focuses on specific issues of system design, physical object design, application design and tuning methods used to achieve high performance. Topics such as table and index design, large tables and NPSI’s, DPSIs, pushing logic into SQL, control and concurrency, enterprise wide performance topics are examined for OLTP, batch, and data warehousing. Topics such as buffer pool design and utilization, concurrency and locking, DB2 compression, EDM pool sizing, RID pool sizing, SORT work tuning, and parallelism are examined, and it is shown how poor memory tuning directly effects application performance. Focus is on the complete set of design points of DB2, and how they will affect performance of your applications. Discover why many old guidelines no longer apply, how old standards can actually hurt us and learn the approaches to systems management and tuning for the DB2 environment. This course gives new ideas on achieving the new demands placed on us in today’s high volume/high availability environments. The rules have changed and certainly in DB2 9 there are many more features to take advantage of and to be careful with!

The case study examples and stories told are from actual performance audits and design/development efforts.

All topics and examples are current with DB2 z/OS 10 and we will cover migration considerations for 9 and 10.

System Overview
Some Internals For Discussion
Discussion of All Performance Opportunities
Physical Database Design Issues
Table Design
Non-attribute Columns
Code Tables and Reference Tables
Materialized Query Tables
Denormalization
Column Placement
Reordered Record Format
Large Tables and their indexes
Partitioning for size and performance
Universal Table spaces
Clone Tables
Index Design
Clustering versus non-clustering indexes
Append processing
Partitioning index design
Referential integrity and index design
NPSIs and Pieces
DPSIs
Hash access
Temporal Tables
Compression (tablespace and index)
Designing for 24×7
Large table case study
Concurrency
Locking issues
Coding for minimal locking overhead
Impacts of row level locking
Lock avoidance
Lock monitoring – proactive vs reactive
Bufferpools
Buffer manager overview
Impacts on application performance
Buffer pool parameters
Bufferpool use, thresholds, and performance
Sequential prefetch impact
List prefetch impact
Checkpoint impacts
Virtual pool design strategies
RID Pool
Usage
List prefetch impact
Correct sizing
Performance problems
Application impacts
Statistics detail/failure types
Sort Pool
Usage
Correct sizing/workfiles
Potential performance problems
Statistics detail
Application performance detail
EDM Pool
Correct sizing
Impacts of new features
Monitoring its efficiency
EDM pool and I/O activity
Dynamic SQL caching
Application Design Issues
Load Reduction By Application Transaction Re-design
Uncommitted Read
Skipping Locked Data
Currently Committed Data
RELEASE(DEALLOCATE)
DEGREE(ANY)
Plan Stability
Parallelism
Query CPU Parallelism
How the Degree is determined
Buffer Pool Impacts on Parallelism
Basic Trace Records For Parallelism
IFCID 221 &222
SQL Performance Issues
Code for Reduced CPU
Pushing Logic into the SQL
Binding and Reoptimization Issues
Filtering Data
Dynamic SQL Cache Usage
Explain Tables and best usage
OLTP
SQL Transaction Cost
Sequential Numbering Issues
Batch
Reducing CPU and I/O
Checkpoint/Restart
Commits and Savepoints
Heuristic control tables
Browsing Performance
Scrollable Cursors
Mass updates, deletes and inserts
Final Guidelines

Every release has its buzz words; sometimes those features are truly amazing, sometimes not so much. In a world of blogs and twitter, this is my take on “what’s hot and what’s not” within DB2 V9 and DB2 V10. We’ll be talking about XML, not logged, native stored procedures, hashing, new security features and much more.

For DB2 the log is crucial, critical, and vital even, but how much do you really know about it? You might know about undo and redo statements, but did you know DB2 can startup without its directory but not without its log? How does DB2 use its log during startup and shutdown? When are records forced to the log? For which objects will the log contain all recovery information? If you want to know the answer to these and many other log related topics, come to this session and we’ll demystify the log. It will show the importance and the power within the log. Almost everything is in the log, for you to use, but you have to be aware of it. This session will make you appreciate the DB2 log more and make you understand why it is so critical. Either you log or you lose.

This presentation looks at the features of DB2 9 and 10 for z/OS with an emphasis on the features focusing on performance. DB2 9 and 10 both promise opportunities for CPU savings in various areas. We will review those enhancements and look at all available features in the database, system and SQL and their potential usage. We will look at where the performance opportunities are, what we need to do to take advantage of them, and any additional considerations when exploiting these features. We will also look at experience with these features and performance realizations.

The format of the physical rows stored on a page has remained essentially the same since DB2 Version 1, but DB2 9 for z/OS changes all this with the introduction of Reordered Row Format. This new feature is on by default so you are very likely to start using without even noticing – it’s implemented whenever you create a new object or when you run the first REORG or LOAD of a table after going to V9 NFM. The good news is that in almost all situations using RRF is a thoroughly good thing and almost all sites will benefit from it. In fact most sites will remain in blissful ignorance that the row format has even changed internally. However, as often happens there are exceptions to the general rule even if they’re rare and it’s far from clear from the documentation what you can do if you find yourself in one of these situations.

This session will explain how RRF is implemented together along with some tips regarding when you might want to take a closer look at whether it may be hurting you and what you can do if you find yourself in such circumstances.

With DB2 10 IBM introduced the concept of Temporal Tables, providing the ability to associate data with a period or point in time. The vast majority of business applications rely on some notion of time. This innovation will help to drastically reduce the volume of application logic required to manage different, time constrained, versions of application data. This session will cover the various flavours of Temporal Tables and dig into the mechanics of what happens when the data in these tables is altered.

This presentation looks at the features of DB2 9 and 10 for z/OS with an emphasis on the features focusing on performance. DB2 9 and 10 both promise opportunities for CPU savings in various areas. We will review those enhancements and look at all available features in the database, system and SQL and their potential usage. We will look at where the performance opportunities are, what we need to do to take advantage of them, and any additional considerations when exploiting these features. We will also look at experience with these features and performance realizations.