× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have a User Defined Table Function (UDTF) written in Java that
connects to an Oracle database on a remote pSeries.  It literally takes
up to four minutes to return a result set containing fewer than 250
records.  Subsequent calls return the result set in twenty seconds or
fewer.  (The calls were done via a STRSQL session).

If I gut the UDTF program so that it becomes a simple program creating a
JDBC connection to the remote database, wrap a Main function around it
and call it through QSH, I can retrieve and iterate through the returned
ResultSet in under twenty seconds.

Before running a test case I logged completely out of my session, the
logged back in, thus starting fresh.  Both programs had the CRTJVAPGM
command run against them, with an optimization level 40 requested.

Further investigation (via the liberal sprinkling of System.out.println
statements calling a simple time calculator) result in the following output:

For the  Java UDTF (comments were added by me for clarity):

-- Load the Oracle Driver
SQLUDF_TF_FIRST  Execution time: 4190 ms.
-- Connect to the remote database & execute query
SQLUDF_TF_OPEN  Execution time: 203914 ms.
-- Fetch records...
SQLUDF_TF_FETCH  Execution time: 1519 ms.
SQLUDF_TF_FETCH  Execution time: 0 ms.


For the QSH java execution:

Getting driver  Execution time: 1479 ms.
Connecting  Execution time: 12477 ms.
Running Query  Execution time: 5458 ms.
ResultSet  Execution time: 267 ms.
ResultSet  Execution time: 2 ms.


Can anyone point me in the right direction here to get this problem
resolved?  I've been in contact with IBM tech support and we have done
some comm traces and such but so far, nothing of note has been
accomplished.  I'm hoping that someone on the list has seen this
behaviour before and can show me the secret handshake necessary to get
good service from a Java-based UDTF.

Thanks in advance for any questions, comments or smart remarks.

Barry

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.