Sessions - SQL and Database

2018 sessions will include:

  • Advanced SQL DDL - More Than Just Physical Files
  • Db2 for i SQL Routines (Triggers, Functions and Procedures) Overview
  • Expanding and Integrating the World of Data
  • Future Data Architectures: What is Polyglot Persistence?
  • Intro to Db2 i SQL Performance Tools and Methods
  • SQL Writing Tips and Techniques
  • Using Db2 i with XML and JSON
  • SQL Easy as MAGIC!
  • Embedded SQL: Beyond the Basics
  • Embedded SQL: The Basics 
  • SQL Aggregation without Aggravation
  • The SQL Procedures Language in a Nutshell
  • Thirty SQL Tips in Sixty Minutes

NEUGC 2018 Sessions by Track – SQL and Database


Embedded SQL: The Basics (Susan Gantner)

Many RPG programmers have used interactive SQL as a tool to quickly browse data or to create test data scenarios, but have stopped short of embedding it into their RPG programs.  Come to this session to learn why, when and how you may want to use SQL in conjunction with RPG. We will cover the basics of embedding SQL into RPG, including using SQL cursors and condition/exception handling.


Prerequisite:  This session assumes you are familiar with the basics of the SQL language, such as the SQL SELECT and UPDATE statements.

Embedded SQL: Beyond the Basics (Susan Gantner)

In this session, we'll move beyond the basics of embedding SQL into RPG programs, using SQL cursors and return codes for condition handling. Here we'll look at topics such as using the SET OPTION statement to control special SQL compile options, multi-row fetch and insert and dealing with Nulls. We’ll also explore using SQL functions and registers even without accessing data from a table (aka, file). We’ll compare and contrast static and dynamic SQL and determine the best uses for each.


This session assumes attendance at "Embedding SQL: The Basics" or equivalent knowledge.


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

Thirty SQL Tips in Sixty Minutes (Ted Holt)

SQL is THE data access language. It runs interactively from green screens and System i Navigator. It's in RPG and COBOL programs, in ODBC and JDBC. It's ubiquitous, and it's everywhere, too! You can't know too much about SQL. In this session, Ted Holt presents an assortment of powerful, effective, and easily implemented SQL tips and techniques. 

These tips and techniques are based on DB2 for i, but many of them apply equally to other platforms. There's something for everyone, from the SQL novice to the advanced user. Come learn some new techniques and reacquaint yourself with some you may have forgotten.

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 Aggregation without Aggravation (Ted Holt)

SQL provides powerful ways to retrieve detailed data and format it the way users need to see it. SQL can summarize data, giving users the information they need to make informed decisions. But what do you do if the user asks for both detailed and summary information in the same query? In this presentation, Ted shares techniques you can use to combine details and summaries to satisfy the needs of those whose jobs depend on your system. 

Topics include: 

1. The necessity of UNION ALL 

2. Extensions to GROUP BY 

3. How to "roll" totals by hand and when you should do so 

4. How to count. (Yes, you read that correctly.)

SQL Writing Tips and Techniques (Rob Bestgen)

SQL is a very powerful language for processing data. There are many ways to write SQL statements to get the desired results. However, not all queries are created equal. 

This session will compare and contrast different ways to approach a problem from an SQL perspective. Whether it is writing queries or procedures, taking the right approach for writing SQL can help the database help you.

Expanding and integrating the world of data (Rob Bestgen)

There's a world of data out there that can improve and integrate with your own business data. Come to this session to see how the Web, including Watson, can be available to you via Db2 for i and SQL.

Advanced SQL DDL - More than just physical files (Rob Bestgen)

Physical and logical files are the bread and butter of databases. But SQL supports other objects that can be very useful in simplifying and extended capabilities. The session will several topics including views, alias, UDTFs, three part naming and more.

Future Data Architectures, What is Polyglot Persistence (Rob Bestgen)

While relational Databases are not going away, it may be best to use additional data stores for different kinds of data. The world of Data has expanded past what is in your current Db2 for i. This session defines polyglot persistence and looks at various other data stores that you might use and incorporate into your data server architecture.

Db2 for i SQL Routines (Triggers, Functions and Procedures) Overview

(Rob Bestgen)

Overview of the Persistent Stored Module standard on Db2 i. Various attributes and settings used on routines, such as adopted authority and SQL VS system naming will be included.

Intro to Db2 i SQL Performance Tools and Methods (Rob Bestgen)

This session will provide an overview of the DB2 SQL performance tools and methods included in IBM i. The presentation will provide particular focus on obtaining value from the index advisor and SQL plan cache - both fully autonomic monitoring and analysis mechanisms available to the database engineer.

Using Db2 i with XML and JSON (Rob Bestgen)

Beyond the jargon of terms, using Db2 to process XML and JSON data... and why.