• 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

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.

• 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

Environment variables are a powerful tool that can be leveraged to change the behavior of applications and processes. These objects are often over looked when it comes to the security of a system. In this iSee we will review all the environment variables, as well as see who is making changes to  them. 

If you use Go Save 21 this iSee is for you! There is a data area on the system that keeps track of the past several GO Save activities.   This iSee will sort of the details of this data area and bring to light the number of saves, and the length of time each save took. Not only useful from the GO Save view, but some interesting SQL on how to parse text data and a little OLAP to help organize this data into something highly readable. 

Short demonstrations of tools, techniques and examples (including working SQL examples!!) to help you solve real problems that are part of your everyday work.  Presented by Scott Forstie and Tim Rowe, IBM

Part of N2i's Getting Started series, this session is on Getting Started with VS Code for IBM i, presented by Liam Allan

• 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).

Collection Services runs on all our systems.  How often do you actually leverage the insights that may lie beneath the surface ? In this iSee we will take a look at some database health things that are easy to see once you unlock the hidden details within the collection services data.