Profience

DB2 COBOL Application Programming Workshop

Why take this course?

After completing this course, you will be able to
  • execute SQL statements (using SPUFI).
  • write standard Select statements.
  • use SQL functions in your queries.
  • code simple and correlated subselects.
  • code intersection and difference operations using the EXISTS predicate.
  • efficiently code UNION statements.
  • write efficient implicit and explicit Inner Joins between two or more tables.
  • code left, right and full outer joins.
  • code meaningful Cartesian joins and avoid invalid ones.
  • maintain DB2 data using Insert, Update and Delete statements.
  • code SQL Data Manipulation Language (DML) for online and batch programs.
  • prepare and test a program containing SQL.
  • program for DB2's concurrency and referential integrity features.
  • use DB2's security and authorization features, and understand the implications for program execution.
Get all the skills you need to effectively develop DB2 application programs. The course covers all relevant topics from relational concepts through SQL language syntax to the DB2 facilities necessary for developing applications. You will code DB2 SQL statements that give an interactive application access to its data so that information can be added, changed and deleted. You will also change a program that needs access to multiple DB2 tables to produce a report with control breaks.
Who should attend?
COBOL programmers who develop or help develop DB2 applications.
Any prerequisites?
Basic TSO/ISPF skills are helpful, as is a general understanding of application programming.

Dates and Fees

5 Days   ? - $ 1,747 (CAD) ? Up to 3 - $ 3,494 Up to 6 - $ 5,244   ? Plus - $ 874
 
Sorry, no classes are currently scheduled, please contact us to request a class
Contact us to arrange your preferred date and/or in-class training

What Topics are covered?

Relational Database Concepts
  • The Relational Model
  • Structure
  • Keys
  • Integrity
  • Manipulation
  • Data Models
  • Mapping business data
  • Entity types
  • Relationships
  • Attributes
DB2 Components and Features
  • DB2 Objects
  • Database
  • Storage Group
  • Tablespace
  • Table
  • Column
  • Index
  • Indexspace
  • View
  • Synonym
  • Alias
  • Object Dependency
  • Column datatypes
  • Primary key
  • Foreign key
  • Referential integrity
The z/OS DB2 Environment
  • DB2 environment
  • The DB2 subsytem
  • DB2 attachments
  • DB2 connections
  • DB2 access
  • Application development tools
  • The DB2 catalog
  • Using the catalog
  • Catalog tables
  • Security
Introduction to SQL
  • What is SQL?
  • The base language
  • Executing an SQL statement
  • Simple SELECT statement
  • SPUFI Demo
Basic SQL Queries
  • The SELECT Statement
  • The SELECT Clause
  • Selecting Columns
  • Qualifying Column Names
  • Renaming Columns
  • Selecting All Columns
  • Selecting Literals
  • Arithmetic Expression
  • Character Expression
  • Date or Time Expression
  • CASE Expression
  • The DISTINCT Keyword
  • The FROM Clause
  • The WHERE Clause
  • Basic Predicate
  • NOT Keyword
  • Linking Predicates
  • The IN Predicate
  • The BETWEEN Predicate
  • The IS NULL Predicate
  • The IS NOT NULL Predicate
  • The LIKE Predicate
  • Special Registers
  • The ORDER BY Clause
SQL Functions
  • SQL Functions
  • Column Functions
  • The COUNT Function
  • The MIN and MAX Functions
  • The SUM Function
  • The AVG Function
  • The GROUP BY Clause
  • The HAVING Clause
  • Scalar Functions
  • Character Functions
  • SUBSTR
  • LEFT, RIGHT
  • LTRIM, RTRIM, STRIP
  • REPLACE
  • LOWER/LCASE, UPPER/UCASE
  • DIGITS
  • CHAR with Decimal Argument
  • Supported Date and Time Formats
  • CHAR with Date Argument
  • Number Functions
  • INTEGER
  • DECIMAL
  • ROUND, TRUNC
  • MOD, POWER, SQRT
  • RAND
  • Date Functions
  • Time Specific Functions
  • DAYS
  • DATE and TIME
  • Miscellaneous Functions
  • LENGTH
  • COALESCE/VALUE
  • NULLIF
Subselects
  • SELECT within SELECT
  • Subselect Returning a Single Value
  • Subselect Returning Multiple Values
  • Correlated Subselect
  • EXISTS/NOT EXISTS
  • Intersection
  • Difference
Union and Joins
  • UNION
  • UNION Considerations
  • UNION Example
  • CARTESIAN JOIN
  • INNER JOIN
  • Example
  • Using the Inner Join Operator
  • With Local Predicates
  • OUTER JOIN
  • Using the Left Outer Join Operator
  • Using the Right Outer Join Operator
  • Using the Full Outer Join Operator
  • With Local Predicates
Data Maintenance
  • INSERT
    • Single Row Insert
    • Mass Insert
  • UPDATE
    • Updating All Rows
    • Updating Selected Rows
  • DELETE
    • Deleting All Rows
    • Deleting Selected Rows
Program Preparation
  • DB2 Environments
  • Programmed Environment
  • Traditional Program Preparation
  • DB2 Program Preparation
  • Precompile
  • Compile and Link-Edit
  • BIND
  • Using Packages
  • REBIND
  • Preparing Multi-Module Programs
  • Program Execution (TSO)
  • Tools and Aids
  • DB2 Interactive (DB2I)
  • DB2I PRIMARY OPTION MENU
  • DB2I BIND/REBIND/FREE Menu
  • BIND JCL (Job Control Language)
  • BIND PLAN Control Statement
  • BIND Package Control Statement
Application Coding
  • Program Elements
  • Coding SQL Statements
  • Host Variables
  • Defining Host Variables
  • Using Host Variables
  • Host Structures
  • SQLCA
  • Table Declaration
  • DCLGEN
  • DB2 Main Menu
  • DCLGEN Panel
  • DCLGEN Output
  • Workshops Overview
  • SELECT in Programs
  • SELECT INTO
  • SQLCA
  • SQLCA Fields
  • SQL Warning Indicators
  • SQLCODE
  • Checking SQLCODE
  • WHENEVER
  • INSERT, UPDATE, DELETE
  • INSERT
  • UPDATE
  • DELETE
  • Indicator Variables
  • Why Cursors?
  • Elements of CURSOR Processing
  • Statements
  • DECLARE . . . CURSOR
  • OPEN/CLOSE
  • FETCH
  • Cursor Processing Flow
  • Temporary Result Table
  • UPDATE through a Cursor
  • DELETE through a Cursor
  • Application Program Coding
Referential Integrity
  • What is Referential Integrity
  • Implementation Alternatives
  • Enforcing Referential Integrity
  • Defining Referential Integrity
  • Design Considerations
  • Basic Concepts
  • DELETE Rules
  • Operational Implications
  • CHECK
Concurrency Control
  • Why Concurrency Control?
  • DB2 Locking Strategy
  • Lock Size
  • Lock Mode
  • Logical Unit of Work
  • Lock Duration
Security and Authorization
  • Application Security
  • Authorization Id
  • Flavours of the AUTHID
  • Primary AUTHID
  • Secondary AUTHID
  • Current SQLID
  • Data Control Language
  • GRANT
  • REVOKE
  • Ownership Privileges
  • Application Plan
DB2 Batch Processing
  • Program Logic
  • Point of Consistency
  • Rollback
  • Savepoint
  • Execution JCL

Competency Assessment and Certificate

At the request of the person booking, a technical competency assessment will be conducted with the student.
This assessment will be a combination of written and multiple choice questions as well as specific coding tests.
Upon a minimum pass rate of 85%, students will receive a Certificate of Competency instead of the standard certificate of course completion.
Please Contact us for more details or specific needs.