Db2 Application Programming and Design

Summary

This course focuses on best practices used to design, create, and process a Relational Database structure using up to and including version 12 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 Aliases. 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 precompiling, compiling, linking and binding Db2 application programs. Students will learn how security is implemented in Db2.

There are many labs throughout the course where the student will be able to practice the skills they have learned in this extensive 5-day course.

Prerequisites

Students should have experience coding in one the programming languages listed (COBOL, C, PL/1), experience with TSO/ISPF and experience with file structures such as VSAM, IMS, or QSAM.

Duration

5 Days/Lecture & Lab

Audience

This course is designed for 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.

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

Related Scheduled Courses