Sessions - SQL and Database

2017 sessions will include:


SQL

  • The SQL Procedures Language in a Nutshell
  • Thirty SQL Tips in Sixty Minutes
  • Achieving Good Performance with SQL on DB2 i
  • Moving from DDS to SQL
  • Writing SQL Stored Procedures and Functions
  • SQL Writing Tips and Techniques
  • SQL Stored Procedures and Application Modernization
  • Advanced SQL Topics
  • SQL (Some quick Lessons) in SQL
  • The Why and How of the iModernize(d) Application Architecture


DB2

  • What's New in DB2 for i

NEUGC 2017 Sessions by Track – SQL

 


The SQL Procedures Language in a Nutshell (Ted Holt)

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


  • SQL PL is an easy and powerful interface to the database.
  • SQL PL builds functions, triggers, stored procedures, and dynamic compound statements.
  • SQL PL runs on all DB2 platforms, not just DB2 for i.
  • SQL PL implements SQL Persistent Stored Modules, an ISO standard, and is similar to scripting languages used on non-IBM platforms.


Programmers who know RPG, CL, or COBOL will find SQL PL easy to learn. In this session, Ted Holt presents an overview of the SQL Procedures Language. Topics covered include basic syntax, control structures, and condition handling.




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.




Achieving Good Performance with SQL on DB2 i (Rob Bestgen)

SQL is a very powerful language for accessing and manipulating data.  Striving to achieve and maintain good performance with SQL should be part of any development project, as increasing data sizes and demands for additional access are a reality over time. This introductory session covers the main aspects that affect performance, how you can address these proactively, and how to find potential issues in existing applications and improve them.




Moving from DDS to SQL (Rob Bestgen)


One of the main topics in ‘application modernization’ is taking advantage of DB2 for i’s SQL technology to replace DDS and native IO. In this session, we'll compare the SQL and native IBM i interfaces and the methodologies for moving to SQL in a seamless manner. 


By the end of this session, attendees will be able to:

  • Understand the differences between SQL & DDS objects. 
  • Identify the advantages of moving business logic into DB2 instead of writing it in application code.
  • Realize the performance differences between SQL & Native I/O programming interfaces and the concept of set based processing.
  • Have a better understanding of where and when modernization would be right for their organization.

One of the main topics in ‘application modernization’ is taking advantage of DB2 for i’s SQL technology to replace DDS and native IO.  In this session, we'll compare the SQL and native IBM i interfaces and the methodologies for moving to SQL in a seamless manner. By the end of this session, attendees will be able to: 
Understand the differences between SQL & DDS objects. 
Identify the advantages of moving business logic into DB2 instead of writing it in application code.
Realize the performance differences between SQL & Native I/O programming interfaces and the concept of set based processing.
Have a better understanding of where and when modernization would be right for their organization.



Writing SQL Stored Procedures and Functions (Rob Bestgen)

Support for SQL Procedural Language (SQL PL) in IBM i has become instrumental in application modernization and porting projects. The procedural language can be brought to bear on many different aspects of database processing. 

This session provides an overview of the SQL procedural language and describe the major areas in which it can be used: procedures, functions and triggers. This session will also highlight recent advancements in SQL PL. 



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.



SQL Stored Procedures and Application Modernization (John Valance)

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.




Advanced SQL Topics (Rob Bestgen)


SQL is a very powerful language for accessing and manipulating data. This session will explore some of the more advanced SQL concepts and their use in “set at a time” processing. The list includes VIEWS, sub queries, Common Table Expressions, derived tables, Grouping Sets, Recursive Common Table Expressions, OLAP functions and more. Bring your SQL environment into a whole new light.




SQL (Some quick Lessons) in SQL (David Andruchuk)

This presentation will give you an overview of the many possibilities of what you can do using SQL and your IBM i DB2 data.

Here are a few topics we will be covering:

  • Learn about CTE (Common Table Expressions) and how to use
  • Learn about UDF (User Defined Function) and how to use
  • Learn about UDTF (User Defined Table Function) and how to use
  • Learn about SQL Aliases and how to use
  • Learn about QSYS2 tables to query to find indexes over a table, indexes advised over over an existing table, etc..
  • Learn about SQL Performance Rules of Thumb





The Why and How of the iModernize(d) Application Architecture (David Andruchuk)


For most IBM i shops, an ongoing initiative is to take advantage of the new offerings IBM has made to the IBM i hardware and software that has defined and set our platform apart from competitors. The process of converting your non data centric architecture to a data centric architecture can appear to be beyond the reach of many IT staffs. 


While the performance gains of modernizing can be easily quantified, the added benefit of flexibility often goes unheralded. Being able to provide faster response to constantly changing business requirements and ever increasing large data demands placed on our environments can be achieved without having to recompile your current programs. 


Adapting your database access and software development practices is as much a necessity as changing your business to constantly changing market requirements.


In this session we will discuss:

  • The process of converting your DDS defined PF and LFs to DDL defined tables, views and indexes
  • Taking advantage of the scalability and flexibility gains offered by modern technologies, both in hardware and in software engineering
  • Adopting the use of views and stored procedures to gain separation between your data model and your programs
  • Designing your system to become easier to implement your web based or graphical UI layer
  • Real life examples of flexibility and performance gains achieved on the path of IBM i(ntegration)




Embedding SQL in RPG Programs - 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.
Embedding SQL in RPG Programs - Beyond The Basics (Susan Gantner)
In this session, we'll move beyond the basics of embedding SQL, 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 into RPG - The Basics" or equivalent knowledge.
NEUGC 2017 Sessions by Track – Database 



What's new in DB2 for i (Rob Bestgen)

Find out how the latest DB2 for i features can be leveraged to help your IBM i database and applications continue to move forward, sometimes dramatically. You will learn about the new DB2 for i features, particularly around its ever expanding SQL capabilities. This session will cover the latest new features available in v7.3 and v7.2.