Administrator > DB2

Port of Entry

The real power of SQL as a business data access language is that it simplifies coding by allowing you to accomplish more work on a single request and to move portions of your business processing into the database engine itself. Because SQL is the industry standard for database access, you also benefit from the fact that you can port and reuse existing SQL-based business logic from database server to database server. The new function introduced with OS/400* V5R2 continues to build on these SQL advantages.

V5R2 extends the SQL capabilities of DB2* UDB for iSeries with new features designed to provide application developers with greater flexibility and help eliminate the commonly encountered roadblocks involved in consolidating and porting non-DB2 solutions to the iSeries. V5R2 also simplifies the management of DB2 UDB for iSeries with SQL support for field reference files, switchable database clustering and extensions to the iSeries Navigator graphical interface.

Let DB2 UDB Do the Work
A major SQL enhancement in V5R2 is Identity columns, which not only ease porting efforts, but provide rich functionality and flexibility for application developers leveraging SQL. Identity columns can be used to generate artificial or surrogate key values by telling DB2 to auto-increment the column value as new rows (or records) are inserted into the table. Instead of creating code that increments to the next ID or number value, DB2 UDB does the work for you.

Identity columns are created by specifying the identity options for a column created with a numeric data type (numeric, decimal, integer, smallint, bigint) with a scale of zero (see Statement 1). The identity options allow you to control both the initial and increment values. The first employee record inserted into this table is assigned an employee ID of 10, the second employee is assigned 20 and so on. To guarantee that this value is unique, a unique constraint or index must be defined over the Identity column. A table can only have one Identity column.

Support for unions in a view also allows you to shift some programming effort onto DB2. If, for example, a database contains sales history data for each year stored in separate tables, anytime end users wanted to view the sales history over multiple years they had to remember to include each sales history table in their report or execute the query with a Union operator. Instead of forcing end users or programmers to remember to combine (union) all of the sales history tables, a single SQL view can be created to simplify this process (see Statement 2). This provides a single object, sales_history, to reference and allows them to rely on DB2 to combine all of the sales history tables together appropriately. Unions are also supported in common table expressions and derived tables as of V5R2.

A smaller V5R2 enhancement that will also make porting and coding easier is the elimination of the Order By restriction that required any column used to order/sort query results to also be included in the results. Statement 3 wasnt allowed in V5R1 but is supported with V5R2.

OS/400 V5R2 continues the DB2 UDB on iSeries momentum with a set of enhancements that provides flexibility for application development. This should also result in a wider range of user solutions, as leading software vendors can more easily port their SQL-based solutions to the iSeries.

Next page: >>