注册登录
提 问
English中文版
itsbegin
作者itsbegin·2013-01-22 09:47
软件开发工程师·上海通联支付
关注4

【原创】DB2学习 - 《DB2 9 数据库管理（731考试）认证指南系列教程》

字数 42762阅读 5114评论 1赞 0
DB2学习 20110102 - 《DB2 9 数据库管理（731考试）认证指南系列教程_学习.PDF》
 
在内置LINUX上启动sample数据库：
db2inst@linux11:~$ db2ilist
db2inst
db2inst@linux11:~$ db2 list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias = SAMPLE
 Database name = SAMPLE
 Local database directory = /home/db2inst
 Database release level = d.00
 Comment =
 Directory entry type = Indirect
 Catalog database partition number = 0
 Alternate server hostname =
 Alternate server port number =

db2inst@linux11:~$ db2 connect to sample
SQL1032N No start database manager command was issued. SQLSTATE=57019
db2inst@linux11:~$ db2start
SQL1063N DB2START processing was successful.
db2inst@linux11:~$ db2 connect to sample

   Database Connection Information

 Database server = DB2/LINUX 9.7.1
 SQL authorization ID = DB2INST
 Local database alias = SAMPLE
 
db2inst@linux11:~$ db2 list tables

Table/View Schema Type Creation time            
------------------------------- --------------- ----- --------------------------
ACT DB2INST T 2010-12-29-17.20.08.198182
ADEFUSR DB2INST S 2010-12-29-17.20.15.866736
CATALOG DB2INST T 2010-12-29-17.20.33.823019
CL_SCHED DB2INST T 2010-12-29-17.19.57.901308
CUSTOMER DB2INST T 2010-12-29-17.20.29.201829
DEPARTMENT DB2INST T 2010-12-29-17.19.58.760218
DEPT DB2INST A 2010-12-29-17.20.00.037330
EMP DB2INST A 2010-12-29-17.20.01.168002
EMPACT DB2INST A 2010-12-29-17.20.08.196197
EMPLOYEE DB2INST T 2010-12-29-17.20.00.038547
EMPMDC DB2INST T 2010-12-29-17.20.20.571620
EMPPROJACT DB2INST T 2010-12-29-17.20.07.657918
EMP_ACT DB2INST A 2010-12-29-17.20.08.197282
EMP_PHOTO DB2INST T 2010-12-29-17.20.01.169407
EMP_RESUME DB2INST T 2010-12-29-17.20.03.116023
INVENTORY DB2INST T 2010-12-29-17.20.28.001869
IN_TRAY DB2INST T 2010-12-29-17.20.09.357993
ORG DB2INST T 2010-12-29-17.20.09.906673
PRODUCT DB2INST T 2010-12-29-17.20.26.042111
PRODUCTSUPPLIER DB2INST T 2010-12-29-17.20.37.753741
PROJ DB2INST A 2010-12-29-17.20.06.518714
PROJACT DB2INST T 2010-12-29-17.20.06.520367
PROJECT DB2INST T 2010-12-29-17.20.05.422969
PURCHASEORDER DB2INST T 2010-12-29-17.20.30.877075
SALES DB2INST T 2010-12-29-17.20.11.053228
STAFF DB2INST T 2010-12-29-17.20.10.445646
STAFFG DB2INST T 2010-12-29-17.20.14.333883
SUPPLIERS DB2INST T 2010-12-29-17.20.36.052710
VACT DB2INST V 2010-12-29-17.20.11.739953
VASTRDE1 DB2INST V 2010-12-29-17.20.11.833058
VASTRDE2 DB2INST V 2010-12-29-17.20.11.838598
VDEPMG1 DB2INST V 2010-12-29-17.20.11.818672
VDEPT DB2INST V 2010-12-29-17.20.11.557304
VEMP DB2INST V 2010-12-29-17.20.11.733034
VEMPDPT1 DB2INST V 2010-12-29-17.20.11.828279
VEMPLP DB2INST V 2010-12-29-17.20.11.877433
VEMPPROJACT DB2INST V 2010-12-29-17.20.11.813495
VFORPLA DB2INST V 2010-12-29-17.20.11.859814
VHDEPT DB2INST V 2010-12-29-17.20.11.727739
VPHONE DB2INST V 2010-12-29-17.20.11.872415
VPROJ DB2INST V 2010-12-29-17.20.11.736679
VPROJACT DB2INST V 2010-12-29-17.20.11.790290
VPROJRE1 DB2INST V 2010-12-29-17.20.11.844475
VPSTRDE1 DB2INST V 2010-12-29-17.20.11.850540
VPSTRDE2 DB2INST V 2010-12-29-17.20.11.855591
VSTAFAC1 DB2INST V 2010-12-29-17.20.11.863893
VSTAFAC2 DB2INST V 2010-12-29-17.20.11.867994

  47 record(s) selected.
db2inst@linux11:~$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.1

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => create table table1(c1 int,c2 int)
DB20000I The SQL command completed successfully.
db2 => select * from table1

C1 C2        
----------- -----------

  0 record(s) selected.
 
db2 => quit
 
 
设置配置文件注册库
使用db2set来进行配置设置，配置分为5个域，具体解释如下：
[e]当前会话或环境所设置的注册库
[u]用户级注册库
[n]节点级注册库
[i]实例级注册库
[g]全局级注册库
 
db2inst@linux11:~$ db2 attach to db2inst

   Instance Attachment Information

 Instance server = DB2/LINUX 9.7.1
 Authorization ID = DB2INST
 Local instance alias = DB2INST

db2inst@linux11:~$ db2 connect to sample

   Database Connection Information

 Database server = DB2/LINUX 9.7.1
 SQL authorization ID = DB2INST
 Local database alias = SAMPLE
 
db2inst@linux11:~$ db2set -lr
DB2_OVERRIDE_BPF
......
DB2_STANDBY_ISO
db2inst@linux11:~$ db2set
DB2PROCESSORS=0
db2inst@linux11:~$ db2set -all
[i] DB2PROCESSORS=0
[g] DB2SYSTEM=linux11
[g] DB2INSTDEF=db2inst
[g] DB2ADMINSERVER=dasusr
db2inst@linux11:~$ db2 get instance

 The current database manager instance is: db2inst
db2inst@linux11:~$ db2 get dbm cfg|grep LOCK
   Lock (DFT_MON_LOCK) = OFF
db2inst@linux11:~$ db2 get db cfg|grep LOCK
 Max storage for lock list (4KB) (LOCKLIST) = 4096
 Percent. of lock lists per application (MAXLOCKS) = 10
 Lock timeout (sec) (LOCKTIMEOUT) = -1
 Block non logged operations (BLOCKNONLOGGED) = NO
 Lock timeout events (MON_LOCKTIMEOUT) = NONE
 Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
 Lock wait events (MON_LOCKWAIT) = NONE
db2inst@linux11:~$ db2 get db cfg|grep DATABASE 
 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(29088)
db2inst@linux11:~$ db2 get db cfg|grep DATABASE
 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(29088)
db2inst@linux11:~$ db2 update db cfg using LOCKLIST 8192
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2inst@linux11:~$ db2 get db cfg |grep LOCKLIST
 Max storage for lock list (4KB) (LOCKLIST) = 8192
 
db2inst@linux11:~$ db2 update db cfg using DATABASE_MEMORY 29089
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2inst@linux11:~$ db2 get db cfg|grep DATABASE
 Size of database shared memory (4KB) (DATABASE_MEMORY) = 29089
db2inst@linux11:~$ db2 update db cfg using DATABASE_MEMORY automatic
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
db2inst@linux11:~$ db2 get db cfg|grep DATABASE
 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(29089)
 
db2inst@linux11:~$ db2 get dbm cfg show detail

          Database Manager Configuration

     Node type = Database Server with local and remote clients

 Description Parameter Current Value Delayed Value
 ---------------------------------------------------------------------------------------------------------------
 Database manager configuration release level = 0x0d00

 CPU speed (millisec/instruction) (CPUSPEED) = 4.684080e-07 4.684080e-07             

 Max number of concurrently active databases (NUMDB) = 8 8                        
 Federated Database System Support (FEDERATED) = NO NO                       
 Transaction processor monitor name (TP_MON_NAME) =                                                                                                          
 
强制停止实例和数据库
 
可以通过force applications all来强制所有数据库连接。
如果同时关闭数据库，使用db2stop force。
如果指定关闭某个数据库连接，使用force application(NO)的语法，如下：
db2inst@linux11:~$ db2 list applications

Auth Id Application Appl. Application Id DB # of
         Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST db2bp 205 *LOCAL.db2inst.110103140837 SAMPLE 1   

db2inst@linux11:~$ db2 force applications all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.

db2inst@linux11:~$ db2 list applications
SQL1611W No data was returned by Database System Monitor.

db2inst@linux11:~$ db2 connect to sample

   Database Connection Information

 Database server = DB2/LINUX 9.7.1
 SQL authorization ID = DB2INST
 Local database alias = SAMPLE

db2inst@linux11:~$ db2 list applications

Auth Id Application Appl. Application Id DB # of
         Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST db2bp 235 *LOCAL.db2inst.110103141836 SAMPLE 1   
 
db2inst@linux11:~$ db2 "force application(235)"
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
 
服务器/客户端的连接配置
DB2目前支持如下协议：TCP/IP, NPIPE。对于到主数据库（如：db2 for z/OS、iSeries）的连接，必须使用DRDA的DB2 Connect。
配置TCPIP连接，需要设置DB2COMM参数，并对dbm进行配置（一般需要同时对/etc/services进行配置，便于系统端口维护）。
另外，可以通过db2admin start启动DAS服务，如果此时DISCOVER设置为SEARCH且DISCOVER_INST是ENABLE，且DISCOVER_DB=ENABLE时，这个数据库可以被search到。
 
db2inst@linux11:~$ db2set DB2COMM=TCPIP
db2inst@linux11:~$ db2set -all
[i] DB2PROCESSORS=0
[i] DB2COMM=TCPIP
[g] DB2SYSTEM=linux11
[g] DB2INSTDEF=db2inst
[g] DB2ADMINSERVER=dasusr
 
root@linux11:~# vi etc/services
root@linux11:/etc# cat /etc/services|grep db2
sample 61000/tcp # db2 sample database service
root@linux11:/etc# su - db2inst
db2inst@linux11:~$ db2 update dbm cfg using svcename sample
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.
db2inst@linux11:~$ db2 get dbm cfg|grep SVCE 
 TCP/IP Service name (SVCENAME) = sample
 SSL service name (SSL_SVCENAME) = 
 
db2inst@linux11:~$ netstat -an|grep 61000
db2inst@linux11:~$ db2stop
SQL1064N DB2STOP processing was successful.
db2inst@linux11:~$ db2start
SQL1063N DB2START processing was successful.
db2inst@linux11:~$ netstat -an|grep 61000
tcp 0 0 0.0.0.0:61000 0.0.0.0:* LISTEN    
 
db2inst@linux11:~$ db2 get dbm cfg|grep DIS
 Discovery mode (DISCOVER) = SEARCH
 Discover server instance (DISCOVER_INST) = ENABLE
 
 
缓冲池
数据库有一个默认缓冲池叫IBMDEFAULTBP，页宽4KB，WINDOWS默认缓冲池为250页或1MB，UNIX默认缓冲池为1000页或4MB。默认缓冲池不能被删除，可以通过alter bufferpool来更改。
 
 
 
客户端配置管理
1. 客户端导入配置文件使用命令：db2cfimp <profile_name>
2. 手动配置：使用Configuration Assistant的Add database向导，或者使用命令：
    编目节点
        db2 catalog tcpip node mynode remote db2server.mycompany.com server db2icdb
        db2 catalog netbios node jeremy remote N01FCBE3 adapter 0
    编目数据库
        db2 catalog database sample as mysamp at node mynode
        db2 catalog database baydb as newbaydb at node mynode
3. 列举节点和数据库目录
    db2 list node directory
    db2 list database directory
 
附加到实例并连接到数据库
可以使用attach to <nodename> user <username> using <password>来实现附加到DB2实例。之后，可以进行：
    1. 创建和删除数据库
    2. 查找、更新和重置数据库管理器和配置参数
    3. 管理数据库监控程序
    4. 备份、还原和前滚数据库
    5. 强制用户和应用程序断开数据库连接
通过connect to <database name>连接数据库后，可以运行select,insert,update,delete等操作，可以进行DDL操作。
--------------------------------------------------------------------------------------------------------------------------------------------------
DB2安全性
 
身份验证类型有：
SERVER / SERVER_ENCRYPT / KERBEROS / KRB_SERVER_ENCRYPT / CLIENT
一般        / 加密ID与口令            / 加密密钥      / kerberos的密钥认证，其它/ 在客户端进行身份验证  
                                                                          的使用server_encrypt身份
身份验证类型是需要在客户端与服务器处同时设置的：服务器端设置认证方式，客户端进行认证方式编目。
    服务器端：db2 update dbm cfg authentication <auth_type>
    客户机端：db2 catalog database <db_name> at node <node_name> authentication  <auth_type>
 
当使用client认证时，信任分为：trust_allclnts和trust_clntauth 两种方式。
TRUST_ALLCLNTS有YES, NO, DRDAONLY，其中YES信任所有，NO仅信任受信任的客户机，否则提供用户ID和口令；DRDA仅信任在iSeries或zSeries平台上运行的客户机。
 
DB2权限级别
SYSADM, DBADM, LOAD, SYSCTRL, SYSMAINT。
其中，SYSADM具有管理实例的完整权限；SYSCTRL, SYSMAINT拥有管理实例、数据库和数据库对象的特定特权，但不含访问数据的权限；DBADM可以访问数据和执行管理任务；LOAD有加载实用工具的特权。
db2inst@linux11:~$ db2 get dbm cfg |grep GROUP
 SYSADM group name (SYSADM_GROUP) = DB2IADM 
 SYSCTRL group name (SYSCTRL_GROUP) = 
 SYSMAINT group name (SYSMAINT_GROUP) = 
 SYSMON group name (SYSMON_GROUP) = 
 Group Plugin (GROUP_PLUGIN) = 
db2inst@linux11:~$ id
uid=500(db2inst) gid=600(db2iadm) groups=600(db2iadm),602(db2asgrp)
 
 
 
 
为了安装DB2的STUDIO，需要配置JAVA环境，下载java.sun.com的JAVA 1.6 RE环境，并配置PATH和JAVA_HOME环境变量，使用java -version验证。
然后解压tar.gz包后，运行install.bin，按提示一路回车。
root@linux11:/opt/softs/linux# ls
eclipse install.bin
root@linux11:/opt/softs/linux# ./install.bin
Preparing to install...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...


Graphical installers are not supported by the VM. The console mode will be used instead...

Preparing CONSOLE Mode Installation...

===============================================================================
Choose Locale...
----------------

    1- Deutsch
  ->2- English
    3- Español
    4- Français
    5- Italiano
    6- Português (Brasil)

CHOOSE LOCALE BY NUMBER: 
===============================================================================
IBM Data Studio stand-alone (created with InstallAnywhere)
-------------------------------------------------------------------------------




===============================================================================
Introduction
------------

InstallAnywhere will guide you through the installation of IBM Data Studio 
stand-alone.

It is strongly recommended that you quit all programs before continuing with 
this installation.

Respond to each prompt to proceed to the next step in the installation. If you
want to change something on a previous step, type 'back'.

You may cancel this installation at any time by typing 'quit'.

PRESS <ENTER> TO CONTINUE: 



===============================================================================




    International License Agreement for Non-Warranted Programs

    Part 1 - General Terms

    BY DOWNLOADING, INSTALLING, COPYING, ACCESSING, CLICKING ON AN
    "ACCEPT" BUTTON, OR OTHERWISE USING THE PROGRAM, LICENSEE AGREES TO
    THE TERMS OF THIS AGREEMENT. IF YOU ARE ACCEPTING THESE TERMS ON
    BEHALF OF LICENSEE, YOU REPRESENT AND WARRANT THAT YOU HAVE FULL
    AUTHORITY TO BIND LICENSEE TO THESE TERMS. IF YOU DO NOT AGREE TO
    THESE TERMS,

    * DO NOT DOWNLOAD, INSTALL, COPY, ACCESS, CLICK ON AN "ACCEPT" BUTTON,
    OR USE THE PROGRAM; AND

    * PROMPTLY RETURN THE UNUSED MEDIA AND DOCUMENTATION TO THE PARTY FROM
    WHOM IT WAS OBTAINED FOR A REFUND OF THE AMOUNT PAID. IF THE PROGRAM
    WAS DOWNLOADED, DESTROY ALL COPIES OF THE PROGRAM.

    1. Definitions

Press Enter to continue viewing the license agreement, or enter "1" to 
   accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
   to the previous screen.: 1




===============================================================================
Choose Install Folder
---------------------

Where would you like to install?

  Default Install Folder: /opt/IBM/IBM Data Studio stand-alone

ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
      : 



===============================================================================
Pre-Installation Summary
------------------------

Please review the following information before continuing:

Product Name:
    IBM Data Studio stand-alone

Install Folder:
    /opt/IBM/IBM Data Studio stand-alone

Disk Space Information (for Installation Target): 
    Required: 302,542,667 bytes
    Available: 14,947,532,800 bytes

PRESS <ENTER> TO CONTINUE: 



===============================================================================
Ready To Install
----------------

InstallAnywhere is now ready to install IBM Data Studio stand-alone onto your 
system at the following location:

/opt/IBM/IBM Data Studio stand-alone

PRESS <ENTER> TO INSTALL: 



===============================================================================
Installing...
-------------

 [==================|==================|==================|==================]
 [------------------|------------------|------------------|------------------]



===============================================================================
Installation Complete
---------------------

Congratulations! IBM Data Studio stand-alone has been successfully installed 
to:

/opt/IBM/IBM Data Studio stand-alone

PRESS <ENTER> TO EXIT THE INSTALLER: 
管理DB2权限
1. SYS*权限是DBM设置中的，最大8字符组名称，如下：
    SYSADM         ===> SYSADM_GROUP
    SYSCTRL       ===> SYSCTRL_GROUP
    SYSMAINT      ===> SYSMAINT_GROUP
    使用如下语句进行配置：$db2 update dbm cfg using sysadm_group <adm_name>
    DBADM和LOAD是数据库级别的权限，可以使用grant 和revoke进行赋权和撤权：    
root@linux11:~# su - db2inst
db2inst@linux11:~$ db2start
SQL1063N DB2START processing was successful.
db2inst@linux11:~$ db2 connect to sample

   Database Connection Information

 Database server = DB2/LINUX 9.7.1
 SQL authorization ID = DB2INST
 Local database alias = SAMPLE

db2inst@linux11:~$ id
uid=500(db2inst) gid=600(db2iadm) groups=600(db2iadm),602(db2asgrp)
db2inst@linux11:~$ db2 grant dbadm on database to user john
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 grant load on database to group db2asgrp
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 revoke load on database from group db2asgrp
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 list active databases

                           Active Databases

Database name = SAMPLE
Applications connected currently = 1
Database path = /home/db2inst/db2inst/NODE0000/SQL00001/
2.  数据库特权有：
    CONNECT：容许连接数据库
    BINDADD：容许创建包
    CREATETAB：容许用户在数据库中建表
    CREATE_NOT_FENCED：容许用户创建NOT FENCED用户定义的函数或存储过程
    fenced用户的UDF和SP作为独立进程运行在独立地址空间里,和DB2通过IPC进行通信.unfenced则直接被DB2映射进自己的地址空间执行.
    这显然效率好的多.直接看DB2的进程模型就很清楚了.
    IMPLICIT_SCHEMA：容许用户在尚不存在的模式下创建对象
    QUIESCE_CONNECT：容许用户在数据库停顿时访问数据库
    CREATE_EXTERNAL_ROUTINE：容许用户创建C,JAVA,OLD,COBOL编写的存储过程
3. 模式特权：
    CREATEIN：容许用户创建模式内对象
    ALTERIN：容许用户更改模式内对象
    DROPIN：容许用户删除模式内对象
db2inst@linux11:~$ db2 create schema dev
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "create table dev.test(id int)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "insert into dev.test values(100)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "select * from dev.test"

ID        
-----------
        100

  1 record(s) selected.

db2inst@linux11:~$ db2 describe table dev.test

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 Yes  

  1 record(s) selected.
db2inst@linux11:~$ db2 list tables for schema dev

Table/View Schema Type Creation time            
------------------------------- --------------- ----- --------------------------
TEST DEV T 2011-01-06-13.52.36.195314

  1 record(s) selected.
4. 表空间特权：
    USE OF TABLESPACE 容许用户在特定表空间内建表。这个特权无法用户SYSCATSPACE或任何系统临时表空间。
5. 表与视图特权：
    CONTROL：为用户提供表或视图的所有特权，以及特权赋予他人的能力（无CONTROL）
    ALTER：容许用户更改表或视图
    DELETE/INSERT/SELECT/UPDATE：容许删除/插入/选择/更新记录条目。
                                                                其中，UPDATE可以将用户约束为特定列更新：grant update(<col1_name>,<col2_name>) on table <tab_name> to <usr_name>
    INDEX：容许用户在表上创建索引
    REFERENCES：容许用户创建/删除外键，指定为关系表的父表
    ALL PRIVILEGES：授予全部特权，但无授予他人的能力
5. 包特权：
    CONTROL：提供重新绑定、删除、执行包，以及赋权能力（除CONTROL）
    BIND：重新绑定现有包
    EXECUTE：执行包
6. 索引特权：CONTROL：容许删除索引
7. 例程特权：EXECUTE：执行函数
8. 顺序特权：USAGE：容许用户为顺序对象使用NEXTVAL和PREVVAL的表达式
9. 授予显示特权：WITH GRANT OPTION：特权赋予他人，获得授权后，用户不能侧小他人的授权。
db2inst@linux11:~$ db2 grant select,update,delete,insert on table dev.test to user john with grant option
DB20000I The SQL command completed successfully.

10.授予隐式及间接特权：执行一些特定操作时获得授权时隐含的。例如：创建对象的用户被赋予CONTROL特权；被授予DBADM的被隐式授权BINDADD,CONNECT,CREATETAB,CREATE_NOT_FENCED和IMPLICIT_SCHEMA权限；用户创建数据库会获得DBADM，CONNECT,CREATETAB,BINDADD,IMPLICIT_SCHEMA,USERSPACE1上的USE OF TABLESPACE被赋予PUBLIC等。

安排作业

作业是通过IBM DB2 Task Center来实现的，包括创建和组织任务，管理任务流，安排任务，任务状态通知等。

创建数据库
最基本语法：create database sample on /database，
                     并将在目录下建立/database/dbinst/NODE0000/sqldbdir和SQL00001目录。
1. 创建DMS类型的USERSPACE1表空间：
    create database sample user table space managed by database
    using(file '/dafiles/cont0' 5000,file '/dbfiles/cont1' 5000)
2. 创建SMS类型的TEMPSPACE1表空间：
    create database sample temporary tablespace managed by system
    using(file '/dbfiles/cont0','/dbfiles/cont1')
3. 创建改变数据库排序序列的数据库：
    create database sample on /database collate using identity
4. 创建带有自动存储类型的数据库：
    create database sample automatic storage yes
    create database sample on db_path1, db_path2
    alter database sample add storage on db_path3
 
创建表空间
语法：create tablespace <ts_name> initialsize 10M increasesize 10M maxsize 100M
如果数据库无自动存储，表空间也可以使用这种特性：create tablespace <ts_name> managed by automatic storage
 
模式（schema）
模式是用于在数据库中创建的数据库对象的一个高级限定符，是一个数据库对象的集合。使用authorization可以指定模式所有者，否则为创建者所有。所有者可以将CREATE_IN特权授权给其它用户或组。
系统模式
SYSIBM：存放基本系统编目，不建议直接访问。
SYSCAT：存放只读编目视图，SELECT权限被授予PUBLIC供访问。
SYSSTAT：可更新的编目视图，信息会影响优化器。
SYSFUN：用户定义函数。
可以使用set current schema或set current sqlid命令指定对象模式。
 
表空间状态
表空间状态可以用list tablespaces show detail来查看，有很多状态，从0x0,0x1,0x2,0x4,0x8,0x10,0x20....到0x40000000等20多种状态。
 
创建和维护不同的DB2对象
缓冲池
创建数据库时，系统默认创建一个IBMDEFAULTBP的缓冲池，在WINDOWS默认1000页（4MB）或UNIX上大小250页（1MB, 4KB/页）。默认缓冲池不能被删除，但可以修改大小。
可以使用create bufferpool创建新的池，不能用SYS和IBM开头。
 
immediate参数：默认缓冲池参数，如果没有足够的内存，会立即创建，否则将被推迟（deferred）
deferred参数：下一次数据库重新启动时再创建
all dbpartitionnumbs参数：默认设置，缓冲区不指定分区组，将给所有分区使用
size参数：缓冲池有多少个页
blocksize参数：基于块的区域中一个给定块的数据页数量，必须在2-256页之间，默认32页
numblockpages参数：指定在缓冲池的基于块的区域中创建的数据页的数量，必须是blocksize的整数倍。最大为缓冲池的98%。如果大小为0，将禁用缓冲池的块I/O
pagesize参数：缓冲区的页宽，默认4K
extended storage/not extended storage参数：是否创建扩展存储区，不适用32位环境。效率介于磁盘和缓冲池之间
db2inst@linux11:~$ db2 create bufferpool bp2 size 1000 pagesize 8k numblockpages 900 
DB20000I The SQL command completed successfully.
 
创建表和获取表信息
create table <tab_name> ( <col1_name> <col_type...>, ... <colN_name> <col_type...>) INDEX IN <ind_tbs_name> LONG IN <lob_tbs_name> IN <tab_tbs_name>
不指定表空间将默认放在IBMDEFAULTGROUP表空间；不够大则放在一个用户创建的表空间中；否则放在USERSPACE1表空间。关于“INDEX IN ,LONG IN ,IN” 这些参数只使用DMS类型。
 
表信息的获取，可以通过以下这些命令：
db2 list tables
db2 list tables for all
db2 list tables for system
db2 list tables for schema <user_name>
db2 describe table <schema_name>.<table_name>
db2inst@linux11:~$ db2 list tables

Table/View Schema Type Creation time            
------------------------------- --------------- ----- --------------------------
ACT DB2INST T 2010-12-29-17.20.08.198182
...
VSTAFAC2 DB2INST V 2010-12-29-17.20.11.867994

  48 record(s) selected.

db2inst@linux11:~$ db2 list tables for all

Table/View Schema Type Creation time            
------------------------------- --------------- ----- --------------------------
ACT DB2INST T 2010-12-29-17.20.08.198182
....
TEST DEV T 2011-01-06-13.52.36.195314
ATTRIBUTES SYSCAT V 2010-12-29-17.18.25.774427
...
CHECK_CONSTRAINTS SYSIBM V 2010-12-29-17.18.25.120022
...
ADMINTABCOMPRESSINFO SYSIBMADM V 2010-12-29-17.18.50.352927
...
DUAL SYSPUBLIC A 2010-12-29-17.18.25.383384
COLDIST SYSSTAT V 2010-12-29-17.18.30.148698
...
HMON_ATM_INFO SYSTOOLS T 2010-12-29-17.22.20.232579
HMON_COLLECTION SYSTOOLS T 2010-12-29-17.22.20.522058
POLICY SYSTOOLS T 2010-12-29-17.22.18.316991

  445 record(s) selected.

db2inst@linux11:~$ db2 list tables for system

Table/View Schema Type Creation time            
------------------------------- --------------- ----- --------------------------
ATTRIBUTES SYSCAT V 2010-12-29-17.18.25.774427
...
HMON_COLLECTION SYSTOOLS T 2010-12-29-17.22.20.522058
POLICY SYSTOOLS T 2010-12-29-17.22.18.316991

  396 record(s) selected.

db2inst@linux11:~$ db2 list tables for schema dev

Table/View Schema Type Creation time            
------------------------------- --------------- ----- --------------------------
TEST DEV T 2011-01-06-13.52.36.195314

  1 record(s) selected.

db2inst@linux11:~$ db2 describe table dev.test

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 Yes  

  1 record(s) selected.

 

索引
create unique index dev.idx_test on dev.test ( id)
create unique index idx_test on dev.test (id) disallow reverse scans collect detailed statistics
db2inst@linux11:~$ db2 "create index dev.idx_test_id on dev.test(id)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "drop index dev.idx_test_id"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "create index dev.idx_test_id on dev.test(id) disallow reverse scans collect detailed statistics"
DB20000I The SQL command completed successfully.
 
标识列
一个表最多只能有一个标识列。可以通过DB2 always或by default生成。
create table dev.inventory(partno INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 200 INCREMENT BY 1), description CHAR(20))
insert into dev.inventory values(DEFAULT, 'door')
insert into dev.inventory values('hinge')
insert into dev.inventory(description) values('table')
insert into dev.inventory values(300,'window')
 
db2inst@linux11:~$ db2 "create table dev.inventory(partno INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 200 INCREMENT BY 1), description CHAR(20)) "
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 describe table dev.inventory

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PARTNO SYSIBM INTEGER 4 0 No    
DESCRIPTION SYSIBM CHARACTER 20 0 Yes   

  2 record(s) selected.

db2inst@linux11:~$ db2 "insert into dev.inventory values(DEFAULT, 'door') "
DB20000I The SQL command completed successfully.

db2inst@linux11:~$ db2 "insert into dev.inventory values('hinge') "
DB21034E The command was processed as an SQL statement because it was not a 
valid Command Line Processor command. During SQL processing it returned:
SQL0117N The number of values assigned is not the same as the number of 
specified or implied columns or variables. SQLSTATE=42802

db2inst@linux11:~$ db2 "insert into dev.inventory(description) values('table') "
DB20000I The SQL command completed successfully.

db2inst@linux11:~$ db2 "insert into dev.inventory values(300,'window')"
DB21034E The command was processed as an SQL statement because it was not a 
valid Command Line Processor command. During SQL processing it returned:
SQL0798N A value cannot be specified for column "PARTNO" which is defined as 
GENERATED ALWAYS. SQLSTATE=428C9

db2inst@linux11:~$ db2 "select * from dev.inventory"

PARTNO DESCRIPTION         
----------- --------------------
        200 door                
        201 table               

  2 record(s) selected.
 
视图
有关视图的信息存在于系统表内：SYSCAT.VIEWS, SYSCAT.VIEWDEP, SYSCAT.TABLES
with check option 限制：通过一个视图插入或更新的每一行必须符合视图的定义。不符合视图定义的行，就是不满足视图搜索条件的行。限制保护了视图所对应的行范围区域不会被本身的DML语句索引影响。
 
约束
有以下几种约束：参照完整性约束，唯一性约束，检查约束，信息约束。约束不可以被修改，只能删除后重建。
  • 完整性约束：primary key, unique constraint, foreign key, references 子句。
  • 唯一性约束：唯一性约束迫使列具有唯一性，不能有NULL值。
  • 检查约束：当有检查约束并关闭时，表将处于check pending状态。
  • 信息约束：ENFORCED,NOT ENFORCED, ENABLE/DISABLE QUERY OPTIMIZATION四种信息约束。
 
触发器
触发器可以验证，调节和保护数据完整性。触发器比约束更灵活。
 
DB2存储三角形
DB2的存储从三角形顶端开始依次是：database, partition groups, tablespaces, containers。其中，分区组是为了支持分区数据库而形成的抽象层。分区组是由一个数据库中的一个或多个数据库分区组成的组或集合；表空间必须有1个或多个为表空间定义物理存储的容器。
 
表空间
表空间是逻辑实体，定义表或索引放在什么地方。获得详细的表空间信息可以使用：
db2 get snapshot for tablespaces on sample
list tablespaces show detail
 
SMS表空间特征
  • SMS表空间（SYSTEM MANAGED SPACE）内，每个容器是一个操作系统目录，创建SMS时必须指定容器的操作系统目录。所有数据和索引共享相同的表空间
  • 每个表有自己的名字，扩展名表示表的类型
  • 表文件的扩展收到操作系统的限制
  • 当一个容器内所有空间都被分配出去后，即使其它仍然具有空间，也认为表空间已满
  • 新的容器只能被添加到没有任何容器的分区的SMS中；SMS表空间在创建好后无法动态添加容器
  • 对于TEMP表空间推荐使用SMS类型
db2inst@linux11:~$ db2 "create tablespace tempspace2 managed by system using ('/home/db2inst/TEMPSPACE2')"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 list tablespaces

           Tablespaces for Current Database

 Tablespace ID = 0
 Name = SYSCATSPACE
 Type = Database managed space
 Contents = All permanent data. Regular table space.
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 1
 Name = TEMPSPACE1
 Type = System managed space
 Contents = System Temporary data
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 2
 Name = USERSPACE1
 Type = Database managed space
 Contents = All permanent data. Large table space.
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 3
 Name = IBMDB2SAMPLEREL
 Type = Database managed space
 Contents = All permanent data. Large table space.
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 4
 Name = IBMDB2SAMPLEXML
 Type = Database managed space
 Contents = All permanent data. Large table space.
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 5
 Name = SYSTOOLSPACE
 Type = Database managed space
 Contents = All permanent data. Large table space.
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 6
 Name = SYSTOOLSTMPSPACE
 Type = System managed space
 Contents = User Temporary data
 State = 0x0000
   Detailed explanation:
     Normal

 Tablespace ID = 7
 Name = TEMPSPACE2
 Type = System managed space
 Contents = All permanent data. Regular table space.
 State = 0x0000
   Detailed explanation:
     Normal

多维聚合（MDC）

db2inst@linux11:~/TEMPSPACE2$ db2 "create table dev.mdctable( year int, nation char(25), color varchar(10), region varchar(10)) organize by (year,nation,color)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~/TEMPSPACE2$ db2 describe table dev.mdctable

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
YEAR SYSIBM INTEGER 4 0 Yes   
NATION SYSIBM CHARACTER 25 0 Yes   
COLOR SYSIBM VARCHAR 10 0 Yes   
REGION SYSIBM VARCHAR 10 0 Yes   

  4 record(s) selected.

通常的，更大的盘区会减少I/O成本，因为每次读取了更多的数据；同时又减少了维块索引的数量，插入操作更快；因为盘区大，所以比小盘区更浪费磁盘空间。

表分区

创建一个分区表、将一个新的物理表添加到分区表中的两个语法示意：

create table t1(c2 int) in tbsp1, tbsp2, tbsp3 PARTITION BY RANGE(c1) 
(starting from (0) ending (80) every (10))
alter table fact_table attch partition starting '06-01-2006' ending '06-30-2006' 
from table fact_new_month

表压缩和预压缩

启用表压缩的语法：

create table <table_name> ... compress yes或者alter table ...compress yes

开始表压缩的语法：

reorg table <table_name> resetdictionary / keepdictionary

先不压缩，看看压缩效果（估计空间节约）：

db2 inspect rowcompestimate table name <table_name> results keep <file_name>
db2inspf <file_name> <output_file_name>

XML列于XML索引

略

数据管理

额外列值索引

DB2的索引，可以包含额外的列值进行存储，例如：

create unique index on dev.inventory (partno) include (description)

 集群索引

集群索引将相同的键值存放于相同的页内，减少键值查找时所需的开销。集群索引只需在普通索引创建语句后加CLUSTER关键词：

create index on dev.test (id) cluster
db2inst@linux11:~$ db2 "create table dev.department(deptno int not null primary key,deptname varchar(20) not null,mgrno int)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 describe table dev.department

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
DEPTNO SYSIBM INTEGER 4 0 No    
DEPTNAME SYSIBM VARCHAR 20 0 No    
MGRNO SYSIBM INTEGER 4 0 Yes   

  3 record(s) selected.

db2inst@linux11:~$ db2 "create table dev.employee(empno int not null primary key,firstname varchar(20) not null,lastname varchar(20) not null,workdept int not null,phoneno char(12),
foreign key (workdept) references dev.department (deptno) on delete restrict)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "grant references(deptno) on dev.department to user dev"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "alter table dev.employee add column job varchar(10) constraint check_job check (job in ('Engineer','Sales','Manager'))"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 describe table dev.employee

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMPNO SYSIBM INTEGER 4 0 No    
FIRSTNAME SYSIBM VARCHAR 20 0 No    
LASTNAME SYSIBM VARCHAR 20 0 No    
WORKDEPT SYSIBM INTEGER 4 0 No    
PHONENO SYSIBM CHARACTER 12 0 Yes   
JOB SYSIBM VARCHAR 10 0 Yes   

信息约束

可以使用not enforced来不强制约束生效（类似disable constraint），可以使用enable query optimization来使优化器使用约束定义的信息。示例如下：

db2inst@linux11:~$ db2 "create table dev.empdata(empno int not null,sex char(1) not null constraint sexok check(sex in ('M','F')) not enforced enable query optimization,salary int not null constraint salaryok check(salary between 0 and 100000) not enforced enable query optimization)"DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 describe table dev.empdata

                                Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMPNO SYSIBM INTEGER 4 0 No    
SEX SYSIBM CHARACTER 1 0 No    
SALARY SYSIBM INTEGER 4 0 No    

  3 record(s) selected.

db2inst@linux11:~$ db2 "insert into dev.empdata values (1,'M',54200),(2,'F',28000),(3,'M',30300),(4,'F',69000),(5,'Q',34444),(6,'M',109999)"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "select * from dev.empdata"

EMPNO SEX SALARY     
----------- --- -----------
          1 M 54200
          2 F 28000
          3 M 30300
          4 F 69000
          5 Q 34444
          6 M 109999

  6 record(s) selected.
db2inst@linux11:~$ db2 "select * from dev.empdata where sex='Q'"

EMPNO SEX SALARY     
----------- --- -----------

  0 record(s) selected.

db2inst@linux11:~$ db2 "alter table dev.empdata alter check sexok disable query optimization"
DB20000I The SQL command completed successfully.
db2inst@linux11:~$ db2 "select * from dev.empdata where sex='Q'"

EMPNO SEX SALARY     
----------- --- -----------
          5 Q 34444

  1 record(s) selected.

系统编目表

SYSCAT.COLUMNS
SYSCAT.INDEXCOLUSE：参与索引的所有列
SYSCAT.INDEXES
SYSCAT.TABLES
SYSCAT.VIEWS
 
默认表空间
SYSCATSPACE：用于系统编目表
TEMPSPACE1：用于系统创建的临时表
USERSPACE1：用户默认的表空间
SYSTOOLSPACE：管理工具和SQL管理例程用来存储历史数据和配置信息
SYSTOOLTMPSPACE：临时表空间
 
 
 
db2inst@linux11:~/sqllib/misc$ db2 connect to sample

   Database Connection Information

 Database server = DB2/LINUX 9.7.1
 SQL authorization ID = DB2INST
 Local database alias = SAMPLE

db2inst@linux11:~/sqllib/misc$ db2 -tf EXPLAIN.DDL 

******* IMPORTANT ********** 

USAGE: db2 -tf EXPLAIN.DDL  

******* IMPORTANT ********** 


DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

DB20000I The SQL command completed successfully.
 
db2inst@linux11:~/sqllib/misc$ db2 "explain all for select * from dev.test"
DB20000I The SQL command completed successfully.
 
数据库教程数据库管理
著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

赞0

添加新评论1 条评论

胆哥胆哥开发工程师新媒传信
2013-03-18 10:40
LZ好人，嘿嘿！
赞
回复
Ctrl+Enter 发表

作者其他文章

  • DB2的字符集指定与导入导出
    评论 0 · 赞 0
  • 【原创】MYSQL 的使用记录 & MyEclipse8.5调用
    评论 0 · 赞 0
  • 【原创】DB2 db2acd进程挂起现象与处理
    评论 6 · 赞 2
  • 【范例】DB2的锁等待与事务的分析与定位
    评论 2 · 赞 0
  • 【原创】CATALOG信息修改造成别名不能正常访问的问题
    评论 0 · 赞 0

    • 相关文章

  • 南大通用对数据库领域国家标准编制与实施的贡献浅析
  • Redis 的优势是如何在保单业务中发挥出来的
  • 数据库行业解决方案都写了啥
  • 企业传统Oracle数据库迁移国产数据库核心难点总结
  • 招聘：建信金科基础技术中心数据库产品团队

    • 相关问题

  • 引入缺乏第三方服务生态的信创数据库，会不会令甲方被厂商锁定而失去议价能力？
  • 对于业务系统的历史数据存放、查询，哪种类型的数据库更为合适？
  • 超融合能否扛起全闪做支撑交易类数据库最佳平台的大旗？
  • 金融行业容器数据库对比传统数据库的主要变化在哪里？或者是架构上哪一层发生了变化？
  • 针对不同国产数据库数据存储介质选型，是采用本地盘、SAN存储，还是NAS存储？

    • 相关资料

  • 分布式数据库金融应用发展报告-2023
  • IDC+中国金融行业国产分布式数据库白皮书
  • Redhat7.6 Oracle 19C RAC部署手册
  • Redhat7.0 Oracle 12C RAC部署手册
  • Redhat7.5 Oracle 18C RAC部署手册

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广