|
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 mailing list archive is Copyright 1997-2025 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.