24

OK, so we can have up to 4,096 partitions in a single partitioned tablespace and we can now add new partitions to existing partitioned tablespaces - this is simple, so what's the big deal with this presentation? Well, just as with on-line schema evolution, there are a number of "gotchas" just waiting for you to trip up. These little features are examined in this presentation whilst we look at the wider implications of a new type of partitioned tablespace management.

With current disks, sequential read is much faster than random read (down to 0.1 ms per 4K). To get the benefit of this, your sequential scans should be really sequential. Leaf page splits add random reads to sequential index scans and misplaced table rows add random reads to clustered index scans with table access.

Some indexes and tables need lots of distributed free space, some do not need any. It makes sense to classify your tables and indexes according to the insert pattern. Row length plays a role as well. Both index and table rows are getting longer than they used to be. How should you specify free space if the average row length is 1,000 bytes?

The presentation recommends reorganization and free space strategies for both indexes and tables. It is based on a study by the DB2 User Group in Finland.

Fact #1- SQL is the only way an application can get data from DB2 (outside of utilities).
Fact #2 - SQL can make DB2 fly or crawl.
Fact #3 - 70% of the performance gains can be achieved by tuning SQL

During this presentation you will get a better understanding of how DB2 dissects SQL. We delve into the mechanics of the DB2 optimiser. DB2 EXPLAIN is explained – this helps the programmer gain insight on how the SQL will perform. Accounting Records offer vital information about how efficient the SQL actually is, here we look at what to concentrate on to spot problems quickly. And finally we have a look at best practise techniques on how to write good SQL.

Part I
The Anatomy of an SQL statement
Predicate processing
Join Processing
Stage 1 vs Stage 2 – Important things you need to know
Explaining EXPLAIN

Part II
Impact of Locking on SQL Performance
Working with Optimization Hints
Accounting Records
Best Practice Techniques for writing SQL

One of the most eagerly awaited features of Version 8 is the considerable extensions to the ALTER statement. We now have the ability to alter far more than we could in Version 7, mostly without taking an outage. However, there are still some implications both for availability and for performance that must be taken into account when performing these alters. Does On-Line Schema evolution spell the end for change management tools such as RC/Migrator? You will find out in this presentation that it doesn't - in fact it makes the need for intelligent change management software even greater.

At last the next version of DB2 for the mainframe is available (or I hope it will be by the time of this conference!). This presentation takes a brief and high level look at the new features and facilities that are available with Version 8. We also look at the migration path, and consider some differences to a traditional DB2 version migration. Lastly, we look at the pre-requisites for Version 8 and summarise the features of earlier versions that have been removed.

Increasingly many DB2 professionals find that index tuning when done properly can dramatically improve performance. During this presentation we look at the effects of an index on performance, the different types of Index Access are explained and most importantly the key ingredients of good index design are discussed in depth.

Why are Indexes important?
Reduce I/O
Reduce Elapsed time
Reduce CPU
Stage 1 and Stage 2 predicates
Different Types of Indexes (including new Index Types in V8)
Clustering
Unique
Non-unique
Partitioning
Catalog Indexes
Sparse Index
Index on Large Objects
DPSI
NPSI
Variable Length Index Keys
Index Access Paths
Index Only
Matching Index
Non Matching Index
List Prefetch
Multiple Index Access
IN list Index Access
Formula for Designing Efficient Indexes
What makes an Index bad?
Things to keep in mind when designing indexes
Formula for designing Efficiency into your indexes
The ‘Grading System’ explained
Keep workloads in mind
How often is that badly performing SQL executed?
Which SQL statements are critical to the business?
Back to the Accounting reports to check performance

The databases with many terabytes of data are no longer rare. DB2 for z/OS is very well equipped for handling these challenges, but the users need to know what features are at their disposal and what are alternatives when it comes to physical database design. This presentation will concentrate on issues relevant to the design of very large databases such as systems setup, resources allocation and physical design.