Sessions - SQL and Database

2019 sessions will include:
Creating JSON data with SQL (SQL and JSON – Part 1) 
Consuming JSON data with SQL (SQL and JSON – Part 2) 
Embedded SQL - An Introduction
Tips and Tricks for Advanced Embedded SQL
Let’s join together – Joining SQL Tables and Views
OLAP Functions – much more than running NumbersCreating JSON data with SQL (SQL and JSON – Part 1) 
  • Creating JSON Data with SQL (SQL and JSON – Part 1) 

  • Consuming JSON Data with SQL (SQL and JSON – Part 2) 

  • Embedded SQL - An Introduction

  • Tips and Tricks for Advanced Embedded SQL

  • Let’s join together – Joining SQL Tables and Views

  • OLAP Functions – Much More Than Running Numbers

  • The SQL Procedures Language in a Nutshell

  • SQL Easy as MAGIC!

  • SQL in CL? Why not?!

  • Getting Started with Db2 SQL Stored Procedures

NEUGC 2019 Sessions by Track – SQL and Database


Creating JSON Data with SQL: SQL and JSON – Part 1 (Birgitta Hauser)

JSON (Java Script Online Notation) is a data exchange format like XML, however JSON is simpler, less verbose and can be faster parsed than XML. In this way JSON becomes more and more important especially when data is exchanged by RESTful web services. The use of XML declines in favor of JSON. With Release 7.3 TR 3 a bunch of scalar and aggregate functions were integrated into SQL, for generating JSON objects, arrays and for combining JSON components into complete documents. The focus of this session is to generate JSON documents.

Consuming JSON Data with SQL: SQL and JSON – Part 2

(Birgitta Hauser)

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 responses returned by web services can be consumed.

Embedded SQL - An Introduction (Birgitta Hauser)

SQL is IBM's relational database language that can be used to query, update, and delete data from the database. SQL is becoming the 'de facto' industry standard for accessing distributed data. Record Level Access can only be used with RPG and Cobol, while in all other programming languages data access is performed through SQL. Additionally SQL provides a lot of great features, such as aggregating rows and building sub-totals or updating a bunch of rows with a single statement. SQL also includes various scalar functions for example for date and time calculation, that are not available in RPG or Cobol. Embedded SQL allows SQL commands to be included and executed directly from within RPG or Cobol and thereby profiting from all the powerful SQL features without additional programming.

Tips and Tricks for Advanced Embedded SQL (Birgitta Hauser)

While native I/O can only be used with programming languages such as RPG and COBOL, (embedded) SQL can, or even must, be used by other programming languages. The main goal of embedded SQL is accessing and modifying data in SQL tables. However instead of using embedded SQL in the same way as native I/O, it makes more sense to join multiple tables or views, merge different sub-selects and even aggregate data in a single SQL-statement (Set based thinking). The result can be either processed sequentially or with a single block fetch read into an array data structure. SQL scalar functions can be used directly in embedded SQL without accessing any table or view. Beginning with Release 7.1 result sets returned by stored procedures can be directly consumed with (embedded) SQL. All kinds of documents (for example XML documents and text files) as well as audio and video files can be stored in the IFS or inserted as large objects into SQL tables. With embedded SQL those files cannot only be copied, but also be written into the IFS and directly read from the IFS. In this session you’ll discover these secrets!

Let’s join together – Joining SQL Tables and Views (Birgitta Hauser)

Accessing data with SQL becomes more and more important. For resolving a single request, data located in multiple tables must be filtered, accumulated and/or merged together. In this way, we need to focus our attention on consolidating data split over multiple tables. SQL provides different methods for joining tables and views together, such as inner, outer, exception, cross, and full join. A table or view even can be joined with itself or with the result of a different select statement. The result returned from multiple select statements can be merged and even accumulated in a single SQL statement.

OLAP Functions – Much More Than Running Numbers (Birgitta Hauser)

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.

The SQL Procedures Language in a Nutshell (Ted Holt)

It seems a new programming language sprouts up every week, and each one has its share of evangelists, telling you to learn their language next. Maybe your next language should be one that was designed for databases and is already on your system. 

The SQL Procedures Language is a database-oriented programming language that offers several benefits to IBM i professionals. 

1. SQL PL is an easy and powerful interface to the database. 

2. SQL PL is ideal for data-centric information sytems. 

3. SQL PL builds functions, triggers, stored procedures, and dynamic compound statements. 

4. SQL PL runs on all DB2 platforms, not just DB2 for i. 

In this session, Ted Holt presents an overview of SQL PL. You will get a tour of the syntax of the language and learn how to put it to work on your system.

SQL Easy as MAGIC! (Steve Wolk)

Do you use SQL? Do you use it to its full capabilities? SQL is an amazingly powerful tool, but can seem overwhelming at first. In fact, it can sometimes seem like magic! Come join us for a session on SQL that begins with the basics and builds into very useful techniques you can use every day. Using magic to help explain some key concepts will make this session entertaining as well as educational. Discover how to use SQL not only for data definition and data manipulation, but also as a wonderful ad-hoc query tool. Save time writing reports and get the answers your business needs in seconds. Learn how to easily execute an SQL statement on your PC that will pull data from your IBM i into an Excel spreadsheet! We will cover other practical examples, such as easily processing a job log for the information you need.

Learning Objectives - After participating in this session, attendees should have the skills and tools to:
1. Use SQL to perform both simple and sophisticated ad-hoc queries
2. Create and modify database files using SQL
3. Modify your data quickly and easily without writing any RPG code
4. Set up your own PC to pull data from your IBM i to Excel using SQL

SQL in CL? Why not?! (Ted Holt)

One of the most recent ways that IBM enhanced CL was to add direct support for the execution of SQL statements. Is this necessary, or even desirable, in a job control language? In this session, Ted Holt talks about the ways that CL supports SQL and discusses how you might use that support in your shop. Among the topics presented are the RUNSQLSTM and RUNSQL commands, dynamic compound statements, and DB2 for i Services.

Getting Started with Db2 SQL Stored Procedures  (John Valance)

SQL is often embedded in other languages, such as RPG, PHP, Java, etc. But IBM provides the SQL Procedure Language (SQL/PL), which can be used to code complex or multiple SQL statements into procedures that can be called from a variety of languages and tools. SQL/PL also provides a simple scripting language, allowing you to create variables and perform conditioning logic with looping and other control structures. This can simplify database access code in RPG, PHP, Node.js, Java, and other languages, as well as reporting tools like Crystal Reports, Excel and DB2 Web Query. It also allows you to edit and test SQL statements in a separate editor which is optimized for SQL development. 

In this session we will cover SQL/PL syntax and features, how to create, edit and run SQL procedures from various clients, passing parameters in and out of SPs, procedure signatures, creating User Defined Functions (UDFs), and IBM i considerations. 

Come and learn about the many features and benefits of SQL stored procedures, how to code them, and how to use them in your applications. x