DB2 for z/OS: Application Developer - Boot Camp


This comprehensive ten-day 'Boot Camp' course provides attendees with an accelerated learning approach to developing applications in a DB2 for z/OS environment. The course is ideal for those coming new to the DB2 for z/OS environment. The regular, hands-on lab exercises give students the opportunity to try out their newly-gained skills immediately. Throughout the course reference will be made to best practices to ensure that the SQL and application programs will run efficiently and effectively. On successfully completing this boot camp, attendees will have reached the skill level needed to enable them to write, maintan and amend programs for a DB2 for z/OS environment.

This course is available 'on demand' (minimum 2 students) for public presentations or for one-company, on-site presentations.

Objectives

On successful completion of this course you will be able to:

  • describe the relational concepts and theory, and the advantages of a RDBMS
  • list the components of the DB2 system and their functions, and describe how they are controlled
  • utilise all the functions available with DB2 Interactive (DB2I
  • describe the internal structure and components of DB2 UDB for z/OS databases
  • use SPUFI to code SQL statements
  • design and assess a logical database model
  • design and implement efficient physical databases from the logical model using relevant options to create and amend storage groups, databases, table spaces, tables, indexes, aliases, synonyms, etc.
  • code SQL SELECT statements including those using techniques such as joins, nested table expressions, subqueries, unions and case expressions
  • code SQL INSERT, UPDATE, DELETE & MERGE statements to modify data
  • embed SQL statements into an application programming language such as COBOL, PL/I or Assembler
  • understand the differences between views, nested table expressions, common table expressions, and temporary tables and select the best option for a specific task
  • understand the use of materialized query tables, clone tables and temporal tables and the additional SQL statements associated with them
  • describe the use of distinct data types, user-defined functions, and OLAP functions within DB2 for z/OS
  • understand how and when triggers may be used
  • understand and control the locking strategy used by DB2 to maintain data integrity
  • understand the DB2 optimizer and the use of EXPLAIN in determining access path, statement efficiency and choosing optimal code
  • plan for application monitoring and tuning
  • use DB2 traces to identify problem areas and identify tuning opportunities.

Who Should Attend

Applications Developers coming new to the DB2 for z/OS environment.

Prerequisites

Experience in using COBOL, PL/I or Assembler in a mainframe application programming role, an ablitiy to use TSO/ISPF and a working knowledge of z/OS JCL.

Duration

10 days

Fee (per attendee)

£3950 (ex VAT)

Course Code

DBAP

Contents

Day 1:

DBMS Overview

What is a database?; comparison of hierarchical, network & relational database management systems; a brief history of DB2.

Relational Theory & Concepts

Relational theory; relational model; relational algebra; entity, referential and user-defined integrity.

DB2 System & Operations

DB2 address spaces; DB2 attachments; storage pools; logging methodology; basic operations; DB2 commands.

DB2 Interactive (DB2I)

DB2I panels; SPUFI; other functions.

Day 2:

DB2 Data Objects

DB2 objects and the relationships between them; DB2 catalog & directory; interrogating the DB2 catalog.

DB2 Table Design

Data concepts; E-R diagrams; Normalisation; Table creation; DB2 data types and attributes; Constraint & domain management; Temporal tables; System period data versioning; Temporary tables.

DB2 Index Design & Usage

Data access paths; Predicates, index usage and I/O types; Index structure; When to index; Composite keys; Clustering; Index key randomisation; Indexes on expressions; Non-key columns in unique indexes; Creating indexes.

Day 3:

DB2 Physical Database Design & DDL

Pageset structure; Tablespace types and defaults; DB2 object definition; Understanding and choosing options for performance.

Basic SELECT Statements

Selecting all columns; row & column control; multiple conditions; special operators; the escape character; arithmetic in DML; using constants; special registers; concatenation; date and time columns.

SQL Built-in Functions

Column functions; scalar functions; 'group by' and 'having' clauses; User-defined functions overview; Inline and non-inline scalar functions.

Day 4:

Joins & Nested Table Expressions

Joins; correlation names; Cartesian product; inner and outer joins; nested table expressions.

Unions, Intersections,Exceptions & Case Expressions

Union, union all, and case expressions.

Subqueries

Simple subqueries; correlated subqueries; 'IN', 'NOT IN' 'NOT EXISTS'; quantified predicates, effect of nulls on SQL statements.

Day 5:

Table Modification Commands

Insert; inserting multiple rows; update and delete statements; the impact of referential integrity constraints

Basic Application Programming

Language support; embedded SQL; the declare table statement; the SQLCA; SQLCODE; host language variables; handling variable length columns & nulls; using host structures; commit and rollback.

Day 6:

Cursor Processing

Cursor processing, cursors for update, cursor with hold, scrollable cursors.

Program Preparation

Precompile, bind, program preparation and execution; plans and packages; BIND PLAN; BIND PACKAGE; BIND QUERY.

Day 7:

DB2 Locking & Concurrency

Resource serialisation; Claims and drain; Concurrent applications and utility processing; Lock control; Locking options; Lock compatibility; Bind options; Lock avoidance; Monitoring locking.

Using Views & Temporary Tables

Views; Nested table expressions; Common table expressions; Recursive SQL; View options; Created temporary tables; Declared temporary tables; Table comparisons.

Materialized Query, Clone & Temporal Tables

Materialized query tables; maintaining data in MQTs; Using MQTs; Automatic Query Rewrite (AQR); Clone tables; Using clone tables; Exchanging data; System period temporal tables; Application period temporal tables; Using temporal tables; FROM period specification.

Day 8:

Distinct Data Types & User Defined Functions

Distinct data types; Casting data; Sourced user defined function; External user defined function; User defined SQL functions; Table functions; Inline and non-inline scalar functions; Invoking functions; Identifying functions; Function resolution.

Triggers

Triggers; Trigger components; Trigger options; Trigger body statements; BEFORE, AFTER & IINSTEAD OF triggers; Trigger examples; Trigger performance.

Advanced DML options

SELECT FROM INSERT/UPDATE/DELETE; INCLUDE with INSERT & UPDATE; Multi-row processing with INSERT & MERGE; Multi-row condition handling; GET DIAGNOSTICS; Statement information; Condition information; Ranking data; ROW_NUMBER; RANK; DENSE_RANK; Moving sums; Moving averages: Identity columns; Sequences; Changing attributes; Using identity columns & sequences in SQL statements.

Day 9:

The DB2 Optimizer

Input to the Optimizer; Catalog statistics; Filter factors; Filter factor and clustering; Filter factor examples; Influencing the Optimizer; Influencing the Optimizer by manually adjusting statistics, modelling production values & using optimization hints'; Catalog statistics; RUNSTATS options; Statistics columns; RUNSTATS - examples.

DB2 EXPLAIN

EXPLAIN; PLAN_TABLE; PLAN_TABLE additions; DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN: basic access paths, multi-index access, nested queries examples.

DB2 Performance Overview

What is 'performance'?; Performance objectives; Workload categories; Service Level Agreements; Performance factors; System parameters; EDM pool size; Buffer pool sizes; Bufferpool thresholds; Bufferpool development; Database design factors; Application design factors; DB2 traces.

Day 10:

DB2 Traces

Trace types; Trace destination; Accounting trace classes; Audit trace classes; Statistics trace classes; Performance trace classes; Monitor trace classes; Global trace classes; IFCID types; Other trace options; Controlling traces.

Monitoring Application Performance

Monitoring overview; DB2 Performance Expert; DB2PE reports and traces; accounting short report; accounting long report; time distribution reports; times, suspensions & highlights report; SQL activity report; dynamic SQL caching report; other processing activity report; locking activity report; parallelism report; buffer pool and group buffer pool activity reports; statistics long report.

Performance Analysis

EXPLAIN information; DB2PM reports; short accounting reports; long accounting reports; report usage summary; a general approach to problem analysis in DB2.


© RSM Technology 2017