Getting Started with SQL on IBM i Bootcamp
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 will be delivered in semi-weekly presentations, from November 6, 2023 until January 15, 2024.
- The Power of SQL on IBM i, Scott Forstie — 11/6/2023
- SQL DDL – Create Table – Basics, Sue Romano — 11/13/2023
- SELECT Statement Basics, Vern Hamberg — 11/27/2023
- Scalar Functions & Date/Time Calculation, Birgitta Hauser — 12/4/2023
- Data Manipulation, Sven Jansson — 12/11/2023
- Group By / Having, Mike Zaringhalam — 12/18/2023
- Joining Tables, Ryan Moeller — 1/8/2023
- Next Steps - A Little Bit Beyond the Basics, Scott Forstie — 1/15/2024
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.
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?
- Programming language
- What you can access with SQL (High level overview)
- IFS access
- Accessing web services
- Terminology differences
- Database objects
- 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
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 firstname.lastname@example.org. Scott is an avid runner and enjoys cooking.
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 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.
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
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!
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 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.