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 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.
Duration:
5 Days/Lecture & Lab
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.
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
Prerequisites:
-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
Last Update: May 22, 2012
