Objectives

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

  • list the benefits of using REXX in a DB2 environment
  • manipulate DB2 data using SQL
  • code SQL requests using REXX to select, insert, update and delete DB2 data
  • identify data integrity issues.

Audience

IT professionals needing to access DB2 data using REXX.

Prerequisites

The ability to code REXX procedures. A basic understanding of Structured Query Language (SQL) would also be advantageous.

Duration

2 days

Outline for DB2 for z/OS: Using REXX

1. Introduction

  • What is REXX?
  • What is DB2?
  • Structured Query Language
  • Accessing DB2 data
  • advantages of REXX.

2. DB2 REXX Language Support

  • The REXX language
  • REXX DB2 language support
  • Controlling REXX DB2 language support interface
  • Connecting and disconnecting REXX procedures to a DB2 subsystem
  • executing SQL statements in a REXX procedure.

3. Introduction to SQL & DB2I

  • Static and dynamic SQL
  • Basic SELECT, INSERT, UPDATE, MERGE and DELETE syntax
  • handling errors
  • the SQL communications area
  • coding and testing SQL using DB2I.

4. Embedding SQL in REXX

  • Basic coding rules
  • REXX clauses
  • DB2 REXX language support
  • SQL coding rules
  • supported SQL statements
  • coding static and dynamic SQL in REXX
  • coding single and multi-row SELECTS in REXX
  • using parameter markers
  • INSERT, UPDATE, MERGE and DELETE in REXX
  • numeric and non-numeric literals
  • basic error handling
  • the SQL Communications Area (SQLCA)
  • SQLCA contents
  • COMMIT and ROLLBACK processing.

5. Other SQL Options

  • DB2 column functions
  • GROUP BY and HAVING clauses
  • DB2 scalar functions
  • using joins
  • using nested table expressions
  • using UNION, INTERSECT and EXCEPT
  • using CASE expressions
  • using subqueries.

6. Advanced Options

  • Null values
  • indicator variables
  • handling null data
  • handling variable length character strings
  • passing values to REXX for inclusion in SQL
  • DB2 data integrity
  • DB2 locking
  • lock isolation
  • controlling isolation level in REXX
  • the SQL Descriptor Area (SQLDA)
  • using SQLDA to retrieve data
  • passing data using the SQLDA
  • using files for input and output.