| Several SQL options can be applied to both tables and indexes to make them more efficient for your specific SQL workload. Deciding on what features to take advantage of should be based on how your application behaves. If you are unaware of the behavior, you can use statement event monitoring to capture all SQL that is sent to the database engine (discussed in Chapter 12, "Performance Tuning"). Table Considerations For LUW and zSeries, you can take advantage of the following features when either creating or altering tables: APPEND ON (LUW only). Used for tables that have heavy INSERTs to avoid searching the table for free space. With this option, you simply append the row to the end of the table. LOCKSIZE TABLE. Change a table's behavior from row-level locking to table-level locking. Use this option for read-only or exclusive access tables, which reduces the amount of time to lock rows and reduces the amount of memory used for locks. On zSeries, DB2 normally chooses page-level locking as the default, and the LOCKSIZE is set at the table space level. PCTFREE. Used to maintain free space on each data page of the table, which helps speed up INSERTs, LOADs, and REORGs (don't use with APPEND ON). In zSeries, PCTFREE and FREEPAGE clauses are part of both the CREATE and ALTER TABLESPACE statements and CREATE and ALTER INDEX statements. VOLATILE. Used to encourage an index scan on tables whose cardinality changes frequently. This will only work in the cases where the index contains all the columns referenced or when the index is able to apply a predicate in the index scan. ACTIVATE VALUE COMPRESSION (LUW only). Can save disk space when a table uses many null and system default values. This can help improve query time when the volume is significant.
On iSeries, you can use the ALLOCATE clause of the column definition in the CREATE TABLE statement to reserve a portion of the variable length columns in the fixed-length portion of the row to avoid a secondary I/O operation. Temporary Table Considerations Temporary tables are ideally suited for SQL procedures that generate large amounts of temporary data which need to be stored for no longer than an application connection session. If the table is only needed for the duration of the SQL procedure execution (including the returning of a result set from it), you can declare the temporary table within the SQL procedure. If the temporary table will be shared among SQL procedures of the same session, the table should be declared outside the SQL procedures to increase manageability. You may find that defined global temporary tables are a nice fit to your application. Especially, if you need a table that Does not require locking. Does not cause inserts into the system catalog tables. Can have minimal logging or no logging. Is only visible to the application that created it.
Now with DB2, you can define indexes on these tables so that they can efficiently handle very large data volumes. Additionally, on LUW and zSeries they also support the collection of statistics. This makes temporary tables increasingly important to SQL procedure developers. Note On zSeries, there are two types of temporary tables: the first one uses the CREATE GLOBAL TEMPORARY TABLE statement, and the second one uses the DECLARE GLOBAL TEMPORARY TABLE statement. For portability with the other platforms, use the second type of temporary tables. Refer to Chapter 10, "Leveraging DB2 Application Development Features," for a comparison between these two statements. In this chapter, temporary tables on zSeries refer to the DECLARE TEMPORARY TABLEs.
Index Considerations On LUW and zSeries, the following list includes some of the features that can optionally be applied to indexes (the Design Advisor will make full use of these in LUW) to enhance performance: PCTFREE (LUW and zSeries only). Used to keep free space on pages of the index. If the index is on a read-only table, there is no need for free space, so a PCTFREE of 0 will use pages more efficiently. However, if the table is not read-only, a PCTFREE of 10 can help speed up inserts by having approximately 10 percent of the page size available as free space. This should be higher for tables with clustered indexes to ensure that the clustered indexes do not become too fragmented. In such a case where there is a high volume of inserts, a value of 1540 may be more appropriate. ALLOW REVERSE SCANS (LUW only). Allows for an index to be scanned bi-directionally, which means quicker retrieval of ascending and descending result sets. This has no negative performance impact because it is done by default at the DB2 code level. INCLUDE (LUW only). Can be used to include additional non-indexed columns in the index to promote faster index-only access and avoid fetches. UNIQUE. Used to efficiently enforce the uniqueness of a column or set of columns. CLUSTER (LUW and zSeries only). Allows the contents of the table physically ordered by the index, which can help greatly improve the efficiency of ORDER BY statements using the same ordering as the columns of the clustered index.
On zSeries, backward-index scans will occur automatically under the following conditions: The index is defined on the same columns as the columns in the ORDER BY clause, or the index is defined on the same columns as the columns in the ORDER BY clause followed by other columns. For each column that is in the ORDER BY clause, the ordering that is specified in the index is the opposite of the ordering that is specified in the ORDER BY clause.
On iSeries, consider using ENCODED VECTOR indexes for decision support and reporting environments. |
