y
DB2 Multisystem tables
New function available in V6R1 whose use may affect SQL performance are derived key indexes,
decimal floating point data type support, and the select from insert statement. A derived key index can
have an expression in place of a column name that can use built-in functions, user defined functions, or
some other valid expression. Additionally, you can use the SQL CREATE INDEX statement to create a
sparse index using a WHERE condition.
The decimal floating-point data type has been implemented in V6R1. A decimal floating-point
number is
an IEEE 754R number with a decimal point. The position of the decimal point is stored in each decimal
floating-point value. The maximum precision is 34 digits. The range of a decimal floating-point number is
either 16 or 34 digits of precision, and an exponent range of 10
-383
to 10
384
or 10
-6143
to 10
6144
respectively.
Use of the new decimal floating-point data type depends on whether you desire the new functionality. In
general, more CPU is used to process data of this type versus decimal or floating-point data. The
increased amount of processing time needed depends on the processor technology being used. Power6
hardware has special hardware support for processing decimal floating-point data, while Power5 does not.
Power6 hardware enables much better performance for decimal floating-point processing. The CPU used
to process this data depends on other factors also, including the application code, the functions used, and
the data itself. As an example, for a specific set of queries run over a particular database, ranges for
increased processing time for decimal floating-point data versus either decimal or floating point are
shown in the chart below in Figure 4.1. The query attribute column shows the type of operations over the
decimal floating-point columns in the queries.
0% to 35%
0% to 20%
Inserts, Updates, and Create Index
35% improved to 500%
40% improved to 600%
Casts ( to/from int, decimal, float)
35% improved to 300%
15% improved to 1200%
Functions ( AVG, MAX, MIN, SUM, CHAR, TRUN)
35% improved to 45%
15% improved to 400%
Arithmetic ( +, -, *, / )
0% to 15%
0% to 15%
Select
POWER6 Processor
POWER5 Processor
Query Attribute
Figure 4.1 Processing time degradation with decimal floating-point data versus decimal or float
Given the additional processing time needed for decimal floating-point data, the recommendation is to use
this data type only when the increased precision and rounding capabilities are needed. It is also
recommended to avoid conversions to and from this data type, when possible. It should not normally be
necessary to migrate existing packed or zoned decimal fields within a mature data base file to the new
decimal floating point data type. Any decimal fields in the file will be converted to decimal float in host
variables, as provided by the languages and APIs chosen. That will, in many cases, be a better performer
overall (especially including existing code considerations) than a migration of the data field to a new
format.
The ability to insert records into a table and then select from those inserted records in one statement,
called Select From Insert, has been added to V6R1. Using a single SQL statement to insert and then
retrieve the records can perform much better than doing an insert followed by a select statement. The
chart below in figure 4.2 shows an example of the performance of a basic select from insert compared to
the insert followed by select when inserting/selecting various number of records, from 1 to 1000. The
data is for a particular database and SQL queries, and one specific hardware and software configuration
running V6R1 i5/OS. The ratio of the clock times for these operations is shown. A ratio of less than 1
indicates that the select from insert ran faster than the insert followed by a select. Select from insert
using NEW TABLE performs better than insert then select for all quantities of rows inserted. Select
from insert using FINAL TABLE performs better in the one row case, but takes longer with more rows.
This is due to the additional locking needed with FINAL TABLE to insure the rows are not modified until
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
43
