IBM Support

50 DB2 Nuggets #9 : Tech Tip - Settings to Customize Java Stored Procedure Execution

Technical Blog Post


Abstract

50 DB2 Nuggets #9 : Tech Tip - Settings to Customize Java Stored Procedure Execution

Body

DB2 LUW is capable of running stored procedures and functions coded in several languages. Among these languages is Java. Since Java stored procedures and functions, (collectively referred to as routines) require a JVM there are some configuration settings that are specific to this type of routine. DB2 comes with a JDK and by default it is this JDK that is used to run Java routines. It is possible to alter DB2 to use another JDK to run Java routines. JDKs that are supported to run routines are documented in the DB2 infocenter for each release.

 

The Version 10.1 page is located at the following URL.

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.qb.server.doc/doc/r0011932.html

 

The JDK DB2 uses is specified by the JDK_PATH dbm cfg variable and by default is set to the instance's JDK location.

 

The following URL documents this variable for version10.1

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.config.doc/doc/r0000138.html

 

In addition to the JDK you can specify how much Java Heap each JVM will use. If you believe your routine workload may require more or less Java Heap than the default, you can modify the amount of heap allocated by changing the dbm cfg variable JAVA_HEAP_SZ to the desired value.

 

The following URL documents the JAVA_HEAP_SZ variable for version10.1

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.config.doc/doc/r0000137.html

 

The JVM for DB2 Java routines is created per process. Java routines that have been created as “threadsafe” can run with other routines in a single process and use a single JVM. If there is a Java routine that is expected to use a large amount of resources. You may wish to create this routines as “not threadsafe”. This will cause it to be run in its own process and have all the resources of the JVM for its use.

The following URLs document the Create Procedure and Create function statements for 10.1

 

Create Procedure

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008328.html

 

Create Scalar Function

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004461.html

 

Create Table Function

http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0004462.html

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11141594