27

SQL can be processed in parallel by DB2.  How does this work? What is the difference between IO parallelism and CPU parallelism? How do I ask for parallelism and how can I find out it has will be or has been used? Which settings and parameters are important? Klaas will show you that processing SQL can be a great feature of DB2 but there are cases when parallelism is very bad option.
Topics: Types of parallelism, asking for parallelism (degree), parameters that influence parallelism (Bufferpools, ZPARM, SQL), Monitoring and predicting (Explain and accounting trace), Rules for using parallelism

This presentation shows the real and often surprising results of analysing DB2 environments in representative DB2 for OS/390 installations (Banks, Insurance, Industry and Cross Service Companies) in Europe and USA.The most common discrepancies and inconsistencies affecting the availability, the performance and  the reliability of DB2 will be reported as well as their reasons, impacts and possible ways to solve or palliate them.

PRESENTATION OUTLINE:

Part 1: BACKGROUND:
Presentation about the investigations done in order to detect discrepancies and inconsistencies affecting the following areas of concern:
 Catalog size
 Catalog  reliability and consistency  
 Space saving
 Application/utility Concurrency  
 CPU and IO performance
 Resource availability and data recoverability  
 Parallel-sysplex datasharing
 DB2 Upgrades
 Security handling
 Change management and housekeeping

Part 2: FINDINGS:
Presentation of the discrepancies and inconsistencies encountered in representative DB2 for OS/390 installations (Banks, Insurance, Industry and Cross Service Companies) in Europe and USA.

CONCLUSION/SUMMARY:
Report of 'very often', 'often' and 'rarely' encountered discrepancies and inconsistencies and a rating of the benefits of resolving them.

Abstract:
This presentation will cover aspects of Version 8 of DB2 on the z/OS platform and how they will impact our applications, SQL and performance. There are several new constructs in DB2 in the next release such as materialized query tables, multi-row fetch/insert, dynamic scrollable cursors, imbedded inserts, better matching predicates, sequence objects and more. There have also been many improvements DB2s usage of indexes and this will have an impact on how we code our SQL. WLM is playing more of a part in our environments and we need to begin the migration toward it if we have not. We need to review these features and see how they impact current applications and help us prepare for the future in order to get the best performance possible from our DB2 applications

Five Bullet Points
Overview of V8 Features that SQL and Applications
Discussion of New SQL Functionality
Discussion of New Application Programming Features
Discussion of Why Its is Important to Use The New Features
Discussion of How to Plan and Best Implement New Features

Outline:
Application and SQL
 Materialized Query Tables
 Coding Predicates for DPSIs
 Table Function Cardinality
 Multi-row FETCH and INSERT
 DDF Block Fetch
 INSERT within SELECT
 Dynamic Scrollable Cursors
 Identity Column Enhancements
 Sequence Objects
 Indexable Unlike Types
 Increased of Number of Tables in a Statement
 Stored Procedures and WLM

Today’s application environments demand more availability and high performance that we ever had in the past. One problem with achieving both of these is locking. Locking can hold resources that effects availability and can slow down performance. We will take a look at some things we can do to minimize this without compromising data integrity. We will also look at application availability issues, because if you are not available….you are not performing!

Locks and Lock Avoidance
 Bind Parameters
  Uncommitted Read
  Release Deallocate
  Current Data
 Lock Avoidance
  How it works
Application Design
 Designing for no deadlocks
 Restructuring code for maximum throughput
 Commit Strategies
 Index availability
 Cursor Usage

DB2 for z/OS plays an integral role in any enterprise network computing solution. This presentation discusses what is new, hot, and on the horizon in the overall area of network computing in DB2. Topics covered include connectivity from ODBC, Java, and .net clients, what is new in Version 8 to support these applications, plus information on web services access and XML support for DB2 for z/OS applications.

Part1
SQL Performance is key to the success of any application, however programmers are often not given the full story and hence queries are not coded in the most optimal way. This session is here to remedy this situation. Part 1 deals with gaining solid in-depth understanding of DB2 EXPLAIN. Filter Factors and Runstats are closely examined and insight is gained into which statistics columns trigger certain optimizer decisions.

Bullet Points
Why is SQL Performance so Important?
DB2 Explain – The Hieroglyphics translated
Understanding Filter Factors
Runstats & the Statistics Tables
Summary

Part2
In this presentation we take a good look at the optimizer and how it goes about creating access paths. The different types of Joins are examined and the reasons why DB2 chooses certain joins over others. Inner and all the flavours of Outer joins are discussed and how they compare performance-wise to other SQL techniques. And most importantly SQL Coding Do's and Don'ts are examined.

Bullet Points
The Optimizer – Understanding how it thinks!
Joins – The different types, how they work
More on Joins - Inner, Outer, Left & Right Outer
Speeding up Performance – The Do’s and Don’ts
Conclusion

One of the major changes to DB2 for z/OS Version 8 is in the way partitioned tablespaces are created and managed. This presentation will introduce you to table-based partitioning, all of the new features that are available and some pointers on how these enhancements will benefit application design and operation. Unfortunately, there are one or two scares along the way, but these are easily understood and by the end of the session, Version 8 partitioning should hold no fears!

Release 11 of the Unicenter Database Management tools for DB2 for z/OS is the first release of tools from CA (indeed from any major software vendor) that supports DB2 UDB for z/OS Version 8. However, there is much more to r11 than Version 8 support. In this session, Phil will summarise many of the key enhancements to our DB2 tools.