DB2-Oriented Sessions
DB2 for i 7.1 - More than Meets the Eye
In this session come and see what new DB2 for i features and functions can help your business applications move forward. You will learn how the latest DB2 enhancements such as transparent column encryption, enhanced DB2 support for XML, and result set support can be used to reduce your application development time and enhance functionality.
You will also not want to miss learning about the self-learning capabilities of the SQL Query Engine. This represents just a few of the exciting new DB2 for i capabilities being discussed.
Speaker: Kent Milligan
Using 'Run SQL Scripts'
Run SQL Scripts – also known as the SQL Script Center or Script Center – is part of the Database function in Navigator for the IBM i. It can be said that in part it is the PC or workstation replacement for Interactive SQL (the STRSQL command) on the green screen side of the IBM i. Run SQL Scripts and Interactive SQL do overlap in function and both provide an interactive interface for executing SQL. While each can do things that the other cannot, as of V6R1 Run SQL Scripts is more functionally rich than Interactive SQL and should be your interactive SQL tool of choice on the IBM i.
Come to this session to learn how to configure and customize Run SQL Scripts and put it to work for you. In this session you will also learn how to write and execute a script comprised of one or more SQL statements and/or batch CL commands. You will learn how to store your scripts as a member in an IBM i source file (how about QSQLSRC or QDDSSRC?) and then use RUN SQL Scripts to re-open the saved script from the source file in which it was saved. You will learn how open two or more Run SQL Script Sessions at the same time, display multiple results set windows at the same time, print your results set, and save your results set on your PC as an Excel spreadsheet. Lastly you will learn how to manage your Run SQL Scripts session.
Speaker: Skip Marchesani
Why Modernize your DB with SQL?!?!
When the System/38 was introduced, DDS was the tool provided for defining the database objects used for storing data within the integrated relational database since SQL was still in it's infancy. Ten years later IBM morphed the System/38 into the AS/400 and added SQL as an alternative interface for defining and accessing database objects.
Today SQL DDL and DML are the industry standards for defining and accessing database objects. In this session we will discuss the advantages of SQL with the current version of IBM's integrated relational database product as well as a comparison of the Native and SQL interface.
Speaker: Kent Milligan
SQL Based Thinking: How SQL Differs from Record Level Access
In this session we will introduce the concept of data "sets" and discuss how the SQL language (DDL and DML) define, access and manipulate rows, not records.
We will compare and contrast the notion of "record at a time" to "set at a time", and work on thinking, programming and processing via sets.
Tables are not files. SQL is declarative, not procedural.
If you are still struggling with the idea of "sets", or still trying to get SQL to behave like record at a time, this topic is for you.
Speaker: Mike Cain
Using Navigator to Create and Managing DB2 Database Objects
Did you know that you can create and manage your DB2 database objects using Navigator's graphical SQL databse interface?
This session will show you how to create, manage, and manipulate your DB2 database objects using Navigator and SQL in place of DDS. You will learn how to use Navigotr's graphical itnerface to the SQL statements CREATE and DROP to create and manage DB2 tables, views, and indexes (physical and logical files). You will learn how to us Navigator's graphical interface to generate or create, save, and then execute SQL scripts that provide function similar to DDS and CRTPF or CRTLF.
An understanding of DB2, DDS, and SQL basics is required for this session.
Speaker: Skip Marchesani
SQL Performance - Where Do I Start
You are running applications that use SQL. The response time and throughput is slow, stagnant, crawling along. The users are complaining, the developers are shrugging their shoulders...
Where do you start to understand the issues, bottlenecks and solution to the standstill?
In this session, we will review the technology and tools for monitoring, analyzing and tuning DB2 for i SQL requests. We will also walk through scenarios to illuminate the methods and strategies for analyzing common SQL performance problems.
Speaker: Kent Milligan
I Didn't Know You Could Do That Using SQL
Tired of writing a one-time RPG program with many lines of code to do special or unique things with your tables and data?
In many cases you can use SQL instead.
In this session you will learn valuable and time saving techniques with SQL that let you avoid writing code. You will learn how to use SQL Subquery or Subselect in an SQL SELECT, INSERT, UPDATE, DELETE or CREATE TABLE statement to reference multiple tables without having to do a join of the tables involved. You will also learn how to use CASE and UNION statements, and where to use Full Outer Join and Hidden Time Stamp columns.
This session is based on the SQL function available in V5R4, V6R1, and V7R1 of OS/400 or i5/OS, assumes you have a basic understanding of relational database concepts and SQL.
Speaker: Skip Marchesani
Integrating XML with DB2
In this session we will take a deeper dive into the new DB2 for i integrated support for storing, retrieving and manipulating XML. The session will include scenarios and examples of using the new XML data type to store a document as well as the new functions and procedures to understand and work with the XML information.
Speaker: Kent Milligan
Take Your SQL to the Next Level
SQL is a very powerful language for accessing and manipulating data. Embedding SELECTs within an SQL request not only allows your statement to perform more powerful data transformations, but also can significantly improve performance and scalability.
In this session, you will learn how to leverage Views, Subqueries, Common Table Expressions, and Derived Tables to boost your productivity and performance. If these SQL terms confuse you, attend this session to get things cleared up!
Speaker: Kent Milligan
Dr. Mike's Rx for High Performance Aggregate Queries
With IBM i 7.1, query optimization and encoded vector index (EVI) technology unique to DB2 for i has been significantly enhanced through the addition of maintained aggregates.
In this session we will introduce the new feature of including aggregates, the function of index only access and benefits of moving up to 7.1 and using the new support.
Speaker: Mike Cain
How to Boost Query Performance Using Indexes
On any platform, good SQL performance depends on good design. And good design includes a solid understanding of indexes: how many to build, their structure and complexity, and their maintenance requirements. This is also true for DB2 for i, which provides a robust set of choices for indexing and accessing tables.
In this session, you will:
1. Understand why indexes are important to the query optimizer.
2. Be able to determine what columns in a table are the best candidates for new indexes.
3. Understand what types of indexes (binary radix or encoded vector) should be created and how they will benefit queries.
4. Understand what new tools are available to analyze your indexing strategy.
Many examples will be included and discussed.
Speaker: Mike Cain
DB2 Web Query Best Practices
DB2 Web Query is IBM's strategic replacement for the Query/400 program product, and it offers a very robust and powerful alternative to its predecessor. To get the MOST out of DB2 Web Query there are some tips, techniques, and best practices that you should be aware of. When implemented, they can improve query performance and make the overall DB2 Web Query experience more enjoyable for developers and end users alike.
This session will also review and demonstrate IBM's latest enhancements to DB2 Web Query
Speaker: Mike Cain
A Deep Dive Into Visual Explain
DB2 for i visual explain is a most powerful tool for representing and understanding a query plan and all the associated data. In this session we will take a deep dive through the window of VE and explore the various panes and information provided to us.
The lecture will include examples on how to effectively read the query graphs and make sense of the execution flow..
Speaker: Mike Cain