DB2基础知识三

 

<!--[if !supportLists]-->13.    <!--[endif]-->表关联（关联、外连接）

Union

――交集运算：INTERSECT、INTERSECT ALL

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

INTERSECT -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

---------------------------------

2 'Tuesday' 'Tues'

3 'Wednesday' 'Wed'

说明：INTERSECT和INTERSECT ALL是等效的。

    ――差集运算：EXCEPT、EXCEPT ALL

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

EXCEPT -- 集合B

SELECT * FROM AA_WEEK w WHERE w.CODE IN (2,3,4);

说明：EXCEPT和EXCEPT ALL是等效的。

---------------------------------

1 'Monday' 'Mon'

       ――合集运算：UNION、UNION ALL

<!--[if !supportLists]-->n       <!--[endif]-->求合集：A+B（不消除重复行）

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

UNION ALL -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

<!--[if !supportLists]-->n       <!--[endif]-->求合集：A+B（消除重复行）

SELECT * FROM AA_WEEK X WHERE X.CODE IN (1,2,3) -- 集合A

UNION -- 集合B

SELECT * FROM AA_WEEK X WHERE X.CODE IN (2,3,4);

---------------------------------

       Join

<!--[if !supportLists]-->n       <!--[endif]-->内连接：inner join在两表共有的行才会输出，内连接没有左右之分。

              select * from t1 inner join t2 on t1.c3=t2.c1

<!--[if !supportLists]-->n      <!--[endif]-->左外连接：left outer join左连接保留前面表的所有记录,后表中没有的补null。

select * from t1 left outer join t2 on t1.c3=t2.c1

<!--[if !supportLists]-->n      <!--[endif]-->右外连接：right outer join右连接保留后表的所有记录,前表中没有的补null。在DB2的内部机制中,会把右外连接重写成左外连接.故我们在写sql语句时尽量使用左外连接。

select * from t1 right outer join t2 on t1.c3=t2.c1

<!--[if !supportLists]-->n      <!--[endif]-->全外连接：full outer join全外连接会输出两表的所有的数据,包括内连接和左外连接和右外连接的行。

select * from t1 full outer join t2 on t1.c3=t2.c1

<!--[if !supportLists]-->n      <!--[endif]-->总结：内连接,全有才有;左外连接,左有就有;右外连接,右有就有;全外连接,全都有。

<!--[if !supportLists]-->14.    <!--[endif]-->检查LOCKTIMEOUT 的值

The default value for LOCKTIMEOUT is -1, which means that there will be no lock timeouts - a situation that can be catastrophic for OLTP applications. Nevertheless, I all too frequently find many DB2 users with LOCKTIMEOUT = -1. Set LOCKTIMEOUT to a very short value, such as 10 or 15 seconds. Waiting on locks for extended periods of time can have an avalanche effect on locks.

First, check the value of LOCKTIMEOUT with this command:

db2 "get db cfg for DBNAME"

and look for the line containing this text:

Lock timeout (sec) (LOCKTIMEOUT) = -1

If the value is -1, consider changing it to 15 seconds by using the following command (be sure to consult with the application developers or your vendor first to make sure the application is prepared to handle lock timeouts):

db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"

You should also monitor the number of lock waits, lock wait time, and amount of lock list memory in use. Issue the command:

db2 "get snapshot for database on DBNAME"

Look for the following lines:

Locks held currently= 0  

Lock waits= 0  

Time database waited on locks (ms)= 0  

Lock list memory in use (Bytes)= 576  

Deadlocks detected= 0  

Lock escalations= 0  

Exclusive lock escalations= 0  

Agents currently waiting on locks= 0  

Lock Timeouts= 0

If the Lock list memory in use (Bytes) exceeds 50 percent of the defined LOCKLIST size, then increase the number of 4K pages in the LOCKLIST database configuration.