Solved

Connection Pooling for MS SQL Server and Sun One App/Web Server

Posted on 2006-11-01
11
3,747 Views
Last Modified: 2013-11-21
Hi Experts. I am new to JAVA and I have been gien the responsibility to write and application using JDBC connection pooling with MS SQlServer Express. and SUN ONE Web server From my research, I use the SQLServerXADatasource to
register the datasource and get the connections
Somewhere this does not seem to work.
Please assist me is determining what other classes I need to use to get the connection pool to work.
Here is what I have done
I created the Connection Pool and JNDI resource in Sun One server
Then I do a lookup and bind in my java code with SQLServerXADatasource
Then I get a connection from the datasource (SQLServerXAdatasource.getConnection)
According to the documentation I have found thius far, that is all I need to do because SQlServerXADatasource implements and extends the other classes that I need
However, when I monitor the database, I don't see the connections and I still get a lot of outofmemory exceptions from the web server when I run my application under a high load.
Plase help me shed on light in this matter
Thansk you
0
Comment
Question by:rnzimiro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 17849202
The OutOfMemoryError could be because you are using the connections and you never release them, so the application server creates even more to server more requests instead of getting the ones from the pool. It's hard to say what exactly is going on, but the steps you follow to create the connection pool and the datasource look correct. Can you read data from the database with the connection you get from the pool? Do you have a loop that you create connections in there? Do you release all the connections after you are done with them? How do you monitor the database?

Not sure if these question will help to identify the problem, but have a look at them and get back to us with more information.
0
 

Author Comment

by:rnzimiro
ID: 17854563
Actually I am closing the connections after the database call has been completed. Initially I was destroying the connection objects but I realized after I close the connection, the connection should be returned to the pool. I am not creating the connection in a loop. To monitor the connections, I use SQL server 2005 studio and there is a facility that lets you monitor the connections to the database. When I monitor the database I can see the connections come in die which is puzzling because I thought that one the first connection is requested, the pool should, automatically create the minimum size of connections, say 20 but leave them all in stand-by until I need them. This is not the case.
Yes I can read from the database with a connection from the data source which is why I am confused. I don't know if the pool is created or if the data source get connection method is creating a physical connection.

Please advise. I need to know specifically how to use SQLServerXADataSource or SQLServerConnectionPoolDataSource. Which one to use, when to use them, when not use them

Thanks
0
 
LVL 35

Expert Comment

by:girionis
ID: 17856311
> I thought that one the first connection is requested, the pool should, automatically create the minimum size of connections, say 20 but leave them all in stand-by until I need them. This is not the case.

This is not always the case. Usually the connections in the pool are created when the server starts up, therefore when you monitor the database you should already see the connections created. Of course you can also manually create the pool during run time and using Java code.

> I don't know if the pool is created or if the data source get connection method is creating a physical connection.

If you have created the pool and the datasource from the console of SUN ONE then you should already have a connection pool with a predefined number of physical connections and a datasource that is pointing to this pool.

Not sure though why you are getting the OutOfMemory exception. Have you used a profiling tool in order to see where most memory consumption occurs? Not sure what else to think of. The way you create and you use the datasource and connection pool seems ok, this is generally the way it is done.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 3

Expert Comment

by:KnightFire
ID: 17857432
Can you post your source code, or at least the relevant sections.
0
 

Expert Comment

by:ceritandogan
ID: 17857749
Hello
i had the same problem On sun one web server, I don't know which sun one webserver are you using but ,

try http://docs.sun.com/source/817-1836-10/perftune.html#wp34760 ,

jDBC Connection Pool Attributes

set poolresizequantity to 5 to 20 if the connections are using frequently. ( For example 1..5 connection for each page request )  

But the principal thing be sure that you close your resultsets and statement when the execution is over.

"Out of memory" and "resource unavailable" errors are usually caused if the maxpoolsize is not enough.
Try to increase it.
0
 

Author Comment

by:rnzimiro
ID: 17859648
When I start the server, No Pools are created. I will postt he section of server.xml  and my source code. No I have not used profiling before, like I said I am new to JAVA.
Here is my connection manager code:
<=========================================================
import java.sql.*;
import java.util.*;
import javax.sql.*;
import javax.naming.*;
import com.microsoft.sqlserver.jdbc.*;
public class ConnectionPoolMgr {
   
    /** Creates a new instance of ConnectionPoolMgr */
    static Context cnt, myContext  = null;
    //static XAConnection conn = null;
    static SQLServerXADataSource sqVreg = null;
    static SQLServerXADataSource sqAreg = null;
    static SQLServerConnectionPoolDataSource sqlData = null;
    static SQLServerConnectionPoolDataSource sqlAVALData = null;
    //static DataSource sqlData = null;
    //static Hashtable hash = null;
    static boolean isInitialized = false;
    static boolean isAVALInitialized = false;
    public ConnectionPoolMgr() {
    }
    private static void RegisterDataSource(String strDatabase, String strServer, String strUser, String strPassword)throws NamingException{
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                sqVreg = new SQLServerXADataSource();
                sqVreg.setUser(strUser);
                sqVreg.setPassword(strPassword);
                sqVreg.setServerName(strServer+"\\SQLEXPRESS");
                //create the context
                myContext = new InitialContext();
                myContext.bind("vccon",sqVreg);
            }else{
                sqAreg = new SQLServerXADataSource();
                sqAreg.setUser(strUser);
                sqAreg.setPassword(strPassword);
                sqAreg.setServerName(strServer);
                //create the context
                cnt = new InitialContext();
                cnt.bind("vccon",sqAreg);          
            }
        } catch(NamingException ne){
            System.err.println("Error with registering context: " + ne.getMessage());
        }
    }
    static void InitializePool(String strDatabase, String strServer, String strUser, String strPassword)throws NamingException{
//        hash = new Hashtable();
//        hash.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.ldap.pool.PooledConnectionFactory");
//        hash.put(Context.PROVIDER_URL,"");
//        //javax.sql.DataSource
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                //RegisterDataSource(strDatabase,strServer,strUser,strPassword);
                sqlData = new SQLServerConnectionPoolDataSource();
                myContext = new InitialContext();
                sqlData.setDatabaseName(strDatabase);
                sqlData.setURL("jdbc:sqlserver://"+ strServer);
                //sqlData.setURL("jdbc/vccon");
                sqlData.setServerName(strServer+"\\SQLEXPRESS");
                sqlData.setUser(strUser);
                sqlData.setPassword(strPassword);
                //SQLServerPooledConnection ss = null;//SQLServerConnectionPoolDataSource();
                //sqlData.setPortNumber(1433);
                System.err.println("About to bind datasource....");
                myContext.bind("vccon",sqlData);
                //cnt.rebind("jdbc/VCConnectPool",sqlData);
                System.err.println("Datasource bound to JNDI service");
                GetDataSource(strDatabase);
            }else{
                sqlAVALData = new SQLServerConnectionPoolDataSource();
                cnt = new InitialContext();
                sqlAVALData.setDatabaseName(strDatabase);
                sqlAVALData.setURL("jdbc:sqlserver://"+ strServer);
                sqlAVALData.setServerName(strServer+"\\SQLEXPRESS");
                sqlAVALData.setUser(strUser);
                sqlAVALData.setPassword(strPassword);
                //SQLServerPooledConnection ss = null;//SQLServerConnectionPoolDataSource();
                //sqlData.setPortNumber(1433);
                System.err.println("About to bind datasource....");
                cnt.bind("avalconn",sqlAVALData);
                //cnt.rebind("jdbc/VCConnectPool",sqlData);
                System.err.println("Datasource bound to JNDI service");
                GetDataSource(strDatabase);
            }
        }catch(NamingException ne){
            System.err.println("Error with registering context: " + ne.getMessage());
        }
    }
    static void GetDataSource(String strDatabase)throws NamingException{
        //build th data source
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                sqlData = (SQLServerConnectionPoolDataSource)myContext.lookup("vccon");
                System.err.println("Pool Manager registered");
                isInitialized = true;
            }else{
                sqlAVALData = (SQLServerConnectionPoolDataSource)cnt.lookup("avalconn");
                System.err.println("Pool Manager registered");
                isAVALInitialized = true;
            }
        }catch(NamingException ne){
            System.err.println("An error occured: "+ ne.getMessage());
        }
    }
   
    static Connection GetConnection(String strDatabase) throws SQLException{
        Connection vcconn = null;
        PooledConnection conPool = null;
        try{
            if(strDatabase.substring(0,2).equalsIgnoreCase("VI")){
                //conPool = sqlData.getPooledConnection();
                //vcconn = conPool.getConnection();
                vcconn = sqlData.getConnection();
            }else{
                //conPool = sqlAVALData.getPooledConnection();
                //vcconn = conPool.getConnection();
                vcconn = sqlAVALData.getConnection();
            }
        }catch(SQLException se){
            System.err.println("A connection Error has occured: "+se.getMessage());
        }
        return vcconn;
    }
}
==========================================================>

I did attempt to close the statement and the resultset explicitly but that did not address the issue.

Any other ideas?
Thanks
0
 

Author Comment

by:rnzimiro
ID: 17862384
Forgot my config section

server.xml
<RESOURCES>
      <JDBCCONNECTIONPOOL name="Pool1" datasourceclassname="com.sybase.jdbc2.jdbc.SybDataSource" steadypoolsize="20" maxpoolsize="80" poolresizequantity="2" idletimeout="300" maxwaittime="60000" connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" validationtablename="" failallconnections="off" transactionisolationlevel="read-uncommitted" isolationlevelguaranteed="off">
        <PROPERTY name="portNumber" value="1040"/>
        <PROPERTY name="Password" value="*******"/>
        <PROPERTY name="serverName" value="131.16.91.28"/>
        <PROPERTY name="User" value="******"/>
      </JDBCCONNECTIONPOOL>
      <JDBCRESOURCE jndiname="jdbc" poolname="Pool1" enabled="on"/>
      <JDBCCONNECTIONPOOL name="VCConnect" datasourceclassname="com.microsoft.sqlserver.jdbc.SQLServerXADataSource" steadypoolsize="50" maxpoolsize="150" poolresizequantity="2" idletimeout="300" maxwaittime="60000" connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" validationtablename="" failallconnections="off" transactionisolationlevel="read-uncommitted" isolationlevelguaranteed="off">
        <PROPERTY name="User" value="*****"/>
        <PROPERTY name="Password" value="*****"/>
        <PROPERTY name="ref-type" value="javax.sql.XADataSource"/>
        <PROPERTY name="serverName" value="ACAPS-DEV\SQLEXPRESS"/>
        <PROPERTY name="port" value="1433"/>
        <PROPERTY name="databaseName" value="VISUAL_CONNECT"/>
        <PROPERTY name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
      </JDBCCONNECTIONPOOL>
      <JDBCRESOURCE jndiname="vccon" poolname="VCConnect" enabled="on"/>
      <JDBCCONNECTIONPOOL name="avalPool" datasourceclassname="com.microsoft.sqlserver.jdbc.SQLServerXADataSource" steadypoolsize="50" maxpoolsize="150" poolresizequantity="5" idletimeout="300" maxwaittime="60000" connectionvalidationrequired="off" connectionvalidationmethod="auto-commit" validationtablename="" failallconnections="off" transactionisolationlevel="read-uncommitted" isolationlevelguaranteed="off">
        <PROPERTY name="User" value="vc_user"/>
        <PROPERTY name="serverName" value="ACAPS-DEV\SQLEXPRESS"/>
        <PROPERTY name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <PROPERTY name="Password" value="*****"/>
        <PROPERTY name="portNumber" value="*****"/>
        <PROPERTY name="databaseName" value="AVAL"/>
      </JDBCCONNECTIONPOOL>
      <JDBCRESOURCE jndiname="avalconn" poolname="avalPool" enabled="on"/>
    </RESOURCES>
0
 

Expert Comment

by:ceritandogan
ID: 17866029
I don't understand why you use ConnectionPoolMgr class

Try this simple piece of this code below:

try{
                            prod=true;
                            InitialContext initContext = new InitialContext();
                            DataSource source =  (DataSource) initContext.lookup("java:comp/env/vccon");
                            connection = source.getConnection();
                            return connection;
                        }catch(Exception e) {
                            e.printStackTrace();
                        }
Maybe this forum's thread can help you how to get a connection.
http://swforum.sun.com/jive/thread.jspa?threadID=49434&messageID=169670
Sun Web server can manage the connection pool using the datasource. No need to use ConnectionPoolMgr
The pool starts when you request to a jsp which is using the datasource

Regards
0
 

Author Comment

by:rnzimiro
ID: 17871058
I already tried the above code and I get the same result. I am getting a connection fine with m above code, I just can't verify if a pool has been created.
I Created a connection Pool manager classes because I am using the DataSource class that ships with the JDBC 3.0 driver from Microsof for SQL server. The class does implement DataSoiurce so there should be no difference between DataSource and SQLServerXADataSource. I want to make sure that the ConnectionPool Manager class is only initialized  (registered)once for the life of the application or until the web server is restarted
0
 
LVL 35

Accepted Solution

by:
girionis earned 500 total points
ID: 17879515
> I just can't verify if a pool has been created.

If you can read/write from/to the database then the pool and the datasource are created.

> I want to make sure that the ConnectionPool Manager class is only initialized  (registered)once for the life of the application or until the web server is restarted

This depends on you. If you create everything with the hand (i.e. by writing java code) then you have to make sure that the class containing the datasource and the pool won't be loaded several times by the rest of the components that use it. Otherwise, if you create it by using the console (and thus you leave it up to the application server) then you can be pretty sure that it will be loaded once.
0
 
LVL 35

Expert Comment

by:girionis
ID: 17954063
:)
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
A solution for Fortify Path Manipulation.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question