DB2 SQL

Overview

This course offers students a lecture/lab intensive environment on DB2's Data Server Technology. The class covers the concepts of Basic SQL, Objects and Advanced SQL in a DB2 database and the use of the Gui interfaces for executing SQL. The student will learn how to write simple to complex queries along with being able to modify database objects through SPUFI, QMF or the Command Console. Advanced technique such as ROLLUP, CUBE, set operators and creation of user-defined datatypes discussed. Embedded SQL in a procedural language such as C or COBOL is also 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
Audience

Application Developers
Database Administrators
Systems Analysts and Technical Support Professionals


Prerequisites

Working knowledge of computers

Course duration

5 days of lecture/lab. This is a lab intensive class.

Hardware/Software Requirements

Tso ids for all students. DB2 privileges on a DB2 subsystem of the clients choice.

Hardware

Mainframe OS/390 system with TSO and DB2 subsystems installed
Windows or Unix
Minimal processor speed of 266 mhz or higher
Memory requirements - 128 mb
Video: 16 color (800 x 600 resolution minimum)
50 mb of storage space per student

Course outline

I. INTRODUCTION TO DB2
 
Objectives
A. DB2 UDB RDBMS Architecture.........………...............................
B. DB2 Components .................…………........................................
C. DB2 UDB GUIEnvironment.........................................................
D. DB2 Command Line INterface.....................................................

II DATABASE MODELING
 
Objectives
A. E/R Modeling techniques .........................................................
B. Normalization ...........................................................................
C. Referential Integrity Constraints ..............................................

III BASIC SQL LANGUAGE
 
Objectives
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 .......................................................

IV. ADVANCED SQL LANGUAGE
 
Objectives
A. Accessing tables through Joins ................................................
B. Understanding the use of Outer Joins ......................................
C. Comprehending Subqueries .....................................................
D. Selecting rows with the Union, Intersect and Minus clauses ....
E. Modifying data through Inserts,Updates and Deletes ...............

V. DATA DEFINITION LANGUAGE
 
Objectives
A. Creating base tables ................................................................
B. Using DB2 datatypes ........................................................
C. Building Indexes for base tables ..............................................
D. Creating Views for Users .........................................................
E. Creating Synonyms ..................................................................

VI. EMBEDDED APPLICATIONS
 
Objectives
A. Singleton Selects ...............................………..........................
B. DB2 to C conversions……….. .................................
C. Cursor Processing.................................................................
D. Error Handling…. ...................................................................
E. Row Level Locking...................................................................


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