1.1 ֤װдǳԱĺùߣ䡣
DB2 SELECT COUNT(*) FROM SYSCAT.TABLES

1.2 |moreĻͨںţ>ļݡ
DB2 select * from employee>c:\\tmp\\emp.out |more

1.3
SELECT * FROM EMPLOYEE WHERE SALARY >9999

ὫϢ͵һΪ9999ļҪЩŵĽޣԱӦ䡣
SELECT * FROM EMPLOYEE WHERE SALARY ">" 9999

1.4 ԱյһϢֲ֪Ӧȡʵ裬ôд䡣
C:\\SQLLIB>db2 ? sql0100 |more

1.5 䡣
DB2 ? backup

1.6 -tѡűͿɰDB2SQL䣬ͨ½ű
DB2 -tvf filename.ddl Ct

1.7 ͨDB2 ? optionsԵõѡİ
C:\\SQLLIB>db2 ? options

1.8 Ա飬ôһݿ´ͼԼSQLԼıҲȡ
select text from syscat.views

select text,tabname from syscat.checks

select text from syscat.procedures

select text from syscat.triggers

1.9 һDDLűļӡһЩкʹд˽ű
CONNECT to the database

䣺
db2 -td@ -f DB2_triggers.ddl

<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<META content="MSHTML 6.00.2900.2769" name=GENERATOR></HEAD>
<BODY><PRE>
-- ɾ˽űĶ
-- drop trigger verify_credit@
-- drop trigger verify_state@
-- drop trigger restrict_delete@
-- drop trigger log_delete@
-- drop table customer_t@
-- drop table product_t@
-- drop table orders_t@
-- drop table delete_log_t@
-- drop sequence cust_seq restrict@
-- drop sequence prod_seq restrict@
-- drop sequence ord_seq restrict@

create table customer_t (
cust_id INT NOT NULL PRIMARY KEY,
company_name VARCHAR(100),
credit DECIMAL(10,2))@

create table product_t (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100))@

create table orders_t (
order_id INT NOT NULL PRIMARY KEY, 
cust_id INT NOT NULL, 
product_id INT NOT NULL, 
quantity INT NOT NULL,  
price DECIMAL(10,2) NOT NULL,
status CHAR(9) NOT NULL,
FOREIGN KEY (cust_id) REFERENCES customer_t,
FOREIGN KEY (product_id) REFERENCES product_t
)@

create table delete_log_t (Text varchar(1000))@
create sequence cust_seq@
create sequence prod_seq@
create sequence ord_seq@
insert into customer_t values 
(NEXTVAL FOR cust_seq, 'Nancys Widgets', 100)@
insert into product_t values
(NEXTVAL FOR prod_seq, 'Blue Widgets')@
CREATE TRIGGER verify_credit 
NO CASCADE BEFORE INSERT ON orders_t
 REFERENCING NEW AS n 
 FOR EACH ROW MODE DB2SQL
 BEGIN ATOMIC
   DECLARE current_due DECIMAL(10,2) DEFAULT 0;
   DECLARE credit_line DECIMAL(10,2);
  /* 
   * ȡͻ
   */
  SET credit_line = (SELECT credit 
			FROM customer_t c
			WHERE c.cust_id=n.cust_id);
  -- ܼƵǰܽ
  FOR ord_cursor AS 
  	SELECT quantity, price 
	FROM orders_t ord
	WHERE ord.cust_id=n.cust_id AND
      		status not IN ('COMPLETED','CANCELLED') DO
  	SET current_due = current_due + 
                          (ord_cursor.price * ord_cursor.quantity);
  END FOR;
  IF (current_due + n.price * n.quantity) &gt; credit_line THEN
     SIGNAL SQLSTATE '80000' ('Order Exceeds credit line');
  END IF;
END@
CREATE TRIGGER verify_state
NO CASCADE BEFORE UPDATE ON orders_t
REFERENCING OLD AS o NEW AS n 
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF o.status='PENDING' and n.status IN ('SHIPPED','CANCELLED') THEN
-- Ч״̬
ELSEIF o.status='SHIPPED' and
n.status = 'DELIVERED' THEN
-- Ч״̬
ELSEIF o.status='DELIVERED' and 
n.status = 'COMPLETED' THEN
-- Ч״̬
    ELSE
         SIGNAL SQLSTATE '80001' ('Invalid State Transition');
    END IF;
END@
CREATE TRIGGER restrict_delete
NO CASCADE BEFORE DELETE ON orders_t
REFERENCING OLD AS o 
FOR EACH ROW MODE DB2SQL
WHEN (o.status &lt;&gt; 'CANCELLED')
SIGNAL SQLSTATE '80003' ('Cannot Delete an order that has not been cancelled')@

CREATE TRIGGER log_delete
AFTER DELETE ON orders_t
REFERENCING OLD AS o
FOR EACH ROW MODE DB2SQL
INSERT INTO delete_log_t VALUES (
'Order #' || CHAR (o.order_id) ||
'Was deleted on ' || CHAR(CURRENT TIMESTAMP))@</PRE></BODY></HTML>

1.10 ûжκζи£﷨ǶԵģǣûκμ¼зϸڲԷǳǿwhereӾɵġ£ԱõһֵΪ100SQLCODE
C:\\sqllib>db2 -a update employee set salary=5 where salary "<" -5

SQLCAϢ£
sqlcaid : SQLCA   sqlcabc: 136   sqlcode: 100   sqlerrml: 0
sqlerrmc:
sqlerrp : SQLRIEXU
sqlerrd : (1) -31743   (2) 1    (3) 0
(4) 0        (5) 0    (6) 0
sqlwarn: (1)    (2)    (3)    (4)    (5)    (6)
(7)    (8)    (9)    (10)   (11)
sqlstate: 02000

1.11 кднõȻͬĽ
create user temporary tablespace usetemp
pagesize 4k managed by system using ('C:\\usetemp');

declare global temporary table t1 (col1 int) not logged;
select count(*) from session.t1;

1.20 ͨѯڵάϵķΧԤӣͨc3ϵάȿ
SELECT .... FROM t1WHERE c3 < 5000 

1.21 ͨѯڵһάϽINԤӣڿɨ衣
SELECT .... FROM t1WHERE c2 IN (1,2037)

1.22 ͨANDc2c3ϵķΧԤc1ϵĵȼԤӡ
SELECT .... FROM t1
WHERE c2 > 100 AND c1 = 16/03/1999 AND c3 > 1000 and c3 < 5000

1.23 ͨѯc1άϵķΧԤc2άϵINԤԼORӡ
SELECT .... FROM t1WHERE c1< 5000 OR c2 IN (1,2,3)

1.24 ͨѯc1άȵԤͶжάȵһΧԤ⼰루ANDӡ
SELECT .... FROM t1WHERE c1= 15 AND c4 < 12

1.25 ͨѯc1άϵķΧԤͷάc4ϵĵȼԤԼORӡ
SELECT .... FROM t1WHERE c1< 5 OR c4 = 100

1.26 ͽڵѯӡ
SELECT .... FROM t1,d1,d2,d3
WHERE t1.c1 = d1.c1 and d1.region = NY
AND t2.c2 = d2.c3 and d2.year=1994
AND t3.c3 = d3.c3 and d3.product=basketball

1.27
CREATE TABLE T1(c1DATE, c2 INT, c3 INT, c4 DOUBLE)
ORGANIZE BY DIMENSIONS (c1, c3, c4)

1.28 άc1 (c3, c4)Ͼۼıӡ
CREATE TABLE T1(c1DATE, c2 INT, c3 INT, c4 DOUBLE)
ORGANIZE BY DIMENSIONS (c1, (c3, c4))

1.29 c2c5c6ϵֵۼӡ
CREATE TABLE T1(c1 DATE, c2 INT, c3 INT, c4 DOUBLE,
c5 DOUBLE GENERATED ALWAYS AS (c3 + c4),
c6 GENERATED ALWAYS AS (MONTH(C1))
ORGANIZE BY DIMENSIONS (c2, c5, c6)

1.32 ڴиòͬSQLһӡ
ȣt1t2
create table t1 (id int not null, name char(10), primary key(id))

create table t2 (id int not null, name char(10), primary key(id))

Ȼִtrig.db2ű
db2 -td@ -f trig.db2
trig.db2£
create trigger trig after insert on t1 referencing new as nd for each row
mode db2sql 
begin atomic
IF EXISTS (select * from t2 where t2.id=nd.id) then 
update t2 set t2.name=nd.name where t2.id=nd.id;
ELSE
insert into t2 values(nd.id,nd.name);
END IF;
end @

1.33 бʶ (identity column) ıвʱʶлԶһֵʱôֵԶ뵽Сһ򵥵ӡ
1.	һбʶеıtestiden
create table testiden (id int not null generated always as identity, name char(20))

2.	һtestidenɵıʶֵıtestdata
create table testdata (id int)

3.	testtrigʵֹܡ
create trigger testtrig after insert on testiden referencing
new as n for each row mode db2sql insert into testdata values(n.id)

4.	ԲԽ
insert into testiden (name) values ('test')

5.	ύ󣬱testidenбʶidֵ뵽testdataС
