您可以做下列事情来减少锁：
- 确保应用程序正在使用最低的隔离级别。
- 经常执行 COMMIT。
- 当执行很多更新时，在更新前显式地锁整个表（使用 LOCK TABLE 语句）。
- 尽量使用 Cursor Stability 隔离级别（缺省情况），以便减少被持有的共享锁的数量。（如果应用程序能够承受脏读，那么 Uncommitted Read 可以进一步减少锁。）
LOCKTIMEOUT (DB)
指定应用程序在获得一个锁之前所等待的秒数。这可以帮助避免全局死锁的情况。如果该值为 -1，如果出现锁等待，则应用程序将会出现。Bill Wilkins 关于锁的文章 也以较大的篇幅包含了这一点。
对于生产系统中的 OLAP，一开始为 60 （秒）比较好，对于 OLTP 大约为 10 秒比较好。对于开发环境，应该使用 -1，以识别和解决锁等待的情况。如果有大量的并发用户，可能需要增加 OLTP 时间，以避免回滚。
如果 "Lock Timeouts"是一个较高的数，那么可能由以下原因造成：(1) LOCKTIMEOUT 的值太低，(2) 某个事务持有锁的时间有所延长，或者(3) 锁升级。
SHEAPTHRES_SHR (DBM)
这是对一个实例中并发共享的排序可以消耗的内存总量的硬性限制。这个值只有在以下情况下才适用：(1) INTRA_PARALLEL=YES，或者 (2) Concentrator 在 （MAX_CONNECTIONS > MAX_COORDAGENTS） 范围内。对于在 WITH HOLD 选项下使用游标的 排序，将从共享内存中为其分配内存。
"Shared Sort heap high water mark"显示一次最多可以分配的共享排序 内存。如果这个值总是远远低于 SHEAPTHRES_SHR，那么应该减少 SHEAPTHRES_SHR，以便为其他数据库函数节省内存。如果这个值刚好接近于 SHEAPTHRES_SHR，那么可能需要增加 SHEAPTHRES_SHR。 "Total Shared Sort heap allocated"是从排序堆空间中为所有排序 分配的总页数。如果元素值大于或等于 SHEAPTHRES_SHR，则意味着这些排序没有得到由 SORTHEAP 参数定义的完整排序堆空间。增加 SHEAPTHRES_SHR 的大小，以帮助避免这种情况。
在设置时，应尽量使其为 SORTHEAP 的倍数。
SORTHEAP (DB)
这个参数指定为私有排序 使用的最大私有内存页数，或者指定为共享排序使用的最大共享内存页数。每个排序都有一个独立的排序堆，这是由数据库管理器在需要的时候分配的。
通常大家都理解得很好的是，当一个排序所需的内存量超过了 SORTHEAP 时，就会发生 排序溢出。然而理解得不够好的一点是，如果统计信息已过时，或者数据有偏差，并且没有收集到发布统计信息，这时一旦 DB2 请求一个太小的堆，而实际的排序操作超出了所请求的量，就会发生溢出。因此，使统计信息保持时新十分重要。此外，应确保排序 不是一个丢失的索引的结果。
对于 OLTP，一开始最好是设为 128，对于 OLAP，则设置在 4096 - 8192 之间。如果有很多的 "Sort overflows" （两位数）那么很可能需要增加 SORTHEAP。如果 "Number of hash join overflows" 不为 0，则按照 256 逐次增加 SORTHEAP，直到它为 0。如果 "Number of small hash join overflows" 不为 0，则按 10% 的速度增加 SORTHEAP，直到小散列连接溢出数为 0。
CHNGPGS_THRESH (DB)
使用这个参数来指定缓冲池中被更改页面所占的百分比，此时将启动异步的页面清除器将更改写入到磁盘，以便在缓冲池中为新的数据空出空间。在只读环境下，不使用页面清除器。在 OLTP 中，使用 20-40 这样的一个值应该可以提高性能（在更新活动庞大的情况下使用 20），因为使这个值更低一些将使 I/O Cleaners 在从脏缓冲池页面写出数据时更具有侵略性，但是每次做的工作却变少了。如果没有很多的 INSERT 或 UPDATE，则对于 OLTP 和 OLAP 来说，缺省的 60 应该就比较好了。
如果 "Dirty page steal cleaner triggers"是一个两位数，则试着降低之。如果 "Buffer pool data writes"较高，而 "Asynchronous pool data page writes"较低，则试着降低这个参数。
从 FixPak 4 起，有另一种页面清除算法，这种算法可以提高特定缓冲池的性能。您需要令概要注册表变量 DB2_USER_ALTERNATE_PAGE_CLEANING=YES，这样忽略 CHNGPGS_THRESH。确保 NUM_IOSERVERS 至少为 3，否则它会拖新算法的后腿。
NUM_IOCLEANERS (DB)
这个参数指定一个数据库的异步页面清除器的数量，异步页面清除器将更改后的页面从缓冲池写到磁盘。一开始将这个参数设为等于系统中 CPU 的数量。当触发了 I/O Cleaners 时，它们会同时启动，因此您不希望有那么多的清除器，以致影响性能和阻塞其他处理过程。
如果 Asynchronous Write Percentage (AWP) 是 90% 或更高，则减少 NUM_IOCLEANERS，如果 Asynchronous Write Percentage (AWP) 小于 90%，则增加 NUM_IOCLEANERS。
AWP = (( "Asynchronous pool data page writes"+ "Asynchronous pool index page writes") * 100) / ( "Buffer pool data writes"+ "Buffer pool index writes")
NUM_IOSERVERS (DB)
I/O 服务器用于执行预取操作，而此参数则指定一个数据库的 I/O 服务器的最多数量。非预取 I/O 是从数据库代理调度的，因此不受此参数的约束。一开始将该参数设置为等于数据库所跨的物理磁盘数（即使是一个磁盘阵列中的许多磁盘或者一个逻辑卷中的许多磁盘） + 1 或 2，但是不大于 CPU 的 # 的 4-6 倍。
如果您很快看到 "Time waited for prefetch (ms)"，那么您或许想添加一个 IO Server，以查看性能是否有提高。
MAXFILOP (DB)
这个参数指定每个数据库代理所能打开的最大文件数。如果打开一个文件时被打开的文件数超出了这个值，则要关闭该代理正在使用的一些文件。过度的打开和关闭都会降低性能。SMS 表空间和 DMS 表空间文件容器都是视作文件来对待的。通常 SMS 使用的文件要更多一些。
增加该参数的值，直到 "Database files closed"为 0。
LOGPRIMARY、LOGSECOND 和 LOGFILSZ (DB)
LOGPRIMARY 指定要预先分配空间的主日志文件的数量，而 LOGSECOND 是按照需要来分配空间的。LOGFILSIZ 定义每个日志文件的大小。
如果 "Secondary logs allocated currently"的值很大，那么就可能需要增加 LOGFILSIZ 或 LOGPRIMARY （但是要确保 LOGPRIMARY + LOGSECOND 不超过 256）。还可以使用 "Maximum total log space used (Bytes)"来帮助指出对日志文件空间（主日志 + 从日志）的依赖性。
日志文件的大小对灾难恢复有一定的影响，因为在灾难恢复中要使用日志发送（log shipping）。日志文件比较大时，性能会更好些，但是可能潜在地增加丢失事务的程度。当主系统崩溃时，最近的日志文件及其事务可能无法发送到从系统，因为在失败之前没有关闭该文件。日志文件越大，随着日志文件的丢失，丢失事务的程度也越大。
LOGBUFSZ (DB)
这个参数允许指定用作在将日志记录写到磁盘之前的缓冲区的数据库堆（DBHEAP）的数量。当提交一个事务或者日志缓冲区已满的时候，就要将日志记录写入磁盘。对日志记录进行缓冲将导致将日志记录写入磁盘的活动不那么频繁，但每次要写的日志记录会更多。对于 OLTP，一开始以至少 256 页为佳，对于 OLAP，则以 128 页为佳。如果常常看到多于一对的 "Log pages read"，那么可能需要增加这个值。如果发生了回滚，也可能要读取日志页。
如果在试图增加 LOGBUFSZ 时收到一个错误，那么可以按相同数量增加 DBHEAP，然后再次尝试。
PKGCACHESZ (DB)
这个包缓存用作静态和动态 SQL 语句的缓存部分。缓冲包允许数据库管理器减少内部开销，因为它消除了在重新装载一个包时访问系统编目的需要；或者，对于动态 SQL，消除了重新编译的需要。
PKGCACHESZ 应该大于 "Package cache high water mark (Bytes)"。如果 "Package cache overflows"不为 0，那么可以尝试通过增加 PKGCACHESZ 来使这个计数器变为 0。
Package Cache Hit Ratio (PCHR) 应该尽可能接近 100%（而不从缓冲池中获取所需的内存）。用下面的公式来计算：
PCHR = (1-( "Package cache inserts"/ "Package cache lookups"))*100
CATALOGCACHE_SZ (DB)
这个参数用于缓存系统编目信息，例如 SYSTABLE、授权和 SYSROUTINES 信息。缓存编目信息十分重要，尤其是在使用 DPF 的情况下更是如此，因为不必为获得先前已经检索过的信息而访问系统编目（编目分区），从而减少了内部开销。
不断增加该值，直到对于 OLTP 的 Catalog Cache Hit Ratio (CCHR) 达到 95% 或更好的值：
CCHR = (1-( "Catalog cache inserts"/ "Catalog cache lookups"))*100
如果 "Catalog cache overflows"的值大于 0，也要增加该参数的值。还可以使用 "Catalog cache high water mark (Bytes)"来确定编目缓存曾消耗过的最多内存。如果 High water mark 等于允许的 Maximum 大小，那么就需要增加编目缓存堆的大小。
下面的参数可能带来额外的性能。然而，快照中的特定监视器并不是直接报告出它们的影响。相反，可能需要一次更改一个参数，然后测量应用程序的总体性能。最好的测量方法是从几个快照中检查更改前后 SQL 的执行次数。
INTRA_PARALLEL (DBM)
该参数指定数据库管理器是否可以使用内部分区并行性（intra-partition parallelism）。缺省值 NO 对于并发连接较多的情况（主要是 OLTP）最好，而 YES 对于并发连接较少的情况以及复杂 SQL （OLAP/DSS）来说最好。混合的工作负载通常可以得益于 NO。
当启用该参数时，就会导致从共享内存中分配排序内存。此外，如果并发程度显著增加的话，还可能导致过多的系统开销。如果系统是非 OLTP 的，则 CPU 数对分区数的比例是 4:1，而 CPU 负载运行的平均百分比是 50%，INTRA_PARALLEL 很可能会提高性能。
DFT_QUERYOPT (DB)
用于指定在编译 SQL 查询时所使用的缺省优化级别。对于混合的 OLTP/OLAP，使用 5 或 3 作为缺省值，对于 OLTP，使用一个更低的级别，而对于 OLAP，则使用一个更高的级别。对于简单的 SELECTS 或短的运行时查询（通常只需花不到 1 秒钟就可以完成），使用 1 或 0 也许比较合适。如果有很多的表，有很多相同列上的连接谓词，那么尝试级别 1 或 2。对于超过 30 秒钟才能完成的长时间运行的查询，或者如果要插入一个 UNION ALL VIEW（这是在 FixPak4 中加进来的），那么可以尝试使用级别 7。在大多数环境下都应该避免使用级别 9。
UTIL_HEAP_SZ (DB)
该参数指定 BACKUP、RESTORE 和 LOAD 实用程序可以同时使用的最大内存数。如果正在使用 LOAD，那么对于每个 CPU 将 UTIL_HEAP_SZ 设置成至少 10000 页。
NEWLOGPATH (DB)
该参数指定最长 242 个字节的一个字符串，用于更改日志文件写和存储的位置。这可以指向一个全限定路径名，或者指向元设备。将日志路径更改到一个独立的本地高速磁盘（只用于日志记录）可以显著地提高性能。
  |
|
如果有一个针对特定问题的查询或者一组查询，那么可以将该工作负载输入到 DB2 Design Advisor (db2advis) 中，由它去推荐一组有效的索引。如果不知道 SQL，也可以
- 使用快照捕获动态 SQL。
- 用一个语句事件监视器收集在一段时间内发出的所有 SQL。
- 从 SYSCAT.STATEMENTS 编目视图中提取静态 SQL。
语句事件监视器的使用将在本节稍后一点讨论。
可以从 DB2 Control Center 使用 Design Advisor，或者从 CLP 命令行使用该工具。下面讨论这两种界面。
使用 DB2 Control Center
在 Control Center 中，展开对象树，直到发现感兴趣的数据库。右键单击数据库名，并从弹出菜单中选择 Design Advisor'。现在您就可以通过查看最近执行的 SQL，检查包，或者手动地添加 SQL 语句来构造工作负载了。
使用 DB2 CLP
当使用 CLP 时，输出被显示到屏幕，这可以捕捉到一个脚本中并执行。下面是一些常见的例子。
要为一个特定的针对 'example' 数据库的 SQL 语句推荐索引，并且要在 1 分钟内标识出索引：
db2advis -d sample -s "select count(*) from sales where region = 'Quebec'" -t 1
要为多条语句推荐索引，我们可以构建一个文本文件，该文件看上去是这样的：
db2advis -d sample -s "--#SET FREQUENCY 10 SELECT * FROM SALES; --#SET FREQUENCY 2 SELECT FIRSTNME FROM EMPLOYEE WHERE EMPNO = ?; |
其中 frequency 是该 SQL 语句与输入文件中其他 SQL 语句相比其执行次数所占的比重。生成结果：
db2advis -d dbname -i sqlstmts_file > recindexes.out
由于输出显示到了屏幕上，我们使用一个重定向将索引定义捕捉到一个文件中，然后该文件就可以作为一个 DB2 脚本来运行了。
您还可以通过管道将动态 SQL 从一个快照发送到 Design Advisor 中：
get snapshot for dynamic SQL on dbname write to file
这样将以一种内部文件格式保存快照。然后就可以用下列语句将结果插入到一个 Design Advisor 表中：
insert into advise_workload(select 'myworkload',0,stmt_text,cast(generate_unique() as char(254)), num_executions, 1,1,0,0,cast(null as char) from table (snapshot_dyn_sql(' dbname', -1)) as snapshot_dyn_sql)
在一个工作负载中，每条 SQL 语句的缺省 frequency 是 1，缺省的 importance 也是 1。generate_unique() 函数将一个惟一的标识符指定给语句。用于可以将这两列更新为更有意义的值。要生成索引：
db2advis -d dbname -w myworkload
一旦执行了 Design Advisor，它就会填充 advise_index 表。您可以通过下面的查询来查询这个表，以列出 Design Advisor 的所有建议：
SELECT CAST(CREATION_TEXT as CHAR(200)) FROM ADVISE_INDEX
CREATE EVENT MONITOR 语句定义一个监视器，在使用数据库的时候，该监视器将记录所发生的某些事件。每个事件监视器的定义还会指定数据库应该将事件记录在哪里。我们可以创建事件监视器来记录跟下列类型的事件有关的信息：DATABASE、TABLES、DEADLOCKS [WITH DETAILS]、TABLESPACES、BUFFERPOOLS、CONNECTIONS STATEMENTS 和 TRANSACTIONS。
清单 10和 清单 11展示了可用于收集事件监视器输出的脚本：
#!/usr/bin/ksh
# create an event monitor and capture its output
# parameters: (1) database name
# (2) monitor output file
# (3) interval between iterations (seconds)
# Note: You may receive an error about the monitor heap being too small. You may want to set
# mon_heap_sz to 2048 while monitoring.
if [ $# -ne 3 ]
then echo "Requires 3 Parameters: dbname monitor_output_file interval_in_#seconds"; exit
fi
MON=evmon
# "nonblocked" may cause loss of data but has less impact on system than default "blocked".
MONTYPE=nonblocked
SLEEP=$3
DB=$1
#EVENTS="deadlocks with details"
#EVENTS="tables, statements, deadlocks with details, connections"
EVENTS="statements"
OUTFILE=$2
OUTDIR="TMPEVMON"
mkdir $OUTDIR
chmod 777 $OUTDIR
cd $OUTDIR
db2 connect to $DB
db2 -v drop event monitor $MON
db2 -v create event monitor $MON for $EVENTS \\
write to file "'`pwd`'" buffersize 64 $MONTYPE
db2 -v set event monitor $MON state = 1
echo ""
echo "Event Monitor active at `date`; sleeping for $SLEEP seconds before turning it off."
sleep $SLEEP
db2 -v set event monitor $MON state = 0
cd ..
db2evmon -db $DB -evm $MON > $OUTFILE
db2 -v drop event monitor $MON
db2 terminate
rm -fr $OUTDIR
echo
echo db2evmon output is in $OUTFILE
|
@echo off REM create an event monitor and capture its output REM parameters: (1) database name REM (2) monitor output file REM (3) interval to monitor for (seconds) REM Note: You may receive an error about the monitor heap being too small. You may want to set REM mon_heap_sz to 2048 while monitoring. :CHECKINPUT IF ""=="%3" GOTO INPUTERROR GOTO STARTPRG :INPUTERROR echo %0 requires 3 parameters: dbname filename sleep_interval echo e.g. "%0 sample evmon0302.out 60" GOTO END :STARTPRG SET dbname=%1 SET outfile=%2 SET sleep_interval=%3 SET MON=evmon REM "nonblocked" may cause loss of data but has less impact on system than default "blocked". SET MONTYPE=nonblocked REM SET EVENTS="deadlocks with details" REM SET EVENTS="tables, statements, deadlocks with details, connections" SET EVENTS="statements" SET OUTDIR="c:\\temp\\evmon" mkdir %OUTDIR% db2 connect to %dbname% db2 -v drop event monitor %MON% db2 -v create event monitor %MON% for %EVENTS% write to file '%OUTDIR%' buffersize 64 %MONTYPE% db2 -v set event monitor %MON% state = 1 echo Sleeping for %sleep_interval% seconds before turning off. sleep %sleep_interval% db2 -v set event monitor %MON% state = 0 db2evmon -db %dbname% -evm %MON% > %OUTFILE% db2 -v drop event monitor %MON% db2 terminate rmdir /s /q %OUTDIR% echo db2evmon output is in %OUTFILE% :END |
输出将包含所有动态 SQL 语句的文本。不过，对于静态 SQL 语句，输出将列出包名和节号。具有包名和节号的 db2expln 可用来提取语句文本，或者也可以查询 syscat.statements 视图来提取文本。
清单 12展示了在语句事件监视器的输出中捕获到的一个事件：
42) Statement Event ...
Appl Handle: 16
Appl Id: *LOCAL.DB2.010746204025
Appl Seq number: 0003
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 201
Creator : NULLID
Package : SQLC2E03
Consistency Token : AAAAAJHR
Package Version ID :
Cursor : SQLCUR201
Cursor was blocking: TRUE
Text : select * from staff
-------------------------------------------
Start Time: 10-06-2003 17:27:38.800490
|
由于可能存在数千个语句事件，找出问题的最容易的方法是使用 grep (UNIX) 或 findstr (Windows)。下面是在输出中用于搜索的一些有用的字符串：
" sqlcode: -"
这对于发现错误很有用，例如发现 -911 RC 2，即死锁，又例如 RC 68，即锁超时。
grep -n " sqlcode: -" stmtevmon_output findstr /C:" sqlcode: -" stmtevmon_output
" Rows read: "
这标识了一条语句读取的行数（没有将索引项或直接表读计算在内）。如果这个数字很大，则意味着需要一个索引，或者统计信息已过时。
grep -n " Rows read: " stmtevmon_output | grep -v ": 0" | sort -k 4,4rn | more findstr /C:" Rows read: " stmtevmon_output | findstr /V /C:" Rows read: 0" | sort
" Exec Time: "
这是语句的实际执行时间，包括锁等待的时间。有时候，可以方便地从事件监视器数据的最后开始，向前搜索 "Exec Time"，并查看一条开销较大的 SQL 语句是否存在某种模式或存在重复。然后可以用 EXPLAIN 检查 SQL 语句，看看是什么问题。
grep -n " Exec Time: " stmtevmon_output | grep -v ": 0.0" | sort -k 4,4rn | more findstr /C:" Exec Time: " stmtevmon_output | findstr /V /C:" Exec Time: 0.0" |sort
" Sort overflows:"
显示开销很大的 排序溢出发生在哪里。这可能表明需要使用索引、运行 RUNSTATS 或加大 SORTHEAP。
grep -n " Sort overflows:" stmtevmon_output | grep -v ": 0" | sort -k 4,4rn | more findstr /C:" Sort overflows: " stmtevmon_output |findstr /V /C:" Sortoverflows: 0"|sort
" Fetch Count:"
对于查看对结果集执行了多少 fetch 操作很有用。这里并不是记录每个 FETCH 操作。DB2 通过加大该字段，在语句级上跟踪这些 FETCH 操作。可以用 FETCH FIRST 子句限制 FETCH 操作。
grep -n " Fetch Count:" stmtevmon_output | grep -v ": 0" | sort -k 4,4rn | more findstr /C:" Fetch Count: " stmtevmon_output | findstr /V /C:" Fetch Count: 0" | sort
标识了想要进一步观察的一些元素之后，打开语句事件监视器的输出，并按您感兴趣的字符串进行搜索。一旦定位到语句，下面的一些字段将很有用：
" Operation: "
该字段可以提供一般语句流。它标识 Prepare、Open、Fetch、Close、Commit 等等。
" Text : "
这是用于动态 SQL 的语句文本。对于静态 SQL，查找 "Section :"和 "Package :"。
" Start Time: " 或 " Stop Time: " 或 " Time: "
这可以帮助标识起始时间、终止时间或者同时标识这两个时间。Stop time 和随后的 Start time 还可以说明语句之间的间歇时间有多长，如果您怀疑 DB2 在其他某个地方花费了时间（可能是存储过程开销），那么这一点就比较有用。
