Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Self-Paced Learning    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
BlockChain
Business Analysis
Business Intelligence
Business Objects
Business Skills
C++ programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Data Science
Datawarehousing
DB2
Desktop Application Software
DevOps
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
DB2 SQL Performance & Tuning
Overview

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:

  1. Create, Retrieve and Manipulate database objects with performance objectives
  2. Evaluate DB2 SQL for better performance and execution
  3. Understand relational versus object database concepts
  4. Identify performance objectives in using joins, subqueries or set operators
  5. Utilize EXPLAIN utilities to examine SQL performance
  6. Use DB2 non-ansi SQL and evaluate performance issues
  7. Utilize QMF to produce reports and jobs in different environments
  8. 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:

  1. TSO id's for each student
  2. DB2 privilege levels of table create authorization on a DB2 subsystem.
  3. Appropriate tablespaces for students to create tables in.
  4. A tablespace available for the instructor to install a large table with 100,00 rows.
  5. The instructor needs to have at least table create privilege as well and a temporary tso id if possible.
  6. 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……………………….


 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
© Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy