COMMON NAViGATE has ended

Create Your Own Event

COMMON NAViGATE

9:00am EDT

Fun with dates and times in SQL!

Db2 for i includes a lot of powerful scalar functions for date and time calculations, even without calendar table. By using this functions it is easy to determine the day of the week, the week according the ISO guidelines, the month end or the date of the next Wednesday. If we combine this functions, it is possible to determine the date of the first monday in a month, the date of the monday in any week or to calculate the number of working days (monday-friday) between two dates and much more.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

Monday May 24, 2021 9:00am - 9:20am EDT
Breakout 2 / Delaware C-D

9:30am EDT

Calculating Sub-Totals and Totals with SQL

By using the GROUP BY clause in a SQL Statement data can be accumulated, and totals, averages and much more accumulated. By specifying CUBE, ROLLUP and GROUPING SETS in the GROUP BY clause GROUP BY Clause, all kinds of sub-totals and totals can be included.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

Monday May 24, 2021 9:30am - 9:50am EDT
Breakout 2 / Delaware C-D

10:00am EDT

Integrating SQL into Db2 Web Query to simplify reporting challenges

One of the hidden gems of Db2 Web Query is the ability to use SQL as a data source for a report or dashboard. Some of those challenges include using date attributes, eliminating use of temp/work files for data pre-processing, and enabling web query over IBM i Services for systems and security metrics monitoring and reporting.

Speakers

Doug Mack

Consultant LAB Services, IBM

Doug Mack is a DB2 for i Analytics Consultant in IBM’s Power Systems Lab Services organization, employed by IBM in the United States. Doug currently leads the Analytics team for the DB2 for i Lab Services team delivering Query/400 modernization, DB2 Web Query, Data Warehouse design... Read More →

Monday May 24, 2021 10:00am - 10:20am EDT
Breakout 2 / Delaware C-D

10:30am EDT

Convert spool file data into Excel with the "easy button"

Practical example of how to reverse engineer the data stored in a spool file to get it into Excel (or PDF or any of the other formats supported by Db2 Web Query). The technique is very simple using an IBM i Service and Systools to query for the spool file of interest then pushing the magic button to dump the data into various Excel formats.

Speakers

Doug Mack

Consultant LAB Services, IBM

Doug Mack is a DB2 for i Analytics Consultant in IBM’s Power Systems Lab Services organization, employed by IBM in the United States. Doug currently leads the Analytics team for the DB2 for i Lab Services team delivering Query/400 modernization, DB2 Web Query, Data Warehouse design... Read More →

Monday May 24, 2021 10:30am - 10:50am EDT
Breakout 2 / Delaware C-D

11:00am EDT

How to write SQL without knowing SQL

Learn how to use the recently upgraded SQL prompting in Run SQL Scripts in ACS to write SQL by just repeatedly hitting Ctrl-Space.

Speakers

Edmund Reinhardt

IBM i Application Development Tools Lead, IBM

Edmund Reinhardt has been working on application development tools for IBM i for over 25 years. Starting with the green screen tools in 1989 through each technology iteration from OS/2 to Java. As the architect for Rational Developer for i, he is passionate about engaging and delighting... Read More →

commonNav2021Final sql

ACS Content Assist Capabilities pdf

COMMON21 How to write SQL without knowing SQL pdf

Monday May 24, 2021 11:00am - 11:20am EDT
Breakout 2 / Delaware C-D

11:30am EDT

Office Hours: SQL Language with Birgitta Hauser

Attend and ask all your SQL language questions of Birgitta Hauser.

Speakers

Birgitta Hauser

Database and Software Architect, IBM Champion, Independent Consultant

Birgitta Hauser worked on the IBM i and its predecessors since 1992. She graduated with a business economics diploma, and started programming on the AS/400 in 1992. She worked and works as traditional RPG Programmer but also as Database and Software Engineer, focusing on IBM i application... Read More →

Monday May 24, 2021 11:30am - 11:50am EDT
Breakout 2 / Delaware C-D

2:30pm EDT

Why you should instrument your SQL Workloads with Client Registers

Client Registers are an overlooked Db2 for i feature that can streamline SQL analysis on your systems. Learn how to easily instrument your SQL workloads with the client registers to add workload and business context to all your SQL statements. You'll also see how these register values can be displayed with the ACS SQL Performance Center tooling.

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Monday May 24, 2021 2:30pm - 2:50pm EDT
Breakout 4 / Union D-E

3:00pm EDT

SQL Routines Performance Checkup

The SQL language makes it easy to create your business logic in stored procedures, triggers, and user-defined functions. Each of these routines have options that performance, but most programmers usually take the default option values without giving any thought to their impact. Learn about these overlooked performance options and how to run reports to quickly identify routines using suboptimal options.

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Monday May 24, 2021 3:00pm - 3:20pm EDT
Breakout 4 / Union D-E

3:30pm EDT

Enhance SQL Statement Reuse with Extended Indicators

Ever feel like your embedded SQL programs contain many variations of the same basic UPDATE or OPEN statement to account for all the different combinations of host variables? If so, then you'll want to come learn how to use SQL Extended Indicators to code a single statement that can be reused. In addition, you'll see how the SQL procedural language has been enhanced to allow the usage of Extended Indicators.

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Monday May 24, 2021 3:30pm - 3:50pm EDT
Breakout 4 / Union D-E

4:00pm EDT

Setting up ODBC on IBM i

If you want to use open-source software on IBM i, you are going to need to set up ODBC, the database API the underlies connections for most OSS on the system. This session will show you exactly how to set up ODBC on your systems, including installation, creation of "data sources", and understanding some of the core connection string keywords. This is a one-time set up, so after this short session you'll be ready to go!

Speakers

Mark Irish

Software Engineer, IBM

I am a software engineer at IBM working on open-source software enablement for the IBM i. I am particularly knowledgeable about Node.js and Machine Learning software libraries. Feel free to talk to me about your open-source questions and tell me what open-source software you'd like... Read More →

Monday May 24, 2021 4:00pm - 4:20pm EDT
Breakout 4 / Union D-E

4:30pm EDT

Kung-Fu Tables

This session covers database constraints and triggers. Records can be inserted/updated from so many different locations in a program and we do our best to protect from bad data in our code. So why not centralize this logic and allow a table to fend for itself?

Speakers

Mike Zaringhalam

Developer, PC Richard & Son

Have you ever wanted to meet someone who started Hawaiian Shirt Fridays at their work place? If the answer is 'Yes!', then feel free to reach out with any questions you have regarding Hawaiian shirts... Or IBM i related topics. My main focuses are RDi, DB2, Modernization, and D... Read More →

Monday May 24, 2021 4:30pm - 4:50pm EDT
Breakout 4 / Union D-E

5:00pm EDT

Office Hours: Database Performance with Kent Milligan

Attend this session to ask your questions about database performance with Kent Milligan, IBM.

Speakers

Kent Milligan

Senior Db2 for i Consultant, IBM

Kent Milligan is a Senior Db2 for i Consultant in IBM Technology Expert Labs. Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the Db2 for i Expert Labs practice in 2020, Kent spent 5 years... Read More →

Monday May 24, 2021 5:00pm - 5:20pm EDT
Breakout 4 / Union D-E

8:30am EDT

Journals and ACS turning potential value into realized value.

Journal entries are a secure form of data that when viewed can be used to weave a story that answers the who, the what, the when, and the where of operations on the system. In this session we will look at how IBM i Access Client Solutions (ACS) can be used to turn the potential value of journal entries into realized value by taking a simple “what happened” scenario. Come take a dive into ACS and Journal entries!

Speakers

Quinton Kramer

Staff Software Engineer, IBM

Having started in January of 2015 Quinton Kramer is one of the fresh faces of IBM i. In that time Quinn has worked on everything from customer/support issues to feature enhancements and deliverables on Db2.Working as a database developer he views speaking engagements as an opportunity... Read More →

Tuesday May 25, 2021 8:30am - 8:50am EDT
Breakout 5 / Morrow

9:00am EDT

Getting advice with ACS and the Index Advisor

In this session we will take a look at how IBM i Access Client Solutions (ACS) can help the database engineer tackle the iterative process that is index management. • What role do maintained temporary indexes play? • What is the difference between raw and condensed advice? • How should you approach the advice you are given? All these questions and more are answered in this 20-minute dive into the Index Advisor!

Speakers

Quinton Kramer

Staff Software Engineer, IBM

Having started in January of 2015 Quinton Kramer is one of the fresh faces of IBM i. In that time Quinn has worked on everything from customer/support issues to feature enhancements and deliverables on Db2.Working as a database developer he views speaking engagements as an opportunity... Read More →

Tuesday May 25, 2021 9:00am - 9:20am EDT
Breakout 5 / Morrow

9:30am EDT

Using SQL global Variables to help audit your data:

This presentation demonstrates a technique for automatically populating an SQL Global variable to be used for adding audit information to your database tables

Speakers

Ray Everhart

Senior IBM i Analyst and X-Analysis Product Manager, Fresche

For over 30 years, Ray has helped companies understand, develop and manage their RPG, COBOL and CA 2E application environments. He also specializes in helping clients enhance and modernize their systems to meet evolving business demands and take advantage of digital transformation... Read More →

Tuesday May 25, 2021 9:30am - 9:50am EDT
Breakout 5 / Morrow

10:00am EDT

Install and Start Using PostgreSQL in 20 Minutes

IBM recently released a current version of the PostgreSQL database to run natively in PASE on IBM i. The software installs in minutes on IBM i via the Yum-based Open Source Package Management process which is part of Access Client Solutions (ACS). This means that PostgreSQL is being fully embraced as a valid IBM i database for storing data for PHP, Python, Node, Java application and more. No need to look elsewhere to host your open-source applications that need to use PostgreSQL. Attend this session to see how to quickly get started installing and using PostgreSQL natively on IBM i.

Speakers

Richard Schoen

President, Mobigogo LLC

Richard has over 30 years of software development, system integration, document management and process automation experience on all system platforms including IBM i, Windows, Linux and Mac. Richard started RJS Software Systems in 1990 and served as President and CTO until the company... Read More →

Tuesday May 25, 2021 10:00am - 10:20am EDT
Breakout 5 / Morrow

10:30am EDT

Using the SQL procedural language to create user-defined table functions

Many IBM i Services rely upon user-defined table functions (UDTFs) to return back results as a table that can be easily queried using a SELECT statement. If you are familiar with these service, you know how useful and powerful they can be. You too can write your own UDTF that returns back real time results as a table. Because UDTFs are called using SQL, they allow you to move your business logic into a single location that can be called from different applications and different languages. Simplify your application logic by calling a UDTF that does your work instead of repeating the same code in multiple locations. Tune into this session to learn how to use the SQL procedural language to easily create UDTFs to modernize your applications.

Speakers

Sarah Mackenzie

IBM

Sarah has worked at IBM since 2013 and is a key developer working on the IBM i Development team in Rochester. She is specifically focused on Db2 for i and is responsible for helping to implementing many of the recent enhancements to Db2 for i, including RCAC and Temporal Tables. Sarah... Read More →

Tuesday May 25, 2021 10:30am - 10:50am EDT
Breakout 5 / Morrow

11:00am EDT

Using IBM i Services to analyze your journals and journal entries

In the past, the only way to convert journal entries into a readable format was through the DSPJRN and CPYAUDJRNE commands or the Retrieve Journal Entries API. Now, however, you can use the IBM i service, DISPLAY_JOURNAL, to easily query your journal entries including audit journal entries. You can also take advantage of the DISPLAY_JOURNAL Sylog support. Need to know what objects are journaled to your journals? There is a service for that too! Tune into this session to learn how to use IBM i Services to analyze your journals and journal entries using the power of SQL.

Speakers

Sarah Mackenzie

IBM

Sarah has worked at IBM since 2013 and is a key developer working on the IBM i Development team in Rochester. She is specifically focused on Db2 for i and is responsible for helping to implementing many of the recent enhancements to Db2 for i, including RCAC and Temporal Tables. Sarah... Read More →

Tuesday May 25, 2021 11:00am - 11:20am EDT
Breakout 5 / Morrow

11:30am EDT

Office Hours: Journaling, ACS, and the Database Engineer with Quinton Kramer

Attend with your questions about journaling and ACS, with Quinton Kramer, IBM.

Speakers

Quinton Kramer

Staff Software Engineer, IBM

Having started in January of 2015 Quinton Kramer is one of the fresh faces of IBM i. In that time Quinn has worked on everything from customer/support issues to feature enhancements and deliverables on Db2.Working as a database developer he views speaking engagements as an opportunity... Read More →

Tuesday May 25, 2021 11:30am - 11:50am EDT
Breakout 5 / Morrow

2:30pm EDT

IBM i in an AI/ML Analytics World

This quick hitter session will discuss available approaches to leverage AI/ML models for data analytics. This is NOT a "how to build an ML Model in Python or R" session, but rather, what facilities are available to you to integrate data to simplify or automate the ability go gain additional insights into your Db2 for i data.

Speakers

Doug Mack

Consultant LAB Services, IBM

Doug Mack is a DB2 for i Analytics Consultant in IBM’s Power Systems Lab Services organization, employed by IBM in the United States. Doug currently leads the Analytics team for the DB2 for i Lab Services team delivering Query/400 modernization, DB2 Web Query, Data Warehouse design... Read More →

Tuesday May 25, 2021 2:30pm - 2:50pm EDT
Breakout 5 / Morrow

3:00pm EDT

Find and eliminate invalid numeric data

Non-SQL database files don't enjoy the protections that are innate to SQL. Your non-SQL files may contain invalid data within numeric fields. How annoying! Data integrity is paramount and with this session you will learn how SQL can be used to tackle this topic.

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

Tuesday May 25, 2021 3:00pm - 3:20pm EDT
Breakout 5 / Morrow

3:30pm EDT

How the DBE can supervise SQE

Db2 for i has many interactive and programmatic tools for the Database Engineer (DBE). Come to this session to learn how to use the latest and greatest state of the art tools to bring new levels of control, insight and management to the SQL Query Engine (SQE).

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

Tuesday May 25, 2021 3:30pm - 3:50pm EDT
Breakout 5 / Morrow

4:00pm EDT

A few quick things you can use SQL to check for

Using SQL allows you to keep tabs on the following: - Do I need to RGZPFM? Number of deleted records in files. What happens if I want to exclude some libraries or files? - Old spool files - Abandoned files in IFS - Check for errors on this partition & others all on one screen - WRKSYSSTS equivalent snapshot to email or log file Also creating SQL Views over data areas & for giving CL information it can only get from APIs.

Speakers

Simon Hutchinson

Author, RPGPGM.COM

Simon Hutchinson has been writing his highly acclaimed IBM i blog, RPGPGM.COM, for eleven years sharing his knowledge, experience, and giving many detailed examples of the latest RPG, SQL, and CL. His work has led him to be recognized by IBM as an IBM Champion for Power and IBM i Community Advocate. He is a subject matter expert for COMMON North America and... Read More →

Tuesday May 25, 2021 4:00pm - 4:20pm EDT
Breakout 5 / Morrow

4:00pm EDT

Dealing with APIs and JSON via SQL

This session covers preparing json, extracting data from json, and calling APIs through SQL. Since JSON is widely used for passing/receiving data via APIs, we can use these methodologies to speed up applications as well as development.

Speakers

Mike Zaringhalam

Developer, PC Richard & Son

Have you ever wanted to meet someone who started Hawaiian Shirt Fridays at their work place? If the answer is 'Yes!', then feel free to reach out with any questions you have regarding Hawaiian shirts... Or IBM i related topics. My main focuses are RDi, DB2, Modernization, and D... Read More →

Tuesday May 25, 2021 4:00pm - 4:20pm EDT
Breakout 8 / Franklin C-D

4:30pm EDT

How to use System Limits to protect your IBM i

Is your IBM i standing in a beautiful meadow or on the edge of a rocky cliff? Your system has been telling you for many years about critical system limit consumption. Have you been listening? Come to this session to find out what limits are tracked and how to interact with the information, allowing you to take action before your system activity is affected.

Speakers

Susan Romano

Technical Lead Db2 for i Development Team, IBM

Sue Romano is a technical leader on the Db2 for i development team and has worked on the SQL parser for almost 30 years. She is involved in designing and implementing SQL enhancements and defines new SQL services. Sue represents Db2 for i on IBM’s SQL Language Council, which ensures... Read More →

Tuesday May 25, 2021 4:30pm - 4:50pm EDT
Breakout 5 / Morrow

5:00pm EDT

Office Hours: Ask the IBM Database Expert with Scott Forstie

Attend with your questions for Scott Forstie, DB2 for i Business Architect with IBM.

Speakers

Scott Forstie

Db2 for i Architect, IBM

Scott Forstie is a Senior Technical Staff Member within IBM i development and spends most of his time working as the Db2 for i Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry events... Read More →

Tuesday May 25, 2021 5:00pm - 5:20pm EDT
Breakout 5 / Morrow

8:30am EDT

LISTAGG and SPLIT - Meet these SQL cousins

Db2 for i includes many powerful built-in functions. In this session you will learn how to use LISTAGG and SPLIT to build or decompose a list of values. In 20 minutes, you will learn how to use these built-in functions to replace or avoid complex programming logic.

Speakers

Susan Romano

Technical Lead Db2 for i Development Team, IBM

Sue Romano is a technical leader on the Db2 for i development team and has worked on the SQL parser for almost 30 years. She is involved in designing and implementing SQL enhancements and defines new SQL services. Sue represents Db2 for i on IBM’s SQL Language Council, which ensures... Read More →

Wednesday May 26, 2021 8:30am - 8:50am EDT
Breakout 5 / Morrow

9:00am EDT

Detect and Respond to Problem Queries Using Db2 for i Tools

Db2 for i provides powerful tools for monitoring query behavior. Learn how to use the newest Db2 for i features for uncovering unwelcome variations in SQL workload performance, stopping runaway queries, and responding to system slowdowns before they become meltdowns.

Speakers

Timothy Clark

Advisory Software Engineer, IBM

Wednesday May 26, 2021 9:00am - 9:20am EDT
Breakout 5 / Morrow

9:30am EDT

Make RUNSQLSTM even better using CL commands

The RUNSQLSTM command lets you run multiple SQL statements in a single call, like a script. You can do even more now by calling CL commands directly. See how this can expand your options.

Speakers

Vern Hamberg

Senior Software Engineer, Ecolab, Pest Elimination

Vern has worked on IBM midrange systems for over 30 years, beginning with the System/38 and AS/400. At present he is a senior software engineer for the Pest Elimination division at Ecolab. He also serves on the COMMON Americas Advisory Council (CAAC) and SET and is one of the SMEs... Read More →

Wednesday May 26, 2021 9:30am - 9:50am EDT
Breakout 5 / Morrow

10:00am EDT

Combine your own CL commands with Query Management to enhance data access

You can create your own CL commands. You can also create prompted Query Management queries. Put them together to get flexible and powerful access to your data.

Speakers

Vern Hamberg

Senior Software Engineer, Ecolab, Pest Elimination

Vern has worked on IBM midrange systems for over 30 years, beginning with the System/38 and AS/400. At present he is a senior software engineer for the Pest Elimination division at Ecolab. He also serves on the COMMON Americas Advisory Council (CAAC) and SET and is one of the SMEs... Read More →

OPEN LAB Expand Your Options Using Query Management docx

OPEN LAB Expand Your Options Using Query Management pdf

Combine your own CL commands with Query Management to enhance data access handout pdf

Wednesday May 26, 2021 10:00am - 10:20am EDT
Breakout 5 / Morrow

11:30am EDT

Office Hours: SQL and Database with Sue Romano

Attend this session as your final opportunity to ask questions about SQL or the database. With Sue Romano, IBM.

Speakers

Susan Romano

Technical Lead Db2 for i Development Team, IBM

Sue Romano is a technical leader on the Db2 for i development team and has worked on the SQL parser for almost 30 years. She is involved in designing and implementing SQL enhancements and defines new SQL services. Sue represents Db2 for i on IBM’s SQL Language Council, which ensures... Read More →

Wednesday May 26, 2021 11:30am - 11:50am EDT
Breakout 5 / Morrow