| DB2 SQL Application Programming |
| |
| This class provides the knowledge and skills needed to use features of DB2 and SQL in both interactive and static formats. It also includes information on relational database concepts and facilities, referential integrity, locking, primary keys, foreign keys and data integrity, DB2 physical storage and programming considerations. Hands on computer workshops allow students to run dynamic SQL through SPUFI or QMF, and static SQL using COBOL application programs. There is discussion of DB2 triggers and procedures in a client server environment. |
|
| Covered Topics: |
- Briefly describe relational database concepts and how DB2 implements the relational model.
- Define and code the DDL statements of CREATE, ALTER and DROP
- Define and code the DML statements of SELECT, INSERT, UPDATE and DELETE
- Use SQL SELECT verb to qualify data retrieval, such as WHERE, DISTINCT, IN, LIKE, BETWEEN, ORDER BY, GROUP BY and HAVING.
- Use SPUFI or QMF to execute SQL interactively.
- Code more complex queries such as Joins, Subqueries and UNIONs.
- Code aggregated functions COUNT, MIN, MAX, AVG and SUM with and w/o GROUP BY
- Code the date functions DAY, HOUR, CHAR and others used for date calculations.
- Code the scalar functions SUBSTR, DECIMAL, VALUE and others for manipulating data values.
- Understand performance considerations, and code efficient SQL statements
- Define the EXPLAIN bind parameter and how to interpret the Plan Table
- Embed SQL code in COBOL programs for static processing of DB2 tables.
- Prepare, execute and test batch programs that access and update DB2 data and maintain integrity.
- Employ CURSOR processing in application programs.
- Code commit point processing and identify when and how to use commit points effectively.
- Describe the different locking modes DB2 employs and the implications to concurrent processing.
- Define the SQL statements that cause locking and unlocking of rows or pages in a DB2 database.
- Use simple dynamic SQL statements in application programs.
- Describe major DB2 facilities and their purpose, such as program preparation and various utilities.
- Identify the use of stored procedures in a DB2 Client Server Environment.
- Identify the use of triggers in a DB2 Client Server Environment.
- Identify the new features of Release 6.0 of DB2.
|
| Prerequisites: |
- Participants should have working knowledge of TSO/ISPF.
- Also, previous COBOL coding experience is preferred
|
| Audience: |
| This course is designed for people who will be coding and/or maintaining data in a DB2 database using application programs or using SPUFI, QMF, or other interactive tools for adhoc queries. This includes users, systems analysts or application developers |
|
| |
| Course Benefits: |
| The student will be able to communicate and translate business requirements into logical data models. |
| Materials Furnished: |
- Student Manual
- A combination of lecture, discussion and numerous computer labs are included. Students will receive an extensive course reference manual, which will be a valuable reference on the job. Included for quick reference are the appendices for syntax, SQLCODEs, glossary as well as a complete index. The course can be split into two separate classes for non-programmers if necessary:
|
| Recommended Follow-On Course: |
| |
|
|
|
|
|
