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, you’re right. If the AS/400 is to compete as a server in today’s 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 it’s 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 IBM’s 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 IBM’s 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 they’ve 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 I’m out of room here. I hope to provide more information next month. Two things should be evident, however, from what I’ve 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.

SQL

Ted Holt is IT manager of Manufacturing Systems Development for Day-Brite Capri Omega, a manufacturer of lighting fixtures in Tupelo, Mississippi. He has worked in the information processing industry since 1981 and is the author or co-author of seven books. 

---

MC Press books written by Ted Holt available now on the MC Press Bookstore.

		Complete CL: Fifth Edition Become a CL guru and fully leverage the abilities of your system. List Price $79.95 Now On Sale
		Complete CL: Sixth Edition Now fully updated! Get the master guide to Control Language programming. List Price $79.95 Now On Sale
		IBM i5/iSeries Primer Check out the ultimate resource and “must-have” guide for every professional working with the i5/iSeries. List Price $99.95 Now On Sale
		Qshell for iSeries Check out this Unix-style shell and utilities command interface for OS/400. List Price $79.95 Now On Sale