COMMON POWERUp 2024

In round 2 of this award-winning session, be prepared to hear Scott Forstie and Carol Woodbury shake up the world of IBM i Security once again, mixing up new SQL concoctions to bedazzle the otherwise boring world of IBM i Security. This menu includes examples of using SQL to find vulnerabilities in your code (aka penetration testing) as well as new (but practical!) ways to use SQL services. Even the most ardent security skeptic will be surprised at how just a sip of SQL can freshen up old IBM i Security processes. Before you know it, you’ll be sipping on the delightful features of SQL and rushing to pour out your current processes that have reached their ‘consume by’ date. A note of caution, however. You may leave a bit shaken (but not stirred) once again!

Learning Objectives:
1. Foundational knowledge of IBM i security
2. Why and how SQL is useful to the Security Officer and beyond
3. Have an idea of where to start and how

Have you recently taken over responsibility for a LPAR? Or are you concerned there is too much “rubbish” on your systems? Use these techniques to identify the most common wastes of space you have, & plan your cleanup efforts.

Learning Objectives:
1. How to identify the big three “wasters” of space
2. Simple ways to clean this up
3. How to monitor that this does not happen again

In this session, I will walk through how to use SQL in your RPG modules/programs. We will talk about static and dynamic concepts, how to write SQL in an RPG module/program, use of host variables, and more.

Learning Objectives:
1. To start using SQL in RPG modules/programs
2. Have examples and then be able to try at home

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

Native file processing has been used for years, showing to be a very strong yet simple way of accessing, analyzing, and maintaining data in IBM i. Time has passed since the appearance of SQL in the platform, and it is still looked by many traditional developers as “too modern, complex, not that easy”. I’ve been working on batch processing optimization, and found that the replacement of traditional file processing with SQL processing has many performance advantages. Also permits the use of many features that can replace part of program logic.

In this session we’ll walk thru some case studies, and show you how SQL can help in optimization issues.

Learning Objectives:
1- Identify potential programs that need to be optimized
2- Learn the basics of replacing native file processing with SQL

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

There are at least 78 security-related resources accessible by SQL; covering 156 security-checks; with another 20 not accessible; for a total of 176. Deliverables are: 10,200+ lines of SQL, with a partition-based implementation worksheet, and a Security Audit spreadsheet; showing each of the individual items, with a “Pass” flag and counters -- pretty enough for your future security audit.

Learning Objectives:
We will start by looking at the results (a single TXT file), and then we will dive into the SQL, and each of the spreadsheets to see how we got here. We won’t review every SP…