Maria Sarikos

This is the sequel of the award winning presentation - Java & the Wild Wild Web. In this presentation we delve into more advanced Web topics such as – The ABC’s of creating a Java pgm accessing DB2 ; Web services and their components and how they interact with DB2; Why XML is so important and how DB2 handles it; Why SQLJ is so much better for you & faster than JDBC; Plus we review some Web Performance commandments.

Presentation Bullet Point:
1. The ABC’s of creating a Java pgm accessing DB2
2. Web services and their components
3. XML - why its so important & how DB2 deals with it
4. Why SQLJ is better for your and your DB2’s health
5. Web Performance commandments

There are two ways to get at DB2 data - either via DB2 utilities or SQL. Badly coded SQL accounts for a large percentage of DB2 performance problems. During this presentation we will examine the different ways of getting at the same data and how to save CPU cycles and I/O!! We will also delve into some little known facts pertaining to Joins and UNIONs as well as CASE Expressions.

Bullet Points
The old favourites revisited - Stage 1, Stage 2 & Sorts
Predicate sequence – it still makes a difference!
Outer Joins, Case Expressions & UNIONs
Locking and The Impact of COMMIT
Arithmetic in Predicate Evaluation

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

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