【转】db2知识文档（二）

最新推荐文章于 2020-12-23 19:53:03 发布

悟V-SpHeNIC

最新推荐文章于 2020-12-23 19:53:03 发布

阅读量103

点赞数

本文链接：https://blog.csdn.net/jasonhongcn/article/details/84453237

版权

安装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 Explain、db2expln、dynexpln 和 db2exfmt 的工具进行格式化，从而以友好的可视方式进行表示。

EXPLAIN 表可以在您第一次使用 Visual Explain 时自动进行创建。即使没有创建它们，您也可以手工进行创建，如下：

% cd <db2 install path>\sqllib\misc

% db2 connect to bank

% db2 -tvf EXPLAIN.DDL

本文中，我们使用 db2exfmt 工具。例如，使用 db2exfmt 解释动态 SQL 语句，在 DB2 命令窗口中按照下列步骤进行：

% db2 connect to <database_name>

% db2 set current explain mode explain

% db2 -tvf <Input file with an SQL statement ended with a semicolon>

% db2 set current explain mode no

% db2exfmt -d <dbname> -g TIC -w -1 -n % -s % -# 0 -o <output file>

如何创建事例?

DB2ICRT <client> 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

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 输出变量名输出变量类型)

紧跟其后的是存储过程属性列表

常用的有：LANGUAGE SQL、MODIFIES SQL DATA、RESULT SETS 1(返回结果集个数)

l 存储过程体以begin开始

l 存储过程体以end结束

存储过程约束规则

存储过程中调用存储过程

CALL 存储过程名（参数1，参数2，参数n）

例：

call spco_init_custom(bankcode,errno,errmsg);

GET DIAGNOSTICS retval=RETURN_STATUS;

if(retval<>0) then

set errno=errno;

set errmsg=errmsg;

return errno;

end if;

变量的定义

变量使用前必须先定义，方法为

DECLARE 变量名变量类型（default 默认值）

例：

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE inum INTEGER DEFAULT 0;

DECLARE curtime char(8);

DECLARE bcode char(6);

DECLARE sqlstate char(5);

if 表达式

if 条件1 then

逻辑体；

elseif 条件2 then

逻辑体；

else

逻辑体；

end if;

例：

IF rating = 1 THEN

UPDATE employee

SET salary = salary * 1.10, bonus = 1000

WHERE empno = employee_number;

ELSEIF rating = 2 THEN

UPDATE employee

SET salary = salary * 1.05, bonus = 500

WHERE empno = employee_number;

ELSE

UPDATE employee

SET salary = salary * 1.03, bonus = 0

WHERE empno = employee_number;

END IF;

case表达式

case 变量名 when

变量值1 then

. . .

when

变量值2 then

- - -

else

. . .

end case;

或

case when

变量名=变量值1 then

. . .

when

变量名=变量值2 then

- - -

else

. . .

end case;

例一：

CASE v_workdept

WHEN 'A00'

THEN UPDATE department

SET deptname = 'DATA ACCESS 1';

WHEN 'B01'

THEN UPDATE department

SET deptname = 'DATA ACCESS 2';

ELSE UPDATE department

SET deptname = 'DATA ACCESS 3';

END CASE；

例二：

CASE

WHEN v_workdept = 'A00'

THEN UPDATE department

SET deptname = 'DATA ACCESS 1';

WHEN v_workdept = 'B01'

THEN UPDATE department

SET deptname = 'DATA ACCESS 2';

ELSE UPDATE department

SET deptname = 'DATA ACCESS 3';

END CASE；

for 表达式

for 循环名 as

游标名或select 表达式

sql表达式；

end for;

例：

1）

DECLARE fullname CHAR(40);

FOR vl AS

SELECT firstnme, midinit, lastname FROM employee

SET fullname = lastname || ',' || firstnme ||' ' || midinit;

INSERT INTO tnames VALUE (fullname);

END FOR

2）

for loopcs1 as cousor1 cursor as

select market_code as market_code

from tb_market_code

for update

end for;

goto表达式

goto 标示名；

标示名：

逻辑体；

例：

GOTO FAIL；

...

SUCCESS: RETURN 0

FAIL: RETURN -200

while表达式

while 条件表达式 do

逻辑体；

end while；

LOOP表达式

LOOP... END LOOP；

例：

OPEN c1;

ins_loop:

LOOP

FETCH c1 INTO v_dept, v_deptname, v_admdept;

IF at_end = 1 THEN

LEAVE ins_loop; --中断循环

ELSEIF v_dept = 'D11' THEN

ITERATE ins_loop; --下一个循环

END IF;

INSERT INTO department (deptno, deptname, admrdept)

VALUES ('NEW', v_deptname, v_admdept);

END LOOP;

CLOSE c1;

关于游标

定义游标：

DECLARE 游标名 CURSOR FOR

Select 语句；

打开游标：

OPEN　游标名；

取值：

　FETCH　游标名　INTO 变量列表

例：

DECLARE c1 CURSOR FOR

SELECT CAST(salary AS DOUBLE)

FROM staff

WHERE DEPT = deptNumber

ORDER BY salary;

DECLARE EXIT HANDLER FOR NOT FOUND

SET medianSalary = 6666;

SET medianSalary = 0;

SELECT COUNT(*) INTO v_numRecords

FROM staff

WHERE DEPT = deptNumber;

OPEN c1;

WHILE v_counter < (v_numRecords / 2 + 1) DO

FETCH c1 INTO medianSalary;

SET v_counter = v_counter + 1;

END WHILE;

CLOSE c1;

注：游标的申明如果放在中间段，要用”begin。。。end;”.段分割标志分割开；

动态sql

1) declare stmt varchar(1024);

set stmt='create table zhouhaiming( f1 smallint, f2 varchar(9), f3 char(5) )';

prepare s1 from stmt;

execute s1;

set stmt='insert into zhouhaiming values (1,'www','aaa')';

prepare s1 from stmt;

execute s1;

2) DECLARE CURSOR C1 FOR STMT1;

PREPARE STMT1 FROM

'ALLOCATE C2 CURSOR FOR RESULT SET ?';

临时表的建立

DECLARE GLOBAL TEMPORARY TABLE TABLE_NAME

AS (FULLSELECT) DEFINITION ONLY

EXCLUDING IDENTITY COLUMN ATTRIBUTES

ON COMMIT DELETE ROWS

NOT LOGGED IN 临时表空间名 with replace；

第一行規定臨時表的名稱.

第二行規定臨時表的列的定義.

第三行規定不是從源結果表定義中復制的恒等列.

第四行規定如果沒有打開WITH GOLD光標,將會刪除表的所有行.

第五行規定不對表的改變進行記錄.

With replace选项会隐式的自动删除该临时表。

例如:

DECLARE GLOBAL TEMPORARY TABLE DEC_BSEMPMS

AS (SELECT * FROM BSEMPMS) DEFINITION ONLY

EXCLUDING IDENTITY COLUMN ATTRIBUTES

ON COMMIT DELETE ROWS

NOT LOGGED；

DB2中的几个全局变量

n ROW_COUNT—影响行数

UPDATE CORPDATA.PROJECT

SET PRSTAFF = PRSTAFF + 1.5

WHERE DEPTNO = deptnbr;

GET DIAGNOSTICS rcount = ROW_COUNT;

n RETURN_STATUS--返回状态

CALL TRYIT;--调用存储过程

GET DIAGNOSTICS RETVAL = RETURN_STATUS;

IF RETVAL <> 0 THEN

...

LEAVE A1;

ELSE

...

END IF;

n SQLSTATE—SQL返回错误代码

注：使用前必先定义

declare sqlstate char(5);

declare state char(5);

insert into tbname values(…)

set state=sqlstate;

if(state<> '00000') then

return -1;

end if;

关于ATOMIC和NOT ATOMIC

P1:BEGIN ATOMIC –P1段的事务会自动回滚

P1:BEGIN NOT ATOMIC –P1段的事务不会自动回滚

DB2中的条件句柄

句柄类型:

n CONTINUE

n EXIT

n UNDO

条件类型：

n SQLSTATE string

n SQLEXCEPTION

n SQLWARNING

n NOT FOUND

例：

1） DECLARE EXIT HANDLER FOR NOT FOUND

SET medianSalary = 6666;

2） DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE EXIT HANDLER FOR not_found

SET rating = -1;

3) DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR FOR

SELECT deptno, deptname, admrdept

FROM department

ORDER BY deptno;

DECLARE CONTINUE HANDLER FOR not_found

SET at_end = 1;

如何抽取/提交存储过程

db2 "get routine into 文件名 from procedure 存储过程名"

抽取存储过程；

提交存储过程

db2 "put routine from 文件名"

安装已编译好的存储过程。

如何在命令符下提交存储过程

在存储过程的最后加上@符号，然后在命令符下打入：db2 -td@ -vf procfile.sql 就可以生成过程。

非存储过程的SQL文件，在命令符下打入：db2 –tvf sqlfile.sql

从存储过程返回结果集(游标)的用法

1、建一sp返回结果集

CREATE PROCEDURE DB2INST1.Proc1 (

LANGUAGE SQL

result sets 2 --(返回两个结果集)

P1: BEGIN

declare c1 cursor with return to caller for

select market_code

from tb_market_code;

--指定该结果集用于返回给调用者

declare c2 cursor with return to caller for

select market_code

from tb_market_code;

open c1;

open c2;

END P1

2、建一SP调该sp且使用它的结果集

CREATE PROCEDURE DB2INST1.Proc2 (

out out_market_code char(1))

LANGUAGE SQL

P1: BEGIN

declare loc1,loc2 result_set_locator varying;

--建立一个结果集数组

call proc1;

--调用该SP返回结果集。

associate result set locator(loc1,loc2) with procedure proc1;

--将返回结果集和结果集数组关联

allocate cursor1 cursor for result set loc1;

allocate cursor2 cursor for result set loc2;

--将结果集数组分配给cursor

fetch cursor1 into out_market_code;

--直接从结果集中赋值

close cursor1;

END P1

悟V-SpHeNIC

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
1
评论
【转】db2知识文档（二）

安装DB2默认值? 在WINDOWS或OS/2中默认实例的是DB2在LINUX或UNIX环境下默认实例的是DB2INST1 在WINDOWS或OS/2中默认帐户的是DB2ADMIN在LINUX或UNIX环境下默认帐户的是DB2AS 如何定义序列CREATE SEQUENCE ORDERSEQ START WITH 1 INCREMENT BY 1 NO...
复制链接

扫一扫