COMMON POWERUp 2024

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.

Are you grappling with the decision of which cloud strategy suits your needs best? Join us in this session as we navigate the complexities of private, public, and hybrid cloud options. Learn how to tailor your cloud strategy based on workloads, organizational considerations, and application dependencies.

Key Points:

- Workload-Centric Considerations
- Organizational Perspectives
- Application Dependency Insights?

Learning Objectives:
Session Highlights:

Comparative Analysis: Gain insights into the advantages and drawbacks of private, public, and hybrid cloud strategies through a comparative analysis.

Decision-Making Framework: Discover a decision-making framework to help you choose the most suitable cloud strategy for your specific needs.

Real-world Examples: Explore case studies showcasing successful implementations of different cloud strategies.

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.

Network statistics are collected by interface and port for specified systems every 300 seconds. Since clients and servers connect to the system randomly; collecting information over time assures that a good representative sample of actual traffic is detected. This information can be leveraged for Traffic Analysis (for measuring workloads and performance); and for Security Analysis (for determining active ports and services). Three SQL routines will be reviewed.

Learning Objectives:
We will review the Collection routine; which is mostly just running NETSTAT_INFO every 300 seconds….
We will review the Traffic Analysis code; and results.
We will review a simple use for Security Analysis.

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.

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.

In days gone by the IBM Supplied program QSTRUP combined with a couple options on CHGIPLA was good enough for most IBM i shops. It started what seemed to be needed and anything else we stumbled through after each IPL. When it was time to shut down a PWRDWNSYS or ENDSBS command took down whatever was running. We were happy.

Today though we often see dozens of subsytems running many web servers, other needed batch jobs and services. We see monitors and queue processing jobs and many writers. For security purposes we should be starting only the TCP servers that we need, not all of them. Likely we should not start IPV6 and start things in sequence as well.

Further considerations may be needed in an HA environment. These include not starting jobs until iASPS are available and starting different IP interfaces and services depending on production or backup mode of the server. We may want to handle special startup modes while doing PTFs, backups or other system maintenance.

In this session we will work with customizing that startup. We'll discuss staring PASE jobs with QSH, web server jobs and more. We'll discuss prestart jobs and autostart jobs. And finally we will also work with sequencing the shutdown of the system. As part of this we'll discuss some simple ways to know what needs to be shut down and smoothly handling that.

Learning Objectives:
1) Learn how to control which startup program will run on your IBM i.
2) Examine the various servers to start and how to control them.
3) Learn options for starting different services.
4) Consider ways to control the startup of your server in different operting modes.
5) Understanding an orderly shutdown of your IBM i server.

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.

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…

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)