Articles & Content

Archives

Bottlenecks Elimination in Real World DB2 Applications (D01)

Topic: 2008 EU

Subtopic: DB2 for LUW



DATE: 2008-10-13 (11:15 - 12:15)
SPEAKERS: Sigen Chen (Lockheed Martin)

Database application performance for a given system (hardware and software) may be determined by application behavior, APIs, database design and layout, data size, system configurations. This presentation will cover these aspects based on the performance improving practice from real world database applications. The focus will be on understanding the application behavior; creating the right indexes; writing optimal queries, exploring the query features wisely; using appropriate APIs for a given requirement, not only on the programming language level, but also on the statement attributes such as cursor type, data type for binding, fetch orientation, array options; practicing proactive maintenance to ensure optimal data layout and statistics; tuning the key configuration parameters based on application behavior and system monitoring data. The troubleshooting examples and sample code segments are used to exemplify the practice. Performance issue debugging and analysis is also included.

EXP. LEVEL: Intermediate

OBJECTIVES:

Discussing how to identify the bottlenecks by analyzing the debugging data using system tools (vmstat, top, prstat, sar, pmap etc.), DB2 native tools (snapshot, even monitor, access plan, db2pd etc..) and profiling tools.

Showing how to collect and analyze the query access plan, and using the right indexes to reduce the cost of a bottleneck queries.

Analyzing several commonly used DB2 Supported APIs (Embedded-SQL, CLI, JDBC, ADO, Perl, CLP), and their performance difference through our test data; Comparing several fetch/insert orientations of CLI, statement attributes, and test the performance.

Writing the most efficient queries, using the query options wisely, such as blocking features. After all the DBMS supposed to do exactly what application (queries) requested to do.

Understanding the application nature (OLTP or DSS or mixed), and tuning the DBM and DB configuration parameters accordingly; maintaining the database proactively to ensure the optimal database performance



This file is being provided by IDUG. We would encourage you to join IDUG to get full access to all of our files and resources. Joining IDUG is FREE and signing up is simple. Click here to join! or login!

Download File
Click to Download

NOTE: These are only open to members of IDUG. If you are not a member, please CLICK HERE for more information.