POWERUp 2019

What else can SQL do?

By now everyone is familiar with SQL and using it for database access. Inserting, updating, selecting, and deleting data with SQL statements is something we do regularly now in place of record level access statements.

While it’s possible to spend a lifetime learning better, more efficient, or more clever ways to write SQL select statements, this session is about a handful of DB2 SQL for i features that are sometimes overlooked.
SQL on the IBM i can be used to call web services, and parse JSON data into tables. The Merge statement can be used to combine data into a table while having control over what happens if there are conflicts. CTEs (Common Table Expressions) provide a way to modularize your complicated SQL statements. Lead and Lag functions can pull data from a row prior or after a row in a query providing a way to pull results that would be very complicated without them.

This session will introduce you to each of these concepts and provide some examples of ways these features can be used.

Learning Objectives:

Learn about CTEs and how they can modularize your elaborate SQL statements Explore lead and lag and an advanced way to collect data before or after it happens. Combine data with Merge. Use sql to call web services and parse JSON.