DB2 数据库的一些常用操作

最新推荐文章于 2025-04-15 18:02:08 发布

H1670801543

最新推荐文章于 2025-04-15 18:02:08 发布

阅读量1.7k

点赞数 5

分类专栏：学习记录文章标签：数据库 db

本文链接：https://blog.youkuaiyun.com/H1670801543/article/details/136146965

版权

学习记录专栏收录该内容

5 篇文章

订阅专栏

数据库的启动、停止

db2start --启动

db2stop [force] --停止

与数据库的连接、断开

db2 CONNECT TO DBName [user UserID using PWD] --建立连接

db2 CONNECT reset / db2 disconnect CURRENT --断开连接

列出所有数据库目录

db2 list db directory （包括远程编目的数据库）

创建数据库

db2 create db 'MGYL' on 'D:\数据库' ALIAS 'MGYL' using codeset UTF-8 territory cn

db2 connect to 'test'

db2 GRANT DBADM ON DATABASE TO USER db2admin

db2 GRANT SECADM ON DATABASE TO USER db2admin

db2 connect reset

备份数据库:

离线备份

db2 force applications all --断开所有连接

db2 force application（h1,h2……） --杀死与欲备份数据库相连接的进程

db2 backup db DBName [to d:\db2_backup] [use TSM] --离线备份

db2 backup db DBName online [to d:\db2_backup] [use TSM] --在线线备份

db2 backup db DBName online incremental [to d:\db2_backup] [use TSM] --在线增量备份

恢复数据库:

首先创建数据库

然后设置数据库ORACLE兼容性

db2set DB2_COMPATIBILITY_VECTOR=ORA

db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES

然后在DB2 命令行窗口运行

db2 restore DATABASE AUTOSR FROM D:\数据库 TAKEN AT 20220221103759 INTO MGNYL WITH 2 BUFFERS BUFFER 1024 WITHOUT PROMPTING

视图

视图的权限

GRANT CREATE VIEW TO 用户;

视图建立

db2 create view 名称 as select 语句.

视图删除

db2 drop view view_name

存储过程:(编写顺,变量,自定义异常,游标定义,异常捕捉定义)

创建:

CREATE OR REPLACE PROCEDURE 存储过程名字 ([参数 [in 参数名参数类型....] [out 参数名参数类型....]])

[Language 语言 (sql,java,c,.net)]

BEGIN

定义:

[DECLARE 变量名类型 [DEFAULT (NULL/值)]]

...

赋值:

[set 变量=值]

[VALUES 值 INTO 变量]

[SELECT (or FETCH) 值 INTO 变量 from ...]

...

游标定义:

[Declare 游标名 cursor [WITH RETURN(返回结果集,不用Close)] for (sql语句);]

游标调用:

[OPEN 游标名;

执行语句

CLOSE 游标名;]

...

异常处理机制:

定义变量:

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

自定义异常:

[DECLARE 异常名 CONDITION FOR SQLSTATE <sqltate_value>;]

异常捕捉:

declare exit|continue|undo

handler for 异常|sqlstate <sqlstate-value>

begin

-- 异常处理代码 ---

....

end;

*continue 在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句。

*exit 在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。

*undo 在处理器操作执行之前，db2会回滚存储过程中执行的sql操作。在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。

*注：exit;只跳转出handler定义所在的begin 和 end的程序块

*for 所捕捉的异常可以是：sqlexception，sqlwarning，not found，自定义异常

异常执行:

resignal/signal <condition-name>

resignal/signal sqlstate <value> [set message_text = <error-message>];

resignal 和 signal 之间的区别在于，signal是在非handler处理逻辑中抛出异常，而resignal则是在handler处理逻辑中抛出异常。语法一致。

条件语句:

[IF 条件 then 执行语句

[ELSEIF 条件 then 执行语句]

[ELSE 执行语句]

END IF]

...

[CASE

[when 条件语句 then 执行语句]

...

[else 执行语句]

END CASE]

....

循环语句: ITERATE---相当于continue,跳过本次循环

LOOP 循环 -- 简单的循环

[L1: LOOP

SQL statements;---执行体

(LEAVE L1);---相当于break,跳出循环,可以搭配条件语句使用

END LOOP L1;]

WHILE 循环 -- 进入前检查条件

[WHILE 条件

执行体(sql语句体,条件变换,避免死循环)

END WHILE;]

REPEAT 循环 -- 退出前检查条件

[REPEAT

SQL statements;---执行体

UNTIL 条件;

END REPEAT;]

FOR 循环 -- 结果集上的隐式循环

[FOR loop_name AS

SELECT … FROM

SQL statements;

END FOR;]

END

删除:

drop procedure sp_name

例:

(1)create procedure insert(in id integer)

language sql

begin

insert into test values(id);

end

(2)create procedure error1(out error_text varchar(50),out p_sqlstate_out char(5), out p_sqlcode_out INTEGER ) language sql

BEGIN

Declare total INTEGER DEFAULT 0;

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE MESSAGE_TEXT varchar(50);

Declare condition1 CONDITION FOR SQLSTATE '20209';

Declare cursor1 cursor for select count(*) from test;

declare CONTINUE handler for condition1

begin

SELECT SQLSTATE,SQLCODE,MESSAGE_TEXT INTO p_sqlstate_out,p_sqlcode_out,MESSAGE_TEXT FROM SYSIBM.SYSDUMMY1;

end;

open cursor1;

FETCH cursor1 INTO total;

if total >=5 then

signal condition1 SET MESSAGE_TEXT ='超了';

else

set error_text='没超';

END IF;

close cursor1;

END;

最常用的专用寄存器有：

CURRENT DATE

CURRENT TIME

CURRENT TIMESTAMP

CURRENT USER

CURRENT PATH

触发器:

创建:

CREATE OR REPLACE TRIGGER 1) trigger_name

2) [BEFORE/AFTER] 3)[INSERT/DELETE/UPDATE] ON 4) [table_name]

[REFERENCING OLD AS ... NEW AS ...]

5) [FOR EACH ROW]

[BEGIN ATOMIC]

6) 触发事件

[END]

属性解析：

1）触发器名称

2）触发器条件达成前还是达成后调用触发器

3）触发器被触发的条件（当table_name表执行insert/delete/update这三种操作哪一种时候被触发）

4）当table_name表被操作时候调用触发器

5）声明该触发器是一个行级触发器（如果未指明则被默认为语句级触发器）

例:

db2 create or replace trigger test_tri

after insert on test referencing new as n

for each row

begin atomic

insert into test1 values(n.id,'ss');

end

高级触发器:

INSTEAD OF 触发器

删除:

db2 drop trigger <trigger_name>

序列:

创建序列:

create sequence seq_name

as datatype

start with a

increment by b

minvalue c

maxvalue d

no cycle

cache e

order

修改序列:

ALTER SEQUENCE sequence-name

[restart with 参数]

increment by b

minvalue c

maxvalue d

no cycle

cache e

order

删除序列:

drop sequence sequencename

查看序列:

select * from syscat.sequences

序列使用:

为了保持DB2向后的兼容，也可以使用nextval和prevval获取下一个序列值和当前序列值。

SELECT NEXTVAL FOR SEQ_NAME FROM SYSIBM.SYSDUMMY1

values nextval for seq_name

例:db2 insert into test values(seq_01.nextval)

db2 insert into test values(nextval for seq_01)

参数:

or replace：若有创建序列重名的序列，那么将会被替代（先删除，再创建）

as datatype：指定数据类型，这里说明下，序列的数据类型只能为数值型，

如，smallint，integer，bigint，不带小数点的decimal类型。

start with：指定序列的起始值，默认情况下，对于升序的序列是当前指定数据类型的最小值，

对于降序序列的是当前指定类型的最大值。

increment by：指定增长的值，默认值为1，正数表示此序列为增长升序的，负数表示此序列为降序的。

minvalue：最小值，若降序序列，no cycle的话，到此值的时候就不再生成序列值了；

no minvalue：对于升序序列来说，此值为start with的值，如果start with值未指定的话就是1.

对于降序序列来说，此值就是指定数据类型的最小值。

maxvalue：指定生产序列的最大值，

no maxvalue：对于升序序列来说，此值为指定数据类型的最大值；

对于降序序列来说，此值为start with值，若未指定start with值的话就是-1.

cycle：循环使用数据值，对于升序序列来说，当达到了最大值之后，下一个值将会是其最小值；

对于降序序列来说，达到最小之后，下一个序列值为其最大值。

no cycle：当达到序列的边界值之后，就不再产生序列值，默认选项。

cache：缓存序列值，表示每次应用此序列的时候，预先生产并存放在内存中的序列值。

其作用是有效的降低了写日志的I/O操作。

若在使用的过程中，出现系统错误的话，那么所有这些缓存值将会丢失。

最小值为2，默认为20

no cache：当指定此选项的时候，内存中不会存储任何序列值，无论出现什么异常现象都不会影响到此序列，

每次生存新的序列值，都会导致写日志的I/O操作。

order：按照请求的顺序生成值。

no order：不会按照请求的顺序生成值，默认情况。

索引

DROP INDEX Index_Name ; --删除索引

CREATE INDEX Index_Name ON TableName(F1) ; --对表TableName的F1字段创建索引Index_Name

db2 describe indexes FOR TABLE TableName ; --查看表TableName的索引

db2 describe indexes FOR TABLE TableName show detail;

db2 SELECT INDNAME,COLNAMES FROM syscat.indexes WHERE tabname = 'TableName';

单表多表操作

以及一些环境可以逐步熟悉起来

. SQLSTATE=55019 暂时挂起状态

. SQLCODE=-668, SQLSTATE=57016 装入暂挂状态

解决：

命令行使用：REORG TABLE my_table_name

工具执行：call sysproc.admin_cmd('reorg table my_table_name');

修改字段后运行ALTER TABLE 时要注意当前运行的语句是否需要执行REORG操作

对于这样的ALTER TABLE语句，如果不执行REORG操作的话，基本上目标表就不再可用

（一般alter table 3次需要执行一次 reorg table 重构命令）

REORG TABLE本身是DB2的command，不是正常的SQL语句（Statement）。

如果在非命令行环境中想使用REORG的话，可以像下面那样调用存储过程间接执行REORG操作，

执行前确保你所使用的帐号有调用这个存储过程的权限： CALL SYSPROC.ADMIN_CMD(‘reorg table my_test’)

解锁表

CALL SYSPROC.ADMIN_CMD('REORG TABLE IPAUTOSR.T_BM_OPC_TAG')