Re: MS SQL Server JDBC connections

Chris:

The following may or may not be useful to you. I did some
testing/development with the MSJDBC driver last year. The following is an
excerpt from an article that was published in the "iSeries 400 Experts
Journal".

Begin quoted material:

I had a tremendous amount of difficulty with the host name part of the URL.
In fact, when I started testing, I used the TCP/IP address of the Windows
2000 server (10.1.1.89) instead of the host name. I was profoundly
distressed to see that my response time to fetch 91 rows from SQL Server was
always over 30 seconds. That really didn't seem right, as I have been
developing .NET applications on the same server, and routinely have
subsecond response times. Although I realize that Microsoft and Java have
not, in the past, enjoyed a harmonious relationship, I couldn't bring myself
to believe that Microsoft would purposely make its own JDBC driver as
lethargic as my example showed.

As a last ditch effort, I changed the TCP/IP address to the TCP/IP host
name, and added the host name for the SQL Server PC to my development PC's
HOSTS file (I do not have a DNS server in my small LAN). To my surprise, the
JdbcTest1s class now delivered subsecond response time, which I expected for
such a small result set. I realize that practically all other development
shops have a DNS server and use host names instead of actual TCP/IP
addresses. My point is that by developing and testing with a small test
class, I was able to more readily isolate the cause of the problem.

Moving the test to the iSeries

Now that I had a test class that I was happy with, I exported it from WDSCi
(in Jar file JdbcTest.jar) to an IFS directory on my iSeries. I also copied
the three JDBC Jar files to their own directory in the IFS. I was now ready
to test the JdbcTest1s class running directly on the iSeries. The class can
be easily run from a command entry panel, using the Run Java (RUNJVA)
command. Before running the command, I used the Work with Environment
Variable (WRKENVVAR) command to set the CLASSPATH evironment variable.

For my class path, I specified the IFS directory that contained my
JdbcTest.jar first, followed by the three entries for the JDBC driver Jars.
Keep in mind that the default class path separator character for the iSeries
is the colon (:) character.

I also repeated my slow-performing test on the iSeries. I tested the class
using the TCP/IP address first, and again observed the dismal performance. I
then added the SQL Server host name to my iSeries HOSTS file (using the
CFGTCP menu, option 10). After adding the name to the HOSTS file, the
performance was still bad. I then remembered that I needed to set the host
name search priority on the iSeries to *LOCAL (CFGTCP, option 12). At that
point, the JdbcTest1s class perked up and once again delivered subsecond
performance, running on the iSeries. I now had the iSeries working directly
with SQL Server, although it was running Java.

End quoted material

Craig Pelkie



----- Original Message ----- 
From: "Price, Chris" <Chris.Price@xxxxxxxxxxxxx>
To: "Websphere Development Studio Client for iSeries" <wdsci-l@xxxxxxxxxxxx>
Sent: Tuesday, February 28, 2006 5:18 AM
Subject: Re: [WDSCI-L] MS SQL Server JDBC connections


> Just to tidy up this discussion, I've now tried running my code on a linux
> box, and it's just as slow as on the iSeries. So whatever the problem is,
> it's nothing to do with the iSeries.
>
> This is pure speculation, but it may be that the MS JDBC driver doesn't
work
> very well when used on non-Windows platforms...
>
> Thanks to those who tried to help.
> Regards,
> Chris.
>
> -----Original Message-----
> From: wdsci-l-bounces@xxxxxxxxxxxx [mailto:wdsci-l-bounces@xxxxxxxxxxxx]
On
> Behalf Of Price, Chris
> Sent: 23 February 2006 08:42
> To: Websphere Development Studio Client for iSeries
> Subject: Re: [WDSCI-L] MS SQL Server JDBC connections
>
> Once the connection is open the sql itself performs fine - it's just
getting
> the connection object that seems to take some time.
>
> Unfortunately I can't ping or ftp to the server - it's in the DMZ behind a
> firewall, and only specific ports are open (such as 1433 for the jdbc
> driver).
>
> Thanks for everybody's help. Since no-one leapt in and said "oh yeah, that
> happened to me" I'm inclined to assume that it's just a feature of our
> network here.
>
> -----Original Message-----
> From: wdsci-l-bounces@xxxxxxxxxxxx [mailto:wdsci-l-bounces@xxxxxxxxxxxx]
On
> Behalf Of DeLong, Eric
> Sent: 22 February 2006 23:15
> To: 'Websphere Development Studio Client for iSeries'
> Subject: Re: [WDSCI-L] MS SQL Server JDBC connections
>
> That doesn't seem likely, since his datasource is MS Sql Server....  No
> direct access to MS sequel server from iSeries.
>
> I don't suppose a simple ping might yield any significant answers, but
I've
> seen problems where iSeries is 100/Full, plugged into a router setup for
> 100/auto, where thruput sags...
>
> Can you ftp something from the iSeries to the MS box, to verify the
> connection speed?
>
> You should be able to see the JDBC connection socket listen in NETSTAT
*CNN.
> There's various performance metrics listed there that might help pin down
> the problem...
>
> hth,
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-297-2863 or ext. 1863
>
>
>
> -----Original Message-----
> From: wdsci-l-bounces@xxxxxxxxxxxx
> [mailto:wdsci-l-bounces@xxxxxxxxxxxx]On Behalf Of Elvis Budimlic
> Sent: Wednesday, February 22, 2006 4:41 PM
> To: 'Websphere Development Studio Client for iSeries'
> Subject: Re: [WDSCI-L] MS SQL Server JDBC connections
>
>
> Chris,
> What type of work your 'extract' process does?
> If it's SQL type of work, did you try running those queries outside java
> (i.e. STRSQL or RUNSQLSTM)?
> I'm thinking that could help you eliminate database as culprit so you know
> you're correct to focus on enhancing Java performance.
>
> Elvis
>
> -----Original Message-----
> Subject: Re: [WDSCI-L] MS SQL Server JDBC connections
>
> Thanks for the reply guys, but the problem is occurring after the JVM has
> started - my timings are generated in the main method being executed.
Before
> creating the SQL Server connection, which is what seems to hang, an
iSeries
> JDBC connection is already created, which is much quicker.
>
> I think the problems are either:
> - something to do with the SQL Server JDBC driver, (perhaps a property
needs
> setting somewhere).
> - are network related (but since both the iSeries and development PC's are
> on the Lan and the SQL server is in a DMZ, I would have though that the
> connection speeds would be similar.)
> - resource related - it just happens to be at this point the jvm runs out
of
> memory and starts asking for more? (does anyone have any tuning pointers?)
>
> Once the connection created the actual data transfers take roughly the
same
> amount of time.
>
> Regards,
> Chris.
>
>
>
> -- 
> This is the Websphere Development Studio Client for iSeries  (WDSCI-L)
> mailing list
> To post a message email: WDSCI-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
> or email: WDSCI-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/wdsci-l.
>
> -- 
> This is the Websphere Development Studio Client for iSeries  (WDSCI-L)
> mailing list
> To post a message email: WDSCI-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
> or email: WDSCI-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/wdsci-l.
>
> ******************************************************
> The information in this E-mail and in any attachment is
> confidential and is intended solely for the addressee.
> Access, copying, disclosure or use of such information
> by anyone else is unauthorised. If you are not the
> intended recipient please contact postmaster@xxxxxxxxxxxxxx
> While reasonable efforts are made to ensure these files are free
> of virus infection and offensive materials, if something of this
> nature is inadvertently sent to you, please destroy it, accept
> our apologies and contact postmaster@xxxxxxxxxxxxx with
> details of the sender. We will ensure that action is taken
> immediately to prevent any recurrence. Debenhams accept no
> responsibility for any views expressed by the originator of this email.
>
>
> Debenhams Retail plc (reg. no. 83395) Registered in England and Wales.
> Registered office: 1 Welbeck Street, London W1G 0AA.
>
> http://www.debenhams.com
> ******************************************************
> -- 
> This is the Websphere Development Studio Client for iSeries  (WDSCI-L)
> mailing list
> To post a message email: WDSCI-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
> or email: WDSCI-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/wdsci-l.
> -- 
> This is the Websphere Development Studio Client for iSeries  (WDSCI-L)
mailing list
> To post a message email: WDSCI-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/wdsci-l
> or email: WDSCI-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/wdsci-l.