Quick! What do binary large objects (BLOBs), column-level security, user-defined data types, and user-defined functions have in common? If you answered that these are DB2/400 features that are available with SQL but not through DDS, youre right. If the AS/400 is to compete as a server in todays market, IBM must bring DB2 UDB for AS/400 in line with the rest of the DB2 family. This means SQL interfaces to support new features. The following paragraphs describe more SQL-only features of V4R5.
Operations Navigator
A tool that is becoming more and more important to those who think that computing is synonymous with database processing is Operations Navigator (OpsNav). If your impression of OpsNav is that its a cutesy toy for system operators but hardly useful to database administrators, take another look. IBM added more functionality to OpsNav in V4R5, and even more goodies are on the way.
Visual Explain is a new OpsNav tool that draws pictures to show how the system satisfied an SQL query. For instance, if the query optimizer read an entire physical file to select records, you would see a table-scan icon on your screen. In other words, icons replace those cryptic messages you see in the job log when you run a query under debug. See Figure 1 for an example.
Moving the pointer over any of the various icons opens a pop-up window with more information. Visual Explain may tell you, for example, that creating an index over a certain column will eliminate a table-scan. If you want, Visual Explain will create that index for you. The idea, of course, is that you use the information Visual Explain provides to improve performance of queries. Visual Explain does not recommend a course of action.
Visual Explain, which grew from research at IBMs Almaden Research Center, is new to the AS/400 but not to IBM platforms. According to information I found at the IBM Research DB2 Visual Explain page (at www.almaden.ibm.com/cs/starwinds/explain/ homepage.html), it was first developed for DB2/2 and DB2/6000. It was subsequently released for DB2 for OS/390. (See IBMs Visual Explain page at www-4.ibm.com/ software/data/db2/os390/db2ve for more details.)
Another new OpsNav goody for database weenies is that they can see the SQL command that is currently executing. For example, suppose response time is unacceptable. After doing some digging, you discover that a certain job is hogging the system. What do
you do? Well, as of V4R5, OpsNav can show you the SQL statement that that job is running.
IBM has further enhanced the AS/400 implementation of SQL. Here are some of the improvements:
The SIGNAL, RESIGNAL, and GET DIAGNOSTICS statements have been added to provide error handling support in SQL procedures.
The SOUNDEX and DIFFERENCE functions allow for fuzzy matching of character strings. SOUNDEX converts a string into a code; DIFFERENCE can compare these codes. You might use these functions to help find a customer whose name sounds like Pearce, even if he spells it Pierce or even Peirce.
Many new math functions have been added.
SQL CALL now supports Java stored procedures.
Distributed Relational Database Architecture (DRDA) now supports password encryption using DB2 encryption standards.
The Call Level Interface (CLI) has been changed to be more like ODBC. This aids in porting applications to the AS/400 from other platforms.
One IBMer emphasized to me that some of the enhancements are useful for querying but that the main reason theyve been released is to beef up the SQL procedures language. (Have you mastered that one yet?)
More to Come
IBM has more database enhancements, but Im out of room here. I hope to provide more information next month. Two things should be evident, however, from what Ive told you. First, IBM continues to bring DB2/400 in line with the other DB2 database management systems. And, second, if you want the new database power, you must use the new stuff, such as Operations Navigator and SQL.
LATEST COMMENTS
MC Press Online