目录
一 安装数据库服务 （环境要求）
1.1 创建实例
db2icrt <实例名称>
set db2intance=db2（设置当前实例）
1.2 删除实例
db2idrop <实例名称>
1.3 更新实例
db2iupdt instance_name
1.4 查看实例
db2ilist
select * from sysibm.sysversions 查看db2版本
二 数据库 （存放位置）
2.1 创建数据库
db2 "CREATE DB TestDB using codeset GBK territory us"
db2 "UPDATE DB CFG FOR TestDB USING applheapsz 4096"
db2 "UPDATE DB CFG FOR TestDB USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR TestDB USING stmtheap 8192"
db2 "UPDATE DB CFG FOR TestDB USING dbheap 2400"
db2 "UPDATE DB CFG FOR TestDB USING locklist 1000"
db2 "UPDATE DB CFG FOR TestDB USING logfilsiz 1000"
db2 "UPDATE DB CFG FOR TestDB USING logprimary 12"
db2 "UPDATE DB CFG FOR TestDB USING logsecond 20"
db2 "UPDATE DB CFG FOR TestDB USING logbufsz 32"
db2 "UPDATE DB CFG FOR TestDB USING avg_appls 5"
db2 "UPDATE DB CFG FOR TestDB USING locktimeout 30"
2.2 修改数据库
grant dbadm on database to user bb
restart database database_name （重启数据库）
activate database database_name（激活数据库）
deactivate database database_name（停止数据库）
2.3 删除数据库
Drop database fxcjh
2.4 备份数据库
db2 force application all
db2 backup db fxcjh to d: （只能备份到数据库所在地 ）
备份成功。此备份映像的时间戳记是：20130910200554
2.5 还原数据库
db2 restore db o_yd from d: to d:
2.6 查看数据库
db2 list database directory on F:
目录中的条目数 = 3
数据库 1 条目：
数据库别名 = TOOLSDB
数据库名称 = TO
数据库目录 = SQL00001
数据库发行版级别 = d.00
注释 =
目录条目类型 = 本地
目录数据库分区号 = 0
数据库分区号 = 0
数据库 2 条目：
数据库别名 = FXCJH
数据库名称 = FX
数据库目录 = SQL00003
数据库发行版级别 = d.00
注释 =
目录条目类型 = 本地
目录数据库分区号 = 0
数据库分区号 = 0
数据库 3 条目：
数据库别名 = SAMPLE
数据库名称 = SA
数据库目录 = SQL00002
数据库发行版级别 = d.00
注释 =
目录条目类型 = 本地
目录数据库分区号 = 0
数据库分区号 = 0
SYSCAT.COLUMNS：包含每一行对应于表或视图中定义的列
SYSCAT.INDEXCOLUSE：包含每一行包含的所有列
SYSCAT.INDEXES：包含每一行对应于表或视图中定义的每个索引
SYSCAT.TABLES：所创建每个表，视图，别名对应其中一行
SYSCAT.VIEWS：所创建每个视图对应其中一行或几行
2.7 连接数据库
db2 connect to fxcjh
connect reset 重连数据库
disconnect fxcjh 中断连接
三 缓冲池
缓冲池是从磁盘读取表和索引数据时，数据库管理器分配的用于高速缓存这些表或索引数据的主存储器区域。每个DB2数据库都必须具有一个缓冲池每个新数据库都定义了一个称为IBMDEFAULTBP的缺省缓冲池。可以通过SYSCAT.BUFFERPOOLS目录视图访问数据库中所定义的缓冲池信息。首次访问表中的数据行时，数据库管理器会将包含该数据的页放入缓冲池中。这些页一直保留在缓冲池中，直到关闭数据库或者其他页需要使用某一页所占用的空间为止
3.1 创建
CREATE BUFFERPOOL "BP04K_I1"
IMMEDIATE
ALL DBPARTITIONNUMS
SIZE 250
NUMBLOCKPAGES 0
PAGESIZE 32 K;
3.2 修改
ALTER BUFFERPOOL <bp_name> SIZE <number_of_pages>
alter bufferpool IBMDEFAULTBP immediate size 100
3.3 删除
删除缓冲池时，应确保没有任何表空间已指定给这些缓冲池。不能删除IBMDEFAULTBP缓冲池。
DROP BUFFERPOOL <buffer pool name>
3.4 查看缓冲池
select * from SYSCAT.BUFFERPOOLS;
四 表空间
表空间是一种存储结构，它包含表，索引，大对象和长型数据，表空间位于数据库分区组中。空间是数据库及存储在该数据库中的表之间的逻辑层。表空间在数据库中创建，表在表空间中创建。 使用表空间的一个明显的好处是能够把数据合理的分布存储在不同的磁盘上或者存储在磁盘的不同位置上，有助于提高数据存取的效率。 DB2 的表空间按管理方式分为两种：系统管理空间（System Management Space，SMS）和数据库管理空间（Database Management Space，DMS）。 按类型分为：规则表空间、大对象表空间、系统临时表空间、用户临时表空间。系统临时表空间用来存储各种数据操作（排序、重组表、创建索引、连接表）中所需的内部临时数据，虽然可以创建任意多个系统临时表空间，但建议用户只使用大多数表所使用的页大小创建一个，默认系统临时表空间名为TEMPSPACE1。
用户临时表空间用来存储已说明全局临时表（已说明全局临时表存储的是应用程序临时数据）。用户临时表空间不是在数据库创建时默认创建的
4.1 创建表空间
CREATE REGULAR TABLESPACE "TSIN01"
IN DATABASE PARTITION GROUP "IBMDEFAULTGROUP"
PAGESIZE 32K
MANAGED BY DATABASE
USING
(File 'H:\DB2\tablespace\TSIN01' 56320
)
EXTENTSIZE 32
PREFETCHSIZE 32
BUFFERPOOL "BP04K_I1"
OVERHEAD 10.50
TRANSFERRATE 0.14
DROPPED TABLE RECOVERY ON;
4.2 修改表空间
改变容器大小alert tablespace idx_data resize(file 'd:\db2\datafile\idx_data' 3200)
新增新的容器alert tablespace idx_data add(file 'd:\db2\datafile\idx_data' 3200)
4.3 删除表空间
DROP TABLESPACE TSIN01
运行完这条命令，相应的表空间会从DB2注册表中删除，但是磁盘上的三个文件夹仍然存在，需要手工删除
4.4 表空间故障
表空间占满故障现象表空间占满故障现象表空间占满故障现象表空间占满故障现象 1、 系统上线初期正常，稳定运行半年以后突然出现故障。
2、 对数据库进行写入操作时失败：用户的流程计划不能正常提交，录 入数据无法保存。 3、 系统应用程序操作响应慢，甚至宕机（死机）。
4、 DB2数据库能正常连接，select语句执行正常，insert语句执行失败。
4.5 查看表空间
db2 list tablespaces show detail
SELECT * FROM SYSIBM.SYSTABLESPACES WHERE TBSPACE='表空间名称'
4.6 备份表空间
BACKUP DATABASE YNDC TABLESPACE ( USERSPACE1 )
TO "D:\temp" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
五 表
关系数据库 将数据表示成表的集合
5.1 创建表
(1) Create table t00_user(
userId varchar(12),
Username varchar(24)
)compress yes --打开表压缩 （大表）
IN TSDAT01
INDEX IN TSIN01
(2) CREATE TABLE YK_TRXP_TMP LIKE YK_TRXP IN TSODSDAT;
alter table tableName compress yes 修改表压缩为打开 要及时维护表
reorg table tableName resetdictionary （全部重建）
reorg table tableName keepdictionary （追加压缩）
5.2 修改表
5.2.1 增加字段
alter table 表名 add 字段名 数据类型 default 默认值
alter table test ADD COLUMN DEPTNO varchar(8);
alter table table_name add column column_name data type varchar(250)
5.2.2 删除字段
ALTER table 表名 DROP column 字段名
ALTER table test DROP column userId
5.2.3 修改字段类型
alter table news alter AUTHOR set data type varchar(250)
5.2.4 对现存列改名
ALTER TABLE distributors RENAME COLUMN address TO city;
5.2.5 对现存表改名
ALTER TABLE distributors RENAME TO suppliers;
rename table_old to table_new
RENAME TABLE "ODS"."S48_CMS_JK_DKZH_LI72" TO S48_CMS_JK_DKZH;
1：不允许修改字段的名称（只能先删除，再添加）。
2：不允许减小字段的长度。
3：不允许修改字段类型（如把 Integer 修改成 varchar）。
5.3 删除表
alter table activate not logged initially with empty table 快速清理表数据不记日志 最快
delete from test,但这种做法，效率比较低，花费时间太长，因为在删除数据时，要记数据库日志。
import from /dev/null of del replace into test//先清空，再导入数据(由于导入的文件为空，故相当于清空表数据)，这种删除的速度较快
Drop table tablename
会删除所有的列名
会删除基于该表的任何列创建的索引
将基于该表的所有视图标记为不可用
删除的表和从属视图的所有特权被隐式撤销
会删除在其中为该表父表或从属表的所有引用约束
5.4 查看表信息
describe table fxcjh.t00_user
| list tables | 列出用于当前用户的表 |
| list tables for all | 列出数据库中定义的所有表 |
| list tables for schema schemaname | 列出指定模式中的表 |
describe table fxcjh.t00_user
select * from SYSCAT.tables where tabschema='FXCJH';（查看数据库中的表）
SELECT * FROM SYSCAT.CHECKS WHERE TABNAME = <tb_name> （查看表约束）
SELECT * FROM SYSCAT.REFERENCES WHERE TABNAME = <tb_name> （查看表的引用完整性）
db2 list history dropped table all for fxcjh （查看被删表的信息）
5.5 导出表结构
db2look -d <数据库名> -u <用户> -e -o <脚本名称>.sql --导出数据库的表结构，
db2look -d fxcjh -u fxcjh -e -o D:\fxcjh.sql (没有导出)
db2look -d fxcjh -e -a -x -i fxcjh -w fxcjh -o /ETLDATA/lishc/fxcjh1.sql（可以导出）
5.6 导出表数据
export to [path(例:D:"TABLE1.ixf)] of ixf select [字段(例: * or col1,col2,col3)] from TABLE1;
db2 export to D:\t00_user.ixf of ixf select * from fxcjh.t00_user
5.7 导入表数据
db2 grant load on database to user fxcjh
db2 grant dbadm on database to user tst1
import from [path(例:D:"TABLE1.ixf)] of ixf insert into TABLE1;
import from d:\t00_user.ixf of ixf insert into fxcjh.t00_user（不能导入）
load from [path(例:D:"TABLE1.ixf)] of ixf insert into TABLE1;
load from d:\t00_user.ixf of ixf insert into fxcjh.t00_user(可以导入)
load from [path(例:D:"TABLE1.ixf)] of ixf replace into TABLE1; // 装入数据前，先删除已存在记录
load from d:\t00_user.ixf of ixf replace into fxcjh.t00_user（可以导入）
load from [path(例:D:"TABLE1.ixf)] of ixf restart into TABLE1; // 当装入失败时，重新执行，并记录导出结果和错误信息
load from d:\t00_user.ixf of ixf restart into fxcjh.t00_user
2N 在不处于 LOAD PENDING 状态的表上，不允许 LOAD RESTART/TERMINATE。（不行）
存在自增长字段的数据导入：
load from [path(例:D:"TABLE1.ixf)] of ixf modified by identityignore insert into TABLE1;// 加入modified by identityignore.
load from d:\t00_user.ixf of ixf modified by identitygnore insert into fxcjh.t00_user(无法执行)
5.8 表维护
值得注意的是，针对数据库对象的大量操作，如反复地删除表，存储过程，会引起系统表中数据的频繁改变，在这种情况下，也要考虑对系统表进行REORG操作。一个完整的REORG表的过程应该是由下面的步骤组成的：
RUNSTATS -> REORGCHK -> REORG -> RUNSTATS -> BIND或REBIND
1) 针对系统表进行REORGCHK
db2 reorgchk update statistics on table system
使用UPDATE STATISTICS参数指定数据库首先执行RUNSTATS命令。
2) 针对用户表进行REORGCHK
db2 reorgchk update statistics on table user
3）REORG TABLE FXCJH.T48_RCS_GUARANTY_INFO;
db2 reorg table 表名称 index 索引名称 use tempspace1 （先做）
4）runstats on table FXCJH.T48_RCS_GUARANTY_INFO with distribution on all columns and detailed indexes all allow write access; （后做）
六 索引
DB2索引实现是一个B+树，通过索引可以实现快速查询，避免全表扫描以此来减少IO操作。索引是对表数据的一种抽象，通过抽取有限数据，对数据的分布进行计算，以此来完成对数据的快速检索。
索引应该用来提高查询速度，但是会对更新和删除操作带来负面影响，因为要同步更新索引。所以索引应该创建到更新、删除相对比读取少的表上。创建索引的目的还有一个就是保证数据唯一性，可以利用”CREATE UNIQUE INDEX <INDEX_NAME> ON <TABLENAME> (<COLNAME>)”，来完成。
主键会隐式创建索引，所以请不要在主键上创建索引浪费空间。创建唯一索引可以避免排序。因为索引是有序数据结构，在进行扫描时，DB2会默认按照顺序输出结果，而不是按照插入先后。通过创建唯一索引可以避免排序，提高查询性能。
避免在索引列上使用IS NULL和IS NOT NULL。避免在索引中使用任何可以为空的列，数据库将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录；对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中。
聚集索引：在每个表上，可以将一个索引创建为聚集索引。如果常常以某一次序引用表数据，那么聚集索引比较有用。聚集索引（clustering index） 定义数据在数据库中存储的次序。在插入期间，DB2 会试图将新的行放置得靠近有相似键的行。这样的话，在查询以聚集索引序列请求数据期间，可以更快地检索数据。
在索引中使用包含的列：在创建索引时，可以选择包含额外的列数据，这些额外的列数据将与键存储在一起，但实际上它们不是键本身的一部分，所以不被排序。在索引中包含额外列的主要原因是为了提高某些查询的性能：因为索引页面中已经提供了数据值，DB2 就不需要访问数据页面。只能为惟一索引定义包含的列。但是，在强制实施索引的惟一性时不考虑包含的列。那么，为什么不干脆在索引中包括所有的数据？首先，这需要占用数据库中更多的物理空间，因为本质上表数据与索引中的数据是重复的。其次，每当更新数据值时，数据的所有拷贝都需要更新，在发生许多更新的数据库中，这是一项很大的开销。
6.1 新增索引
create index 索引名称 on 表名称 (
HTH（表字段） ASC
);
创建双向索引 CREATE INDEX Idx_3 ON TableName(C1) ALLOW REVERSE SCANS
聚集索引 CREATE INDEX IAUTHBKNAME ON BOOKS (AUTHORID,BOOKNAME) CLUSTER
在索引中使用包含的列 CREATE UNIQUE INDEX IBOOKID ON BOOKS (BOOKID) INCLUDE(BOOKNAME)
6.2 修改索引
alter index idx_col2 compress yes 开启索引压缩 （要进行表维护）
6.3 删除索引
drop index FXCJH.I_T48_CMS_QK_JTKHGX_HTH;
6.4 维护索引
SELECT * FROM SYSCAT.INDEXES WHERE INDNAME='索引名称'
db2 describe indexes for table fxcjh.t00_user ; （查看表索引）
七 序列
7.1 新增序列
CREATE SEQUENCE FXCJH.SEQ_CREDIT_PROJECT_00 AS INTEGER MINVALUE 1 MAXVALUE 999999999 START WITH 1120 INCREMENT BY 1 CACHE 20 NO CYCLE NO ORDER;
7.2 修改序列
修改最大值：ALTER SEQUENCE <sequence_name> MAX VALUE <numeric-constant> | NO MAXVALUE
修改最小值： ALTER SEQUENCE <sequence_name> MIN VALUE <numeric-constant> | NO MINVALUE （此值需要比当前值小）
修改步长： ALTER SEQUENCE <sequence_name> INCREMENT BY <numeric-constant>;
修改CACHE值： ALTER SEQUENCE <sequence_name> CACHE <numeric-constant> | NO CACHE
修改循环属性: ALTER SEQUENCE <sequence_name> <CYCLE | NO CYCLE>
修改排序属性：ALTER SEQUENCE <sequence_name> <ORDER | NO ORDER>
从新计数： ALTER SEQUENCE <sequence_name> RESTART | RESTART WITH <numeric-constant>
7.3 删除序列
drop SEQUENCE 序列名称 ;
7.4 维护序列
八 存储过程
将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的DB2存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。
DB2存储过程的优点：
1.DB2存储过程只在创造时进行编译，以后每次执行DB2存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用DB2存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用DB2存储过程封装起来与数据库提供的事务处理结合一起使用。
3.DB2存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某此用户才具有对指定DB2存储过程的使用权。
8.1 创建存储过程
CREATE PROCEDURE fxcjh.p_log_test (
in log_type varchar(10),
in log_name varchar(200),
in log_desc varchar(1000)
)
LANGUAGE SQL
SPECIFIC proc_with_vars
BEGIN
DECLARE v_startTime CHARACTER(20);
DECLARE v_log_id int;
/* 获取当前时间*/
select current date||' '||current time into v_startTime from sysibm.sysdummy1;
select max(log_id)+1 into v_log_id from fxcjh.t_log_test;
if v_log_id is null
then set v_log_id=1;
end if;
INSERT INTO FXCJH.T_LOG_TEST(log_id,log_type,log_name,log_time,log_desc) values (v_log_id,log_type,log_name,v_startTime,log_desc);
END
注：COALESCE(v_log_id,1) 返回第一个不为null的参数
如果v_log_id 为null 返回 1
如果v_log_id 不为null 返回 v_log_id的值
Call fxcjh.p_log_test(‘’,’’,’’)
8.2 修改存储过程
8.3 删除存储过程
Drop PROCEDURE fxcjh.p_log_test;
8.4 查询存储过程
SELECT bname,pkgname, BSCHEMA FROM syscat.packagedep WHERE btype='T' AND pkgname in(select bname from sysibm.sysdependencies where dname in (select specificname from syscat.procedures where procname='p_log_test' AND PROCSCHEMA='fxcjh'))
SELECT * FROM SYSCAT.PROCEDURES; (查看所有的存储过程)
8.5 维护存储过程
8.6 常用系统存储过程
九 函数
UDF 鼓励代码重用，这样可以提高应用程序开发人员的生产率。如果您有很多实现相同逻辑的程序，那么可以通过 UDF 标准化该逻辑，并让所有这些程序使用相同的实现。一旦在 DB2 中定义好一个 UDF，便可以像使用内置 DB2 函数那样，在 SQL 语句中使用这个 UDF
9.1 创建函数
db2 grant dbadm on database to user db2admin
create function ADD(a int ,b int)
returns int
LANGUAGE SQL
BEGIN ATOMIC
DECLARE sum int default 0;
set sum = a + b;
return sum;
END
VALUES (ADD(1, 2));(总是调用失败)
9.2 删除函数
DROP FUNCTION FUNC_CAN_MANAGE;
9.3 查看函数
db2 select body from syscat.functions where funcname = 'add'
9.4 维护函数
9.5 常用系统函数
函数名 函数解释 函数举例
AVG() 返回一组数值的平均值.SELECT AVG(SALARY) FROM BSEMPMS;
CORR(),CORRELATION() 返回一对数值的关系系数.
SELECT ORRELATION(SALARY,BONUS) FROM BSEMPMS;
COUNT() 返回一组行或值的个数.SELECT COUNT(*) FROM BSEMPMS;
COVAR(),COVARIANCE() 返回一对数值的协方差.
SELECT COVAR(SALARY,BONUS) FROM BSEMPMS;
MAX() 返回一组数值中的最大值.SELECTMAX(SALARY)FROMBSEMPMS;
MIN() 返回一组数值中的最小值.SELECTMIN(SALARY)FROMBSEMPMS;
STDDEV() 返回一组数值的标准偏差.SELECTSTDDEV(SALARY)FROMBSEMPMS;
SUM() 返回一组数据的和.SELECTSUM(SALARY)FROMBSEMPMS;
VAR(),VARIANCE() 返回一组数值的方差.SELECTVARIANCE(SALARY)FROMBSEMPMS;
ABS(),ABSVAL() 返回参数的绝对值.SELECTABS(-3.4)FROMBSEMPMS;
ACOS() 返回参数的反余弦值.SELECTACOS(0.9)FROMBSEMPMS;
ASCII() 返回整数参数最左边的字符的ASCII码.SELECTASCII('R')FROMBSEMPMS;
ASIN() 返回用弧度表示的角度的参数的反正弦函数.SELECTASIN(0.9)FROMBSEMPMS;
ATAN() 返回参数的反正切值,该参数用弧度表示的角度的参数.SELECT ATAN(0.9)FROMBSEMPMS;
ATAN2() 返回用弧度表示的角度的X和Y坐标的反正切值.SELECT ATAN2(0.5,0.9)FROMBSEMPMS;
BIGINT() 返回整型常量中的数字或字符串的64位整数表示.SELECT BIGINT(EMP_NO)FROMBSEMPMS;
CEILING() OR CEIL() 返回比参数大或等于参数的最小的整数值.SELECTCEILING(3.56)FROMBSEMPMS;SELECTCEIL(4.67)FROMBSEMPMS;
CHAR() 返回日期时间型,字符串,整数,十进制或双精度浮点数的字符串表示.SELECT CHAR(SALARY,',')FROMBSEMPMS;
CHR() 返回具有由参数指定的ASCII码的字符.SELECT CHAR(167)FROMBSEMPMS;
CONCAT() 返回两个字符串的连接.SELECT CONCAT(EMP_NO,EMP_NAM)FROMBSEMPMS;
YEAR() 返回数值的年部分.SELECT YEAR('2003/01/02')FROMBSEMPMS;
VARCHAR() 返回字符串,日期型,图形串的可变长度的字符串表示.SELECT VARCHAR(EMP_NAM,50)FROMBSEMPMS;
UCASE() OR UPPER() 返回字符串的大写.SELECT UCASE(EMP_NAM)FROMBSEMPMS;SELECTUPPER(EMP_NO)FROMBSEMPMS;
TRUNCATE() OR TRUNC() 从表达式小数点右边的位置开始截断并返回该数值.SELECT TRUNCATE(345.6789,2)FROMBSEMPMS;
TIME() 返回一个数值中的时间.SELECT TIME('2001-03-19.12.30.123456')FROMBSEMPMS;
SUBSTR(EXP1,EXP2) 返回EXP1串自EXP2处开始的子串.SELECT SUBSTR('CDNJFDJFJD',5)FROM BSEMPMS;SELECT SUBSTR('CDNJFDJFJD',5,2)FROM BSEMPMS;
SQRT() 返回该参数的平方根.SELECT SQRT(36)FROMBSEMPMS;
SPACE() 返回由参数指定的长度,包含空格在内的字符串.SELECT SPACE(10)FROMBSEMPMS;
SECOND() 返回一个数值的秒部分.SELECT SECOND('18:34:32')FROMBSEMPMS;
RTRIM() 删除字符串尾部的空格.SELECT RTRIM('COMMENT')FROMBSEMPMS;
ROUND(EXP1,EXP2) 返回EXP1小数点右边的第EXP2位置处开始的四舍五入值.SELECTROUND(2345.6789,2)FROMBSEMPMS
REPLACE(EXP1,EXP2,EXP3) 用EXP3替代EXP1中所有的EXP2SELECTCHAR(REPLACE('ROMANDD','NDD','CCB'),10)FROMBSEMPMS;
REPEAT(EXP1,EXP2) 返回EXP1重复EXP2次后的字符串.SELECTCHAR(REPEAT('REPEAT',3),21)FROMBSEMPMS;
REAL() 返回一个数值的单精度浮点数表示.SELECTREAL(10)FROMBSEMPMS;
RAND() 返回0和1之间的随机浮点数.SELECTRAND()FROMBSEMPMS;
POWER(EXP1,EXP2) 返回EXP1的EXP2次幂.SELECTPOWER(2,5)FROMBSEMPMS;
POSSTR(EXP1,EXP2) 返回EXP2在EXP1中的位置.SELECT('ABCDEFGH','D')FROMBSEMPMS;
NULLIF(EXP1,EXP2) 如果EXP1=EXP2,则为NULL,否则为EXP1
NODENUMBER() 返回行的分区号.SELECTNODENUMBER(EMP_NO)FROMBSEMPMS;
MONTH() 返回一个数值的月部分.SELECTMONTH('2003/10/20')FROMBSEMPMS;
MOD(EXP1,EXP2) 返回EXP1除以EXP2的余数.SELECTMOD(20,8)FROMBSEMPMS;
MINUTE() 返回一个数值的分钟部分.SELECTMINUTE('18:34:23')FROMBSEMPMS;
LTRIM() 删除字符串前面的空格.SELECTLTRIM('CDDD')FROMBSEMPMS;
HOUR() 返回一个数值的小时部分.SELECTHOUR('18:34:23')FROMBSEMPMS;
DOUBLE() 如果参数是一个数字表达式,返回与其相对应的浮点数,如果参数是字符串表达式,则返回该数的字符串表达式.SELECTDOUBLE('5678')FROMBSEMPMS;
EXP() 返回参数的指数函数.SELECTEXP(2)FROMBSEMPMS;
FLOAT() 返回一个数的浮点表示.SELECTFLOAT(789)FROMBSEMPMS;
FLOOR() 返回小于或等于参数的最大整数.SLECTFLOOR(88.93)FROMBSEMPMS;
HEX() 返回一个表示为字符串的值的16进制表示.SELECTHEX(16)FROMBSEMPMS;
CAST()
十 视图
视图就是允许不同的用户或应用程序以不同的方式查看相同的数据。这不仅使得数据更容易访问，还可以用它来限制用户可以查看或更新哪些行和列。 在创建一个视图时，可以将它定义为只读视图 或者可更新视图。视图的 SELECT 语句决定视图是只读的还是可更新的。视图（View）是一个虚表，它本身不存储数据，而是架设在其他表或者视图上来查看数据。视图就好比是一个放大镜，当定义好这个放大镜的范围之后，就可以通过这个放大镜查看其他表中的相关数据。能够查看哪些数据，以什么样的方式查看数据，都是在创建视图的时候决定的。合理使用视图能够带来很多优势，主要包括：
（1）视图能够简化用户的操作；
（2）视图使用户能够以多种角度看待同一数据；
（3）视图对重构数据库提供了一定程度的逻辑独立性；
（4）视图能够对机密数据提供安全保护。
10.1 创建
create view 视图名称 as select 字段1,字段2 from 表名;
10.2 修改
不能修改视图；要更改视图定义，必须删除视图，然后重新创建它。 DB2 提
供的 ALTER VIEW 语句只用于修改引用类型。
UPDATE VIEW_NAME SET SNAME=’张三’ WHERE SID=’1’;
10.3 删除
DROP VIEW 视图名称。
10.4 查看
Select * from SYSCAT.VIEWS where VIEWSCHEMA='FXCJH';(查询这个模式下视图)
select * from SYSCAT.VIEWDEP where VIEWSCHEMA='FXCJH' and VIEWNAME='MYVIEW';（查询这个视图依赖哪些表）
select * from SYSIBM.SYSVIEWS where NAME='MYVIEW';（查询视图的其它约束）
十一 模式
十二 容器
十三 包
检查未绑定的包
db2 "SELECT DISTINCT 'db2 rebind package FXCJH.'||pkgname||' resolve any;' FROM syscat.packages WHERE pkgschema = 'FXCJH' and valid != 'Y' with ur"
十四 日志
十五 配置
15.1 查看配置
查看实例配置参数: db2 get dbm cfg
查看数据库配置参数: db2 get db cfg for <db_name>
注意: 数据库配置参数buffpage仅对缓冲区大小设置为 -1 的缓冲池起作用。
15.2 修改配置
修改实例配置参数: db2 update dbm cfg using 参数名 新值
修改数据库配置参数: db2 update db cfg for <db_name> using 参数名 新值
十六 触发器
十七 锁
17.1 查看锁
get snapshot for locks on fxcjh （获得数据锁的快照）
17.2 解除锁
force application(application handle) 注：application handle对应的是一个整数
17.3 分析锁
十七 常用命令
查看执行计划记日志
db2expln -d fxcjh -f "check_00.sql" -t -g -nostats > check_00_sql.log;
check_00.sql 是要查询的sql语句
check_00_sql.log 生成日志文件
更新多条使用 ROW_NUMBER() OVER()
update (
SELECT
TT.*,
ROW_NUMBER() OVER() AS RN
FROM
TRANSACTION AS TT order by amount
)
set seq=rn
清空表不记日志
ALTER TABLE TRANSACTION ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
如何让DB2 order by 的时候区分字符串大小写
create table test_one (col varchar(10))
insert into test_one values ('a'), ('B'), ('b'), ('A')
SELECT COL
FROM test_one
ORDER BY (case
when upper(col) = col then
ASCII(COL) + 1000
else
ASCII(COL)
end)
db2 get monitor switches （获得 监控信息）
监视器记录开关
数据库分区号 0 的开关列表
缓冲池活动信息 (BUFFERPOOL) = OFF
锁定信息 (LOCK) = OFF
排序信息 (SORT) = OFF
SQL 语句信息 (STATEMENT) = OFF
表活动信息 (TABLE) = OFF
获取时间戳记信息（时间戳记） = ON 2013-09-10 19:53:59.890016
工作单元信息 (UOW) = OFF
db2 update monitor switches using table off （修改监控开关）
一 安装数据库
\SQLLIB安装的根目录,包括README文件
\SQLLIB\ADSM包含ADSTAR分布式存储管理器文件
\SQLLIB\BIN包含DB2工具的可执行文件
\SQLLIB\BND包含DB2工具的绑定文件
\SQLLIB\CC包含运行控制中心所需的文件
\SQLLIB\CFG包含默认的系统配置文件
\SQLLIB\CONV包含代码页转换表文件
\SQLLIB\DB2默认的实例目录
\SQLLIB\DB2DAS00缺省的DB2管理服务器目录
\SQLLIB\DOC包含DB2联机手册
\SQLLIB\FUNCTION默认的用户自定义函数目录
\SQLLIB\FUNCTION\UNFENCED默认的非隔离用户自定义函授目录
\SQLLIB\HELP联机帮助文件
\SQLLIB\JAVADB2所需的JAVA类库
JAVA12包含JDK1.2的支持程序
\SQLLIB\MISC包含HTML搜索服务器文件
\SQLLIB\MSG\PRIME包含信息文件
\SQLLIB\QP包含QUERYPATROLLER的客户端文件
\SQLLIB\SAMPLES包含样例程序和样例脚本
\SQLLIB\SPMLOG包含DB2同步点管理器日志文件
\SQLLIB\THNSETUP包含瘦客户端安装文件
9.UNIX和LINUX环境下的DB2安装目录结构?
用DB2SETUP.EXE来安装
安装的根目录下还将创建以下目录:
README安装的根目录,包括README文件
ADM包含系统管理工具文件
ADSM包含ADSTAR分布式存储管理器文件
BIN包含DB2工具的二进制可执行文件
BND包含DB2工具的绑定文件
CC包含运行控制中心所需的文件
CFG包含默认的系统配置文件
CONV包含代码页转换表文件
DOC包含DB2联机手册
FUNCTION默认的用户自定义函数目录
FUNCTION\UNFENCED默认的非隔离用ё远ㄒ搴谀柯?lt;BR> INSTALL包含安装程序
INSTANCE包含实例脚本
JAVADB2所需的JAVA类库
LIBDB2库文件
MAP包含DB2CONNECT使用的映射文件
MISC包含HTML搜索服务器文件
SAMPLES包含样例程序和样例脚本
MSG\$L包含DB2信息文件
第七章 存储过程
7.1 日志表的建立
/*
记录日志表
1 日志编号
2 日志类型
3 日志名称
4 日志时间
*/
drop table fxcjh.t_log_test;
create table fxcjh.t_log_test(
Log_id INTEGER, --日志id
log_type varchar(10), --日志类型
log_name varchar(200), --任务名称
log_time CHARACTER(20), --执行时间
log_desc varchar(1000) --日志描述
)
7.2 存储过程的建立
/*
插入日志的存储过程
*/
drop procedure fxcjh.p_log_test;
CREATE PROCEDURE fxcjh.p_log_test (
in log_type varchar(10),
in log_name varchar(200),
in log_desc varchar(1000)
)
LANGUAGE SQL
SPECIFIC proc_with_vars
BEGIN
DECLARE v_startTime CHARACTER(20);
DECLARE v_log_id int;
/* 获取当前时间*/
select current date||' '||current time into v_startTime from sysibm.sysdummy1;
select max(log_id)+1 into v_log_id from fxcjh.t_log_test;
if v_log_id is null
then set v_log_id=1;
end if;
INSERT INTO FXCJH.T_LOG_TEST(log_id,log_type,log_name,log_time,log_desc) values (v_log_id,log_type,log_name,v_startTime,log_desc);
END
注：COALESCE(v_log_id,1) 返回第一个不为null的参数
如果v_log_id 为null 返回 1
如果v_log_id 不为null 返回 v_log_id的值
存储过程的调用
call fxcjh.p_log_test('p','ptest2',V_KHBH||' 描述');
八 常用命令
一、基础篇
select current date||' '||current time from sysibm.sysdummy1;
1、db2 connect to <数据库名> --连接到本地数据库名
db2 connect to <数据库名> user <用户名> using <密码> --连接到远端数据库
2、 db2 force application all --强迫所有应用断开数据库连接
3、db2 backup db db2name<数据库名称> --备份整个数据库数据
db2 restore db <db2name> --还原数据库
4、db2 list application --查看所有连接（需要连接到具体数据库才能查看）
5、db2start --启动数据库
db2stop --停止数据库
6、create database <数据库名> using codeset utf-8 territory CN --创建数据库使用utf-8编码
7、db2 catalog 命令
db2 catalog tcpip node <接点名称> remote <远程数据库地址> server <端口号> --把远程数据库映射到本地接点一般为50000
db2 catalog db <远程数据库名称> as <接点名称> at node PUB11 --远程数据库名称到本地接点
db2 CONNECT TO <接点名称> user <用户名> using <密码> --连接本地接点访问远程数据库
8、数据库导出
db2look -d <数据库名> -u <用户> -e -o <脚本名称>.sql --导出数据库的表结构，其中用户空间一般为db2admin/db2inst1
db2look -d <数据库名> -u <用户> -t <表1> <表2> -e -o <脚本名称>.sql --导出数据库中表1和表2的表结构
db2move <数据库名> export --导出数据库数据
db2move <数据库名> export -tn <表1>,<表2> --导出数据库中表和表数据
9、数据库导入
db2 -tvf <脚本名称>.sql --把上述导出的表结构导入到数据库表结构
db2move <数据库名> load -lo replace --把上述“db2move <数据库名> export “导出的数据导入到数据库中并把相同的数据替换掉
在实际使用过程中，如果用到db2自增主键，需要使用by default， 而不是always，功能是一样的，但这样在数据移植时候会很方
便!
10、db2 connect reset 或 db2 terminate --断开与数据库的连接
11、db2set db2codepage=1208 --修改页编码为1208
12、db2 describe table <表名> --查看表结构
13、db2 list tables --查看数据库中所有表结构
list tables for system --列出所有系统表
14、db2 list tablespaces --列出表空间
二、高级篇
去除重复行
delete from (select * from (select a,b,c,row_number() over(partition by a,b,c order by a,b,c) as row_num from tab) as e where row_num >1)
从表导数据到另一张表
大数据量采用 LOAD FROM CURSUR
1 DECLARE mycursor CURSOR FOR SELECT distinct * FROM emp_profile;
2 LOAD FROM mycursor OF CURSOR INSERT INTO emp_profile_temp;
3 drop table emp_profile;
4 rename table emp_profile_temp to emp_profile
【编辑推荐】
values trim(translate('343.a.sa','', '0123456789.'))
15、fetch first 10 rows only --列出表中前10条数据
例如：select * from <表名> fetch first 10 rows only
16、coalesce(字段名，转换后的值) --对是null的字段进行值转换
例如：select coalesce(id,1) from <表名> --对表中id如果为null转换成1
17、dayofweek(日期) --计算出日期中是周几（1是周日，2是周一.......7是周六）
dayofweek_iso --计算出日期中是周几(1是周一.......7是周日)
例如：dayofweek(date(2008-01-16)) --它会返回是4，代表星期三
dayofweek_iso(date(2008-01-16)) --它会返回是3，代表星期三
18、dayofyear(日期) --一年中的第几天，范围在1-366范围之内
注意：参数中日期的格式是YYYY-MM-DD的形式，如果不是需要进行转换，否则函数不能使用
例如：日期是20080116必须要进行转换
dayofweek(concat(concat(concat(substr(openDate,1,4),'-'),concat(substr(openDate,5,2),'-')),substr(openDate,7,2))) as week)
这样格式正确的。
19、concatt(参数1，连接值) --把参数１加上连接值组成一个新值。
例如： concat('aa','b') --返回是aab
把oracle表导入到db2数据库中，有些字段类型需要进行转换，具体看：oracle与db2数据类型转换
启动DB2服务：db2start
关闭DB2服务: db2stop
db2 set schema btp 修改当前模式为"btp"
db2 list tablespaces show detail 查看当前数据库表空间分配状况
db2 list tablespace containers for 2 show detail 查看tablespace id=2使用容器所在目录
db2 list application
db2 list db directory 列出所有数据库
db2 list active databases 列出所有活动的数据库
db2 list tables for all 列出当前数据库下所有的表
db2 list tables for schema btp 列出当前数据库中schema为btp的表
db2 list tablespaces show detail 显示数据库空间使用情况
db2 list packages for all
db2 "import from tab76.ixf of ixf commitcount 5000 insert into achact"
db2 "create table achact_t like achact"
db2 "rename table achact_t to achact"
db2 "insert into achact_t select * from achact where txndt>=(select lstpgdt from
acmact where actno=achact.actno)"
db2 get snapshot for dynaimic sql on jining
删除一个实例：
# cd /usr/lpp/db2_07_01/instance
# ./db2idrop InstName
列出所有DB2实例：
# cd /usr/lpp/db2_07_01/bin
# ./db2ilist
为数据库建立编目
$ db2 catalog db btpdbs on /db2catalog
取消已编目的数据库btpdbs
$ db2 uncatalog db btpdbs
查看版本
# db2level
显示当前数据库管理实例
$ db2 get instance
设置实例系统启动时是否自动启动。
$ db2iauto -on 自动启动
$ db2iauto -off 不自动启动
数据库优化命令：
reorg、runstats
当数据库经过一段时间使用，数据空间会变得越来越庞大。一些delete掉
的数据仍存放在数据库中，占用数据空间，影响系统性能。因此需要定期
运行reorg、runstats命令，清除已delete的数据，优化数据结构。
db2 reorg table 表名
db2 runstats on table 表名 with distribution and indexes all
因为要优化的表比较多，所以在/btp/bin目录下提供了一个sh程序runsall，
可在当天业务结束后，运行runsall，对数据库进行优化
============================================================================================================================================================
DB2命令详解 收藏
在使用 DB2 RDBMS 时有几个重要的命令。有关这些命令和其它数据库命令的附加信息，请参阅 DB2 文档。这些命令用于以下操作：
启动一个 DB2 交互式会话
要启动一个交互式会话，请输入以下命令：
db2
编制 DB2 服务器节点目录
要编制 DB2 服务器节点目录，请输入以下命令：
db2 catalog tcpip node db2node remote hostname server service_name
其中 db2node 是安装了 DB2 客户机的系统名称（它在节点目录列表中必须是唯一的），hostname 是安装了 DB2 服务器的全限定系统名称，service_name 是在 services 文件中定义的连接端口名。 编制远程 DB2 数据库目录
要编制远程数据库目录，请输入以下命令：
db2 catalog database db_name as alias_name at node db2node
其中 db_name 是远程数据库的名称，alias_name 是客户机实例的名称，db2node 是安装了 DB2 客户机的系统名称。
连接到 DB2 服务器
要连接到 DB2 服务器，请输入以下命令：
db2 connect to database user name using password
其中 database 是系统数据库目录中的数据库名称或别名，name 是数据库实例所有者的用户名，password 是先前用户的密码。
运行脚本
要运行脚本，请输入以下命令：
db2 -f script_name -o -t -z log_name
其中 script_name 是脚本的名称，log_name 是日志文件的名称。
结束一个 DB2 会话
要注销一个交互式会话，请输入以下命令：
quit
values char(replace('ABCD','A','OPMN')) 返回的结果就是OPMNBCD
update act set actdesc=replace(actdesc,'D','W') where actno=70
这个函数的运用关键之处是区分大小写，字符串之间不能有任何的空格 否则是不能替换的。
create table b_student1 like b_student; 创建表的副本
select * from b_student fetch first 5 rows only; 用来处理分页技术
DB2中的like的使用是有限制的，它后面不能跟一个变量或者是字段，因此，在存储过程或SQL语句中就不能like一个变量或一个字
对于语句：
select A.a,B.b from A,B where A.d like B.d||'%'
在db2中应该修改为：
select A.a,B.b from A,B where locate(B.d,A.d)>0
Order
ORDER BY VALUE(A.DKZYE,0) DESC ,D.DKZH ASC'
SELECT
ROW_NUMBER() OVER(PARTITION BY KHBH ORDER BY SJTZBL DESC) SN,
J.*
FROM
T48_CMS_QK_GDZL J
三
load from fxcjhdata/T00_CAL_NOTES.ixf of ixf replace into FXCJH.T00_CAL_NOTES nonrecoverable ;
rtrim(char(字段))
cast colname as char(10)
select cast('123' as integer) from sysibm.sysdummy1;
select cast(123.0 as int) from sysibm.sysdummy1;
count(1)中的1是一个常量，如果你喜欢，也可以用count(2),count('hello') 。
sum求和也一样，但和count计数不同，sum参数只能是数值。数值常量情况下，结果相当于count(1)乘上数值，即：
sum(1)=count(1)=count(*)
sum(2)=coun(1)*2
想要实现输出一些连续数字方面，如何用一个sql实现？
with t(id) as
(
select 1 as id from sysibm.sysdummy1 a
union all
select id+1 as id from t,sysibm.sysdummy1 a where id<=100
)
select id from t
ETL全称是数据的抽取(Extract)、转换(TansForm)和装载(Load)，俗称“数据整合”、“数据集成”、“数据汇总”等
SELECT * FROM FINAL TABLE
(
UPDATE t0_USER SET SALARY=SALARY*(1+0.2) WHERE SALARY<=2000
) WHERE NAME LIKE '李%'FETCH FIRST 10 ROWS ONLY;
SELECT * FROM FINAL TABLE
(
UPDATE USER
INCLUDE (OLD_SALARY FLOAT)
SET SALARY=SALARY*(1+0.2),OLD_SALARY=SALARY
WHERE SALARY<=2000
)
SELECT * FROM old TABLE
(
delete from t0_USER where SALARY<2000
);
select count(*) from final table(
INSERT INTO t0_USER (NAME,SALARY) VALUES
('张三', 1000),
('李四', 2000),
('王五', 2400),
('赵六', 2800),
('高七', 3000)
)
随机取数
SELECT * FROM <TABLE_NAME> ORDER BY RAND() FETCH FIRST 10 ROWS ONLY ;
随机抽样
SELECT *
FROM t0_user TABLESAMPLE BERNOULLI(18) REPEATABLE(586)
ORDER BY SALARY;
select A.a,B.b from A,B where locate(B.d,A.d)>0
convert(char(4),order_date,120)=
在 SQL 语句的 WHERE 子句中应该尽量避免在字段上使用函数，因为这样做会使该字
段上的索引失效
SELECT * FROM USER WHERE REGISTERDATE>='2009-9-24 00:00:00.0' AND REGI
STERDATE<'2009-9-25 00:00:00.0';
计算相隔天数
values DAYS(DATE('2009-10-1') ) - DAYS(DATE('2008-10-1')) ;
1 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面
2. 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。
db2 grant dbadm on database to user fxcjh
十八 db2 调优
1 在where 语句后面字段不要用函数，这样导致字段不走索引
(1)比如 PCF_OPENTIME 这个14位的日期yyyymmddhhffmm
WHERE
LEFT(A.PCF_OPENTIME,8)=REPLACE(@awp_data_date:char@,'-','') --当天 日期
这样容易导致PCF_OPENTIME 不走索引
这个可以改为
PCF_OPENTIME> =REPLACE(@awp_data_date:char@,'-','')||’000000’
And PCF_OPENTIME<=REPLACE(@awp_data_date:char@,'-','')||’235959’
(2)
TO_DATE(LEFT(A.DATETIME,8),'YYYY-MM-DD') >= DATE(@awp_data_date:char@) - @p_700014019:number@ DAYS /*参数天时间内 */
改为 A.DATETIME>REPLACE((DATE(@awp_data_date:char@) - @p_700014019:number@ DAYS),'-','')
2 一个sql语句不要写的太长，尽量分临时表，一步一步写。
DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP1(
CUSTOMERID VARCHAR(32) , /* 客户内部编码 */
) WITH REPLACE NOT LOGGED
ON COMMIT PRESERVE ROWS
;
Insert into SESSION.TMP1
Select a,b,c
From table1
3 表不要建太多的索引
影响插入速度，有次生产表建立2个索引，导致插入12w数据，4,5个小时都没有完成，但是去掉索引之后1分钟之内就插入完成。
当查询数量很快时，插入非常慢时，试试去掉索引 重整，REORG ->
RUNSTATS ->
4 修改生产字段 一定要记得 REORG ->RUNSTATS -> 否则容易产生一些莫名其妙的错误。
5 游标使用 注意commit 和rollback
使用游标时要特别注意如果没有加 with hold选项，在commit和rollback时，该游标将被关闭。
Declare cursor1 cursor with hold for select a,b,c
From t00_user
6 case when 用法 coalesce(username,’’) value(username,’’)
Select case a1
When ‘1’ then ‘n1’
When ‘2’ then ‘n2’
Else ‘n3’
End as aa1 from t00_user;
7 插入或者删除太慢，随时记得表维护
runstats on table FXCJH.T48_RCS_GUARANTY_INFO with distribution on all columns and detailed indexes all allow write access;
8 动态sql能 ，自动选择最佳的执行计划。
扫一扫
1034
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
