COMMON POWERUp 2026

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. With IBM i 7.6, IBM continue to enhance our ability to manage the database effectively.

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

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 all 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:

* Learn the syntax of the most commonly used SQL JSON publishing functions * Understand the differences between a standard SQL select statement and an SQL statement constructing a JSON response * See how to integrate these SQL functions into an RPG program

In the new world of mobile data collection by 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