2.1 ҪӵڽڵĿ¼ѹΪtestdb2ʵ
db2 attach to testdb2

ִtestdb2ʵά󣬾ͿִͨʵӶ
db2 detach

2.2 USER AڼƻCзһCREATE TABLE䡣
CREATE TABLE C.X (COL1 INT)

2.4 ݿMYDBȻݿתΪSQL룬MYDBݿɵSQL¡
UPDATE DATABASE CONFIGURATION FOR BMDB
USING APP_CTL_HEAP_SZ 128;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING BUFFPAGE 14606;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING CATALOGCACHE_SZ 935;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING CHNGPGS_THRESH 50;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING DBHEAP 600;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING LOCKLIST 50;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING LOGBUFSZ 131;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING LOGFILSIZ 1024;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING LOGPRIMARY 3;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING LOGSECOND 1;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING MAXAPPLS 40;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING MAXLOCKS 77;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING MINCOMMIT 1;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING NUM_IOCLEANERS 1;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING NUM_IOSERVERS 3;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING PCKCACHESZ 604;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING SOFTMAX 105;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING SORTHEAP 192;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING STMTHEAP 2048;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING DFT_DEGREE 1;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING DFT_PREFETCH_SZ 32;
UPDATE DATABASE CONFIGURATION FOR BMDB
USING UTIL_HEAP_SZ 000;
UPDATE DATABASE MANAGER CONFIGURATION
USING SHEAPTHRES 1954;
UPDATE DATABASE MANAGER CONFIGURATION
USING INTRA_PARALLEL OFF;
UPDATE DATABASE MANAGER CONFIGURATION
USING MAX_QUERYDEGREE 1;
UPDATE DATABASE MANAGER CONFIGURATION
USING MAXAGENTS 400;
UPDATE DATABASE MANAGER CONFIGURATION
USING NUM_POOLAGEN 400;
UPDATE DATABASE MANAGER CONFIGURATION
USING NUM_INITAGENTS 0;
UPDATE DATABASE MANAGER CONFIGURATION
USING FCM_NUM_BUFFERS 4096;
UPDATE DATABASE MANAGER CONFIGURATION
USING FCM_NUM_RQB 0;
CONNECT TO BMDB;
ALTER BUFFERPOOL IBMDEFAULTBP SIZE 14606;
COMMIT;
CONNECT RESET;

----------------------------------------
The command completed successfully. //ɹɡ
Explanation: //͡
No errors were encountered during the execution of this command.
//ִʱûв
User Response: //ûӦ
No action required. //ûлӦ

2.5 CREATE DATABASEһʾʹǰ۵ĺܶѡ
 CREATE DATABASE MY1STDB
  DFT_EXTENT_SZ 4
  CATALOG TABLESPACE MANAGED BY DATABASE USING
   (FILE 'C:\CAT\CATALOG.DAT' 2000, FILE 'D:\CAT\CATALOG.DAT' 2000)
    EXTENTSIZE 8
    PREFETCHSIZE 16
    TEMPORARY TABLESPACE MANAGED BY SYSTEM USING
   ('C:\TEMPTS','D:\TEMPTS')
   USER TABLESPACE MANAGED BY DATABASE USING
   (FILE 'C:\TS\USERTS.DAT' 121)
    EXTENTSIZE 24
    PREFETCHSIZE 48

2.6 дpersonlݿĿΪhumanresͼ2.14ʾ
CATALOG DATABASE personl AS humanres
WITH "Human Resources Database"

2.7
db2 CATALOG APPC NODE DB2NODE
REMOTE DB2CPIC SECURITY PROGRAM

2.8
db2 CATALOG TCPIP NODE MVSIPNOD
REMOTE MVSHOST
SERVER DB2INSTC

2.10
CREATE TABLESPACE dms1
MANAGED BY DATABASE
USING (DEVICE /dev/raw/raw1 11170736)

2.11 дʹCREATE TABLESSPACEռдSTUDENT
CREATE TABLE STUDENT
(STUNO		CHAR(2)			NOT NULL PRIMARY KEY,
NAME			VARCHAR(12)	NOT NULL,
CLASS		CHAR(3),
COURSE		CHAR(4),
SCORE		DECIMAL(3,2))
IN SSPACE

2.12 STUDENTеΪCLASSCLASSеΪCLASSNO.
常CLASSSQL£
CREATE TABLE CLASS
(CLASSNO		CHAR(3)			NOT NULL,
DEPTNO		CHAR(3)			NOT NULL)
IN SSPACE

2.13 CREATE TABLE䶨Уͼ2.22ʾ
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))

2.14 д䣬ʱgbl_tempͼ2.23ʾ
DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE empltabl
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp

2.15 CREATE TABLEжʶУͼ2.24ʾ
CREATE TABLE table
(col1	INT,
col2	DOUBLE,
col3	INT			NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 100, INCREMENT BY 5)
)

2.15 ͨSQL䴴ͼ
CREATE VIEW EMPV
(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
PHONENO, HIREDATE, DEPTNAME)
AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, 
PHONENO, HIREDATE, DEPTNAME
FROM EMPLOYEE, DEPARTMENT WHERE 
EMPLOYEE.WORKDEPT = DEPARTMENT.DEPTNO

ϵEMPVͼ壬ʹͼݣѾ䡣
2.16
CREATE TRIGGER EMPV_INSERT INSTEAD OF INSERT ON EMPV
REFERENCING NEW AS NEWEMP DEFAULTS NULL
FOR EACH ROW MODE DB2SQL
INSERT INTO EMPLOYEE
(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
WORKDEPT, PHONENO, HIREDATE)
VALUES
(EMPNO, FIRSTNME, MIDINIT, LASTNAME,
COALESCE((SELECT DEPTNO FROM DEPARTMENT AS D
WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
RAISE_ERROR(70001, Unknown department name)),
PHONENO, HIREDATE)

CREATE TRIGGERִжEMPVͼINSERT
2.17
CREATE TRIGGER EMPV_DELETE INSTEAD OF DELETE ON EMPV
REFERENCING OLD AS OLDEMP FOR EACH ROW MODE DB2SQL
DELETE FROM EMPLOYEE AS E
WHERE E.EMPNO = OLDEMP.EMPNO

CREATE TRIGGERִжEMPVͼDELETE
2.18
CREATE TRIGGER EMPV_UPDATE INSTEAD OF UPDATE ON EMPV
REFERENCING NEW AS NEWEMP
OLD AS OLDEMP
DEFAULTS NULL FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
VALUES
(
CASE WHEN NEWEMP.EMPNO = OLDEMP.EMPNO THEN 0
ELSE RAISE_ERROR(70002, Mustnotchange EMPNO) END);
UPDATE EMPLOYEE AS E
SET
(FIRSTNME, MIDINIT, LASTNAME,
WORKDEPT, PHONENO, HIREDATE)
= (NEWEMP.FIRSTNME, NEWEMP.MIDINIT, NEWEMP.LASTNAME,
COALESCE((SELECT DEPTNO FROM DEPARTMENT AS D
WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
RAISE_ERROR(70001, Unknown department name)),
NEWEMP.PHONENO, NEWEMP.HIREDATE)
WHERE NEWEMP.EMPNO = E.EMPNO;
END

2.19 ǿ޼ΪSTAGTAB1ķֶα
SET INTEGRITY FOR STAGTAB1 PRUNE;

2.1  SQLռĻͳơ
CREATE INDEX IDX1
ON TABL1
(COL1)
COLLECT STATISTICS
