System i: Introduction to Embedded SQL

Course Overview

Although a late arrival on the iSeries, SQL has rapidly become the de-facto standard for database access across all computer systems. This one-day course provides an introduction to using Embedded SQL on the iSeries.

Audience

Application development staff needing to view, create or modify test data quickly and simply, and need to embed SQL in to an RPG programming language.

Skills Gained

On completion of this course, you will be able to:

• Creating a database with SQL
• Data definition language
• Creating views and index's
• Performance considerations
• Including SQL in RPG
• Pre-compiling
• Using host variables
• Using select in RPG
• Return codes
• File maintenance using Embedded SQL
• Error trap - whenever
• Selecting multiple rows with SQL (Fetch)
• Using an SQL cursor
• File maintenance with SQL cursor
• Dynamic SQL introduction
• Preparing/Declaring SQL statements
• Variable sequencing
• SQL communication area
• Data control & Security
• Journalling and commitment control

Prerequisites

Delegates attending this course should attend the iSeries Interactive SQL and RPGIV or RPG/400 Programming courses or have the equivalent working knowledge.

Course Outline

• Data Definition Language
• Create Database/Create Collection
• Create Table
• Referential Constrains
• Trigger Programs
• Comment On/Label On
• Drop/Views
• Data Modelling and Indexes for the Data Model
• Using and Controlling Indexes
• Creating/Dropping Indexes
• Indexes on Non-Key Fields
• Indexes for Performance
• General Performance Consideration
• Embedded SQL
• Pre-compiling and Running Programs
• Syntax rules for using SQL in RPG
• Using host variables
• Using SELECT in RPG
• SQL Return Codes
• SQL commands for file maintenance
• Whenever Statement
• Accessing Rows from the Results Table
• Fetch
• Embedded code example
• Updating/deleting rows
• Dynamic SQL
• Execute Immediate
• Prepare and Execute
• Prepare a Select Statement and Fetch results
• Open Using
• Variable sequencing

System i: Introduction to Embedded SQL / ISSQE (ISSQEGB)