This course focuses on best practices used to design, create, and process a Relational Database structure using up to and including version 11 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 labs throughout the course where the student will be able to practice the skills they have learned in this extensive course.
Before taking this course, students should have experience coding in one of the programming languages listed (COBOL, C, PL/1), experience with TSO/ISPF and experience with file structures such as VSAM, IMS or QSAM.
5 Days/Lecture & Lab
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
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