Application Development Enhancements

 <  Day Day Up  >  

The second category of enhancements pertains to application development and programming. DB2 V7 offers many new features to simplify the process of programming DB2 applications, thereby helping developers become more productive.

Stored Procedure Enhancements

Stored Procedure Builder (SPB) is a new feature that provides a point-and-click environment for building stored procedures. The SPB can be used to develop stored procedures for both the distributed and mainframe DB2 environments. The SPB can be used either standalone or in conjunction with a development tool (such as IBM VisualAge, Microsoft Visual Basic, and Microsoft Visual Studio). SPB supports SQL Procedure Language and Java as stored procedure host languages.

The second big application enhancement is SQL Procedure Language support. SQL Procedure Language enables stored procedures to be written in an extended, procedural SQL language. IBM's SQL Procedure Language is compatible with the ANSI SQL/PSM specification. It extends the SQL language to support additional functionality, effectively making SQL a more computationally complete language. Examples of the extended programming capabilities added to SQL for SQL Procedure Language include

  • Assignment statements

  • CASE - LEAVE

  • Cursors

  • IF - THEN - ELSE

  • Local variables

  • LOOP , REPEAT , and WHILE

  • FOR , CALL , and RETURN

  • GET DIAGNOSTICS

  • SIGNAL and RESIGNAL

Before SQL Procedure Language programs can be executed, first the code needs to be converted into C by the Stored Procedure Builder. Once converted, the code goes through the program preparation process. So, you get the benefit of writing code using the simple SQL Procedure Language dialect and the performance benefit of optimized C code. However, you will need to own a C compiler to take advantage of SQL Procedure Language.

Finally, for stored procedures, as of V7 DB2 provides the capability to issue COMMIT and ROLLBACK statements inside a stored procedure. The COMMIT or ROLLBACK will affect the entire unit of work, including any work done by the calling program, not just the work done within the stored procedure itself. So, you will need to use caution when issuing a COMMIT or ROLLBACK within a stored procedure.

Scrollable Cursors

Probably the most significant new application development enhancement made to DB2 for V7 is scrollable cursors. A scrollable cursor provides the ability to scroll forward and backward through the data once the cursor is open . This can be achieved using nothing but SQL ”no host language code (COBOL, C, and so on) is required to facilitate a scrollable cursor in DB2 V7. A scrollable cursor makes navigating through SQL result sets much easier. There are two types of DB2 scrollable cursors: SENSITIVE and INSENSITIVE .

A SENSITIVE scrollable cursors is updateable, meaning it can access data changed by the user or other users. An INSENSITIVE scrollable cursor, however, is not updateable, so it will not show any changes made.

To use scrollable cursors, you must use declared temporary tables, another new feature of DB2 Version 7. Declared temporary tables are discussed later in this appendix in the section "Data Management Enhancements." DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor.

Scrollable cursors allow developers to move through the results of a query in multiple ways. The following keywords are supported when fetching data from a scrollable cursor:

  • NEXT ” Will FETCH the next row, the same way that the pre-V7 FETCH statement functioned

  • PRIOR ” Will FETCH the previous row

  • FIRST ” Will FETCH the first row in the results set

  • LAST ” Will FETCH the last row in the results set

  • CURRENT ” Will re- FETCH the current row from the result set

  • BEFORE ” Positions the cursor before the first row of the results set

  • AFTER ” Positions the cursor after the last row of the results set

  • ABSOLUTE n ” Will FETCH the row that is n rows away from the first row in the results set

  • RELATIVE n ” Will FETCH the row that is n rows away from the last row fetched

For both ABSOLUTE and RELATIVE , the number n must be an integer. It can be either a positive or a negative number, and it can be represented as a numeric constant or as a host variable.

All of the FETCH options for scrollable cursors also reposition the cursor before fetching the data. For example, consider the following cursor logic:

 

 DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR FOR SELECT   FIRSTNAME, LASTNME    FROM     DSN8710.EMP    ORDER BY LASTNME; OPEN csr1; FETCH LAST csr1 INTO :FN, :LN; 

Issuing this SQL will declare a scrollable cursor named csr1 , open that cursor, and then FETCH the last row from the cursor's results set. The FETCH LAST statement will reposition the cursor to the last row of the results set, and then FETCH the results into the host variables as specified. Scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries.

But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so.

Limiting the Number of Rows Fetched

Application developers frequently need to retrieve a limited number of qualifying rows from a table. For example, maybe you need to list the top ten best selling items from inventory. There are several ways to accomplish this prior to DB2 V7 using SQL, but they are not necessarily efficient.

The first reaction is to simply use the WHERE clause to eliminate non-qualifying rows. But this is simplistic, and often is not sufficient to produce the results desired in an optimal manner. What if the program only requires that the top ten results be returned? This can be a somewhat difficult request to formulate using SQL alone. Consider, for example, an application that needs to retrieve only the ten most highly paid employees from the EMP sample table. You could simply issue a SQL request that retrieves all of the employees in order by salary, but only use the first ten retrieved. That is easy, for example

 

 SELECT   EMPNO, FIRSTNME, LASTNAME, SALARY FROM     DSN8710.EMP ORDER BY SALARY DESC; 

You must specify the ORDER BY clause with the DESC keyword. This sorts the results into descending order, instead of the default, which is ascending . Without the DESC keyword, the "top ten" would be at the very end of the results set, not at the beginning.

But that does not really satisfy the requirement ”retrieving only the top ten. It merely sorts the results into descending sequence. So, the results would still be all employees in the table, but in the correct order so you can view the "top ten" salaries very easily. The ideal solution should return only the ten employees with the highest salary and not merely a sorted list of all employees.

You can code some "tricky" SQL to support this request for all versions of DB2, such as the following:

 

 SELECT   EMPNO, FIRSTNME, LASTNAME, SALARY FROM     DSN8710.EMP A WHERE 10 > (SELECT COUNT(*)             FROM   DSN8710.EMP B             WHERE A.SALARY < B.SALARY             AND   B.SALARY IS NOT NULL) ORDER BY SALARY DESC;  SELECT   EMPNO, FIRSTNME, LASTNAME, SALARY   FROM     DSN8710.EMP A   WHERE 10 > (SELECT COUNT(*)   FROM   DSN8710.EMP A   WHERE A.SALARY < B.SALARY)   AND SALARY IS NOT NULL   ORDER BY SALARY DESC;   ---------+---------+---------+---------+---------+---------+---------+-   DSNT408I SQLCODE = -206, ERROR:  B.SALARY IS NOT A COLUMN OF AN INSERTE  UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS  COLUMN OF THE TRIGGERING TABLE OF A TRIGGER  

This SQL is portable from version to version of DB2 (as well as to another DBMS, such as Oracle or SQL Server). And, of course, you can change the constant 10 to any number you wish, thereby retrieving the top 20, or top 5, as deemed necessary by the needs of your application. Because the SALARY column is nullable in the EMP table, you must remove the nulls from the results set. The ORDER BY is required to sort the results in the right order. If it is removed from the query, the results will still contain the top ten, but they will be in no particular order.

DB2 V7 provides an easier and less complicated way to limit the results of a SELECT statement ”the FIRST keyword. You can code FETCH FIRST n ROWS , which will limit the number of rows that are fetched and returned by a SELECT statement. Additionally, you can specify a new clause ” FETCH FIRST 1 ROW ONLY ”on SELECT INTO statements when the query can return more than one row in the answer set. Doing so informs DB2 to ignore any other rows.

There is one difference between the new V7 formulation and the other SELECT statement we reviewed, and that is the way "ties" are handled. A tie occurs when more than one row contains the same value. The previous query we examined might return more than 10 rows if there are multiple rows with the same value for price within the top ten. Using the FIRST keyword, DB2 will limit the number of rows returned to ten, even if there are other rows with the same value for price as the number ten row in the results set. The needs of your application will dictate whether ties are to be ignored or included in the result set. If all "ties" need to be included in the results set, the new V7 feature might not prove to be helpful.

External SAVEPOINTS

DB2 V7 allows you to set a SAVEPOINT within a transaction. You can think of a SAVEPOINT as a sub-UOW (unit of work) "stability" point. You can code application logic to undo any data modifications and database schema changes that were made since the application set the SAVEPOINT . Application development should be more efficient using SAVEPOINT s because you will not need to include contingency and what-if logic in your application code.

Issuing a SAVEPOINT does not COMMIT work to DB2. It is simply a mechanism for registering milestones within a transaction or program. Let's learn by example. Consider the following pseudo-code:

 

 SAVEPOINT POINTX ON ROLLBACK RETAIN CURSORS; Subsequent processing. . . ROLLBACK TO SAVEPOINT POINTX; 

The ROLLBACK will cause any data or schema changes made in the "subsequent processing" to be undone.

It is permissible to code multiple SAVEPOINT s within a UOW, and you can ROLLBACK to any SAVEPOINT (as long as you do not reuse the SAVEPOINT name ). The UNIQUE keyword can be specified to ensure that the SAVEPOINT name is not reused within the unit of recovery.

There are two clauses that can be specified to further define the nature of the SAVEPOINT when a ROLLBACK is issued:

  • RETAIN CURSORS ” Specifies that any cursors opened after the SAVEPOINT is set are not tracked and will not be closed when rolling back to that SAVEPOINT .

  • RETAIN LOCKS ” Specifies that any locks acquired after the SAVEPOINT is set are not tracked and will not be released when rolling back to the SAVEPOINT .

Even if RETAIN CURSORS is specified, some of the cursors might not be useable. For example, if the ROLLBACK removes a row (that is, rolls back an INSERT ) upon which the cursor was positioned, an error will arise.

Row Expressions

SQL becomes even more flexible under DB2 V7 with row expressions. Row expressions allow SQL statements to be coded using more than one set of comparisons in a single predicate using a subquery. The net result is that multiple columns can be compared within the scope of a single SQL predicate ”possibly against multiple rows on the right side of the predicate. Once again, the best way to understand this feature is by viewing an example:

 

 SELECT * FROM   SAMPLE_TABLE WHERE  (COL1, COL2) IN (SELECT COLX, COLY                         FROM   OTHER_TABLE); 

You can readily see the difference: Two columns are coded on the left side of the predicate, thereby enabling two columns to be selected in the SELECT statement on the right side of the predicate. Of course, a row expression need not be limited to only two columns; multiple columns can be specified, so long as the number of columns on the left matches the number of columns on the right side of the predicate. Row expressions bring more flexibility and can greatly simplify certain types of SQL statements.

SQL Assist

Another feature that will aid application developers is SQL Assist. The SQL Assist feature is a GUI-driven tool to help you build SQL statements such as SELECT , INSERT , UPDATE , and DELETE . It is accessible from the following "products":

  • Control Center

  • Stored Procedure Builder

  • Data Warehouse Center

Precompiler Services

DB2 V7 supports more robust Precompiler Services. An API is provided that can be called by a host language compiler or preprocessor. Precompiler Services enable developers to precompile and compile programs in a single step, instead of multiple steps. This makes the development environment more flexible, easier to use, and able to offer better portability between members of the DB2 Family. Initial support for Precompiler Services is provided for COBOL only, but support for other languages is planned for later DB2 releases.

Additional Application Development Improvements

IBM has made numerous additional improvements to application development aspects of DB2 in Version 7. Some of the more interesting enhancements include

  • Improving DB2's support of JDBC and ODBC, including support for JDBC 2.0 and ODBC 3.0.

  • Improvements in SQL optimization and better parallel query support.

  • The ability to run ODBC/CLI programs as a static application (instead of only as dynamic).

  • Support for encouraging or discouraging index access for small tables. A DSNZPARM value is provided that can be set to give the DB2 optimizer guidance on the threshold for what constitutes a small table in your shop.

  • The ability to code a self-referencing sub- SELECT on searched UPDATE and DELETE statements. In previous releases of DB2, the WHERE clause cannot refer to the table (or view) being modified by the statement. For example, the following SQL is legitimate as of DB2 V7 and can be used to implement a 10% raise for employees who earn less than their department's average salary:

     

     UPDATE DSN8710.EMP E1 SET SALARY = SALARY * 1.10 WHERE SALARY < (SELECT AVG(SALARY)                 FROM   DSN8710.EMP E2                 WHERE  E1.WORKDEPT = E2.WORKDEPT); 

    DB2 will evaluate the complete subquery before performing the requested UPDATE .

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net