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.
  

　　z