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
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online