六,創建數據庫
創建數據庫時,完成如下任務:
* 設置數據庫所需的所有系統目錄表
* 分配數據庫恢復日志
* 創建數據庫配置文件,設置缺省值
* 將數據庫實用程序與數據庫綁定
初始數據庫分區組的定義
當最初創建數據庫時,會為所有在db2nodes.cfg文件中指定的分區創建數據庫分區.可以用
add dbpartitionnum 和 drop dbpartitionnum verify 命令來添加或除去其他分區.
定義了三個數據庫分區組:
* 用于容納syscatspace表空間的ibmcatgroup, 保存系統目錄表
* 用于容納tempspaces1表空間的ibmtempgroup,保存系統臨時表
* 用于容納userspace1表空間的ibmdefaultgroup,缺省保存用戶表和索引
(創建新的數據庫后最好重新啟動db2clp)
EXAMPLE:
CREATE DATABASE PERSONL
CATALOG TABLESPACE
MANAGED BY SYSTEM USING (path 'D:\PCATALOG','E:\PCATALOG')
EXTENTSIZE 16 PREFETCHSIZE 32
USER TABLESPACE
MANAGED BY DATABASE USING (FILE 'D:\DB2DATA\PERSION1' 5000,
FILE 'D:\DB2DATA\PERSION2' 5000)
EXTENSIZE 32 PREFETCHSIZE 64
TEMPORARY TABLESPACE
MANAGED BY SYSTEM USING (path 'F:\DB2TEMP\PERSONL')
WITH "PERSONNEL DB FOR DSCHIEFER CO"
create database sccrm using codeset GBK territory CN
#刪除數據庫
DROP DATABASE <DB-NAME>
# 數據庫目錄的定義
*本地數據庫目錄(節點的目錄中的文件SQLDBDIR)
本地數據庫目錄文件存在于定義了數據庫的每條路徑(或WIN中的"驅動器")
對于可以從該位置存取得每個數據庫此目錄都包含一個條目.包含信息:
數據庫名稱,數據庫別名,數據庫注釋,數據庫的根目錄的名稱,其他系統信息.
*系統數據庫目錄(實例中的目錄中的文件SQLDBDIR)
對于數據庫管理器的每個實例,都存在一個系統數據庫目錄文件,該文件對于針對
此實例編目的每個數據庫都包含一個條目.使用CREATE DATABASE時隱式的編目數據庫
每個數據庫包含一條信息: 數據庫名,數據庫別名,數據庫注釋,本地數據庫目錄的位置,
指示該數據庫是間接的指示符,表示它與系統數據庫目錄文件駐留在相同的機器上.
查看本地或系統數據庫目錄文件
LIST DATABASE DIRECTORY ON <location>
LIST DATABASE DIRECTORY
*節點目錄
數據庫管理器在編目第一個數據庫分區時會創建節點目錄.要編目數據庫分區,
使用CATALOG NODE命令.要顯示本地節點目錄的內容,使用LIST NODE DIRECTORY
.在每個數據庫客戶機上都創建并維護節點目錄.對于具有客戶機可以存取得一個
或多個數據庫的每個遠程工作站,該目錄都包含一個條目.db2客戶機使用該節點目錄中
的通信端點信息.
catalog tcpip node my_node_name remote 10.10.10.10 server 54321
uncatalog node my_node_name
catalog database DB as my_data_alias at node my_node_name
# "輕量級目錄訪問協議" (LDAP)目錄服務
目錄服務是一個關于分布式環境中的多個系統和服務的資源信息的資源庫;它
提供對這些資源的客戶機和服務器存取.客戶機和服務器將使用目錄服務來找出
如何存取其他資源.
LDAP是業界標準的存取目錄服務的方法.每個數據庫服務器實例都會將它的存在情況發布給LDAP,
并在創建數據庫時向LDAP目錄提供數據庫信息.當客戶機與數據庫連接后,可從LDAP目錄檢索
服務器的目錄信息.不再要求每個客戶機將目錄信息以本地方式存儲在每臺機器上.
# 創建數據庫分區組
可以使用CREATE DATABASE PARTITION GROUP語句創建數據庫分區組.此語句指定表空間
容器和表數據將駐留其上的一組數據庫分區.
*為數據庫分區組創建分區映象.
*生成分區映象標識
*將記錄插入下列目錄表:
SYSCAT.DBPARTITONGROUPS,SYSCAT.PARTITIONMAPS,SYSCAT.DBPARTITIONGROUPDEF
CREATE DATABASE PARTITION GROUP <name> ON DBPARTITIONNUMS (<value>,<value>)
#創建表空間
表空間建立數據庫系統使用的物理存儲設備與用來存儲數據的邏輯容器或表的關系
CREATE TABLESPACE <name>
MANAGED BY SYSTEM
USING ('<path>')
CREATE TABLESPACE <name>
MANAGED BY DATABASE
USING (FILE'<path>' <size>)
DEVICE
*指定分區組
CREATE TABLESPACE PLANS IN ODDNODEGROUP
MANAGED BY DATABASE
USING (DEVICE '/dev/HDISK0' 10000,DEVICE '/dev/n1hd01' 40000) on node 1
(DEVICE '/dev/HDISK0' 10000,DEVICE '/dev/n1hd03' 40000) on node 3
(DEVICE '/dev/HDISK0' 10000,DEVICE '/dev/n1hd05' 40000) on node 5
CREATE REGULAR TABLESPACE CUSTTBS IN DATABASE PARTITION GROUP CUSTOMER
PAGESIZE 4 K MANAGED BY DATABASE USING
( FILE 'D:\testdbtbs\custtbs0_1' 5120 ) ON DBPARTITIONNUM (0)
USING
( FILE 'D:\testdbtbs\custtbs1_1' 5120 ) ON DBPARTITIONNUM (1)
EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16
在多分區數據庫中創建單分區組.
CREATE DATABASE PARTITION GROUP single_tbs_grp ON DBPARTITIONNUM(0)
在單分區組中創建單分區表空間.
CREATE TABLESPACE single_part_tbs IN DATABASE PARTITION GROUP single_tbs_grp
MANAGED BY DATABASE USING (FILE 'd:\testdbtbs\single_part_tbs_1' 5120)
在UNIX中使用字符設備.
* 創建特定類型的表空間
創建系統臨時表空間
CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp
MANAGED BY SYSTEM
USING ('d:\tmp_tbsp','e:\tmp_tbsp')
在分區數據庫中創建系統臨時表空間 只能在IBMTEMPGROUP中產生
CREATE SYSTEM TEMPORARY TABLESPACE TEMPSYS_TBSP1
IN DATABASE PARTITION GROUP IBMTEMPGROUP
PAGESIZE 4 K MANAGED BY SYSTEM
USING ('D:\testdbtbs\sys_temp_0' ) ON DBPARTITIONNUM (0)
USING ('d:\testdbtbs\sys_temp_1' ) ON DBPARTITIONNUM (1)
EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16
創建用戶臨時表空間
CREATE USER TEMPORARY TABLESPACE usr_tbsp
MANAGED BY DATABASE
USING (FILE 'd:\db2data\user_tZbsp' 5000,
FILE 'e:\db2data\user_tbsp' 5000)
在分區數據庫中創建用戶臨時表空間 可在除IBMTEMPGROUP中的其他分區組中產生.
CREATE USER TEMPORARY TABLESPACE USER_TEMP_TBSP
IN DATABASE PARTITION GROUP USER_TEMP_TBSGRP
PAGESIZE 4 K MANAGED BY SYSTEM
USING ('D:\testdbtbs\user_temp_0' ) ON DBPARTITIONNUM (0)
USING ('d:\testdbtbs\user_temp_1' ) ON DBPARTITIONNUM (1)
*指定物理設備
在WINDOWS上,指定物理硬盤驅動器,使用\\.\PhysicalDriveN (N-0,1,2..)
指定邏輯驅動器使用\\.\N: (N是系統中的邏輯盤符)
在UNIX中指定字符設備.
#SMS 當對象增大時，文件每次擴展一頁。若需要提高插入性能，可以考慮啟用多頁分配，如MDC表。
運行db2empfa. 對于多分區數據庫必須對每個分區運行此使用程序，一旦啟用就不能禁止。
#DMS
單分區表空間大小，4kb - 64gb;8kb-128gb;16kb-256gb;32kb-512gb
在缺省情況下，每個容器都保留一個數據塊作為開銷，表空間的最小大小是5個數據塊。
三個保留給開銷使用。兩個用于用戶表數據。
＃在多個節點上產生表空間
CREATE TABLESPACE TS1 MANAGED BY DATABASE USING
(device '/dev/rcont $N' 20000)
CREATE TABLESPACE TS2 MANAGED BY DATABASE USING
(file '/DB2/containers/TS2/container $N+100' 10000)
CREATE TABLESPACE TS3 MANAGED BY SYSTEM USING
('/TS3/cont $N%2', '/TS3/cont $N%2+2')
#增加表空間的空間
ALTER TABLESPACE RESOURCE
ADD (DEVICE '/dev/rhd9' 10000,
DEVICE '/dev/rhd10' 10000)
#改變表空間狀態
DB2 ALTER TABLESPACE <name> SWITCH ONLINE
#刪除表空間
DROP TABLESPACE <name>
可以先增加一個系統臨時表空間,然后刪除老的.
#獲取表空間使用信息
get snapshot for tablespaces on sample
＃獲取表空間的CONTAINER
LIST TABLESPACE CONTAINERS FOR 0(TABLESPACE_ID) SHOW DETAIL
# 創建和填充表
列出表:
list tables － 列出當前用戶的表
list tables for all － 列出數據庫中所定義的所有表
list tables for schema schemaname － 列出具有指定模式的表
describe table tablename － 顯示指定表的結構
產生表和主鍵
CREATE TABLE DEPARTMENT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(29) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16),
PRIMARY KEY (DEPTNO))
IN RESOURCE
產生外鍵
CREATE TABLE EMPLOYEE
(EMPNO CHAR(3) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3),
PHONENO CHAR(4),
PHOTO BLOB(10m) NOT NULL,
FOREIGN KEY DEPT (WORKDEPT)
REFERENCES DEPARTMENT ON DELETE NO ACTION)
IN RESOURCE
ON DELETE NO ACTION 表示如果該部門有任何雇員則該部門不能被刪除.
定義表檢查約束
在創建或改變表時,通過將檢查約束定義與表關聯來對該表創建表檢查約束.
當INSERT或UPDATE語句修改該表中的數據時,就自動激活此約束.表檢查約束對
DELETE或SELECT沒有影響.檢查約束不能與類型表相關.
約束名不能與在同一個CREATE TABLE語句內指定的任何其他約束相同.若不
指定約束名,系統會自動生成18個字符的唯一標識符.
表檢查約束用于實現鍵唯一性或引用完整性約束所未涵蓋的數據完整性規則.
如:
CREATE TABLE EMP_ACT
(EMPNO CHAR(6) NOT NULL,
PROJNO CHAR(6) NOT NULL,
ACTNO SMALLINT NOT NULL,
EMPTIME DECIMAL(5,2),
EMSTDATE DATE,
EMENDATE DATE,
CONSTRAINT ACTDATES CHECK(EMSTDATE <= EMENDATE) )
IN RESOURCE
約束EMSTDATE必須小于EMENDATE
定義信息性約束
信息性約束是一個規則,可由SQL編譯器使用,但數據庫管理器不會強制使用它.
SQL編譯器包括一個重寫查詢階段,它將SQL語句變換為可能是優化的格式并改進
所需數據的存取路徑.目的是改進查詢性能.
對新表定義生成列
生成列在基本表中定義,在這些列中,存儲的值是使用表達式計算得出的,而不是
通過插入或更新操作指定.可以改善查詢性能,特別是計算很復雜或在查詢時要進行
多次表達式求值.
CREATE TABLE t1 (c1 INT,
c2 DOUBLE,
c3 DOUBLE GENERATED ALWAYS AS (c1 + c2)
c4 GENERATED ALWAYS AS
(CASE WHEN c1 > c2 THEN 1 ELSE NULL END))
創建用戶定義臨時表
臨時表不出現在系統目錄中,不能共享此表
DECLARE GLOBAL TEMPORARY TABLE gb1_temp
LIKE tmpltab1
ON COMMIT DELETE ROES
NOT LOGGED
IN usr_tbsp
定義此用戶臨時表所使用的列的名稱和描述與empltabl的列的名稱和描述完全相同.
隱式定義只包括列名,數據類型,可空性特征和列缺省值屬性.未定義其他列屬性,包括
唯一約束,外鍵約束,觸發器和索引.
對新表定義身份列
為插入表的每一行自動生成保證唯一數字值的方法.
只在單分區數據庫中支持.
CREATE TABLE test_table ( col1 int,
col2 double,
col3 int not null generated always as identity
(start with 100,increment by 5))
創建序列
序列是一個數據庫對象,它允許自動生成值.序列特別適合于生成唯一鍵值.
與身份列屬性不同,未使序列與特定表列相關,也未將它綁定至唯一列,只是僅可通過該表
列存取.只在單分區數據庫中才受支持.
在多分區環境中的單分區數據庫也不行.
CREATE SEQUENCE order_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 24
使用序列
insert into order (orderno,custno)
values (nextval for order_seq,123456);
insert into line_item (orderno,partno,quantity)
values (prevval for order_seq,987654,1)
使用NEXTVAL,PREVVAL可以在兩個不同的表中使用相同的序列號.
對表定義維
引用完整性約束 第 13 頁（共17 頁）
引用完整性約束是在創建表時定義的，或者是在之后使用 alter table 語句定義的。
建立引用完整性的子句有：
primary key 子句
unique constraint 子句
foreign key 子句
references 子句
例如：
create table artists (artno INT, ... primary key (artno) foreign key dept (workdept)
references department on delete no action)
讓我們了解一下各種引用完整性規則：
插入規則：
有一個隱式規則，在沒有找到父項時取消插入。
刪除規則：
Restrict：如果有從屬行就不能刪除父行。
Cascade：刪除父表中的行會自動刪除其從屬表中的任何相關行。
No Action（缺省值）：在應用了所有其它引用約束之后強制每個子行的父行都存在。
Set Null：外鍵字段設置成 null；其它列保持不變。
更新規則：
Restrict：如果從屬表中的行與鍵的初始值相匹配，則不更新父鍵。
No Action（缺省值）：如果從屬表中沒有任何行與父鍵相匹配，則不更新父鍵。
增加表中的列
ALTER TABLE EMPLOYEE
ADD <COLUMN_NAME> <DATA_TYPE> <NULL_ATTRIBUTE>
增加唯一性約束
ALTER TABLE EMPLOYEE
ADD CONSTRAINT NEWID UNIQUE(EMPNO,HIREDATE)
刪除唯一性約束
ALTER TABLE EMPLOYEE
DROP UNIQUE NEWID
刪除主鍵
ALTER TABLE <NAME>
DROP PRIMARY KEY
增加主鍵
ALTER TABLE <NAME> ADD PRIMARY KEY (COL1,COL2,..)
刪除外鍵
ALTER TABLE <name>
DROP FOREIGN KEY <foreign_key_name>
刪除表檢查約束
ALTER TABLE <table_name>
DROP CHECK <check_constraint_name>
添加表的外鍵
ALTER TABLE <NAME>
ADD CONSTRAINT <constraint_NAME>
FOREIGN KEY <COLUMN_NAME>
references table_name <col_name>
ON DELETE <ACTION_TYPE>
ON UPDATE <ACTION_TYPE>
在多個表空間中創建表
表數據,表索引及與表相關聯的任何長型列數據可以存儲在同一表空間中,也可以
放在不同的表空間中.只能使用DMS.
CREATE TABLE <name>
(<column_name> <data_type> <null_attribute>)
IN <table_space_name>
INDEX IN <index_space_name>
LONG IN <long_space_name>
在分區數據庫中創建表
必須小心的選擇適當的分區建,以后不能跟改.再者,必須將任何唯一索引定義為分區鍵的一個超集.
表的大小是(分區數*分區大小(4K是64GB).
CREATE TABLE MIXREC (MIX_CNTL INTEGER NOT NULL,
MIX_DESC CHAR(20) NOT NULL,
MIX_INT INTEGER NOT NULL)
IN MIXTS12
PARTITIONING KEY (MIX_INT) USING HASHING
產生觸發器
用途:
驗證輸入的數據
為新插入的行生成值
為交叉引用而從其他表中進行讀取
為審計跟蹤而向其他表寫入
CREATE TRIGGER <name>
<action> ON <table_name>
<operation>
<triggered_action>
創建用戶定義函數(UDF)或方法
UDF擴展并添加了SQL的內置函數提供的支持,且可在可使用內置函數的任何
地方使用.可使用兩種方式創建UDF:
外部函數,用一種編程語言編寫
有源函數,從另一個現有函數繼承產生.
三種UDF:標量,返回一個單值答案.
列,從一組相似(一列)的值中返回單值答案,如AVG()只能定義有源函數.
表,將一個表返回至引用它的SQL,只能在select語句的from子句中引用表函數.
UDF 記錄在SYSCAT.FUNCTIONS AND SYSCAT.FUNCPARMS目錄視圖中.
用戶定義類UDT
UDT是由用戶在數據庫中創建的命名的數據類型.UDT可以是單值類型,它與內部數據類型或
結構化類型共享一個公共的表示法,結構化類型具有一個命名屬性序列,其中每個屬性都有一個類型.
結構化類型可以是另一個定義類型層次結構的結構化類型的子類型.
創建視圖
CREATE VIEW <name> (<column>,<column>,<column>)
SELECT <column_names> FROM <table_name>
WITH CHECK OPTION
WITH CHECK OPTION 子句指示必須根據該視圖定義檢查該視圖的任何更新的行或插入的行,
如它不符合,則拒絕它,增加了數據完整性.
CREATE VIEW EMP_VIEW
SELECT LASTNAME AS DA00NAME,
EMPNO AS DA00NUM,
PHONENO
FROM EMPLOYEE
WHERE WORKDEPT = 'A00'
WITH CHECK OPTION
創建具體查詢表
具體查詢表是以查詢結果為基礎所定義的一種表.因此,具體查詢表通常包含預先計算的結果,
這些結果是根據表定義中引用的一個或多個表中的現有數據計算而得.若SQL編譯器確定查詢
在針對具體查詢表運行時比對一個或多個基本表運行時效率更高,將對具體查詢表執行該查詢.
創建別名
別名是引用表,別名或視圖的間接方法,這樣SQL語句可與該表或視圖的限定名無關.
CREATE ALIAS WORKERS FOR EMPLOYEE
索引,索引擴展或索引規范
索引是行位置的列表,按一個或多個指定列的內容來排序.
索引擴展是一個索引對象,它配合帶有結構化類型或單值類型列的索引使用.
索引規范是一個元數據結構.它告訴優化器別名所引用的數據源對象(表或視圖)
是否存在索引.只是索引的描述.
索引顧問,,db2advis
索引得最大列數是16,最大長度是1024字節.
CREATE INDEX <name> ON <talbe_name> (<column_name>)
重命名表或索引
要重命名的表或索引不能是目錄表或索引,總結表或索引,類型表,已聲明
全局臨時表以及昵稱的名稱.
并且不能在下列任何一個對象中引用現有表或索引:
視圖,觸發器,引用約束,總結表,現有引用列的作用域
表中不能有檢查約束,不能有除身份列以外的其他生成列.
RENAME TABLE <SCHEMA_NAME>.<TABLE_NAME> TO <NEW_NAME>
RENAME INDEX <schema_name>.<index_name> TO <new_name>
索引可以：
是升序或是降序（如果沒有指定，缺省值是升序）
是唯一的，也可以是不唯一的（如果沒有指定，缺省值是不唯一的）
是復合的
用來執行群集
是雙向的（這由 allow 或 disallow reverse scans 控制的）
包括其它列（這只適用于唯一索引）。
刪除表
DROP TABLE <TABLE_NAME>
刪除索引
DROP INDEX <index_name>
通過命令行處理器調用"性能配置向導"
使用AUTOCONFIGURE
終止所有應用程序與數據庫的連接
db2 force applicaitons all
#給用戶授權
GRANT privilege ON object-type object-name
TO [{USER | GROUP | PUBLIC}] authorization-name
[WITH GRANT OPTION]
GRANT INSERT,DELETE ON TABLE staff TO USER rosita WITH GRANT OPTION
#撤消用戶權利
REVOKE privilege ON object-type object-name
FROM [{USER | GROUP | PUBLIC}] authorization-name
REVOKE ALL PRIVILEGES ON TABLE staff FROM joanna