iSeries (AS/400) Power RPG with SQL

Introduction

This course is designed for application development and support staff who need to view, create or modify data in a more flexible way than that afforded by standard RPG operation codes and logic by including standard and dynamic SQL statements.

Who should attend

Application development and support staff.

Pre-requisites

Delegates should have attended the Accesing DB2 with SQL (ISSQL) and RPGIV Programming (ISR4P) or the
RPG/400 Programming (ISRPP) courses prior to attending this course or have the equivalent hands on experience.

Course content

• Data Definition Language.
• Creating SQL data store - CREATE TABLE
• Using Constraints such as : PRIMARY KEY, UNIQIE, FOREIGN KEY, CHECK
• Changing tables and columns - ALTER
• Removing SQL objects - DROP
• Adding Documentation using COMMENT ON & LABEL ON
• Creating SQL views and indexes - CREATE VIEW, INDEX, ALIAS
• Performance considerations
• Catalog and Data Dictionary consideration
• Data control and security overview
  
  
• Including SQL in RPG
• Pre compilation - and pre compiler parameters
• Source code availability and control for Debug
• Syntax rules to embed SQL in RPG and RPGLE
• Syntax rules to embed SQL in free-format RPGLE
• Using host variables
• SQL return codes
• Communication area
• Using SELECT in an RPG program
  .
• Updates with SQL in RPG
• Using SQL DML to manipulate data from RPG
• INSERT INTO
• UPDATE SET
• DELETE FROM
• Maintaining transaction integrity
• Journaling and Commitment Control
• WHENEVER
• SET TRANSACTION
  .
• Embedded SQL commands
• Using a results table - DECLARE CURSOR
• Scrollable cursors
• OPEN CURSOR, CLOSE CURSOR
• Single and multiple row FETCH
• Updating or Deleting current row
  
  
• Dynamic Embedded SQL
• Execute immediate
• Prepare and Execute
• Using parameter markers to control select structure at run time
• Prepare with Cursor
• Open using
• Variable sequencing at run time
• SET OPTION
  

Materials

A course manual with supporting documentation is provided. The course includes practical sessions to use SQL statements against database files on the iSeries.

Schedule