Start_With_RPLG

What will you learn?

Description

This practical workshop is open for developers with or without SQL experience.

Theoretical explications are alternated with practical exercises using Access Client Solutions on your own IBM i environment, this allowing you to use the data you are familiar with for the exercises.

You will learn how to run SQL scripts via Access Client Solutions as well best practices for setting options and connection properties.

The first exercises cover the SQL basics, allowing you to familiarize with the DB2 for i SQL syntax.

Then the exercises gradually build up to cover the complete syntax of the SELECT statement including commonly used SQL functions, joining, grouping, subselects and Common Table Expressions.

Examples are also provided for the INSERT, UPDATE and DELETE statements and for creating tables, views and indexes.

After a chapter about best practices  for SQL performance, you learn how to use the database part of Access Client Solutions, as well as the functions in the SQL Performance Center allowing to check and analyze the SQL performance and the Index Advice.

Learning objectives

  • Learn the SQL basics and commonly used SQL functions
  • Learn how to use joining, grouping, subselects and Common Table Expressions
  • Learn to use the INSERT, UPDATE and DELETE statements
  • Learn DB2 for i best practices for creating tables, views and indexes
  • Get familiar with the DB2 for i SQL syntax
  • Change connection parameters and run SQL scripts via Access Client Solutions
  • Learn how to use the database part of Access Client Solutions
  • Understand SQL performance and learn how to use the SQL performance center

This course covers the following topics

  • Part 1: Introduction
  • Part 2: IBM i Access Client Solutions
  • Part 3: SQL basic syntax
  • Part 4: CL commands and Database Metadata
  • Part 5: SELECT statement (+ exercises)
    • SELECT statement – Basics
    • WHERE and ORDER BY clause
    • Single-row functions
    • Joining multiple tables
    • Grouping functions
    • Subselects and Common Table Expressions
  • Part 6: Some advanced SQL functions
  • Part 7: SQL performance and best practices
  • Part 8: INSERT, UPDATE and DELETE rows
  • Part 9: Create tables, views and indexes
  • Part 10: More SQL possibilities
  • Part 11: References

Prerequisites

  • Recent version of Access Client Solutions installed on your PC
  • Connection available to your IBM i environment