COMMON POWERUp 2026

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, the session will review Plan Cache related tools & services that can help you identify query performance problems.

Learning Objectives:

Understanding of how Db2 uses the SQL Plan Cache Learn how to use Plan Cache tooling to identify and remedy query performance problems.

Obtaining information from external systems or data servers has come a long way since the introduction of EDI in the 1980s! Today, heaps of publicly available web service APIs offer a much more flexible and easier-to-integrate method for pulling pertinent data into your IBM i applications. Whether it be commodity prices, financial data, current weather information, demographic statistics or whatever else you can think of, there's no shortage of information available on the internet that could complement portions of your company's business, adding valuable real-time information to your user applications.

In this session we'll put this concept to work in a hands-on fashion. The presenter will use real-world examples to demonstrate how IBM i developers can quickly find and access public web service APIs to pull data into an RPG program or UI tool using SQL.

Attendees will see three simple 5250-based interactive examples in action, along with source code they can try on their own systems.

Learning Objectives:
- Introduce the concept of pulling data from external web services, and how best to find them
- Understand the difference (and benefits) of using SQL to pull data from RESTful web services, versus "old school" SOAP-based ones
- Master the steps required to integrate a web service-consuming SQL statement into an RPG program or dashboard tool

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 (and get free code).

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.

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.

SQL has become very powerful. It includes for example recursions, multi-dimensional grouping, OLAP specifications, IFS and Webservice access ... But not everybody will be able to code those complex queries ... and this is not necessary as long as the average programmer or analyst can easily use the queries. With SQL Views complexity can be hidden. Since a SQL View can be used in a SELECT statement like any table even beginners can write reports based on complex SELECT statements. Sometimes information has to be set in the stored SELECT statement. With SQL global variables, values can be set on a job/connection level ... and SQL global variables can be specified within SQL views. How ever, sometimes more than one step is needed to retrieve the desired data. In this situation User Defined Functions (UDFs) or User Defined Table Functions come into play.

Learning Objectives:

In this session you will learn how to generate SQL Views. A lot of examples will demonstrate how even complex queries can be hidden. You will also learn how to call and write User Defined Table Functions

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:

Learn how to code in SQL PL (Procedural Language) from an RPG programmer perspective. You know how write an RPG IF, CASE, SELECT, loop, etc. but what’s the SQL PL equivalent.

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:

Understanding of index maintenance considerations. Introduction to tools help you to determine the value of index.

The RUNSQLSTM command lets you run multiple SQL statements in a single call, like a script. You can do even more now by calling CL commands directly. See how this can expand your options.

Learning Objectives:

1. Learn how to use the RUNSQLSTM command in CL programs 2. Learn how to call CL commands inside RUNSQLSTM scripts 3. Learn how to handle errors raised when using RUNSQLSTM

Have you ever wanted the ability to see which records were affected by an Insert, Update, or Delete statement? Data Change Table References (FINAL/OLD/NEW Table) statements were created for this exact purpose. Join me in this session to learn how to use these statements.Learning Objectives:

1. Learn how and when to use an SQL FINAL/OLD/NEW Table statement