Operating System/400 (5722-SS1)462 iSeries Handbook• Explain functionExamines and reports the access method used by individual SQL queries. The output canbe examined to determine whether the access method generated for the query could beimproved by query and/or database changes.Multiple isolation levels. Four record locking options are available to fine tune applicationperformance:–Read Stability (*ALL) : Locks all rows read during transaction and maintains locks untilthe transaction ends.–Repeatable Read (*RR) : Locks all rows read during remote unit of work and maintainslocks until the unit of work ends.–Cursor Stability (*CS) : Maintains locks on the current and previously changed rowsonly, which can offer increased performance for multiple applications contending forthe same rows.–Uncommitted Read (*CHG) : Offers higher performance by not acquiring or examiningdatabase locks for read-only operations.–No Commit (*NONE) : Provides the highest performance of the four isolation levels, bynot examining locks and disabling transaction management: the ability to eitherCOMMIT or ROLLBACK a transaction.• Block INSERT and FETCHProvides applications with the ability to store and retrieve arrays of data directly, insteadof one row at a time.• Automatic record blockingImproves client/server performance by returning rows to the client in blocks rather thanindividually. Subsequent record access of the current block can then be performed locallyat the client without accessing the server. This ability is now provided for all isolationlevels.• Parallel data accessQueries returning or requiring DB2 UDB for iSeries to process large amounts of datarequire significant I/O activity. Due to the iSeries' single-level store architecture, this datais often spread across many physical devices. The parallel data access feature nowallows multiple internal DB2 UDB for iSeries tasks to be activated for each physicaldevice, allowing DB2 UDB for iSeries to transfer data from disk to memory faster thanwith the previous single task I/O architecture.• Query GovernorLong-running queries can have negative performance effects for other users of adatabase. The governor facility allows a time limit to be set for a query, so as to avoid asingle query from consuming unusually large amounts of resources, negatively affecting
