Loading…
COMMON Fall Conference & Expo | October 15-17 | Pittsburgh, PA | www.common.org/fall

Log in to bookmark your favorites and sync them to your phone or calendar.

Database [clear filter]
Sunday, October 14
 

8:00am

A1AA WORKSHOP: Application Modernization Database
Today, when we talk about application modernization, most think about having to reface their application and going mobile. New technologies will be used to change the layout and programs will be rewritten. … but what will a nice design be worth if the base, i.e. the underlying database is still in your grandfather’s style, grown over the years with lots of redundancies? In this workshop you will learn why to go from DDS to SQL, what are the benefits of SQL over DDS. Techniques for converting and redesigning your existing grown over the time database into a modern normalized database will be shown. In a modern environment database objects can and must be accessed from multiple interfaces and programming languages. For guaranteeing data integrity and security business logic and security rules should be moved into the database.

This is Session ID:170027

Speakers
avatar for Birgitta Hauser

Birgitta Hauser

Database and Software Architect, Fresche Solutions, Inc.
Application Modernization, Database Modernization, EducationSQL Queries, Database Design,SQL Programming, User Defined Functions and Table Functions,Journaling and Commitment Control,SQL Performance and Performance AnalysisRPG Free Format, ILE Concepts, Modular Programming, Data IntoXML... Read More →


Sunday October 14, 2018 8:00am - 11:30am
City Center A

12:30pm

A5AA WORKSHOP: SQL Programming
SQL is much more than a query language. Besides querying and manipulating data in database objects and generating those database objects, SQL also provides all kinds of commands and constructs, for writing and executing programs. With the SQL Programming Language, Stored Procedures, Triggers and User Defined (Table) Functions can be written, without knowing anything else than SQL commands!

This is Session ID:170049

Speakers
avatar for Birgitta Hauser

Birgitta Hauser

Database and Software Architect, Fresche Solutions, Inc.
Application Modernization, Database Modernization, EducationSQL Queries, Database Design,SQL Programming, User Defined Functions and Table Functions,Journaling and Commitment Control,SQL Performance and Performance AnalysisRPG Free Format, ILE Concepts, Modular Programming, Data IntoXML... Read More →


Sunday October 14, 2018 12:30pm - 4:00pm
City Center A
 
Monday, October 15
 

9:30am

12AA OLAP (Online Analytical Processing) Functions – Much More Than Running Numbers!
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.

This is Session ID:180278

Speakers
avatar for Birgitta Hauser

Birgitta Hauser

Database and Software Architect, Fresche Solutions, Inc.
Application Modernization, Database Modernization, EducationSQL Queries, Database Design,SQL Programming, User Defined Functions and Table Functions,Journaling and Commitment Control,SQL Performance and Performance AnalysisRPG Free Format, ILE Concepts, Modular Programming, Data IntoXML... Read More →


Monday October 15, 2018 9:30am - 10:30am
City Center A

10:45am

13AA Create or Replace … DDS Files with DDL Objects
With the latest releases and technology refreshes the SQL data definition language (DDL) for creating and maintaining database objects was enormously enhanced. DDS on the other hand is stabilized. But still a lot of applications even those with a modern graphical interface and where the latest goodies of the programming language are used in the source code are based on DDS described files. The excuse DDL enhancements cannot be used, because of the DDS description and the native I/O access of these files, does not count any more. With Reverse Engineering, the SQL code for DDS described objects can be retrieved. CREATE OR REPLACE statements allow DDS described files to be converted into SQL defined database objects, while the programs that use these files can stay untouched. With the CREATE OR REPLACE TABLE statement, files with fields based on field reference tables can be managed.

Learning Objectives:
In this session you will learn to convert DDS described files into SQL tables and SQL indexes without touching any program code. It will also be demonstrated how reference files can be used in composition with SQL tables and how (future) enhancements, for example adding columns on a specific position or changing data types and lengths can be managed.

This is Session ID:170028

Speakers
avatar for Birgitta Hauser

Birgitta Hauser

Database and Software Architect, Fresche Solutions, Inc.
Application Modernization, Database Modernization, EducationSQL Queries, Database Design,SQL Programming, User Defined Functions and Table Functions,Journaling and Commitment Control,SQL Performance and Performance AnalysisRPG Free Format, ILE Concepts, Modular Programming, Data IntoXML... Read More →


Monday October 15, 2018 10:45am - 11:45am
City Center A

2:45pm

16AA What's New in Db2 for i
In the tradition of previous Technology Refreshes (TR), the database team is delivering enhancements on the same cadence. Attend this session to hear about the many enhancements to SQL on i, SQL Query Engine, IBM i Services and more. This session will cover the new and enhanced Db2 for i capabilities added to IBM i 7.2and 7.3 over the last Technology Refreshes.

This is Session ID:170110

Speakers
avatar for Scott Forstie

Scott Forstie

Db2 for i Business 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 Business Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry... Read More →


Monday October 15, 2018 2:45pm - 3:45pm
City Center A

2:45pm

16AH SQL Writing Tips and Techniques
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.

This is Session ID:180169

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Monday October 15, 2018 2:45pm - 3:45pm
Marquis Ballroom C

4:00pm

17AA SQL Stored Procedures and Application Modernization
Among the many modernization technologies available on IBM i, SQL is perhaps the one best known by RPG developers. This session won't focus on how to write SQL statements, but rather how to write scripts that combine SQL statements together into procedures that encapsulate database access and core business logic for use in a myriad of modern applications. We'll discuss how stored procedures fit into a modernization strategy, and why they are such a good fit for RPG programmers who want to participate in modernization projects. They're also a great fit for anyone new to IBM I who needs access to its database for applications in PHP, Java, Crystal Reports, IBM Web Query, Excel, Web Services, conversion projects, etc. 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 form the backbone of a service oriented architecture. These procedures can be called from any programming language. This can simplify the database access code in PHP, Java, and other host languages, while taking full advantage of DB2-specific SQL features. Come and learn about the many features and benefits of SQL stored procedures, how to code them, and how to use them in your modernization strategy.


This is Session ID:170074

Speakers
avatar for John Valance

John Valance

Senior Consultant, Profound Logic
John Valance is a senior consultant and modernization strategist for Profound Logic Software. He has worked on the IBM midrange platform since the 1980s, and on web development since 1998. As a consultant and developer, John specializes in modernization and web application development... Read More →


Monday October 15, 2018 4:00pm - 5:00pm
City Center A

5:15pm

18AA Dueling SQL
At this new session, Tim and Scott will enter into an SQL duel. Who will win? The attendees of the session will decide. They will take turns presenting their favorite approaches to using one or more of the IBM i Services. Come to this session to have some fun and see the best of the best SQL has to offer. Let the match begin.


This is Session ID:180297

Speakers
avatar for Alison Butterill

Alison Butterill

WW IBM i Offering Manager, IBM
Alison Butterill is the offering manager for IBM i. She owns the responsibility for the IBM i business, including defining and guiding future directions and strategies. She works with the IBM i sales, marketing, and development teams as well as clients and partners to ensure that... Read More →
avatar for Scott Forstie

Scott Forstie

Db2 for i Business 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 Business Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry... Read More →


Monday October 15, 2018 5:15pm - 6:15pm
City Center A
  • Content Level All
  • Operating Systems IBMi
 
Tuesday, October 16
 

8:00am

21AA Generating JSON Documents with SQL
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.

Learning Objectives:
Get an introduction into JSON basics. Learn how JSON object and arrays can be generated, concatenated or nested and composed into JSON documents.

This is Session ID:180016

Speakers
avatar for Birgitta Hauser

Birgitta Hauser

Database and Software Architect, Fresche Solutions, Inc.
Application Modernization, Database Modernization, EducationSQL Queries, Database Design,SQL Programming, User Defined Functions and Table Functions,Journaling and Commitment Control,SQL Performance and Performance AnalysisRPG Free Format, ILE Concepts, Modular Programming, Data IntoXML... Read More →


Tuesday October 16, 2018 8:00am - 9:00am
City Center A

9:15am

22AA Consuming JSON Documents and Web Services with SQL
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.

This is Session ID:170054

Speakers
avatar for Birgitta Hauser

Birgitta Hauser

Database and Software Architect, Fresche Solutions, Inc.
Application Modernization, Database Modernization, EducationSQL Queries, Database Design,SQL Programming, User Defined Functions and Table Functions,Journaling and Commitment Control,SQL Performance and Performance AnalysisRPG Free Format, ILE Concepts, Modular Programming, Data IntoXML... Read More →


Tuesday October 16, 2018 9:15am - 10:15am
City Center A

1:30pm

25AA Clearing the Fog on SQL Joins
Clear up the confusion on how joins works. Pick the right join type for the job.


Learning Objectives:
1. Better understand how joins work
2. determining the right join for a given situation


This is Session ID:170259

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Tuesday October 16, 2018 1:30pm - 2:30pm
City Center A

2:45pm

26AA SQL Views for Dummies
SQL views provide the modern user interface to obtain system level information traditionally available via OUTFILE and API support.
Attend this session and learn how easy it is to use a SQL view. SQL views provide the administrator of the system easy access to lots of system configuration and management information.
This session will provide a list of available SQL views and their purpose as well as examples of how to use them. I will explain how the examples work and you'll leave with the skills necessary to be a better and more system administrator.

This is not an introduction to SQL class, but rather an explanation of why you need to add SQL to your toolset and a quick-start guide showing you how to become a better systems administrator or security officer.
http://ibm.biz/Db2foriServices

This is Session ID:170108

Speakers
avatar for Scott Forstie

Scott Forstie

Db2 for i Business 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 Business Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry... Read More →


Tuesday October 16, 2018 2:45pm - 3:45pm
City Center A

2:45pm

26AF Introduction and What's New with Db2 Web Query
You own it. You may have looked at it many years ago. Today’s Version 2.2.1 is night and day different than the original version, including the “easy button” for creating sample reports, many new visualizations like geographical maps and responsive dashboards, and data replication and transformation tools. This session reviews this low cost business intelligence solution, with updates on the latest cool enhancements.


Learning Objectives:
1. Learn the fundamental features of DB2 Web Query for i
2. Understand how to put it to quick use with creation of templates out of the box
3. Learn about the latest features that you own


This is Session ID:170095

Speakers
avatar for Doug Mack

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 October 16, 2018 2:45pm - 3:45pm
Marquis Ballroom A

2:45pm

26AD POWER PICK: Advanced SQL Data Manipulation Language (Double Session)
Part 1 SQL is a very powerful language for accessing and manipulating data. This session will explore some of the more advanced SQL concepts. The session begins with a discussion about thinking “set at a time” vs. row at a time, then continues with Set operations, Sub queries, Common Table Expressions, Derived Tables and more. Part 2 This session will focus on more of the Set at a time concepts of SQL DML. Topics covered include: Grouping Sets, Cube, Rollup, Recursive Common Table Expressions, and OLAP Extensions provided by Db2 i 7.3


Learning Objectives:
1. Improved understanding of SQL language elements related to manipulation of data.
2. Change the thought process of manipulating data from processing rows, to processing sets..


This is Session ID:180112

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Tuesday October 16, 2018 2:45pm - 5:00pm
Grand Ballroom 5

4:00pm

27AA What’s New in IBM i 7.2 & IBM i 7.3
With IBM i 7.1 normal support ending on April 30, 2018, it’s a good time to review the new possibilities that should be considered after upgrading to IBM i 7.2 or IBM i 7.3. In this session, Scott will explain the major features and functions that exist within these releases. You will leave this session with a firm grip on the benefits to be gained on the latest and greatest IBM i has to offer. Attend this session to move your IBM i plans from Coach to First class.



This is Session ID:180298

Speakers
avatar for Scott Forstie

Scott Forstie

Db2 for i Business 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 Business Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry... Read More →


Tuesday October 16, 2018 4:00pm - 5:00pm
City Center A
  • Content Level All

5:15pm

28AA Data Security and Compliance Monitoring with Db2 for i
With additional compliance laws coming from Europe and various states in the US, it is harder to do business in different parts of the world without adequately securing your data. This session will discuss various security compliance laws and provide an overview of how to best secure your data. Topics covered include: Security administrative roles, Object level Security, Row and column access control, PowerSC, and Guardium.



This is Session ID:180170

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Tuesday October 16, 2018 5:15pm - 6:15pm
City Center A

5:15pm

28AD POWER PICK: Stop Building Data Extracts for BI Analytics!
Building work files, temp files, and extracts can be unproductive, add extraneous processing, and creates too many copies of (unsecure?) data. In this session we will talk about simplifying and automating processes to eliminate custom extract programs. We'll cover tools like DataMigrator, why the data warehouse might make sense, and streamlining creation of PDFs or Excel files outputs w/o having to reverse engineer spool files.


Learning Objectives:
1. learn about simplifying and automating data transformations and replication.
2. Understand how Db2 Web Query can eliminate many extra steps currently in place to get data into a spreadsheet or visualization tool.
3. Learn about an IBM i based ETL tool that you probably haven't heard of.


This is Session ID:180273

Speakers
avatar for Doug Mack

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 October 16, 2018 5:15pm - 6:15pm
Grand Ballroom 5
 
Wednesday, October 17
 

8:00am

31AA The Best of ACS!
IBM i Access Client Solutions (ACS) is the stratigic member of the IBM i Access Product Family, and it is a platform independent client that is supported running on multiple Operating Systems like Macintosh, Linux, and Windows. In this session Scott and Tim will use ACS to show best practices in managing and administering the database as well as other aspects of the system. Come to this session to learn about all the great features available in ACS for the database developer and administrator, and how they are in many cases better than what you've been using! Learning Objectives: 1. Discover the latest features in Run SQL Scripts, SQL Performance Center, and more 2. Revisit the IBM i system access and management strategy 3. Live Demo !!



This is Session ID:180300

Speakers
avatar for Wayne Bowers

Wayne Bowers

Software Engineer, IBM
Wayne Bowers has been with IBM since 1997, where he works with the IBM i Global Support Center in Rochester MN. He is an Advisory Software Engineer specializing in the support of the IBM i Access (aka Client Access) products, Console, the TCP Telnet communications server, and the... Read More →
avatar for Scott Forstie

Scott Forstie

Db2 for i Business 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 Business Architect. He has worked on IBM operating system development since joining IBM in 1989.Scott is a frequently published author, speaker at industry... Read More →


Wednesday October 17, 2018 8:00am - 9:00am
City Center A
  • Content Level All
  • Operating Systems IBMi

8:00am

31AF Quick Start to Value with Db2 Web Query
Now that you've heard about DB2 Web Query ,what are some of the ways you can take immediate advantage of it? Import Query/400 definitions and modernize them. Download and install the simplified TRIAL code that includes built in reports for system utilization information. Scared of meta data - don't be - as we'll talk about that too.


Learning Objectives:
1. Learn about ways that you can get immediate value out of DB2 Web Query.
2. Understand the options in modernizing Query/400 reports
3. Learn how to be a hero.


This is Session ID:170098

Speakers
avatar for Doug Mack

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 →


Wednesday October 17, 2018 8:00am - 9:00am
Marquis Ballroom A

9:15am

32AA Transactional vs. Analytical SQL
Understanding and spotting it: what should an appropriate data model look like? What are the red flags that your data model is not best for your usage? This session will discuss these topics along with strategies for doing more analytical processing of your transactional data. Additional topics covered such as: - virtual vs. ETL mapping - using dimension tables for dates, VINs, customer name, zip code, etc.. Operational Data Stores, Dimensional Data Model and why they are best for analytical analysis will also be discussed.



This is Session ID:180174

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Wednesday October 17, 2018 9:15am - 10:15am
City Center A

10:30am

33AA Be a Hero with Database Indexing
Learn about database indexing technology for DB2 for i and the proper way to determine which indexes to create. Not all good indexes are advised and not all advised indexes are good


Learning Objectives:
1. General understanding of database indexing technology
2. why indexes are so important for good performance
3. approaches for determining the right indexes to create


This is Session ID:170241

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Wednesday October 17, 2018 10:30am - 11:30am
City Center A

10:30am

33AF Taking Db2 Web Query to the Next Level: Advanced Reporting Tips
Simple reports are....simple. But how do deal with the more complex requests you get for reports and leverage techniques in Db2 Web Query to meet those requirements. This session will cover dealing with dates - data range reports, y2y, rolling month issues. We'll show you how to leverage SQL Views and Db2 OLAP Specifications for difficult analytics. We'll even delve into integrating data from Watson into your reports.


Learning Objectives:
1. Get advanced education on reporting techniques
2. Learn about augmenting existing operational data with data from the cloud
3. Discover the power of meta data and data dimension tables for date processing in reports


This is Session ID:180043

Speakers
avatar for Doug Mack

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 →


Wednesday October 17, 2018 10:30am - 11:30am
Marquis Ballroom A

10:30am

33AE Exploring NoSQL Databases
Open Source Databases are one of the fastest growing topics of interest today and NoSQL databases are no exception. NoSQL databases provide for storage and retrieval of data that is modeled in ways other than tabular relations used in relational databases. This session will explore why NoSQL databases are important as well as showing how to work with some of the more popular NoSQL databases such as MongoDB.


This is Session ID:180181

Speakers
avatar for Erwin Earley

Erwin Earley

Sr Solutions Consultant, Zend by Perforce
With over 20 years working in the IBM i community previously at IBM and now with Zend by Perforce I have worked with a number of technologies on the POWER platform including cloud (PowerVC), Dev/Ops solutions (Docker, Chef), Open Source technologies (Linux, MySQL/MariaDB, PHP). I... Read More →


Wednesday October 17, 2018 10:30am - 11:30am
Grand Ballroom 6

11:45am

34AD POWER PICK: DB2 for i Visual Explain Deep Dive
Using Visual Explain for DB2 for i. Seeing is believing on SQL queries.


Learning Objectives:
1. What is Visual Explain
2. using it to better understand SQL performance
3. learn the different ways to utilize it


This is Session ID:170246

Speakers
avatar for Rob Bestgen

Rob Bestgen

IBM
Rob Bestgen is a Business Architect for IBM i, responsible for development strategy for systems management and external interfaces. He is also chief architect of the DB2 SQL Query Engine for DB2 i. Rob is also a member of the DB2 for i Center of Excellence team in lab services and... Read More →


Wednesday October 17, 2018 11:45am - 12:45pm
Grand Ballroom 5