1. create database
create db testdb on /cardpro/dbdir
2. create tablespace
create tablespace tbs2 managed by database using (device '/dev/rapplv2' 4640M, device '/dev/rapplv8' 4640M )
create tablespace tbs2 managed by database using (file '/home/db2inst1/buff4000.dat' 25000)
3. connect database
connect to testdb user <userid> using <pwd>
4. create table
CREATE TABLE ACL_FUNCTION(
FUNCTION_ID CHAR(10) NOT NULL,
FUNCTION_DES CHAR(80),
MODULE_ID CHAR(10) NOT NULL,
BATRUN_STA CHAR(1),
MODIFY_TIME CHAR(30),
MODIFY_BY CHAR(10),
CONSTRAINT PKACL_FUNCTION PRIMARY KEY (FUNCTION_ID)
) IN TBS1
INDEX IN TBS6
5. create index
create index cb_finacc_acctno on cp_indacc(cb_fin_acctno)
6. select, delete, insert, update
1) select * from acl_user where user_id='test'
选择前3条记录: select * from acl_user fetch first 3 rows only
查询从第m条到第n条记录:
select * from (select ROW_NUMBER() over() as a, acl_user.* from acl_user) as acl_user where a >=5 and a<=30
2 )
insert into acl_screen_fun_map value('CCPM1900C','0',null,'CPM1900',null)
insert into acl_screen_fun_map (sceen_id, button_def) value ('CCPM1900C' , '0' )
3） delete from acl_user
delete from acl_user where user_id='test'
4）update acl_user set user_id='newtest' where user_id='test'
7. 显示相关信息
显示当前活动数据库
db2 list active databases
显示命令选项
db2 list command options
显示系统数据库目录
db2 list db directory
显示表空间
db2 list tablespaces
db2 list tablespaces show detail
显示表空间容器
db2 list tablespace containers for tablespace-id
Example: db2 list tablespace containers for 1
显示表
db2 list tables for schema cardpro 显示用户为cardpro的所有表
db2 list tables 显示当前用户所有表
db2 list tables for system 显示所有系统表
显示当前用户数据库的存取权限
db2 get authorizations
显示当前数据库的活动进程
db2 list applications
db2 list application for db <dbname>
db2 force application all : 关闭所有db2 的进程
显示数据库配置信息
db2 get db cfg for <db name>
显示db2 数据库管理程序配置
db2 get dbm cfg
显示表结构
db2 describe table <tablename>
显示表索引
db2 describe indexes for table <tablename>
8. 重新连接
connect reset
9. 中断数据库连接
disconnect <dbname>
10. lock table
lock table <tablename> in exclusive mode
lock table <tablename> in share mode
11. 修改数据库参数
1） 数据库配置参数
db2 update db cfg for <dbname> using <parameter> <value>
eg: db2 update db cfg for testdb using LOGRETAIN on
2） 数据库管理器配置参数
db2 update dbm cfg using <parameter> <value>
db2 update dbm cfg using MON_HEAP_SZ 512
12. 启动实例
db2start
13. 停止实例
db2stop
db2stop force
14. 授权
ADD USER:
grant createtab,connect on database to user cardpro
SCHEMA :
grant createin,dropin,alterin on schema cardpro to user cardpro with grant option
TABLE :
grant control on table cp_fintbl to user cardpro
grant select,insert,update,delete,alter,index,references on table cp_fintbl to user cardpro with grant option
VIEW :
grant control on v_syscolumns to user cardpro
grant select,insert,delete,update on v_syscolumns to user cardpro with grant option
INDEX :
grant control on index wb_start_card_no to user cardpro
TABLESPACE :
grant use of tablespace userspace1 to user cardpro with grant option
15. 系统表说明
检查约束 syscat.checks
列 syscat.columns
检查约束引用的列 syscat.colchecks
关键字中使用的列 syscat.keycoluse
数据类型syscat.datatype
函数参数或函数结果 syscat.funcparms
参考约束 syscat.references
模式 syscat.schemata
表约束 syscat.tabconst
表 syscat.tables
触发器 syscat.triggers
用户定义函数 syscat.functions
视图 syscat.views
16. 多数据库联合查询（FEDERATED）
首先确认实例配置参数中FEDERATED值设为YES。
然后通过以下步骤达到在mbank库中访问cards24库cardpro.cp_banknm表的目的。
1） connect to mbank user cardpro using cardpro
2) create wrapper drda
3) create server db2sam type db2/nt version 7.2 wrapper drda authorization db2inst1 password cardpro4bos options(node 'p610', dbname 's24db')
注意，要将数据库cards24编目在本地节点上，node对于节点名，dbname对于数据库别名
例如:
catalog tcpip node p610 remote 10.168.4.18 server 50000
catalog db cards24 as s24db at node p610
4) create user mapping for db2inst1 server db2sam options(remote_authid 'db2inst1', remote_password 'cardpro4bos')
5) create nickname cardpro.cp_banknm for db2sam.cardpro.cp_banknm
之后，就可以在mbank数据库中用select语句查询，如select * from cardpro.cp_banknm。
17. 利用已有表生成表结构脚本
在命令窗口中利用db2look工具，可以生成表结构。
db2look -d <dbname> -a -e -o <filename>
其中，-d 后跟数据库名，-a 是指针对所有用户，-o 后跟要输出的文件名。
如果希望输出某个用户创建的表，可以用 -u 参数。指定需要输出表结构的表名，可以用 -t 参数。
例如，数据库cards24中，对于db2inst1用户创建的cp_crdtbl表，可以用下面的语句生产表结构：
db2look -d cards24 -t cp_crdtbl -u db2inst1 -e -o cp_crdtbl.sql
生成的脚本文件可以用 db2 -svtf 文件名 来执行。
18. 在命令行下执行DB2脚本（script）
在命令窗口中运行DB2脚本，可用 db2 -svtf 脚本文件名 来实现。
例如，脚本文件名为sample.sql，运行：db2 -svtf sample.sql参数中：
s 代表遇到错误时中止运行脚本
v 代表输出结果到屏幕
t 指以；号作为每行的分隔符
f 指后面需跟脚本文件名
如果希望即使遇到错误也不要中止运行脚本可以去掉参数s。
如果脚本中没有分隔符，应去掉参数t。
如果脚步中分隔符为其他字符，请用-td后接所使用的分隔符，例如如果使用的分隔符为@，可以用db2 -td@ -svf 脚本文件名 来执行脚本。
19. Quit 与 Terminate
Quit和Terminate都可以退出DB2 CLP。但是Terminate同时终止了后台的进程并清空了所分配的内存。
20. 如何判断应用程序是否出现死锁现象
在一次会话（SESSION）中，首先用
UPDATE MONITOR SWITCHES USING LOCK ON
命令打开开关，然后用
GET SNAPSHOT FOR LOCKS ON <dbname>
得到当前加在该数据库上的锁的情况。
下面是一个应用程序死锁后快照的情况：
Database Lock Snapshot
Database
Application handle = SAMPLE
Application
Application status = Lock-wait
...
Application ID holding lock = *LOCAL.DB2.011226074931
...
Application handle = 9
Application ID = *LOCAL.DB2.011226074931
...
Application status = Lock-wait
...
Application ID holding lock = *LOCAL.DB2.011226074903
...
可以看出句柄为8和9的程序分别处于Lock-wait状态，而它们所需要的所又分别被对方拿住，造成死锁状态的形成。
21. 数据库代码页不匹配
login as db2inst1，运行
db2set db2codepage=819
db2 terminate
db2stop
db2start
然后在客户端重新连接数据库。
22. 归档日志模式下哪些日志文件可以移走
归档日志模式下，日志归档后数据库会生成新的活动日志，日志的生成从SQL0000000开始，直至SQL9999999，然后再从SQL0000000（如果SQL0000000存在将被覆盖）开始，重新生产日志。
考虑到日志目录空间大小的问题，可以将归档了的日志移走，如果确认归档了的日志没有用处，也可以用PRUNE命令删除，例如：
PRUNE LOGFILE PRIOR TO S0000100.LOG
或者用 GET DB CFG FOR <dbname> 命令查看日志文件目录（Path to log files）参数确定数据库当前使用的日志文件目录。
然后，从控制中心或着用 GET DB CFG FOR dbname 命令查看第一活动日志文件（First active log file）参数，该参数对应的日志文件之前的日志文件是可以移走的。安全起见，建议使用用户出口程序(userexit)导出归档日志。
23. Schema
模式是一个标识符，它帮助分组表和其他数据库对象。模式可以归个人拥有，拥有者可以控制对数据以及其中的对象的存取。
1) 模式是数据库中的对象。
Create schema cardpro
Set schema cardpro
2) 模式名用作两部分对象名的第一部分。创建一个对象时，可将其分配给特定模式。若不指定模式，则它被分配给缺省模式，缺省模式通常是创建该对象的人员的用户 ID。名称的第二部分是对象名。例如，cardpro.cp_sysprm。
24. 计算DB2 UDB的日志文件空间
日志文件所需的空间量（以字节数计），其范围可以为：
( logprimary * (logfilsiz + 2 ) * 4096 ) + 8192 至 ( (logprimary + logsecond) * (logfilsiz + 2 ) * 4096 ) + 8192
其中：
logprimary 是在数据库配置文件中定义的主日志文件数 (logprimary=20)
logsecond 是在数据库配置文件中定义的辅助日志文件数 (logsecond =60)
logfilsiz 是在数据库配置文件中定义的每个日志文件中的页数 (logfilsiz=10000)
2 是每个日志文件所需的标题页的数目
* 4096 是一页中的字节数
* 8192 是日志控制文件的大小（以字节计）。
对于DB2 UDB V7.2而言，总的活动日志空间不能超过 32 GB。
例如：生产环境日志文件空间
（20*(10000+2)*4096）+8192 ~ (20+60)*(10000+2)*4096)+8192 即781M ~ 3126M
每个日志文件的大小：10000*4096＝39M
25. 如何将表空间的状态置为正常
当使用load工具时，会使用一种持续状态的锁，它属于一个连接，当该连接所属的进程意外中止时，有时quiesce状态依然存在，其他客户端无法查询该表空间里的数据库的表。
1) 连接到数据库
2) List tablespaces 判断哪个tablespace处于quiesce状态，并取得object ID & tablesapce ID
3) 判断object ID 对应的表
4) db2 quiesce tablespaces for table <tablename> reset
26. backup and restore
log file: circular log & archival log
backup: offline & online
1) db2 update db cfg for <dbname> using LOGRETAIN on
2) db2 restart db <dbname>
3) db2 backup db <dbname> to <dir>
Example1 ：backup db online and restore to an existed database
db2 "backup db <dbname> online to . without prompting"
db2 "restore db <dbname> from . taken at <timestamp> newlogpath <dir>"
db2 "rollforward db <dbname> to the end of logs and stop overflow log path (<dir>) "
or:
db2 "rollforward db <dbname> to <UTCtimestamp> and stop overflow log path (<dir>) "
Example2：backup tablespace online and restore to an existed database
db2 "backup db <dbname> user db2inst1 using cardpro4bos tablespace(App1tb,App2tb) online to . without prompting"
db2 "restore db <dbname>tablespace(App1tb,App2tb) online from . taken at <timestamp> newlogpath <dir> without prompting"
db2 "rollforward db <dbname>to end of logs and stop tablespace(App1tb,App2tb) online overflow log path(<dir>)"
26. Sequence
1) create sequence
db2 "create sequence cardpro.autseq as integer start with 1 increment by 1 minvalue 1 maxvalue 999999999 cycle cache 10"
2) alter sequence
db2 "alter sequence cardpro.indaccseq restart with 1 "
3) drop sequence
db2 drop sequence cardpro.autseq restrict
4) query sequence
db2 "select char(seqname,20),decimal(start,7) from sysibm.syssequences"
27. Database optimization
db2 "reorg table <tablename> use tempspace1"
db2 "runstats on table <tablename> and indexes all"
db2 rebind <pkgname>
db2 "reorgchk update statistics on table <tablename>"
or
db2 "reorgchk current statistics on table <tablename>"
28. Data management
Import, export, load, db2move
db2 "load from afintbl.txt of del modified by coldel^ decpt. implieddecimal keepblanks savecount 10000 messages loadfintbl.msg insert into cardpro.cp_fintbl"
db2move <dbname> export -tn <tablename> -u <userid> -p <pwd>
db2move <dbname> import -u <userid> -p <pwd>
29. 修改表结构，属性
db2 "ALTER TABLE <tablename> DATA CAPTURE NONE VOLATILE "
db2 "ALTER TABLE <tablename> APPEND ON"
30. 修改bufferpool size大小
login as db2inst1
db2 "alter bufferpool ibmdefaultbp size 300000"
31. 表空间重定向恢复
恢复数据库时，如果数据库表空间使用的容器（container）被别的数据库占用，那么在恢复时须要做表空间重定向。下面是一个表空间重定向的例子，数据库的别名为cardprod：
1). 使用 restore database 加 redirect 参数：
db2 "restore db testdb from . taken at $TIMESTAMP to /cardpro/dbdir newlogpath /cardpro/prod/db2logs redirect without prompting"
在第一步后，第三步前，数据库恢复可以用下面命令取消：
db2 restore db testdb abort
2). 用 set tablespace containers 命令重定义容器
db2 "set tablespace containers for 0 using (path "/cardpro/dbdir/db2inst1/NODE0000/SQL00001/SQLT0000.0")"
db2 "set tablespace containers for 1 using (device '/dev/rapplv2' 589824, device '/dev/rapplv8' 589824 )"
db2 "set tablespace containers for 3 using (file "/home/db2inst1/buff.dat 25000 )"
第二步中须要重定义所以需要做表空间重定向的容器。
3). 成功运行第一步和第二步后，使用：
db2 restore db mydb continue 完成恢复工程。如果第三步失败，重定向须要从第一步做起。
32.DB2性能顾问程序
---------------------------------------------------------------------------------------------------
##DB2性能顾问程序
1，图形化工具CONFIGURATION ADVISOR
2,CLP AUTOCONFIGURE USING mem_percent 60 workload_type complex num_stmts 20 APPLY DB AND DBM
##DB2 设计顾问程序
设计顾问程序可以帮您找到 SQL 语句的最佳索引。它使用 DB2 优化器、数据库统计信息和解释（Explain）
机制来为特定查询或一组 SQL 语句（也称为工作负载）生成推荐索引。您可以从命令行用 db2advis 加上必要的输入启动该顾问程序。
下面的示例使用输入文件
input.sql 对 sample 数据库执行设计顾问程序，该输入文件含有一组 SQL 语句。然后将输出存储在 output.out 中。
db2advis -d sample -i input.sql -o output.out
db2advis command syntax:
---------------------------------------------------------------------------------------------------
Use one of the -w/-i/-s options to specify the input.
db2advis is a tool that recommends database objects based
on an existing database and a set of queries.
SYNTAX:
db2advis -d <db name> [-t <time>] [-l <disk_space>]
-s "sql stmt" | -i <infile> | -w <workload name> | -g | -qp
[ -[user|a] <username> [/<password>] ]
[ -[type|m] <advis_type> ]
[ -[compress|k] <compression level> ]
[ -[tbsp|b] <MQT creation table space> ]
[ -[simtbsp|c] <simulation table space> ]
[ -[schema|n] <schema name> ]
[ -[qualifier|q] <qualifying schema name>]
[ -[script|o] <output script> ]
[ -[password|x] ] [ -[sample|r] ] [ -[deferred|u] ] [ -[drop|f] ]
NOTE 1: only one of the following four options
can be used: [stmt,file,workload,pkg]
NOTE 2: -tbsp, -simtbsp, -schema and -sample can only be specified when MQTs a
re to be selected
where:
-[db|d] database name.
-[keep|p] keep plans in explain tables.
-[timelimit|t] maximum duration for db2advis to run, in minutes.
default is 1 minute, a value of 0 means unlimited duration.
-[disklimit|l] maximum disk space in megabytes. default is
unlimited.
-[stmt|s] recommend database objects for this SQL statement.
-[file|i] get SQL from this input file.
-[workload|w] get SQL from rows in the ADVISE_WORKLOAD table,
specified by matching WORKLOAD_NAME.
-[pkg|g] get workload from dynamic package cache
-qp get workload from query patroller table
-[script|o] place the database objects creation script in a file.
-[user|a] username to connect with. (optional password)
-[type|m] advis type.
Subset of:
I - for index selection,
M - for MQT selection,
C - for Multi-dimensional Clustering selection,
P - for partitioning selection,
Default is I
Example: MICP - for ALL selections.
-[tbsp|b] MQT creation tbspace name (default is USERSPACE1)
-[simtbsp|c] simulation table space name that is defined only on the cat
alog node
-[schema|n] schema name that is used to define new MQTs
-[qualifier|q] schema name used to qualify unqualified tables
-[sample|r] gets more statistics for MQT.
-[compress|k] specifies compression OFF, LOW, MED, or HIGH
-[drop|f] drops previously existing simulated catalog tables
-[deferred|u] specified that immediate and deferred MQTs are to be output
. Default is immediate.
-[password|x] indicates that the password will be read from stdin
EXAMPLE:
Create an input file called db2advis.in with the following 5 lines:
--#SET FREQUENCY 100
SELECT COUNT(*) FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS';
--#SET FREQUENCY 1
SELECT AVG(BONUS), AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT ORDER BY WORKDEPT;
run the following command and let it finish
db2advis -d sample -i db2advis.in -t 5
for bigger workloads, the program will take longer.
