Overview

This intensive, five-day, hands-on course has been designed to provide a comprehensive understanding of the Db2 for z/OS Structured Query Language and how it is embedded in application programs. Throughout the course reference will be made to best practices to ensure that the SQL and application programs will run efficiently and effectively.<br>This course is suitable for software developers and programmers working in Db2 for z/OS environments.<br><br>This course is also available for one-company, on-site presentations and for live presentation over the Internet, via the Virtual Classroom Environment service.
Read more

Prerequisites

Attendees must be familiar with a major application programming language, e.g. COBOL, PL/I or Assembler. Previous exposure to the z/OS environment and the ability to use TSO/ISPF and JCL would also be a big advantage.
Read more

Delegates will learn how to

  • use SPUFI to code SQL statements
  • code SQL SELECT statements including those using techniques such as joins, nested table expressions, subqueries, unions and case expressions
  • code SQL INSERT, UPDATE, DELETE and MERGE statements to modify data
  • identify access paths chosen by Db2 by using the EXPLAIN feature
  • embed SQL statements into an application programming language such as COBOL, PL/I or Assembler
  • understand and control the locking strategy used by Db2 to maintain data integrity
  • prepare and execute Db2 applications and understand the use of precompile and bind steps in the preparation process.
Read more

Outline

DBMS Overview

Database Management Systems; Hierarchical database structure; Network database structure; Relational database structure; Database comparison; Table structure; A brief history of Db2.

Relational Theory & Concepts

Dr. E F Codd's 12 rules; The relational model; Structure - tables; Structure - rules for columns and rows; Structure: candidate keys, primary and alternate key, foreign keys; Manipulation: relational algebra, projection, restriction, join, union, intersection, difference, product; Integrity - domain and user-defined integrity; Integrity - entity and referential integrity; Referential integrity - terminology; Tablespace set; Referential integrity - defining constraint; Constraint definition restrictions; Data manipulation restrictions; Catalog entries; Referential Integrity and INSERT; Referential Integrity and UPDATE; Referential Integrity and DELETE; Referential Integrity review.

Db2 System Architecture

Architecture overview; Working Storage Areas; Buffer, Sort, RID & EDM pools; Buffer Pools - 64 bit addressing; EDM pool; Sort pool; RID pool; Db2 Attachments; System datasets & databases; DB2 objects; Object characteristics; SQL overview; Db2 Interactive (Db2I); Basic Db2 operations; Db2 commands; Db2I commands panel.

Introduction to Structured Query Language

Structured Query Language; Data Definition Language; Data Control Language; Data Manipulation Language: SELECT, INSERT, UPDATE, DELETE, MERGE; COMMIT / ROLLBACK; DB2 optimizer..

Introduction to Db2 Interactive (Db2i)

Db2I primary option menu; Db2i option D - DB2i defaults; Db2i option 1 - SPUFI; Current SPUFI defaults; SPUFI SQL statement creation; Browsing SPUFI output; SPUFI commit or rollback panel; SPUFI AUTOCOMMIT options; Db2i option 2 - DCLGEN (Declarations Generator); DCLGEN output; Db2i option 3 - Program Preparation; Db2i option 4 - Precompile; Db2i option 5 - Bind / Rebind / Free; Db2i option 6 - Run; Db2i option 7 - DB2 commands; Db2i option 8 - Db2 utilities; Query Management Facility; Reporting options - SPUFI versus QMF 154; SPUFI report; QMF report.

Basic SELECT Statements

Supplied database structure; Supplied table formats; Specifying table names; Using view, alias or synonym; Specifying column names; Selecting all columns; |Column sequence; Naming columns; Eliminating duplicate rows; Row sequence; Row restriction; Comparing columns from the same row; Multiple conditions; The BETWEEN operator; The IN operator; The LIKE operator; The SQL ESCAPE character; Searching for apostrophes; Arithmetic in SELECT statements; Arithmetic in the WHERE clause; Naming derived columns; Using literals in the SELECT list; Special registers; The concatenation operator; Date and time columns.

SQL Built-in Functions

Column functions; GROUP BY; HAVING; Rules for GROUP BY / HAVING; Scalar functions; Data conversion functions; Date manipulation functions; Numeric manipulation functions; String manipulation functions; Handling null values; User defined functions.

Joins & Nested Table Expressions

Inner JOIN; Correlation names; A JOIN of three tables; Joining a table to itself; A three-way join of two tables; Cartesian product; Outer join; Full outer join; Full outer join using COALESCE; Left / right outer joins; Nested table expressions - example.

Unions, Exceptions, Intersections & CASE Expressions

UNION, INTERSECT and EXCEPT; UNION and UNION ALL; UNION with named derived result columns; INTERSECT and EXCEPT; CASE expressions; CASE expression formats; CASE expressions in functions; CASE expressions in the WHERE clause.

Subqueries

Simple (non-correlated) subquery; Simple subquery with multiple rows; Subquery with multiple columns; Quantified predicates; Correlated subquery; Tests for existence with correlated subqueries; Performance considerations; Null values and SQL.

Table Modification Statements

.The INSERT statement; Inserting all columns, single row; Inserting a subset of columns, single row; Inserting multiple rows using SELECT; Inserting multiple rows using row set; Inserting using expressions; The UPDATE statement; Updating rows; Updating & case expressions; Updating and subselects; The DELETE statement; Deleting rows; The MERGE statement; Merging rows; Referential Integrity & INSERT; Referential Integrity & UPDATE; Referential Integrity & DELETE.

The Db2 Optimiser and Access Path Selection

Db2 Optimiser; Access path selection; Access paths - index usage; Access paths - direct row access; Access paths - matching index access; Access paths - non-matching index access; Access paths - table(space) scan; Input to the optimiser; Filter factors; Filter factor and clustering; Filter factor and column cardinality; Filter factor and column correlation; Filter factor and column distribution; Influencing the optimiser; Influencing the optimiser - manually adjusting statistics; Influencing the optimiser - using optimisation hints; Influencing the optimiser - modelling production values; Catalog statistics; Updating catalog statistics using RUNSTATS; Statistics columns; RUNSTATS examples.

Db2 EXPLAIN

XPLAIN; EXPLAIN table enhancements; EXPLAIN syntax enhancements; PLAN_TABLE (DB2 version 1); PLAN_TABLE (Versions 2 to 6); PLAN_TABLE (Db2 versions 7 to 11); DSN_STATEMNT_TABLE; DSN_FUNCTION_TABLE; EXPLAIN example 1 (basic access paths); EXPLAIN example 2 (multi-index access); EXPLAIN example 3 (nested queries).

Basic Db2 Application Programming

Embedded SQL; The DECLARE TABLE statement; Host language variables; Host language variable data types; Variable length columns; Variable length columns and LIKE; Handling null values; Setting null values; Extended indicator variables; Using host language structures; Nulls and host language structures; Indicator variables and insertions; COMMIT and ROLLBACK; Explicit COMMIT and ROLLBACK; SAVEPOINTS; Error handling; SQLCODE & SQLWARNn flags; Other SQLCA fields; The Declarations Generator (DCLGEN); DCLGEN output - DECLARE TABLE; DCLGEN output - Host language structure; Including DCLGEN output.

Cursor Processing

Multi-row processing; Cursor processing; Using a cursor to update data; Cursor WITH HOLD; Ambiguous cursors; Read-only cursors; Scrollable cursors; Insensitive scrollable cursors; Sensitive static scrollable cursors; Sensitive dynamic scrollable cursors; Controlling fetch sensitivity; Controlling cursor scrolling; Scrollable cursor review.

Program Preparation & Execution

Processing SQL statements; Program preparation overview; Db2 precompile; Db2 BIND; BIND alternatives - plan only; BIND alternatives - packages; Packages & collections; BIND parameters; Plan management & access path stability; Plan management (RE)BIND parameters; Packages - varying BIND options; Packages - using mirrored tables; Packages - using versions; Program execution - TSO; Program execution - IMS; Program execution - CICS.

Db2 Locking & Concurrency

Controlling concurrent access; Claims and drains; Claim classes; Drains; Utility restrictive states; Transaction locking; Reasons for locking - preventing lost updates; Reasons for locking - preventing reads of uncommitted data; Reasons for locking - allowing repeatable reads of data; Lock control; Lock options - installation parameters; Lock options - tablespace creation; Lock options - SQL statements; Locking hierarchy; Lock compatibility - row and page locks; Lock compatibility - table and tablespace locks; Lock options - BIND parameters; BIND - ACQUIRE and RELEASE parameters; BIND - ISOLATION; BIND - CURRENTDATA; Lock avoidance; Lock avoidance example; Locking and concurrency recommendations; Monitoring locking - DB2 commands; Monitoring locking - LOCKINFO; Monitoring locking - DB2PM / DB2PE reports.

Read more

Why choose QA

Dates & Locations

Frequently asked questions

See all of our FAQs

How can I create an account on myQA.com?

There are a number of ways to create an account. If you are a self-funder, simply select the "Create account" option on the login page.

If you have been booked onto a course by your company, you will receive a confirmation email. From this email, select "Sign into myQA" and you will be taken to the "Create account" page. Complete all of the details and select "Create account".

If you have the booking number you can also go here and select the "I have a booking number" option. Enter the booking reference and your surname. If the details match, you will be taken to the "Create account" page from where you can enter your details and confirm your account.

Find more answers to frequently asked questions in our FAQs: Bookings & Cancellations page.

How do QA’s virtual classroom courses work?

Our virtual classroom courses allow you to access award-winning classroom training, without leaving your home or office. Our learning professionals are specially trained on how to interact with remote attendees and our remote labs ensure all participants can take part in hands-on exercises wherever they are.

We use the WebEx video conferencing platform by Cisco. Before you book, check that you meet the WebEx system requirements and run a test meeting (more details in the link below) to ensure the software is compatible with your firewall settings. If it doesn’t work, try adjusting your settings or contact your IT department about permitting the website.

Learn more about our Virtual Classrooms.

How do QA’s online courses work?

QA online courses, also commonly known as distance learning courses or elearning courses, take the form of interactive software designed for individual learning, but you will also have access to full support from our subject-matter experts for the duration of your course. When you book a QA online learning course you will receive immediate access to it through our e-learning platform and you can start to learn straight away, from any compatible device. Access to the online learning platform is valid for one year from the booking date.

All courses are built around case studies and presented in an engaging format, which includes storytelling elements, video, audio and humour. Every case study is supported by sample documents and a collection of Knowledge Nuggets that provide more in-depth detail on the wider processes.

Learn more about QA’s online courses.

When will I receive my joining instructions?

Joining instructions for QA courses are sent two weeks prior to the course start date, or immediately if the booking is confirmed within this timeframe. For course bookings made via QA but delivered by a third-party supplier, joining instructions are sent to attendees prior to the training course, but timescales vary depending on each supplier’s terms. Read more FAQs.

When will I receive my certificate?

Certificates of Achievement are issued at the end the course, either as a hard copy or via email. Read more here.

Contact Us

Please contact us for more information