发点以前学db2时写的笔记，忘了的话就来lu看一下

--#
--# DATA TYPE
--#


TYPE                BYTE        PRECISION
SMALLINT:        2        5
INTEGER:        4        10
BIGINT:                8        19
NUMERIC/DECIMAL:        (p,s)
REAL:                32
DOUBLE/FLOAT:        64       

DATE:                10        yyyy-mm-dd
TIME:                8        hh.mm.ss
TIMESTAMP:        26        yyyy-mm-dd-hh.mm.ss.nnnnnn

CLOB(n):        2G
DBCLOB(n):        2G
BLOB(n):        2G

TYPE                LENGTH
CHAR(n):        254
VARCHAR(n):        32672
GRAPHIC(n):        127
VARGRAPHIC(n):        16336


--#
--# CREATE TABLE
--#


CREATE TABLE test ( col1 CHAR(10), col2 CHAR(10), col3 INTEGER, col4 INTEGER,
        col5 GENERATED ALWAYS AS ( 'AAA' || RTRIM(LTRIM(col1)) || RTRIM(LTRIM(col2)) )
        col6 GENERATED ALWAYS AS ( col3 + col4 ) )

CREATE TABLE tbname1 LIKE tbnam2

CREATE ALIAS name1 FOR name2
DROP ALIAS name1


--#
--# CONSTRAINTS
--#


NOT NULL
WITH DEFAULT '...'


--# Unique Constraint

----# DB2 will automatically add a unique index on the columns under a unique constraint with the same as it


ALTER TABLE tbname ADD CONSTRAINT rule1 UNIQUE ( team, position )


--# Check Condition Constraints


CREATE TABLE tbname ( ..., jobcode INTEGER, ... CHECK (jobcode IN ( 10, 20, 30 ) )

ALTER TABLE tbname ADD CONSTRAINT check2 CHECK ( salary < 1000 AND bonus <= salary )


--# Primary Key


CREATE TABLE tbname ( col1 CHAR(4) NOT NULL, col2 ..., PRIMARY KEY ( col1, col2 )


--#Foreign Key

----# Parent key must be a primary key or have a unique index; It's better to add a index on a foreign key

CREATE TABLE department ( deptno CHAR(3) NOT NULL PRIMARY KEY, deptname VARCHAR(32), location VARCHAR(32) )

CREATE TABLE employee ( empno CHAR(6) NOT NULL PRIMARY KEY, name VARCHAR(32) NOT NULL, deptno CHAR(6), manager CHAR(6),
        CONSTRAINT fk1 FOREIGN KEY ( deptno ) REFERENCES department( deptno ) ON DELETE CASCADE,
        CONSTRAINT fk2 FOREIGN KEY ( manager ) REFERENCES employee( empno ) ON DELETE CASCADE )


----# Delete Rule: ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE NO ACTION | RESTRICT


--#
--# VIEW
--#


CREATE VIEW low_pay AS SELECT name, salary FROM staff WHERE salary < 2000

CREATE VIEW low_pay_dept ( dept, min_salary ) AS
        SELECT dept, MIN(salary) FROM staff GROUP BY dept

CREATE VIEW staff_org AS
        SELECT name, location, salary FROM staff s INNER JOIN org o ON s.dept = o.deptnumb WHERE job <> 'Mgr'

--#Read-only View & Symmetric View


--#
--# TRIGGER
--#


CREATE TRIGGER tr1 BEFORE INSERT ON staff
CREATE TRIGGER tr2 AFTER DELETE ON staff
CREATE TRIGGER tr3 AFTER UPDATE OF jobcode, salary ON staff


--# The Granularity of Trigger


FOR EACH STATEMENT | ROW


--# Transition Variable


REFERENCING OLD AS new_row OLD AS old_row
REFERENCING OLD_TABLE AS old_table NEW_TABLE AS new_table


--# Trigger Conditions


WHEN ( new_row.salary < old_row.salary )
WHEN ( new_row.salary < ( SELECT MAX(salary) FROM emp WHERE jobcode = new_row.jobcode ) )
WHEN ( SELECT COUNT(*) FROM old_table ) > 100 )


--# Create a Trigger


CREATE TRIGGER reorder AFTER UPDATE OF qty ON stock REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
        WHEN ( new_row.qty <= 5 )
        INSERT INTO order VALUES ( new_row.itemno, CURRENT TIMESTAMP )

DROP TRIGGER reorder


----# A before trigger must be a row trigger, and must be tagged as "NO CASCADE"


CREATE TRIGGER tr1 NO CASCADE BEFORE INSERT ON emp REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
        SET ( salary, bonus ) = ( SELECT salary, bonus FROM pay WHERE jobcode = new_row.jobcode )

CREATE TRIGGER tr2 NO CASCADE BEFORE INSERT ON bb REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
        SET ( new_row.col2 ) = ( SELECT MAX(seq) FROM seq_table )

CREATE TRIGGER tr3 NO CASCADE BEFORE UPDATE OF salary ON emp REFERENCING OLD AS old_row NEW AS new_row
        FOR EACH ROW MODE DB2SQL WHEN ( new_row.salary > 1.5 * old_row.salary )
        SET new_row.salary = 1.5 * old_row.salary

CREATE TRIGGER tr4 NO CASCADE BEFORE DELETE ON emp REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL
        WHEN ( importence(old_row.jobcode, old_row.project) > 20 )
        SIGNAL SQLSTATE '70010' ( 'Do Not Fire This Person' )

CREATE TRIGGER tr5 NO CASCADE BEFORE INSERT ON shipments REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
        SET pkgid = generate_unique()

----#  generate_unique() generates a CHAR(13) FOR BIT DATA


---# After Trigger


CREATE TRIGGER tr1 AFTER UPDATE OF salary ON emp REFERENCING OLD AS old_row NEW AS new_row
        FOR EACH ROW MODE DB2SQL WHEN ( new_row.salary < old_row.salary )
        BEGIN ATOMIC
        VALUES ( logEvent( 'Salary decrease', CURRENT TIMESTAMP, old_row.empno );
        SIGNAL SQLSTATE '70011' ( 'Emplyee's Salary Is Decreased: ' || old_row.empno );
        END

----# If a atomic fails, it will rollback and rerurn SQLCODE -723


CREATE TRIGGER tr2 AFTER UPDATE ON temps REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
        WHEN ( new_row.temp > ( SELECT hightemp FROM extremes WHERE place = new_row.place )
        OR ( SELECT hightemp FROM extremes WHERE place = new_row.place ) IS NULL )
        UPDATE extremes SET hightemo = new_row.hightemp, highdate = CURRENT DATE WHERE place = new_row.place

CREATE TRIGGER tr3 AFTER INSERT ON temps REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
        INSERT INTO extremes ( place, hightemp, highdate, lowtemp, lowdate )
        VALUES ( new_row.place, new_row.temp, CURRENT DATE, new_row.temp, CURRENT DATE )

CREATE TRIGGER tr4 AFTER INSERT ON accounts REFERENCING NEW_TABLE AS new_table FOR EACH STATEMENT MODE DB2SQL
        INSERT INTO account_changes ( type, when, bywhom, nrows )
        VALUES ( 'I', CURRENT TIMESTAMP, USER, ( SELECT COUNT(*) FROM new_table ) )


--#
--# SEQUENCE
--#


CREATE SEQUENCE seq1 START WITH 1000 INCREMNET BY 1 NO MAXVALUE NO CYCLE NO CACHE

NEXTVAL / PREVVAL FOR seq1

CREATE TABLE test ( col1 CHAR(10),
        col2 INTEGER GENERATED ALWAYS AS INDENTITY START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE NO CACHE )

INSERT INTO test VALUES ( 'a', DEFAULT )


--#
--# ENCRYPTION
--#


CREATE TABLE enc ( col1 CHAR(10), col2 VARCHAR(30) FOR BIT DATA )
INSERT INTO enc VALUES ('a3', ENCRYPT( 'a3value', 'Taipei', 'City') )

SELECT DECRYPT_CHAR(col2, 'Taipei') FROM enc WHERE col1 = 'a3'
SELECT GETHINT(col2) FORM enc WHERE col1 = 'a3'

SET ENCRYPTION PASSWORD = 'Taipei'
INSERT INTO enc VALUES ( 'a4', ENCRYPT('a4value') )
SELECT DECRYPT_CHAR(col2) FROM enc WHERE col1 = 'a4'

catalog tcpip node node1 remote hostname server 50000
catalog database test as test1 at node node1
catalog database test on /opt
uncatalog node node1
uncatalog database dbname


db2set -g                                #global level
db2set -lr                                #list all
db2set -g DB2COMM=TCPIP                        #set global level
db2set -i db2inst1 DB2COMM=TCPIP        #set instance level
db2set DB2COMM=TCPIP                        #set current instance


list db directory on /opt
list active databases


activate db dbname
deactivate db dbname


attach to nodename | instname
detach


get instance
get connection state


list tables for all | for schema test
describe select id, name, salary + comm from test.staff
describe table tbname
describe indexes for table tbname show detail


list applications show detail
force application(x) | all


list tablespaces show detail
list tablespace containers for tsid show detail


quiesce instance instname [ for user test ] defer | immediate
quiesce database dbname defer | immediate
quiesce tablespace for table tbname share | intent to update | exclusive
unquiesce instance instname
unquiesce database dbname


get monitor switches
update monitor switches using lock on bufferpool on
reset monitor all


reorgchk update statistics on table tbname | all

reorg table tbname using tempspace1
reorg table tbname allow read access
reorg indexes all for table tbname allow read access

runstats on table tbname with distribution and indexes all


start | stop database manager

db2admin create | drop | start | stop


db2adutl
db2advis
db2batch -d dbname -f test.sql -o p 2
db2cc
db2cfexp
db2cfimp
db2ckbkp
db2dclgn
db2empfa dbname
db2expln
db2icrt
db2idrop
db2ilist
db2iupdt
db2imigr
db2look
db2rbind
db2move
db2sampl
db2trc
db2untag

CREATE DATABASE testdb ON '/opt/db2data'
USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM
CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE '/opt/db2data/syscat.dbf' 51200 )
        EXTENTSIZE 32 PREFETCHSIZE 32
USER TABLESPACE  MANAGED BY DATABASE USING ( FILE '/opt/db2data/users.dbf' 51200 )
        EXTENTSIZE 32 PREFETCHSIZE 32
TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE '/opt/db2data/temp.dbf' 51200 )
        EXTENTSIZE 32 PREFETCHSIZE 32
AUTOCONFIGURE
        using mem_percent 25
        workload_type simple | mixed | complex
        num_stmts 10 tpm 20
        admin_priority performance | recovery | both
        num_local_apps 2 num_remote_apps 200
        isolation cs bp_resizeable yes apply db and dbm;

CREATE TABLESPACE user1 PAGESIZE 32 K
MANAGED BY SYSTEM USING ( '/db2/dir1' , '/db2/dir2" )
EXTENTSIZE 32

CREATE TABLESPACE user2 PAGESIZE 32 K
MANAGED BY DATABSE USING ( DEVICE '/dev/rdata1' 1024, DEVICE '/dev/rdata2' 1024 )
EXTNETSIZE 32 BUFFERPOOL bp_name

CREATE TABLE abc ( ... ) IN ts1 INDEX IN ts2 LONG IN ts3


LIST TABLESPACE SHOW DETAIL
LIST TABLESPACE CONTAINERS FOR tsid SHOW DETAIL


ALTER TABLESPACE ts1 ADD ( DEVICE '/dev/rdb4' 4000 )
ALTER TABLESPACE TS1 DROP ( DEVICE '/dev/rdb2' )
ALTER TABLESPACE ts1 REDUCE ( FILE '/opt/user1.dbf 200' ) --# cut off 200 pages
ALTER TABLESPACE ts1 RESIZE ( ALL 500 )
--# option "BEGIN NEW STRIPE SET" :

db2empfa dbname

--# set db cfg parameter num_ioservers = disk_num + 2

:mad yun.

同晕

:lu1:

很好很强大