DB2查询优化技巧-优快云博客

1.系统表SYSIBM.SYSCOLUMNS

可获得表的字段类型，长度等信息

SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = '表名' AND TBCREATOR = 'schma名' ORDER BY COLNO FOR FETCH ONLY WITH UR

2. FOR FETCH ONLY WITH UR

UR stays for Uncommited Read. If you use it, you may get some pieces of information that are "out of date". DB2 will not lock the pages you're reading.
With FOR FETCH ONLY, you're saying to DB2, that you do not intend to update the rows you are reading. DB2 will try to avoid locking the pages you are reading, but will only give you "clean" pages, i.e., pages that are allready commited.

3. 索引系统表

4. command line 建立远程数据库连接

使用 CATALOG 命令分别建立实例与数据库节点.再使用 Connect to 命令登录数据库..

过程如下:

db2 => catalog tcpip node mynode remote pvcent20.cn.ibm.com server 5000
db2 => catalog db db_1 as mydb_1 at node mynode
db2 connect to mydb_1 user db2inst1 using db2inst1 (用户名和密码是远程server的用户名和密码)

5. OPTIMIZE FOR n ROWS

Example: Suppose that you query the employee table regularly to determine the employees with the highest salaries. You might use a query like this:

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
  FROM EMP
  ORDER BY SALARY DESC;

An index is defined on column EMPNO, so employee records are ordered by EMPNO. If you have also defined a descending index on column SALARY, that index is likely to be very poorly clustered. To avoid many random, synchronous I/O operations, DB2 would most likely use a table space scan, then sort the rows on SALARY. This technique can cause a delay before the first qualifying rows can be returned to the application.

If you add the OPTIMIZE FOR n ROWS clause to the statement, DB2 will probably use the SALARY index directly because you have indicated that you expect to retrieve the salaries of only the 20 most highly paid employees.

Example: The following statement uses that strategy to avoid a costly sort operation:

SELECT LASTNAME,FIRSTNAME,EMPNO,SALARY
  FROM EMP
  ORDER BY SALARY DESC
  OPTIMIZE FOR 20 ROWS;

Effects of using OPTIMIZE FOR n ROWS:

The join method could change. Nested loop join is the most likely choice, because it has low overhead cost and appears to be more efficient if you want to retrieve only one row.
An index that matches the ORDER BY clause is more likely to be picked. This is because no sort would be needed for the ORDER BY.
List prefetch is less likely to be picked.
Sequential prefetch is less likely to be requested by DB2 because it infers that you only want to see a small number of rows.
In a join query, the table with the columns in the ORDER BY clause is likely to be picked as the outer table if there is an index on that outer table that gives the ordering needed for the ORDER BY clause.