469,609 Members | 1,146 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Delays in between UDF invocations

Hi,

I have written a Java UDF for DB2 and registered it using the
following params:

CREATE FUNCTION arsu_bit_value ( varchar(32) for bit data, integer )
RETURNS INTEGER EXTERNAL NAME
'ch.ips.g2.db2.ch.ips.g2.db2.ARSU_BIT_VALUE!arsu_b it_value'
LANGUAGE java PARAMETER STYLE db2general DETERMINISTIC NOT FENCED NOT
NULL CALL NO SQL NO EXTERNAL ACTION SCRATCHPAD
NO FINAL CALL ALLOW PARALLEL NO DBINFO

Basically the function returns the value (0 or 1) of the specified bit
in a bit array.

Example of usage:

-- returns all arsu rights that have the 254-th bit
-- set to 1 (LIST is varchar(32) for bit data)

select * from arsu_list where ARSU_BIT_VALUE(LIST, 254)=1

The functions itself works fine and is relatively fast (<1ms per
invocation), however, DB2 seems to "insert" approx. 160 ms delays in
between individual function invocations. Does anybody have any
reasonable explanation for this?

Thank you,
Jan Moravec
Nov 12 '05 #1
3 1880
As of v8, all java routines run outside the engine. The context
switching could account for the delay. If you're pre v8, then it'd be
JNI argument creation.

Jan Moravec wrote:
Hi,

I have written a Java UDF for DB2 and registered it using the
following params:

CREATE FUNCTION arsu_bit_value ( varchar(32) for bit data, integer )
RETURNS INTEGER EXTERNAL NAME
'ch.ips.g2.db2.ch.ips.g2.db2.ARSU_BIT_VALUE!arsu_b it_value'
LANGUAGE java PARAMETER STYLE db2general DETERMINISTIC NOT FENCED NOT
NULL CALL NO SQL NO EXTERNAL ACTION SCRATCHPAD
NO FINAL CALL ALLOW PARALLEL NO DBINFO

Basically the function returns the value (0 or 1) of the specified bit
in a bit array.

Example of usage:

-- returns all arsu rights that have the 254-th bit
-- set to 1 (LIST is varchar(32) for bit data)

select * from arsu_list where ARSU_BIT_VALUE(LIST, 254)=1

The functions itself works fine and is relatively fast (<1ms per
invocation), however, DB2 seems to "insert" approx. 160 ms delays in
between individual function invocations. Does anybody have any
reasonable explanation for this?

Thank you,
Jan Moravec


Nov 12 '05 #2
Thanks for your answer. BTW we are running v7. What difference does it
make if the JVM runs inside or outside of the DB engine?

The JVM is always an external process from the DB engine's perspective
so you always end up with either JNI calls, or some sort of a socket
communication. Correct me if I am wrong, but I cannot see any other
way.

What is actually the diff between v7 and v8 in terms of invocation of
methods in the JVM process?

Thank you,
Jan Moravec
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<bp**********@hanover.torolab.ibm.com>...
As of v8, all java routines run outside the engine. The context
switching could account for the delay. If you're pre v8, then it'd be
JNI argument creation.

Jan Moravec wrote:
Hi,

I have written a Java UDF for DB2 and registered it using the
following params:

CREATE FUNCTION arsu_bit_value ( varchar(32) for bit data, integer )
RETURNS INTEGER EXTERNAL NAME
'ch.ips.g2.db2.ch.ips.g2.db2.ARSU_BIT_VALUE!arsu_b it_value'
LANGUAGE java PARAMETER STYLE db2general DETERMINISTIC NOT FENCED NOT
NULL CALL NO SQL NO EXTERNAL ACTION SCRATCHPAD
NO FINAL CALL ALLOW PARALLEL NO DBINFO

Basically the function returns the value (0 or 1) of the specified bit
in a bit array.

Example of usage:

-- returns all arsu rights that have the 254-th bit
-- set to 1 (LIST is varchar(32) for bit data)

select * from arsu_list where ARSU_BIT_VALUE(LIST, 254)=1

The functions itself works fine and is relatively fast (<1ms per
invocation), however, DB2 seems to "insert" approx. 160 ms delays in
between individual function invocations. Does anybody have any
reasonable explanation for this?

Thank you,
Jan Moravec

Nov 12 '05 #3
There is a big difference in whether the JVM runs in the engine or
not...it is not an external process, it is started either directly in
the agent, or in the db2dari process (JNI apis used to start up the JVM
threads). When it runs in the engine (in an agent), you avoid context
switches on the invocation of the UDF.

Unfortunately, in v8 we discovered that running the jvm in the engine
can cause fatal termination of the entire engine (JVM will call abort()
in some situations, blowing away the instance, which is not acceptable),
so we no longer allow for trusted Java routines.

Since you're v7, you have no sql etc in your UDF, so you're looking at
the overhead from JNI calls to populated the routines arguments, and
garbage collection...for really short duration routines you'd be better
off going with C. The cost of setting up to run a Java routines will
only be amortized if the actual runtime of the routine is more significant.

Jan Moravec wrote:
Thanks for your answer. BTW we are running v7. What difference does it
make if the JVM runs inside or outside of the DB engine?

The JVM is always an external process from the DB engine's perspective
so you always end up with either JNI calls, or some sort of a socket
communication. Correct me if I am wrong, but I cannot see any other
way.

What is actually the diff between v7 and v8 in terms of invocation of
methods in the JVM process?

Thank you,
Jan Moravec
Sean McKeough <mc******@nospam.ca.ibm.com> wrote in message news:<bp**********@hanover.torolab.ibm.com>...
As of v8, all java routines run outside the engine. The context
switching could account for the delay. If you're pre v8, then it'd be
JNI argument creation.

Jan Moravec wrote:

Hi,

I have written a Java UDF for DB2 and registered it using the
following params:

CREATE FUNCTION arsu_bit_value ( varchar(32) for bit data, integer )
RETURNS INTEGER EXTERNAL NAME
'ch.ips.g2.db2.ch.ips.g2.db2.ARSU_BIT_VALUE!ars u_bit_value'
LANGUAGE java PARAMETER STYLE db2general DETERMINISTIC NOT FENCED NOT
NULL CALL NO SQL NO EXTERNAL ACTION SCRATCHPAD
NO FINAL CALL ALLOW PARALLEL NO DBINFO

Basically the function returns the value (0 or 1) of the specified bit
in a bit array.

Example of usage:

-- returns all arsu rights that have the 254-th bit
-- set to 1 (LIST is varchar(32) for bit data)

select * from arsu_list where ARSU_BIT_VALUE(LIST, 254)=1

The functions itself works fine and is relatively fast (<1ms per
invocation), however, DB2 seems to "insert" approx. 160 ms delays in
between individual function invocations. Does anybody have any
reasonable explanation for this?

Thank you,
Jan Moravec


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by roisin | last post: by
3 posts views Thread by rajiv04 | last post: by
reply views Thread by Krzysiek | last post: by
4 posts views Thread by Ranald Davidson via AccessMonster.com | last post: by
2 posts views Thread by Rob Long | last post: by
3 posts views Thread by eager | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.