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
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
Creating and Managing DB2 Database Objects Using SQL
Did you know that you can create and manage ALL your DB2 database objects using SQL?
This session will show you how to create, manage, and manipulate your DB2 database objects using SQL in place of DDS. You will learn how to use SQL statements like CREATE and DROP to create and manage DB2 tables, views, and indexes (physical and logical files).
You will also explore the various interfaces that provide SQL based DB2 management capability
- Graphical interface in Navigator
- Run SQL Scripts (aka SQL Script Center)
- RUNSQLSTM command
You will learn how to 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
Putting 'Run SQL Scripts' to Work for You
Run SQL Scripts – also known as the SQL Script Center or Script Center – is part of the Database function in iSeries or System i Navigator. 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 System i. Run SQL Scripts and Interactive SQL do overlap in function, but each can do things that the other cannot. When teaching SQL related topics I use Run SQL Scripts to demonstrate various SQL functions and also answer SQL syntax and function questions.
Now that the SQL Script Center is becoming more widely known and used, when I teach and use it for demonstrations I am getting more and more questions about how it works. One reason for the increasing number of questions is that there is little information available for Run SQL Scripts via IBM’s System i Information Center web site, and IBM has no softcopy publication available for it. The only documentation available is Help, which has improved over time, but on occasion can still be a challenge when trying to find an answer to a question. So based on the more frequently asked questions I get while teaching, this session will show how you can put Run SQL Scripts to work for you, and will include a review of the 6.1 enhancements.
Speaker: Skip Marchesani
What You Need to Know About DB2 Query Optimization
The integrated, self learning and adaptive DB2 for i Query optimizer is second to none when producing SQL query plans. Moving at the speed of light this statistical processing powerhouse quickly transforms the most complex of queries into the most efficient set of database engine instructions.
Understanding how the DB2 for i SQL Query Optimizer decides to implement your query can go a long way when proactively and/or reactively tuning queries. In this session we will cover the ins and outs of the optimizer and what really happens when producing a query plan.
Speaker: Mike Cain
New Stuff in Run SQL Scripts for V6R1
This session will review and demonstrate many of the new functions in V6R1 of Run SQL Scripts including:
1. Change font type and size for both the SQL script and result set windows
2. Save SQL scripts to a System i source file
3. Print a displayed result set
4. Save a displayed result set as an Excel spreadsheet, CSV file, or text file
5. Toggle between Column Headings and Column Names for the result set window
Speaker: Skip Marchesani
SQL Performance - Where Do I Start
You are running applications that use SQL. The response time and throughput is slow, stagnent, crawling along. The users are complaining, the developers are shrugging their shoulders...
Where do you start to understand the issues, bottnecks 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
The Newest DB2 Index Technology - IBM OmniFind
Explore the newest indexing technology for DB2 for i - the IBM OmniFind Text Search Server. Come and learn how your applications can perform advanced, linguistic text searches on plain text fields as well as searches against rich text documents in formats such as Microsoft Word and Adobe PDF.
By the end of the session, attendees will be able to:
1. Understand the differences between a traditional DB2 index and a Text Search Index
2. Be able create a text search index and perform searches utilizing the index
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 and V6R1 of OS/400 or i5/OS, assumes you have a basic understanding of relational database concepts and SQL.
Speaker: Skip Marchesani
SQL Tuning: Monitoring DB2 for i Performance
As more and more applications use SQL to access DB2 for i, the ability to identify and resolve performance issues is a critical skill. In this session, we review the technology and tools for monitoring, analyzing and tuning DB2 for i SQL requests, and walk through scenarios to illuminate the methods and strategies for analyzing common SQL performance problems.
Speaker: Mike Cain
DB2 Web Query for i Overview
DB2 WebQuery is the replacement for Query/400 with extensions into Business Intelligence. At first glance it might seem a bit complex because of the robustness of capabilities; however, you can start small and grow into the solution at your own pace.
So where do you start?
What's new and different?
Attend this session to improve productivity, performance and get you out of the RPG report writing business!
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
From the Crystal Ball: Enhancements to DB2 for i Features, Functions and Benefits
In this session, we will look into the proverbial crystal ball and ponder the potential enhancements to DB2 for i. This will include new things involving:
- The SQL language
- Interaction between high level language programs and stored procedures
- System i Navigator
- Query optimization
- Query execution
- Enhanced DB2 support for XML
- Transparent column encryption
Speaker: Mike Cain
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 and V6R1 of OS/400 or i5/OS, assumes you have a basic understanding of relational database concepts and SQL.
Speaker: Skip Marchesani