COMMON POWERUp 2024

Attending this event?

COMMON POWERUp 2024

THE BEST IBM i & POWER SYSTEMS EDUCATION.When you come to POWERUp, you’ll be able to customize your experience. Choose from 300 sessions, covering over 20 areas of IT, when you plan your schedule. Visit the largest Expo of its kind and meet the experts behind the solutions. Attend social events to meet renowned speakers and peers alike. This conference is structured to give you the pure education and professional connections needed to best enhance your career. Are you ready to POWERUp?

Exhibitor Hall: https://www.expocad.com/host/fx/common/s24/exfx.html

Exhibitor Hall Map Here

10:15am CDT

105: What's New in Db2 for i

In the tradition of previous Technology Refreshes (TR), the database team is delivering enhancements on the same cadence. Attend this session to hear about the many enhancements to SQL on i, SQL Query Engine, IBM i Services and more. This session will cover the new and enhanced Db2 for i capabilities added to IBM i 7.4 and IBM i 7.5 over the most recent IBM i Technology Refreshes.

This session will include something for everyone and anyone who uses the IBM i.

Resources: https://www.ibm.com/support/pages/node/1119129 and http://ibm.biz/Db2foriSQLTutor

Learning Objectives:

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

What's New in Db2 for i PowerUP May 2024 pdf

Monday May 20, 2024 10:15am - 11:15am CDT
Meeting Room 102 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Award BRONZE
Audience All comers

11:30am CDT

179: SQL and IFS - Reading/Writng IFS Files with SQL

SQL is great for accessing data located in a relational database. But in a modern world it is also necessary to consume data that is located in the IFS (Integrated File System) or provided by web services

With each Technology Refresh IBM introduces and enhances Db2 for i Services. With Release 7.4 Technology Refresh 3 IBM provides new SQL Stored Procedures for reading data from and writing Data into the IFS.

In composition with other SQL Functions it is easy to scan through IFS files or decompose *.csv files or JSON files or XML documents located in the IFS.

Learning Objectives:
In this session you’ll learn how to write data to the IFS and how to read IFS files, by using no other programming language than SQL.
It will be also demonstrated how to access and decompose *.csv files located in the IFS without copying them into a physical file or SQL table.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

POWERUp 2024 SQL and IFS Birgitta Hauser pdf

POWERUp 2024 SQL and IFS Birgitta Hauser sql

Monday May 20, 2024 11:30am - 12:30pm CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level Advanced
Audience Developers and users who are experienced in SQL Queries and want to know how to write data into the IFS or have to consume IFS data, or simply want to enhance their SQL know-how.

11:30am CDT

188: SQL Coding Tips and Techniques

SQL is a very powerful language for processing data. There are many ways to write SQL statements to get the desired results. However, not all queries are created equal. This session will compare different ways to approach a problem from an SQL perspective in terms of code maintenance and performance. Taking the right approach for writing SQL helps Db2 for i help you.

Learning Objectives:

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

SQL Coding Tips and Techniques pdf

Monday May 20, 2024 11:30am - 12:30pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level All
Audience Developers

2:45pm CDT

256: SQL Plan Cache Uncovered

This session explains how the SQL Plan Cache is used by Db2 for i to support the execution of your SQL & non-SQL queries. In addition, you'll learn about the various Plan Cache related tools & services that you can leverage to identify and remedy query performance problems.

Learning Objectives:

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

SQL Plan Cache Uncovered pdf

Monday May 20, 2024 2:45pm - 3:45pm CDT
Meeting Room 201C 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Award SILVER

2:45pm CDT

391: Analyzing Geospatial Data Using Db2 for i

In everyday life when we asks questions like “Where is the nearest coffee shop” or “How far away is the airport” we are asking questions about geospatial data. Geospatial data is used to represent objects and events that occur on the surface of the earth. Geospatial analytics allows you to not only store geospatial data representing real location in your database, but to also compare and analyze geographic relationships and trends.

Learning Objectives:
This session will introduce the set of new user-defined geospatial data types, scalar functions, and table functions that make up Geospatial Analytics with Watson and provide many real-world examples of how to use them

Speakers

John Westcott

Db2 for i Consultant and Team Lead, IBM

John Westcott is the Db2 for i Team Lead in IBM Technology Expert Labs. John is highyly skilled in SQL, RPG, Data Analytics, Rest API, and both database and application modernization. John has over 35 years of experience on IBM i and it's past family of names. John has a storied career... Read More →

391 Analyzing Geospatial Data Using Db2 for i pdf

Monday May 20, 2024 2:45pm - 3:45pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Database developer designing applications

4:00pm CDT

173: Getting Started with SQL Functions and Procedures

SQL includes a full-fledged programming language, SQL PL. Come learn about the programming constructs, including error handling, that this language contains. You will leave with all the knowledge you need to start writing your own SQL functions and procedures.

Learning Objectives:
1. Understand the difference between SQL procedures, scalar functions, and table functions.
2. Become familiar with the SQL statements used for writing SQL routine logic.

Speakers

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.

Getting Started with SQL Functions and Procedures pdf

Monday May 20, 2024 4:00pm - 5:00pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level Beginner
Audience Developers who want to start writing SQL-based solutions.

5:15pm CDT

210: What is SYSTOOLS and How Can You Leverage It?

Db2 for i provides useful tools and examples in the SYSTOOLS schema. Come to this session to learn what shiny gems lie within this unopened treasure chest. You need not be an SQL programmer to gain tremendous value from SQL tools!

Learning Objectives:
1. Understand what is SYSTOOLS?
2. Quick training in how to discover and use the tools within SYSTOOLS.
3. Understand how ACS can help you use SYSTOOLS.

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

What is SYSTOOLS and how can you leverage it pdf

Monday May 20, 2024 5:15pm - 5:35pm CDT
Meeting Room 201B 1508 Houston St, Fort Worth, TX 76102

Session Level Advanced
Award GOLD
Audience Anyone using an IBM i

5:15pm CDT

352: Simple Ways to Convert Your DDS Files into DDL Statements

Modernization is more than putting a GUI front-end on your applications. Database modernization should also be included, converting from DDS files to DDL tables, index, & views. All of the tools needed to do this is included with your IBM i & Db2 for I at no additional charge. Why use a third party tool when you can do it for free? Will also include how you can de-create various SQL objects into statements.

Learning Objectives:
1. Learn what tools are available to convert DDS to DDL
2. See example of how to use them
3. Learn how to recover the SQL statement used to create various SQL objects

Speakers

Simon Hutchinson

Author, RPGPGM.COM

Simon Hutchinson has been writing his highly acclaimed IBM i blog, RPGPGM.COM, for eleven years sharing his knowledge, experience, and giving many detailed examples of the latest RPG, SQL, and CL. His work has led him to be recognized by IBM as an IBM Champion for Power and IBM i Community Advocate. He is a subject matter expert for COMMON North America and... Read More →

Simon Hutchinson 352 Simple Ways to Convert Your DDS Files into DDL Statements pdf

Monday May 20, 2024 5:15pm - 5:35pm CDT
Meeting Room 204A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Anyone wanting to modernize their database from traditional DDS files to more modern DDL tables, indexes, & views.

5:50pm CDT

250: Top 10 Reasons to Build Your Own SQL Indexes

In todays world many of us use vendor supplied software. Many of us also are under the edict from manage to 'Not modify the Software'. Many CIOs do not necessairly understand that 'modify' means. This session will provide reasoning for developers, lower level managers can use to make the case for upper mananagement that creating indexes is not modifying the software, and can result in significant performance gains.

Learning Objectives:
1. Learn that adding indexes does not necessarily mean you are modifying packaged software.
2. Learn the most important reasons WHY you should add indexes to packaged software.

Speakers

Tom Davidson

Sr Consultant, Cleindori Consulting

I'm a 40 year veteran of the IBM i (System/38,AS/400, iSeries, IBM i). My background is in Manufacturing and Performance. I specialize is tuning older systems to extend their life.I am also a JDE CNC (18 years), and my second specialty is JDE/IBM i/Weblogic.

Top 10 Reasons to build your own SQL Indexes pdf

Monday May 20, 2024 5:50pm - 6:10pm CDT
Meeting Room 201C 1508 Houston St, Fort Worth, TX 76102

Session Level All
Audience Programmers and DBAs that are trying to improve performance of their applications.

5:50pm CDT

339: 20 SQL Tips in 20 Minutes

In this new session, Scott will take you through 20 tips that will help you be more successful with SQL on IBM i. Rapid fire SQL tips to help you do more and do it better, because #SQLcandoit and so can you!

Learning Objectives:
1. Some SQL statements you may not have noticed
2. Some SQL options you may not have understood
3. Working SQL examples!

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

20 SQL tips in 20 minutes pdf

Monday May 20, 2024 5:50pm - 6:10pm CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level All
Audience Anyone who wants to improve their SQL skills

8:00am CDT

180: Revving up SQL Performance with Indexes

One of the most important things needed to improve SQL performance is to have the proper indexing over tables, especially as table grow in size. Indexes are so important that the database provides index advice for assistance. However, advice is no substitute for knowledge and proper understanding of indexes. As with all advice, it must be reviewed and good judgment applied on when to create them.

Learning Objectives:
1. Learn about the importance of indexes on SQL performance
2. Learn how to use ACS tools to process Db2 Index Advice
3. Learn a methodology for identifying and ordering the key definition for new indexes.

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Revving Up SQL Performance With Indexes pdf

Tuesday May 21, 2024 8:00am - 9:00am CDT
Meeting Room 203B 1508 Houston St, Fort Worth, TX 76102

Session Level Beginner
Audience Database engineers and performance analysts as well as SQL programmers trying to improve performance

8:00am CDT

272: Access MongoDB from IBM i

MongoDB is a database / document management system which arose from the NoSQL movement to become a popular backend for web applications. While MongoDB is, in our opinion, no competition in the RDBMS space for IBM i Db2, Mongo is still fun and easy. You can use Seiden Community PHP+ and/or Python to access remote MongoDB installations from IBM i : we'll show you exactly how.

Learning Objectives:
1. Learn about MongoDB.
2. Learn to access remote MongoDB from IBM i PHP or Python.
3. Learn how to perform typical web application tasks using MongoDB and why you might wish to do so, or find yourself doing so.

Speakers

Jack Woehr

Senior Engineer, Absolute Performance, Inc.

Author of Ublu, an open source remote language for IBM i. A Quantum Computing enthusiast, Jack is an IBM Qiskit Advocate, 2021 IBM Champion, 2022 IBM Champion, 2023 IBM Champion, and 2024 IBM Champion.A consulting programmer with over 30 years' experience in open source specializing in open source based IBM i modernization... Read More →

AccessMongoDBfromIBMi pdf

Tuesday May 21, 2024 8:00am - 9:00am CDT
Meeting Room 201B 1508 Houston St, Fort Worth, TX 76102

Session Level All
Audience Anyone interested in MongoDB, especially in the context of accessing remote MongoDB installations from IBM i.

9:15am CDT

327: Db2 for i Inside of VS Code

VS Code is really built with developers in mind. IBM has been building a neat Visual Studio Code extensions that makes accessing and working with the database much simpler for developers. Not only can VS Code be used for writing in many different languages, but now it has great tools for working with Db2 for i. You might see exporting data, working with examples, browsing your schemas, or even managing multiple database configurations. There's a lot to see!

Learning Objectives:
* How to connect to Db2 for i thru VS Code
* Using content assist for SQL
* How Visual Explain works in VS Code
* Understanding the power of Notebooks

Speakers

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.

VSCode for i Database Extension pdf

Tuesday May 21, 2024 9:15am - 10:15am CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level All
Digital Badge VS Code

10:30am CDT

104: Consuming JSON Documents and Web Services with SQL (SQL and JSON - Part 2)

JSON becomes more and more important especially in RESTful web services. The use of XML declines in favor of JSON. With the JSON_TABLE table function JSON documents can be parsed, converted into a relational representation and data picked specifically out. Additional scalar functions allow SQL to directly access JSON documents located within the IFS (Integrated File System) or in source physical file members. Last but not least IBM provides SQL functions for getting access to web services. In composition with the JSON_TABLE function the JSON answers returned by web services can be consumed.

Learning Objectives:
Learn how JSON documents located in either the IFS or a column in a table or JSON answers from web services can be parsed with the JSON_TABLE table function, converted into a relational representation and data specifically selected. You’ll also get an overview over the new http-functions and how to use them for getting access to web services.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

POWERUp Consuming JSON Birgitta Hauser pdf

POWERUp Consuming JSON Birgitta Hauser sql

Tuesday May 21, 2024 10:30am - 11:30am CDT
Meeting Room 201A 1508 Houston St, Fort Worth, TX 76102

Session Level Advanced
Award BRONZE
Audience Developers and Users who are experienced in SQL Queries and need to process JSON documents located in either SQL tables or the IFS or JSON answers returned by web services or simply want to enhance their SQL know-how.

10:30am CDT

262: Accelerate Your SQL Queries with SMP

With Symmetric Multiprocessing (SMP) now available as a no-charge feature in IBM i, you've gained access to a powerful feature that can significantly improve the speed of query execution. However, SMP must be approached carefully, as improper usage may produce lackluster results. This session will cover the inner workings of SMP, when to utilize SMP, and tools for controlling system-wide SMP usage.

Learning Objectives:
1.) Understand how the SQL Query Engine utilizes SMP to accelerate query execution
2.) Understand what workloads benefit most from SMP
3.) Understand the controls available to the Database Engineer to control SMP

Speakers

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.

Accelerate Your SQL Queries with SMP pdf

Tuesday May 21, 2024 10:30am - 11:30am CDT
Meeting Room 204A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Award GOLD
Audience Database engineers, programmers, application developers

10:30am CDT

264: Partitioned Table Functionality

Did you know that partitioned table functionality is now included in IBM I 7.5? Attend and learn what partitioned tables are and the benefits of using them to organize your data. Q&A will follow a live demo.

Learning Objectives:
participants will be able to use partition tables in their own IBM i shops.

Speakers

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... Read More →

Tuesday May 21, 2024 10:30am - 11:30am CDT
Meeting Room 201C 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience DBE and developers

2:45pm CDT

138: Using SQL to Overcome Challenges when Consuming XML (Case Study)

In this new world of mobile data collection by our field specialists, we ran head-on into special characters as well as emojis. This case study tells the tale of overcoming the hurdles we were stumbling over to deal with them.

Learning Objectives:
1. Learn what issues can be raised when using mobile devices to collect information from the field
2. Learn how XML support in Db2 for i was used to resolve these issues

Speakers

Vern Hamberg

Senior Software Engineer, Ecolab, Pest Elimination

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 of Ecolab. He serves on the COMMON Americas Advisory Council (CAAC) and SET and is one of the SMEs for... Read More →

Using SQL to Overcome Challenges When Consuming XML screenshots 2up pdf

Tuesday May 21, 2024 2:45pm - 3:45pm CDT
Meeting Room 204A 1508 Houston St, Fort Worth, TX 76102

Session Level All
Audience Developers who need to process XML files and web services and others who need to know what can be done with XML support in SQL for IBM i

2:45pm CDT

140: OLAP (Online Analytical Processing) Functions – Much More Than Running Numbers!

The first OLAP Functions were already introduced with Release 6.1, for building running numbers and ranks, in a specific sequence and even with the ability to restart numbering at a predefined level. At release 7.3 a bunch of additional OLAP functions and enhancements for existing OLAP functions are introduced. With these enhancements it is possible to easily build running totals or averages with or without level breaks. Data cannot only be accumulated over all rows, but also within a specific range. It is easy to access and compare the first, previous, next or last occurrence of the data within a partition. This session will give you an overview over the available OLAP functions and how they can be exploited.

Learning Objectives:
In this session you will learn which OLAP functions are available and how they can be used for and how they can be exploited especially for statistical reports.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

POWERUp 2024 OLAP Specifications Birgitta Hauser pdf

POWERUp 2024 OLAP Specifications Birgitta Hauser sql

Tuesday May 21, 2024 2:45pm - 3:45pm CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level Advanced
Audience Manager, Programmers, Database Administrators and Engineers who need to generate and analyze complex statistics or everybody who is just interested in what is possible with OLAP functions.

2:45pm CDT

304: Run Away Queries Got You Down?

Having negative performance impacts from query tools like Query/400, QMF, Showcase, Tableau and others because of the "query from heck" eating up system resources? Is your system open to security exposures from wide-open ODBC type connections? This session will cover options including using the new-ish Db2 Query Supervisor to control and/or audit long-running queries.

Learning Objectives:
1. Learn about the Db2 Supervisor to control and/or audit long running queries.
2. Gain insight into the tools you can use to monitor, tune, and prune queries.

Speakers

John Westcott

Db2 for i Consultant and Team Lead, IBM

John Westcott is the Db2 for i Team Lead in IBM Technology Expert Labs. John is highyly skilled in SQL, RPG, Data Analytics, Rest API, and both database and application modernization. John has over 35 years of experience on IBM i and it's past family of names. John has a storied career... Read More →

304 Run Away Queries Got You Down pdf

Tuesday May 21, 2024 2:45pm - 3:45pm CDT
Meeting Room 201C 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Anyone concened with managing system performance

4:00pm CDT

266: Under the Hood: A Deep Dive into the SQL Query Engine

The SQL Query Engine (SQE) powers (nearly) every query that executes on your system. Since its introduction, SQE has been continually improved to create more efficient access plans, but these inner workings are rarely understood. In this session, Ryan will give audience members a rare peek under the hood of the engine that powers Db2 for i.

Speakers

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.

Under the Hood A Deep Dive Into the SQL Query Engine pdf

Tuesday May 21, 2024 4:00pm - 5:00pm CDT
Meeting Room 204A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Database engineers, database administrators, application developers

4:00pm CDT

303: Temporal Tables: Time Travel Is Cool

Learn how to time travel within your data! How many programs are already doing some type of temporal logic? Would moving to a more data-centric approach reduce your development effort delivering faster and better results? This session explains how to temporalize your existing database, how to manage temporal database, and how to perform temporal analysis.

Learning Objectives:
How to temporalize your existing database.
How to manage temporal databases.
How to perform temporal analysis.

Speakers

John Westcott

Db2 for i Consultant and Team Lead, IBM

John Westcott is the Db2 for i Team Lead in IBM Technology Expert Labs. John is highyly skilled in SQL, RPG, Data Analytics, Rest API, and both database and application modernization. John has over 35 years of experience on IBM i and it's past family of names. John has a storied career... Read More →

303 Temporal Tables Time Travel Is Cool pdf

Tuesday May 21, 2024 4:00pm - 5:00pm CDT
Meeting Room 201C 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Anyone interested in creating a temporal database or performing temporal analysis

5:15pm CDT

251: Performance Tuning, QAQQINI what you care about and how to use it

This session hits the highlights of the file that defines your SQL environment. Did you know that you can have more than one? This session will hit the most important entries of this file. What values you want to use, and how to use it.

Learning Objectives:
1. Learn the most important entries in the file that defines your SQL environment
2. Learn how to use more than the one provided by IBM for your different workloads

Speakers

Tom Davidson

Sr Consultant, Cleindori Consulting

I'm a 40 year veteran of the IBM i (System/38,AS/400, iSeries, IBM i). My background is in Manufacturing and Performance. I specialize is tuning older systems to extend their life.I am also a JDE CNC (18 years), and my second specialty is JDE/IBM i/Weblogic.

QAQQINI What it is and how to use it pdf

Tuesday May 21, 2024 5:15pm - 5:35pm CDT
Meeting Room 203C 1508 Houston St, Fort Worth, TX 76102

Session Level Beginner
Audience Programmers and DBAs that are trying to improve performance of their applications.

5:15pm CDT

294: How Many Indexes is Too Many?

The creation of indexes can have a positive impact on query performance. However, an index also has a cost since it has to be maintained every time rows in the associated table are inserted, deleted and updated. This session will the discuss indexing tradeoffs including the review of a benchmark test created to quantify the positive & negative impacts of indexes. In addition, you will learn about tooling that can be used to identify your most valuable and least valuable indexes.

Learning Objectives:

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

How Many Indexes is Too Many pdf

Tuesday May 21, 2024 5:15pm - 5:35pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level All

5:50pm CDT

260: Geospatial Analytics - What About Performance?

Like any new offering, the Geospatial Analytics support features cool technology with lots of bells and whistles. When integrating a new technology like this into your applications, it's easy to focus just on the functional implementation and to overlook the performance considerations.

Learning Objectives:
In this session, learn how to streamline a Geospatial Analytics solution by leveraging the Geohash functionality and other advanced features.

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Geospatial Analytics What About Performance pdf

Tuesday May 21, 2024 5:50pm - 6:10pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Database developer designing applications

8:00am CDT

349: Looking at Your Data From a Different View Point

Simon will explain how building SQL Views should become a part of your development strategy. He will give examples of the common ways he builds and uses Views to make his own and that of his team easier and simpler. Why do the hard work yourself when something else can do it for you?

Learning Objectives:
1. Why use Views
2. How to create a View
3. Deformalize your data for easy access
4. Create new derived & calculated data within the View

Speakers

Simon Hutchinson

Author, RPGPGM.COM

Simon Hutchinson has been writing his highly acclaimed IBM i blog, RPGPGM.COM, for eleven years sharing his knowledge, experience, and giving many detailed examples of the latest RPG, SQL, and CL. His work has led him to be recognized by IBM as an IBM Champion for Power and IBM i Community Advocate. He is a subject matter expert for COMMON North America and... Read More →

Simon Hutchinson 349 Looking at Your Data From a Different View Point pdf

Wednesday May 22, 2024 8:00am - 9:00am CDT
Room 202 C-D 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience People interested in modernizing their database, people who want to make their programming simpler & easier to understand

9:15am CDT

228: ACS & Db2 for i – Explained

In this new session, you’ll hear the inside scoop about ACS from Ryan & Wayne.

At this session we will explore ACS and have a fun time understanding all the ACS has to offer.

Learning Objectives:
1. Discover the database and SQL feature-rich options within ACS
2. Gain best practices by hearing and seeing two experts discuss and demonstrate ACS

Speakers

Wayne Bowers

Software Engineer, IBM

Wayne Bowers has been with IBM since 1997, where he works with the IBM i Global Support Center in Rochester MN. He is an Advisory Software Engineer specializing in the support of the IBM i Access (aka Client Access) products, Console, the TCP Telnet communications server, and the... Read More →

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.

ACS and Db2 for i Explained pdf

Wednesday May 22, 2024 9:15am - 10:15am CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level Beginner
Audience Anyone who needs to interact with Db2 for i

10:30am CDT

392: API Enable Your Database

How important is delivering business data to your users and customers? I’d bet it’s among the top concerns of any development effort. How do you deliver data in the modern world? Rest API are one of the most common methods. We’ll explore, though live demos, how to expose data in a safe and secure manner using Integrated Web Services (IWS). Our focus will be on directly leveraging the database, not using RPG or Service program.

Learning Objectives:
1. How to exose data using Select, Insert, Update, and Delete statements
2. How to expose data using SQL Stored Procedures

Speakers

John Westcott

Db2 for i Consultant and Team Lead, IBM

John Westcott is the Db2 for i Team Lead in IBM Technology Expert Labs. John is highyly skilled in SQL, RPG, Data Analytics, Rest API, and both database and application modernization. John has over 35 years of experience on IBM i and it's past family of names. John has a storied career... Read More →

392 API Enable Your Database pdf

Wednesday May 22, 2024 10:30am - 11:30am CDT
Meeting Room 201A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience SQL developers delivering data to any part of the business

1:30pm CDT

302: Database Modernization An Evolution not a Revolution

Modernizing applications and neglecting the database? Think converting from DDS to DDL is enough database modernization? It may be enough, although there’s more to the database than the way it’s defined. This session explores database modernization techniques from an evolution perspective for continuous change. And, how that type of database modernization compliments application modernization.

Learning Objectives:

Speakers

John Westcott

Db2 for i Consultant and Team Lead, IBM

John Westcott is the Db2 for i Team Lead in IBM Technology Expert Labs. John is highyly skilled in SQL, RPG, Data Analytics, Rest API, and both database and application modernization. John has over 35 years of experience on IBM i and it's past family of names. John has a storied career... Read More →

302 Database Modernization – An Evolution not Revolution pdf

Wednesday May 22, 2024 1:30pm - 2:30pm CDT
Meeting Room 103A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Anyonme intereted in database moderization

1:30pm CDT

310: Elevate your SQL Capabilities with Lateral Joins, Pivots, and Window Functions

Take your SQL skills to the next level with this session on Lateral joins, Window functions, and Pivoting. Discover how LATERAL joins simplify complex queries, learn new insights with Window Functions, and master the art of pivoting data for dynamic reporting. Whether you're an SQL pro or beginner, join us to unlock the full potential of these powerful tools and techniques.

Learning Objectives:
1. Learn how and when to use the Lateral keyword (along with correlation and cross joins).
2. Learn all the available Window Functions and how/when to use them.
3. Learn what pivoting is and how to setup an SQL procedure to do it dynamically.

Speakers

Mike Zaringhalam

Developer, PC Richard & Son

Have you ever wanted to meet someone who started Hawaiian Shirt Fridays at their work place? If the answer is 'Yes!', then feel free to reach out with any questions you have regarding Hawaiian shirts... Or IBM i related topics. My main focuses are RDi, DB2, Modernization, and D... Read More →

Wednesday May 22, 2024 1:30pm - 2:30pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level Advanced
Audience Anyone looking to further their SQL skillset would benefit from this

2:45pm CDT

187: Streamline your SQL Routines

The SQL procedural language makes it easy to create your business logic in stored procedures, triggers, and user-defined functions. Each of these routines have options that performance, but most programmers usually take the default option values without giving any thought to their impact. Learn about these overlooked performance options and other procedural language coding best practices.

Learning Objectives:

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Streamline SQL Routines pdf

Wednesday May 22, 2024 2:45pm - 3:45pm CDT
Meeting Room 203A 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Developers, data base engineers, performance analysts

2:45pm CDT

351: Simon’s Top Ten SQL

Simon has written a lot about modern Db2 for i and SQL on his blog for the past ten years. In this fast past session he will take you through his top 10 favorites. You will have to attend to learn what these cool SQL tricks and tips are & how they can help you be a SQL super hero! (this will be updated to include any cool new things added to SQL in the intervening time)

Learning Objectives:
1. Get a quick overview of my favorite SQL table functions & views that allow you to get to information you did know you could do
2. Simple examples shown

Speakers

Simon Hutchinson

Author, RPGPGM.COM

Simon Hutchinson has been writing his highly acclaimed IBM i blog, RPGPGM.COM, for eleven years sharing his knowledge, experience, and giving many detailed examples of the latest RPG, SQL, and CL. His work has led him to be recognized by IBM as an IBM Champion for Power and IBM i Community Advocate. He is a subject matter expert for COMMON North America and... Read More →

Simon Hutchinson 351 Simon’s Top Ten SQL pdf

Wednesday May 22, 2024 2:45pm - 3:45pm CDT
Meeting Room 102 1508 Houston St, Fort Worth, TX 76102

Session Level All
Audience Examples anyone can use these examples to maximize their use of SQL to make their lives simpler.

4:00pm CDT

393: Visual Explain - IBM's SQL Performance Show-&-Tell

For many years now IBM have positioned SQL as the primary interface to the database on IBM i. At the same time, new capabilities are being added only to SQL, not to the native database interface.

We need a different tool to understand and analyze query implementation plans and feedback from the optimizer – the SQL component that determines the most effective way to deliver the data we ask for in SQL statements. Visual Explain is just such a tool, and it both shows us graphically what plan the optimizer has chosen, as well as tells us details about each step along the way. It can also give us suggestions on ways to improve performance of our queries.

Learning Objectives:
1. A brief description of optimization with SQL
2. The several ways of invoking Visual Explain
3. The various panes of the Visual Explain window
4. Some features of Visual Explain for managing how it behaves
5. Examples of improved performance after using suggested changes

Speakers

Vern Hamberg

Senior Software Engineer, Ecolab, Pest Elimination

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 of Ecolab. He serves on the COMMON Americas Advisory Council (CAAC) and SET and is one of the SMEs for... Read More →

Wednesday May 22, 2024 4:00pm - 5:00pm CDT
Meeting Room 102 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Digital Badge SQL

5:15pm CDT

346: Database Features in VSCode

Come explore the new Db2 capabilities in VSCode! These features, geared toward the database developer, enable full-stack development on IBM i. Come to this session for a demo and discussion of how to use these new features and why they may be beneficial to your workflows

Learning Objectives:
Understand the new Db2 features in VSCode.

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

Adam Shedivy

Software Developer, IBM

Adam is a software Developer working on enhancing client and open source integration on IBM i.

Wednesday May 22, 2024 5:15pm - 6:15pm CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level Beginner
Digital Badge VS Code
Audience Application Developers or IT Planners

8:00am CDT

269: SQL Error Logging Facility (SELF)

Have you ever been faced with “strange” SQL Errors or SQL Errors that were not trapped but simply ignored? How to find those problems?

SQL Performance Monitors can help, at least if you know where to start, i.e. which job, which program caused the problems. But SQL Performance Monitors come with significant overheads, just by gathering everything.

SELF is an additional tool for finding understanding and fixing SQL Errors and Warnings. SELF can be started without modifying any programs and can safely run in production.

This session will show what are the components of SELF and how to use them.

Learning Objectives:
In this session you’ll get detailed information about SELF, i.e. which Global Variables can be set, which views and functions are available and where the information is logged.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

POWERUp SELF SQL Error Logging Facility Birgitta Hauser pdf

POWERUp SELF SQL Error Logging Facility Birgitta Hauser sql

Thursday May 23, 2024 8:00am - 9:00am CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience Programmers and Analysts who have to detect where and which (SQL) problems occur or simply want to have an overview about how often a specific SQLCODE occurs.

9:15am CDT

242: Fun with JSON Publishing: Letting SQL Do the Dirty Work

If you've ever been tasked as a developer with creating complex responses for an internal or external web service, you've likely explored multiple avenues for generating JSON from your IBM i programs. From constructing responses manually in your RPG code, to using various publicly available toolkits like YAJL, RPG NextGen, etc., there are a myriad of ways to create JSON strings with all the requisite curlies, commas, colons, quotes and hard brackets.

If you're on IBM i OS 7.2 or higher, there's yet another avenue you can explore in which you let SQL do the JSON construction work for you. The biggest benefit of this approach becomes most apparent when your JSON response requires arrays, and especially nested subarrays. Coding such responses can be a bit tedious when done on a record-by-record basis in your program, whereas by using some nifty functions you can can let SQL assemble your JSON response automatically.

In this session we'll review the basics of JSON publishing functions such as JSON_Object, JSON_Array, JSON_ObjectAgg and JSON_ArrayAgg. We'll explore some practical use examples for publishing your business data in JSON, letting SQL do all the dirty work.

Learning Objectives:
1. Learn the syntax of the most commonly used SQL JSON publishing functions
2. Understand the differences between a standard SQL select statement and an SQL statement constructing a JSON response
3. See how to integrate these SQL functions into an RPG program

Speakers

Robert Swanson

Senior Partner, CNX Corporation

Rob has worked as an in-the-trenches IBM i developer since 1992, with the past 15 years focused on developing modernization efforts for legacy systems written in RPG. Currently serving as Senior Partner for CNX Corporation in Chicago, Rob is a strong advocate of introducing highly... Read More →

JSON Publishing Let SQL do the Dirty Work pdf

Thursday May 23, 2024 9:15am - 10:15am CDT
Room 202 A-B 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Digital Badge SQL
Audience Developers looking to create intricate JSON responses for web services or various JavaScript frameworks.

10:30am CDT

427: A New Era of Analytics for IBM i using IBM Cognos

Come here about the powerful reporting and analysis capabilities in IBM Cognos Analytics. Whether you're migrating reporting workload from DB2 WebQuery, or exploring new ways you can empower your team for data analysis and distribution, join this session to get a view into what's possible and discover potential pricing benefits to existing IBM Db2 Web Query customers.

Speakers

Jonah Mudse

Product Management, IBM

Jonah Mudse has devoted his professional life to building software products that customers love. He is a product manager, design thinker, and educator. In 2022, Jonah joined the IBM Business Analytics portfolio - driving innovation for business to be better at business, more often... Read More →

Thursday May 23, 2024 10:30am - 11:30am CDT
Meeting Room 101 1508 Houston St, Fort Worth, TX 76102

Session Level All

1:30pm CDT

259: Simple Steps to Improving Data Security on IBM i

Db2 for i offers a wide range of built in security capabilities allowing you to better secure your data. Learn how to quickly utilize these capabilities to not only understand how to secure your data, but to better secure your data. And, to ensure compliance via auditing.

Learning Objectives:
During this session we will cover a wide range of security and compliance methods including row permissions and column masks used to control access to data and enforce separation of duties, authority collection used to determine the lowest level of security needed by a user, audit journals used to audit the effectiveness of your security solutions, and IBM i services which can be used to quickly accomplish security tasks, including automating security policies. This session will include many examples that you will be able to take home and use right away.

Speakers

John Westcott

Db2 for i Consultant and Team Lead, IBM

John Westcott is the Db2 for i Team Lead in IBM Technology Expert Labs. John is highyly skilled in SQL, RPG, Data Analytics, Rest API, and both database and application modernization. John has over 35 years of experience on IBM i and it's past family of names. John has a storied career... Read More →

259 Simple Steps to Improving Data Security on IBM i pdf

Thursday May 23, 2024 1:30pm - 2:30pm CDT
Meeting Room 203C 1508 Houston St, Fort Worth, TX 76102

Session Level Intermediate
Audience The database engineer looking for security and compliance options.

2:45pm CDT

155: IBM i Services for the Developer

IBM i services provided by Db2 for i can be used to access system information that is useful for developers. In the past you may have considered using CL or coding to system APIs to access and work with some system information. Today there is a better option: SQL. In this session you will learn how to use SQL to work with data areas and data queues, discover how easy it is to review your program and service program attributes, and more.

http://ibm.biz/Db2foriServices

Learning Objectives:

Speakers

Ann Wilkerson

Software Developer, IBM

Ann Wilkerson is the technical team lead for Navigator for i and a developer for IBM i Services.

SQL for the Application Developer pdf

Thursday May 23, 2024 2:45pm - 3:45pm CDT
Meeting Room 102 1508 Houston St, Fort Worth, TX 76102

Session Level All

4:00pm CDT

311: Honey, I Looped the Data: Recursive CTEs

Discover SQL’s incredible capabilities of Common Table Expressions (CTEs) and Recursive CTEs in this enlightening session. Join us as we dive into the world of recursion and learn how Recursive CTEs can help solve complex problems efficiently and elegantly.

Learning Objectives:
1. Learn what Common Table Expressions are and how they are setup.
2. Learn how and when to utilize Recursive CTEs (repeated functionality and cyclical data)

Speakers

Mike Zaringhalam

Developer, PC Richard & Son

Have you ever wanted to meet someone who started Hawaiian Shirt Fridays at their work place? If the answer is 'Yes!', then feel free to reach out with any questions you have regarding Hawaiian shirts... Or IBM i related topics. My main focuses are RDi, DB2, Modernization, and D... Read More →

Thursday May 23, 2024 4:00pm - 5:00pm CDT
Meeting Room 201C 1508 Houston St, Fort Worth, TX 76102

Session Level Advanced
Audience Anyone looking to further their SQL skillset would benefit from this