| Previous | Table of Contents | Next |
Stored procedures provide one of the best ways to enhance the AS/400’s performance of client/server applications. A CALL statement in SQL allows a client application to call a stored procedure and have it execute on the AS/400 server. In this way, an entire business transaction can be accomplished with a single access to the server. Without stored procedures, numerous database accesses to the server might be required to perform a single business transaction. A stored procedure can, with only a couple of exceptions, be any program in the AS/400. The program can be written in any HLL and can even contain SQL statements. Access to a stored procedure is available only through the SQL interface using the CALL statement.
In V3R1, the first step was taken toward the implementation of Unicode on the AS/400 by encoding object names in some of the integrated file system components using this multibyte encoding scheme. Unicode allows multiple character sets to coexist in the same encoding scheme.
With the RISC models, this support was expanded to allow database files to store data encoded in Unicode (UCS2, level 1). For example, French, German, English, Hebrew, Chinese, Russian, and other language data can exist in one database file. SQL has the capability to convert to and from Unicode, so that query activities and data manipulation on Unicode data can be done in an application. Also available is locale support, an X/Open standard for different cultural environments on the AS/400. This support enables programmers to create applications that adapt themselves to various cultural dependencies, such as currency symbols, date and time formats, numeric formats, collating sequences, and case conversions.
Most relational databases provide some form of a query governor to ensure that no single query runs too long. After some preset time, the governor in most databases stops the query. DB2/400 uses a predictive query governor to prevent a query from running if it will run beyond a preset time. In this way, system resources are not wasted on a query that will be stopped anyway.
A query optimizer analyzes the way the database is to be accessed to perform a query before it is started. As part of this analysis, the optimizer predicts the amount of time it will take to perform the query. This predicted time is then compared to a query time limit associated with the current user. If the predicted time exceeds the time limit, a message is sent to the user. The user can then choose either to end the query or to run the query even though it exceeds the limit.
DB2/400 provides several mechanisms to allow performance prediction and tuning for various database operations. For example, you can use an EXPLAIN command to predict or review the execution characteristics of a query. This function gathers information about how SQL is used in a program. You then can use the information from EXPLAIN to tune the performance of a query either by making changes to the database or to the query request. Still other functions let you block fetch and insert operations, which means you can manipulate arrays of data with a single command.
Also implemented are advanced caching mechanisms for database operations. Users can define both an expert cache and a static cache. The user can define an expert cache in memory that automatically expands and contracts in size. The expert cache uses artificial intelligence (AI) algorithms to dynamically change the cache size based on workload, predictive database activity, and allocated resources. Likewise, the user can define a static cache in memory to allow an entire table or a portion of a table to fit into a memory-resident area.
The AS/400 allows an application program to access a database on a remote system as well as the one on the local system; the location of the data is transparent to the application. This means the application can process a database file without knowing where the file resides. It also means parts of the database can be moved to another system without requiring changes to the application programs.
The capability to access a database on a remote system and for other remote systems to access AS/400 data is accomplished through the implementation of two key architectures. One is the Distributed Relational Database Architecture (DRDA), and the other is the Distributed Data Management (DDM) architecture.
The SQL interface uses DRDA to access remote data. An SQL CONNECT statement that includes the name of the remote database is first used to establish the link to the remote database. A directory on the local system is used to look up the remote database name and to identify the specific system where the remote database resides. After the communications link has been made between the systems, SQL requests can be sent to the remote database. The database manager on the remote system performs the SQL request and returns the records that satisfy the request to the local system.
The AS/400’s native database interface uses the DDM architecture. A DDM file defines the file name on the remote system and the name of the remote system itself. When an application program requires remote data, the DDM file is linked to the program with a command. After the communications link between the systems has been established, the application program can work with the remote file. With the DDM approach, file processing is performed on the local system, as opposed to the DRDA approach, where the processing occurs on the remote system. DDM sends all records in the file back to the local system, whereas with DRDA, only the records that meet the selection criteria are sent back to the local system. If only a few records are involved in a particular database operation, the DRDA approach may result in better application performance because there is less communications overhead.
The AS/400 works with databases that support the DRDA and DDM architecture as just described. The AS/400 also provides an integrated approach to support access to other databases. This support allows an AS/400 to work directly with any vendor’s database on another system in the network. In addition to a Distributed Database Directory in OS/400, there is a Distributed Database Driver Manager. This driver manager works with the drivers for the target databases or file systems. These drivers for various Unix and PC databases let an AS/400 application work with these databases in the same way it does with any DRDA database.
| Previous | Table of Contents | Next |