RE: JDBCR4 and Inserts -- MIDRANGE-L

I'm using the jTDS driver.

Yep, I'm re-preparing the statement every time. I'll test the idea of preparing once and see how it goes. Though it sounds like the JDBC connection might not be the answer here, and that's fine. Good idea about mapping to the IFS.

In the scenario where I'm creating a file on the IFS, Bulk Insert is how the file is getting loaded into the database.

Thanks for the responses,
Kurt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Monday, February 25, 2013 11:17 AM
To: Midrange Systems Technical Discussion
Subject: RE: JDBCR4 and Inserts

Couple things to look at:

1) What JDBC driver are you using to connect to SQL Server? I've had the best results with the jTDS driver. The Microsoft drivers just don't go as fast. http://jtds.sourceforge.net/

2) You don't need to prepare and execute for each record. A JDBC PreparedStatement object is reusable. I don't know how Scott has structured his interface, but you should be able to create a PreparedStatement object and then use the executeUpdate() method against it in a loop after setting the values using the set...() methods (setString(), setBigDecimal() and so on.) Reusing a prepared statement is much, much faster than making a new one each time. The driver can then optimize and block up requests. Again, the capabilities of the driver is important. Study Scott's interface documentation for how you might utilize these capabilities.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Anderson, Kurt
Sent: Monday, February 25, 2013 10:12 AM
To: 'Midrange Systems Technical Discussion'
Subject: JDBCR4 and Inserts

At IBM i 7.1, we've been using Scott Klement's JDBCR4 for some time now. It's been great, although we've been doing inserts not as it's shown in the JDBCR4 presentation, but by sending the SQL statement to SQL Server for it to then use the Linked Server to select the records to insert. But we're finding limitations in doing it that way (when inserting millions of records) - every so often we get a "Connection Reset" message. I'm told that the Linked Server can't reliably handle such a large volume of records, so we're looking at other options.

One option is to instead have the RPG program use JDBCR4 to directly insert into the SQL Server database. From my understanding based on the presentation, this method inserts one record at a time instead of writing a block of records. I ran a test insert and it took about 5 minutes to insert 10,000 records, way too slow for our purposes. This test was block-reading the file, but was preparing and executing the insert statement for every record read.

Presentation: http://www.scottklement.com/presentations/External%20Databases%20from%20RPG.pdf
See pg 25 for the Prepared Statement Insert

Another suggestion was using Client Access to create a file for a bulk insert by SQL server.
http://blog.stevienova.com/2009/05/20/etl-method-fastest-way-to-get-data-from-db2-to-microsoft-sql-server/

We also tried using CPYTOIMPF and then FTP'd the file to a location for SQL Server to load the file using a bulk insert. The bulk insert only took a minute for 2 million records. The COPY and FTP took about 10 minutes. This speed was great compared to both the test I mentioned above (inserting directly to the db from RPG) and compared to the method we have been using of sending a Linked Server statement to SQL Server (when it would work).
I was hoping to use JDBCR4 to perform the insert so there would be less steps involved in the process. It's not necessary, but I thought I might draw upon others' experience.

Thanks,

Kurt Anderson
Sr. Programmer/Analyst
CustomCall Data Systems, a division of Enghouse Systems Ltd.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.