ITPub博客

首页 > 数据库 > 数据库开发技术 > db2 基础

db2 基础

原创 数据库开发技术 作者:mrpxq 时间:2007-11-12 01:18:42 0 删除 编辑
一、db2 基础
基本语法
注释:“--”(两个减号)
字符串连接:“||
set msg=’aaaa’||’bbbb’,则msg’aaaabbbb’
字符串的引用:‘’(一定用单引号),如果需要输入单引号,输入两个单引号即可。
语句结束:“;”
语法来源:PASCLE
转义字符
如果你想查询字符串中包含‘%’或‘_’ ,就得使用转义字符(Escape Characters)。比如,要想查询book_title中包含字符串’99%’的纪录:
SELECT * FROM books WHERE book_title like ‘%99!%%’ escape ‘!’
后面的escape ‘!’是定一个转义字符‘!’, 指明紧跟着转义字符’!'后的%不再是统配符。
DB2命令参数选项
Db2 list command options 可以查看
-a 显示 SQLCA OFF
-c
自动落实 ON
-e
显示 SQLCODE/SQLSTATE OFF
-f
读取输入文件 OFF
-l
将命令记录到历史文件中 OFF
-n
除去换行字符 OFF
-o
显示输出 ON
-p
显示交互式输入提示 ON
-r
将输出保存到报告文件 OFF
-s
在命令出错时停止执行 OFF
-t
设置语句终止字符 OFF
-v
回送当前命令 OFF
-w
显示 FETCH/SELECT 警告信息 ON
-x
不打印列标题 OFF
-z
将所有输出保存到输出文件 OFF
这些选项的具体功能及其缺省设置为:
.a
显示 SQLCA 的数据,缺省为 OFF
.c
是否自动落实 SQL 命令,缺省为 ON
.e {c|s}
显示 SQLCODE SQLSTATE,缺省为 OFF
.f
文件名将命令的输入从标准输入指定到某一文件,缺省为 OFF注:命令“db2 < 文件名“db2 -f 文件名作用相同。
.l
文件名将命令记录到历史文件中,缺省为 OFF
.n
除去换行字符,缺省为 OFF
.o
将输出数据及信息送到标准输出,缺省为 ON
.p
在交互方式下显示命令行处理器的提示信息,缺省为 ON
.r
将输出保存到指定文件中,缺省为 OFF
.s
执行批处理文件中或交互方式下的命令出错时即停止执行操作,缺省为 OFF
.t
设置语句终止字符,缺省为 OFF
.v
回送当前命令到标准输出,缺省为 OFF
.w
显示 FETCH SELECT 警告信息,缺省为 ON
.x
不打印列标题,缺省为 OFF
.z
文件名将所有输出保存到输出文件,缺省为 OFF
SQLSTATE含义
db2命令行方式下输入:? 20012SQLSTATE值)
可以获取sql错误含义
import和export的用法
IMPORT FROM CO_ACCEPTANCEDRAFT.del OF DEL MESSAGES importmsgs.txt INSERT INTO cmmcorc.CO_ACCEPTANCEDRAFT;
EXPORT TO CO_ACCEPTANCEDRAFT.del OF DEL MESSAGES exportmsgs.txt SELECT * FROM cmmcorc.CO_ACCEPTANCEDRAFT;
如果是Sybase导出的文本,并且用TAB分隔符的文档,那么可以采用
Db2 import from filename.txt of del modified by COLDEL0x09 insert into tablename;
其中COLDEL是关键字,0x09是16进制,表示tab符号
Load用法
load from tempfile of del modified by delprioritychar replace into TABLENAME nonrecoverable
说明:
在不相关的数据表export数据时,可以采取并发的形式,以提高效率;

TABLENAME指待清理table的名称;
modified by delprioritychar防止数据库记录中存在换行符,导致数据无法装入的情况;
replace into对现数据库中的内容进行替换,即将现行的数据记录清理,替换为数据文件内容;

nonrecoverable无日志方式装入;
查询出用户表
SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR='USER'
如何知道当前DB2的版本?
select * from sysibm.sysversions
如何知道TABLESPACE的状况?
select * from sysibm.SYSTABLESPACES
如何知道INDEX的状况?
select * from sysibm.sysindexes where tbname=’XXXX’
or
describe indexes for table table_name show detail
测试SQL的执行性能
db2batch -d DB_NAME -f select.sql -r benchmark.txt -o p3
select.sqlselect语句写在文件中
如何获取连接的进程
List applications
删除当前正在使用的application:
db2 "force application (Id1,Id2,Id3)"
Id1,Id2,Id3
是List显示的应用号;
删除所有的进程 db2 force application all
查看当前应用号的执行状态:db2 get snapshot for application agentid 299 |grep Row
如何修改缓冲池
db2 alter bufferpool ibmdefaultbp size 10240
如何知道表的字段的状况?
select * from sysibm.syscolumns where tbname=’XXXX’
如何知道DB2的数据类型?
select name,* from sysibm.sysdatatypes
如何知道BUFFERPOOLS状况?
select * from sysibm.sysbufferpools
如何查看表的结构?
describe table table_name
or
describe select * from schema.table_name
如何快速清除一个大表?
alter table table_name activate not logged initally with empty table
or
import from null_file of del replace into table_name
如何查看数据库的包?
select * from syscat.packages
如何查看数据库的存储过程?
select procname,text,* from syscat.procedures
Or
select procname,text from sysibm.sysprocedures
如何查看数据库SAMPLE的配置文件的內容?
get database configuration for sample
or
get db cfg for sample
如何將数据库SAMPLE的参数设置为默认数值?
reset database configuration for sample
or
reset db cfg for sample
如何修改数据库SAMPLE配置参数数值?
update database configuration for sample
using
or
update db cfg for sample using
如何重新啟動数据库?
Restart db db_name
如何激活数据库?
Activate db db_name
如何停止数据库?
Deactivate db db_name
如何重命名表?
Rename old_tablename to new_tablename
如何设置DB2环境变量
Db2set命令,语法如下:
Db2set variant=value
如何除去quiesce状态
1. 连接到数据库
2. 用 list tablespaces 判断哪个tablespace处于quiesce状态和和取得对象(object)ID
3. 判断对象ID对应的表
a)用 db2 "select tabname from syscat.tables where tablid=对象ID" 得到表名
b)用 db2 list history 判断是那个表
4. 用 db2 quiesce tablespaces for table 表名 reset 去除quiesce状态
如何实施已删除表的恢复(Dropped Table Recovery)
1. 首先数据库要可以前滚恢复(数据库配置参数logretain或userexit打开)。
2. 对要实施Drop Table Recovery的表空间(限regular tablespace),执行:
alter tablespace 表空间名 dropped table recovery on
3. 用 list history dropped table all for 数据库名 得到删除表的tableid(例如 0000000000006d0000020003)和表结构的生成语句(DDL),记录tableid和该语句以便恢复。之后,用drop命令删除的表中的数据可以在前滚恢复时导出。
4. 恢复数据库后,如果想恢复已删除的表,在前滚时加recover dropped table tableid to 目标目录 。 在该目录下被删除的表中的数据导出,例如导出至 ../NODE0000/data文件。利用上面提到表结构生成语句生成被删除了的表,然后用import命令将数据导入表中。
如何备份数据库
db2 "backup database sample tablespace (syscatspace, userspace1) online to /dev/rmt0 without prompting"
or
db2 "backup database sample user db2admin using db2admin to c:backup with 3 buffers buffer 1000 without prompting"
 
如何生成所有对象的DDL
db2look -d DB_NAME -a -e -m -l -f -o filename.sql
如何恢复数据库
restore db db1 to /tstdb2/catalog into db newlogpath /tstdb2/db2log buffer 2048
replace existing redirect parallelism 16;
set tablespace containers for 1 using (path '/tstdb2/db2tmp');
set tablespace containers for 2 using
(device '/dev/rtstcontlv00' 2621440, device '/dev/rtstcontlv01' 2621440,
device '/dev/rtstcontlv02' 2621440, device '/dev/rtstcontlv03' 2621440 ) ;
restore db db1 continue;
恢复完成后,执行db2s命令,如果出错,再执行如下命令:
db2 rollforward db db to end of logs and complete
如何查看数据库目录
1)首先用 db2 list database directory 命令查看系統資料庫目錄(System Database Directory)中有沒有該資料庫。如果有,應該再確定該資料庫是沒有用的資料庫之後用 db2 drop database 資料庫名將其刪除。

2)
如果沒有,再用 db2 list database directory on location 看在本端資料庫目錄(Local Database Directory)中有沒有該資料庫,location指定資料庫的位置(如Windows下的C: ,Unix下的/home/db2inst1)。
如何更改本地系统名称
1db2set db2system=新系统名
2. db2 terminate
3. db2 uncatalog node
原节点名
4. db2 terminate
如何使用DB2MOVE
Db2move DB_NAME action -u USERID –p PASSWORD
Action:export,import,load
Options如下:
Option
Default
Notes
-tc
table-creators
all_creators
EXPORT.Wildcard
-tn
table-names
all_user_tables
EXPORT.Wildcard
-sn
schema-names
all_schemas
EXPORT.Wildcard
-ts
tblspace-names
all_tablespace
EXPORT.Wildcard
-tf
tables
from
file
-io
import-option
REPLACE_CREATE
IMPORT_only.
-lo
load-option
INSERT
LOAD_only.
-l
lobpaths
current_dir
separated_by_commas.NO
-u
userid
logged_on_userid
-p
password
logged_on_password
-aw
allow-warnings
false
include
warnings
during
export.
例如:
Db2move ehrdb export –u ehr –p ehr
Db2move ehrdb import –u her –p ehr
IMPORT如何避免日志满出错
在使用IMPORT命令向数据库出入数据时,如何避免日志空间满错误?
在执行IMPORT命令时, 如果使用循环日志, 有时会出现日志满错误, 这时可用COMMITCOUNT参数来解决. 因为日志空间满往往是因为所有的日志均处于活动状态导致的. COMMIT执行后, 会释放所占据的资源, 其中包括日志 . 这样, 被当前事务使用的日志在COMMIT命令执行后, 即变成非活动状态了.
如何处理日值
-日志处理
DB2日志是以文件的形式存放在文件系统中,分为两种模式:循环日志和归档日志。当创建新数据库时,日志的缺省模式是循环日志。在这种模式下,只能实现数据库的脱机备份和恢复。如果要实现联机备份和恢复,必须设为归档日志模式。
目前在综合业务系统中,设置的均是归档日志模式;其它系统(如事后监督、经营决策、中间业务等)一般都设置为循环日志模式。至于采用何种模式,可以通过修改数据库配置参数(LOGRETAIN)来实现: 归档日志模式:db2 update db cfg for using logretain on 注:改为on后,查看数据库配置参数logretain的值时,实际显示的是recovery。改变此参数后,再次连接数据库会显示数据库处于备份暂挂(BACKUP PENDING)状态。这时,需要做一次对数据库的脱机备份(db2 backup db ),才能使数据库状态变为正常。
循环日志模式:db2 update db cfg for using logretain off
-处理步骤
必须按照以下正确的步骤进行操作:
要求必须使用DB2命令PRUNE进行清理,不建议使用rm命令删除。
删除前应保证应用已停止(即联机已下来)。
查看当前使用的日志文件目录及第一活动日志文件
用 “db2 get db cfg for ”命令查看日志文件目录(Path to log files)参数,确定数据库当前使用的日志文件目录。 例如:Path to log files = /db2log/,说明DB2日志存放目录是/db2log
用 “db2 get db cfg for ”命令查看第一活动日志文件(First active log file)参数,该参数对应的日志文件之前的日志文件均为归档日志文件,如果确认没有用,可以删除。例如:First active log file = S0015913.LOG,说明当前第一活动日志文件是S0015913.LOG。
备份好要删除的归档日志
删除归档日志 以应用用户(如BTP)登录,执行:

$ db2 connect to

$ db2 prune logfile prior to S???????.LOG
注:S???????.LOG为查看到的第一活动日志文件。此命令可以将当前第一活动日志文件之前的归档日志文件全部删除。
如何清理db2diag.log文件
db2diag.log,是用来记录DB2数据库运行中的信息的文件。可以通过此文件,查看记录的有关DB2数据库详细的错误信息。此文件也是不断增大的,需要定期进行清理。
可以通过查看实例的配置参数DIAGPATH,来确定db2diag.log文件是放在哪个目录下:db2 get dbm cfg 如果Diagnostic data directory path(DIAGPATH) = /home/db2inst1/sqllib/db2dump,则此文件是放在/home/db2inst1/sqllib/db2dump目录下。当文件系统/home的使用率达到80%-90%左右时,应及时删除db2diag.log文件。
请按以下正确步骤操作:确认应用(如BTP)、DB2已经停止。
将原db2diag.log文件备份到其它文件系统下。
删除db2diag.log文件。删除后,DB2会自动创建一个新的文件。
标准函数
length: 返回自变量中的字节数
CAST: 变量类型转换或截断字符串
如:CAST(RESUME AS VARCHAR(370))
Select CAST(colname as integer) from tablename;
decimal: 变量转换为指定精度的数值
如: select decimal(amount,16,2) from tablename;
WHERE 子句条件
谓词
功能
x = y
x 等于 y
x <> y
x 不等于 y
x < y
x 小于 y
x > y
x 大于 y
x <= y
x 小于或等于 y
x >= y
x 大于或等于 y
IS NULL/IS NOT NULL
测试空值
获取系统日期或系统时间
select current time into curtime from (values 1) as tmp;
select current date into curdate from (values 2) as tmp;
select year(current date) into curdate from (values 2) as tmp; --获取系统年份
select month(current date) into curdate from (values 2) as tmp; --获取系统月份
select day(current date) into curdate from (values 2) as tmp; --获取系统日份
(CURRENT TIMESTAMP 精度达到微秒)
如何预防空值
DB2的COALESCE函数返回()中表达式列表中第一个不为空的表达式,可以带多个表达式, 和oracle的isnull类似。语法格式如下:
CLEASCE(colname,default_value)
多表的关联更新方法
db2的update语法不支持“update table1 set t1.col1=t2.value1 from table1 t1,table2 t2 where …”的写法,但是可以通过如下方法解决:
update table1 t1 set t1.col1=(select t2.col1 from table2 t2 where …)
例:
update test t1 set (t1.username,t1.instcode) = (select t2.instcode,t2.instname from sysinsttb t2 where t2.instcode=t1.instcode);
获取操作(insert、update)的记录数
GET DIAGNOSTICS rcount=ROW_COUNT;
get diagnostics rcount =ROW_COUNT; 只对update,insert,delete起作用. 不对select into 有效
如何执行RUNSTATS等优化命令
db2 runstats on table with distribution and detailed indexes all
db2 reorgchk update statistics on table all
查看什么时候进行了runstats
db2 "select name, stats_time from sysibm.systables"
完整执行如下:
db2 connect to ocrm1 user db2iocrm using db2iocrm
db2 runstats on table db2iocrm.eosoperator with distribution and indexes all
db2 reorg table db2iocrm.eosoperator allow read access
db2 reorg indexes all for table db2iocrm.eosoperator allow read access
db2 connect reset
如何获取结果集的前N行数据
Select * from tablename fetch first N rows only
安装DB2默认值?
WINDOWS或OS/2中默认实例的是DB2
LINUX或UNIX环境下默认实例的是DB2INST1
WINDOWS或OS/2中默认帐户的是DB2ADMIN
LINUX或UNIX环境下默认帐户的是DB2AS
如何定义序列
CREATE SEQUENCE ORDERSEQ START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE CACHE 24
例如:
create sequence pk_only_empid;
select NEXTVAL FOR pk_only_empid from (values 1) as tmp;
如何关闭表的日志
  ALTER TABLE TABLE_NAME ACTIVE NOT LOGGED INIALLY
如何获取SQL执行计划
SQL 解释工具
SQL 解释工具提供查询优化器为 SQL 语句所选择的访问计划的有关详细信息。该信息存储在 EXPLAIN 表中,可以在稍后使用诸如 Visual Explaindb2explndynexpln db2exfmt 的工具进行格式化,从而以友好的可视方式进行表示。
EXPLAIN 表可以在您第一次使用 Visual Explain 时自动进行创建。即使没有创建它们,您也可以手工进行创建,如下:
% cd sqllibmisc
% db2 connect to bank
% db2 -tvf EXPLAIN.DDL
本文中,我们使用 db2exfmt 工具。例如,使用 db2exfmt 解释动态 SQL 语句,在 DB2 命令窗口中按照下列步骤进行:

% db2 connect to
% db2 set current explain mode explain
% db2 -tvf
% db2 set current explain mode no
% db2exfmt -d -g TIC -w -1 -n % -s % -# 0 -o
如何创建事例?
DB2ICRT INSTNAME <...PARAMETERS>
如果是客户端,加上client关键字
例如:
1) 用root登录,命令为smitty创建用户db2inst1,用于数据库管理。
2) 用root登录,使用db2icrt创建实例DB2 实例,使用以下命令。
cd /usr/opt/db2*/instance/
./db2icrt -s client db2inst1
3) 用db2inst1用户登录,创建数据库的本地节点目录,建立远程数据库映射别名,使用以下命令。
cd /usr/opt/db2*/bin
db2 catalog tcpip node wmsint remote 182.247.70.94 server 60000
db2 catalog database wmsint as wmsint at node wmsint
db2set db2codepage=1386
db2 terminate
4) 为了使root用户可以使用db2命令,要把home/db2inst1下的.profile文件中的下面一段文字加到根目录下的.profile中。
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.
export PATH
# The following three lines have been added by UDB DB2.
if [ -f /home/db2inst1/sqllib/db2profile ]; then
. /home/db2inst1/sqllib/db2profile
fi
5)退出root用户,重新登入
SQL写法技巧
多个字段时如何不通过使用select子句使用in/not in
select * from tabschema.tabname where (colA, colB, colC) [not] in (values (valueA1, valueB1, valueC1), (valueA2, valueB2, valueC2), ...(valueAn, valueBn, valueCn))
Update tablenameA t1
Set (colA,colB,colC)=(selelct b.colA,b.colB,b.colC from tablenameB t2 where t1.key = t2.key)
使用部分命令
列出所有实例 DB2ILIST
获取当前实例 GET INSTANCE
更新事例的配置 DB2IUPDT
删除事例 DB2IDROP INSTANCE_NAME
具体步骤如下:
停止事例上所有应用程序
在所有打开的命令行上执行DB2 TERMINATE
运行DB2STOP
备份DB2INSTPROF注册变量指出的事例目录
退出事例所有登陆者
使用DB2IDROP
也可以删除ID
列出本地系统上有许可信息的所有产品 DB2LICM -L
增加一产品许可DB2LICM -A FILENAME
删除一个产品的许可 DB2LICM -R PRODUCT PASSWORD
更新已购买的许可数量DB2LICM -U
强制只使用已经购买的数量DB2LICM -E HARD
更新系统上所使用的许可政策类型 DB2LICM -P REGISTERED CONCURRENT
更新系统上的处理器的数量 DB2LICM -N
查询许可文件记录的版本信息 DB2LICM -V
查询DB2LICM的帮助信息 DB2LICM -H
二、存储过程
如何声明一个存储过程
CREATE PROCEDURE 存储过程名(IN 输入变量名 输入变量类型,OUT 输出变量名 输出变量类型)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/490225/viewspace-981796/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    4
  • 访问量
    33146