Transcript Upgrading to DB2 9.5 for Linux, UNIX, and Windows

Best Practice on Using External Stored
Procedures and UDFs
Benjamin Leung
[email protected]
Continuing Engineering Software Development
DB2 Linux, UNIX, Windows
March 3, 2009
© 2009 IBM Corporation
Information Management – DB2
Agenda
2
●
Introduction
●
SQL vs. External Routines
●
Fenced vs. Not Fenced Routines
●
Threadsafe vs. Not Threadsafe Routines
●
Parameter Styles of External Routines
●
Restrictions on External Routines
●
Security on External Routines
●
External Routines related setting
© 2009 IBM Corporation
Information Management – DB2
Introduction
●
Stored Procedure/UDF is a user-written program that is stored on DB2 server
●
Multiple SQL statements can be called
●
Stored Procedure can be executed by an application with the CALL statement.
e.g. db2 “call deposit(‘011733’, 5000);
●
UDF is similar to stored procedure but is executed differently
e.g. db2 “select price_with_tax(price) from inventory”;
Why use Stored Procedures and UDFs
●
●
Better performance

significantly reduce network traffic
Easier to maintain

encapsulated logic

minimize change in client program
●

3
Improved application security
sensitive business logic runs on the DB2 server
© 2009 IBM Corporation
Information Management – DB2
SQL vs. External Routines
SQL Routines:
●
●
●
SQL routine is written entirely of SQL statements.
SQL statements that define the logic of the routines are included within the
CREATE statement used to create the routine in the database.
SQL routines cannot directly make system calls and cannot directly perform
operations on entities that reside outside of the database.
External Routines:
●
●
●
4
External routines are written in programming language (e.g. C, Java, CLR etc).
Routines logic implemented in a programming language application that resides
outside of the database, in the file system of the database server.
The association of the routine with the external code application is asserted by the
specification of the EXTERNAL clause in the CREATE statement of the routine.
© 2009 IBM Corporation
Information Management – DB2
FENCED vs. NOT FENCED Routines
●
Define a routine as FENCED or NOT FENCED in
CREATE PROCEDURE / CREATE FUNCTION statement
CREATE PROCEDURE CSTP (OUT para1 CHAR(8))
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE SQL
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!cstp‘;
●
5
FENCED/NOT FENCED clause does not apply to SQL routines
© 2009 IBM Corporation
Information Management – DB2
FENCED vs. NOT FENCED Routines (cont.)
NOT FENCED (Trusted) Routine:
●
●
●
Run inside db2agent
Generally NOT FENCED results in better performance as
compared with running it in FENCED mode
If the routine traps it will trap the db2agent and can bring
down the whole instance
NOT FENCED ROUTINE
C routines can be run as NOT FENCED
routine library
db2agent
6
© 2009 IBM Corporation
Information Management – DB2
FENCED vs. NOT FENCED Routines (cont.)
FENCED Routine:
●
●
●
7
Run inside db2fmp process/thread
Additional overheads in
●
spawning and setting up db2fmp process
●
communicating between db2fmp and db2agent
DB2 instance will remain unaffected if there is anything wrong
with the routine.
© 2009 IBM Corporation
Information Management – DB2
FENCED ROUTINE (NOT THREADSAFE)
1:1
routine library
C, Java, CLR routines can
be declared as FENCED
JVM/CLR
Worker Thread
db2agent
db2fmp
FENCED ROUTINE (THREADSAFE)
routine library
JVM/CLR
N:1
Master Thread
Worker Thread
Worker Thread
Worker Thread
db2agent
…
db2fmp
8
© 2009 IBM Corporation
Information Management – DB2
THREADSAFE and NOT THREADSAFE Routines
●
Define a routine as THREADSAFE or NOT THREADSAFE in CREATE
PROCEDURE/CREATE FUNCTION Statement
CREATE FUNCTION javaudf ( CLOB(100K)) RETURNS INTEGER
FENCED
THREADSAFE
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'javaUDFs.javaudf'
NO EXTERNAL ACTION
CALLED ON NULL
INPUT DETERMINISTIC
NO SQL
●
9
THREADSAFE/NOT THREADSAFE clause only applied to FENCED
routines
© 2009 IBM Corporation
Information Management – DB2
THREADSAFE and NOT THREADSAFE Routines (cont.)
THREADSAFE:
●
●
●
●
●
●
10
FENCED THREADSAFE routines run in the same process as other threadsafe
routines.
If a FENCED THREADSAFE routine abends, only the thread running this
routine is terminated.
Each types of routine (C/C++, Java, .CLR) runs in it’s own threaded db2fmp
Make sure the routine is actually threadsafe if you define the routine as
THREADSAFE in the CREATE Statement
For Java THREADSAFE routines, they will share the same JVM and Java
Heap with other Java THREADSAFE routines in the same process. Same to
CLR.
IN AIX, threaded db2fmp will be displayed as db2fmp(C) or db2fmp (Java)
© 2009 IBM Corporation
Information Management – DB2
THREADSAFE and NOT THREADSAFE Routines (cont.)
NOT THREASAFE:
●
●
11
Each NOT THREASAFE routine will run in its own dedicated process, and has it’s
own JVM or CLR (when applied).
In AIX, active non-threaded db2fmp will run as db2fmp(<agent_pid>)
idle, pooled non-threaded db2fmp process will be displayed as db2fmp(idle)
●
C Fenced routines are NOT THREADSAFE by default
●
Java and CLR Fenced routines are THREADSAFE by default
© 2009 IBM Corporation
Information Management – DB2
FENCED ROUTINE (NOT THREADSAFE)
1:1
routine library
JVM/CLR
Worker Thread
db2agent
db2fmp
FENCED ROUTINE (THREADSAFE)
routine library
JVM/CLR
N:1
Master Thread
Worker Thread
Worker Thread
Worker Thread
db2agent
…
db2fmp
12
© 2009 IBM Corporation
Information Management – DB2
Parameter Styles – C/C++
DB2 v9.5 Information Center
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.ap
dv.routines.doc/doc/c0009023.html
●
●
●
●
SQL (Supported for procedures and functions; Recommended)
DB2SQL (Supported for functions)
GENERAL (Supported for procedures)
GENERAL WITH NULLS (Supported for procedures)
Parameter Style SQL:
SQL_API_RC SQL_API_FN function-name ( SQL-arguments, SQL-argument-inds, sqlstate,
routine-name, specific-name, diagnostic-message )
Example:
extern "C" SQL_API_RC SQL_API_FN cstp ( sqlint32 *inParm, double *outParm,
SQLUDF_NULLIND *inParmNullInd, SQLUDF_NULLIND *outParmNullInd,
SQLUDF_TRAIL_ARGS )
13
© 2009 IBM Corporation
Information Management – DB2
Parameter Styles – C/C++ (cont.)
Parameter Style DB2SQL
extern "C" SQL_API_RC SQL_API_FN cstp_db2sql ( sqlint16 *inParm, double *outParm,
SQLUDF_NULLIND *nullIndVector, SQLUDF_TRAIL_ARGS )
(a vector of null indicators for each parameter is used.)
Parameter Style GENERAL WITH NULLS
extern "C" SQL_API_RC SQL_API_FN cstp_generaln( sqlint16 *inParm, double *outParm,
SQLUDF_NULLIND *inParmNullInd, SQLUDF_NULLIND *outParmNullInd)
Parameter Style GENERAL
extern "C" SQL_API_RC SQL_API_FN cstp_general ( sqlint16 *inParm, double *outParm)
●
14
No parameter marker is required for procedure result sets. Any unclosed
cursors statement from the routine will be returned as result sets
© 2009 IBM Corporation
Information Management – DB2
Parameter Styles – Java
JAVA
(Recommended)
DB2GENERAL
●
●
Parameter Style JAVA:
public static void method-name ( SQL-arguments, ResultSet[] result-set-array ) throws
SQLException
Example:
public static void javastp( int inparm, int[] outparm, ResultSet[] rs ) throws SQLException
●
●
15
Declare as public static
Return result set via ResultSet[]
© 2009 IBM Corporation
Information Management – DB2
Parameter Styles – Java (cont.)
Parameter Style DB2GENERAL:
Use Parameter style DB2GENERAL if you are using:
●
table functions
●
scratchpads
●
access to the DBINFO structure
●
the ability to make a FINAL CALL (and a separate first call) to the function or
method
If you don’t use the above features, use Parameter Style Java.
For Stored Procedure:
public class user-STP-class extends COM.ibm.db2.app.StoredProc{ ... }
For UDFs:
public class user-UDF-class extends COM.ibm.db2.app.UDF{ ... }
16
© 2009 IBM Corporation
Information Management – DB2
Parameter Styles – CLR
•
•
•
•
SQL
DB2SQL
GENERAL
GENERAL WITH NULLS
•
•
NULL indicators are System.Int16, or System.Int16[]
No parameter marker is required for procedure result sets. Any unclosed
cursors statement from the routine will be returned as result sets
Passing CLR routine parameters by value or by reference
17
C#
Visual Basic
IN
none
byValue
INOUT
ref
byRef
OUT
out
byRef, must
assigned a value
before the routine
returns to the caller
© 2009 IBM Corporation
Information Management – DB2
Parameter Styles – CLR (cont.)
Example of a CLR (C#) stored procedure written in Parameter Style SQL:
public static void clrstp
( String inParam,
Int16 inParamNullInd,
out String language, out Int16 languageNullInd,
ref String sqlState,
String funcName,
String funcSpecName,
ref String sqlMsgString)
{…}
18
© 2009 IBM Corporation
Information Management – DB2
Security on External Procedures
For FENCED Routines:
●
All FENCED routines will have the OS privilege of the fenced id.
In AIX, to check your current fenced id, look at ~/sqllib/adm/.fenced file
-rw-r--r--
●
●
1 benleung build
0 Feb 24 15:35 .fenced
In general, do not use the instance id as the fenced id
Need to understand what the routine can do, who can call the routines and what
privilege the fenced user has
db2 “call OS_PROCEDURE(“rm ~/sqllib/*”);
For NOT FENCED Routine:
●
19
All NOT FENCED routines will have the OS privilege of the instance id (as the
routines will run inside db2agent)
© 2009 IBM Corporation
Information Management – DB2
Restrictions on External Routines
DB2 v9.5 Information Center:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.ap
dv.routines.doc/doc/c0009198.html
●
CANNOT create new threads or spawn new process within your routines
CANNOT write a routine that will terminate process/thread.
CANNOT install any signal handler
DO NOT use standard input-output streams like printf(), System.out.println() etc.
CANNOT receive input from keyboard
Dynamic allocations of memory REQUIRED to be freed before external routines
return.
DO NOT use global variables
●
CANNOT call connect level APIs or Connection related statement such as
●
●
●
●
●
●
●
●
●
●
●
●
●
●
20
BACKUP
CONNECT
CONNECT TO
CONNECT RESET
CREATE DATABASE
DROP DATABASE
FORWARD RECOVERY
RESTORE
© 2009 IBM Corporation
Information Management – DB2
DBM Configuration Parameters
KEEPFENCED - Keep fenced process configuration parameter
When KEEPFENCED = NO
●
●
Non-threaded db2fmp – new db2fmp process is created and destroyed for
each fenced mode invocation
Threaded db2fmp – db2fmp process persists, but the thread created for the
call is terminated.
When KEEPFENCED = YES (Default)
●
db2fmp process or thread is reused for subsequent fenced mode calls
Recommend to set to Yes if the system have a large amount of fenced mode calls,
(with proper setting of FENCED_POOL value)
21
© 2009 IBM Corporation
Information Management – DB2
DBM Configuration Parameters (cont.)
FENCED_POOL - Maximum number of fenced processes
(cached) configuration parameter
●
For threaded db2fmp - the number of threads cached in each db2fmp process.
●
For non-threaded db2fmp - the number of processes cached.
●
Default values is AUTOMATIC (max_coordagents in v9.1)
FENCED_POOL should be set according to the average workload of your
system.
●
NOT a parameter to control the maximum number of active db2fmp processes
on a system at a given time
●
●
●
22
FENCED_POOL controls the maximum number of db2fmp process we will
keep after execution.
Maximum number of active db2fmp on a system is restricted by OS
resources on the system.
© 2009 IBM Corporation
Information Management – DB2
DBM Configuration Parameters (cont.)
In v8 and v9.1, the max number of db2fmp is capped by IPC resources
●
●
For non-threaded db2fmp, each process need 1 IPC resource
For threaded db2fmp, each process need 1 + X IPC resource, where X is
the number of threads in the process.
Size of IPC resource is approximately 2*ASLHEAPSZ (in 4K) and total memory is
DB2_FMP_COMM_HEAPSZ (in 4K)
In v9.5 and Cobra, because of STMM, memory will be adjusted automatically thus
the formula will no longer apply.
23
© 2009 IBM Corporation
Information Management – DB2
DBM Configuration Parameters (cont.)
NUM_INITFENCED -
●
24
Initial number of fenced processes
configuration parameter
The initial number of non-threaded, idle db2fmp processes created during
DB2START time
●
Used for non-threaded C/C++ external routines only
●
Default values is 0
© 2009 IBM Corporation
Information Management – DB2
DB2 Registry Variables
JAVA_HEAP_SZ – Maximum Java interpreter heap size
configuration parameter
●
Default value for HP-UX is 4096 (in 4K), 2048 for all other platforms.
●
One Java Heap for each db2fmp process
●
25
For a threaded db2fmp, java heap is shared among all the threads in
the process
© 2009 IBM Corporation
Information Management – DB2
Need Help?
Please collect the following information when contact IBM support
db2diag.log (DIAGLEVEL 4 for Java)
stacks of db2fmp (kill -36 on AIX as root)
●
●
●
●
●
db2pd –fmp output & ps –elf output for the system.
db2pd –memb fmp output
●
ls –la ~/sqllib/adm/.fenced output
●
db2trc
●
26
(db2pd –stack <pid> or db2pd –stack all)
Anything above <sqloInvokeFnArgs> belongs to the routine itself
© 2009 IBM Corporation
Information Management – DB2
Q&A
27
© 2009 IBM Corporation