Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1399065
  • 博文数量: 335
  • 博客积分: 4690
  • 博客等级: 上校
  • 技术积分: 4337
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-08 21:38
个人简介

无聊之人--除了技术,还是技术,你懂得

文章分类

全部博文(335)

文章存档

2016年(29)

2015年(18)

2014年(7)

2013年(86)

2012年(90)

2011年(105)

分类: DB2/Informix

2013-05-30 22:30:54

前面介绍了相关procedure的知识,如可以,在trigger中调用procedure,但是貌似V10现在只支持 native sql/external sql  procedure,现在我们要讨论的问题是,这二者之间的区别?
differ  between natvie sql procedure / external sql procedure :
Before: SQL procedure turned into a C language 
program under the covers
? Runs as an external stored procedure in a WLM-managed SPAS
? Not-in-DB2 part of a C program generally consumes more CPU 
than does equivalent COBOL code (though less than Java)
? A native SQL procedure (available beginning with DB2 9 
in new function mode) is just a package – a “runtime 
structure” based on the SQL statements to be executed
? A native SQL procedure runs in the DB2 database services
advantage of native sql procedure :
For every SQL statement in an external stored procedure 
(or any other external-to-DB2, SQL-issuing program), an 
“addressability round trip” is required
? Program’s task switches addressability from “home” address space 
(for external SQL procedure, that’s a WLM-managed stored 
procedure address space) to DB2 DBM1 for SQL execution, then 
switches back
? Each round trip probably consumes a few thousand instructions, and 
that’s just the back-and-forth – not SQL execution in DBM1
Native SQL procedures eliminate this extra path length
? With the CALL to the native SQL procedure, you’re already in DBM1,
and you stay there until the stored procedure completes
上图:

life cycle of native procedure :

Simpler creation, management, maintenance versus 
external stored procedures
? No external-to-DB2 resources involved (e.g., no source / 
object / load libraries)
? The native SQL procedure package is the executable, and it is stored 
in the DB2 directory
? No external-to-DB2 processes involved (e.g., no need for 
compile and link processes)
? Among other good things: no worries about 
program/package coordination
? Native SQL procedures have a consistency token (used to 
match external-to-DB2 programs with corresponding package), 
but it’s just a “synonym” for the procedure’s version ID
二者之间的关系,可以从external procedure convert to native sql procedure
method: 
Sometimes, as simple as dropping and recreating the procedure 
without the FENCED and EXTERNAL options, and without a WLM 
ENVIRONMENT specification
? May need WLM ENVIRONMENT FOR DEBUG MODE

V10参数的改进:


内容补充:
Stored procedures 
A user-written program  
Can be called by an application with an SQL CALL statemen
A compiled program (not for Native SQL procedures) 
Stored at a DB2 server 
Can execute SQL statements 
主要构成部分:
SQL control statements 
– SQL control statements provide the capability to control the logic flow, 
declare and set variables, and handle warnings and exceptions.  
– Assignment/CALL/CASE/compound/FOR/GET DIAGNOSTICS 
/GOTO/IF/ITERATE/LEAVE/LOOP/REPEAT/RESIGNAL/RETURN 
/SIGNAL/WHILE 
 
? SQL statements 
– ALTER TABLE/ALTER VIEW/CALL/CONNECT/CREATE 
ALIAS/CREATE DATABASE/CREATE GLOBAL TEMPORARY 
TABLE/CREATE INDEX/CREATE PROCEDURE/CREATE 
TABLE/CREATE VIEW/DECLARE CURSOR/DECLARE GLOBAL 
TEMPORARY TABLE/DELETE/DROP INDEX/DROP TABLE/DROP 
VIEW/EXECUTE/GRANT/INSERT/LABEL/LOCK 
TABLE/OPEN/PREPARE/RELEASE 
SAVEPOINT/RENAME/REVOKE/ROLLBACK1/SELECT INTO/SET 
SCHEMA/TRUNCATE/UPDATE etc
ref: 
ibm red books 
 

阅读(1524) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~