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.
SQL Adria is the independent, non-profit organization that gathers relational database users for Croatia and Slovenia. It was founded 1994. and in the same year it has become the regional user group.
8 reasons to come Speakers Events Contact
VODICE - 10th - 13th June 2024