The majority of mainstream IBM users will likely find Version 2 of the DB2 relational database the most important element in IBM’s torrent of new products this week – not least because it represents what so many of them have been demanding. No false modesty on IBM’s part: it says unequivocally that DB2 Version 2 offers a breakthrough in relational database performance and new function that enhances DB2’s use for critical production applications and decision support environments. It claims that high volume transaction processing has demonstrated up to 438 transactions per second, and that performance gains can be realised for many queries as a result of enhancements to the DB2 optimiser and sort. Perhaps the most important new feature is the addition of referential integrity, which enforces defined data relationships. An audit facility is also now provided for tracking data access and security violations within DB2 and there is a governor – that’s right, just like the thing they put on diesel engined buses to stop the driver going too fast – the DB2 Resource Limit Facility, which enables a site to prevent dynamic queries from consuming excessive CPU resources. IBM also notes that DB2 benefits from MVS/ESA improvements in performance and recovery for multiple address space operations for greater efficiency in hardware resource usage.
Referential Integrity Going into a little more detail, referential integrity ensures the consistency of data values between related columns of two different tables. The DB2.2 support for referential integrity can be applied to new or existing tables and programmers should be more productive now that they don’t need to worry about including it in application programs. For those in the dark about the subject, IBM explains: Suppose a user defines an Employee table that contains employee and department numbers, and a Department table that contains department numbers. In addition, suppose the user wants to ensure that for every department number in the Employee table there must be an equal and unique department number in the Department table. Such a constraint defined on the Employee table is a referential constraint. The department number in the Department table is called the primary key, and the department number in the Employee table is called the foreign key in this constraint. Enforcement of this constraint provides referential integrity, and DB2.2 records and enforces this data relation-ship, and enforcement by application logic is not necessary. The specific extensions include the facility to define referential constraints, primary keys and foreign keys using the Create and Alter table statements. DB2 also enforces referential constraints on the Load, Insert, Update and Delete operations. DB2 ensures that primary key values are unique and that foreign key values always have a corresponding primary key value. The Delete operation enforces one of three delete rules specified when the relationship was defined: Restrict will not allow deletion of a primary key value that has dependent foreign key values. This is the default rule. Cascade will allow the deletion of a primary key value and deletes all dependent foreign key values. Set Null will allow the deletion of a primary key value and sets all dependent foreign key values to null. IBM also notes that DB2 utilities are extended to maintain referential integrity so that if for example, recovery to a prior point in time leaves data in violation of defined referential constraints, a new status recorded in the DB2 catalogue, Check Pending, indicates such possible violations. The Check utility is extended to locate, identify and repair constraint violations. And IBM promises that Structured Query Language/Data System – SQL/DS – will provide referential integrity capabilities – one day. That’s referential integrity. Performance gains of 51% with ESA
On the performance front, DB2.2 is the most wonderful thing that ever happened in relational database according to IBM, which claims that in an MVS/ESA environment, the cumulative effect of enhancemen
ts expands the number and scope of applications that can take advantage of the benefits of relational database technology, and that performance gains over and above the ones listed may result from performance improvements of related subsystems such and IMS and CICS. IBM claims that laboratory performance measurements on a 3090-600E with 256Mb main and 256Mb Expanded store using DB2.2 with IMS/VS Fast Path data communications demonstrated 438 transactions per second at 85.1% CPU usage under ESA with average transit time of under one second for credit card authorisation and 300 transactions per second at 84.1% CPU usage with an average transit time under 1 second for debit processing. And with the standard DB2 workload with IMS/VS as the Data Communication front end, 186 transactions per second at 89.8% CPU usage under ESA with an average transit time under 1 second using Wait For Input transactions. That compares with 123 transactions per second using the current DB2 1.3 release with 91.4% CPU usage under XA – a 51% improvement. And the gain from ESA is put at up to 13% compared with DB2.2 under MVS/XA. And needless to say, the biggest improvement will be seen by users in High Volume Transaction work. Version 2 of DB2 also introduces numerous improvements to benefit query processing. Many queries, says IBM, especially those involving large sorts, can experience a substantial improvement in both elapsed time and CPU time and the IBM labs benchmarked a 3090 300E – 128Mb main, 128Mb Expanded – running DB2.2 against the same box running DB2 1.3. It found simple queries using Order By on 8m records used 51% less CPU time and ran up to four times faster. A query composed of many IN-list and OR predicates used 81% less CPU time and executed 3.7 times faster. And with regard to batch performance, a batch program fetching 300 columns took 53% less CPU time and a Load operation took 15% less CPU time. Enhancements to the DB2 algorithms also provide substantially improved performance for the Create Index operation and result in an index with optimal organisation, IBM claims, and says the labs found that a Create Index operation took 35% less CPU time on a four-column index (one clustered, three non-clustered) of 255,000 rows, with a nine-times improvement in elapsed time. And that governor? The Resource Limit Facility controls the CPU resources used by a dynamic SQL statement and allows termination of long running queries that exceed a specified limit. DB2 Version 2 is $3,600 a month, and is also available for a one-time $108,000 on 4381s and small 3090s, $172,800 on large 3090s. It is out in October, November here.
Content from our partners
