DB2 and SQL Programming course outline

DB2 and SQL Programming

Duration: 5 days

Audience: Programmers who will be working with DB2

Prequisites: Good working knowledge of COBOL or PL/1

Objectives: Top Contents

On successful completion of this course each student will be able to:

* describe how a DB2 database is held on physical storage
* list the tasks involved in implementing a DB2 application
* explain the meaning of terminology such as table and view
* describe the functions of DDL, DCL, and DML
* state how data is arranged in a DB2 table
* describe the use of primary and foreign keys
* explain how the use of indexes can improve access to DB2 data
* describe how a DB2 program is prepared for execution
* use SQL statements such as SELECT, INSERT, UPDATE, DELETE
* use SPUFI to test SQL statements
* code statements which use scalar functions
* code statements which use column functions
* list the functions introduced with DB2 UDB for OS/390 V6 and V7
* code statements which combine tables
* code statements which use correlated subselects
* code statements which use outer joins
* code the entries which enable a COBOL program to access DB2 data
* describe the function of IKJEFT01 when running batch COBOL/DB2 programs
* use cursors and host variables
* describe the two phase commit procedure
* describe the security facilities of DB2
* explain how DB2 locking affects rows and tables
* code a utility to load a DB2 table
* unload data from a DB2 table
* explain how to resolve CHECK PENDING status
* describe the use of a plan table
* interpret EXPLAIN information
* describe the function of stored procedures
* describe the function of triggers

Contents: Top Objectives

Introduction
Comparison of DB2 tables with VSAM files and hierarchical databases (e.g. IMS, TOTAL); DB2 and the Relational Model; tasks and personnel involved in implementing a DB2 system; DML and DDL; sample uses of DB2; Web Enabled DB2.

Tables and DB2 architecture
How data is arranged; storage groups, databases, tablespaces, indexes, tables, views; basic table design, keys, table splitting; referential integrity, foreign keys, constraints, triggers, stored procedures, LOBs; functions of the catalog tables; typical disk requirements; physical storage considerations; temporary tables.

Accessing DB2 data
How programs can access DB2, SQL language; precompile and BIND process; EXPLAIN. Access from TSO and batch. Functions and use of DB2I and SPUFI. SQL communication area. DCLGEN.

SQL programming
SELECT statement, predicates, JOIN and UNION queries; scalar functions; labelled durations; column functions: BETWEEN, NULL, LIKE, EXISTS, IN; GROUP BY; UPDATE, DELETE, INSERT; CASE expressions; special registers; subquery with IN; subquery with NOT IN; correlated subselect; EXISTS, ANY, ALL; outer joins; dynamic SQL; access from COBOL or PL/1; singleton selects; using cursors, scrollable cursors, host variables, indicator variables; CICS considerations, debugging with EDF; running in batch, IKJEFT01.

Security, integrity, utilities
Facilities which can be controlled; GRANT and REVOKE; primary and secondary authorisation IDs. Overview of locking; COMMIT, ROLLBACK. Utilities: load utility, making image copies, unloading tables, CHECK. SQL performance: using the plan table, interpreting EXPLAIN information, estimating the cost of an SQL statement.