DB2 Development for COBOL / PL1 Programmers

Course

In Edinburgh

£ 1,500 + VAT

Description

  • Type

    Course

  • Location

    Edinburgh (Scotland)

  • Duration

    5 Days

The aim of this Course is to provide the programmer, unfamiliar with DB2, with the necessary skills required to define, access and manipulate DB2 data, either via an application program or using SPUFI. On completion of this Course the student will be able to: set up a DB2 test environment, using correctly defined tables, views, indexes, synonyms and aliases,use both permanent and temporary. Suitable for: This course provides the applications programmer with an in-depth knowledge of the DB2 development process. It is aimed at programmers who need to code or support DB2 application programs written in COBOL or PL1.

Facilities

Location

Start date

Edinburgh (Midlothian/Edinburghshire)
16 St. Mary'S Street, EH1 1SU

Start date

On request

About this course

The delegate should be familiar with the z/OS host environment, together with a working knowledge of either COBOL or PL/I program development using TSO / ISPF.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Objectives
The aim of this Course is to provide the programmer, unfamiliar with DB2, with the necessary skills required to define, access and manipulate DB2 data, either via an application program or using SPUFI. On completion of this Course the student will be able to:

  • set up a DB2 test environment, using correctly defined tables, views, indexes, synonyms and aliases
  • use both permanent and temporary tables
  • use the DB2I toolset, including SPUFI
  • code SQL statements to read and manipulate DB2 data
  • develop, prepare and execute DB2 programs
  • bind packages and plans
  • use non-scrollable and scrollable cursors
  • use multi-row INSERT and FETCH processing
  • describe the locking process used by DB2
  • define and use Referential Integrity
  • use Identity Columns and Sequences
  • produce EXPLAIN output using a PLAN_TABLE
  • use the LOAD utility to populate tables

Course Details

  • INTRODUCTION TO DB2
  • The Relational Model
  • Data Representation
  • The DB2 Environment
  • The DB2 Table
  • Accessing The Data
  • Interfaces to DB2
  • SQL Structure
  • Embedded SQL
  • DB2 Data Relationships
  • Access Path Selection
  • DB2 Table Structure
  • DB2 Data Types
  • DB2 Catalog
  • DB2 Interactive
  • SPUFI
  • Database Design / Data Analysis
  • Overview Of Normalisation
  • First Normal Form
  • Order Form Example
  • Second Normal Form
  • Third Normal Form
  • Check 3nf
  • DATA DEFINITION LANGUAGE
  • The Structure Of DB2 Objects
  • Definition Of DB2 Objects - DDL
  • Database Definition
  • Tablespace / Page Organisation
  • Page Sizes
  • Tablespace Layout
  • Types Of Tablespace
  • Tablespace Definition
  • Table Definition
  • Table Names
  • Copying Table Definitions
  • Rename Table
  • Data Partitioning
  • DB2 Column Types
  • Null Values
  • User Defined Default Values
  • Global Temporary Tables
  • Declared Temporary Tables
  • Declared Temporary Table Considerations
  • Declared Temporary Tables - Comparisons
  • DB2 Views
  • Read Only Views
  • Views - With Check Option
  • Creating A View Of Two Tables
  • DB2 Synonym
  • DB2 Alias
  • The Alter Statements
  • DB2 Indexes
  • Index Organisation - The B Tree Index
  • Index Clustering
  • Non-Unique Indexes
  • Index Definition
  • Partitioning Indexes
  • Index Design Considerations
  • The Drop Statement
  • DB2 INTERACTIVE (DB2I)
  • DB2I Options
  • SPUFI - SQL Processor Using File Input
  • Running Queries
  • SPUFI Defaults
  • SPUFI - Setting Autocommit to NO
  • DB2 Commands
  • DB2 Utilities
  • The DB2I Defaults Panel
  • UTILITIES
  • Introduction
  • Utility Overview
  • Running Utilities
  • The LOAD Utility
  • The Load Utility Syntax
  • Online Load Resume
  • Online Load Considerations
  • DATA MANIPULATION LANGUAGE
  • SQL - Structured Query Language
  • DB2 Environments
  • SQL Features
  • SQL Query Results
  • The Select Statement
  • The ''As'' Clause
  • The Where Clause
  • Special Operators
  • Not Operand
  • In Operand
  • Like Operand
  • Between Operand
  • Statements Using Nulls
  • Is Not Distinct From
  • Column Functions
  • Using ''Distinct''
  • Multiple Distinct
  • Group By Clause
  • Expressions / Functions in Group By
  • Having Clause
  • Order By Clause
  • Fetch First ''n'' Rows Only Clause
  • Special Registers (Date, Time, Timestamp And User)
  • Current Date
  • Current Time
  • Current Timestamp
  • User Keyword
  • The Update Statement
  • Update with Subselect
  • The Delete Statement
  • The Insert Statement
  • The Mass Insert Statement
  • Select from Insert
  • Select from Insert Example
  • Scalar Functions
  • Function Examples
  • Date, Time And Timestamp Functions
  • The Case Statement
  • Table Join
  • Outer Joins
  • Outer Join Syntax
  • Joining More Than 2 Tables (using Newer Syntax)
  • Outer Join - Where Clause
  • Nested Table Expression
  • SQL Union
  • Subqueries
  • Subqueries Using In
  • Exists
  • The ''All'' Subquery
  • The ''Any'' Or ''Some'' Subquery
  • Common Table Expressions (V8)
  • Common Table Expression Example
  • Recursive SQL (V8)
  • Recursive SQL Example
  • Recursive SQL - Controlling Depth of Recursion
  • APPLICATION PROGRAMMING
  • DB2 Environments
  • Important Note
  • Development Cycle With DB2
  • SQL Statement Format - Cobol
  • SQL Statement Format - Pl/I
  • Table Declaration
  • SQL Statements Used In Application Programs
  • SQL Include
  • SQL Communication Area
  • Retrieving Data Into Host Variables
  • Ambiguous Host Variables
  • SQL Error Codes
  • The SQLca - SQL Communications Area
  • Decoding The SQLca In A Program
  • Singleton Selects
  • Using A Cursor To Retrieve A Result Set
  • Declare Cursor
  • Open Cursor
  • Fetch A Row
  • Row Update
  • Row Deletion
  • Close Cursor
  • With Hold Option
  • The Optimize Statement
  • Fetch First ''n'' Rows Only Clause
  • Fetch First vs Optimize For
  • Handling Nulls
  • Retrieving System Registers
  • Rowid - Direct Row Access
  • Direct Row Access - Example
  • Table Names In Application Programs
  • Batch Execution
  • SCROLLABLE CURSORS
  • Scrollable Cursors
  • Declaring a Scrollable Cursor
  • Insensitive Cursor
  • Sensitive Static Cursor with Insensitive Fetch
  • Sensitive Static Cursor with Sensitive Fetch
  • Sensitive Dynamic Cursor (V8)
  • Asensitive Cursor
  • Scrollable vs Non-scrollable Cursors
  • Updatable Cursors
  • Declaring a Scrollable Cursor
  • Fetching from a Scrollable Cursor
  • Absolute Fetching Examples
  • Relative Fetching Examples
  • Sensitive Dynamic Cursors - Fetching Rows
  • Sensitive Fetches - Update and Delete Holes
  • Positioned Updates Using A Sensitive Cursor
  • Scrollable Cursor Considerations
  • MULTI-ROW PROCESSING (V8)
  • Multi-row Fetch and Insert
  • New Syntax for Declare Cursor
  • Fetching Rowsets
  • Fetch Examples
  • Host Variable Arrays
  • Catering for Update and Delete Holes
  • Partial Rowsets
  • Rowsets - SQLCA
  • Locking Rowsets
  • Positioned Update
  • Positioned Delete
  • Multi-row Insert
  • Multi-row Insert Syntax
  • Static & Dynamic Insert
  • Get Diagnostics
  • Get Diagnostics - Statement Information
  • Get Diagnostics - Condition Information
  • Get Diagnostics - Connection Information
  • Get Diagnostics - Examples
  • Diagnostic Information for Multi-Row Fetch
  • Get Diagnostics Fetch Example
  • Diagnostic Information for Multi-Row Insert
  • Get Diagnostics Insert Example
  • PLANS AND PACKAGES
  • DB2 Bind
  • Rebind
  • Packages Overview
  • Explanation Of Packages
  • Advantages Of Using Packages
  • Binding Packages
  • Binding Plans
  • Defaults For Binding
  • Binding In Batch
  • Identifying A Collection Within A Program
  • Program Execution In Batch
  • Plan Names In Application Programs
  • IDENTITY COLUMNS AND SEQUENCES (V8)
  • Identity Columns
  • Identity Columns - Examples
  • Identity Column Enhancements
  • Altering Identity Columns
  • Identity Columns - New Version 8 Parameters
  • Identity Columns - Retrieving the Generated Number
  • Identity Columns - Data Sharing Implications
  • Using Identity Columns with the Load Utility
  • Sequences
  • Create Sequence Syntax
  • Sequence Ordering
  • Altering Sequences
  • Dropping Sequences
  • Using Sequences in Applications
  • Sequences - Considerations and Restrictions
  • Sequence Application Examples
  • Sequences and Identity Columns Comparison
  • LOCKING
  • Implications Of Concurrent Processing
  • Ims Resource Lock Manager (Irlm)
  • DB2 Locking Methods
  • The Lock Table Statement
  • Lock Modes
  • When Locks Are Acquired
  • When Locks Are Released
  • Isolation Levels
  • Controlling The Isolation Level At SQL Level
  • Keep Update Locks
  • Unit Of Work In TSO
  • Commit And Rollback
  • Declaring Cursors With Hold
  • CICS Issues
  • IMS Issues
  • Savepoints
  • Savepoint Definition
  • Savepoints - Considerations and Restrictions
  • Transaction Deadlocks
  • Design Considerations
  • REFERENTIAL INTEGRITY
  • What Is Referential Integrity?
  • Parent And Dependent Tables
  • The Primary Key
  • The Foreign Key
  • Referential Constraint Rules
  • Constraint Names
  • More Complex Referential Structures
  • Check Pending Status
  • Resetting Check Pending Status
  • Referential Integrity Access
  • Design Considerations
  • PERFORMANCE AND TUNING
  • The DB2 Explain Function
  • SQL Explain Statement
  • Plan Table Layout
  • Plan Table Column Definitions
  • Predicate Evaluation
  • Indexable Predicates
  • Stage 1 And Stage 2 Predicates
  • Predicate Evaluation Sequence
  • Indexable Stage1 Predicates

Course Environment Development will be performed using:

  • IBM Mainframe

Course Format: The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to develop COBOL or PL/I programs accessing the data held on the DB2 Tables. On completion of this course students will be ready to start the development of application programs accessing DB2 data. The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course. An IBM SQL REFERENCE SUMMARY HANDBOOK is provided free to all attendees as part of the training course.

DB2 Development for COBOL / PL1 Programmers

£ 1,500 + VAT