Introduction

Keeping ahead of the competition is a top priority in today's ever-changing business environment. From an IT and database perspective, this means that you need to be able to access and present business data in new, insightful ways to your user, and deliver these capabilities yesterday!

Ease of use is a key aspect of the integrated database IBM DB2 for i, which has attracted and spoiled System i and AS/400 customers for a long time. As the complexities and requirements of IBM System i applications have increased in recent years, IBM has significantly enhanced the SQL functionality and performance of DB2 for i, while keeping it easy to use and manage. As you will discover in this article, the V6R1 release delivers another round of sophisticated SQL features, paired with self-managing and self-learning features.

Back to top

Development advancements

Report creation and development is simplified in V6R1 with the support for Grouping Sets and the CUBE and ROLLUP OLAP expressions, which enable you to aggregate data by different dimensions with a single SQL statement. For instance, these new features allow a single query to return sales data to the user and allow those sales results to be viewed at a store, region, or country level. Listing 1 provides an example of using the new grouping set and super group syntax to return data from business orders in several perspectives:

                
SELECT OrderYear, OrderQuarter, OrderMonth, SUM(OrderAmount)
 FROM orders
 GROUP BY GROUPING SETS(
            CUBE(OrderYear,OrderQuarter,OrderMonth),
            ROLLUP(OrderMonth, OrderNo),
                  (DiscCode),
                  (CustomerNo))
      

The SELECT FROM INSERT support is another enhancement that allows you to accomplish more processing on a single SQL statement. This feature is a big help to those developers who take advantage of DB2's ability to automatically generate key values with the identity column attribute. One challenge with identity columns is accessing the key value (for example, part number) that DB2 generated since it requires an additional SQL statement to be executed. The INSERT statement is executed to add the new row, followed by an extra SELECT statement with the Identity_Val_Local function to retrieve the generated value. This two-statement solution is also problematic when a block of rows are inserted with a blocked INSERT because the Identity_Val_Local function only returns the generated key value for the last row inserted.

With this new syntax in DB2 for i 6.1, the INSERT statement on the FROM clause is executed first, and then the outer SELECT statement returns the values that DB2 generated for the part_id identity column. Notice that the FINAL TABLE keyword is required when referencing an INSERT statement on the FROM clause. The second INSERT on FROM example in Listing 1 demonstrates how this new support can be used to access the generated identity values for a blocked insert operation. The ORDER BY INPUT SEQUENCE clause allows you to get the generated values back for the part_id column in the order in which they were generated by DB2. Not only does this new INSERT on FROM syntax solve the issue of retrieving generated values for blocked inserts, but it also allows applications to receive a small performance boost from a single DB2 request that executes multiple database operations.

                
CREATE TABLE parts (part_id INTEGER AS IDENTITY,
 part_type CHAR(10),
 part_qty INTEGER,
 part_desc CHAR(6));

SELECT part_id FROM FINAL TABLE (
 INSERT INTO orders VALUES(DEFAULT, 'Widget1' , 5, 'BIG') );

SELECT part_id FROM FINAL_TABLE (
 INSERT INTO orders VALUES(DEFAULT, 'Widget2', 120, 'SMALL') , 
 (DEFAULT, 'Widget3', 2, 'MEDIUM'))
ORDER BY INPUT SEQUENCE;

The ability to specify a VALUES clause on a FROM clause also makes it easier for developers creating queries that need to reference data from in-memory tables or data structures in a result set. In the example in Listing 3, rate data maintained by the application is referenced in a join operation without first having to go through the overhead of creating and populating a DB2 temporary table. The VALUES in FROM support provides IBM i developers with more flexibility when constructing queries and reports.

                
WITH proposedRates(prType, prRate) AS ( VALUES(?,?),(?,?),(?,?),(?,?) )
 SELECT rmtype, prRate, ((prRate - rmRate)/rmRate)*100 AS RateChgPercent
 FROM rooms, proposedRates WHERE rmtype = prType
   ORDER BY RateChgPercent DESC

The toolbox of built-in SQL functions was also expanded in V6R1 to make life simpler for IBM i developers using SQL. Data privacy laws and policies continue to make the encryption of data a popular topic, so DB2 for i has added support for the popular AES encryption algorithm with the delivery of the ENCRYPT_AES function. Those IBM i applications dealing with date and time values will also benefit from the enhancements added to the VARCHAR_FORMAT and TIMESTAMP_FORMAT functions as well as the following new functions: MONTHS_BETWEEN, ROUND_TIMESTAMP, and TRUNC_TIMESTAMP.

The SKIP LOCKED DATA clause is a great addition for those developers looking to increase throughput of applications supporting a large number of concurrent users. One common misconception about DB2 for i locking is that if you're running a query against a DB2 table that other jobs and users are changing, that DB2 will automatically skip over any row changes that have not yet been committed. Actually, the opposite occurs -- if a row matches the search criteria of the query, then DB2 stops and waits to see if it can acquire the lock on that row. Let's review a simple example using the Cursor Stability (CS) isolation level to make this behavior easier to understand.

One night you're using the internet to search for possible flights to Fiji. Two seconds before you submit your search on the travel agency's website, the following update is performed on their flight database to change the departure time from 4:30pm to 5:25pm as part of a large batch of updates to their flight schedules:

UPDATE flights SET departTime='05:25' 
     WHERE departTime = '4:30' AND flightNum=331 AND destCity='NAN' 

Your flight search request issues the following SELECT statement to find a flight that leaves after 5pm for Fiji. When the SELECT statement processes the row that was just updated, the SELECT statement finds a match because the newly updated departure time of 5:25pm meets the specified selection criteria.

SELECT * FROM flights 
      WHERE departTime >= '05:00' AND destCity='NAN'

Since DB2 does not know that the row has been locked, DB2 for i attempts to acquire a read lock on this row as dictated by the CS isolation level. The UPDATE statement has not yet been committed since it's part of the batch update, meaning that the update lock on the row still exists. Therefore, the update lock conflicts with the requested read lock, so the SELECT statement will now stop and wait until the row lock is released. If the batch update process in this example is long-running, the SELECT statement may fail with a lock timeout error (the default record wait time is 60 seconds on IBM i). The fact that the SELECT statement stops and waits for an update lock on the row to be released is a big surprise to many developers who believe that DB2 will just skip over the locked row and search for other rows in the table meeting the selection criteria. This may be good or bad news depending on the requirements of your application.

With the support in DB2 for i 6.1, developers have their choice of behaviors. They can continue to have applications run with the default behavior of DB2 waiting for locks or have DB2 skip over locked rows. The skipping behavior is activated with the SKIP LOCKED DATA clause, as shown in the following listing:

SELECT * FROM flights 
 WHERE departTime >= '05:15' AND destCity='NAN'
    SKIP LOCKED DATA
      

This SELECT statement will now skip over any rows where it encounters a lock conflict, instead of waiting to acquire the row lock. The SKIP LOCKED DATA clause can also be specified on UPDATE and DELETE statements. This new clause is only honored by DB2 for i with the Cursor Stability and Read Stability isolation levels.

Extended indicators provide UPDATE statements with another new capability in V6R1. Extended indicators enable developers to code a single, generic UPDATE statement instead of coding a different UPDATE statement for each combination of columns that need to be updated. This statement reusability is demonstrated in the coding example found in Listing 4. Notice the WITH EXTENDED INDICATORS clause on the cursor declaration. This clause allows extended indicator variables to be passed on positioned update operations associated with the referenced cursor (in other words, cur1). Extended indicators are then set to a defined set of special values to tell the UPDATE statement whether or not the column should be included in the update operation. In this example, the inds2 extended indicator variable is set to a value of -7, which tells DB2 to omit status column from the positioned update. Thus, only two columns (shipdate and quantity) are actually changed by the embedded UPDATE statement even though three columns are referenced on the SET clause. INSERT statements are also able to utilize extended indicators in V6R1.

                
EXEC SQL DECLARE cur1 CURSOR WITH EXTENDED INDICATORS FOR 
 SELECT order_id,shipdate,quantity,status FROM orders; 
 
 EXEC SQL OPEN cur1;
 EXEC SQL FETCH cur1 INTO :orddat:inds; 
 ...
 inds3 = -7;

 EXEC SQL UPDATE orders
 SET shipdate = :nsdate:inds1, quantity = :nqty:inds2,
 status = :nstat:inds3
          WHERE CURRENT OF cur1;                           
...

Many IBM i developers execute SQL scripts from a CL program or command line interface using the Run SQL Statements (RUNSQLSTM) command. The 6.1 version of the RUNSQLSTM command now has the ability to process and execute SQL statements stored in a stream file. In previous releases, the SQL had to be stored in an IBM i source physical file member. This new stream file support can benefit developers in several different ways. Most importantly, your SQL statements no longer have to be limited to 80 characters in length or less when stored in a stream file. This 80-character limit for statements stored in a source file member made SQL coding quite difficult and tedious for developers. In addition, a stream file can accommodate larger, more complex SQL scripts since they have a size limit of 1TB, which is dramatically bigger than the 16MB limit of a source file member. Those developers using a stream-file-based change management system can now easily execute SQL scripts stored in that type of change management system.

Support for source stream files is also available with the ILE SQL pre-compilers in V6R1. In addition, IBM continued the momentum of improved SQL and RPG integration from previous releases with additional enhancements to the ILE RPG SQL pre-compiler. One of the key improvement areas in V6R1 is the pre-compiler's ability to scope variables at a procedure level. This new capability allows the SQL pre-compiler to properly process and execute code similar to the RPG code in Listing 5:

                
PSubProc1 B EXPORT 
D PI 
D MyArray ds qualified dim(1000) 
D customer 25A 
D region 25A 
 /free 
 EXEC SQL 
 DECLARE c1 CURSOR FOR 
 SELECT customer, region FROM cust_dim; 

 EXEC SQL 
 OPEN c1; 
 EXEC SQL 
 FETCH NEXT FROM c1 FOR 100 ROWS INTO :MyArray; 
 EXEC SQL 
 CLOSE c1; 
 return; 
 /end-free 
P E 
PSubProc2 B EXPORT 
D PI 
D MyArray ds qualified dim(1000) 
D part 55A 
D mfgr 25A 
D brand 10A 
 
 /free 
 EXEC SQL 
 DECLARE c2 CURSOR FOR 
 SELECT part, mfgr, brand FROM part_dim; 
 EXEC SQL 
 OPEN c2; 
 EXEC SQL 
 FETCH NEXT FROM c2 FOR 200 ROWS INTO :MyArray; 
 EXEC SQL 
 CLOSE c2; 
 return; 
 /end-free 
P                 E 

The same variable name, MyArray, is used in both RPG procedures in this example to declare data structures with slightly different attributes. The first procedure declares the MyArray data structure with two fields, and the second procedure contains three fields within the MyArray declaration. In addition, each procedure contains an SQL FETCH statement referencing the duplicated variable name. Previously, the SQL RPG pre-compiler was unable to scope the variable name to the procedure, which resulted in headaches for RPG developers. The result of this deficiency was that the SQL RPG pre-compiler would either fail with an error, flagging the duplicate variables names as illegal (even though it's valid RPG syntax), or unpredictable results would occur at runtime because the SQL RPG pre-compiler was incorrectly sharing one definition of the duplicated variable across multiple procedures. The V6R1 SQL RPG pre-compiler eliminates this hurdle by correctly scoping the variables at a procedure level. Later this year, IBM plans on providing a PTF to make this variable-scoping enhancement also available on V5R4. Here is a listing of some other V6R1 enhancements to the RPG SQL pre-compiler:

• Ability to define variables based on SQLCA variables (SQLSTATE) using LIKE
• Improved integration with IBM application tools for source code error resolution

Not to be overlooked, COBOL programmers will also benefit from the Unicode support added to the ILE COBOL SQL pre-compiler.

IBM i development shops coding with industry-standard data access methods such as JDBC, SQL Call Level Interface (CLI) and ADO.NET will also benefit from the V6R1 enhancements. Java™ data access is improved with the JDBC Version 4.0 enhancements along with dramatic performance improvements in V6R1. The DB2 for i .NET data provider has been enhanced to exploit Version 2.0 of ADO.NET as well as provide support for distributed transactions and multi-row inserts.

Back to top

Porting pearls

Software vendors continue to port their solutions to IBM i to expand the market reach of their applications and to satisfy demand from existing System i customers wanting the latest applications and tools to run natively IBM i. To reduce the effort required in these porting projects, IBM has enhanced the SQL functionality of DB2 for i with V6R1.

Porting Microsoft SQL Server and MySQL tables with automatic or hidden timestamp columns to DB2 for i was a daunting challenge prior to V6R1. When a table in one of these database products includes a timestamp column, the database engine automatically updates that timestamp column each time a row is inserted or updated. This automatic update behavior provides applications an easy mechanism to determine if a table row that was read earlier has changed before they attempt to update that same row in the table. It's simple to identify which row has changed by comparing the value of the timestamp column when the row was read with the current value of the timestamp column in that same row of data. This timestamp comparison is central to applications that use an optimistic locking approach and can now be easily implemented with the DB2 for i hidden timestamp column support available in V6R1.

Let's look at the example in Listing 6, below, to better understand how a hidden timestamp column is defined and how it performs during normal I/O to the table:

                
CREATE TABLE tickets(ticket_ord INTEGER,
 ticket_qty INTEGER,
 ticket_event VARCHAR(10),
 ticket_ts TIMESTAMP NOT NULL 
 IMPLICITLY HIDDEN 
 FOR EACH ROW ON UPDATE 
 AS ROW CHANGE TIMESTAMP);

 INSERT INTO tickets VALUES(1,11,'mvGAME1'), (2,8,'ihGAME4');
 SELECT * FROM tickets;
 UPDATE tickets SET ticket_qty = 6 WHERE ticket_ord = 2;
 SELECT ticket_ord, ticket_qty, ticket_event, 
 ticket_ts FROM tickets;

The new syntax for hiding a column is specified on the CREATE TABLE statement with the IMPLICITLY HIDDEN clause on the ticket_ts column definition. This HIDDEN clause indicates the column will not be visible on any SQL statement unless it's explicitly referenced by name. This behavior is illustrated in Figure 1, which contains the output for the first SELECT statement (SELECT * FROM tickets) in Listing 6. The ticket_ts column is excluded from the result set since that column was not explicitly referenced on the SELECT statement.

The FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP clause results in DB2 changing the ticket_ts column value each time that a row is inserted or updated into the table. The effect of this clause is displayed in Figure 2. Figure 2 contains the result of the final SELECT statement in Listing 5, which explicitly includes the ticket_ts column along with the unhidden columns in the Tickets table. Observe the values in the ticket_ts column to see how DB2 automatically maintained the values for this column during the INSERT and UPDATE statements in Listing 5, even though the statements did not include any references to the ticket_ts column.

New data types in 6.1 also make table definitions more portable to DB2 for i. The National Character data types (NCHAR, NVARCHAR, and NCLOB) provide a standard way of defining a Unicode column with the UTF-16 encoding. The Decimal Float data type, DECFLOAT, is a new numeric data type added for compatibility with the other DB2 products that combines the attributes of decimal and float with extended accuracy.

Porting databases and applications from DB2 for z/OS® and DB2 for Linux®, UNIX®, and Windows® products to DB2 for i is also simplified in V6R1. DB2 for i 6.1 enhancements for DB2 family portability include Full Outer Join syntax support and a new feature allowing DB2 for i to ignore unsupported syntax. The SQL scripts for other DB2 databases often contain low-level configuration syntax (for example, tablespace options) on the SQL data definition language statements that prevented the scripts from executing on DB2 for i until the unsupported syntax was removed. To eliminate this tedious work, DB2 for i 6.1 now just ignores this type of syntax and options that will never be supported on DB2 for i. This is due to the fact that the DB2 for i database engine and operating system automatically handle many low-level database administration tasks that require manual configuration with the other DB2 products. Listing 7 contains examples of statements with the ignored syntax highlighted in bold. An SQL warning will be returned to flag whenever syntax is ignored on DB2 for i. Porting of database creation scripts should be much easier by eliminating the step of deleting unnecessary syntax from script files.

                
                CREATE TABLESPACE tspace1 MANAGED BY DATABASE 
 USING(device '/dev/rcont $N' 20000);

CREATE TABLE new_table (c1 INTEGER PRIMARY KEY, c2 CHAR(10))
                INDEX IN tspace1;

SQL CLI is frequently the data access programming interface used by DB2 applications that need to be ported to DB2 for i. In V6R1, these SQL CLI applications are also easier to port with the addition of support for the CLI Wide-Character APIs. The CLI Wide-Character functions are found in applications that support Unicode or Double-byte data.

Back to top

Performance boosters

Over the last couple of releases, the SQL Query Engine (SQE) has been one of the IBM key initiatives for boosting SQL performance to new peaks. This performance advancement for SQL workloads on DB2 for i continues in V6R1 with the removal of two major roadblocks that prevented the usage of the SQE: National Language Sort Sequences (NLSS) and functions relying on low-level translation function. Low-level translation capability is required by commonly used SQL built-in functions, such as Upper and Lower. The NLSS support in IBM i is frequently used in global markets where the application requires the character data be sorted in a manner that matches the local language, instead of the default ordering (in other words, *HEX), which closely mirrors the sorting of the English alphabet. With these roadblocks now removed, the most common items that prevent the usage of SQE in V6R1 are:

• Select/omit and derived logical files defined on an underlying table
• Logical file references on FROM clause
• Non-SQL interfaces (for example, OPNQRYF, Query/400)

Note: Since SQL is the strategic interface for DB2 for i data access and the industry standard, non-SQL interfaces are a very low priority for the SQL Query Engine. SQE will only support SQL-based interfaces for the foreseeable future.

IBM has also changed the default value for the Ignore_Derived_Index QAQQINI configuration parameter to enable more SQE usage in V6R1. The new default value of *YES allows the SQL Query Engine to be used in environments where SQL statements are referencing DB2 objects created with DDS. This new value allows the SQE query optimizer to ignore any keyed logical files that contain select/omit criteria or key field derivations it encounters during optimization, instead of rerouting execution to the older Classic Query Engine (CQE). The Ignore_Derived_Index QAQQINI parameter can be manually set back to *NO to have the SQE optimizer behave the same as releases prior to V6R1.

The most interesting V6R1 SQE enhancements are those new features that leverage the extensible architecture of the SQL Query Engine. One of the key 6.1 enhancements is the introduction of self-learning query optimization into SQE. A self-learning query optimizer is able to analyze poor-performing query plans and dynamically adjust its internal algorithms based on feedback to select a better query plan on future executions. In V6R1, the SQE optimizer automatically analyzes poor performing query plans to examine the I/O characteristics as well as record retrieval patterns and compare them to the values used during optimization of the query. If the query optimizer detects significant mismatches, the optimizer modifies its assumptions and algorithms during the next execution of that SQL request to generate a better plan. To complement the learning optimizer, the DB2 runtime engine is also equipped with adaptive technologies that allow the plan of a currently running query to be modified on the fly to improve the efficiency of the query for the remainder of the run.

In addition, the SQE optimizer can also improve SQL performance in V6R1 with new indexing technology. This new DB2 for i indexing technology is a feature called SQL derived indexes. Long-time customers may view this enhancement as the SQL interface finally catching up with DDS by allowing SQL indexes to be created with expressions and selection criteria just as i5/OS and OS/400 developers have been doing for years on keyed logical file definitions. This view is on partly true. Listing 8 demonstrates how SQL-derived indexes take these capabilities even further with the ability to specify SQL built-in functions as part of the key expression:

                
CREATE INDEX upper_cname ON 
     customers(UPPER(company_name))

The UPPER function shown in the previous section will most likely be one of the more popular SQL built-in functions used on derived index definitions. Developers often use the UPPER function to implement case-insensitive searches, such as the query shown in Listing 9. By forcing all of the company names to be converted to upper case, the query will return all occurrences of IBM even if the data entry clerks entered that company name differently (for example, IBM or ibm).

                
SELECT customer_id, customer_phone FROM customers 
 WHERE UPPER(company_name)= 'IBM' 

Performance problems could arise on past releases due to the fact that the UPPER function prevented the DB2 for i query optimizer from using an index to speed up this search. The new SQL-derived index support allows the index in Listing 8 to be created and the SQE query optimizer to use this index to quickly retrieve the customer data since the key expression on the index exactly matches the column expression in the case-insensitive query.

Shared-weight sort sequences have been successfully used by applications to solve the performance issues associated with case-insensitive searches. However, that approach does require more complex configuration when creating your database and applications. In addition, sort sequences can't be used with more complex expressions such as:

WHERE UPPER(CONCAT(FirstName,CONCAT(' ',LastName)) = 'DALLAS CLARK'.

Just about any SQL built-in function can be included in the key definition of an SQL-derived index.

Listing 10 contains an example of an SQL-derived index containing selection criteria. This is known as a sparse index since the index does not contain a key value for every row in the table. While this sparse SQL index can be created on V6R1, it should be noted that the SQE query optimizer currently does not have the ability to use a sparse index in a query implementation. The SQE query optimizer can only choose SQL-derived indexes with key expressions. IBM plans on enhancing the SQE query optimizer in the future with the ability to use sparse indexes.

                
CREATE INDEX cust_ix1 ON 
     customers(customer_id) WHERE activefld='A'

SQL workloads on IBM i should also run faster on V6R1 with the streamlining of the SQL full open code path. Full open is the processing that DB2 has to perform the first and second time an SQL statement is run within a connection (or job) -- some tests have shown a 10% performance improvement.

Applications utilizing stored procedure calls will receive a nice boost from improved caching of repeated stored procedure calls. In addition, IBM also made improvements in the code generation utilized for SQL procedures, functions, and triggers. One of those improvements is the ability for DB2 for i to generate service program objects for SQL procedures, which is accomplished by specifying the PROGRAM TYPE SUB clause on the CREATE PROCEDURE statement. Simple SQL routines will need to be recreated on V6R1 in order to benefit from these improvements.

Back to top

Tooling advancements

Tuning database performance can be an arduous task when there are SQL statements being executed from remote clients such as browsers and ADO.NET applications. A system administrator can easily detect the IBM i server jobs that are consuming extra resources because of a long-running SQL statement. However, it's quite difficult to trace back and determine which remote client or program submitted the SQL request to DB2 for i. To address this issue, IBM has introduced the following special registers in V6R1 to allow applications to use them in an effort to simplify this problem determination exercise:

                
  CURRENT CLIENT_ACCTNG
CURRENT CLIENT_APPLNAME 
 CURRENT CLIENT_PROGRAMID 
  CURRENT CLIENT_USERID 
  CURRENT CLIENT_WRKSTNNAME 

Application developers can instrument their applications to set the client register values with one of the following interfaces:

• Set Client Information (SQLESETI) IBM i API
• CLI SQLSetConnectAttr function
• JDBC setClientInfo connection method
• SYSIBM.WLM_SET_CLIENT_INFO stored procedure

Once these registers have been set by the application, administrators can retrieve these settings by collecting database monitor data or by using the System i Navigator SQL Details for a Job task by just right-clicking on the Database object in the navigation tree. Output from the System i Navigator task can be found in Figure 3. In earlier releases, this Navigator task was known as Current SQL for a Job.

A close review of the SQL details output in Figure 3 shows that IBM has instrumented some of its own products and tools to assign values to these new client special registers. The UPDATE statement was run by user KMILL from the System i Navigator Run SQL Scripts interface.

The usability of the SQL Performance Monitors in System i Navigator took a major step forward in V5R4, and that continues with V6R1. The SQL Monitors Analyze task makes it relatively easy to identify a set of SQL statements with performance issues. However, sharing those SQL statements with a co-worker was easier said than done. On releases prior to V6R1, the only options available were a primitive copy and paste or telling the user how to run the same analysis report. The new Save Results task in V6R1 allows the results of a report to be saved directly in a wide variety of output formats. This new interface is shown in Figure 4. This dialog not only allows the report data to be saved into a shareable format, but it also provides an option to launch the PC application (in this case, Microsoft Excel) that's associated with this file type.

Column customization is another V6R1 enhancement that makes System i Navigator output easier to read and analyze. The SQL Performance Monitor analysis reports are very comprehensive in returning every piece of information that could be affecting DB2 performance. The downside of this comprehensive approach is that users can be overwhelmed by the numerous columns of data returned -- some reports include dozens of columns. Furthermore, the default ordering of the columns in the reports may slow your ability to find the most performance information. For example, some reports require the user to scroll two or three screens to the right to find the SQL statement text. The column customization support in V6R1 solves both of these problems by providing the ability to reorder the columns and remove columns from the output display. This customization is accomplished through the Select Columns window, as shown in Figure 5. This column customization is also available on other interfaces, such as the Plan Cache, Show Indexes, and Index Advisor. Once an interface has been customized, the reporting tools will remember those settings on future reports.

In addition to these great interface improvements, IBM has also enhanced the data being collected by the database monitor. One example of new data collected by the DB2 for i monitors is the new client special registers (for example, CURRENT CLIENT_APPLNAME) that were discussed earlier. An even bigger addition is the monitor's ability to collect detailed data for Query/400 and OPNQRYF requests. With this addition, the System i Navigator Visual Explain tool and SQL Performance Monitor analysis reports can be used for the first time to analyze and tune the performance of Query/400 and OPNQRYF requests.

The usability of the index analysis tools in System i Navigator is also improved with V6R1. Customers love the real-time index advice provided by the system-wide Index Advisor added in V5R4. However, the interface for creating indexes based on the advice has not been as well-received. If the DB2 for i advisor suggested ten different indexes that you wanted to create from that interface, you manually had to click on each of the ten advised indexes to create the index. The V6R1 Index Advisor interface allows you to more quickly accomplish this task by simply highlighting a group of advised indexes and selecting the Show SQL task to generate all the Create Index statements with a single operation.

The Index Evaluator has been a user favorite since the tool was introduced in V5R3. This is due to the fact that this tool allows users to easily evaluate which indexes and logical files (LF) are being used by DB2 for i and which index and LF objects are just wasting disk space and system resources used to maintain them. The drawback with this tool is that indexes could only be evaluated at a table level. The Index Evaluator is accessed by executing the Show Indexes task. Whenever it was necessary to perform index evaluation for all 50 tables (physical file) in a schema (library), the user would manually have to run the Show Indexes task on each table in the collection. Fortunately, that's no longer an issue with V6R1 because the Show Indexes task can be executed at a schema level by just right-clicking on the Tables object in a schema and then selecting this task, as shown in Figure 6:

The final System i Navigator enhancement to highlight is the V6R1 modifications to the Health Center. The Health Center has been enhanced to start reporting on DB2 runtime limits, such as the number of LOB (Large Object) locators or CLI handles per connection (or job).

System i Navigator is not the only IBM tool that simplifies the day-to-day work of i5/OS developers and administrators. The IBM Rational Data Architect, Version 7, (see Resources) provides improved data modeling support for DB2 for i databases with Fix Pack 7.0.0.3, including enhancements for DDS-created databases.

Customers and developers will also benefit from the DB2 Web Query enhancements being delivered in 2008 (see Resources). The new DB2 Web Query Run Time User licensing option can significantly reduce the costs and simplify deployment for IBM i customers that have a large number of users that only need to run reports. Report scheduling and distribution will also be available to customers with the new Report Distributor option. To make it easier for developers to leverage and integrate DB2 Web Query reports into their applications, IBM is delivering a software development kit (SDK) and Web services interfaces. In addition, a new Excel plug-in enables the execution of DB2 Web Query reports from within a spreadsheet.

Back to top

Stepping up simplicity

It should be clear that V6R1 lifts DB2 for i to new heights, especially when you consider that this article is just the DB2 V6R1 highlights and not a complete list. From the great additions, to SQL that enables rapid solution development, to the sizzling DB2 performance gains, the DB2 for i V6R1 enhancements provide a great foundation for managing business data in a simplified manner, allowing your company to focus on running your business instead of your DBMS. So run, don't walk, to V6R1.

Resources

Learn

• DB2 for i5/OS V6R1 overview: See a brief overview of the many enhancements that DB2 for i5/OS V6R1 delivers. Get more detail about these enhancements, as shown in the included chart.
  
  
• IBM DB2 for i online information: Browse the DB2 for i references.
  
  
• "i5/OS DBA: New Derived Key Indexes in DB2 for i5/OS V6R1" (IBM Database Magazine, February 2008): Speed data access for even complex SQL.
  
  
• IBM DB2 for i porting information: Porting to DB2 for i? Find out how.
  
  
• IBM Rational Data Architect: Find more information about Rational Data Architect, which simplifies data modeling and integration design, enabling architects to discover, model, visualize, and relate diverse and distributed data assets.
  
  
• DB2 Web Query for i: Find out more information about DB2 Web Query for i, which offers a set of modernized tools for a robust, extensible and productive reporting solution.
  
  
• DB2 for i SQL Performance Workshops: Attend this workshop to understand the science of query optimization, the art of query optimization, SQL performance techniques and considerations, and SQL performance tools and analytical methods.
  
  

Get products and technologies

• Rational Data Architect V7.0: Download a trial version of Rational Data Architect V7.0, an enterprise data modeling and integration design tool designed to help data architects design relational and federated databases, understand data assets and their relationships, and streamline database projects.
  
  

Discuss

• Participate in the discussion forum.
  
  

About the author

Kent Milligan is a senior DB2 consultant in ISV Solutions Enablement for IBM i. Kent spent the first eight years of his IBM career as a member of the DB2 development team in Rochester after graduating from the University of Iowa in 1989. He speaks and writes regularly on DB2 for i topics.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Development advancements
• Porting pearls
• Performance boosters
• Tooling advancements
• Stepping up simplicity
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers