Digging deeper into DB2 UDB

Doug Mack, DB2 UDB for iSeries Product Marketing at IBM, answers questions on logical files, query tools and more.

You read all the tips and articles and listen to the webcasts, but sometimes you still have questions. Doug Mack, DB2 UDB for iSeries Product Marketing at IBM, fresh from his recent webcast "DB2 UDB for iSeries -- V5R2 enhancements" is here to help. Read what he says about logical files, query/sql performance and query tools.

Have a question of your own for Doug? Send it to editor@search400.com.


Q: Using DDS I can create a logical file with a unique key (file level keyword UNIQUE) and a subset of the data using SELECT or OMIT keywords. How can I do this with DB2 UDB? Specifically, DB2 UDB version 7.2 Service pack 5 -- running on Windows 2000.
A: This is more a question of how to get this function in SQL vs. a specific DB2 version. But unfortunately, there is not a similar function in the SQL CREATE INDEX statement. You could create an index with Unique Key and then a View with select/omit logic.

Q: Where can I get information on the Windows-based query tool?
A: Information on QMF for Windows can be found at http://www-3.ibm.com/software/data/qmf. Click in the QMF for Windows for iSeries under the EDITIONS box to read details. For local assistance, if you'd like to shoot me an e-mail at mackd@us.ibm.com, I can put you in touch with a salesperson in your area.

Q: Can you recommend a specific query performance class?
A: Several options existing for obtaining query/sql performance. I'll list them by most high level to most comprehensive:

1. At the DB2 UDB for iSeries Web site there are links to self-guided education. Here you'll find many presentation style learning vehicles, including SQL Performance Basics, Coding for SQL Performance, and Query and Performance Optimization Update. Check it out at http://www-919.ibm.com/servers/eserver/iseries/developer/education/ibo/view.html?biz.

2. At COMMON and the iSeries Technical Conferences there are many sessions that deal with query optimization. You can take the entire roadmap of optimization courses or limit it to a few to gain just the basics.

3. An SQL Optimization Workshop is offered several times a year. This four and a half-day class is the most comprehensive course that provides hands-on labs and taught by two the best performance analysts in the world. Information on this course can be found at
http://www-1.ibm.com/servers/eserver/iseries/service/igs/db2performance.html.

Q. Are DDS logicals included in the DB2 UDB optimizer analytical engine as well as SQL created logicals?
A. Yes, whether you create logicals via DDS or indexes via SQL, the DB2 Optimizer will leverage those for both statistical purposes and data access purposes.

Q: Using DDS I can create a logical file with a unique key (file level keyword UNIQUE) and a subset of the data using SELECT or OMIT keywords. How can I do this with DB2 UDB, specifically DB2 UDB Version 7.2 Service pack 5 running on Windows 2000?
A: This is more a question of how to get this function in SQL vs. a specific DB2 version. But unfortunately, there is not a similar function in the SQL CREATE INDEX statement. You could create an index with Unique Key and then a View with select/omit logic.

Q: Is the multiple database on a single I series the same as an instance in DB2 UDB for Unix/NT?
A: Similar, but not the same. Because of OS/400's unique "Single Level Store" implementation, there is no concept of a "database" -- the entire addressable space of the storage pool available to OS/400 is one database. Within that database, there is a concept of libraries or schemas, but all within a single system-wide database.

With Multiple Namespace support in V5R2 of DB2 UDB for iSeries, you can leverage a concept called Independent Auxiliary Storage Pools (IASPs) to allocate disk storage across a number of storage "pools." These IASPs can be leveraged to provide some of the benefits of independent, isolated databases on an iSeries server. Multiple IASP can contain database schemas with the same schema name, a shared application can access a specific instance of that schema by specifying attributes in the user profile or job attributes.

Q: Will Visual Explain show the underlying keys that were created on a (Non SQL View) logical file?
A: Yes, Visual Explain does show the key fields for a keyed logical file if that keyed logical file is used in the implementation of a query. Other iSeries Navigator tools do not provide access to the key field definitions for a logical file, since they classify all logical files as SQL Views (and SQL views cannot be keyed).

Q: What are the improvements from a WebSphere/SQL perspective?
A: Several things are improved in V5R2 related to WebSphere-based applications. First, a new transaction manager more efficiently processes and provides administrative utilities for WebSphere-based transactions. Performance improvements and the ability to manage these transactions are the key benefit. Second, the SQL Query Engine introduced at V5R2 provides a foundation for improved performance of SQL-based applications. Finally, WebSphere applications can benefit from the enhanced SQL functionality delivered in V5R2 with features such as the auto-generating key capabilities of identity columns and unions in a view.


This was first published in January 2003

Dig deeper on iSeries system performance and monitoring

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close