DB2内部培训.ppt

文档介绍：DB2内部培训
DB2逻辑组成图
DB2基本概念
实例:Instance
数据库:Database
用户\模式: Schema
表空间:DMS SMS
表:Table
DB2安装与配置
Win系统安装
实例目录:DB2
安装目录$:\Program Files\IBM\SQLLIB
运行:db2cmd
建库:create db dbname;drop db dbname
建表空间:
建表:
DB2安装与配置
Linux\AIX系统安装
安装DB2程序
建用户:实例用户和管理器用户
建实例:db2inst1
建库:create db dbname
建表空间:
建表:
DB2管理类命令
Db2start ,db2stop, Db2stop force
Catalog
List db directory
Db2 list application (show detail)
Db2 force application all
Db2 get dbm cfg(update dbm cfg using XXX ???)
Db2 get db cfg(update db cfg using XXX ???)
DB2 ?SQLCODE, db2 ? sql0803
Db2 connect to Dbname ;db2 connect reset
DB2数据库对象
CREATE 语句用于创建数据库对象,包括:
缓冲池(Buffer pool)
事件监控程序(Event monitor)
函数(Function)
索引(Index)
模式(Schema)
存储过程(Stored procedure)
表(Table)
表空间(Table space)
触发器(Trigger)
视图(View)
DB2数据维护
Export to fliename of del select * from tabname
Import from filename of del insert into tabname
import from filename of del|ixf replace into tabname
import from filename of del|mitcount 1000 replace into tabname
Load from filename of del insert into tabname nonrecoverable
Db2move dbname export
Db2move dbname import
Backup: backup db dbname
Restore: restore db dbname
DB2SQL
列函数:rtrim,length,substr。。。。
Join:
selet , from tab a,tab b where =;
select , from tab a left join tab b on =
聚合:sum,min,max,avg,count(*)
Select a, sum(fse) from tab group by a having sum(fse)>1000
Select distinct col from tab
字符串匹配 like ‘qwe%’
范围查找between and
空值查询 col is null
查找一组值 col in (1,2);
Select ,update, delete, insert
DB2SQL
子查询
Select * from tabA where col1 in (select col from tabB);
case 表达式
Select case
When score < 65 then ‘not passed’
When score<=90 then ‘passed’
Else ‘excellent’
End
From tabA