Questions[jdbc](server)

I'm trying to connect to RDS MySQL from linux machine. When I test the connection with telnet, the telnet session succeeds but when I'm trying to connect to the db with the next command

mysql -h hostname -uusername -ppassword

I can't get access and the command gets stuck. In the RDS configuration the public accessibility is set to yes, and I added an inbound rule in the security group allowing access from all addresses.

I'm assuming that when this problem will be solved, I will be able to connect via jdbc that also doesn't work right now.

Thanks to the helpers!

update: attaching the outputs of the commands I ran:

When using an Amazon Web Services RDS instance from a Java application, what is the best JDBC connection string? It should tolerate the instance falling over to the standby. AWS handles flipping the CNAME to point from the primary to standby under the hood.

I've and Oracle (11 Enterprise) schema with a table

 CREATE TABLE USER.WSP_BUNDLE ( 
    NODE_ID     RAW(16) NOT NULL,
    BUNDLE_DATA BLOB NOT NULL 
    );
CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ON USER.WSP_BUNDLE(NODE_ID);

and 3'rd party library (Java 6, JDBC - latest jdbc driver) that manipulate it.

Oracle profiler (tkprof) shows that about 50% of overall time the lib execute such statement:

update WSP_BUNDLE set BUNDLE_DATA = :1 where NODE_ID = :2

TKProf data

Plan Hash: 4085453680
update WSP_BUNDLE set BUNDLE_DATA = :1  where NODE_ID = :2 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      264      0.00       0.00          0          0          0           0
Execute    400     30.59     382.88     141451    1623163    3233827         400
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      664     30.59     382.88     141451    1623163    3233827         400

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  WSP_BUNDLE (cr=8753 pr=707 pw=706 time=0 us)
      1   INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)(object id 75730)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                    141251        5.53        328.04
  direct path write                             402        0.09          0.43
  SQL*Net more data from client              142158        1.04         11.89
  direct path read                              200        0.03          0.07
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                     400        0.00          0.00
  SQL*Net message from client                   400        0.29          0.50
  log file switch (private strand flush incomplete)
                                                  5        0.05          0.23
  asynch descriptor resize                   139723        7.46          8.57
  buffer busy waits                               2        0.00          0.00
  log file switch (checkpoint incomplete)         3        0.18          0.27
  log file sync                                   2        0.00          0.00

Could anybody explain/hint me what's going on? Why the update is so slow?

The table WSP_BUNDLE contains about 200+k rows. At the same time I've other tables in the same schema with blobs (CLOB to be more concrete) that contains 600+k rows where similar updates work correctly.

(Posted first on SO, but its not getting any answers there. Cross-posting to reach a larger audience, since it is cross-discipline.)

In the process of trying to help out an app dev team with some performance issues on a SQL 2000 server, I ran a SQL trace and discovered that all calls to the database are full of API Server Cursor statements (sp_cursorprepexec, sp_cursorfetch, sp_cursorclose).

Looks like they're specifying some connection string properties that force the use of server-side cursors, retrieving only 128 rows of data at a time: (From http://msdn.microsoft.com/en-us/library/Aa172588)

When the API cursor attributes or properties are set to anything other than their defaults, the OLE DB provider for SQL Server and the SQL Server ODBC driver use API server cursors instead of default result sets. Each call to an API function that fetches rows generates a roundtrip to the server to fetch the rows from the API server cursor.

UPDATE: They have multiple Java apps using a JDBC connection to the SQL server with selectMethod=cursor specified (as opposed to selectMethod=direct).

From my DBA perspective, that's just annoying (it clutters the trace up with useless junk), and in all likelihood is resulting in many extra app-to-SQL server round trips, reducing overall performance.

They've apparently tested selectMethod=direct in a very limited way (changed just one application out of ~60) and experienced some issues of some sort (technical details of which I do not have).

So, my questions are:

• What's the performance impact of selectMethod=cursor vs direct on an already busy SQL 2000 server? (I had speculated that increasing the number of round trips between SQL and APP servers can't do anything good. Was I wrong?)
• Is selectMethod an application-transparent setting? Could this break their apps if we change it?
• When should they use cursor vs direct? Are there some types of apps that would benefit from one over the other?

Update: Found out the name of the driver parameter, which have warranted significant edits to title, body, and tags.

Update: Added bounty. Also added bounty to SO question (which is more focused on application behavior). Thanks!

We are planning to upgrade our application servers to a new version of JRun which is carrying with it an upgrade from Java 1.4 to Java 1.6. As part of that, we have been testing various pieces of our software stack and realized the need to look at our Oracle JDBC drivers as well.

Looking at the Oracle site and documentation, the different versions seem to be primarily targeting various versions of the JDBC API, which tend to correspond to the version of the JVM they were introduced in. With Java (generally) being backwards compatible, it seems like sticking with ojdbc14.jar might make the most sense, but I was hoping someone out there could clarify things a bit better.

The docs specifically call out a chart like this, which makes me wonder if ojdbc14.jar is officially supported under Java 6.

classes12.jar - for Java 1.2 and 1.3
ojdbc14.jar - for Java 1.4 and 1.5
ojdbc5.jar - for Java 1.5
ojdbc6.jar - for Java 1.6

Is there something I should be aware of from a systems perspective when mixing and matching drivers and virtual machines?