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.
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
0.00
1.00
2.00
3.00
4.00
5.00
6.00
1
10
100
1000
Records Inserted/Selected
Cl
o
c
k
T
im
e
Ra
ti
o
S
e
lect
f
ro
m
I
n
sert
/
I
n
se
rt
t
h
e
n
S
e
le
ct
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
