This course offers students a lecture/lab intensive environment on DB2's Data Server Technology. The class covers the concepts of both performance tuning of SQL, Objects and Advanced SQL in a DB2 database and the use of SQL. The student will learn how to write simple to complex queries along with being able to modify database objects through SPUFI and QMF. Advanced technique such as ROLLUP, CUBE, set operators and creation of user-defined datatypes discussed.
LEARNING OBJECTIVES
The student upon completion of this course will be able to:
- Create, Retrieve and Manipulate database objects with performance objectives
- Evaluate DB2 SQL for better performance and execution
- Understand relational versus object database concepts
- Identify performance objectives in using joins, subqueries or set operators
- Utilize EXPLAIN utilities to examine SQL performance
- Use DB2 non-ansi SQL and evaluate performance issues
- Utilize QMF to produce reports and jobs in different environments
- Create Objects in the DB2 Object/Relational Database
Audience
Application Developers - Database Administrators
Systems Analysts - Technical Support Professionals
Prerequisites
Working knowledge of computers
Course duration
5 days
Software & Privileges
For Mainframe:
- TSO id's for each student
- DB2 privilege levels of table create authorization on a DB2 subsystem.
- Appropriate tablespaces for students to create tables in.
- A tablespace available for the instructor to install a large table with 100,00 rows.
- The instructor needs to have at least table create privilege as well and a
temporary tso id if possible.
- Labs on diskette need to be placed onto a PDS dataset accessible by all
students. The PDS is created by the customer with their naming conventions.
Hardware
Minimal processor speed of 266 mhz or higher for NT/UNIX
OS/390 mainframe
Memory requirements - 128 mb
Video: 16 color (800 x 600 resolution minimum)
50 mb of storage space per student
Course outline
|
1. SQL LANGUAGE PERFORMANCE ISSUES
|
| |
A. Understanding the SQL Format .............................................
B. Querying Row and Columns ..................................................
C. Using the Where clause .........................................................
D. Accessing tables with compound statements ........................
E. Utilizing Built-In Functions .....................................................
F. Searching for Null values .......................................................
|
|
2 TUNING SQL CODE |
| |
A. COMMON SENSE SQL
. .............................................
B. Using ORDER BY'S FOR PERFORMANCE..........................
C. Using the Where clause instead of ORDER BY.....................
D. Accessing tables with compound statements ........................
E. Utilizing table scans for speed
..
F. Evaluating Indexes for use in table access
.
G. Utilizing Built-In Functions .....................................................
H. Searching for Null values .......................................................
I. How Null values are stored
|
|
3 ADVANCED SQL CODING |
| |
A. Use of Order BY and GROUP BY statements
B. Accessing tables through Joins ...................................................
C. Using Inner Joins versus non-equal joins.....................................
D. Understanding the use of Outer Joins .........................................
E. Performance heuristics for joins
..
F. Comprehending Subqueries ........................................................
G. Selecting rows with the Union, Intersect and EXCEPT clauses .......
H. Complex modifications using Inserts,Updates and Deletes ...............
|
|
4. Tuning SQL with IBM Tools |
| |
A. SQL Applications........................................................................................
B. Memory Applications .................................................................................
C. Tuning I/O....................
..............
.....................................
D. Phases of the SQL statement Stage 1 & Stage 2
.
..
E. Evaluating Table storage clauses
.
.
|
|
5. DB2 FUNCTIONS AND NON ANSI SQL |
| |
A. Converting data to different data types.....................................
B. Using DB2 datatypes ........................
...............................
C. Date conversions and functions ..............................................
D. Executing Scalar Functions......................................................
|
|
6. USING IBM's PLAN TABLE/EXPLAIN |
| |
A. USING THE PLAN TABLE......................................................
.........
B. OPTIMIZER RULES..................................................................
.......
C. DRIVING TABLES.......................................................................
....
D. EVALUATING QUERIES
...
.
E. TRACE & DIAGNOSTICS Tools
|
|
7. EMBEDDED APPLICATIONS |
| |
A. Singleton Selects ...............................
..........................
B. DB2 to COBOL/C conversions
.. .................................
C. Cursor Processing.................................................................
D. Error Handling
. ...................................................................
E. Row Level Locking...................................................................
F. Cursor First, Last movement
|
|
8. SQL PERFORMANCE & TUNING |
| |
A. Using Indexes
B. Partitioned Tablespaces
C. Segmented Tablespaces
D. DB2 Catalog Tables - RUNSTATS
E. Lab - DB2 Catalog and RUNSTATS
F. DB2 Stage 1 and Stage 2 Predicates
G. DB2 Filter Factors
|
|
9. SYSIBM CATALOG TABLES |
| |
Objectives
A. Reviewing the SYSIBM tables for Performance
.......................
B. Identifying Statistical patterns
..
.. .................................
C. The Optimizer plans
........................................................
D. Error Handling
. ...................................................................
E. Row Level Locking...................................................................
|
|
10. ISOLATION LEVELS |
| |
Objectives
A. Reviewing the IBM locking mechanisms.......................
B. Types of Page/Row Locks
..
.. .................................
C. Table/Tablespace Locks....................................
D. Watching for problem binds
.
|
