前面介绍了相关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
