Db2 for z/OS: Application Programming Workshop Training in United Arab Emirates

Efficient database programming is essential for developing high-performance applications in IBM Db2 for z/OS environments. Well-designed SQL statements, effective transaction management, and optimised access paths play a vital role in ensuring application reliability and scalability on enterprise mainframe platforms.

Db2 for z/OS: Application Programming Workshop is an intensive five-day hands-on programme that provides developers with the practical knowledge required to build, optimise, and maintain Db2 applications. Participants learn how to write advanced SQL, embed SQL within application programs, analyse execution plans, manage transactions, and implement performance optimisation techniques.

Practical workshops and instructor-led exercises allow learners to apply best practices for developing efficient Db2 applications while gaining a deeper understanding of database architecture and application preparation processes.

The programme is available as classroom training, dedicated on-site delivery, or live virtual instruction.

Participants are recommended to have:

• Experience with COBOL, PL/I, Assembler, or another mainframe programming language
• Basic familiarity with the z/OS operating environment
• Experience using TSO/ISPF
• Working knowledge of JCL

This course is intended for:

• Mainframe Application Developers
• Db2 Application Programmers
• COBOL Developers
• PL/I Developers
• Assembler Programmers
• Db2 Technical Specialists
• z/OS Software Engineers

After completing this course, participants will be able to:

• Execute SQL statements using SPUFI.
• Develop complex SQL queries using joins, subqueries, CASE expressions, and set operations.
• Modify database records with INSERT, UPDATE, DELETE, and MERGE statements.
• Analyse Db2 access paths using the EXPLAIN facility.
• Embed SQL statements into COBOL, PL/I, or Assembler applications.
• Understand Db2 locking mechanisms and concurrency control.
• Apply transaction management techniques using COMMIT and ROLLBACK.
• Prepare Db2 applications through precompile and bind processes.
• Optimise SQL performance using Db2 best practices.

Module 1 – Database Management Fundamentals

• Database management systems
• Hierarchical databases
• Network databases
• Relational database concepts
• Db2 history
• Table structures
• Database architecture comparison

Module 2 – Relational Database Concepts

• Relational theory
• Keys and constraints
• Primary and foreign keys
• Referential integrity
• Entity integrity
• Domain integrity
• Relational algebra
• Constraint management

Module 3 – Db2 System Architecture

• Db2 architecture
• Buffer pools
• EDM pool
• RID pool
• Sort pool
• Db2 objects
• System datasets
• Attachments
• SQL processing

Module 4 – SQL Fundamentals

• SQL language overview
• DDL
• DML
• DCL
• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• COMMIT
• ROLLBACK
• Db2 Optimizer

Module 5 – Working with Db2 Interactive (Db2I)

• Db2I interface
• SPUFI
• DCLGEN
• Program preparation
• Precompile
• Bind and Rebind
• Program execution
• Utilities
• Query Management Facility

Module 6 – SQL Query Development

• SELECT statements
• Filtering
• Sorting
• DISTINCT
• BETWEEN
• IN
• LIKE
• ESCAPE
• Derived columns
• Date and time functions

Module 7 – SQL Functions

• Aggregate functions
• Scalar functions
• GROUP BY
• HAVING
• Data conversion
• Date functions
• Numeric functions
• String functions
• NULL handling
• User-defined functions

Module 8 – Advanced Query Techniques

• Inner joins
• Outer joins
• Self joins
• Multi-table joins
• Nested table expressions

Module 9 – Set Operations and CASE Expressions

• UNION
• UNION ALL
• INTERSECT
• EXCEPT
• CASE expressions

Module 10 – Subqueries

• Simple subqueries
• Correlated subqueries
• EXISTS
• Multi-row subqueries
• Performance considerations

Module 11 – Data Modification

• INSERT
• UPDATE
• DELETE
• MERGE
• Multi-row operations
• Referential integrity

Module 12 – SQL Optimisation

• Db2 Optimizer
• Access path selection
• Index usage
• Table scans
• Filter factors
• Cardinality
• RUNSTATS
• Performance tuning

Module 13 – Using EXPLAIN

• EXPLAIN facility
• PLAN_TABLE
• Access path analysis
• Multi-index processing
• Nested query analysis

Module 14 – Embedded SQL Programming

• Embedded SQL
• Host variables
• SQLCA
• DCLGEN
• NULL processing
• Error handling
• SAVEPOINT
• COMMIT and ROLLBACK

Module 15 – Cursor Processing

• Cursor operations
• Multi-row fetch
• WITH HOLD cursors
• Scrollable cursors
• Read-only cursors
• Updating through cursors

Module 16 – Program Preparation and Execution

• Application preparation
• Precompile
• BIND
• Packages
• Collections
• Plan management
• TSO execution
• IMS execution
• CICS execution

Module 17 – Locking and Concurrency

• Locking mechanisms
• Isolation levels
• Lock compatibility
• Claims and drains
• Lock avoidance
• CURRENTDATA
• ACQUIRE / RELEASE
• Monitoring tools
• Concurrency best practices