Performance features in DB2 V9 for z/OS – Part 9

As I talked about in the previous weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these features can really benefit your standard DBA operations, improve application performance and overall availability of your systems and data.

ORDER BY and FETCH FIRST n ROWS in a Sub-Select

Another one of the new enhancements, ORDER BY and FETCH FIRST n ROWS in sub-select and full-select, is especially great for web and Java application programmers.  You may be surprised because your programmers have been using ORDER BY and FETCH FIRST n ROWS for some time, but in DB2 Version 9 for z/OS they can use these keywords within sub- and full-selects.

Doing an ORDER BY and FETCH FIRST n ROWS within a sub-select is a very powerful feature because it can quickly limit a DB2 search for the data you are interested in.  For example, your application calls for retrieving  all the songs and albums from the top 10 artists on Billboard.  An ORDER BY and FETCH FIRST 10 ROWS sub-query is perfect for these requirements. When used properly within an SQL JOIN, it limits the data retrieval from a table of artist songs and albums to only those top ten artists.

SELECT SONG, ALBUM, ARTIST
FROM ALL_MUSIC
WHERE ARTIST IN
(SELECT ARTIST
FROM TOP_BILLBOARD_ARTISTS
FETCH FIRST 10 ROWS
ORDER BY COUNTDOWN_NBR ASC)

This is new feature is very helpful when the application requirement is to get the most, least, expensive or cheapest items from a table.  Given all the business intelligence and analytical application requirements, it’s clear why this new feature was included in DB2 Version 9.

This feature is also very good at limiting the number of rows retrieved from larger tables involved in any type of JOIN.  The optimizer recognizes the FETCH FIRST n ROWS phrase as limiting access to database partitions or various rows when it determining its SQL access path.

Check out your applications and see where this feature can help your performance with DB2 Version 9 for z/OS.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>