Getting Started with SQL on IBM i Bootcamp

image



SQL is the standardized language for defining and manipulating data in a relational database. SQL is the recommended interface from applications into your data on IBM i. And SQL views can access and alter system services procedures, and functions on IBM i.

In short, it is imperative to know SQL.

This bootcamp defines Structured Query Language (SQL) as supported by Db2 for IBM i. It contains basic reference information for the tasks of system administration, database administration, application programming, and operation.

This bootcamp consists of the following sections:

  • The Power of SQL on IBM i, Scott Forstie
  • SQL DDL – Create Table – Basics, Sue Romano
  • SELECT Statement Basics, Vern Hamberg
  • Scalar Functions & Date/Time Calculation, Birgitta Hauser
  • Data Manipulation, Sven Jansson
  • Group By / Having, Mike Zaringhalam
  • Joining Tables, Ryan Moeller
  • Next Steps - A Little Bit Beyond the Basics, Scott Forstie

All sessions will appear on the CONTENT tab of this page at 10:00 am CT on the date indicated.  Register now and return here on the premier dates to access the content.

  • The Power of SQL on IBM i — Scott Forstie
    Contains 1 Component(s)

    Session 1 of Getting Started with SQL on IBM i Bootcamp. What is SQL and why is it important?

    • What is SQL
    • Why SQL is important
      • Integrated in o/s
      • SQL services
    • What are the parts?
      • DDL
      • DML
      • Programming language
    • What you can access with SQL (High level overview)
      • JSON/XML
      • IFS access
      • Accessing web services
    • Terminology differences
      • File
      • Table
    • Database objects
      • Schema
        • Table
        • View
        • Indexes
          • Why they are necessary
      • SQL routines
    • Insert from examples
    • Where to go for all documentation (IBM sources and COMMON LMS sources), word of warning of Google searches

    Scott Forstie

    Db2 for i Business Architect

    Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Business Architect. He has worked on IBM operating system development since joining IBM in 1989.

    Scott is a frequently published author, speaker at industry events around the globe and can be found @Forstie_IBMi and forstie@us.ibm.com. Scott is an avid runner and enjoys cooking.

  • SQL DDL – Create Table – Basics — Sue Romano
    Contains 2 Component(s)

    Session 2 of Getting Started with SQL on IBM i. SQL DDL – Create Table – Basics

    • Create schema
      • Difference between schema and library
    • SQL Table versus DDS Described physical file
    • CREATE OR REPLACE TABLE
      • SQL Data Types
      • NULL Values / Default Values
      • Identity Column
      • Primary and Unique Key Constraints
    • INSERT data into table (revisit INSERT session 4)
    • ACS schemas
      • Show content of schema
      • Show definition of table
    • Reverse Engineering (Retrieving the SQL Code for a database object)
    • Modifying an existing Table (ALTER TABLE / CREATE OR REPLACE).

    Sue Romano

    IBM

    Sue Romano is a technical leader on the Db2 for i development team and has worked on the SQL parser for almost 30 years. She is involved in designing and implementing SQL enhancements and defines new SQL services. Sue represents Db2 for i on IBM’s SQL Language Council, which ensures consistent SQL implementation across the Db2 family.

  • SELECT Statement Basics — Vern Hamberg
    Contains 1 Component(s)

    Session 3 of Getting Started with SQL on IBM i. SELECT Statement Basics

    • SELECT Statement – Structure
    • ACS Run SQL Scripts – Introduction / Basics
      • SQL Naming versus System Naming in Brief
    • Running Basic SQL Statements (SELECT * FROM)
    • Generating new Columns
    • Numeric: Basic Mathematical Operations +-*/  Quantity*Price  
    • Character: CONCAT FirstName concat ‘ ‘ concat LastName
    • Where Conditions
      • Selecting numeric, character and/or Date/Time Values
      • Logical Operators (AND /  OR / NOT)
      • Comparison Operators
      • Using Parenthesis
    • Predicates (IN /  BETWEEN / LIKE)
    • Order By
    • Save results

    Vern Hamberg

    Senior IT Developer, Ecolab

    Vern has worked on IBM midrange systems for over 30 years, beginning with the System/38 and AS/400. At present he is a Senior Software Engineer for the Pest Elimination division at Ecolab. He also serves on the COMMON Americas Advisory Council (CAAC) and SET and is one of the SMEs for Db2.  He is degreed in both music and mathematics, which gives him a distinct approach to the art and science of programming. In his spare time he has participated in various musical pursuits, such as playing trumpet in a community band, singing in a pretty-good a capella choral group, working with local musical theater productions, and most recently playing keyboard in the Zuhrah Shrine Flames big band and accordion in a weekly jazz workshop - quite a stretch!

  • Scalar Functions & Date/Time Calculation — Birgitta Hauser
    Contains 1 Component(s)

    Session 4 of of Getting Started with SQL on IBM i. Scalar Functions & Date/Time Calculation.

    • Scalar Functions
      • Scalar Functions for String handling (UPPER, LOWER, TRIM, SUBSTR, TRANSLATE, LOCATE)
        • Solving Problems:
          • Sorting Character Columns (case insensitive – UPPER/LOWER)
          • Determining rows with invalid numeric characters in a character field (TRANSLATE)
          • Converting or Replacing invalid characters (for example due to a wrong CCSID – TRANSLATE, REPLACE)
          • Left-/Right adjusting texts (TRIM, SPACE, REPEAT, RIGHT, LPAD, RPAD)
          • Splitting Columns (FirstName, LastName separated by a comma – SUBSTR, LOCATE)
      • Pitfalls when calculating with Integer Values/Columns (especially when dividing integer numbers)
      • Rounding Functions
        • ROUND – Rounding to decimal positions, rounding to 100, 1000 etc.
        • CEILING/FLOOR Rounding up or down to the next integer.
          • Example: Packing pallets – 20 Boxes on a pallet – for 102 Boxes 6 pallets and not 5 are needed
      • Converting between Data Types (CAST)
    • Date & Time Calculation
    • Date/Time/Timestamps 3 different data types
    • Converting numeric Date/Time values into real date (and vice versa)
    • How to convert a date like Monday 11-SEP-23 19:43 into a real timestamp?
    • Adding time units (Days, Hours, …) to Dates/Times/Timestamps
      • Adding/subtracting the same number of months may not return the start date
    • Subtracting two date/time values:
      • Why is the result ‘2023-09-10’ – ‘2023-08-08’ 102?
      • How to get the difference in Minutes or Days?
    • Date/Time Functions: DayName, Week_ISO, Last_Day, First_Day, Next_Day
    • Dimension Table

    Birgitta Hauser

    Independent Consultant

    Birgitta Hauser graduated with a business economics diploma and started programming on the AS/400 in 1992. She is an experienced RPG programmer as well as a database and software engineer, focusing on IBM i application and database modernization. Today, she is an independent consultant on application and database modernization on IBM i and Db2 for i. 

    Birgitta also works in education as a trainer for RPG and SQL developers. Since 2002, she has been a frequent speaker at COMMON user groups meetings and other IBM i and Power conferences in Germany, other European countries, the United States, and Canada. In addition, she is co-author of two IBM Redbooks and has also authored several articles and papers focusing on RPG and SQL for the ITP Verlag (a German publisher), IT Jungle Guru, and IBM DeveloperWorks. 

    She received the John Earl Speaker Scholarship Award in 2015, the Al Barsa Memorial Scholarship Award in 2018, and was named an IBM Champion in 2021.


  • Data Manipulation — Sven Jansson
    Contains 1 Component(s)

    Session 5 of of Getting Started with SQL on IBM i. Data Manipulation

    • Commitment / Transaction Control Introduction 
    • INSERT
      • Insert a single (or multiple rows) by column listing
      • Insert rows based on a SELECT Statement
    • UPDATE
      • Update a single Value / multiple Values / Single Row / Multiple Rows
      • Update a Table from another table
    • DELETE
      • Delete a Single Row / multiple Rows
      • Delete based on a SELECT Statement
    • Using LIMIT (and OFFSET) in composition with INSERT/UPDATE/DELETE

    Sven Jansson

    Senior Developer

    Apper Systems AB

    I have worked on the IBM i platform since 1995. My technical roles include developer, analyst, database specialist, application security, and communications. I also worked on IBMz for a few years, and I serve on the COMMON Sweden board. I'm a curious person who always wants to learn new things. I enjoy participating in basketball and hockey as a player, referee, coach, and equipment manager. My big passion is SCUBA diving to explore wrecks in the Baltic Sea. When I'm not behind a laptop, I'm out on the water, either sailing or diving, and I have a weakness for ice cream.

  • Group By / Having — Mike Zaringhalam
    Contains 1 Component(s)

    Session 6 of of Getting Started with SQL on IBM i. Group By / Having

    • How grouping works
    • Group By
    • Aggregate Functions (SUM(), AVG(), MIN(), MAX(), COUNT() …)
    • Having

    Mike

    Zaringhalam

    PC Richard & Son

    Michael Zaringhalam, based in Massapequa, NY, US, is currently a Software Engineer and DBA at PC Richard & Son

  • Joining Tables — Ryan Moeller
    Contains 1 Component(s)

    Session 7 of of Getting Started with SQL on IBM i. Joining Tables

    •  Syntax (multiple Key Columns)
    • Dependencies among Tables
    •  Inner Joins
    • Cross Join
    • Self Joins
    • NULL Values
      • Scalar Function for converting NULL Values (COALESCE(), NULLIF())
    • Left/Right Outer Join
    • Left/Right Exception Join
    • Full Join

    Ryan Moeller

    Software Engineer

    IBM

    Ryan Moeller is a software engineer working on Db2 for i, specifically on the SQL Query Engine (SQE) team. He joined the team in the Rochester, MN development lab in 2020. Ryan specializes in SQL, database development, and query performance analysis.

  • Next Steps: A Little Bit Beyond the Basics — Scott Forstie & Sue Romano
    Contains 2 Component(s)

    Session 8 of of Getting Started with SQL on IBM i. Next Steps

    • Unions
    • Nested subselects
    • Common table expressions
    • Commitment / Transaction Control 
      • Journaling
    • Views
    • High level SQL Performance

    Scott Forstie

    Db2 for i Business Architect

    Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Business Architect. He has worked on IBM operating system development since joining IBM in 1989.

    Scott is a frequently published author, speaker at industry events around the globe and can be found @Forstie_IBMi and forstie@us.ibm.com. Scott is an avid runner and enjoys cooking.

    Sue Romano

    IBM

    Sue Romano is a technical leader on the Db2 for i development team and has worked on the SQL parser for almost 30 years. She is involved in designing and implementing SQL enhancements and defines new SQL services. Sue represents Db2 for i on IBM’s SQL Language Council, which ensures consistent SQL implementation across the Db2 family.