Intel AS/400 RISC Server, 170 Servers manual Query Attribute POWER5 Processor POWER6 Processor

yDB2 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-383to 10384 or 10-6143to 106144 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.

Query Attribute	POWER5 Processor	POWER6 Processor
Select	0% to 15%	0% to 15%
Arithmetic ( +, -, *, / )	15% improved to 400%	35% improved to 45%
Functions ( AVG, MAX, MIN, SUM, CHAR, TRUN)	15% improved to 1200%	35% improved to 300%
Casts ( to/from int, decimal, float)	40% improved to 600%	35% improved to 500%
Inserts, Updates, and Create Index	0% to 20%	0% to 35%

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