Learn: in:
» back to Search Results

Course rating of 0 Vendor rating of 4


This course focuses on best practices used to design, create and process a Relational Database structure using up to and including version 9.1 of DB2 for z/OS.


 
Course Outline
Description

This course focuses on best practices used to design, create and process a Relational Database structure using up to and including version 9.1 of DB2 for z/OS. The student will demonstrate how to use Entity Relationship Modeling and Normalization techniques to design logical data models that can support Referential Integrity. The student will learn how to define basic DB2 objects, such as Tables, Indexes, Views and Synonyms. The student will demonstrate how to effectively and efficiently use SQL to extract and manipulate data in DB2 database objects, with an emphasis on performance considerations. Participants will design, code and test application programs using embedded SQL to process data in DB2. The students will demonstrate the process of pre-compiling, compiling, linking and binding DB2 application programs. Students will learn how security is implemented in DB2.

Topics
  • DB2 Concepts, Terminology and Storage Concepts
  • Entity Relationship Modeling Techniques
  • Logical and Physical Database Design
  • Referential Integrity Rules
  • Basic SQL - Using SELECT, INSERT, UPDATE, DELETE, TRUNCATE and MERGE
  • Advanced SQL - Using JOIN, Subquery, UNION, EXCEPT, INTERSECT and Nested Table Expression
  • Basic Data Definition Language (DDL) - Used to Create and Modify DB2 Objects, such as Tables, Views, Indexes and Synonyms
  • Application Coding Considerations, including use of Host Variables, Standard Cursors, Scrollable Cursors and Non-Cursor Processing
  • Program Preparation, Bind and Locking Strategies
  • SQL Performance Considerations
  • Benefits of EXPLAIN
  • Implementing Security by using GRANT and REVOKE to control Object Privileges
Audience

Application programmers and programmer/analysts who will write SQL statements in a high level programming language (such as COBOL, C, PL/1) to manipulate DB2 tables in a mainframe CICS, TSO or IMS/TM environment. This course would also be beneficial to analysts and database administrators who support application teams.

Course Outline

I. Entity Relationship Modeling

A. Introduction to Database Design
B. Defining the Mission
C. Entity Relationships - Modeling and Diagrams
D. Entity Relationship Diagrams - Notational Conventions
E. Association Entities
F. Drawings Guidelines
G. EXERCISE - Entity Relationship Diagrams
H. Data Elements and Primary Keys
I. EXERCISE - Assign Data Elements and Primary Keys

II. Normalization
A. Introduction to Normalization
B. First Cut
C. Overview of Normal Forms
D. First Normal Form (1NF)
E. Second Normal Form (2NF)
F. Third Normal Form (3NFDerived Columns
G. Logical Design Evaluation
H. Benefits of Normalization
I. Denormalization
J. Referential Integrity
K. Delete Concepts
L. Insert and Update Implications
M. Summary
N. Transition from Logical to Physical
O. EXERCISE - Normalization

III. DB2 Overview and Storage Concepts
A. What is DB2?
B. What is the History behind DB2?
C. What are DB2's Objectives?
D. What is a Relational DBMS?
E. What are DB2's Features?
F. Operational Environment
G. SQL
H. Program Preparation Process
I. Unit of Recovery
J. Commit / Rollback
K. DB2 Terminology
L. Physical Hierarchy of DB2 Objects
M. Naming Conventions
N. Object Naming Conventions
O. Databases
P. DB2 and VSAM
Q. Storage Groups
R. Page Management
S. Tablespace
T. Segmented Tablespaces
U. Partitioned Tables
V. Base Tables
W. View Table
X. Synonym
Y. Indexes
Z. Stored Procedures and Functions
AA. DB2 String Data Types
BB. DB2 Numeric Data Types
CC. Data and Time Data Types
DD. Display Formats
EE. ROWID Data Type versus Identity Column Attribute
FF. User-Defined Data Type
GG. DB2 Catalogs

IV. Basic SQL - SELECT
A. Table Names
B. Basic SELECT Statement
C. WHERE Clause
D. INEQUALITIES
E. Specific Column Selection
F. Case Expression
G. Derived Columns
H. Date and Time Usage
I. Common Special Registers
J. User Request #1
K. ORDER BY Clause - The Results Table Sort
L. Ordering Derived Columns
M. Order By and Fetch First n Rows ONLY
N. DISTINCT Operand
O. Expanding on the WHERE Clause
P. Multiple Conditions
Q. BETWEEN Clause
R. IN Clause
S. LIKE Clause
T. User Request #2
U. Negative logic
V. NULLS
W. IS DISTINCT FROM
X. Types of Built-In Functions
Y. Aggregate Functions
Z. Functions Involving NULL Values
AA. NULL Values are Considered In
BB. User Request # 3
CC. Scalar Functions
DD. CHAR
EE. Date or Time
FF. Hour, Minute, Second, Year, Month, Day
GG.Days
HH. Decimal
II. ROUND
JJ. TRUNC
KK. Digits
LL. Integer
MM. CAST
NN. COALESCE
OO. UPPER and LOWER
PP. Strip
QQ.POSSTR
RR. Substr
SS. Concatenation
TT. Basic SQL - SELECT
UU. GROUP BY Clause
VV. HAVING Clause
WW. User Request # 4

V. Advanced SQL - SELECT
A. Join
B. Inner Join
C. Full Outer Join
D. Left - Right Outer Join
E. Joins of More Than 2 Tables
F. User Request # 5
G. Subquery
H. Single Value Subquery
I. Multivalued Subqueries
J. Multivalued Subqueries - ALL
K. Multivalued Subqueries - ANY or SOME
L. Multiple Column Subqueries
M. User request # 6
N. Correlated Subqueries
O. Correlated Subqueries - Exists
P. Using Correlation Variables to Check R.I
Q. Nested table Expression
R. Union
S. Union All
T. Rules for Union
U. Performance Considerations
V. INTERSECT
W. EXCEPT
X. User Request #7

VI. Update Data Manipulation
A. Insert
B. Update
C. SELECT FROM UPDATE
D. Delete
E. Truncate
F. SELECT FROM Delete
G. Merge
H. DB2 Valid SQL Return Codes for Updating

VII. Creating DB2 Objects using Data Definition Language
A. Structured Query Language (SQL)
B. DDL - Create Table Statement
C. Identity Column
D. Check Constraints
E. Alter Table Statement
F. Not NULL with Default
G. Referential Integrity
H. SynonymsDeleting DB2 Objects
I. Index
J. Unique Versus Non-Unique Indexes
K. Cluster versus Non-Cluster Indexes
L. Cluster Index
M. Index Create
N. Partitioned Table
O. Views
P. Creating Common Tables Expressions

VIII. Application Program Considerations
A. DB2 Program Components
B. DB2 Additions to a Program Structure
C. Delimiters
D. Program Storage - Host Variables
E. Program Storage - DCLGEN
F. DCLGEN Utility
G. Expanded Include Member
H. SQLCA SQL Communication Area
I. SQLCA Field Definitions
J. SQLWarning Definition
K. Error Handling
L. Unit of Work in an Application Program
M. Commit
N. Overview of Cursor and Non-Cursor Processing
O. Non-Cursor Processing
P. DB2 to Host Language Data Type Conversion Chart
Q. Host Structures
R. Host Structure Arrays
S. How to Handle NULLS
T. Non-Cursor Exercise
U. Cursor Processing Overview
V. Declaring a Standard Cursor
W. Declaring a Static, Dynamic or Rowset Scrollable Cursor
X. OPEN Cursor
Y. FETCH Standard Cursor
Z. FETCH Scrollable Cursor
AA. FETCH Rowset Cursor
BB. Application Program Considerations
CC. Cursor Update or Delete
DD. CLOSE Cursor
EE. Set Level Update in an Application Program
FF. Example COBOL Program
GG. Cursor Exercise

IX. Program Preparation, Bind and Locking
A. Overall Procedure
B. Precompile
C. DB2 to Host Language Translations
D. Bind Procedure
E. Bind Panel
F. Rebind and Free
G. Validate Option
H. Timestamp
I. Program Isolation
J. Lock Table
K. Lock Duration

X. Security
A. Security Overview
B. Resources and Users
C. Privileges
D. Implicit Versus Explicit
E. Grant
F. Revoke
G. Secondary Auth-ID
H. Role

XI. Application Performance Considerations
A. Application Tuning - Performance
B. Application Tuning - Explain
XII. DB2 Application Programming Labs
A. LAB 1 - Creating Tables
B. LAB 2 - Loading Tables
C. LAB 3 - DCLGEN
D. LAB 4 - Coding Exercise
E. Extra Challenge Problems - Optional for this Lab


Prerequisites & Certificates
Pre-Requisites

- Experience coding in one the programming languages listed (COBOL, C, PL/1) - Experience with TSO/ISPF - Experience with file structures such as VSAM, IMS, or QSAM

Certificates offered

Certificate of Completion


Cancellation Policy
10 Day cancellation required for all courses in order to get a full refund.
Map & Reviews
Protech Training
[ View Provider's Profile ]

Reviews
 

This course has not yet been rated by one of our members.

If you have taken a course through this vendor please log into your account and leave feedback for this vendor. You will be helping ensure our members get directed to the best training facilities.

Here are some reviews of the training vendor.
I took the training remotely so I cannot comment on some of the questions. I think that this seminar has too much content to squeeze it into one week.... might be better as a two week course?
Reviewed by 2013
found the material very helpful - although there was a lot of material covered - the pace was well set. Would definitely recommend people to have explored Microsoft Access beforehand to be familiarized with the program before taking the course. I would imagine someone without any exposure would find it confusing. Environment really good - people very, very nice and helpful. Would definitely take the next level of access with the same training facility.
Reviewed by 2013
I was not in the physical classroom. Feedback centered around online training would be good because now I have to rate items that I was not present for. Those items are getting half a star because I was not there to rate them. The course material must go through an editing process. There are a lot of grammar mistakes, but also a lot of actual procedure/activity mistakes.
Reviewed by 2012
need-to-train-a-group-banner

This course currently does not have any dates scheduled. Please call 1-877-313-8881 to enquire about future dates or scheduling a private, in house course for your team.

This page has been viewed 245 times.