Intel 7xx Servers, 170 Servers manual DB2 i5/OS V5R4 Highlights, Records Inserted/Selected

the statement is complete. The implementation to invoke the locking causes a physical DASD write to the journal for each record, which causes journal waits. Journal caching on allows the journal writes to accumulate in memory and have one DASD write per multiple journal entries, greatly reducing the journal wait time. So select from insert statements with FINAL TABLE run much faster with journal caching on. Figure 4.2 shows that select from insert with FINAL TABLE and journal caching on ran faster than the insert followed by select for all but the 1000 row insert size.

	Select	6.00
		5.00
Clock Time Ratio	from Insert / Insert then	4.00
		3.00
		2.00
		1.00
	Select
		0.00
		1	10	100	1000

Records Inserted/Selected

Select from Insert: Final Table

Select from Insert: Final Table Journal caching on

Select from Insert: New Table

Select from Insert: New Table Journal caching on

Figure 4.2 Select from Insert versus Insert followed by Select clock time ratios

In addition to updates for new functionality, in V6R1 substantial performance improvements were made to some SQL code paths. Improvements were made to the optimizer to make query execution cost estimates more accurate. This means that the optimizer is producing more efficient access plans for some queries, which may reduce their run time. The time required to full open and optimize queries was also largely reduced for many queries in V6R1. On average, for a group of greatly varying queries, the total open time including optimization has been reduced 45%. For a given set of very simple queries which go through a full open, but whose access plan already exists in the plan cache, the full open time was reduced by up to 30%.

In addition to the optimization and full open performance improvements, for V6R1 there was a comprehensive effort to reduce the basic path of a simple query which is running in re-use mode (pseudo open), and in particular is using JDBC to access the database. The results of this are potentially large reductions in the CPU time used in processing queries, particularly very simple queries. For a stock trade workload running through JDBC, throughput improvements of up to 78% have been measured. For more information please see Chapter 6. Web Server and WebSphere Performance.

4.2 DB2 i5/OS V5R4 Highlights

In i5/OS V5R4 there were several performance enhancements to DB2 for i5/OS. With support in SQE for Like/Substring, LOBs and the use of temporary indexes, many more queries now go down the SQE path. Thus there is the potential for better performance due to the robust SQE optimizer choosing a better plan along with the more efficient query engine processing. Also supported is use of Recursive Common

IBM i 6.1 Performance Capabilities Reference - January/April/October 2008

© Copyright IBM Corp. 2008	Chapter 4 - DB2 Performance
	44